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
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.
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_
$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-
PTDEBUG && _d('Will use wanted 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_
if ( $index eq 'PRIMARY' || $indexes-
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/
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:/
tags: | added: i67807 |
Changed in percona-toolkit: | |
status: | New → Confirmed |
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) |
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 0.1,u=root, p=msandbox, P=21690 pt-table- checksum line 6768.
Bye
[user@sandbox ~]$ pt-table-checksum --databases=test --where "1=1" h=127.0.
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/
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
04-26T14:41:21 1 0 0 1 0 0.024 test.t1