pt-table-checksum inaccurate reports when timezones are different
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona Toolkit moved to https://jira.percona.com/projects/PT |
Invalid
|
Undecided
|
Unassigned |
Bug Description
pt-table-checksum was reporting diffs between data on the master and data on the slave, despite numerous attempts to create and re-create the slave.
In the end it turned out to be that because the Master server was set to EST and the slave HST (Master should have been HST like the rest of the servers, something had messed up the tzdata), the returned data from timestamp fields was different. timestamp data is stored as UTC but converted to localtime during select.
You might want to either throw up an error if you can detect that the master & slave are on different timezones, or maybe force the timezone to UTC for your connection through the time_zone variable.
pt-table-checksum version 2.0.1, mysql 5.1.58 on master, 5.1.59 on slave.
description: | updated |
I believe that this should go into the list of sanity-checks we do: check for the timezone, compare, stop with an error message if any differ. The fix for this is to use the --set-vars option to set the time_zone variable for the connections. But in the general case, the DBA should decide whether that's the right fix.