What is causing PDO error Cannot execute queries while other unbuffered queries are active?

13,512

Solution 1

Oddly enough, the PHP packages provided by Ubuntu are not compiled with the Mysql native driver, but with the old libmysqlclient instead (tested on Ubuntu 13.10 with default packages):

<?php
echo $dbh->getAttribute(PDO::ATTR_CLIENT_VERSION); // prints "5.5.35", i.e MySQL version
// prints "mysqlnd (...)" when using mysqlnd

Your very test case ("Edit 4", with setAttribute(MYSQL_ATTR_USE_BUFFERED_QUERY, true)) works as expected with PHP 5.5.3 manually compiled with mysqlnd with:

./configure --with-pdo-mysql=mysqlnd # default driver since PHP v5.4

... but fails with:

bash> ./configure --with-pdo-mysql=/usr/bin/mysql_config

It quite odd that it fails only if the first statement is executed twice; this must be a bug in the libmysqlclient driver.

Both drivers fail as expected when MYSQL_ATTR_USE_BUFFERED_QUERY is false. Your Common Sense already demonstrated why this is expected behaviour, regardless of the number of rows in the result set.

Mike found out that the current workaround is installing the php5-mysqlnd package instead of the Canonical-recommended php5-mysql.

Solution 2

This is not necessarily the answer to this question, but this may help somebody in the future.

I came across exactly the same error and it took hours to discover what was wrong. It turned out it was just a extremely minor syntax issue all along. If you're not actually using any buffering, but still have this error, like I did, this could be your issue - so check your code.

I was doing my normal database queries when I came across this error -- not purposely using any buffering techniques -- so I highly doubted it had anything to do with buffering. I read every SO question about it and looked deeper in to it.

This was my STUPID syntax issue:

$SQL = "UPDATE articles SET
            topicID = :topic;    <-------- semicolon - woops!
            heading = :heading,
            subheading = :subheading,
            keywords = :keywords,
            rawContent = :rawContent,
            content = :content,
            ...
            ...

This resulted in me getting this buffering error. I fixed the code and it went away. What was most annoying, was the fact the PDO error was pointing at another query, the next query, but that query was in a function elsewhere in the code, and that through me well off course for a while!

Solution 3

It seems that you have PDO::MYSQL_ATTR_USE_BUFFERED_QUERY set to FALSE.

And in such a case it is obligatory to make sure that there are no more rows pending for the retrieval. To do so one to run fetch() one extra time, as it seems that fetch() returning false is "releasing" non-buffered resultset somehow. Without such extra call non-buffered resultset remains locked and causing "Commands out of sync" error

Share:
13,512
Mike
Author by

Mike

Canadian born, but left the cold winters to live in the perfect weather of Costa Rica. Currently working as general manager of a hotel in San Jose, Costa Rica.

Updated on June 06, 2022

