With PDO on MySQL in PHP 8, why does rollBack() throw 'There is no active transaction' after an ALTER TABLE ran inside the transaction?
answer
- DDL on MySQL ends the transaction
- implicit COMMIT before the ALTER
- PHP 8.0: inTransaction() asks the server
- commit()/rollBack() check that state first
- guard with inTransaction(), rethrow original
basics
~20 sMySQL implicitly commits the open transaction when DDL such as ALTER TABLE runs. Since PHP 8.0, pdo_mysql reads the server's real state, so inTransaction() is false and rollBack() throws; the earlier writes are already committed.
solid answer
~40 sOn MySQL, `CREATE`, `ALTER` and `DROP TABLE` cause an **implicit commit**: everything done so far is committed and the connection is left outside any transaction. Since PHP 8.0, `pdo_mysql` answers `inTransaction()` from the server's status instead of PDO's own flag, and `commit()` and `rollBack()` check that state first, so both throw `PDOException` "There is no active transaction" regardless of the error mode. Before 8.0 the call appeared to succeed while undoing nothing. The data written before the DDL cannot be undone either way. The fix is to keep DDL out of the transaction, run schema changes separately and idempotently, and guard `rollBack()` with `inTransaction()` so this exception does not replace the real error.
code
php · 17 lines<?php
declare(strict_types=1);
// Schema change first, outside any transaction (MySQL would commit implicitly).
$pdo->exec('ALTER TABLE loyalty_accounts ADD COLUMN tier VARCHAR(16) NULL');
$pdo->beginTransaction();
try {
$pdo->exec("UPDATE loyalty_accounts SET tier = 'gold' WHERE points >= 10000");
$pdo->exec("UPDATE loyalty_accounts SET tier = 'basic' WHERE tier IS NULL");
$pdo->commit();
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $e;
}go deeper
Recall that on MySQL a CREATE, ALTER or DROP TABLE inside a transaction commits everything done before it.
Explain the PHP 8.0 change: inTransaction() now reflects the server, so commit() and rollBack() throw once an implicit commit has ended the transaction.
Diagnose a half-applied migration from the no-active-transaction exception, recover it, and restructure the script so DDL runs outside the transaction and each step can be re-run.
Decide how the team's migration tooling handles databases without transactional DDL: per-step idempotence and forward fixes rather than relying on rollback.
## Implicit commits Some databases cannot run a schema change inside a transaction. **MySQL** is the one PHP developers meet most: statements such as `CREATE TABLE`, `ALTER TABLE` and `DROP TABLE` cause an **implicit commit**. The server commits everything the transaction has done so far, runs the DDL (data definition language) statement, and leaves the connection with **no open transaction**. Rows changed before the DDL are now permanent; there is nothing left for `rollBack()` to undo. The PHP manual warns about this on `PDO::beginTransaction()`, `PDO::commit()` and its transactions chapter, naming MySQL and Oracle as examples. ## What changed in PHP 8.0 Before PHP 8.0, PDO kept its own flag: `beginTransaction()` set it, `commit()` and `rollBack()` cleared it. The flag knew nothing about implicit commits, so after an `ALTER TABLE` PDO still believed a transaction was open. A later `rollBack()` appeared to succeed while undoing nothing, and the code never learned that its data changes had already been committed. Since **PHP 8.0**, the `pdo_mysql` driver answers `inTransaction()` from the **server's own status** (the "in transaction" bit MySQL reports with each response). The migration guide states it directly: after a query subject to implicit commit, `inTransaction()` returns `false`. Because `commit()` and `rollBack()` check that same state first, both now throw: ``` PDOException: There is no active transaction ``` That exception is thrown whatever `PDO::ATTR_ERRMODE` is set to. ## How it bites in practice Picture a one-off script that backfills a `tier` column for loyalty customers: 1. `beginTransaction()` 2. `UPDATE loyalty_accounts SET points = points + 100 WHERE ...` (the bonus) 3. `ALTER TABLE loyalty_accounts ADD COLUMN tier ...` — implicit commit here 4. `UPDATE loyalty_accounts SET tier = ...` — fails on a bad value 5. `catch` calls `rollBack()` Step 5 throws "There is no active transaction". Three things went wrong at once: - The bonus from step 2 is **already committed** and cannot be taken back. - Every statement after the `ALTER TABLE` ran in **autocommit mode**, so any that succeeded were committed on their own, outside the rollback's reach. - The unguarded `rollBack()` threw a new `PDOException` from inside the `catch`, which **replaced** the step-4 exception that explained the real problem. ## How to write it safely - **Keep DDL out of transactions on MySQL.** Run the schema change on its own, before `beginTransaction()`, then do the data changes inside the transaction. - **Make each step re-runnable.** A migration that cannot be rolled back as a whole must be safe to run again after a partial failure, for example by skipping rows already updated. - **Guard the rollback:** `if ($pdo->inTransaction()) { $pdo->rollBack(); }`, then rethrow the original exception, so the real cause is not masked. - **Assert the state when it matters.** Checking `inTransaction()` right before `commit()` turns a silent assumption into a clear failure in a test. ## Other databases | Database | DDL inside a transaction | |---|---| | MySQL | implicit commit; `inTransaction()` is `false` afterwards (PHP 8.0+) | | Oracle | implicit commit, per the PHP manual | | PostgreSQL, SQLite | most DDL runs inside the transaction and can be rolled back | The same PHP code therefore behaves differently per driver. A test suite that runs on SQLite may pass a migration that mixes DDL and data changes, and the same code then commits half its work on MySQL. ## Recognising it after the fact The symptoms are distinctive once you know them: - A `PDOException` with the message "There is no active transaction" coming from a `rollBack()` or `commit()` call, usually inside a `catch` block. - Data in a state the code believed impossible: the first half of a multi-step change applied, the second half missing. - The original error, the one that entered the `catch`, absent from the logs because the second exception replaced it. - A script that behaved "correctly" on PHP 7.4 starting to fail after an upgrade to PHP 8 — the upgrade exposed a partial commit that had always been happening. Recovery is manual: inspect which steps landed, repair the data with a forward fix, and restructure the script before rerunning it. ## What PDO cannot do for you PDO does not warn before a DDL statement and cannot turn an implicit commit back into an open transaction. Its only signal is after the fact: `inTransaction()` turning `false`, and `commit()` or `rollBack()` throwing. The fix is structural — order the statements so no DDL lands between `beginTransaction()` and `commit()` on a database that commits implicitly.
- How would the same buggy script have behaved on PHP 7.4?PDO tracked the transaction with its own flag, which an implicit commit did not clear. `rollBack()` therefore appeared to succeed and returned normally, even though the server had nothing to roll back and the earlier writes were already permanent. The script looked correct while committing half its work, which is why the PHP 8.0 change surfaces a real bug rather than creating one.
- Why does the unguarded rollBack() make the incident harder to diagnose?The `catch` was entered because of a real failure, say a constraint violation. When `rollBack()` then throws "There is no active transaction", that new `PDOException` propagates instead of the original, so the log shows a transaction-state error and hides the constraint problem. Guarding with `inTransaction()` and rethrowing the caught exception keeps the real cause visible.
saying these in an interview costs you the question
- Assuming rollBack() undoes an ALTER TABLE on MySQL along with the data changes.
- Believing only the DDL is committed and the earlier UPDATE is still pending.
- Treating 'There is no active transaction' as a PHP 8 bug to silence.
- Thinking PDO::ATTR_AUTOCOMMIT set to false stops MySQL's implicit commit.
- Assuming a migration that passes on SQLite behaves the same on MySQL.