pt-table-sync does take into account timezone dependent columns
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
In Progress
|
Undecided
|
Frank Cizmich |
Bug Description
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
*******
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
*******
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=
*******
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
...
at /usr/bin/
[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(
# TableSyncer:5984 1436 dst: SELECT /*test.t:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(
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]$
Changed in percona-toolkit: | |
status: | New → Triaged |
tags: | added: i66113 |
tags: | added: pt-table-sync |
Possible patch:
[revin@acme rsandbox_5_6_280]$ diff /usr/bin/ pt-table- sync pt-table-sync ($result) ";
4835c4835
< $result .= ' + 0';
---
> $result = "UNIX_TIMESTAMP