Bug #41690 Foreign keys: failure with two connections
Submitted: 23 Dec 2008 0:17 Modified: 20 Dec 2013 8:25
Reporter: Peter Gulutzan Email Updates:
Status: Won't fix 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: CPU Architecture:Any

[23 Dec 2008 0:17] Peter Gulutzan
Description:
I'm using mysql-6.1-fk.
I start the server with mysqld --foreign-key-all-engines=1.

I start two client connections.
I create a primary-key table and foreign-key table.
I do some inserts and deletes and commits.
I soon see a foreign-key row with no matching primary-key row.

How to repeat:
Start up two connections. Call them t1 and t2.

On t1, say:
set @@autocommit=0;
set transaction isolation level repeatable read;
drop table if exists t1;
drop table if exists t2;
create table t1 (s1 int not null unique) engine=falcon;
create table t2 (s1 int not null unique, 
                 foreign key (s1) references t1 (s1)) engine=falcon;
insert into t1 values (1);
commit;

On t2, say:
set @@autocommit=0;
insert into t2 values (1);

On t1, say:
delete from t1;
commit;

On t2, say:
commit;
select * from t1;
select * from t2;
/* Notice that t2 has a row, t1 does not. */
[23 Dec 2008 14:48] MySQL Verification Team
Thank you for the bug report. Verified as described:

t2 >select * from t1;
Empty set (0.00 sec)

t2 >select * from t2;
+----+
| s1 |
+----+
|  1 | 
+----+
1 row in set (0.00 sec)

t2 >
[11 Mar 2009 10:34] Dmitry Lenev
Bug #43521 mentions the problem which is duplicate of issue reported in this bug report.
[20 Dec 2013 8:25] Erlend Dahl
The 6.x project has been abandoned.