Comments

  • Mike
    Mike almost 2 years

    I have the following code:

    $dbh = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $dbh->prepare("SELECT 1");
    $stmt->execute();
    $result = $stmt->fetch();
    
    $stmt->execute();
    $result = $stmt->fetch();
    
    $stmt = $dbh->prepare("SELECT 1");
    $stmt->execute();
    $result = $stmt->fetch();
    

    However, for some reason I get the following error when executing the second prepared statement:

    Fatal error: Uncaught exception 'PDOException' with message '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.'

    I know what this error means and how to fix it (either doing unset($stmt); or $stmt->closeCursor();), so I am not looking for a solution of how to get it to work. From what I understand it is usually caused by doing fetch instead of fetchAll and not fetching all the results. However in this case, there is only one result and it is being fetched. Also, if I only execute the first prepared statement once, the error does not occur. It only happens when the first statement is executed twice. It also only happens when PDO::ATTR_EMULATE_PREPARES is false.

    So my question is, what is causing the above error to occur in this case? It doesn't appear to be any different than any other query I've ever executed.

    I have tested this on two Ubuntu 13.10 servers, Debian and CentOS and all produce the same error using the default packages.

    Edit:

    To answer Ryan Vincent's comment, I am a complete mysqli noob, but I believe what I have below is roughly equivalent to the above example. Please correct me if I'm wrong. However it produces no errors, so it would appear to be a PDO-only error:

    $mysqli = new mysqli($host, $user, $pass, $dbname);
    if ($mysqli->connect_errno) {
        die("Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error);
    }
    
    if (!($stmt = $mysqli->prepare("SELECT 1"))) {
         die("Prepare 1 failed: (" . $mysqli->errno . ") " . $mysqli->error);
    }
    
    if (!$stmt->execute()) {
        die("Execute 1 failed: (" . $stmt->errno . ") " . $stmt->error);
    }
    $stmt->store_result();
    $stmt->bind_result($col1);
    $stmt->fetch();
    
    if (!$stmt->execute()) {
        die("Execute 2 failed: (" . $stmt->errno . ") " . $stmt->error);
    }
    $stmt->store_result();
    $stmt->bind_result($col1);
    $stmt->fetch();
    
    if (!($stmt = $mysqli->prepare("SELECT 1"))) {
        // The following line is what fails in PDO
        die("Prepare 2 failed: (" . $mysqli->errno . ") " . $mysqli->error);
    }
    
    if (!$stmt->execute()) {
        die("Execute 3 failed: (" . $stmt->errno . ") " . $stmt->error);
    }
    $stmt->store_result();
    $stmt->bind_result($col1);
    $stmt->fetch();
    
  • Ryan Vincent
    Ryan Vincent almost 10 years
    If you fetch one row at a time from the 'result set' then Mysql cannot close the result set until you fetch the last row from it. Which means you need to fetch twice even if only one row is returned from the query as you cannot tell there are no more rows until you try and read them.
  • Your Common Sense
    Your Common Sense almost 10 years
    @RyanVincent yes, but only if result weren't buffered. If it was, only one fetch is enough
  • Ryan Vincent
    Ryan Vincent almost 10 years
    sorry, i am 'hard of thinking' currently.
  • Your Common Sense
    Your Common Sense almost 10 years
    Just try it and see, if you don't like the sound.
  • Ryan Vincent
    Ryan Vincent almost 10 years
    I was wrong! I have read the instructions for the 'mysqli' and 'PDO' API for the version of 'mysql' that i use here (5.5.16 on windows XP) and you are quite correct
  • Mike
    Mike almost 10 years
    Actually, it works for mysqli (see my edit 5), but not PDO. Is it possible that PDO uses libmysqlclient and mysqli uses mysqlnd on the same server?
  • RandomSeed
    RandomSeed almost 10 years
    Err... I meant "libmysqlclient" at the end (fixed). Yes, you can very well compile your PHP with ./configure --with-pdo-mysql=/usr/bin/mysql_config --with-mysqli=mysqlnd. Let me test how this behaves.
  • Mike
    Mike almost 10 years
    When I do mysqli_get_client_info() it also returns 5.5.35 (without mentioning mysqlnd) so it still looks like if there's a bug it's somewhere in PDO, not in libmysqlclient.
  • RandomSeed
    RandomSeed almost 10 years
    Yes, I just realised the same driver is used for all API's (it would be quite weird otherwise). I'd say there is a bug either in PDO or libmysqlclient, because PDO works just as expected with mysqlnd. I would file a bug at PHP, perhaps, even though I don't think it will be fixed, the old driver being kind of deprecated. Alternatively, let's suggest the Ubuntu people to update their compilation scripts.
  • Mike
    Mike almost 10 years
    The manual says "The mysql extension, the mysqli extension and the PDO MySQL driver can each be individually configured to use either libmysqlclient or mysqlnd", so there must be a way. Not that it really matters because it appears I'm using libmysqlclient for both. And I actually did file a bug report a few days ago. Feel free to add to it.
  • Mike
    Mike almost 10 years
    And about recommending to the Ubuntu people to update their compilation scripts, I agree completely. It appears Debian as well chose to use the old driver as well even though PHP is 5.4.4.
  • RandomSeed
    RandomSeed almost 10 years
    "it would be quite weird if the Ubuntu guys had compiled PHP with two different drivers" ;)
  • Your Common Sense
    Your Common Sense almost 10 years
    @Mike to test for mysqlnd, you have to run phpinfo() and watch pdo_mysql section
  • Your Common Sense
    Your Common Sense almost 10 years
    btw, to me 5.5.53 is apparently looks like a mysql wersion, irrelevant to client driver
  • Mike
    Mike almost 10 years
    @RandomSeed I just realized that there is a php5-mysqlnd package for Ubuntu, however php5-mysql (which uses libmysqlclient) has the little "recommended by Canonical" icon in the package managers. Installing it gets rid of the error, as you found.
  • Mike
    Mike almost 10 years
    @YourCommonSense I think RandomSeed was right about the output to PDO::ATTR_CLIENT_VERSION. When using libmysqlclient it appears to output the MySQL version, but when using mysqlnd it produces something like "mysqlnd 5.0.11-dev - 20120503 - $Id: 40933630edef551dfaca71298a83fad8d03d62d4 $".
  • Your Common Sense
    Your Common Sense almost 10 years
    @Mike thanks, I had to test it myself before commenting. Well, it seems we have found the cause?
  • RandomSeed
    RandomSeed almost 10 years
    @Mike Nice catch with the php5-mysqlnd package. Having found the cause, I am still looking forward to seeing how the PHP team will respond.
  • Your Common Sense
    Your Common Sense almost 10 years
    @RandomSeed I doubt there will be any response other than "libmysql is obsoleted, use mysqlnd". I'd rather ask Ubuntu guys for making mysqlnd default choice, like they have it in Fedora.
  • Mike
    Mike almost 10 years
    @YourCommonSense Possibly they could confirm the bug exists but set it as "won't fix". At least then it may (further) dissuade people from using libmysql binaries by default in their distributions.
  • Mike
    Mike almost 10 years
    By the way, Debian appears to recommend php5-mysql instead of php5-mysqlnd as well.