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

Question

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:

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

Answer 1

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");

Answer 2

I encountered the same problem, and haven't found any solution:

$pdo->exec('update....;update...;')
$pdo->exec('update....;update...;')

Before php 5.3 you could close connection by destroy $pdo, in php 5.3+, you could use $pdo->query() to execute multi-query, take care that $pdo->exec() finished does not mean the sql server finished the execution:

$cnt=1000;
$sql='';
$j=0;
for ($i=0;$i<$cnt;++$i)
    {++$j;
     $sql.="update sem_UploadKeywordQueue set ProductCount='{$i}' where ID='{$i}';";
     if ($j==100)
        {$pdo=new PDO('mysql:host=domain.com;port=3306;dbname=db', "user", "pass");
         $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $pdo->exec($sql);
         $pdo=null;
         $sql='';
         $j=0;
        }
    }
if ($sql)
   {$pdo->exec($sql);
   }

for diyism