April 13, 2006

MySQL Gotchas | DDL and Transactions: No Warnings Gotcha

Many of the gotchas in pre 5.0 MySQL versions stem from the fact that operations silently failed without any type of error message or warning. That situation has improved with 5.0, with explicit warnings being emmitted on some operations, such as when data is truncated, making it easier to catch potential problems.

However, with DDL operations (e.g. DROP TABLE) in a transactional context, there are still no warnings which would alert the user to potential problems. Of course, in MySQL DDL operations are by nature not transactional, but sometimes it is easy to forget this.

mysql> WARNINGS; Show warnings enabled. mysql> CREATE TABLE ddl_test (id INT) ENGINE=InnoDB; Query OK, 0 rows affected (0.08 sec) mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> DROP TABLE ddl_test; Query OK, 0 rows affected (0.04 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec)

Does the table ddl_test still exist? The above gives no clues, although it appears the operation has succeeded.

mysql> DESC ddl_test; ERROR 1146 (42S02): Table 'test.ddl_test' doesn't exist

(the above executed in with MySQL 5.0.19).

Posted at 7:41 AM

come on ... it's a drop statement...

ofcorse is DDL and not transactional
Posted by: dboyr | 2008-01-11 09:53
dboyr, I think the main point is that MySQL has given no indication that the rollback doesn't work. This could be confusing to people who usually use databases with transaction safe DDL (like PostgreSQL and MS SQL Server).

But I do agree that while it's nice to be able to do DDL in a transaction it shouldn't be taken for granted. It's best to get in the habit of having a backup of your data before issuing DROP commands (and even PostgreSQL doesn't let you roll back from dropping a database, it's gone from disk).
Posted by: umaz | 2008-07-31 09:00
Actually, I believe the rollback works perfectly fine, but is effectively a no-op because the DROP has implicitly committed your transaction at that point.

Isn't MySQL fun? Always so much to keep in mind:)
Posted by: Bart | 2008-10-13 12:21
@dbyor as umaz indicated, DDL is transactional in some databases (like postgres), so the above set of commands would rollback the drop table just fine.

@umaz it is worth clarifying, while it's true postgres won't let you rollback a drop database, it isn't ambiguous about it. Attempting to drop a database in a transaction will result in an error, stating explicitly that you can't drop a a database within a transaction.
Posted by: Robert Treat | 2010-05-14 13:00
ORACLE also has DDL no transactional...
Posted by: jorneg | 2011-02-04 15:48