pt-table-checksum ignoring primary key
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Fix Released
|
High
|
Daniel Nichter | ||
2.0 |
Invalid
|
High
|
Daniel Nichter | ||
2.1 |
Fix Released
|
High
|
Daniel Nichter |
Bug Description
pt-table-checksum is ignoring the use of a primary key.
$ ./pt-table-checksum --user=X --password=X -d wc_acj -t multi_resource_apt wc-c1-db1
04-10T19:06:18 Error checksumming table wc_acj.
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
04-10T19:06:18 1 0 0 1 1 0.005 wc_acj.
Running PTDEBUG=1, I see the following.
# SchemaIterator:4807 17970 Found 1 tables in database wc_acj
# TableParser:1783 17970 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(
# TableParser:1788 17970 DBI::db=
# TableParser:1792 17970 SHOW CREATE TABLE `wc_acj`
# TableParser:1804 17970 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_
# TableParser:2013 17970 Storage engine: InnoDB
# TableParser:1834 17970 Table cols: `apt_id`, `res_id`
# TableParser:2013 17970 Storage engine: InnoDB
# TableParser:2029 17970 Parsed key: PRIMARY KEY (`apt_id`
# TableParser:2055 17970 PRIMARY key cols: `apt_id`, `res_id`
# TableParser:2077 17970 This key is the clustered key
# TableParser:2029 17970 Parsed key: KEY `resid` (`res_id`)
# TableParser:2055 17970 resid key cols: `res_id`
# SchemaIterator:4698 17970 Next schema object: wc_acj multi_resource_apt
# RowChecksum:3226 17970 Checksum strat: $VAR1 = {
# crc_type => 'int',
# crc_width => 16,
# func => 'CRC32',
# opt_slice => undef
# };
#
# RowChecksum:3208 17970 Row checksum: CRC32(CONCAT_
# RowChecksum:3247 17970 Chunk checksum: COUNT(*) AS cnt, COALESCE(
# NibbleIterator:3993 17970 EXPLAIN SELECT * FROM `wc_acj`
# NibbleIterator:3995 17970 $VAR1 = {
# extra => 'Using index',
# id => '1',
# key => 'resid',
# key_len => '4',
# possible_keys => undef,
# ref => undef,
# rows => '396069',
# select_type => 'SIMPLE',
# table => 'multi_
# type => 'index'
# };
#
# NibbleIterator:3870 17970 One nibble: no
#######
#
# HERE - MySQL ignore's the primary key, in return so does pt-table-checksum
# I think it would be wise to force PRIMARY KEY indexes to be used regardless of other indexes.
# Even though the cardinality is not the highest, the primary key is still unique.
#
#######
# NibbleIterator:3908 17970 MySQL wants to use index resid
# NibbleIterator:3920 17970 Wanted index is a possible index
# NibbleIterator:3938 17970 No PRIMARY or unique indexes; will use index with highest cardinality
# NibbleIterator:3969 17970 SHOW INDEXES FROM `wc_acj`
# NibbleIterator:3979 17970 Index resid cardinality: 10
# NibbleIterator:3958 17970 Best index: resid
# TableNibbler:2204 17970 Will ascend index resid
# TableNibbler:2211 17970 Will ascend columns res_id
# TableNibbler:2222 17970 Will ascend, in ordinal position: 1
# NibbleIterator:3591 17970 Ascend params: $VAR1 = {
# boundaries => {
# '<' => '((`res_id` < ?))',
# '<=' => '((`res_id` <= ?))',
# '>' => '((`res_id` > ?))',
# '>=' => '((`res_id` >= ?))'
# },
# cols => [
# 'apt_id',
# 'res_id'
# ],
# index => 'resid',
# scols => [
# 'res_id'
# ],
# slice => [
# 1
# ],
# where => '((`res_id` > ?))'
# };
#
Related branches
- Daniel Nichter: Approve
-
Diff: 151 lines (+78/-6)5 files modifiedbin/pt-table-checksum (+7/-4)
lib/NibbleIterator.pm (+14/-1)
t/pt-table-checksum/chunk_index.t (+17/-1)
t/pt-table-checksum/samples/not-using-pk-bug.out (+20/-0)
t/pt-table-checksum/samples/not-using-pk-bug.sql (+20/-0)
- Daniel Nichter: Approve
-
Diff: 85 lines (+57/-1)3 files modifiedt/pt-table-checksum/chunk_index.t (+17/-1)
t/pt-table-checksum/samples/not-using-pk-bug.out (+20/-0)
t/pt-table-checksum/samples/not-using-pk-bug.sql (+20/-0)
description: | updated |
description: | updated |
tags: | added: chunking pt-table-checksum |
Changed in percona-toolkit: | |
status: | New → In Progress |
assignee: | nobody → Daniel Nichter (daniel-nichter) |
I know that there is a setting to force a key to be used, --chunk- index=PRIMARY, however as a feature I think that if a primary index exist and no other unique key exists that the primary key should take precedence over any non unique key regardless what MySQL Explain says.