MySQL Bugs: #59508: Unable to create table with a foreign key constraint

Bug #59508	Unable to create table with a foreign key constraint
Submitted:	14 Jan 2011 16:09	Modified:	9 Nov 2011 1:53
Reporter:	Sandy Pendergraft	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.x, 5.5.8, 5.5.9-bzr	OS:	Any (redhat 5.4 x86_64, Mac OS X)
Assigned to:	John Russell	CPU Architecture:	Any

Description:
Unable to create a table with a foreign key constraint. This is only with MySQL version 5.5.8. 
Specific version: 5.5.8-enterprise-commercial-advanced-log
We have previous versions in house and all other versions succeed.

How to repeat:
Try to pass the following create table statement to a 5.5 MySQL:

Use any tool that you can to pass the following SQL to a 5.5.8-enterprise-commercial-advanced-log database:

CREATE TABLE JVSDDBTA35 (COL1 CHAR(10) NOT NULL,  COL2 CHAR(10) NOT NULL, COL3 CHAR(10) NOT NULL,INDEX(COL2), PRIMARY KEY JVSDDBPK35 (COL1), CONSTRAINT JVSDDBFK35 FOREIGN KEY  (COL2) REFERENCES JVSDDBTA35(COL2)) ENGINE = InnoDB

Specific version: 5.5.8-enterprise-commercial-advanced-log

Suggested fix:
Make it work.

Also with 5.5.8 on Windows. ("select version" only returns "5.5.8')

Even referencing another table as 'parent' fails:

CREATE TABLE parent (col2 INTEGER KEY) ENGINE = INNODB;

CREATE TABLE JVSDDBTA35 (COL1 CHAR(10) NOT NULL,  COL2 CHAR(10) NOT NULL, COL3 CHAR(10) NOT NULL,INDEX(COL2), PRIMARY KEY JVSDDBPK35 (COL1), CONSTRAINT JVSDDBFK35 FOREIGN KEY (COL2) REFERENCES parent(COL2)) ENGINE = INNODB;

Error Code : 1005
Can't create table 'test.jvsddbta35' (errno: 150)

Indeed, this looks like a regression bug. In 5.1 we had:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.55-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE JVSDDBTA35 (COL1 CHAR(10) NOT NULL,  COL2 CHAR(10) NOT
    -> NULL, COL3 CHAR(10) NOT NULL,INDEX(COL2), PRIMARY KEY JVSDDBPK35
    -> (COL1), CONSTRAINT JVSDDBFK35 FOREIGN KEY  (COL2) REFERENCES
    -> JVSDDBTA35(COL2)) ENGINE = InnoDB
    -> ;
Query OK, 0 rows affected (0.08 sec)

While in current mysql-5.5 tree we have:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.9-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE JVSDDBTA35 (COL1 CHAR(10) NOT NULL,  COL2 CHAR(10) NOT
    -> NULL, COL3 CHAR(10) NOT NULL,INDEX(COL2), PRIMARY KEY JVSDDBPK35
    -> (COL1), CONSTRAINT JVSDDBFK35 FOREIGN KEY  (COL2) REFERENCES
    -> JVSDDBTA35(COL2)) ENGINE = InnoDB
    -> ;
ERROR 1005 (HY000): Can't create table 'test.jvsddbta35' (errno: 150)

I don ot see this explained anywhere at http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

A couple of things to verify:

1) If it is the repro stated in the bug report, then this is an existing behavior since 5.1 plugin with fast create index, while the bug report states this is only for 5.5.8? Please double check

2) In the repro, is the FK being COL2 references to COL2 itself (same column) or COL1? Since it would make little sense have COL2 referencing to COL2 itself. In both case, you will need to set SET foreign_key_checks = 0 to insert the first set of data.

If it is the case of COLUMN referencing to itself, then we might intentionally block it. Here are some exchange with Marko on IRC regarding the issue:

