Bug #61146 "Incorrect index name" error when using InnoDB fast add/drop index
Submitted: 12 May 2011 9:18 Modified: 24 May 2011 23:32
Reporter: Alexey Kopytov Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S3 (Non-critical)
Version:5.1, 5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[12 May 2011 9:18] Alexey Kopytov
Description:
The following fails with InnoDB plugin in both 5.1 and 5.5, but passes with built-in InnoDB in 5.1:

mysql> create table t1(a int, b int, primary key (a), key b (b)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t1 drop key b, add key (b, a);
ERROR 1280 (42000): Incorrect index name 'b'

Specifying the index name explicitly does work:

mysql> alter table t1 drop key b, add key b (b, a);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to repeat:
create table t1(a int, b int, primary key (a), key b (b)) engine=innodb;
alter table t1 drop key b, add key (b, a);
[12 May 2011 11:04] Valeriy Kravchuk
Thank you for the bug report. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.5.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t1(a int, b int, primary key (a), key b (b)) engine=innodb;
Query OK, 0 rows affected (0.11 sec)

mysql> alter table t1 drop key b, add key (b, a);
ERROR 1280 (42000): Incorrect index name 'b'
[24 May 2011 23:32] Omer Barnir
This bug s a duplicate of bug#54927 that is fixed in an upcoming release - the fix will not be back ported as a good workaround (split the drop and add to two separate commands exists.