Bug #80949 [Opn->Csd]: The transaction is closed when dropping a table but it should not

Edit report at https://bugs.php.net/bug.php?id=80949&edit=1 ID: 80949 Updated by: nikic@php.net Reported by: lyrixx at lyrixx dot info Summary: The transaction is closed when dropping a table but it should not -Status: Open +Status: Closed Type: Bug Package: PDO MySQL Operating System: linux PHP Version: 8.0.3 -Assigned To: +Assigned To: nikic Block user comment: N Private report: N New Comment: As there was no support for changing the behavior of commit() on the mailing list, I'm going to close this issue. Previous Comments: ------------------------------------------------------------------------ [2021-04-12 12:26:28] lyrixx at lyrixx dot info For the record, I have also patched my code. Instead of: ``` $conn->exec('DROP TEMPORARY TABLE greg'); ``` I now use: ``` $conn->exec('DROP TABLE greg'); ``` As stated in the MySQL documentation, the TEMPORARY keyword does not trigger an implicit COMMIT ------------------------------------------------------------------------ [2021-04-12 12:16:27] nikic@php.net I've landed https://github.com/php/php-src/commit/8effbd1cb0654c359721aad761ac75265903983c to fix the incorrect ROLLBACK. I've also started an internals discussion on whether the want to change the commit() behavior: https://externals.io/message/114028 Keeping this report open until that discussion concludes. ------------------------------------------------------------------------ [2021-04-12 10:27:13] nikic@php.net MySQL DDL statement cause an implicit commit (see https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html), so there is indeed no transaction active at the point you call commit(). However, the ROLLBACK that PDO issues on destruction is indeed incorrect. ------------------------------------------------------------------------ [2021-04-12 10:19:41] lyrixx at lyrixx dot info Description: ------------ With PDO, when dropping a table, the driver emits a ROLLBACK, and so close the transaction. It should not. I tested it with a raw CLI connection, and it works well. MySQL Logs ``` 210412 10:17:07 77 Connect root@172.21.0.2 as anonymous on rio 77 Query START TRANSACTION 77 Query CREATE TEMPORARY TABLE greg ( source_input LONGTEXT DEFAULT NULL COLLATE utf8mb4_bin, INDEX source_input_index (source_input (12) ) ) 77 Query DELETE FROM greg 77 Query DROP TABLE greg 77 Query ROLLBACK 77 Quit ``` Note: The following code work well on PHP 7.4 Note2: To enable MySQL Logs at runtime: ``` SET GLOBAL general_log = 'ON'; SET GLOBAL general_log_file = '/var/log/mysql/mysql.log'; ``` Test script: --------------- $conn = new PDO('mysql:dbname=rio;host=database', 'root', 'rio'); $conn->beginTransaction(); $conn->exec(<<<'EOSQL' CREATE TEMPORARY TABLE greg ( source_input LONGTEXT DEFAULT NULL COLLATE utf8mb4_bin, INDEX source_input_index (source_input (12) ) ) EOSQL); var_dump($conn->inTransaction()); // true $conn->exec('DELETE FROM greg'); var_dump($conn->inTransaction()); // true $conn->exec('DROP TABLE greg'); var_dump($conn->inTransaction()); // false, but it should not $conn->commit(); // Throw an exception because there is not transaction Expected result: ---------------- bool(true) bool(true) bool(true) Actual result: -------------- bool(true) bool(true) bool(false) PDOException {#844 #message: "There is no active transaction" #code: 0 #file: "./test.php" #line: 44 +errorInfo: null trace: { ./test.php:44 { › var_dump($conn->inTransaction()); › $conn->commit(); › } } } ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=80949&edit=1