Bug #6188 Clarified InnoDB error message during foreign key mismatches

• Submitted: 20 Oct 2004 22:24
• Modified: 10 Nov 2004 9:37
• Reporter: Thomas Park
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 4.1.5-gamma
• OS: Linux (Linux)
• Assigned to: Bugs System
• CPU Architecture: Any

[20 Oct 2004 22:24] Thomas Park

Description:
InnoDB does not give a clear error message if you try to create a table with one or more column types that do not match the column types of foriegn keys that had been established against that table.

I apologize if this description is not clear; please see the replication steps for what is hopefully a more insightful explanation.

How to repeat:
(Pseudo-sql)

-- create tables
create table FOO ( someCol decimal(10,0) primary key );
create table BAR ( someCol decimal(10,0) );
add foreign key to BAR.someCol on FOO.someCol;

-- alter column on which a foreign key constraing exists
drop table FOO;
create table FOO ( someCol int primary key auto_increment );

-- this fails because table bar still had a foreign key constraint on foo.someCol but we changed the column type.  

-- the error message given is "ERROR 1005 (HY000) at line 17: Can't create table './MYDATABASE/FOO.frm' (errno: 150)"

Suggested fix:
The error message "ERROR 1005 (HY000) at line 17: Can't create table './MYDATABASE/FOO.frm' (errno: 150)" does not give a clear indication of the root cause of the problem.  It would have greatly aided my troubleshooting of this problem if the engine returned an error message along the lines of

"Can't create table 'FOO'; type of column 'someCol' does not match existing foreign key restrictions"

[20 Oct 2004 22:30] Thomas Park

Note that the "create" statements should specify "type = InnoDB".  I apologize for the omission.

[20 Oct 2004 22:32] Thomas Park

updated category

[20 Oct 2004 22:40] Thomas Park

Ah, I located "perror" in the documentation.  Please disregard this feature request.

bash$ perror 150
Error code 150:  Unknown error 150
150 = Foreign key constraint is incorrectly formed

[20 Oct 2004 23:01] MySQL Verification Team

Thank you for the info.

[10 Nov 2004 9:37] Marko Mäkelä

I'm assigning this to Jan Lindström. See also bug #3491. As a workaround, the latest foreign key error can be viewed by issuing the command
SHOW INNODB STATUS\G

[15 Aug 2010 3:36] J Ballard

A little late, yes, but I feel it is relevant.

If it was not clear before, this error also arises if the referenced table is anything other than InnoDB. For instance, if you have a table FOO, which is innoDB, and you have a field FIELD with a foreign key referencing table BAR, and the table BAR is not innoDB, then an "errno:150" will arise as well.