MySQL Bugs: #36869: adding hash index on myisam or innodb table

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

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.

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.

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?

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.

This behavior is documented and relied upon in replication scenarios.

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.

I agree we need to start producing warnings for cases when some DDL clauses are ignored.
Please re-triage as a feature request.

In addition to the warning, I think the differences between SHOW CREATE TABLE and SHOW INDEXES FROM should also be corrected.

Regarding the lack of warnings for ignored syntax, feel free to make a comment on bug #47771