Some Hosting Not Liking SET NAMES utf8 — “Cannot e

Question

I've introduced $PDO->query('SET NAMES utf8;'); in my connection library code to get some Unicode compliance. This works on some hosts where I can follow this with PDO statement executions with INSERT using the .execute().

However, on some hosting plans, I'm encountering this error:

General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

So, before SET NAMES, I introduced $PDO->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);, but I'm still getting the error. I then set that value to FALSE and I'm still getting the error.

Of course, if I remove the "SET NAMES utf8" statement, the problem goes away. And again -- this problem only occurs on some web hosts.

What's the proper way to switch MySQL to Unicode that works on most Linux hosting plans?

Answer 1

The preferred way to set the connection encoding to UTF-8 is the use of PDO::MYSQL_ATTR_INIT_COMMAND in the PDO constructor, as shown in the manual:

$dsn      = 'mysql:host=localhost;dbname=testdb';
$username = 'username';
$password = 'password';
$options  = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); 

$dbh = new PDO($dsn, $username, $password, $options);

This should always work.