John Davidson

Message:

For example

$conn = new PDO("mysql:host=127.0.0.1;dbname=test", 'root', '123456');

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$conn->beginTransaction();

try {

    $sql = "CREATE TABLE IF NOT EXISTS test2 (

  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,

  `text` varchar(32) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

    $conn->exec($sql);

    $conn->commit();

} catch (\Exception $e) {

    $conn->rollBack();

}

It works fine in PHP 7.4, but it does not work in PHP 8.0 and throws an exception on line $conn->commit();:

PDOException: There is no active transaction

---

My Answer:
The transaction that contained a DDL (Data Definition Language) statement may have thrown an exception because DDL statements such as CREATE, ALTER, and DROP cannot be rolled back in a transaction. When a DDL statement is executed within a transaction, it immediately commits the changes to the database schema, which cannot be undone.

In the example provided, the code snippet creates a new PDO object for database connection. If a DDL statement is executed within a transaction using this connection, it will throw an exception because the DDL statement cannot be rolled back.

To avoid this issue, DDL statements should be executed outside of a transaction or in a separate transaction that does not include other statements that need to be rolled back.