Bug #36869 adding hash index on myisam or innodb table
Submitted: 22 May 2008 1:55 Modified: 18 Nov 2010 13:31
Reporter: Jim Grill Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.1.24-rc-log OS:Any (probably any OS)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[22 May 2008 1:55] Jim Grill
Description:
Incorrectly attempting to add a hash index on a myisam or innodb table does not result in an error or warning.  Show create table incorrectly reports a hash index while show indexes reports btree.

Tested with 5.1.23 and 24 on Solaris 10 x86 and RHL 5.

How to repeat:
mysql> create table t1 (a int, index(a) using hash);
Query OK, 0 rows affected (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`) USING HASH
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          1 | a        |            1 | a           | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)

The same test with an innodb table yields the same result.

Suggested fix:
attempting to add an unsupported index type should produce an error.  Show create table and show indexes should not be able to show contradicting index information.
[22 May 2008 2:41] Paul Dubois
http://dev.mysql.com/doc/refman/5.0/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.
"

Same applies to index definitions for CREATE TABLE.
[22 May 2008 2:50] Jim Grill
Albeit counterintuitive, IMHO, to invisibly choose a theoretically compatible index type, shouldn't show "create table" and "show indexes" reflect the same index type?

Could that behavior still be a bug?
[22 May 2008 4:16] Valeriy Kravchuk
Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot test -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.24-rc-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t1;
Query OK, 0 rows affected (0.16 sec)

mysql> create table t1 (a int, index(a) using hash) engine=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL,
  KEY `a` (`a`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)

mysql> show indexes from t1\G
*************************** 1. row ***************************
       Table: t1
  Non_unique: 1
    Key_name: a
Seq_in_index: 1
 Column_name: a
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.01 sec)

Indeed, I'd consider different results a minor bug, because of inconsistency.
[7 Oct 2008 20:52] Konstantin Osipov
This behavior is documented and relied upon in replication scenarios.
[9 Oct 2009 12:57] Kristian Köhntopp
Could you please at least add a warning? MySQL does this for example for data truncation and many other silent changes. Here, nothing is changed AND the server lies to me in a SHOW CREATE TABLE. I consider this wrong and a warning is at least in order.
[9 Oct 2009 15:35] Konstantin Osipov
I agree we need to start producing warnings for cases when some DDL clauses are ignored.
Please re-triage as a feature request.
[18 Nov 2010 13:31] Jim Grill
In addition to the warning, I think the differences between SHOW CREATE TABLE and SHOW INDEXES FROM should also be corrected.
[7 Dec 2011 8:49] Shane Bester
Regarding the lack of warnings for ignored syntax, feel free to make a comment on bug #47771