MySQL Bugs: #41692: Foreign keys: failure if mixed engines

Bug #41692	Foreign keys: failure if mixed engines
Submitted:	23 Dec 2008 0:22	Modified:	6 Feb 2009 20:29
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 make a MyISAM table with a foreign-key reference to a Falcon table.
I insert into the Falcon table.
I insert into the MyISAM table.
I rollback so the Falcon-table inserted row is gone.
I select from the MyISAM table.
I see a row. Thus referential integrity has been violated.

How to repeat:
create table t1 (s1 int primary key) engine=falcon;
create table t2 (s1 int references ta(s1) on delete restrict on update restrict) engine=myisam;
insert into t1 values (1);
insert into t2 values (1);
rollback;
select * from t1;           /* 0 rows */
select * from t2;           /* 1 row */

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

2703 Dmitry Lenev	2009-02-06
      Fix for bug #41692 "Foreign keys: failure if mixed engines".
      
      Foreign keys in which one participating table was transactional 
      and another was not didn't preserve integrity in scenarios when
      both parent and child table were modified in the same transaction
      and then this transaction was rolled back.
      
      This problem stems from the nature of such foreign keys. Changes
      to transactional tables can be rolled back and therefore cannot
      be fully relied upon when performing foreign key checks before
      modification of non-transactional table (changes to which are
      not rollbackable).
      
      This fix solves this problem by prohibiting such foreign keys.
      In future we might consider raising this limitation by allowing
      them in certain "safe" scenarios.

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

2706 Dmitry Lenev	2009-02-06
      Fix for bug #41692 "Foreign keys: failure if mixed engines".
      
      Foreign keys in which one participating table was transactional
      and another was not didn't preserve integrity in scenarios when
      both parent and child table were modified in the same transaction
      and then this transaction was rolled back.
      
      This problem stems from the nature of such foreign keys. Changes
      to transactional tables can be rolled back and therefore cannot
      be fully relied upon when performing foreign key checks before
      modification of non-transactional table (changes to which are
      not rollbackable).
      
      This fix solves this problem by prohibiting such foreign keys.
      In future we might consider raising this limitation by allowing
      them in certain "safe" scenarios.

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