MySQL must improve error messages

Posted on March 12, 2012 Comments

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

There is a way to create a new foreign key:

-- Create two tables foo and bar
CREATE TABLE foo (
	id INTEGER NOT NULL PRIMARY KEY,
	bar_id INT NOT NULL    -- foreign key
);
CREATE TABLE bar (
	id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
	ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;

The last sentence returns a generic error message:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD), sometimes happens,  but if MySQL would have shown a different message like "field bar.SOME_FIELD don't exists" I would not be awake until 2 am.

I’m using MySQL 5.5.21 community edition.

if I had noticed that wrong field name

Related Posts :

Comments

  1. Did you know that you can look at the output of SHOW ENGINE INNODB STATUS and see the details of the last foreign key error? Or did you check the manual at http://dev.mysql.com/doc/refman/5.5/en/innodb-error-codes.html to see what the error code was? That page says:

    “1005 (ER_CANT_CREATE_TABLE)

    Cannot create table. If the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to error –1, table creation probably failed because the table includes a column name that matched the name of an internal InnoDB table.”

    So if you had tried to search for your error in a search engine, you would have found that page, and you would have known that the foreign key constraint was not correctly formed.

    You are also very lucky – if you run your example in MySQL 5.1 or earlier, the example works just fine with no errors, and the only time you get an error is when you try to insert into foo, and the foreign key actually fails.

    • Thank you!, but I still thinking is not the right way… maybe add some comment on first error message like “see InnoDB status for more details.”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" cssfile="">