MySQL Bugs: #37910: Dropping unrelated index fails if fk on first col

Bug #37910	Dropping unrelated index fails if fk on first col
Submitted:	6 Jul 2008 23:58	Modified:	21 Aug 2008 12:29
Reporter:	Arjen Lentz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.51a, 5.0.62, 5.1.25	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	drop index, foreign key, innodb

Description:
If there's an additional index (composite in our testcase) on a column that is also part of a fk, then dropping the additional index actually fails.

Verified by me on 5.0.51a (by Arjen), as well as 5.0.58 (by PeterZ)

How to repeat:
CREATE TABLE supplier (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;
CREATE TABLE location (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(10) NOT NULL,
 supplier_id INT UNSIGNED NOT NULL
) ENGINE=InnoDB;
ALTER TABLE location ADD CONSTRAINT FK_SUPPLIER FOREIGN KEY FK_SUPPLIER (supplier_id)
   REFERENCES supplier (id) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE location ADD UNIQUE INDEX unique_name_to_supplier (supplier_id,name);

ALTER TABLE location DROP INDEX unique_name_to_supplier;
# This last command returns:
# ERROR 1025 (HY000): Error on rename of './test/#sql-8b_1' to './test/location' (errno: 150)

Suggested fix:
Fix the check for an index on fk so that it allows dropping of other indexes.
Such indexes with either be composite, or a duplicate of an existing index.

Thank you for a bug report. Verified just as described with 5.0.62 and 5.1.25-rc.

Assigning this to Marko. He should check why InnoDB refuses to drop the index though there exists another index that the FK could use.

The problem is as follows.

The InnoDB foreign key implementation requires that the referenced and referencing columns are indexed. When a FOREIGN KEY constraint is defined, InnoDB used to complain if an index was missing. Starting with some 4.1 or 5.0 release, InnoDB automatically creates the required index on the referencing columns. But InnoDB does not distinguish automatically created and user-created indexes. Also, when a user-created index "covers" a previously defined index, MySQL/InnoDB will silently discard the "weaker" index. What happens in the test case is this:

ALTER TABLE location ADD CONSTRAINT FK_SUPPLIER ... implicitly creates a non-unique index on location.supplier_id. Now "location" has two indexes:
PRIMARY KEY (id), KEY (supplier_id)

ALTER TABLE location ADD UNIQUE INDEX unique_name_to_supplier (supplier_id,name)
is stricter than KEY (supplier_id). Two indexes remain on "location":
PRIMARY KEY(id), UNIQUE KEY(supplier_id, name).
The FOREIGN KEY constraint continues to work, because (supplier_id) is the first column of the UNIQUE KEY.

ALTER TABLE location DROP INDEX unique_name_to_supplier
attempts to drop the UNIQUE KEY (supplier_id, name), leaving only the
PRIMARY KEY(id). The FOREIGN KEY constraint will not work, and thus the
ALTER TABLE will fail.

Workaround: create the required (non-unique) index on supplier_id in the same ALTER TABLE statement where dropping the key:
ALTER TABLE location DROP INDEX unique_name_to_supplier,ADD INDEX (supplier_id);

Possible fix: after dropping an index, automatically create any indexes required by foreign keys. This may be non-trivial, because I seem to remember that the automatic addition of keys required changes to the MySQL layer. This fix could introduce another problem: what to do in response to DROP INDEX (supplier_id)? Complain that it is needed by a foreign key constraint, or silently drop and re-create the index?

When a FOREIGN KEY is created, MySQL silently creates the required INDEX on the referencing table. MySQL also silently drops that index when the user creates a "stronger" index. So, MySQL should silently re-create the required INDEX when the user drops the "stronger" index. This bug cannot be fixed in a storage engine.

Therefore, I am assigning this bug away from InnoDB and myself. See my previous comment for a workaround.

Side note: after Bug #38786 is fixed, the InnoDB plugin should issue a more helpful error message:
ERROR 1553 (HY000): Cannot drop index 'unique_name_to_supplier': needed in a foreign key constraint

On another thought, implicit changes to the data dictionary (such as creating indexes when creating FOREIGN KEY constraints, dropping indexes when creating "stronger" indexes, or creating an index when dropping a "stronger" index that is needed by a foreign key) are a bad idea. There are good reasons to change this in MySQL 6.0:

* It just seems odd to drop an index as a side-effect of creating an index, or vice versa.

* It is simpler not to have any implicit operations.

* It makes more sense for other engines and not to create indexes implicitly.

* It prepares the way for the upcoming native MySQL foreign key implementation.

* It allows multiple indexes to coexist that the optimizer may benefit from.

* It eliminates the confusion of implicitly generated indexes and odd names.

All good points, Marko.  Just to further add to the rationale for removing the implicit creation and dropping of indexes ...

1. It is less work to remove existing code that implicitly creates or drops indexes than it is to add additional cases where indexes are implicitly created or dropped.

2. InnoDB will (continue to) choose the most appropriate (remaining) index when an ADD or DROP index occurs and will prevent the last remaining useful index from being dropped.  This treatment frees MySQL from having to worry about it.  No changes would be needed in InnoDB for 6.0 to live with this change.

Could please somebody tell, if there is any reference entries, e.g. in information_schema, to find out foreign keys and corresponding indexes. I'm often confused trying to find out the right index.
I no such entry exists, which modell uses mysql to find the "strongest" index.
Thank you in advance!