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).
ofcorse is DDL and not transactional
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).
Isn't MySQL fun? Always so much to keep in mind:)
@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.