|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80949 The transaction is closed when dropping a table but it should not
Submitted: 2021-04-12 10:19 UTC Modified: 2021-05-05 15:10 UTC
From: lyrixx at lyrixx dot info Assigned: nikic (profile)
Status: Closed Package: PDO MySQL
PHP Version: 8.0.3 OS: linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Anyone can comment on a bug. Have a simpler test case? Does it work for you on a different platform? Let us know!
Just going to say 'Me too!'? Don't clutter the database with that please !
Your email address:
Solve the problem:
8 + 4 = ?
Subscribe to this entry?

 [2021-04-12 10:19 UTC] lyrixx at lyrixx dot info
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@ 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');
    source_input LONGTEXT DEFAULT NULL COLLATE `utf8mb4_bin`,
    INDEX source_input_index (source_input (12) )
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:

Actual result:
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();


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2021-04-12 10:27 UTC]
MySQL DDL statement cause an implicit commit (see, 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 12:16 UTC]
I've landed to fix the incorrect ROLLBACK.

I've also started an internals discussion on whether the want to change the commit() behavior:

Keeping this report open until that discussion concludes.
 [2021-04-12 12:26 UTC] 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-05-05 15:10 UTC]
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2021-05-05 15:10 UTC]
As there was no support for changing the behavior of commit() on the mailing list, I'm going to close this issue.
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Dec 09 11:03:34 2021 UTC