Bug #51451 ALTER TABLE DROP INDEX, ADD INDEX executed in opposite order

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.

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".

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.

feedback was provided I believe, setting status to Open

Thank you for the feedback.

This is related to Bug #49838.

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)

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.

Closing as a duplicate of Bug #49838.

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)

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)

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)

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)