Figuring out MySQL ERROR 1005 (HY000) Can’t create table (errno: 150)

So I’m dumping a database on a remote server to pull down to my local box to do some development.  When loading the mysqldump file I encountered the error:

ERROR 1005 (HY000) at line 8680: Can’t create table ‘database.table’ (errno: 150)

After doing some searching online it seems that is one of the notoriously cryptic MySQL error messages that is woefully overloaded.

I did manage to fix the error (which ended up being a foreign key reference from another table to the one that was erroring out whereby the column definitions were not exactly the same) and learned two important things:

  1. You can invoke the following mysql command immediately after the error to get a more verbose error message (which is what enabled me to solve the problem): SHOW INNODB STATUS
  2. Jason Hinkle has a very nice online reference with a number of possible culprits, and their solutions are listed.

Leave a Reply