MySQL Bugs: #21395: Unable to drop compound indexes that contain FKs

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

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.

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;

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> 
)

You just need to drop your "FOREIGN KEYS" before drop the index.

Felipe Lopes

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.

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

When/if MySQL gets its own foreign key implementation, then this bug gets fixed automatically. Setting the status to 'To be fixed later'.

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.

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