| 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: | |
| 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 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.
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.