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).