When you use pt-table-checksum with --where option, if index chosen at sub get_row_estimate is not a primary key, it will not be able to choose primary key as index

Bug #1576036 reported by Jaime Sicam
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Medium
Carlos Salguero

Bug Description

So, table structure is this:

CREATE TABLE t1 (
    a int(10) unsigned NOT NULL,
  b datetime DEFAULT NULL,
  PRIMARY KEY (a),
  KEY b(b)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Cardinality is this:
mysql> show indexes from test.a
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | gni | A | 97330 | NULL | NULL | | BTREE | | |
| t1 | 1 | b | 1 | b | A | 97330 | NULL | NULL | YES | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

pt-table-checksum --databases=test --chunk-time=0.01 --chunk-size=4 h=127.0.0.1,P=21690,u=root,p=msandbox --empty-replicate-table 2>&1 --where "1=1" | tee output.txt

Assuming in get_row_estimate sub, b index was chosen,
so:

$mysql_index = lc($mysql_index);

so here $mysql_index = 'b';

On can_nibble sub:
   if ( !$where ) {
      $mysql_index = undef;
   }

Since where is equal to "1=1", $mysql_index will not be set to undef.

So, on function _find_best_index:

   //want_index is false because chunk-index was not set:
   if ( !$want_index && $args{mysql_index} ) {
      PTDEBUG && _d('MySQL wants to use index', $args{mysql_index});
      $want_index = $args{mysql_index};
   }
  //Now, want_index contains b index

   my $best_index;
   my @possible_indexes;

  Since $want_index has a value, it will not be able to reach code for autoselecting the best index where the PRIMARY KEY is a candidate.

   if ( $want_index ) {
      if ( $indexes->{$want_index}->{is_unique} ) {
         PTDEBUG && _d('Will use wanted index');
         $best_index = $want_index;
      }
      else {
         PTDEBUG && _d('Wanted index is a possible index');
         push @possible_indexes, $want_index;
      }
   }
   else {
      PTDEBUG && _d('Auto-selecting best index');
      foreach my $index ( $tp->sort_indexes($tbl_struct) ) {
         if ( $index eq 'PRIMARY' || $indexes->{$index}->{is_unique} ) {
            $best_index = $index;
            last;
         }
         else {
            push @possible_indexes, $index;
         }
      }
   }

The problem is if b index is chosen and b column contains nulls and the boundary of the chunk is null and null, this is how debug output would look like:
# NibbleIterator:6647 30375 First lower boundary: $VAR1 = [
# undef,
# undef
# ];
#
# NibbleIterator:6663 30375 Next lower boundary: $VAR1 = [
# undef,
# undef
# ];
#

Then you would get this error:

04-25T14:39:09 Error checksumming table test.t1: Use of uninitialized value in join or string at /usr/bin/pt-table-checksum line 6727.

I think there are two bugs here. The other bug is if the boundaries is both null, you should test for this and not get an perl exception. This is reported here: https://bugs.launchpad.net/percona-toolkit/+bug/1202026

Jaime Sicam (jssicam)
tags: added: i67807
Jaime Sicam (jssicam)
Changed in percona-toolkit:
status: New → Confirmed
Revision history for this message
Jaime Sicam (jssicam) wrote :

Test case:

initial dump file attached.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | a | A | 106541 | NULL | NULL | | BTREE | | |
| t1 | 1 | b | 1 | b | A | 106541 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> update t1 set b=null limit 20000;
Query OK, 20000 rows affected, 1 warning (0.51 sec)
Rows matched: 20000 Changed: 20000 Warnings: 1

mysql> show indexes from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | a | A | 95961 | NULL | NULL | | BTREE | | |
| t1 | 1 | b | 1 | b | A | 95961 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[user@sandbox ~]$ pt-table-checksum --databases=test --where "1=1" h=127.0.0.1,u=root,p=msandbox,P=21690
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
 LANGUAGE = (unset),
 LC_ALL = (unset),
 LC_CTYPE = "UTF-8",
 LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
04-26T14:41:21 Error checksumming table test.t1: Use of uninitialized value in string ne at /usr/bin/pt-table-checksum line 6768.

            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
04-26T14:41:21 1 0 0 1 0 0.024 test.t1

Revision history for this message
Jaime Sicam (jssicam) wrote :

PTDEBUG output attached

Revision history for this message
Jaime Sicam (jssicam) wrote :

PTDEBUG output attached

Changed in percona-toolkit:
status: Confirmed → Triaged
importance: Undecided → Medium
assignee: nobody → Frank Cizmich (frank-cizmich)
Changed in percona-toolkit:
status: Triaged → In Progress
Changed in percona-toolkit:
milestone: none → 2.2.18
status: In Progress → Fix Committed
tags: added: pt-table-checksum
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Changed in percona-toolkit:
assignee: Frank Cizmich (frank-cizmich) → Carlos Salguero (carlos-salguero)
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-705

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.