Causes of MySQL Error 2014 Cannot Execute Queries While Other Unbuffered Queries Are Active

Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active

The MySQL client protocol doesn't allow more than one query to be "in progress." That is, you've executed a query and you've fetched some of the results, but not all -- then you try to execute a second query. If the first query still has rows to return, the second query gets an error.

Client libraries get around this by fetching all the rows of the first query implicitly upon first fetch, and then subsequent fetches simply iterate over the internally cached results. This gives them the opportunity to close the cursor (as far as the MySQL server is concerned). This is the "buffered query." This works the same as using fetchAll(), in that both cases must allocate enough memory in the PHP client to hold the full result set.

The difference is that a buffered query holds the result in the MySQL client library, so PHP can't access the rows until you fetch() each row sequentially. Whereas fetchAll() immediately populates a PHP array for all the results, allowing you access any random row.

The chief reason not to use fetchAll() is that a result might be too large to fit in your PHP memory_limit. But it appears your query results have just one row anyway, so that shouldn't be a problem.

You can closeCursor() to "abandon" a result before you've fetched the last row. The MySQL server gets notified that it can discard that result on the server side, and then you can execute another query. You shouldn't closeCursor() until you're done fetching a given result set.

Also: I notice you're executing your $stmt2 over and over inside the loop, but it will return the same result each time. On the principle of moving loop-invariant code out of the loop, you should have executed this once before starting the loop, and saved the result in a PHP variable. So regardless of using buffered queries or fetchAll(), there's no need for you to nest your queries.

So I would recommend writing your code this way:

$sql ='SELECT temp_id FROM temp1';
$stmt2 = db::db()->prepare($sql);
$stmt2->execute();
$rs2 = $stmt2->fetchAll(PDO::FETCH_ASSOC);
$stmt2->closeCursor();

$sql='SELECT COUNT(*) AS valid FROM cities_has_zipcodes 
      WHERE cities_id=:cities_id AND zipcodes_id=:zipcodes_id';
$stmt1 = db::db()->prepare($sql);

foreach($data AS $row)
{
    try
    {
        $stmt1->execute($row);
        $rs1 = $stmt1->fetchAll(PDO::FETCH_ASSOC);
        $stmt1->closeCursor();
        syslog(LOG_INFO,'$rs1: '.print_r($rs1[0],1).' '.rand());
        syslog(LOG_INFO,'$rs2: '.print_r($rs2[0],1).' '.rand());
    }
    catch(PDOException $e){echo(sql_error($e));}            
}

Note I also used named parameters instead of positional parameters, which makes it simpler to pass $row as the array of parameter values. If the keys of the array match the parameter names, you can just pass the array. In older versions of PHP you had to include the : prefix in the array keys, but you don't need that anymore.

You should use mysqlnd anyway. It has more features, it's more memory-efficient, and its license is compatible with PHP.

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active

Finally I got the answer:

$statement = $connection->prepare($query);

$statement->execute();

$results = $statement->fetchAll();

$statement->closeCursor();

PDO General error: 2014 Cannot execute queries while other unbuffered queries are active when trying to LOCK TABLES

You should use exec() instead query(). exec() does not expect any return values, which is exactly what LOCK TABLES needs.

$pdo->exec("LOCK TABLES Inspections WRITE");

Different solution to `Cannot execute queries while other unbuffered queries are active`

And here's how my constructor looked:

$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $db . ';port=' . $port, $user, $pass, 
                array(
                  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8;SET SESSION time_zone="system"', 
                  PDO::MYSQL_ATTR_LOCAL_INFILE => true
                ));

I changed it to

$pdo = new PDO('mysql:host=' . $host . ';dbname=' . $db . ';port=' . $port, $user, $pass, 
                array(
                  PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8, SESSION time_zone="system"',     
                  PDO::MYSQL_ATTR_LOCAL_INFILE => true
                ));

and it does work now.

I am posting my solution here because all other places have "wrong" ones.

So the difference is instead of having

'SET NAMES utf8;SET SESSION time_zone="system"'

in PDO::MYSQL_ATTR_INIT_COMMAND I have:

'SET NAMES utf8, SESSION time_zone="system"'

and everything's fine now.

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while there are pending result sets

I used $stmt->closeCursor(); after fetching data from GetAllCategories() stored procedure to solve this error.

$query = "CALL GetAllCategories()";
$stmt = $pdo->prepare($query);
$stmt->execute();
$categories = $stmt->fetchAll();
$stmt->closeCursor();

Cannot execute queries while other unbuffered queries are active

I've used $query->closeCursor();, as noted in some of the linked SO questions, for every select query executed on this page to reach working status.

This is less than ideal and I wish we could determine a real cause and solution for this problem. Unfortunately I don't have server config access to check some of the other solutions.

This also worked, but some comments state that it didn't work for them:

$link->setAttribute (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

---

Related Topics