Bug #64617 MySQL must improve error messages

• Submitted: 12 Mar 2012 6:37
• Modified: 7 Jun 2012 16:07
• Reporter: Ivan Cachicatari
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S2 (Serious)
• Version: 5.5.21
• OS: Any
• CPU Architecture: Any

[12 Mar 2012 6:37] Ivan Cachicatari

Description:
When you add a new foreign key with a wrong field name in reference table MySQL return a generic message.

How to repeat:
Create two tables:

create table foo (
	id integer not null primary key,
	bar_id int not null
);
create table bar (
	id integer not null primary key
);

Try to add a new foreign key with wrong field name:

alter table foo
	add foreign key(bar_id) references bar(ANY_FIELD) ;

it returns a error like: 

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

Suggested fix:
Please improve the DML error messages.

in this case it must return an error message like this: 

Can't add foreign key, field bar.ANY_FIELD don't exists.

[12 Mar 2012 6:39] Ivan Cachicatari

changed DML to DDL

[12 Mar 2012 6:52] Valeriy Kravchuk

Thank you for the problem report.

[7 Jun 2012 16:07] Paul DuBois

Noted in 5.6.6 changelog.

When an ALTER TABLE operation was performed with an invalid foreign
key constraint, the error reported was ER_CANT_CREATE_TABLE rather
than ER_CANNOT_ADD_FOREIGN.

[24 Dec 2012 9:06] Erlend Dahl

Bug #55183 was marked as a duplicate.