php.net |  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
Votes:1
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
 [2020-07-18 13:46 UTC] brian at statagroup dot com
Description:
------------
Hypothetically there is no difference between what these two examples do (except for the introduction of a variable).

ex1. 

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

ex2. 

    $connection->prepare($sql)->execute();

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.

https://github.com/Incognito/php-pdo_mysql-defect-evidence





$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();

$connection->beginTransaction();
$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.


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

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] cmb@php.net
-Summary: Semantic Language inconsistency when using commit and 2 statements. +Summary: Can't commit with pending unbuffert results
 [2020-08-28 12:31 UTC] cmb@php.net
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
fix.

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

<?php
// 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] <https://github.com/php/php-src/commit/51cdd3dc50af88461d83aec0fac1dca83400b58f>
 [2020-12-10 10:34 UTC] nikic@php.net
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] nikic@php.net
-Summary: Can't commit with pending unbuffert results +Summary: Can't execute query with pending result sets
 [2020-12-10 10:49 UTC] nikic@php.net
Automatic comment on behalf of nikita.ppv@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=288581fadef58363dac4fc72ded0fae830bf603c
Log: Fixed bug #79872 by improving error message
 [2020-12-10 10:49 UTC] nikic@php.net
-Status: Open +Status: Closed
 [2020-12-10 10:50 UTC] nikic@php.net
-Assigned To: +Assigned To: nikic
 [2020-12-10 10:50 UTC] nikic@php.net
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-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 11:01:28 2024 UTC