While migrating one of my hobby projects from the PHP mysql extension to PDO, I came across this error:<\/p>\n
PHP Fatal error: \u00a0Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. \u00a0Consider using PDOStatement::fetchAll(). \u00a0Alternatively, 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.'<\/tt><\/p><\/blockquote>\n
A quick search on the web suggested this happens when you don’t fetch all rows from a query. I knew this wasn’t the case and didn’t want to just enable the buffered query attribute as I felt something else was wrong.<\/p>\n
Turns out this problem came about as I was trying to migrate my MySQL connection properties, previously defined with:<\/p>\n
[code lang=”php”]define(‘UTC_OFFSET’, date(‘P’));
\nmysql_query(“SET time_zone='” . UTC_OFFSET . “‘;”);
\nmysql_query(“SET NAMES ‘utf8’ COLLATE ‘utf8_general_ci;'”);[\/code]<\/p>\nThe natural change was to add these two statements to PDO::MYSQL_ATTR_INIT_COMMAND<\/tt> (separated by a semicolon). However, that’s where the problem is. The SET<\/a><\/tt> command allows\u00a0both to be specified at once, hence\u00a0the right way of doing it is:<\/p>\n
[code lang=”php”]PDO::MYSQL_ATTR_INIT_COMMAND => “SET NAMES ‘utf8’ COLLATE ‘utf8_general_ci’, time_zone = ‘” . UTC_OFFSET . “‘”[\/code]<\/p>\n
Credit: Stack Overflow<\/a><\/p>\n
Bonus:\u00a0Setting the timezone with a UTC offset allows you to use a zone that PHP knows about, but the MySQL server doesn’t. That way it can be set with the ini setting date.timezone<\/tt> or date_default_timezone_set<\/a><\/tt> and doesn’t need to be modified in two places if it needs to be changed.<\/p>\n","protected":false},"excerpt":{"rendered":"
While migrating one of my hobby projects from the PHP mysql extension to PDO, I came across this error: PHP Fatal error: \u00a0Uncaught exception ‘PDOException’ with message ‘SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. … Continue reading