Percona Toolkit moved to https://jira.percona.com/projects/PT

pt-table-checksum corrupts data if PK is binary

Bug #1732165 reported by Sveta Smirnova
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
In Progress
High
Carlos Salguero
Percona Toolkit moved to https://jira.percona.com/projects/PT 3.0.6

Bug Description

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.

Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :
Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :
Changed in percona-toolkit:
status: New → Confirmed
Carlos Salguero (carlos-salguero)
Changed in percona-toolkit:
importance: Undecided → Medium
importance: Medium → High
assignee: nobody → Carlos Salguero (carlos-salguero)
milestone: none → 3.0.6
Carlos Salguero (carlos-salguero)
tags: added: pt219
Carlos Salguero (carlos-salguero)
Changed in percona-toolkit:
status: Confirmed → In Progress
Revision history for this message
Carlos Salguero (carlos-salguero) wrote :

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.

Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :

Carlos, can you explain what is the problem?

Revision history for this message
Carlos Salguero (carlos-salguero) wrote :

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})

Revision history for this message
Sveta Smirnova (svetasmirnova) wrote :
Download full text (5.0 KiB)

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...

Read more...

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-405

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

Other bug subscribers

Remote bug watches

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