|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79872 Can't commit with pending unbuffert results
Submitted: 2020-07-18 13:46 UTC Modified: 2020-08-28 12:31 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:
Status: Open Package: PDO MySQL
PHP Version: master-Git-2020-07-18 (Git) OS: Linux
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [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] <>
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Tue Nov 24 00:01:23 2020 UTC