Fixed: ROLLBACK Doesn't Undo My Changes After CREATE TABLE in MySQL

I ran ROLLBACK, and MySQL answered Query OK, and my UPDATE was still there. A CREATE TABLE in between had quietly committed it. MySQL doesn't warn you, and the ROLLBACK has nothing to report.

What I Was Doing

I was trying out a change in a transaction, so I could take it back, on MySQL 9.7.4 in Docker. Partway through, I created a small table to keep notes in:

mysql

START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
CREATE TABLE notes (Id int PRIMARY KEY);
ROLLBACK;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;

What I Expected

The quantity back at 50, where it started.

What I Got

Output (ROLLBACK, then the SELECT)

Query OK, 0 rows affected (0.001 sec)

+----+-------------+----------------+
| Id | Sku         | QuantityOnHand |
+----+-------------+----------------+
|  3 | CBL-HDMI-2M |             49 |
+----+-------------+----------------+
1 row in set (0.001 sec)

What I Checked

The docs. MySQL's implicit commit page says: "The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement." CREATE TABLE is on the list.

Whether it needs to succeed. It doesn't. A TRUNCATE of a table that doesn't exist fails, and commits anyway:

Output (TRUNCATE TABLE __nosuchtable, then ROLLBACK and the SELECT)

ERROR 1146 (42S02): Table 'inventory.__nosuchtable' doesn't exist

+----+-------------+----------------+
| Id | Sku         | QuantityOnHand |
+----+-------------+----------------+
|  3 | CBL-HDMI-2M |             49 |
+----+-------------+----------------+
1 row in set (0.001 sec)

Why It Happens

MySQL can't roll back most changes to a table's structure, so those statements end your transaction first. By the time you run ROLLBACK, no transaction is open, and ROLLBACK succeeds at doing nothing.

The Fix

The change can't be recovered by ROLLBACK now, so put the value back by hand if you need to. Next time, do the structure change outside the transaction:

mysql

CREATE TABLE notes (Id int PRIMARY KEY);
START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
ROLLBACK;
SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;

If you need a table only for this session, CREATE TEMPORARY TABLE doesn't commit, though it has its own small catch.

After the Fix

Output (the SELECT)

+----+-------------+----------------+
| Id | Sku         | QuantityOnHand |
+----+-------------+----------------+
|  3 | CBL-HDMI-2M |             50 |
+----+-------------+----------------+
1 row in set (0.001 sec)

Common Mistakes

Mistake	What happens	Do this instead
Mixing structure changes and data changes in one transaction	The data changes are committed early	Keep them separate
Reading Query OK from ROLLBACK as "it worked"	It only means the statement ran	Check the data
Expecting a migration to roll back when it fails	Its structure changes are already permanent	Which statements commit

Troubleshooting

Symptom	Likely cause	Fix
ROLLBACK didn't undo, with no CREATE TABLE	A second START TRANSACTION, or autocommit was on	Check the statements in between
A table type with no transactions	ROLLBACK can't affect a MyISAM table	Use InnoDB

Conclusion

CREATE TABLE, ALTER TABLE, TRUNCATE and similar statements commit your open transaction, even if they fail, and ROLLBACK then has nothing to undo. Run them outside the transaction.

Additional Resources

• Statements That Cause an Implicit Commit — the full list.

Related

• Which MySQL Statements Commit a Transaction Automatically
• How to Use a Transaction in MySQL: START TRANSACTION, COMMIT and ROLLBACK
• Fixed: Warning 1751: "The creation of some temporary tables could not be rolled back" in MySQL