MySQL Bugs: #42603: Foreign keys: crash if temporary table

Bug #42603	Foreign keys: crash if temporary table
Submitted:	4 Feb 2009 19:44	Modified:	5 Feb 2009 21:03
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	6.1.0-alpha-debug	OS:	Linux (SUSE 10.0 / 32-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

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

I create a Falcon table t1 with a primary key.
I create a Falcon table t2 referencing t1.
I insert a row in t1.
I create a MyISAM temporary table t2.
I try to update t1. Failure.
I create an index on t2.
I try to update t1. Crash.

How to repeat:
create table t1 (s1 int primary key) engine=falcon;
create table t2 (s1 int references t1(s1) on update set null) engine=falcon;
insert into t1 values (5);
create temporary table t2 (s1 int) engine=myisam;
update t1 set s1 = 2;
create index i on t2 (s1);
update t1 set s1 = 2;

Example run:

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

mysql> create table t2 (s1 int references t1(s1) on update set null) engine=falcon;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values (5);
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table t2 (s1 int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set s1 = 2;
ERROR 1782 (HY000): Foreign key error: constraint 'fk_t2_dn641': no supporting index on child table
mysql> create index i on t2 (s1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update t1 set s1 = 2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Hello Miguel!

I suspect that crash that Peter sees is caused by assertion failure. So to reproduce it one needs to use debug version of server. In any case error
message about missing supporting index which both of you are getting is a sufficient indication that something is wrong. Inspecting code in FK implementation confirms this. So to save time I am simply marking this bug as "verified".

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/65397

2705 Dmitry Lenev	2009-02-05
      Fix for bug #42603 "Foreign keys: crash if temporary table".
      
      In --foreign-key-all-engines=1 mode temporary table with the same 
      name as one of the tables participating in a foreign key was 
      shadowing this table during foreign key checks and actions which 
      could have led to unwarranted errors, violation of constraints or 
      even server crashes due to failing asserts.
      
      This fix prohibits such shadowing by requiring all tables which
      are being open for foreign key checks to be base tables. It is OK 
      to do so since only such tables can participate in foreign key
      constraints.

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/65407

2705 Dmitry Lenev	2009-02-05
      Fix for bug #42603 "Foreign keys: crash if temporary table".
      
      In --foreign-key-all-engines=1 mode temporary table with the same 
      name as one of the tables participating in a foreign key was 
      shadowing this table during foreign key checks and actions which 
      could have led to unwarranted errors, violation of constraints or 
      even server crashes due to failing asserts.
      
      This fix prohibits such shadowing by requiring all tables which
      are being open for foreign key checks to be base tables. It is OK 
      to do so since only such tables can participate in foreign key
      constraints.

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