|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79872 Can't execute query with pending result sets
Submitted: 2020-07-18 13:46 UTC Modified: 2020-12-10 10:50 UTC
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: brian at statagroup dot com Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: master-Git-2020-07-18 (Git) OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
Solve the problem:
4 + 48 = ?
Subscribe to this entry?

 [2020-07-18 13:46 UTC] brian at statagroup dot com
Hypothetically there is no difference between what these two examples do (except for the introduction of a variable).


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



In practice, the first example fails under specific and reproducible conditions.

Test script:
I've created a full demonstration of the issue on github using PHP docker images.

$connection = new PDO('mysql:host=db;port=3306;dbname=mysql', 'root', 'example', []);

$connection->prepare('DROP TABLE IF EXISTS pdo_bug_test;')->execute();
$connection->prepare('CREATE TABLE pdo_bug_test (value1 VARCHAR(10), value2 INT);')->execute();

$sql = 'INSERT INTO pdo_bug_test (value1, value2) VALUES (1, 2);';
$sql .= 'INSERT INTO pdo_bug_test (value1, value2) VALUES (3, 4);';
$statement = $connection->prepare($sql); $statement->execute();
$result = $connection->commit();

Expected result:
I expect the two examples to work exactly the same. In practice this difference is introduced on versions of PHP which contain commit 51cdd3dc50af88461d83aec0fac1dca83400b58f .

This means 5.6 works the same for both examples. However, 7.0.23, 7.1.9, and 7.2.0 are the releases that introduce this change in the git tree.


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2020-07-18 13:49 UTC] brian at statagroup dot com
-: drakain at statagroup dot com +: brian at statagroup dot com
 [2020-07-18 13:49 UTC] brian at statagroup dot com
fixing email address
 [2020-08-28 12:31 UTC]
-Summary: Semantic Language inconsistency when using commit and 2 statements. +Summary: Can't commit with pending unbuffert results
 [2020-08-28 12:31 UTC]
Thank for reporting!  This is not related to commit 51cdd3d[1],
tough.  This commit just fixed the return value, so that
::commit() properly returns false for versions which have that

And actually, this issue is not particularly related to transactions
at all.  Consider the following modified reproduce script:

// connect and create table as before, but also set PDO::ERRMODE_WARNING

$sql = 'INSERT INTO pdo_bug_test (value1, value2) VALUES (1, 2);';
$sql .= 'INSERT INTO pdo_bug_test (value1, value2) VALUES (3, 4);';
$statement = $connection->prepare($sql); $statement->execute();

print_r($connection->query('SELECT * FROM pdo_bug_test')->fetchAll());

That raises the following warning:

Warning: PDO::query(): 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. in %s on line %d

The failing ::commit() doesn't show that warning due to bug #66528.

To make the code work as desired (without the need for buffered
queries), you'd need to call $statement->nextRowset() after
executing the multi-statement (or to destroy the PDOStatement,
what would do that implicitely; that is why the code without the
variable works).

I tend to change this ticket to doc problem.

[1] <>
 [2020-12-10 10:34 UTC]
Right, this is not a bug. You need to either unset the $statement variable, or at least call $statement->closeCursor().

I think the only thing we can do here is improve the error message. It already tries to be very explicit about how to resolve the issue, but currently only focuses on a result set that has not been fully consumed and ignores the case of multiple result sets not being consumed.

We should either make mention of both, or try to distinguish these two situations.
 [2020-12-10 10:44 UTC]
-Summary: Can't commit with pending unbuffert results +Summary: Can't execute query with pending result sets
 [2020-12-10 10:49 UTC]
Automatic comment on behalf of
Log: Fixed bug #79872 by improving error message
 [2020-12-10 10:49 UTC]
-Status: Open +Status: Closed
 [2020-12-10 10:50 UTC]
-Assigned To: +Assigned To: nikic
 [2020-12-10 10:50 UTC]
Looks like we can distinguish these cases, so now it will print the following error message:

> SQLSTATE[HY000]: General error: 2014 Cannot execute queries while there are pending result sets. Consider unsetting the previous PDOStatement or calling PDOStatement::closeCursor()

I believe that should be sufficient guidance as to what went wrong and how it can be addressed.
PHP Copyright © 2001-2023 The PHP Group
All rights reserved.
Last updated: Wed May 31 03:03:44 2023 UTC