Comment 10 for bug 978432

I like simple solutions. Thanks.

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

--
David W. Juntgen