MySQL Bugs: #26305: Foreign Keys: Incorrect error reporting when dropping index tied by an FK

Bug #26305	Foreign Keys: Incorrect error reporting when dropping index tied by an FK
Submitted:	13 Feb 2007 0:25	Modified:	12 Jul 2012 14:53
Reporter:	Alex Rytov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.15, 5.0 BK, 4.1 BK	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	error, foreign key, INDEX

Description:

When you try to drop an index and there is a FOREIGN KEY relying on this index (the index in the the referring table of the FK relationship), the server returns an error. This is to be expected and is in fact documented somewhere in the manual.

However, the error message looks like this:

  "Error on rename of './test/#sql-12f4_20' to './test/t1' (errno: 150)"

It is not very specific, in fact it doesn't tell anything at all about why the index can't be removed and doesn't even mention the index name.  

Could you please change the error message to something more helpful like:

 "The index XXX is in use by FK YYYY and can't be dropped" 

How to repeat:

create table t(
  i int not null key,
  j int,
  index ix1(j),
  foreign key (j) references t(i)
)
  engine innodb;

drop index ix1 on t;   -- causes the error

Thank you for the report.

Verified as described. All versions are affected.

the errno 150 gives more info,it can be found in the file include/my_base.h(#define HA_ERR_CANNOT_ADD_FOREIGN 150    /* Cannot add a foreign key constr. */),
but it can't be found in the manual? also you can give the detail info from the server.

tail -f var/log/master.err
071106 11:17:31  InnoDB: Error: in ALTER TABLE `test`.`t`
InnoDB: has or is referenced in foreign key constraints
InnoDB: which are not compatible with the new table definition.
...

In 5.6.6 after the fix for Bug#64617, you now get:
query 'drop index ix1 on t' failed: 1553: Cannot drop index 'ix1': needed in a foreign key constraint