MySQL Bugs: #54385: #1280 Incorrect index name

Bug #54385	#1280 Incorrect index name
Submitted:	9 Jun 2010 22:56	Modified:	10 Jun 2010 4:34
Reporter:	Ian Lord	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.4-m3	OS:	FreeBSD (7.2 (Probably not relevant))
Assigned to:		CPU Architecture:	Any
Tags:	1280, INDEX, innodb, name

Description:
Hi,

Dropping and recreating an index as a single step sometime doesnt work on 5.5.4-m3. The exact same query works on 5.0/5.1/5.4 without any problem.

It seems to happen only when there is a primary key, the same key name is reused and with InnoDB only. Myisam works fine.

The error returned is:
#1280 - Incorrect index name 'foo'

How to repeat:
#### Create the test table ####
CREATE TABLE `tTable1` (
`pkiTable1ID` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
`foo` TINYINT NOT NULL ,
`bar` TINYINT NOT NULL ,
PRIMARY KEY (`pkiTable1ID`),
UNIQUE (
`foo`, `bar`
)
) ENGINE = INNODB ;

#### Alter the unique Index ####
ALTER TABLE `tTable1` DROP INDEX `foo` ,
ADD UNIQUE `foo` ( `foo` );

This is already fixed in current mysql-trunk tree:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> CREATE TABLE `tTable1` (
    -> `pkiTable1ID` mediumint( 8 ) unsigned NOT NULL AUTO_INCREMENT ,
    -> `foo` TINYINT NOT NULL ,
    -> `bar` TINYINT NOT NULL ,
    -> PRIMARY KEY (`pkiTable1ID`),
    -> UNIQUE (
    -> `foo`, `bar`
    -> )
    -> ) ENGINE = INNODB ;
Query OK, 0 rows affected (0.45 sec)

mysql> ALTER TABLE `tTable1` DROP INDEX `foo` ,
    -> ADD UNIQUE `foo` ( `foo` );
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check bug #44571. I think it is related.

In connection with another reported issue with this error, tested on 5.1.47 also with both builtin and plugin 1.0.8, both working fine. Ref bug #55465