Fabio Cepeda

Archive for the ‘MySQL’ Category

Yesterday, I spent a few good hours finding why I was not able to create a table because of the infamous errno:150

What happended:

  1. Set foreign key checks to 0
  2. Dump some tables
  3. Set foreing key checks to 1
  4. Try to insert updated tables back in the DB and got the infamous error.

With relational DBs got to be careful with the relationships left behind. I did not remember a relationship of one of the dumped tables in the database and it was not letting recreate the tables.

I found two good resources to troubleshoot this type of error, and I hope some day the MySQL team is able to be more precise about what is going on, in my case if the output of the error would have been like the one given by SHOW ENGINE INNODB STATUS; it would had save me a couple of hours. This error usually happens when something about the foreign key relationships is not ok. For example, trying the create relationships with different db fields types, or trying to create a table that has foreign relationships in other tables like what I did.

These two resources helped me a lot specially the command found in 2.:

1. Reasons for this error
2. Tips of importing to a DB

After I ran SHOW ENGINE INNODB STATUS; I noticed immieadiately that I needed to modify a foreign key relationship because this command shows the last FK error in the engine.

Well that is the way it goes, after an error comes a learning experience.