| 2012-04-10 23:10:29 |
Dave Juntgen |
bug |
|
|
added bug |
| 2012-04-10 23:11:48 |
Dave Juntgen |
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.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` > ?))'
# };
# |
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 heights, 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` > ?))'
# };
# |
|
| 2012-04-10 23:12:08 |
Dave Juntgen |
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.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 heights, 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` > ?))'
# };
# |
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 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`.`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` > ?))'
# };
# |
|
| 2012-04-16 12:36:28 |
Dave Juntgen |
attachment added |
|
PTDEBUG info https://bugs.launchpad.net/percona-toolkit/+bug/978432/+attachment/3076856/+files/debug.log |
|
| 2012-04-16 13:26:17 |
Dave Juntgen |
attachment added |
|
Best Index fix. https://bugs.launchpad.net/percona-toolkit/+bug/978432/+attachment/3077192/+files/pt-table-checksum.patch |
|
| 2012-04-17 21:12:46 |
Daniel Nichter |
tags |
|
chunking pt-table-checksum |
|
| 2012-04-17 21:14:44 |
Daniel Nichter |
percona-toolkit: status |
New |
In Progress |
|
| 2012-04-17 21:14:48 |
Daniel Nichter |
percona-toolkit: assignee |
|
Daniel Nichter (daniel-nichter) |
|
| 2012-05-04 20:59:40 |
Daniel Nichter |
nominated for series |
|
percona-toolkit/2.0 |
|
| 2012-05-04 20:59:40 |
Daniel Nichter |
bug task added |
|
percona-toolkit/2.0 |
|
| 2012-05-04 20:59:40 |
Daniel Nichter |
nominated for series |
|
percona-toolkit/2.1 |
|
| 2012-05-04 20:59:40 |
Daniel Nichter |
bug task added |
|
percona-toolkit/2.1 |
|
| 2012-05-04 20:59:46 |
Daniel Nichter |
percona-toolkit/2.0: status |
New |
In Progress |
|
| 2012-05-04 20:59:49 |
Daniel Nichter |
percona-toolkit/2.0: importance |
Undecided |
High |
|
| 2012-05-04 20:59:52 |
Daniel Nichter |
percona-toolkit/2.1: importance |
Undecided |
High |
|
| 2012-05-04 20:59:54 |
Daniel Nichter |
percona-toolkit/2.0: assignee |
|
Daniel Nichter (daniel-nichter) |
|
| 2012-05-04 23:47:08 |
Daniel Nichter |
percona-toolkit/2.0: milestone |
|
2.0.5 |
|
| 2012-05-04 23:47:10 |
Daniel Nichter |
percona-toolkit/2.1: milestone |
|
2.1.2 |
|
| 2012-05-04 23:48:18 |
Launchpad Janitor |
branch linked |
|
lp:~percona-toolkit-dev/percona-toolkit/fix-ptc-pk-bug-978432 |
|
| 2012-05-08 18:40:10 |
Daniel Nichter |
bug watch added |
|
http://code.google.com/p/maatkit/issues/detail?id=378 |
|
| 2012-05-08 22:33:12 |
Daniel Nichter |
percona-toolkit/2.1: status |
In Progress |
Fix Committed |
|
| 2012-05-08 22:34:14 |
Daniel Nichter |
percona-toolkit/2.0: status |
In Progress |
Invalid |
|
| 2012-05-08 22:34:18 |
Daniel Nichter |
percona-toolkit/2.0: milestone |
2.0.5 |
|
|
| 2012-05-08 22:34:21 |
Launchpad Janitor |
branch linked |
|
lp:~percona-toolkit-dev/percona-toolkit/fix-ptc-pk-bug-978432-2.0 |
|
| 2012-06-19 19:58:11 |
Daniel Nichter |
percona-toolkit/2.1: status |
Fix Committed |
Fix Released |
|
