Bug #51451 ALTER TABLE DROP INDEX, ADD INDEX executed in opposite order
Submitted: 24 Feb 2010 9:57 Modified: 27 Apr 2010 13:58
Reporter: Marko Mäkelä Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb

[24 Feb 2010 9:57] Marko Mäkelä
Description:
InnoDB does not allow duplicate index names on the same table. In InnoDB Plugin, ha_innobase::add_index(), there is disabled assertion that checks for duplicate index names. The assertion is disabled, because mysql_alter_table() in 5.1 will first call add_index() and then drop_index(), no matter in which order the operations are specified in the ALTER TABLE statement.

If InnoDB is killed between the completion of ha_innobase::add_index() and the completion of ha_innobase::final_drop_index(), the InnoDB data dictionary may contain a duplicate index definition.

How to repeat:
Enable the call dict_table_check_for_dup_indexes(prebuilt->table) in storage/innodb_plugin/handler/handler0alter.cc (define UNIV_DEBUG in storage/innodb_plugin/include/univ.i) and execute the following:

create table t(a int primary key,b int)engine=innodb;
alter table t add index b(b);
alter table t drop index b, add index b(b);

Suggested fix:
Try to execute the add_index() and drop_index() in the order specified in the ALTER TABLE statement. The current practice of always executing ADD first will cause this problem, and changing this to always DROP first would cause problems when trying to change index definitions that are needed for a FOREIGN KEY constraint.

If changing the 5.1 mysql_alter_table() is out of the question, then InnoDB Plugin could return a (highly counterintuitive) error for the

alter table t drop index b, add index b(b);

saying that creating index b would have a duplicate name, even though the user did request to DROP INDEX first.
[24 Feb 2010 12:43] Marko Mäkelä
I submitted an InnoDB change for review that prevents the creation of duplicate indexes. I had to adjust a few tests for the additional limitation. The limitation would have to be lifted in MySQL core, as in "Suggested fix".
[24 Feb 2010 14:31] MySQL Verification Team
Thank you for the bug report. Could you please provide the diff of storage/innodb_plugin/handler/handler0alter.cc  and storage/innodb_plugin/include/univ.i? I wasn't able to compile on Windows X64 so I assume I did something wrong. Thanks in advance.
[9 Mar 2010 16:35] Mikhail Izioumtchenko
feedback was provided I believe, setting status to Open
[11 Mar 2010 0:59] MySQL Verification Team
Thank you for the feedback.
[11 Mar 2010 12:06] Marko Mäkelä
This is related to Bug #49838.
[6 Apr 2010 7:58] Bugs System
Pushed into 5.1.46 (revid:sergey.glukhov@sun.com-20100405111026-7kz1p8qlzglqgfmu) (version source revid:svoj@sun.com-20100401151005-c6re90vdvutln15d) (merge vers: 5.1.46) (pib:16)
[8 Apr 2010 15:09] Vasil Dimov
I think this is irrelevant after the fix of Bug#49838 because now MySQL does copy the table if the same index is dropped and added in the same ALTER.
[27 Apr 2010 13:58] Marko Mäkelä
Closing as a duplicate of Bug #49838.
[5 May 2010 15:10] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:38] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:06] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[15 Jun 2010 8:15] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:32] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[17 Jun 2010 12:15] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:02] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:43] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)