{"id":72,"date":"2012-03-12T01:24:25","date_gmt":"2012-03-12T06:24:25","guid":{"rendered":"http:\/\/en.latindevelopers.com\/ivancp\/?p=72"},"modified":"2012-03-12T01:24:25","modified_gmt":"2012-03-12T06:24:25","slug":"mysql-must-improve-error-messages","status":"publish","type":"post","link":"http:\/\/en.latindevelopers.com\/ivancp\/2012\/mysql-must-improve-error-messages\/","title":{"rendered":"MySQL must improve error messages"},"content":{"rendered":"

I just finished a database modification, a new foreign key creation\u00a0 shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.<\/p>\n

There is a way to create a new foreign key:<\/p>\n

-- Create two tables foo and bar\r\ncreate table foo (\r\n\tid integer not null primary key,\r\n\tbar_id int not null    -- foreign key\r\n);\r\ncreate table bar (\r\n\tid integer not null primary key\r\n);\r\n-- Try to create a foreign key on `foo`\r\nalter table foo\r\n\tadd foreign key(bar_id) references bar(SOME_FIELD) ;<\/pre>\n
The last sentence returns a generic error message:<\/p>\n
Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)<\/code><\/p>\n
Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD)<\/code>, sometimes happens,\u00a0 but if MySQL would have shown a different message like \"field bar.SOME_FIELD don't exists\"<\/code> I would not be awake until 2 am.<\/p>\n
I’m using MySQL 5.5.21 community edition.<\/p>\n
if I had noticed that wrong field name<\/p>\n
Share this:<\/h3>