Bug #79872 [Opn]: Can't commit with pending unbuffert results

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 commit with pending unbuffert results Status: Open Type: Bug Package: PDO MySQL Operating System: Linux PHP Version: master-Git-2020-07-18 (Git) Block user comment: N Private report: N New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [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