November 22, 2005

Die, MySQL, die!

You know what's coming by now, if you've read such balanced posts as MySQL sucks, MySQL makes me sick, or MySQL is a PITA.

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?
Posted by savs at November 22, 2005 2:02 PM
Comments

Hell yeah! DIE MYSQL! Postgresql rules!

Posted by: Hell Yeah! at November 22, 2005 3:45 PM

When i tried restoring my backups onto a new machine I found that only 700MB of 1.2GB worth of databases actually restored for this precise reason, which was fun.

Personally i now

FLUSH TABLES WITH READ LOCK

tar -cf mysql_backup.tar *database_dir*

then UNLOCK TABLES,

Note only works if all tables are myisam and you can tolerate not being able to write to your database for the minute or so it takes to tar up the data directory.

mysqlsnapshot http://jeremy.zawodny.com/mysql/mysqlsnapshot/ looks good but i couldn't get it to work properly


Posted by: cal at November 22, 2005 4:02 PM

Hmm, surely if your tables are MyISAM then they don't support foreign keys anyhow? I thought foreign keys were only available in InnoDB tables?

Posted by: Andrew Savory at November 22, 2005 4:10 PM

yeah sorry by "this issue" i meant the fact that mysqldump will merrily dump out sql that mysql will refuse to load. If any of your users have used a reserved keyword as a column name in a table (and it appears many do and mysql doesn't stop them when they do it) then the table won't get created and won't load when restoring from backup.

I would still look at mysqlsnapshot or master to slave replication if you are looking to get a backup, mysqldump doesn't work.

Posted by: cal at November 22, 2005 11:09 PM

Andrew, don't lose your time. Use a REAL database, just use PostgreSQL - http://www.postgresql.org/ ;-)

Posted by: Antonio Gallardo at November 23, 2005 2:10 AM

Postgres is better??? I'll just say one word... VACUUM!

Posted by: StewartB at November 25, 2005 12:40 PM

Yeah, PostgreSQL is better. I'll just say one word: autovacuum.

(Postgres is not better, but since Postgres ceased maintenance in 1994, replaced first by Postgres95 and then by PostgreSQL, this is a straw-man comparison.)

Posted by: Nix at December 1, 2005 1:30 PM