pt-online-schema-change doesn't work with HASH indexes

Bug #1340728 reported by Frederic Descamps
10
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Medium
Frank Cizmich

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)

Related branches

Revision history for this message
Kenny Gryp (gryp) wrote :

Rangescans and hash indexes do not work well together. So I guess the pt-tc and pt-osc's have to detect that and give an appropriate error.

tags: added: pt-online-schema-change
Changed in percona-toolkit:
status: New → In Progress
assignee: nobody → Frank Cizmich (frank-cizmich)
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

Hello Frederic, Kenny

Kenny, you are probably right. Nevertheless I made a small patch that makes --nocheck-plan *completely* disregard the EXPLAIN query result.
Tested it on the sample table provided by Frederic with 300,000 rows.
Should be used with care though, especially on very large tables.

Revision history for this message
Kenny Gryp (gryp) 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

Revision history for this message
Frederic Descamps (lefred) wrote : Re: [Bug 1340728] Re: pt-online-schema-change doesn't work with HASH indexes

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
>

Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

I only tested it with the MEMORY engine, but I think it's enough since the problem isn't the type of engine or index itself, but the fact that MySQL 'explains' it won't use the primary key. (for whatever reason).

We should determine what the desired behaviour of "--nocheck-plan" should be, since without the patch it actually does check the PLAN in two other places in the code.

By the way, this should be considered just a hack for now.

(I'm uploading another smaller patch, since one of the checks in the previous one was redundant.)

Changed in percona-toolkit:
importance: Undecided → Medium
milestone: none → 2.2.10
status: In Progress → Fix Committed
Revision history for this message
Frank Cizmich (frank-cizmich) wrote :

Commited fix makes --nocheck-plan effectively ignore MySQLs choice of key.

Use with caution, specially on very large tables.

Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-654

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.