MySQL Bugs: #16290: Unclear error message when adding foreign key constraint

Bug #16290	Unclear error message when adding foreign key constraint
Submitted:	8 Jan 2006 22:47	Modified:	14 Dec 2010 18:51
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	all	OS:	Any (all)
Assigned to:	Jimmy Yang	CPU Architecture:	Any

Description:
If you create a foreign key constraint which references to a column that's not indexed, you get the error message:

ERROR 1005 (HY000): Can't create table '.\test\b.frm' (errno: 150)

How to repeat:
mysql> CREATE TABLE a (
    ->   id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
    ->   ref_col INT UNSIGNED NOT NULL
    ->   ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE b (
    ->   id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
    ->   ref_a INT UNSIGNED NOT NULL,
    ->   FOREIGN KEY (ref_a) REFERENCES a (ref_col)
    ->   ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table '.\test\b.frm' (errno: 150)

mysql> ALTER TABLE a ADD INDEX (ref_col);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE b (
    ->   id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
    ->   ref_a INT UNSIGNED NOT NULL,
    ->   FOREIGN KEY (ref_a) REFERENCES a (ref_col)
    ->   ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
It would be nice if the error message would indicate more exactly what the problem is, e.g. ERROR ... referenced column xxx does not have an index.

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE a (
    -> id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
    -> ref_col INT UNSIGNED NOT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.15 sec)

mysql> CREATE TABLE b (
    -> id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
    -> ref_a INT UNSIGNED NOT NULL,
    -> FOREIGN KEY (ref_a) REFERENCES a (ref_col)
    -> ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table './test/b.frm' (errno: 150)
mysql> exit
Bye
miguel@hegel:~/dbs/5.0> bin/perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Extended error message you can see in SHOW ENGINE INNODB STATUS in LATEST FOREIGN KEY ERROR section, though user needs to have SUPER privilege to do this.

mysql> alter table child add foreign key(p_id) references parent(id);
ERROR 1005 (HY000): Can't create table './test/#sql-32ff_9.frm' (errno: 150)
mysql> show engine innodb status;
ERROR 1227 (42000): Access denied; you need the SUPER privilege for this operation

It would be really good if clear error message will be provided for all users and not only if you have SUPER privilege.

Another case that triggers a similarly confusing error is when the data types of the fields involved in the foreign key don't exactly match (integer vs varchar, integer signed versus integer unsigned, etc).  

"Foreign key data types do not match" or even a generic "Error creating foreign key" would be far more useful than "Cannot write file".

See also https://support.mysql.com/view.php?id=26022

One of our users has just been bitten by this. Is there any plan to actually fix this very irritating problem?

Bug 16290 has been open for 2 1/2 years and it looks like it will never be fixed. This causes problems for people without the SUPER privilege who are unable to view the reason for ALTER/CREATE TABLE failures due to incorrect foreign key definitions. While the problem may not be an issue in smaller organisations in larger ones where not all users are given SUPER privileges this is more of an issue, and it would be nice if the problem were addressed or at least when it will be addressed.

Thanks for any updated comments on this.

Any update on this at all?  It would be *extremely* helpful if these error message were more meaningful.  In my case, I had this error when I had not yet defined the table the foreign key was dependent on.  The error message received was the same as Markus Popp's except that the database and table name specified were different.  If the error message had specified "Unable to define foreign key against missing table: <tablename>" - that would have been really helpful.  There are other cases where this error message comes up around the foreign key constraint definition but the error message is nearly worthless to the one who is writing the code.

Having looked at the code, it seems the right person is assigned to the issue, however, I see that it's going to be a significant effort to fix as there are about fourty different places where the code returns the same value indicating that InnoDB was not able to create a foreign key constraint.  Heikki - has that code been modified since it was created 10 years before the issue was raised here?  Just curious... :-)

KB

InnoDB actually returned HA_ERR_CANNOT_ADD_FOREIGN (150). However, in mysql server layer, ha_create_table() reports all error as ER_CANT_CREATE_TABLE:

ha_create_table()
{
  ...
  if (error)
  {
    strxmov(name_buff, db, ".", table_name, NullS);
    my_error(ER_CANT_CREATE_TABLE, MYF(ME_BELL+ME_WAITTANG), name_buff, error);
  }

}

So if you would like to see "ERROR: Create table failed ... referenced column does not have an index". It has to be done in above interface (substitute ER_CANT_CREATE_TABLE with the right error code).

What we can do from InnoDB side is to push a warning message, so user can see the warning message through "show warnings".

Here is the warning message printed:

mysql> CREATE TABLE b (
    ->        id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY,
    ->        ref_a INT UNSIGNED NOT NULL,
    ->        FOREIGN KEY (ref_a) REFERENCES a (ref_col)
    ->        ) ENGINE=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.b' (errno: 150)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                               |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  150 | Create table 'test/b' failed. Cannot find an index in the referenced table where the referenced columns appear as the first columns.
 |
| Error   | 1005 | Can't create table 'test.b' (errno: 150)                                                                                              |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Fix checked into /mysql-5.5-innodb and merge into mysql-trunk-innodb.

Following warning will be issued if we create table without appropriate index on referenced/referencing keys.

| Warning |  150 | Create table 'test/b' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

| Warning |  150 | Create table 'test/b' with foreign key constraint failed. There is no index in the referencing table where referencing columns appear as the first columns.

Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)

Added to change log:

Clarified the message when a CREATE TABLE statement fails because a
foreign key constraint does not have the required indexes.

Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)