pt-table-sync does take into account timezone dependent columns

[Jervin R]

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;
+----+---------------------+--------------------+
| id | ts | unix_timestamp(ts) |
+----+---------------------+--------------------+
| 1 | 2016-03-04 22:21:28 | 1457158888 |
+----+---------------------+--------------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select *, unix_timestamp(ts) from t;
+----+---------------------+--------------------+
| id | ts | unix_timestamp(ts) |
+----+---------------------+--------------------+
| 1 | 2016-03-05 01:21:28 | 1457158888 |
+----+---------------------+--------------------+
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;
+----+---------------------+--------------------+
| id | ts | unix_timestamp(ts) |
+----+---------------------+--------------------+
| 1 | 2016-03-04 22:21:28 | 1457158888 |
+----+---------------------+--------------------+
1 row in set (0.00 sec)

slave1 [localhost] {msandbox} (test) > select *, unix_timestamp(ts) from t;
+----+---------------------+--------------------+
| id | ts | unix_timestamp(ts) |
+----+---------------------+--------------------+
| 1 | 2016-03-04 22:21:28 | 1457148088 |
+----+---------------------+--------------------+
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:

# TableSyncer:5983 1436 src: SELECT /*test.t:1/1*/ 0 AS chunk_num, COUNT(*) 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
# TableSyncer:5984 1436 dst: SELECT /*test.t:1/1*/ 0 AS chunk_num, COUNT(*) 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]$

[Jervin R (revin)]

Possible patch:

[revin@acme rsandbox_5_6_280]$ diff /usr/bin/pt-table-sync pt-table-sync
4835c4835
< $result .= ' + 0';
---
> $result = "UNIX_TIMESTAMP($result)";

[Jervin R (revin)]

[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
REPLACE INTO `test`.`t`(`id`, `ts`) VALUES ('1', '2016-03-04 22:21:28') /*percona-toolkit src_db:test src_tbl:t src_dsn:P=56280,h=127.0.0.1,p=...,u=msandbox dst_db:test dst_tbl:t dst_dsn:P=56281,h=127.0.0.1,p=...,u=msandbox lock:1 transaction:1 changing_src:percona.checksum replicate:percona.checksum bidirectional:0 pid:7219 user:revin host:acme.com*/;

[Jervin R (revin)]

Update, so sending the update as constant will not get what we want, but we need to transform it to timezone dependent as well by selecting it as unixtime from the chunk query.

SELECT id, UNIX_TIMESTAMP(ts) AS ts FROM t /* checksum chunk */
REPLACE INTO `test`.`t`(`id`, `ts`) VALUES ('1', FROM_UNIXTIME(@ts))

[Frank Cizmich (frank-cizmich)]

Hi Jervin,

This seems the same issue that was solved in pt-table-checksum (that's why pt-tc works ant pt-ts doesn't)

https://bugs.launchpad.net/percona-toolkit/+bug/1388870

Only extra complication here is, as you pointed out, that the tool not only has to detect the difference but also fix it.

[Shahriyar Rzayev (rzayev-sehriyar)]

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