Comment 0 for bug 978432

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.multi_resource_apt: Possible infinite loop detected! The lower boundary for chunk 1 is <738> and the lower boundary for chunk 2 is also <738>. This usually happens when using a non-unique single column index. The current chunk index for table wc_acj.multi_resource_apt is resid which is not unique and covers 1 column.

            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
04-10T19:06:18 1 0 0 1 1 0.005 wc_acj.multi_resource_apt

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(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
# TableParser:1788 17970 DBI::db=HASH(0x2f246b0) USE `wc_acj`
# TableParser:1792 17970 SHOW CREATE TABLE `wc_acj`.`multi_resource_apt`
# TableParser:1804 17970 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_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`,`res_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_WS('#', `apt_id`, `res_id`))
# RowChecksum:3247 17970 Chunk checksum: COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `apt_id`, `res_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc
# NibbleIterator:3993 17970 EXPLAIN SELECT * FROM `wc_acj`.`multi_resource_apt` WHERE 1=1
# 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_resource_apt',
# 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 nights, 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`.`multi_resource_apt` WHERE Key_name = 'resid'
# 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` > ?))'
# };
#