pt-table-checksum corrupts data if PK is binary

[Sveta Smirnova]

If Primary Key is BINARY and tables on master and slave have differences data, reported for lower_bound and upper_bound in checksums table not relevant to actual content.

How to repeat.

1. Start MTR suite: ./mtr --start --suite=rpl rpl_alter &
2. Enable replication:

$mysql -h127.0.0.1 -P13002 -uroot
...
mysql> CHANGE MASTER TO master_host='127.0.0.1', master_port=13001, master_user='root';
Query OK, 0 rows affected, 1 warning (0.47 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

3. On master (port 13001):

mysql> create database percona;
Query OK, 1 row affected (0.02 sec)

mysql> create database db1;
Query OK, 1 row affected (0.01 sec)

mysql> use percona
Database changed
mysql> CREATE TABLE `dsns` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `parent_id` int(11) DEFAULT NULL,
    -> `dsn` varchar(255) NOT NULL,
    -> PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql> insert into dsns(dsn) values('h=127.0.0.1,P=13002,u=root');
Query OK, 1 row affected (0.06 sec)

4. Load attached dump to master:

mysql -h127.0.0.1 -P13001 -uroot db1 < t1_master.sql

5. Load attached dump to slave:

mysql -h127.0.0.1 -P13002 -uroot db1 < t1_slave.sql

6. Run pt-table-checksum

$ ~/build/percona-toolkit/bin/pt-table-checksum --set-vars innodb_lock_wait_timeout=50 --tables=db1.t1 --empty-replicate-table --recursion-method=dsn=h=127.0.0.1,P=13001,u=root,D=percona,t=dsns --max-load="Threads_connected=1300, Threads_running=20" --progress="time,5" --chunk-time=0.005 --max-lag=1 --pause-file="/tmp/ptchecksum.pause" --no-check-binlog-format --no-check-replication-filters --chunk-size=10 h=127.0.0.1,P=13001,u=root
            TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
11-14T14:28:04 0 10 103 13 0 5.071 db1.t1

7. Connect to percona database on slave (port 13002) and run:

mysql> select * from checksums;
+-----+-----+-------+------------+-------------+---------------------------+---------------------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+-----+-----+-------+------------+-------------+---------------------------+---------------------------+----------+----------+------------+------------+---------------------+
| db1 | t1 | 1 | 0.166802 | PRIMARY | U<PMÐâº!8YùãZ | U<PMÐâº!8YùãZ | e01583ca | 10 | 166001dd | 10 | 2017-11-14 14:27:59 |
| db1 | t1 | 2 | 0.145081 | PRIMARY | U<PMÐâº!8YùãZ | 'U<PMÐâº!8YùãZ' | 11767253 | 10 | ccf2d046 | 10 | 2017-11-14 14:27:59 |
| db1 | t1 | 3 | 0.137513 | PRIMARY | (U<PMÐâº!8YùãZ( | 6U<PMÐâº!8YùãZ6 | 3adb2295 | 10 | b4893b83 | 10 | 2017-11-14 14:28:00 |
| db1 | t1 | 4 | 0.177275 | PRIMARY | 7U<PMÐâº!8YùãZ7 | EU<PMÐâº!8YùãZE | 379c30d2 | 10 | 58bb8022 | 10 | 2017-11-14 14:28:00 |
| db1 | t1 | 5 | 0.190659 | PRIMARY | FU<PMÐâº!8YùãZF | USÅÝ

+¢…Ÿž5¥ | ebcfe880 | 10 | 16d38831 | 10 | 2017-11-14 14:28:00 |
| db1 | t1 | 6 | 0.118443 | PRIMARY | UU<PMÐâº!8YùãZU | dU<PMÐâº!8YùãZd | 42cf5e7 | 10 | 3e5774a3 | 10 | 2017-11-14 14:28:01 |
| db1 | t1 | 7 | 0.130475 | PRIMARY | eSÅÝ

+¢…Ÿž5¦ | sU<PMÐâº!8YùãZs | 37f2aac0 | 10 | 45846761 | 10 | 2017-11-14 14:28:01 |
| db1 | t1 | 8 | 0.131578 | PRIMARY | tU<PMÐâº!8YùãZt | xU<PMÐâº!8YùãZx | bfd404d6 | 10 | 3e4196c4 | 10 | 2017-11-14 14:28:01 |
| db1 | t1 | 9 | 0.262167 | PRIMARY | yU<PMÐâº!8YùãZy | ‡U<PMÐâº!8YùãZ‡ | 1fb53acb | 10 | c8a36ff5 | 10 | 2017-11-14 14:28:02 |
| db1 | t1 | 10 | 0.151902 | PRIMARY | ˆU<PMÐâº!8YùãZˆ | –U<PMÐâº!8YùãZ– | 35492b1f | 10 | 2634f201 | 10 | 2017-11-14 14:28:02 |
| db1 | t1 | 11 | 0.109676 | PRIMARY | —U<PMÐâº!8YùãZ— | ™U<PMÐâº!8YùãZ™ | b27d4b56 | 3 | b27d4b56 | 3 | 2017-11-14 14:28:02 |
| db1 | t1 | 12 | 0.116479 | PRIMARY | NULL | U<PMÐâº!8YùãZ | 0 | 0 | 0 | 0 | 2017-11-14 14:28:02 |
| db1 | t1 | 13 | 0.096695 | PRIMARY | ™U<PMÐâº!8YùãZ™ | NULL | 0 | 0 | 0 | 0 | 2017-11-14 14:28:03 |
+-----+-----+-------+------------+-------------+---------------------------+---------------------------+----------+----------+------------+------------+---------------------+
13 rows in set (0.01 sec)

mysql> select hex(lower_boundary), length(lower_boundary), hex(upper_boundary), length(upper_boundary) from checksums;
+----------------------------------------------------+------------------------+----------------------------------------------------+------------------------+
| hex(lower_boundary) | length(lower_boundary) | hex(upper_boundary) | length(upper_boundary) |
+----------------------------------------------------+------------------------+----------------------------------------------------+------------------------+
| 10553C504DC39011C3A2C2BA213859C3B9C3A35A10 | 21 | 18553C504DC39011C3A2C2BA213859C3B9C3A35A18 | 21 |
| 19553C504DC39011C3A2C2BA213859C3B9C3A35A19 | 21 | 27553C504DC39011C3A2C2BA213859C3B9C3A35A27 | 21 |
| 28553C504DC39011C3A2C2BA213859C3B9C3A35A28 | 21 | 36553C504DC39011C3A2C2BA213859C3B9C3A35A36 | 21 |
| 37553C504DC39011C3A2C2BA213859C3B9C3A35A37 | 21 | 45553C504DC39011C3A2C2BA213859C3B9C3A35A45 | 21 |
| 46553C504DC39011C3A2C2BA213859C3B9C3A35A46 | 21 | 5553C38504C39D011E2BC2A213E280A6C5B8C5BE35C2A500 | 24 |
| 55553C504DC39011C3A2C2BA213859C3B9C3A35A55 | 21 | 64553C504DC39011C3A2C2BA213859C3B9C3A35A64 | 21 |
| 6553C38504C39D011E2BC2A213E280A6C5B8C5BE35C2A600 | 24 | 73553C504DC39011C3A2C2BA213859C3B9C3A35A73 | 21 |
| 74553C504DC39011C3A2C2BA213859C3B9C3A35A74 | 21 | 78553C504DC39011C3A2C2BA213859C3B9C3A35A78 | 21 |
| 79553C504DC39011C3A2C2BA213859C3B9C3A35A79 | 21 | E280A1553C504DC39011C3A2C2BA213859C3B9C3A35AE280A1 | 25 |
| CB86553C504DC39011C3A2C2BA213859C3B9C3A35ACB86 | 23 | E28093553C504DC39011C3A2C2BA213859C3B9C3A35AE28093 | 25 |
| E28094553C504DC39011C3A2C2BA213859C3B9C3A35AE28094 | 25 | E284A2553C504DC39011C3A2C2BA213859C3B9C3A35AE284A2 | 25 |
| NULL | NULL | 10553C504DC39011C3A2C2BA213859C3B9C3A35A10 | 21 |
| E284A2553C504DC39011C3A2C2BA213859C3B9C3A35AE284A2 | 25 | NULL | NULL |
+----------------------------------------------------+------------------------+----------------------------------------------------+------------------------+
13 rows in set (0.00 sec)

mysql> select min(length(guid)), max(length(guid)) from db1.t1;
+-------------------+-------------------+
| min(length(guid)) | max(length(guid)) |
+-------------------+-------------------+
| 16 | 16 |
+-------------------+-------------------+
1 row in set (0.01 sec)

You will see what data in checksum table is longer than data in original table. It is not possible to use this data to identify modified chunks.

[Carlos Salguero (carlos-salguero)]

There is a problem with the Perl DBI driver.
I tried to fix it or at least to find a workaround but I couldn't.

[Sveta Smirnova (svetasmirnova)]

Carlos, can you explain what is the problem?

[Carlos Salguero (carlos-salguero)]

Even for prepared statements, using placeholders in the queries, Perl driver is not respecting the binary data read from the table, in this case, the uuid field.
I ran tests even setting the filed type manually, but Perl DBI is still doing some internal conversion.

$ins_sth->bind_param( 4, $ref->{'guid'}, {TYPE => SQL_BINARY})

[Sveta Smirnova (svetasmirnova)]

It seems what DBI delegates proper handling of data types to database driver and DBD/mysql.pm has this code:

 520 elsif ($basetype =~ /^(binary|varbinary)/)
 521 {
 522 $info->{COLUMN_SIZE} = $type_params[0];
 523 # SQL_BINARY & SQL_VARBINARY are tempting here but don't match the
 524 # semantics for mysql (not hex). SQL_CHAR & SQL_VARCHAR are correct here.
 525 $info->{DATA_TYPE} = ($basetype eq 'binary') ? SQL_CHAR() : SQL_VARCHAR();
 526 }

And SQL_CHAR/SQL_VARCHAR have character set defined (unlike binary)

So partially working workaround could be:

1. Create custom checksum table which will use binary character set instead of utf8:

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;

2. Run pt-table-checksum with binary character set:

~/build/percona-toolkit/bin/pt-table-checksum --set-vars innodb_lock_wait_timeout=50 --tables=db1.t1 --empty-replicate-table --recursion-method=dsn=h=127.0.0.1,P=13001,u=root,D=percona,t=dsns --max-load="Threads_connected=1300, Threads_running=20" --progress="time,5" --chunk-time=0.005 --max-lag=1 --pause-file="/tmp/ptchecksum.pause" --no-check-binlog-format --no-check-replication-filters --chunk-size=10 h=127.0.0.1,P=13001,u=root,A=binary

3. It will hang:

Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain
Waiting to check replicas for differences: 0% 00:00 remain

But it will found correct differences and won't corrupt data:

mysql> select hex(lower_boundary), length(lower_boundary), hex(upper_boundary), length(upper_boundary) from checksums;
+----------------------------------+------------------------+----------------------------------+------------------------+
| hex(lower_boundary) | length(lower_boundary) | hex(upper_boundary) | length(upper_boundary) |
+----------------------------------+------------------------+----------------------------------+------------------------+
| 10553C504DD011E2BA213859F9E35A10 | 16 | 18553C504DD011E2BA213859F9E35A18 | 16 |
| 19553C504DD011E2BA213859F9E35A19 | 16 | 27553C504DD011E2BA213859F9E35A27 | 16 |
| 28553C504...

[Shahriyar Rzayev (rzayev-sehriyar)]

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