Timezone and pt-table-checksum

I recently worked through an issue with a client trying to detect data drift across some servers that were located in different timezones.  Unfortunately, several of the tables had timestamp fields and were set to a default value of CURRENT_TIMESTAMP.  From the manual, here is how MySQL handles timezone locality with timestamp fields:

Values for TIMESTAMP columns are converted from the current time zone to UTC for storage, and from UTC to the current time zone for retrieval.

This can result in every row being deemed out of sync when each server is using in it’s own timezone.  Fortunately, there is a workaround for this as a result of how pt-table-checksum/pt-table-sync calculate their checksums.  Lets look at an example:

Node1 is configured in PDT time while node2 is configured in EDT time.  Now, lets insert some rows into a table with timestamp field configured to use CURRENT_TIMESTAMP as the default and verify that they look different when no timezone manipulation takes place:

So, when we run pt-table-checksum against node1, we see that even though MySQL is storing these values in UTC internally, we have “data drift” on both nodes:

pt-table-checksum –replicate=percona.checksums –databases=foo h=localhost

Naturally, pt-table-sync finds this and reports that all 3 rows in this chunk are different and gives the REPLACE INTO statements bring node2 in sync:

This is where we can use the @@session.time_zone variable to our advantage.  If both servers were set up using UTC as the timezone (which is the recommended practice – store everything in UTC and then perform the timezone logic in the application), we wouldn’t see any data drift.  And as explained above, the timestamps are actually stored in UTC on the server regardless of the timezone.  So, to overcome the timezone inconsistencies, we can use the –set-vars option with pt-table-checksum like this:

pt-table-checksum –replicate percona.checksums –databases=foo –set-vars=”time_zone=’+00:00′” h=localhost

This effectively sets both servers to UTC (in terms of display logic) and the checksums are calculated in the same locality: