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
 [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: Thu Dec 26 13:01:30 2024 UTC