MySQL Bugs: #46125: Foreign keys: reorganizing partitions causes dangling reference

Bug #46125	Foreign keys: reorganizing partitions causes dangling reference
Submitted:	10 Jul 2009 23:49	Modified:	29 Jul 2009 13:17
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	6.1.0-alpha-debug	OS:	Linux (SUSE 11.1 64-bit)
Assigned to:	Dmitry Lenev	CPU Architecture:	Any

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

I create a partitioned parent table t1.
I create a foreign-key table t2 referencing t1.
I insert a row in t1.
I insert a matching row in t2.
I reorganize t1's partitions.
(Effectively I'm deleting, see Bug#23603 Partitions: reorganize means delete.)
Now there's a row in t2 that doesn't match any row in t1.

How to repeat:
drop database d;
create database d;
use d
create table t1 (s1 int primary key) engine=innodb partition by list (s1) (partition p1 values in (1));
create table t2 (s1 int references t1 (s1)) engine=innodb;                                                                  
insert into t1 values (1);
insert into t2 values (1);
alter table t1 reorganize partition p1 into (partition p1 values in (0));
select * from t1;
select * from t2;

Thank you for the bug report. Verified as described.

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

2741 Dmitry Lenev	2009-07-16
      Fix for bug #46125 "Foreign keys: reorganizing partitions causes
      dangling reference".
      
      In --foreign-key-all-engines=1 mode ALTER TABLE ... REORGANIZE
      PARTITION might have caused dangling references if table which
      was reorganized was a parent in some foreign key relationship
      and this reorganization removed some rows from this table.
      
      New foreign key implementation simply didn't take into account
      that ALTER TABLE ... REORGANIZE PARTITION might delete some rows
      from table being reorganized. So nothing was done to prevent
      deletion of parent rows with existing child rows.
      
      This fix solves this problem by checking if ALTER TABLE REORGANIZE
      PARTITION on table which serves as parent in a foreign key has
      deleted any rows, rolling back effect of such reorganization and
      emitting appropriate error message.
      
      Question for reviewer is marked by QQ.
     @ mysql-test/r/foreign_key_all_engines_2.result
        Added test for bug #46125 "Foreign keys: reorganizing partitions causes
        dangling reference".
     @ mysql-test/t/foreign_key_all_engines_2.test
        Added test for bug #46125 "Foreign keys: reorganizing partitions causes
        dangling reference".
     @ sql/sql_partition.cc
        Check if ALTER TABLE REORGANIZE PARTITION which changes
        parent table deletes any rows and if yes emit an appropriate
        error and rollback changes which were done.

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

2762 Dmitry Lenev	2009-07-29
      Fix for bug #46125 "Foreign keys: reorganizing partitions causes
      dangling reference".
      
      In --foreign-key-all-engines=1 mode ALTER TABLE ... REORGANIZE
      PARTITION might have caused dangling references if table which
      was reorganized was a parent in some foreign key relationship
      and this reorganization removed some rows from this table.
      
      New foreign key implementation simply didn't take into account
      that ALTER TABLE ... REORGANIZE PARTITION might delete some rows
      from table being reorganized. So nothing was done to prevent
      deletion of parent rows with existing child rows.
      
      This fix solves this problem by checking if ALTER TABLE REORGANIZE
      PARTITION on table which serves as parent in a foreign key has
      deleted any rows, rolling back effect of such reorganization and
      emitting appropriate error message.
     @ mysql-test/r/foreign_key_all_engines_2.result
        Added test for bug #46125 "Foreign keys: reorganizing partitions causes
        dangling reference".
     @ mysql-test/t/foreign_key_all_engines_2.test
        Added test for bug #46125 "Foreign keys: reorganizing partitions causes
        dangling reference".
     @ sql/sql_partition.cc
        Check if ALTER TABLE REORGANIZE PARTITION which changes
        parent table deletes any rows and if yes emit an appropriate
        error and rollback changes which were done.
        Since the above is not possible for one-phase partition changes
        disable them for parent tables.

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

2764 Dmitry Lenev	2009-07-29
      Fix for bug #46125 "Foreign keys: reorganizing partitions causes
      dangling reference".
      
      In --foreign-key-all-engines=1 mode ALTER TABLE ... REORGANIZE
      PARTITION might have caused dangling references if table which
      was reorganized was a parent in some foreign key relationship
      and this reorganization removed some rows from this table.
      
      New foreign key implementation simply didn't take into account
      that ALTER TABLE ... REORGANIZE PARTITION might delete some rows
      from table being reorganized. So nothing was done to prevent
      deletion of parent rows with existing child rows.
      
      This fix solves this problem by checking if ALTER TABLE REORGANIZE
      PARTITION on table which serves as parent in a foreign key has
      deleted any rows, rolling back effect of such reorganization and
      emitting appropriate error message.
     @ mysql-test/r/foreign_key_all_engines_2.result
        Added test for bug #46125 "Foreign keys: reorganizing partitions causes
        dangling reference".
     @ mysql-test/t/foreign_key_all_engines_2.test
        Added test for bug #46125 "Foreign keys: reorganizing partitions causes
        dangling reference".
     @ sql/sql_partition.cc
        Check if ALTER TABLE REORGANIZE PARTITION which changes
        parent table deletes any rows and if yes emit an appropriate
        error and rollback changes which were done.
        Since the above is not possible for one-phase partition changes
        disable them for parent tables.

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