MDEV-7318 RENAME INDEX

This patch adds support of RENAME INDEX operation to the ALTER TABLE
statement. Code which determines if ALTER TABLE can be done in-place
for "simple" storage engines like MyISAM, Heap and etc. was updated to
handle ALTER TABLE ... RENAME INDEX as an in-place operation. Support
for in-place ALTER TABLE ... RENAME INDEX for InnoDB was covered by
MDEV-13301.

Syntax changes
==============

A new type of <alter_specification> is added:

<rename index clause> ::= RENAME ( INDEX | KEY ) <oldname> TO <newname>

Where <oldname> and <newname> are identifiers for old name and new
name of the index.

Semantic changes
================

The result of "ALTER TABLE t1 RENAME INDEX a TO b" is a table which
contents and structure are identical to the old version of 't1' with
the only exception index 'a' being called 'b'.

Neither <oldname> nor <newname> can be "primary". The index being
renamed should exist and its new name should not be occupied
by another index on the same table.

Related to: WL#6555, MDEV-13301

1 files changed, 989 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/innodb_rename_index.result b/mysql-test/suite/innodb/r/innodb_rename_index.result
new file mode 100644
index 00000000000..482070c4ffc
--- /dev/null
+++ b/mysql-test/suite/innodb/r/innodb_rename_index.result
@@ -0,0 +1,989 @@
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+ALTER TABLE t RENAME INDEX i1 TO GEN_CLUST_INDEX;
+ERROR 42000: Incorrect index name 'GEN_CLUST_INDEX'
+ALTER TABLE t RENAME INDEX i1 TO i1;
+ALTER TABLE t RENAME INDEX aa TO aa;
+ERROR 42000: Key 'aa' doesn't exist in table 't'
+# combination: aaaa
+ALTER TABLE t ADD INDEX i4(f), DROP INDEX i4, RENAME INDEX i4 TO i4;
+ERROR 42000: Key 'i4' doesn't exist in table 't'
+# combination: aabb
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX i2 TO i2;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX bb TO bb;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX bb TO bb;
+ERROR 42000: Key 'bb' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i2 TO i2;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i2` (`c`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`),
+  KEY `i1` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i1	f
+test/t	i2	c
+test/t	i3	d
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+# combination: abcc
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX cc TO cc;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX i3 TO i3;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO cc;
+ERROR 42000: Key 'cc' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i3;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i1` (`b`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`),
+  KEY `aa` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	aa	f
+test/t	i1	b
+test/t	i3	d
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+# combination: abaa
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i1, RENAME INDEX aa TO aa;
+ERROR 42000: Key 'aa' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i1;
+ERROR 42000: Duplicate key name 'i1'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO i1;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO aa;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+# combination: baaa
+ALTER TABLE t ADD INDEX i2(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX bb(f), DROP INDEX i1, RENAME INDEX i1 TO i1;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i2(f), DROP INDEX aa, RENAME INDEX aa TO aa;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX bb(f), DROP INDEX aa, RENAME INDEX aa TO aa;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), RENAME INDEX aa TO bb;
+ERROR 42000: Key 'aa' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), RENAME INDEX bb TO aa;
+ERROR 42000: Key 'bb' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), RENAME INDEX i2 TO aa;
+ERROR 42000: Duplicate key name 'aa'
+ALTER TABLE t ADD INDEX i1(f), RENAME INDEX i1 TO bb;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `bb` (`b`),
+  KEY `i2` (`c`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`),
+  KEY `i1` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	bb	b
+test/t	i1	f
+test/t	i2	c
+test/t	i3	d
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+# combination: abba
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i2 TO i1;
+ERROR 42000: Key 'i2' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i2 TO aa;
+ERROR 42000: Key 'i2' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX bb TO i1;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX bb TO aa;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+# combination: cabc
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i2 TO i3;
+ERROR 42000: Duplicate key name 'i3'
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i2 TO i3;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX bb TO i3;
+ERROR 42000: Key 'bb' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX bb TO i3;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX i2 TO cc;
+ERROR 42000: Duplicate key name 'cc'
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX i2 TO cc;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX bb TO cc;
+ERROR 42000: Key 'bb' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX bb TO cc;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t DROP INDEX i1, RENAME INDEX i1 TO bb;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t DROP INDEX aa, RENAME INDEX i2 TO aa;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t DROP INDEX aa, RENAME INDEX aa TO i2;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t DROP INDEX i1, RENAME INDEX i4 TO i1;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i2` (`c`),
+  KEY `i3` (`d`),
+  KEY `i1` (`e`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i1	e
+test/t	i2	c
+test/t	i3	d
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+# combination: accb
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO i2;
+ERROR 42000: Key 'i3' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i3 TO bb;
+ERROR 42000: Key 'i3' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO i2;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX cc TO bb;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO i2;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX cc TO bb;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+# combination: aaab
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO bb;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i1, RENAME INDEX i1 TO i2;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX aa, RENAME INDEX aa TO bb;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+# combination: abcd
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO i4;
+ERROR 42000: Key 'cc' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX cc TO dd;
+ERROR 42000: Key 'cc' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO i4;
+ERROR 42000: Key 'cc' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX cc TO dd;
+ERROR 42000: Key 'cc' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
+ERROR 42000: Duplicate key name 'i4'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
+ERROR 42000: Duplicate key name 'i1'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO i4;
+ERROR 42000: Duplicate key name 'i4'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX i3 TO dd;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i1` (`b`),
+  KEY `dd` (`d`),
+  KEY `i4` (`e`),
+  KEY `aa` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	aa	f
+test/t	dd	d
+test/t	i1	b
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+# combination: abab
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i2, RENAME INDEX i1 TO i2;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i2` (`b`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`),
+  KEY `i1` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i1	f
+test/t	i2	b
+test/t	i3	d
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX bb, RENAME INDEX i1 TO bb;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i2, RENAME INDEX aa TO i2;
+ERROR 42000: Key 'aa' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX bb, RENAME INDEX aa TO bb;
+ERROR 42000: Can't DROP INDEX `bb`; check that it exists
+# combination: acbc
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX i2 TO cc;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX cc, RENAME INDEX bb TO cc;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX cc, RENAME INDEX bb TO cc;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX bb TO i3;
+ERROR 42000: Key 'bb' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX bb TO i3;
+ERROR 42000: Key 'bb' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
+ERROR 42000: Duplicate key name 'i1'
+ALTER TABLE t ADD INDEX aa(f), DROP INDEX i3, RENAME INDEX i2 TO i3;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i1` (`b`),
+  KEY `i3` (`c`),
+  KEY `i4` (`e`),
+  KEY `aa` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	aa	f
+test/t	i1	b
+test/t	i3	c
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+# combination: cacb
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO i2;
+ERROR 42000: Key 'cc' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO i2;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX aa, RENAME INDEX cc TO bb;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX i1, RENAME INDEX cc TO bb;
+ERROR 42000: Key 'cc' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO i2;
+ERROR 42000: Duplicate key name 'i2'
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO i2;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX aa, RENAME INDEX i3 TO bb;
+ERROR 42000: Can't DROP INDEX `aa`; check that it exists
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX i1, RENAME INDEX i3 TO bb;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i2` (`c`),
+  KEY `bb` (`d`),
+  KEY `i4` (`e`),
+  KEY `i3` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	bb	d
+test/t	i2	c
+test/t	i3	f
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+# combination: ccab
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO i2;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX i1 TO bb;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO i2;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX cc(f), DROP INDEX cc, RENAME INDEX aa TO bb;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO i2;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX cc, RENAME INDEX aa TO bb;
+ERROR 42000: Can't DROP INDEX `cc`; check that it exists
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO i2;
+ERROR 42000: Duplicate key name 'i2'
+ALTER TABLE t ADD INDEX i3(f), DROP INDEX i3, RENAME INDEX i1 TO bb;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `bb` (`b`),
+  KEY `i2` (`c`),
+  KEY `i4` (`e`),
+  KEY `i3` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	bb	b
+test/t	i2	c
+test/t	i3	f
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+ALTER TABLE t RENAME INDEX i1 TO x;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `x` (`b`),
+  KEY `i2` (`c`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i2	c
+test/t	i3	d
+test/t	i4	e
+test/t	PRIMARY	a
+test/t	x	b
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+ALTER TABLE t RENAME INDEX i1 TO i2;
+ERROR 42000: Duplicate key name 'i2'
+ALTER TABLE t RENAME INDEX foo TO i1;
+ERROR 42000: Key 'foo' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i9 (f), RENAME INDEX i1 TO i8;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i8` (`b`),
+  KEY `i2` (`c`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`),
+  KEY `i9` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i2	c
+test/t	i3	d
+test/t	i4	e
+test/t	i8	b
+test/t	i9	f
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO i9;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i9` (`b`),
+  KEY `i2` (`c`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`),
+  KEY `i1` (`f`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i1	f
+test/t	i2	c
+test/t	i3	d
+test/t	i4	e
+test/t	i9	b
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+a INT,
+b INT,
+c INT,
+d INT,
+e INT,
+f INT,
+PRIMARY KEY (a),
+INDEX i1 (b),
+INDEX i2 (c),
+INDEX i3 (d),
+INDEX i4 (e)
+) ENGINE=INNODB;
+INSERT INTO t SET a = 1;
+ALTER TABLE t ADD INDEX foo (f), RENAME INDEX i1 TO foo;
+ERROR 42000: Duplicate key name 'foo'
+ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX i1;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t ADD INDEX i1 (f), RENAME INDEX i1 TO foo, DROP INDEX foo;
+ERROR 42000: Can't DROP INDEX `foo`; check that it exists
+ALTER TABLE t ADD INDEX foo (f), RENAME INDEX foo TO bar, DROP INDEX foo;
+ERROR 42000: Can't DROP INDEX `foo`; check that it exists
+ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i2 TO x;
+ERROR 42000: Duplicate key name 'x'
+ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO y;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+ALTER TABLE t RENAME INDEX i1 TO x, RENAME INDEX i1 TO x;
+ERROR 42000: Key 'i1' doesn't exist in table 't'
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` int(11) DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  KEY `i1` (`b`),
+  KEY `i2` (`c`),
+  KEY `i3` (`d`),
+  KEY `i4` (`e`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i1	b
+test/t	i2	c
+test/t	i3	d
+test/t	i4	e
+test/t	PRIMARY	a
+DROP TABLE t;
+CREATE TABLE t (
+c1 INT NOT NULL,
+c2 INT NOT NULL,
+c3 INT,
+c4 INT,
+PRIMARY KEY (c1),
+INDEX i1 (c3),
+INDEX i2 (c4)
+) ENGINE=INNODB;
+INSERT INTO t SET c1=1, c2=2;
+ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (c2), RENAME INDEX i1 TO x;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `c1` int(11) NOT NULL,
+  `c2` int(11) NOT NULL,
+  `c3` int(11) DEFAULT NULL,
+  `c4` int(11) DEFAULT NULL,
+  PRIMARY KEY (`c2`),
+  KEY `x` (`c3`),
+  KEY `i2` (`c4`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i2	c4
+test/t	PRIMARY	c2
+test/t	x	c3
+ALTER TABLE t RENAME INDEX i2 TO y, ROW_FORMAT=REDUNDANT;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `c1` int(11) NOT NULL,
+  `c2` int(11) NOT NULL,
+  `c3` int(11) DEFAULT NULL,
+  `c4` int(11) DEFAULT NULL,
+  PRIMARY KEY (`c2`),
+  KEY `x` (`c3`),
+  KEY `y` (`c4`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	PRIMARY	c2
+test/t	x	c3
+test/t	y	c4
+DROP TABLE t;
+CREATE TABLE t (
+c1 INT NOT NULL,
+c2 INT,
+c3 INT,
+INDEX i1 (c2),
+INDEX i2 (c3)
+) ENGINE=INNODB;
+INSERT INTO t SET c1=1;
+ALTER TABLE t ADD PRIMARY KEY (c1), RENAME INDEX i1 TO x;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `c1` int(11) NOT NULL,
+  `c2` int(11) DEFAULT NULL,
+  `c3` int(11) DEFAULT NULL,
+  PRIMARY KEY (`c1`),
+  KEY `x` (`c2`),
+  KEY `i2` (`c3`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT
+t.name AS table_name,
+i.name AS index_name,
+f.name AS column_name
+FROM
+information_schema.innodb_sys_tables t,
+information_schema.innodb_sys_indexes i,
+information_schema.innodb_sys_fields f
+WHERE
+t.name LIKE '%/t' AND
+t.table_id = i.table_id AND
+i.index_id = f.index_id
+ORDER BY 1, 2, 3;
+table_name	index_name	column_name
+test/t	i2	c3
+test/t	PRIMARY	c1
+test/t	x	c2
+DROP TABLE t;
+CREATE TABLE t (a INT, INDEX iiiii (a)) ENGINE=INNODB;
+INSERT INTO t SET a=NULL;
+ALTER TABLE t RENAME INDEX iiiii TO i;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t RENAME INDEX i TO iiiii;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t RENAME INDEX iiiii TO i;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ALTER TABLE t RENAME INDEX i TO iiiii;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+DROP TABLE t;