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

Question

My server runs CentOS 6.4 with MySQL 5.1.69 installed using yum with CentOS's repos, and PHP 5.4.16 installed using yum with ius's repos. Edit3 Upgraded to MySQL Server version: 5.5.31 Distributed by The IUS Community Project, and error still exists. Then changed library to mysqlnd, and seems to eliminate the error. Still, with this back and forth, need to know why this error only sometimes manifests.

When using PDO and creating the PDO object using PDO::ATTR_EMULATE_PREPARES=>false, I sometimes get the following error:

Table Name - zipcodes
Error in query:
SELECT id FROM cities WHERE name=? AND states_id=?
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.
File Name: /var/www/initial_install/build_database.php
Line: 547
Time of Error: Tuesday July 2, 2013, 5:52:48 PDT

Line 547 is the last line of:

$stmt_check_county->execute(array($data[5],$data[4]));
if(!$county_id=$stmt_check_county->fetchColumn())
{
    $stmt_counties->execute(array($data[5]));
    $county_id=db::db()->lastInsertId();
}
//$stmt_check_county->closeCursor(); //This will fix the error
$stmt_check_city->execute(array($data[3],$data[4]));

I had a similar problem several years ago, but upgraded from PHP 5.1 to PHP 5.3 (and MySQL probably was updated as well), and the problem magically went away, and now I have it with PHP 5.5.

Why does it only manifest itself when PDO::ATTR_EMULATE_PREPARES=>false, and with only alternating version of PHPs?

I've also found that closeCursor() will also fix the error. Should this always be done after every SELECT query where fetchAll() is not used? Note that the error still occurs even if the query is something like SELECT COUNT(col2) which only returns one value.

Edit By the way, this is how I create my connection. I've only recently added MYSQL_ATTR_USE_BUFFERED_QUERY=>true, however, it doesn't cure the error. Also, the following script could be used as is to create the error.

function sql_error($e,$sql=NULL){return('<h1>Error in query:</h1><p>'.$sql.'</p><p>'.$e->getMessage().'</p><p>File Name: '.$e->getFile().' Line: '.$e->getLine().'</p>');}

class db {
    private static $instance = NULL;
    private function __construct() {}   //Make private
    private function __clone(){}   //Make private
    public static function db() //Get instance of DB
    {
        if (!self::$instance)
        {
            //try{self::$instance = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
            try{self::$instance = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_EMULATE_PREPARES=>false,PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=>true,PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
            //try{self::$instance = new PDO("mysql:host=localhost;dbname=myDB;charset=utf8",'myUsername','myPassword',array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC));}
            catch(PDOException $e){echo(sql_error($e));}
        }
        return self::$instance;
    }
}

$row=array(
    'zipcodes_id'=>'55555',
    'cities_id'=>123
);
$data=array($row,$row,$row,$row);

$sql = 'CREATE TEMPORARY TABLE temp1(temp_id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (temp_id) )';
db::db()->exec($sql);

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

$sql ='SELECT temp_id FROM temp1';
$stmt2 = db::db()->prepare($sql);

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

Answer 1

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.

Answer 2

I am hoping for a better answer than the following. While some of these solutions might "fix" the problem, they don't answer the original question regarding what causes this error.

1. Set PDO::ATTR_EMULATE_PREPARES=>true (I don't wish to do this)
2. Set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY (didn't work for me)
3. Use PDOStatement::fetchAll() (not always desirable)
4. Use $stmt->closeCursor() after each $stmt->fetch() (this mostly worked, however, I still had several cases where it didn't)
5. Change PHP MySQL library from php-mysql to php-mysqlnd (probably what I will do if no better answer)

Answer 3

I have almost same problem. My first query after connection to db return empty result and drop this error. Enabling buffer doesn't help.

My connection code was:

try { 
    $DBH = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password, 
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8; SET NAMES utf8",
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM));
} 
catch(PDOException $e) { echo $e->getMessage(); }

Solution in my way was to remove initial command:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET CHARACTER SET utf8; SET NAMES utf8"

Here is a correct code:

try { 
    $DBH = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password, 
    array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NUM));
} 
catch(PDOException $e) { echo $e->getMessage(); }

And MYSQL_ATTR_USE_BUFFERED_QUERY is not forced to true. It's set as default.

Answer 4

I also experienced this problem today and noticed that I put wrong SQL statement (SELECT) into PDO's exec() method. Then I came to a conclusion that we can only put write (INSERT, UPDATE, DELETE) SQL statements instead of read (SELECT) ones to the method.

Answer 5

!!! WARNING !!!

This can also happen if you are trying to fetch a non SELECT query (Eg - UPDATE/INSERT/ALTER/CREATE)

Answer 6

if anybody is here, with error while creating tables
this also happens if you try to execute create 2 tables in single query;
this error was thrown when i fired below query;

$q24="create table if not exists table1 like template1;create table if not exists table2 like template2;";
$s24=$link->prepare($q24);
$s24->execute();

seems tables are to be created separately;

$q1="create table if not exists table1 like template1;";
$s1=$link->prepare($q1);
$s1->execute();
//and
$q2="create table if not exists table2 like template2;";
$s2=$link->prepare($q2);
$s2->execute();

Answer 7

I had the same problem, I was sending results to another function mid loop. Quick fix was, save all results in an array (like Bill stated, if it's too large you have other issues to worry about), after collecting the data, I ran a separate loop to call the function one at a time.

Also, PDO::MYSQL_ATTR_USE_BUFFERED_QUERY did not work for me.

Answer 8

I had the same problem and solved it by removing all initial requests related to the character set. so I started from

$con = new \PDO(self::getDriver() . ":host=" . self::getHost() . ":".self::getPort()."; dbname=" . self::getName() . ";charset=utf8", self::getUser(), self::getPassword(), array( \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8;SET SESSION time_zone ='+01:00'"));

to

$con = new \PDO(self::getDriver() . ":host=" . self::getHost() . ":".self::getPort()."; dbname=" . self::getName() . ";charset=utf8", self::getUser(), self::getPassword(), array( \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,\PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION time_zone ='+01:00'"));

so I removed the command SET NAMES utf8;

Answer 9

I got this error when I accidentally called execute twice, one explicitly and one that was hidden in another call (PDO class). After removing the first execute, the error was gone.

Answer 10

The main reason behind this error is that MySQL is trying to run 'exec' instead of 'execute' and vice versa.

There are two PDO statements that are there to execute queries PDO::exec() and PDO::execute(), both are not the same.

PDO::exec() is designed to execute commands and queries that do not produce a result set.

Ex: SET, UPDATE, INSERT, DELETE etc.

PDO::execute() is designed to execute commands and queries that produce a result set.

Ex: SELECT, CALL, SHOW, OPTIMIZE, EXPLAIN etc.

If you use these commands in wrong place, your will be ended up with this error.

Solution:

Be careful where to use PDO::exec() and PDO::execute()

In my case for Laravel I changed my query from

DB::select("DELETE FROM " . env('DB_PREFIX') . 'products WHERE product_id = ' . $product->id); // Internally Laravel will run PDO::execute() 'select' method

to

DB::table('product_currency')->where('product_id', $product->id)->delete(); // Internally Laravel will run PDO::exec()

Hope this gives some more clarification!