MySQL Bugs: #35519: Foreign keys: create fails with InnoDB

Bug #35519	Foreign keys: create fails with InnoDB
Submitted:	24 Mar 2008 17:27	Modified:	28 Apr 2008 10:36
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.1.0-fk-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start mysqld with --foreign-key-all-engines=1.

I start by saying "set @@storage_engine=innodb".
I try to create a table with a foreign key reference.
I get an error message.

How to repeat:
mysql> set @@storage_engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1 (s1 int primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> create table t2 (s1 int, foreign key (s1) references t1 (s1));
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)

Thank you for the bug report. Verified as described:

[miguel@mirador dbs]$ 6.1fk/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 6.1.0-fk-debug Source distribution

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

mysql> set @@storage_engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 int primary key);
Query OK, 0 rows affected (0.00 sec)

mysql> create table t2 (s1 int, foreign key (s1) references t1 (s1));
ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150)
mysql>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/45997

ChangeSet@1.2609, 2008-04-25 13:16:47+04:00, dlenev@mockturtle.local +4 -0
  Fix for bug #35519 "Foreign keys: create fails with InnoDB".
  
  In --foreign-key-all-engines mode attempt to create table foreign key
  constraint for InnoDB table led to "Can't create table '...' (errno: 150)"
  error.
  
  This is happened because in addition to new SQL-layer FK we still were
  trying to create old-style, 'native' InnoDB FK.
  
  This fix disables creation of 'native' InnoDB FK in new mode. It also
  temporarily disallows to open tables with 'native' foreign keys in new
  mode in order to avoid problems until we will figure out what is the
  sensible, upgrade-friendly behavior in this case.
  
  Note to InnoDB developers: this patch probably can be ignored since it
  is unlikely to appear in the main tree as it is now.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46105

ChangeSet@1.2611, 2008-04-28 14:25:44+04:00, dlenev@mockturtle.local +5 -0
  Fix for bug #35519 "Foreign keys: create fails with InnoDB".
  
  In --foreign-key-all-engines mode attempt to create table foreign key
  constraint for InnoDB table led to "Can't create table '...' (errno: 150)"
  error.
  
  This is happened because in addition to new SQL-layer FK we still were
  trying to create old-style, 'native' InnoDB FK.
  
  This fix disables creation of 'native' InnoDB FK in new mode. It also
  temporarily disallows to open tables with 'native' foreign keys in new
  mode in order to avoid problems until we will figure out what is the
  sensible, upgrade-friendly behavior in this case.
  
  Note to InnoDB developers: this second change can be ignored since it
  is unlikely to appear in the main tree as it is now.

Fix for this bug was pushed into mysql-6.1-fk tree. Since it was reported against tree which is not publicly available yet I am simply closing this bug.