MySQL Bugs: #26848: Need better error message for foreign key constraint error.

Bug #26848	Need better error message for foreign key constraint error.
Submitted:	5 Mar 2007 20:05	Modified:	6 Mar 2007 19:06
Reporter:	Kevin Regan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.36, 5.0.22	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The "my_bar" table below has an error in it (it specifies that a "not null" foreign key should be set to null on delete):

CREATE TABLE my_foo (
    uid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
) Engine=InnoDB;

CREATE TABLE my_bar (
    my_foo_uid INT UNSIGNED NOT NULL,
    FOREIGN KEY (my_foo_uid) REFERENCES my_foo(uid) ON DELETE SET NULL
) Engine=InnoDB;

When entering the "my_bar" table, I get the following error message:

ERROR 1005 (HY000): Can't create table './f5em/my_bar.frm' (errno: 150)

It would be much better if this said something like "attempting to set NOT NULL field to NULL at line #...".

How to repeat:

Attempt to creat the following tables:

CREATE TABLE my_foo (
    uid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
) Engine=InnoDB;

CREATE TABLE my_bar (
    my_foo_uid INT UNSIGNED NOT NULL,
    FOREIGN KEY (my_foo_uid) REFERENCES my_foo(uid) ON DELETE SET NULL
) Engine=InnoDB;

Suggested fix:

Add a better error message along with a line #.  Currently, it is difficult to diagnose what is going wrong (or where in my DB creation script the problem is happening).

I also get the same error for the tables below:

CREATE TABLE my_foo (
    uid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
) Engine=InnoDB;

CREATE TABLE my_bar (
    my_foo_uid INT UNSIGNED,
    FOREIGN KEY (my_foo_uid) REFERENCES my_foo(uid_X) ON DELETE SET NULL
) Engine=InnoDB;

Notice that, this time, I'm attempting to reference the wrong column name (uid_X instead of uid).

Again, this makes it very difficult to diagnose problems with my DB creation scripts when all of these errors are lumped together like this.

Thank you for a bug report. Verified just as described with 5.0.36 on Linux:

mysql> CREATE TABLE my_foo (
    ->     uid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
    -> ) Engine=InnoDB;
Query OK, 0 rows affected (0.39 sec)

mysql> CREATE TABLE my_bar (
    ->     my_foo_uid INT UNSIGNED NOT NULL,
    ->     FOREIGN KEY (my_foo_uid) REFERENCES my_foo(uid) ON DELETE SET NULL
    -> ) Engine=InnoDB;
ERROR 1005 (HY000): Can't create table './ttt/my_bar.frm' (errno: 150)
mysql> CREATE TABLE my_bar (
    ->     my_foo_uid INT UNSIGNED,
    ->     FOREIGN KEY (my_foo_uid) REFERENCES my_foo(uid_X) ON DELETE SET NULL
    -> ) Engine=InnoDB;
ERROR 1005 (HY000): Can't create table './ttt/my_bar.frm' (errno: 150)
mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.0.36-enterprise-gpl |
+-----------------------+
1 row in set (0.04 sec)

I think this feature request already is in the bugs database.

Note that SHOW INNODB STATUS prints a very detailed error message.

I'm not sure where to find the link to the other bug in this interface.  Could you give me a pointer to it?

Bug #23693 is the latest example.

This bug report is about cryptic foreign key error messages:

http://bugs.mysql.com/bug.php?id=16290