MySQL Bugs: #62544: ALTER TABLE DROP INDEX ADD INDEX

Bug #62544	ALTER TABLE DROP INDEX ADD INDEX
Submitted:	26 Sep 2011 13:27	Modified:	18 Oct 2012 23:04
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.5.16, 5.5.17	OS:	Any
Assigned to:	Marko Mäkelä	CPU Architecture:	Any
Tags:	qc, regression

Description:
It looks like a regression bug in the 5.5 tree here with an "ALTER TABLE DROP INDEX ADD INDEX" construction.  It works in 5.1.59 (with the 'built in' InnoDB).  Not with 5.5.16. No issue with MyISAM.

Further colleages tell me that in 5.5.9 we also had this error/bug occurring but not in 5.5.15.

So 5.5.15 >> 5.5.16 reintroduced a bug that was fixed between 5.5.9 and 5.5.19 it seems?

How to repeat:
-- MySQL 5.5.16

DROP TABLE IF EXISTS test;

CREATE TABLE test(
a INT,
b INT,
c INT,
INDEX a(c)
) ENGINE INNODB;

ALTER TABLE test
DROP INDEX a,
ADD INDEX(a, c);
-- Error Code: 1280
-- Incorrect index name 'a'

ALTER TABLE test
DROP INDEX a;

ALTER TABLE test
ADD INDEX(a, c);

ALTER TABLE test
DROP INDEX a,
ADD INDEX(a, c);
-- this time success with statement that failed before ??
-- I give this more question marks ??????? :-)

-- MyISAM not affected
DROP TABLE IF EXISTS test;

CREATE TABLE test(
a INT,
b INT,
c INT,
INDEX a(c)
) ENGINE MYISAM;

ALTER TABLE test
DROP INDEX a,
ADD INDEX(a, c);
-- works fine !!

-- MySQL 5.1.59:

DROP TABLE IF EXISTS test;

CREATE TABLE test(
a INT,
b INT,
c INT,
INDEX a(c)
) ENGINE INNODB;

ALTER TABLE test
DROP INDEX a,
ADD INDEX(a, c);
-- works fine !!

Suggested fix:
no idea.

I have a 'little less stupid' test case here (where the DROP+ADD actually makes sense) and what may also help to identify the problem.

DROP TABLE IF EXISTS test;

CREATE TABLE test(
a INT,
b INT,
c INT,
INDEX a(c)
) ENGINE INNODB;

ALTER TABLE test
DROP INDEX a,
ADD INDEX(a, b, c);
-- Error Code: 1280
-- Incorrect index name 'a'
 
ALTER TABLE test
DROP INDEX a;

ALTER TABLE test
ADD INDEX(a, b, c);

ALTER TABLE test
DROP INDEX a,
ADD INDEX(a, c);
-- Error Code: 1280
-- Incorrect index name 'a'
 
ALTER TABLE test
DROP INDEX a, 
ADD INDEX(a, b, c);
-- success

Please rephrase:

So 5.5.15 >> 5.5.16 reintroduced a bug that was fixed between 5.5.9 and 5.5.19 it seems?

>>>

So 5.5.15 >> 5.5.16 reintroduced a bug that was fixed between 5.5.9 and 5.5.15 it seems?

Thank you for the problem report. Verified with 5.5.17 on Mac OS X also:

macbook-pro:5.5 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 21
Server version: 5.5.17-debug Source distribution

Copyright (c) 2000, 2011, 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 IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE TABLE test(
    -> a INT,
    -> b INT,
    -> c INT,
    -> INDEX a(c)
    -> ) ENGINE INNODB;
Query OK, 0 rows affected (0.13 sec)

mysql> 
mysql> ALTER TABLE test
    -> DROP INDEX a,
    -> ADD INDEX(a, c);
ERROR 1280 (42000): Incorrect index name 'a'
mysql> ALTER TABLE test
    -> DROP INDEX a;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test
    -> ADD INDEX(a, c);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE test
    -> DROP INDEX a,
    -> ADD INDEX(a, c);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

<a href=http://pharmacynorthern.com/products/levitra.htm><img>http://legalusdrugstore.com/9.jpg</img></a>                                                 
pharmacy online pain pills http://pharmacynorthern.com/categories/anti-diabetic.htm rewards in critical care pharmacy <a href=http://pharmacynorthern.com/products/norvasc.htm>norvasc</a>                                                 
hipaa and pharmacy http://pharmacynorthern.com/products/innopran-xl.htm top online mexico pharmacy <a href=http://pharmacynorthern.com/products/grifulvin-v.htm>phen fen mexican pharmacy</a>                                                 
kansas pharmacy interview participants http://pharmacynorthern.com/products/torsemide.htm south river compounding pharmacy <a href=http://pharmacynorthern.com/products/accupril.htm>accupril</a>                                                 
home town pharmacy http://pharmacynorthern.com/products/rumalaya.htm acend pharmacy <a href=http://pharmacynorthern.com/products/premarin.htm>anthem rx pharmacy</a>

http://worlddatingsingles.info/seeking/springs-male-seeking-29-minnesota  <a href=http://worlddatingsingles.info/matchmaker/matchmaker-canada></a>   
 http://worlddatingsingles.info/brides/bludging-brides  <a href=http://worlddatingsingles.info/relationships/an-essay-on-overcomin-relationships></a>       
 http://worlddatingsingles.info/matchmaker/matchmaker-bangelore  <a href=http://worlddatingsingles.info/love/jarrod-birmingham-blog-journal-love></a>   
 http://worlddatingsingles.info/brides/black-veil-brides-the-gunsling  <a href=http://worlddatingsingles.info/romance/background-my-chemical-romance></a>

Added to changelog for 5.6.6: 

An ALTER TABLE statement for an InnoDB table that dropped one index
and create another could fail with an error code 1280, and displaying
the wrong index name in the message.