<Jimmy> marko, I was looking at bug #59508, In dict_foreign_find_index(), the "types_idx" is skipped as part of (fast index) change, is this for a reason?
....
<marko> I think that we already do not allow self-referencing FKs
<marko> already before ha_innobase::add_index, that is
<marko> does it work without old_alter_table=0?
<marko> sorry, does it work with old_alter_table=1?
<Jimmy> marko, it works in 5.1
<Jimmy> builtin
<marko> and what about plugin with old_alter_table=1?
<Jimmy> No, it does not (for plugin), same reason, dict_foreign_find_index() can't find appropriate index (for referenced table)
...
<marko> OK, it is referencing the same column. I thought that this was forbidden, because it would lead to a stack overflow at runtime. (We should use an explicit stack in the FK checks instead of recursion.)
*** thek|gone is now known as thek
<Jimmy> is this documented?
<marko> i.e., make that constraint ON DELETE CASCADE, insert a chain of records, and you will get a stack overflow
<marko> hmm, or should it be COL2 REFERENCES COL1
<Jimmy> if "COL2 REFERENCES COL1", then everything is fine
<marko> yes, but try COL2 REFERENCES COL1 ON DELETE CASCADE, then insert a chain of records, and delete the head of the chain so that the entire chain should be deleted. It should cause a stack overflow for long chains (say, several 10s)
<Jimmy> marko, did we document such restriction (self reference not allowe)?
<marko> I do not remember. I ran into this maybe 5 years ago.

This is what we have with 5.1.56 using InnoDB Plugin:

openxs@ubuntu:~/dbs/5.1$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.56-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show plugins;
+------------+----------+----------------+---------------------+---------+
| Name       | Status   | Type           | Library             | License |
+------------+----------+----------------+---------------------+---------+
| binlog     | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| partition  | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| ARCHIVE    | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| BLACKHOLE  | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| CSV        | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| FEDERATED  | DISABLED | STORAGE ENGINE | NULL                | GPL     |
| MEMORY     | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| MRG_MYISAM | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| MyISAM     | ACTIVE   | STORAGE ENGINE | NULL                | GPL     |
| InnoDB     | ACTIVE   | STORAGE ENGINE | ha_innodb_plugin.so | GPL     |
+------------+----------+----------------+---------------------+---------+
10 rows in set (0.02 sec)

mysql> drop table JVSDDBTA35;
ERROR 1051 (42S02): Unknown table 'JVSDDBTA35'
mysql> CREATE TABLE JVSDDBTA35 (COL1 CHAR(10) NOT NULL,  COL2 CHAR(10) NOT NULL, COL3 CHAR(10)
    -> NOT NULL,INDEX(COL2), PRIMARY KEY JVSDDBPK35 (COL1), CONSTRAINT JVSDDBFK35 FOREIGN KEY 
    -> (COL2) REFERENCES JVSDDBTA35(COL2)) ENGINE = InnoDB
    -> ;
ERROR 1005 (HY000): Can't create table 'test.JVSDDBTA35' (errno: 150)
mysql> CREATE TABLE JVSDDBTA35 (COL1 CHAR(10) NOT NULL,  COL2 CHAR(10) NOT NULL, COL3 CHAR(10) NOT NULL,INDEX(COL2), PRIMARY KEY JVSDDBPK35 (COL1), CONSTRAINT JVSDDBFK35 FOREIGN KEY  (COL2) REFERENCES JVSDDBTA35(COL1)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.09 sec)

So, yes, 5.1 with plugin demonstrates the same (changed) behavior.

Foreign key (col2) is references to col2 itself (if it references to PK, col1, everything works). I agree that it does not make much sense in general, so this is more like I4 problem.

As this looks like intended change in behavior in plugin and 5.5.x we should make sure it is clearly documented. This is a documentation bug for now.

well .. my example failed when the FK referenced another table?

Peter, in your example, "COL2" in parent table is Integer data type, while "COL2" in JVSDDBTA35 is char(10). So there is column type mismatch. If you change COL2 in parent to char(10), everything should work:

mysql> CREATE TABLE parent (col2 CHAR(10) KEY) ENGINE = INNODB;
Query OK, 0 rows affected (1.73 sec)

mysql> CREATE TABLE JVSDDBTA35 (COL1 CHAR(10) NOT NULL,  COL2 CHAR(10) NOT NULL, COL3 CHAR(10)
    -> NOT NULL,INDEX(COL2), PRIMARY KEY JVSDDBPK35 (COL1), CONSTRAINT JVSDDBFK35 FOREIGN KEY
    -> (COL2) REFERENCES parent(COL2)) ENGINE = INNODB;
Query OK, 0 rows affected (2.93 sec)

Amending the 'note' in the foreign key doc to say:

InnoDB supports foreign key references between one column and another
within a table. (A column cannot have a foreign key reference to
itself.) In these cases, "child table records" really refers to
dependent records within the same table.

That's in 5.6 and 5.5. In 5.1, will clarify that the restriction only applies to the InnoDB Plugin.