Comment 4 for bug 1340728

You can use MEMORY engine to have HASH indexes.

On Tue, Jul 22, 2014 at 9:02 AM, Kenny Gryp <email address hidden> wrote:

> Frank: Did you test this with the NDB Storage engine?
>
>
> There's a bug in MySQL in that you can define HASH indexes in InnoDB, and
> SHOW CREATE TABLE will show it's a HASH index, but they won't be HASH
> indexes, they will be BTREE: http://bugs.mysql.com/bug.php?id=36869
>
> ** Bug watch added: MySQL Bug System #36869
> http://bugs.mysql.com/bug.php?id=36869
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1340728
>
> Title:
> pt-online-schema-change doesn't work with HASH indexes
>
> Status in Percona Toolkit:
> In Progress
>
> Bug description:
> When the primary key is an HASH (on supported ENGINEs, like MEMORY or
> NDB),
> the EXPLAIN is not using an index and therefore the process fails.
>
> Example of command:
>
> ./pt-online-schema-change --execute --user=root --no-drop-old-table
> --alter-foreign-keys-method=auto --no-check-alter --alter "DROP
> PRIMARY KEY, ADD PRIMARY KEY(id, identifier) USING HASH, DROP KEY id"
> D=sbtest,t=sbtest1,h=localhost
>
> This return something like (could not reproduce, on my machine I
> always got a segfault):
>
> Error copying rows from `sbtest`.`sbtest1` to `sbtest`.`_sbtest1_new`:
> Cannot determine the key_len of the chunk
> MySQL chose no index instead of the PRIMARY index for the first lower
> boundary statement. See --[no]check-plan in the documentation for more
> information. at ./pt-online-schema-change line 8876.
>
>
> Table definition:
>
> CREATE TABLE `sbtest1` (
> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `identifier` bigint(20) unsigned NOT NULL,
> `profile_id` int(10) unsigned NOT NULL,
> PRIMARY KEY (`identifier`) USING HASH,
> UNIQUE KEY `id` (`id`) USING HASH,
> KEY `fk_sub_idenities_profiles_idx` (`profile_id`)
> ) ENGINE=MEMORY AUTO_INCREMENT=232805 DEFAULT CHARSET=latin1
>
>
> I've also tested with --nocheck-plan :
>
> Error copying rows from `sbtest`.`sbtest1` to `sbtest`.`_sbtest1_new`:
> 2014-07-11T06:29:26 Aborting copying table `sbtest`.`sbtest1` at chunk
> 1 because it is not safe to ascend. Chunking should use the PRIMARY
> index, but MySQL EXPLAIN reports that no index will be used for...
>
>
> explain output:
>
> mysql> explain SELECT identifier FROM sbtest.sbtest1 FORCE
> INDEX(`PRIMARY`) WHERE ((`identifier` >=2223)) order BY `identifier` LIMIT
> 999,2\G
> *************************** 1. row ***************************
> id: 1
> select_type: SIMPLE
> table: sbtest1
> type: ALL
> possible_keys: PRIMARY
> key: NULL
> key_len: NULL
> ref: NULL
> rows: 9738
> Extra: Using where; Using filesort
> 1 row in set (0.00 sec)
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-toolkit/+bug/1340728/+subscriptions
>