Bug #45985 Foreign keys: crash if drop duplicate index
Submitted: 7 Jul 2009 0:12 Modified: 7 Jul 2009 8:40
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:6.1.0-alpha-debug-log OS:Linux (SUSE 10.2)
Assigned to: Dmitry Lenev CPU Architecture:Any

[7 Jul 2009 0:12] Peter Gulutzan
Description:
I'm using mysql-6.1-fk-stage.
I start the server with mysqld --foreign-key-all-engines=1.

I create a two-column parent table.
I create a two-column child table.
I create a redundant index on the parent table.
I drop the index that I just created.
Crash.

How to repeat:
create table t1 (s1 int not null, s2 int not null) engine=innodb;
create unique index i1 on t1 (s1,s2);
create table t2 (s1 int, s2 int, foreign key (s1,s2) references t1 (s1,s2)) engine=innodb;
create unique index i2 on t1 (s1,s2);
drop index i2 on t1;
[7 Jul 2009 7:01] Dmitry Lenev
Verified as described.
[7 Jul 2009 8:10] Dmitry Lenev
Investigation shows that the same crash (i.e. caused by the same reason) is repeatable with simpler test case:

drop table if exists t1, t2;
create table t1 (s1 int not null, s2 int not null,
                 unique i1 (s1, s2));
create table t2 (s1 int, s2 int,
                 key j1 (s1, s2),
                 constraint c foreign key (s1, s2) references t1 (s1, s2));
drop index i1 on t1;
drop index j1 on t2;
drop tables t1, t2;

So actually the fact that index/unique index is redundant does not matter,
what matters is the fact that index is multi-column.
[7 Jul 2009 8:30] Bugs System
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/78082

2735 Dmitry Lenev	2009-07-07
      Bug #45985 "Foreign keys: crash if drop duplicate index".
      
      In --foreign-key-all-engines=1 mode server crashed if one tried to
      drop multi-column index which might have been used by a foreign key.
      
      This crash was caused by simple coding error. Code which detects
      if we are trying to drop index used by a foreign key was iterating
      through wrong array.
      
      The fix is simply use correct array for this.
[7 Jul 2009 8:40] Dmitry Lenev
Fix for this bug was pushed into mysql-6.1-fk-stage tree. Since the bug was reported against tree which is not publicly available yet I am simply closing this report.
(The review will happen as part of review for the whole milestone 13).