PDO Cannot execute queries while other unbuffered queries are active

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.