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

by

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.

More Related Contents:

  1. PDOException SQLSTATE[HY000] [2002] No such file or directory
  2. How to get numeric types from MySQL using PDO?
  3. How can I use PDO to fetch a results array in PHP?
  4. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  5. mysql PDO how to bind LIKE
  6. Use bound parameter multiple times
  7. What is the difference between MySQL, MySQLi and PDO? [closed]
  8. how safe are PDO prepared statements
  9. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  10. PHP PDO – Num Rows
  11. How to solve General error: 2006 MySQL server has gone away
  12. Replacing mysql_* functions with PDO and prepared statements
  13. Understanding pdo mysql transactions
  14. Calling stored procedure with Out parameter using PDO
  15. When *not* to use prepared statements?
  16. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  17. Setting a connect timeout with PDO
  18. In PHP with PDO, how to check the final SQL parametrized query? [duplicate]
  19. What is the best way to insert multiple rows in PHP PDO MYSQL?
  20. PDO MySQL: Insert multiple rows in one query
  21. How to bind LIKE values using the PDO extension?
  22. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  23. PDO::rowCount VS COUNT(*)
  24. PHP 7 RC3: How to install missing MySQL PDO
  25. How to escape single-quote (apostrophe) in string using php
  26. PHP Laravel: No connection could be made because the target machine actively refused it
  27. php, mysql – Too many connections to database error
  28. PDO bindParam into one statement?
  29. What is the PDO equivalent of function mysql_real_escape_string?
  30. Installing PDO driver on MySQL Linux server

Leave a Comment