Fixed: Warning 1751: "The creation of some temporary tables could not be rolled back" in MySQL

A ROLLBACK came back with Query OK, 0 rows affected, 1 warning, and the warning was The creation of some temporary tables could not be rolled back. Your data was rolled back correctly, and only the temporary table stayed. Here's how to see it and avoid it.

What I Was Doing

I was working in a transaction on MySQL 9.7.4 in Docker, and created a temporary table for some working data:

mysql

START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
CREATE TEMPORARY TABLE scratch (Id int PRIMARY KEY);
ROLLBACK;

What I Expected

Query OK, 0 rows affected, with everything undone.

What I Got

Output (the ROLLBACK)

Query OK, 0 rows affected, 1 warning (0.003 sec)

mysql

SHOW WARNINGS;

Output

+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1751 | The creation of some temporary tables could not be rolled back. |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.000 sec)

What I Checked

Whether my data was rolled back.

mysql

SELECT Id, Sku, QuantityOnHand FROM products WHERE Id = 3;

Output

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

Yes: 50. Only the table is left, and DROP TEMPORARY TABLE scratch; removes it.

The docs. The implicit commit page says of CREATE TEMPORARY TABLE: "although no implicit commit occurs, neither can the statement be rolled back". And: "if you use CREATE TEMPORARY TABLE and then roll back the transaction, the table remains in existence."

Why It Happens

A temporary table's creation can't be undone, and it also doesn't commit your transaction the way CREATE TABLE does. So a ROLLBACK undoes everything else, and warns you about the one thing it couldn't undo.

The Fix

Create the temporary table before the transaction starts. Rows in it are still rolled back with the transaction, because they're ordinary changes:

mysql

CREATE TEMPORARY TABLE scratch (Id int PRIMARY KEY);
START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
INSERT INTO scratch VALUES (1);
ROLLBACK;

After the Fix

Output (the ROLLBACK, then the SELECT)

Query OK, 0 rows affected (0.004 sec)

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

No warning, and the data's back.

Common Mistakes

Mistake	What happens	Do this instead
Reading the warning as a failed rollback	Your data was rolled back	Read SHOW WARNINGS, and check the data
Running the same transaction again	ERROR 1050: the table already exists	Drop it, or use CREATE TEMPORARY TABLE IF NOT EXISTS
Using a normal CREATE TABLE	It commits the transaction	It's a different problem

Troubleshooting

Symptom	Likely cause	Fix
1 warning with a different code	Something else	Run SHOW WARNINGS straight after
SHOW WARNINGS is empty	Another statement ran in between	Run it immediately after
The table disappears by itself	Temporary tables end with the session	That's expected

Conclusion

Warning 1751 means a temporary table created in the transaction survived the ROLLBACK, while everything else was undone. Create temporary tables before the transaction, and the warning goes away.

Additional Resources

• Statements That Cause an Implicit Commit — temporary tables.
• CREATE TEMPORARY TABLE Statement — how they work.

Related

• Which MySQL Statements Commit a Transaction Automatically
• Fixed: ROLLBACK Doesn't Undo My Changes After CREATE TABLE in MySQL
• How to Use a Read-Only Transaction in MySQL