Bug #74263 Index comment in ADD INDEX is ignored
Submitted: 8 Oct 2014 9:22 Modified: 4 Nov 2014 15:34
Reporter: Mike Lischke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6, 5.7, 5.6.22, 5.7.6 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[8 Oct 2014 9:22] Mike Lischke
Description:
Altering an index comment by altering a table (dropping and re-creating the same index in the same command) doesn't work as documented. In fact the new comment is simply ignored and the existing one stays unchanged.

How to repeat:
Create a table like this:

CREATE TABLE test.`actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`) comment 'Test + more'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Check the index:

mysql> show index from actor \G
*************************** 1. row ***************************
        Table: actor
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment: Test + more
2 rows in set (0.00 sec)

Alter the index:

mysql> ALTER TABLE `test`.`actor` DROP INDEX `idx_actor_last_name` ,ADD INDEX `idx_actor_last_name` (`last_name` ASC)  COMMENT 'Test';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check the index again:

mysql> show index from actor \G
*************************** 1. row ***************************
        Table: actor
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment: Test + more
2 rows in set (0.00 sec)

or

mysql> show create table actor \G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`) COMMENT 'Test + more'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

As you can see the index comment did not change.

Suggested fix:
Fix DROP INDEX/ADD INDEX in ALTER TABLE.
[8 Oct 2014 10:37] MySQL Verification Team
Hello Mike,

Thank you for the bug report and test case.
Confirmed this behavior on 5.6.22 and 5.7.6 with mysql client and even with latest WB version.

Thanks,
Umesh
[8 Oct 2014 10:38] MySQL Verification Team
// 5.6.22/ 5.7.6

mysql> CREATE TABLE test.`actor` (
    ->   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->   `first_name` varchar(45) NOT NULL,
    ->   `last_name` varchar(45) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`actor_id`),
    ->   KEY `idx_actor_last_name` (`last_name`) comment 'Test + more'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`) COMMENT 'Test + more'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show index from actor \G
*************************** 1. row ***************************
        Table: actor
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment: Test + more
2 rows in set (0.00 sec)

mysql> ALTER TABLE `test`.`actor`
    -> DROP INDEX `idx_actor_last_name` ,
    -> ADD INDEX `idx_actor_last_name` (`last_name` ASC)  COMMENT 'Test';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from actor \G
*************************** 1. row ***************************
        Table: actor
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment: Test + more
2 rows in set (0.00 sec)

mysql> ALTER TABLE `test`.`actor` DROP INDEX `idx_actor_last_name` ,ADD INDEX `idx_actor_last_name` (`last_name` ASC)  COMMENT 'Test';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`) COMMENT 'Test + more'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Workaround I noticed was, first drop index and add later with updated comment

mysql>
mysql> ALTER TABLE `test`.`actor` DROP INDEX `idx_actor_last_name`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `test`.`actor` ADD INDEX `idx_actor_last_name` (`last_name` ASC)  COMMENT 'Test';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`) COMMENT 'Test'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[8 Oct 2014 10:41] MySQL Verification Team
// 5.5.41 - not affected

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.41                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.41-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql>
mysql> CREATE TABLE test.`actor` (
    ->   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->   `first_name` varchar(45) NOT NULL,
    ->   `last_name` varchar(45) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`actor_id`),
    ->   KEY `idx_actor_last_name` (`last_name`) comment 'Test + more'
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.11 sec)

mysql> show index from actor \G
*************************** 1. row ***************************
        Table: actor
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment: Test + more
2 rows in set (0.00 sec)

mysql> ALTER TABLE `test`.`actor` DROP INDEX `idx_actor_last_name` ,ADD INDEX `idx_actor_last_name` (`last_name` ASC)  COMMENT 'Test';
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from actor \G
*************************** 1. row ***************************
        Table: actor
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment: Test
2 rows in set (0.00 sec)

mysql> show create table actor\G
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`) COMMENT 'Test'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[4 Nov 2014 15:34] Paul DuBois
Noted in 5.7.6 changelog.

Any index comment specified for ALTER TABLE ... ADD INDEX was
ignored.