On Tue, May 8, 2012 at 2:40 PM, Daniel Nichter <email address hidden> wrote:
> I realized a simpler, better solution. The very original issue for this
> bug was http://code.google.com/p/maatkit/issues/detail?id=378
>
> MySQL's chosen index is only something we should prefer if --where is
> used. Else, we can chose our own index and disregard the MySQL index
> from EXPLAIN SELECT * FROM tbl WHERE 1=1 (the row estimate) because this
> query is nothing like the checksum queries. If there's a --where,
> however, then it's used instead of 1=1 and then the MySQL is important
> because it tells us how MySQL plans to optimize for the --where.
>
> The latest code of the branch simply does:
>
> if ( !$where ) {
> $mysql_index = undef;
> }
>
> The test for this bug passes and all original tests pass too.
>
> ** Bug watch added: code.google.com/p/maatkit/issues #378
> http://code.google.com/p/maatkit/issues/detail?id=378
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/978432
>
> Title:
> pt-table-checksum ignoring primary key
>
> Status in Percona Toolkit:
> In Progress
> Status in Percona Toolkit 2.0 series:
> In Progress
> Status in Percona Toolkit 2.1 series:
> In Progress
>
> 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.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` > ?))'
> # };
> #
>
> To manage notifications about this bug go to:
> https://bugs.launchpad.net/percona-toolkit/+bug/978432/+subscriptions
I like simple solutions. Thanks.
On Tue, May 8, 2012 at 2:40 PM, Daniel Nichter <email address hidden> wrote: code.google. com/p/maatkit/ issues/ detail? id=378 com/p/maatkit/ issues #378 code.google. com/p/maatkit/ issues/ detail? id=378 /bugs.launchpad .net/bugs/ 978432 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. multi_resource_ apt REPLACE( @@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_ SHOW_CREATE, @@SQL_QUOTE_ SHOW_CREATE := 1 */ HASH(0x2f246b0) USE `wc_acj` .`multi_ resource_ apt` SHOW_CREATE := @OLD_QUOTE */ ,`res_id` ), WS('#', `apt_id`, `res_id`)) LOWER(CONV( BIT_XOR( CAST(CRC32( CONCAT_ WS('#', `apt_id`, `res_id`)) AS UNSIGNED)), 10, 16)), 0) AS crc .`multi_ resource_ apt` WHERE 1=1 resource_ apt', ####### ###### ####### ###### .`multi_ resource_ apt` WHERE Key_name = 'resid' /bugs.launchpad .net/percona- toolkit/ +bug/978432/ +subscriptions
> I realized a simpler, better solution. The very original issue for this
> bug was http://
>
> MySQL's chosen index is only something we should prefer if --where is
> used. Else, we can chose our own index and disregard the MySQL index
> from EXPLAIN SELECT * FROM tbl WHERE 1=1 (the row estimate) because this
> query is nothing like the checksum queries. If there's a --where,
> however, then it's used instead of 1=1 and then the MySQL is important
> because it tells us how MySQL plans to optimize for the --where.
>
> The latest code of the branch simply does:
>
> if ( !$where ) {
> $mysql_index = undef;
> }
>
> The test for this bug passes and all original tests pass too.
>
> ** Bug watch added: code.google.
> http://
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https:/
>
> Title:
> pt-table-checksum ignoring primary key
>
> Status in Percona Toolkit:
> In Progress
> Status in Percona Toolkit 2.0 series:
> In Progress
> Status in Percona Toolkit 2.1 series:
> In Progress
>
> 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` > ?))'
> # };
> #
>
> To manage notifications about this bug go to:
> https:/
--
David W. Juntgen