Php – I get 2014 Cannot execute queries while other unbuffered queries are active when doing exec with PDO

pdoPHPtransactionsunbuffered-queries

I am doing a PDO::exec command on multiple updates:

$MyPdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY,true);
$MyPdo->exec("update t1 set f1=1;update t2 set f1=2");

I am doing it inside a transaction, and I keep getting the following error:

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.

those are the only query/ies

Best Answer

You shouldn't execute the statements as a single query. Execute them one at a time:

$MyPdo->exec("update t1 set f1=1");
$MyPdo->exec("update t2 set f1=2");

Related Solutions

Mysql – how to resolve General error: 2014 Cannot execute queries while other unbuffered queries are active. using PDO connection

This is a bit of a poor feature of PDO that's not well documented. The closeCursor method doesn't work when the statement has executed a stored procedure. You need to use the nextRowSet method. Here's what I use

            while($sth->nextRowSet())
        {
            $sth->fetchAll();
        }
        $sth->closeCursor();

Php – causing PDO error Cannot execute queries while other unbuffered queries are active

Oddly enough, the PHP packages provided by Ubuntu are not compiled with the Mysql native driver, but with the old libmysqlclient instead (tested on Ubuntu 13.10 with default packages):

<?php
echo $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION); // prints "5.5.35", i.e MySQL version
// prints "mysqlnd (...)" when using mysqlnd

Your very test case ("Edit 4", with setAttribute(MYSQL_ATTR_USE_BUFFERED_QUERY, true)) works as expected with PHP 5.5.3 manually compiled with mysqlnd with:

./configure --with-pdo-mysql=mysqlnd # default driver since PHP v5.4

... but fails with:

bash> ./configure --with-pdo-mysql=/usr/bin/mysql_config

It quite odd that it fails only if the first statement is executed twice; this must be a bug in the libmysqlclient driver.

Both drivers fail as expected when MYSQL_ATTR_USE_BUFFERED_QUERY is false. Your Common Sense already demonstrated why this is expected behaviour, regardless of the number of rows in the result set.

Mike found out that the current workaround is installing the php5-mysqlnd package instead of the Canonical-recommended php5-mysql.

Best Answer

Related Solutions

Mysql – how to resolve General error: 2014 Cannot execute queries while other unbuffered queries are active. using PDO connection

Php – causing PDO error Cannot execute queries while other unbuffered queries are active

Related Topic