Bug #21395 Unable to drop compound indexes that contain FKs
Submitted: 1 Aug 2006 16:42 Modified: 19 Jan 2009 10:56
Reporter: Cristian Gafton Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.22/4.1;5.0;5.1BK OS:Any
Assigned to: Heikki Tuuri CPU Architecture:Any

[1 Aug 2006 16:42] Cristian Gafton
Description:
I think this is best described by example:

drop table if exists foo;
drop table if exists foo1;
drop table if exists foo2;

create table foo1(id integer primary key);
create table foo2(id integer primary key);

create table foo(
    id1 integer not null,
    id2 integer not null,
    constraint foo_id1_fk foreign key(id1) references foo1(id),
    constraint foo_id2_fk foreign key(id2) references foo2(id)
);

create index fooIdx on foo(id1, id2);

-- this one fails. WHY?
alter table foo drop index fooIdx;

How to repeat:
SQL example provided in the bug report.
[1 Aug 2006 16:44] Cristian Gafton
I am using InnoDB:

drop table if exists foo;
drop table if exists foo1;
drop table if exists foo2;

create table foo1(id integer primary key) Engine InnoDB;
create table foo2(id integer primary key) Engine InnoDB;

create table foo(
   id1 integer not null,
   id2 integer not null,
   constraint foo_id1_fk foreign key(id1) references foo1(id),
   constraint foo_id2_fk foreign key(id2) references foo2(id)
) Engine InnoDB;

create index fooIdx on foo(id1, id2);

-- this one fails. WHY?
alter table foo drop index fooIdx;
[2 Aug 2006 18:04] MySQL Verification Team
Thank you for the bug report. Verified as described:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.25-debug

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

mysql> drop table if exists foo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists foo1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists foo2;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 
mysql> create table foo1(id integer primary key) Engine= InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> create table foo2(id integer primary key) Engine= InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> create table foo(
    ->    id1 integer not null,
    ->    id2 integer not null,
    ->    constraint foo_id1_fk foreign key(id1) references foo1(id),
    ->    constraint foo_id2_fk foreign key(id2) references foo2(id)
    -> ) Engine= InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> create index fooIdx on foo(id1, id2);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table foo drop index fooIdx;
ERROR 1025 (HY000): Error on rename of './db2/#sql-18eb_3' to './db2/foo' (errno: 150)
mysql> 
)
[12 Sep 2006 22:05] Felipe Lopes
You just need to drop your "FOREIGN KEYS" before drop the index.

Felipe Lopes
[12 Sep 2006 22:53] Cristian Gafton
The "just need to drop foreign keys" part is what this bug is about. Any index created after the table was defined - like in the example I gave - should be able to be dropped without having it dependent on how the table was defined.

In more complex examples, "just dropping the foreign keys" is not something that works.
[8 Nov 2006 14:55] Heikki Tuuri
Hi!

The reason for the unexpected behavior probably is that when the compound index is created, MySQL removes the automatically created index on id1 in table foo. The compound index becomes the index 'supporting' the foreign key constraint.

The fix would be to make MySQL to automatically create a new index on id1 if the compound index is dropped.

Regards,

Heikki
[16 Apr 2007 12:07] Heikki Tuuri
When/if MySQL gets its own foreign key implementation, then this bug gets fixed automatically. Setting the status to 'To be fixed later'.
[29 Sep 2008 18:43] Gordon Shannon
Giving the benefit of the doubt, I can forgive the developers for not fixing this bug in 2 years -- perhaps it is not easy to fix and other things have higher priority.  What I cannot understand is the fact that there does not exist the will to even fix the completely incorrect error message that appears when this problem is hit (cannot rename file), or the other similar errors around alter table with foreign keys.  We in the community are left scratching our heads wondering why it is so difficult to generate a meaningful error message.  It's been over 2 years since this bug was reported. It really makes mysql look ridiculous.
[19 Jan 2009 10:56] Marko Mäkelä
This bug has been fixed in the InnoDB Plugin 1.0.2, which was released in December 2008:

alter table foo drop index fooIdx;
ERROR 1553 (HY000): Cannot drop index 'fooIdx': needed in a foreign key constraint