MySql needs better error messages

(written by Lawrence Krubner, however indented passages are often quotes)

Yesterday I wrote that MySql WorkBench is a total waste of time. I’ve now wasted several hours trying to debug the schema that WorkBench generated. I will add another complaint: MySql needs better error messages. Every time I try to execute my schema, I get an error like this:

ERROR 1005 (HY000): Can’t create table ‘msc_wine.media’ (errno: 150)

I finally got frustrated enough that I posted this question on MySql Emergency, and I’m offering a $20 prize.

From my research so far, I’ve learned that errno 150 usually shows up for 1 of 4 reasons:

1.) Foreign key fails – other table or key does not exist

2.) Foreign key fails – the foreign key field has a type that is different than the field in that other table that is being referenced – types need to match

3.) Foreign key fails – the 2 tables being linked by the foreign key have different engines, perhaps InnoDB versus MyIsam. The engines need to be the same.

4.) Foreign key fails – the character sets are different. These need to be the same. If one table has a character set of UTF8, then the other table must also have a character set of UTF8.

First of all, it is terrible that MySql takes these 4 different problems and lumps them all under 1 error code: errno 150. Second of all, why doesn’t MySql just tell me what the problem is? Why do I have to spend hours trying to figure this out?

Anyway, I’ve checked the above 4 items carefully, and I still can not create the table that I want to create. So now I’m looking for a 5th reason.