Bug #79872 [Csd]: Can't execute query with pending result sets

Edit report at https://bugs.php.net/bug.php?id=79872&edit=1 ID: 79872 Updated by: nikic@php.net Reported by: brian at statagroup dot com Summary: Can't execute query with pending result sets Status: Closed Type: Bug Package: PDO MySQL Operating System: Linux PHP Version: master-Git-2020-07-18 (Git) -Assigned To: +Assigned To: nikic Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2020-12-10 10:49:04] 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:34:25] 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-08-28 12:31:09] 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-07-18 13:49:45] brian at statagroup dot com fixing email address ------------------------------------------------------------------------ [2020-07-18 13:46:49] 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. ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=79872&edit=1