Content with Style

Web Technique

Zend Framework DB and Mysql pre 5.17

by Pascal Opitz on October 8 2009, 10:38

I was getting weird errors when running multiple queries with Zend Framework, that I just couldn't replicate on my local environment.


Exception information:

Message: SQLSTATE[HY000]: 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.
Stack trace:

#0 /project/ZendFramework-1.7.1/Zend/Db/Statement.php(109): Zend_Db_Statement_Pdo->_prepare('UPDATE `foo...')
#1 /project/ZendFramework-1.7.1/Zend/Db/Adapter/Pdo/Abstract.php(170): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Pdo_Mysql), 'UPDATE `foo...')
#2 /project/ZendFramework-1.7.1/Zend/Db/Adapter/Abstract.php(429): Zend_Db_Adapter_Pdo_Abstract->prepare('UPDATE `foo...')
#3 /project/ZendFramework-1.7.1/Zend/Db/Adapter/Pdo/Abstract.php(220): Zend_Db_Adapter_Abstract->query('UPDATE `foo...', Array)
#4 /project/ZendFramework-1.7.1/Zend/Db/Adapter/Abstract.php(551): Zend_Db_Adapter_Pdo_Abstract->query('UPDATE `foo...', Array)

...

Turns out the server runs MySQL pre 5.17:


$ yum list installed | grep mysql
mysql.i386                              5.0.45-7.el5                   installed
mysql-server.i386                       5.0.45-7.el5                   installed
php-mysql.i386                          5.1.6-23.2.el5_3               installed

In order to fix this we need to turn on query buffering:


$pdoParams = array(
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true
);

$params = array(
    'host'           => '127.0.0.1',
    'username'       => 'webuser',
    'password'       => 'xxxxxxxx',
    'dbname'         => 'test',
    'driver_options' => $pdoParams
);

$db = Zend_Db::factory('Pdo_Mysql', $params);

Thanks to Joe Devon from MySQL Talk for pointing out the relevant part of the Zend Framework documentation that points out why it happens and how to fix it.