pt-table-checksum has some errors with different time zones

Bug #1388870 reported by Steve Simpson on 2014-11-03
16
This bug affects 2 people
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to https://jira.percona.com/projects/PT
Fix Released
Medium
Frank Cizmich

Bug Description

We have successfully replicated a fairly large database for some time. And have used pt-table-checksum to validate that replication. We recently moved the slave and as a result changed the timezone (of the slave only).

As soon as we restarted mysql after changing the tz, we started seeing errors in pt-table checksum, one example was a 20 line (1 chunk) table that was showing 1 error.

Maybe this is a known limitation, not sure. I can supply some more information (like a sample table) via direct email, if required.

Related branches

Roman Vynar (roman-vynar) wrote :

Are you sure it's related to Percona Monitoring Plugins? Looks like you have an issue with pt-table-checksum itself.
Please provide more information.

Changed in percona-monitoring-plugins:
status: New → Incomplete
Steve Simpson (sksimpson) wrote :

Roman - you are correct, looks like this bug should be a against the Percona Toolkit, I was confused what was included in each project. Is it possible to change the project?

Roman Vynar (roman-vynar) wrote :

Steve, I have changed the project but I think more information needs to be provided anyway.

affects: percona-monitoring-plugins → percona-toolkit
Steve Simpson (sksimpson) wrote :

Version Information (for both master & slave)

Red Hat Enterprise Linux Server release 6.6 (Santiago)
Linux 2.6.32-504.el6.x86_64 #1 SMP Tue Sep 16 01:56:35 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux

percona-toolkit-2.2.4-1.noarch

mysql-shared-5.5.36-2.linux2.6.x86_64
mysql-devel-5.5.36-2.linux2.6.x86_64
mysql-client-5.5.36-2.linux2.6.x86_64
mysql-server-5.5.36-2.linux2.6.x86_64

Frank Cizmich (frank-cizmich) wrote :

Hello Steve,

Can you provide the schema of that 20 row table? (modify sensitive column names if you wish)
Does it have time related datatypes or data?
Did you spot any difference upon manual examination of that table between master and slave?
Master and server are same version?

Regards

Steve Simpson (sksimpson) wrote :

Both systems were in the same timezone, "America/New York". Then the slave was changed by editting /etc/sysconfig/clock ZONE="America/Chicago", rm /etc/localtime; ln -s /usr/share/zoneinfo/America/Chicago /etc/localtime

Because of kernel updates at the same time, both systems were rebooted after this we saw the errors from pt-table-checksum with any tables that had timestamps.

an example is attached, reverting the time zone fixed the issue

Steve Simpson (sksimpson) wrote :

If you change the time zone of both systems, the checksums report as correct

Frank Cizmich (frank-cizmich) wrote :

Thanks Steve.

I think we can solve this issue on pt-table-checksum using UNIX_TIMESTAMP() to normalize the time data.

Changed in percona-toolkit:
status: Incomplete → In Progress
assignee: nobody → Frank Cizmich (frank-cizmich)
importance: Undecided → Medium
milestone: none → 2.2.12
Changed in percona-toolkit:
milestone: 2.2.12 → none
tags: added: pt-table-checksum
Changed in percona-toolkit:
milestone: none → 2.2.13
Changed in percona-toolkit:
status: In Progress → Fix Committed
Changed in percona-toolkit:
status: Fix Committed → Fix Released

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Duplicates of this bug

Other bug subscribers