pt-table-checksum fails on BINARY field in PK

Could binary charset in percona.checksums be possible workaround?

CREATE TABLE `checksums` (
  `db` binary(64) NOT NULL,
  `tbl` binary(64) NOT NULL,
  `chunk` int(11) NOT NULL,
  `chunk_time` float DEFAULT NULL,
  `chunk_index` varbinary(200) DEFAULT NULL,
  `lower_boundary` blob,
  `upper_boundary` blob,
  `this_crc` binary(40) NOT NULL,
  `this_cnt` int(11) NOT NULL,
  `master_crc` binary(40) DEFAULT NULL,
  `master_cnt` int(11) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`db`,`tbl`,`chunk`),
  KEY `ts_db_tbl` (`ts`,`db`,`tbl`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

it doesn't work when binary, master_crc is NULL for every chunk.
latin1 & charset latin1 works fine though

typo. Read "blob lower_boundary and upper_boundary & charset latin1 works fine though"

I wonder if this is really a duplicate of https://bugs.launchpad.net/percona-toolkit/+bug/925781

Hi Aleksandr,

Can you provide SHOW CREATE TABLE output for the source table (alter column names if you wish) and some sample data? (fake data will do, as long as it breaks the tool)
If we can reproduce this we should be able to solve it from within the code.

Thx

Having trouble reproducing this issue.
As a workaround for this table I'd recommend using --chunk-index-columns=1
This way the tool will use only the first column of the key, ignoring the problematic binary column.

Aleksandr,

I've ran pt-table-checksum through the test suite using your workaround ( blob datatype for upper and lower boundary ) and the results were ok.
So in principle at least, you should be ok using this fix.

This is easy to reproduce (tbin/sql will be uploaded later).

1. Make sure you have default-character-set=utf8 for clients, like this:

[openxs@chief p5.6]$ cat ~/.my.cnf
[client]
default-character-set=utf8

2. Make sure there is no percona database.
3. Load data:

[openxs@chief p5.6]$ bin/mysql --no-defaults -uroot test </tmp/tbin.sql [openxs@chief p5.6]$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.6.21-69.0 MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from tbin;
+----------+
| count(*) |
+----------+
| 8192 |
+----------+
1 row in set (0.01 sec)

mysql> exit
Bye

Try to use pt-table-checksum:

[openxs@chief p5.6]$ pt-table-checksum u=root,P=3306,h=127.0.0.1
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-22T09:36:54 0 0 0 1 0 0.142 mysql.columns_priv
10-22T09:36:54 0 0 2 1 0 0.165 mysql.db
10-22T09:36:54 0 0 0 1 0 0.165 mysql.event
10-22T09:36:55 0 0 0 1 0 0.124 mysql.func
10-22T09:36:55 0 0 40 1 0 0.124 mysql.help_category
10-22T09:36:55 0 0 485 1 0 0.123 mysql.help_keyword
10-22T09:36:55 0 0 1090 1 0 0.123 mysql.help_relation
10-22T09:36:55 0 0 533 1 0 0.124 mysql.help_topic
10-22T09:36:55 0 0 18 1 0 0.165 mysql.innodb_index_stats
10-22T09:36:55 0 0 2 1 0 0.124 mysql.innodb_table_stats
10-22T09:36:55 0 0 0 1 0 0.123 mysql.ndb_binlog_index
10-22T09:36:56 0 0 0 1 0 0.133 mysql.plugin
10-22T09:36:56 0 0 0 1 0 0.131 mysql.proc
10-22T09:36:56 0 0 0 1 0 0.132 mysql.procs_priv
10-22T09:36:56 0 0 2 1 0 0.132 mysql.proxies_priv
10-22T09:36:56 0 0 0 1 0 0.141 mysql.servers
10-22T09:36:56 0 0 0 1 0 0.148 mysql.slave_master_info
10-22T09:36:56 0 0 0 1 0 0.149 mysql.slave_relay_log_info
10-22T09:36:57 0 0 0 1 0 0.140 mysql.slave_worker_info
10-22T09:36:57 0 0 0 1 0 0.123 mysql.tables_priv
10-22T09:36:57 0 0 0 1 0 0.125 mysql.time_zone
10-22T09:36:57 0 0 0 1 0 0.366 mysql.time_zone_leap_second
10-22T09:36:57 0 0 0 1 0 0.132 mysql.time_zone_name
10-...

Sample table that breaks pt-table-checksum.

attached is a version of pt-table-checksum that creates a checksum table using blob datatypes for the boundaries.

Now we should make sure pt-table-sync also works with the checksum table using blob data types, and that new structure is documented, and that newer versions of tools work well with chacksum tables from older versions etc

Reason for postponed release is that this needs exhaustive testing.

Added --binary-index flag to optionally create checksum table using BLOB data type.

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