MySQL Bugs: #60689: ALTER TABLE and Incorrect index name error

Bug #60689	ALTER TABLE and Incorrect index name error
Submitted:	29 Mar 2011 14:06	Modified:	12 Oct 2012 23:57
Reporter:	Cyril SCETBON	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.9	OS:	Any (ubuntu lucid x86)
Assigned to:		CPU Architecture:	Any
Tags:	ALTER TABLE, error, INDEX, regression

Description:
When I try to drop and create an index in the same time with alter table I get an error if both indexes use the same first column and have auto-generated index name

How to repeat:
mysql> create table t1(i int,j int,k int);
Query OK, 0 rows affected (0.02 sec)

mysql> alter table t1 add index(i,j);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Mysql is trying to use the same index name for the new one and so tells that there is an error

mysql> alter table t1 drop index i,add index(i,k);
ERROR 1280 (42000): Incorrect index name 'i'

But, If I fix another name for the second one, it works

mysql> alter table t1 drop index i,add index i2 (i,k);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

Suggested fix:
Use an internal method to use a temporary name for the new index, and rename it at the end

or

Generate another name, like autogeneratedname.rand()

This is definitely related to InnoDB only and fix for bug #49838.

It works OK with 5.1.56 (InnoDB, but not plugin), or 5.5.x and MyISAM:

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 16
Server version: 5.5.9 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> drop table t1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t1(i int,j int,k int);
Query OK, 0 rows affected (0.17 sec)

mysql>  alter table t1 add index(i,j);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 drop index i,add index(i,k);
ERROR 1280 (42000): Incorrect index name 'i'
mysql> alter table t1 engine=MyISAM;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 drop index i,add index(i,k);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

WorkBench will generate this error if it is asked to create Foreign Keys.

sorry, but it's not fixed in 5.5.9 in your example. You said it's fixed in 5.5.x. what does the x mean ? In the fix you noted it's said to be fixed in 5.5.4 !!!

sorry, you meant using MYISAM in 5.5.x ? if that's it, I definitely agree...

I associated this bug number with the existing changelog feature entry for online DDL. Rather than creating a new bug entry in the changelog.

(Online DDL being a new feature added in MySQL 5.6.6, so that's the changelog section it's under.)