MySQL Bugs: #73326: Behavior when using a non-permitted index type.

Bug #73326	Behavior when using a non-permitted index type.
Submitted:	18 Jul 2014 11:31	Modified:	18 Jul 2014 11:57
Reporter:	Roland Bouman	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	all, 5.6.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When creating an index we can specify an index type. The doc at http://dev.mysql.com/doc/refman/5.7/en/create-index.html reads:

"
Some storage engines permit you to specify an index type when creating an index. The permissible index type values supported by different storage engines are shown in the following table.
"

The problem is that MySQL issues neither error nor warning when using a "non-permitted" index type. Consider this DDL:

CREATE TABLE `sessions` (
    `id` char(40) COLLATE utf8_unicode_ci NOT NULL,
    `payload` text COLLATE utf8_unicode_ci NOT NULL,
    `last_activity` int(11) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `session_id_unique` (`id`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Note the sessions table is defined using the InnoDB storage engine, and the session_id_unique index is specified USING HASH - but HASH is not listed as a permitted type for the InnoDB engine. Still this can be executed with no error or warning. This is undesirable - at least a warning should be give.

Also the SHOW CREATE TABLE statement returns the DDL as it was entered, not showing the actual index type, but rather the specified one. (The information schema.STATISTICS table does list the actual index type)

How to repeat:
see description.

Suggested fix:
Please issue at least a warning when specifying the wrong index type. Please allow an error to be reported if the user chooses so for example by specifying a particular sql mode. 

At the very least, modify the documentation. The term "permissible index type" strongly suggests something bad will happen when it is not permitted whereas MySQL is perfectly silent in this case.

Hello Roland,

Thank you for the report and test case.
Verified as described.

Thanks,
Umesh

// 5.6.20

mysql> CREATE TABLE `sessions` (
    ->     `id` char(40) COLLATE utf8_unicode_ci NOT NULL,
    ->     `payload` text COLLATE utf8_unicode_ci NOT NULL,
    ->     `last_activity` int(11) unsigned NOT NULL,
    ->     PRIMARY KEY (`id`),
    ->     UNIQUE KEY `session_id_unique` (`id`) USING HASH
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.34 sec)

mysql> show create table `sessions`\G
*************************** 1. row ***************************
       Table: sessions
Create Table: CREATE TABLE `sessions` (
  `id` char(40) COLLATE utf8_unicode_ci NOT NULL,
  `payload` text COLLATE utf8_unicode_ci NOT NULL,
  `last_activity` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id_unique` (`id`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

mysql>
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.20                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.20-enterprise-commercial-advanced-log               |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)