php.net |  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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: lyrixx at lyrixx dot info
New email:
PHP Version: OS:

 

 [2021-04-12 10:19 UTC] 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();
      › 
    }
  }
}


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-04-12 10:27 UTC] 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 12:16 UTC] 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 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] nikic@php.net
-Status: Open +Status: Closed -Assigned To: +Assigned To: nikic
 [2021-05-05 15:10 UTC] nikic@php.net
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-2024 The PHP Group
All rights reserved.
Last updated: Fri Dec 27 04:01:29 2024 UTC