**Reported in Launchpad by Jervin R last update 08-03-2016 10:27:36

So we have one row on the table, by definition the values are the same its just that, when the TIMESTAMP column is read, the time_zone on the server is applied. The master is on PST while the slave is on EST.

The user sees this, based on ts value that they are different, though in fact they are not.

master [localhost] {msandbox} (test) > select *, unix_timestamp(ts) from t;

--------------------+-------------------+

--------------------+-------------------+

--------------------+-------------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select *, unix_timestamp(ts) from t;

--------------------+-------------------+

--------------------+-------------------+

--------------------+-------------------+
1 row in set (0.00 sec)

Running a checksum confirms they are actually the same value.

slave1 [localhost] {msandbox} (test) > select * from percona.checksum where db = 'test' \G
*************************** 1. row ***************************
db: test
tbl: t
chunk: 1
chunk_time: 0.003371
chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
this_crc: 39bf6832
this_cnt: 1
master_crc: 39bf6832
master_cnt: 1
ts: 2016-03-05 01:24:24
1 row in set (0.00 sec)

Now, what if I change/induce an inconsistency on the slave, such that the face value of ts is the same as the master.

slave1 [localhost] {msandbox} (test) > update t set ts = '2016-03-04 22:21:28';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

master [localhost] {msandbox} (test) > select *, unix_timestamp(ts) from t;

--------------------+-------------------+

--------------------+-------------------+

--------------------+-------------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select *, unix_timestamp(ts) from t;

--------------------+-------------------+

--------------------+-------------------+

--------------------+-------------------+
1 row in set (0.00 sec)

Now the value stripped off of timezone are not the same, but the face value are consistent. This is a legitimate inconsistency in this case since the master and slave are on different timezones.

Checksum now sees this as differences, again this is expected and legitimate difference.

slave1 [localhost] {msandbox} (test) > select * from percona.checksum where db = 'test' \G
*************************** 1. row ***************************
db: test
tbl: t
chunk: 1
chunk_time: 0.004221
chunk_index: NULL
lower_boundary: NULL
upper_boundary: NULL
this_crc: acc103a
this_cnt: 1
master_crc: 39bf6832
master_cnt: 1
ts: 2016-03-05 01:30:52
1 row in set (0.00 sec)

Trying pt-table-sync:

[revin@acme rsandbox_5_6_280]$ pt-table-sync --print --replicate=percona.checksum --databases=test --sync-to-master h=127.0.0.1,u=msandbox,p=msandbox,P=56281
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
...
at /usr/bin/pt-table-sync line 8607.
[revin@acme rsandbox_5_6_280]$

Wait, what? Nothing was synced? Let's see the debug output:

1. TableSyncer:5983 1436 src: SELECT /test.t:1/1/ 0 AS chunk_num, COUNT:yellow_star: AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `ts` + 0)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`t` FORCE INDEX (`PRIMARY`) WHERE (1=1) FOR UPDATE

2. TableSyncer:5984 1436 dst: SELECT /test.t:1/1/ 0 AS chunk_num, COUNT:yellow_star: AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `ts` + 0)) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `test`.`t` FORCE INDEX (`PRIMARY`) WHERE (1=1) LOCK IN SHARE MODE

So the column type of ts is not taken into account properly unlike when running the checksums - this is a bug on pt-table-sync and a job for Super Frank!

[revin@acme rsandbox_5_6_280]$ pt-table-sync --version
pt-table-sync 2.2.16
[revin@acme rsandbox_5_6_280]$