ZF-5576: Can't execute subsequent query after multiple queries from same Pdo_Mysql->exec() call

Description


        include 'Zend/Loader.php';
        Zend_Loader::registerAutoload();

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

$params = array(
    'host'           => 'localhost',
    'username'       => 'test',
    'password'       => 'xxxxxx',
    'dbname'         => 'test_db',
    'driver_options' => $pdoParams
);

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

        $sql0="DROP TABLE IF EXISTS someTable";
        $sql1="CREATE TABLE someTable (id INT);";
        $sql2="INSERT INTO someTable (id) VALUES (1)";
        $db->getConnection()->exec($sql0);
        $db->getConnection()->exec($sql1);
        $db->getConnection()->exec($sql2);

The above code works fine. However, attempting to execute more than one query in each exec() call does not:


        $sql1="DROP TABLE IF EXISTS someTable; CREATE TABLE someTable (id INT);";
        $sql2="INSERT INTO someTable (id) VALUES (1)";
        $db->getConnection()->exec($sql1);
        $db->getConnection()->exec($sql2);

Execution of $sql2 fails with 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.

Note that


        $sql1="DROP TABLE IF EXISTS someTable; CREATE TABLE someTable (id INT);";
        $db->getConnection()->exec($sql1);

works fine on it's own - it's the subsequent call that fails.

This is more irritating than it appears, as it makes loading query data from files more fiddly - for instance in my unit test setUp() I have something like


      $sql = file_get_contents('createTestSchema.sql');
      $db->getConnection()->exec($sql);

where createTestSchema.sql consists of a number of CREATE TABLE & INSERT statements. This then basically kills the $db object - it can't be used for any other queries without giving this error.

Assuming it's linked to the similar stored procedure problem, there's some discussion here that it's specific to FreeBSD; I'm certainly running FreeBSD but haven't tested it elsewhere.

Comments

I've got absolutely the same bug on my home machine: Windows Vista HP, mysql 5.0.45, php 2.5.4 (and 2.5.5 in cgi mode fails too), so It's not because of FreeBSD. Must be something wrong with PDO, but i couldn't find an answer.

Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".

Feel free to re-open and provide a patch if you want to fix this issue.