pt-online-schema-change doesn't work with HASH indexes
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-
This return something like (could not reproduce, on my machine I always got a segfault):
Error copying rows from `sbtest`.`sbtest1` to `sbtest`
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-
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_
) ENGINE=MEMORY AUTO_INCREMENT=
I've also tested with --nocheck-plan :
Error copying rows from `sbtest`.`sbtest1` to `sbtest`
explain output:
mysql> explain SELECT identifier FROM sbtest.sbtest1 FORCE INDEX(`PRIMARY`) WHERE ((`identifier` >=2223)) order BY `identifier` LIMIT 999,2\G
*******
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
- Daniel Nichter: Approve
-
Diff: 288 lines (+116/-111)5 files modifiedbin/pt-online-schema-change (+40/-33)
t/pt-online-schema-change/bug-1315130.t (+0/-78)
t/pt-online-schema-change/bugs.t (+64/-0)
t/pt-online-schema-change/samples/bug-1340728.sql (+10/-0)
t/pt-online-schema-change/samples/bug-1340728_cleanup.sql (+2/-0)
Changed in percona-toolkit: | |
status: | New → In Progress |
assignee: | nobody → Frank Cizmich (frank-cizmich) |
Changed in percona-toolkit: | |
importance: | Undecided → Medium |
milestone: | none → 2.2.10 |
status: | In Progress → Fix Committed |
Changed in percona-toolkit: | |
status: | Fix Committed → Fix Released |
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.