Consider this fairly normal scenario: a database on one server that I need to move to another server. I might do it like this:
server1$ mysqldump my_database > db.sql
server2$ mysql my_database < db.sql
Unfortunately, if my_database happens to contain tables with foreign key constraints, we get the following delightfully informative error:
ERROR 1005 (HY000) at line 12: Can’t create table ‘./my_database/foo.frm’ (errno: 150)
It turns out the way to resolve this is to use SET FOREIGN_KEY_CHECKS = 0; before you do the import, and SET FOREIGN_KEY_CHECKS = 1; after the import successfully completes.
So, my questions to the MySQL apologists are these:
- Why do we get to see ERROR 1005 (HY000) instead of something intelligent?
- Why doesn’t mysqldump warn the user that it cannot safely create a dump that can be restored error-free?
- Why doesn’t mysqldump offer to prepend and append suitable FOREIGN_KEY_CHECKS lines to the output?
- Why do people still use and even recommend this joke of a database, thereby forcing me to deal with it?