MySQL Bugs: #42063: Foreign keys: constraint survives after table is dropped

Bug #42063	Foreign keys: constraint survives after table is dropped
Submitted:	12 Jan 2009 21:06	Modified:	28 May 2009 17:43
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 the server with mysqld --foreign-key-all-engines=1.

I create a table t1.
I create a table t2 which references t1.
I drop t2.
I try to update t1.
I get an error message "Table 'test.t2' doesn't exist".
That suggests the referencing constraint wasn't destroyed.

How to repeat:
create table t1 (s1 int primary key) engine=falcon;
create table t2 (s1 int references t1 (s1)) engine=falcon;
drop table t2;
insert into t1 values (1);
update t1 set s1 = 0;

Sample run:

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

mysql> create table t2 (s1 int references t1 (s1)) engine=falcon;
Query OK, 0 rows affected (0.13 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.02 sec)

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

mysql> update t1 set s1 = 0;
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

Thank you for the bug report. Verified as described:

c:\dbs>6.1\bin\mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 6.1.0-alpha-nt Source distribution

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

mysql> create database d2;
Query OK, 1 row affected (0.00 sec)

mysql> use d2
Database changed
mysql> create table t1 (s1 int primary key) engine=falcon;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t2 (s1 int references t1 (s1)) engine=falcon;
Query OK, 0 rows affected (0.17 sec)

mysql> drop table t2;
Query OK, 0 rows affected (0.03 sec)

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

mysql> update t1 set s1 = 0;
ERROR 1146 (42S02): Table 'd2.t2' doesn't exist
mysql>

I also get "table doesn't exist" messages if I do
RESTORE ... OVERWRITE;
for a database that contains the foreign-key table.
Because of this bug, I am assuming that backup and
restore won't work with foreign keys for some time.

Hello!

This bug was fixed as part of work on milestone 14 of WL#148 "Foreign keys (for all engines)". Test for this bug was pushed into mysql-6.1-fk tree with the rest of test coverage for this milestone. Since this bug was reported against tree which is not publicly available yet I am simply closing this bug.