Bug #40710 "USING HASH" ignored when creating indexes on InnoDB tables
| Submitted: | 13 Nov 2008 17:33 | Modified: | 13 Nov 2008 18:34 |
|---|---|---|---|
| Reporter: | Tim Soderstrom | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.1.29rc | OS: | Any (OS X (from vanilla source), Gentoo) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | alter, hash, indexes | ||
[13 Nov 2008 17:36]
Paul DuBois
http://dev.mysql.com/doc/refman/5.1/en/create-index.html says: "If you specify an index type that is not legal for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type."
[13 Nov 2008 17:38]
Tim Soderstrom
Fair enough, but MySQL shouldn't fail silently. It should flag a warning if nothing else. The fact that it is documented doesn't make it right :)
[13 Nov 2008 18:34]
Valeriy Kravchuk
Looks like a reasonable feature request for me.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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.