PDO Cannot execute queries while other unbuffered queries are active

26
votes

I know this has to be a simple fix and I partially understand why I am getting this error but don't know how to fix it. I've looked over the docs but can't find a solution other than using buffered queries option. I have tried that as well but it doesn't work.

The error is: PDO Cannot execute queries while other unbuffered queries are active

The error is coming from the line where I am building the $result array.

foreach($phones as $phone)
{
    $stmt = db::getInstance()->prepare("CALL phones(:phone)");
    $stmt->bindParam(':phone', $phone, PDO::PARAM_INT, 10);
    $stmt->execute();

    $result[] = db::getInstance()->query("SELECT @phone;")->fetchAll(PDO::FETCH_ASSOC);
}
4
phpmysql
late to the party, but the whole benefit of prepared statments is that you only need to prepare them once. move your prepare out of the loop. - Sassafras_wot
Amen, @KenK. Amen. - Anthony Rutledge

4 Answers

37
votes

You need to free up your connection using the PDOStatement::closeCursor() method

http://www.php.net/manual/en/pdostatement.closecursor.php

I believe 

foreach($phones as $phone)
{
    $stmt = db::getInstance()->prepare("CALL phones(:phone)");
    $stmt->bindParam(':phone', $phone, PDO::PARAM_INT, 10);
    $stmt->execute();

    $stmt->closeCursor()

    $result[] = db::getInstance()->query("SELECT @phone;")->fetchAll(PDO::FETCH_ASSOC);
}

should do it for you

12
votes

I ran into this problem due to an error in my PDO connection clause. I was trying to change the timezone upon connecting:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8; SET time_zone = '$timezone';"

I changed it to:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8, time_zone = '$timezone';"

And it worked fine.

4
votes

I just ran into this problem myself and the problem turned out to be the use of stacked queries. The above solution did not solve the problem.

We had this query running right before the one that triggered the error:

return $this->fquery('
  SELECT @follow_id:=COALESCE(MAX(follow_id) + 1, 0) FROM sync_delete_value; 
  INSERT INTO sync_delete_value (...)
  VALUES (%d, @follow_id, %d, "%s")',
  $val1, $val2, $val3
);

Everything resumed as usual when I changed this into:

$followId = $this->fquery('
  SELECT @follow_id:=COALESCE(MAX(follow_id) + 1, 0) FROM sync_delete_value'
);
return $this->fquery('
  INSERT INTO sync_delete_value (...)
  VALUES (%d, %d, %d, "%s")',
  $val1, $followId, $val2, $val3

);

It's sorta pseudo-code but you get the point.

3
votes

If $stmt->closeCursor() does not work for you (it didn't for me), you can just unset the $stmt variable to free upp the cursor, like so: 

foreach($phones as $phone)
{
    $stmt = db::getInstance()->prepare("CALL phones(:phone)");
    $stmt->bindParam(':phone', $phone, PDO::PARAM_INT, 10);
    $stmt->execute();
    unset($stmt);

    $result[] = db::getInstance()->query("SELECT @phone;")->fetchAll(PDO::FETCH_ASSOC);
}