Description:
MySQL is allowing the "USING HASH" modifier when adding an index using the ALTER TABLE command on an InnoDB table. It should fail, since InnoDB does not support HASH indexes.
How to repeat:
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`string` text,
PRIMARY KEY (`num1`),
KEY `string_idx` (`string`(16)) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE t1 DROP INDEX string_idx;
Query OK, 9 rows affected (0.19 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`string` text,
PRIMARY KEY (`num1`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> ALTER TABLE t1 ADD INDEX string_idx USING HASH (string(8));
Query OK, 9 rows affected (0.12 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num1` int(10) unsigned NOT NULL AUTO_INCREMENT,
`string` text,
PRIMARY KEY (`num1`),
KEY `string_idx` (`string`(8)) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW INDEXES FROM t1\G
*************************** 1. row ***************************
Table: t1
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: num1
Collation: A
Cardinality: 2
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
*************************** 2. row ***************************
Table: t1
Non_unique: 1
Key_name: string_idx
Seq_in_index: 1
Column_name: string
Collation: A
Cardinality: 2
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
2 rows in set (0.00 sec)
Suggested fix:
MySQL should throw a warning or error and remove the USING HASH modifier. This is confusing and incorrect since the index is actually using a BTREE according to the SHOW INDEXES command.
Description: MySQL is allowing the "USING HASH" modifier when adding an index using the ALTER TABLE command on an InnoDB table. It should fail, since InnoDB does not support HASH indexes. How to repeat: mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `num1` int(10) unsigned NOT NULL AUTO_INCREMENT, `string` text, PRIMARY KEY (`num1`), KEY `string_idx` (`string`(16)) USING HASH ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> ALTER TABLE t1 DROP INDEX string_idx; Query OK, 9 rows affected (0.19 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `num1` int(10) unsigned NOT NULL AUTO_INCREMENT, `string` text, PRIMARY KEY (`num1`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> ALTER TABLE t1 ADD INDEX string_idx USING HASH (string(8)); Query OK, 9 rows affected (0.12 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `num1` int(10) unsigned NOT NULL AUTO_INCREMENT, `string` text, PRIMARY KEY (`num1`), KEY `string_idx` (`string`(8)) USING HASH ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> SHOW INDEXES FROM t1\G *************************** 1. row *************************** Table: t1 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: num1 Collation: A Cardinality: 2 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: *************************** 2. row *************************** Table: t1 Non_unique: 1 Key_name: string_idx Seq_in_index: 1 Column_name: string Collation: A Cardinality: 2 Sub_part: 8 Packed: NULL Null: YES Index_type: BTREE Comment: 2 rows in set (0.00 sec) Suggested fix: MySQL should throw a warning or error and remove the USING HASH modifier. This is confusing and incorrect since the index is actually using a BTREE according to the SHOW INDEXES command.