Percona Toolkit moved to https://jira.percona.com/projects/PT

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

Bug #1388870 reported by Steve Simpson
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
Percona Toolkit moved to https://jira.percona.com/projects/PT 2.2.13

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

lp:~percona-toolkit-dev/percona-toolkit/ptc-errors-on-slave-with-different-time-zone-1388870
Daniel Nichter: Needs Fixing
Diff: 155 lines (+38/-13)
7 files modified
bin/pt-table-checksum (+3/-3)
lib/RowChecksum.pm (+3/-3)
t/lib/RowChecksum.t (+4/-4)
t/pt-table-checksum/bugs.t (+25/-0)
t/pt-table-checksum/samples/chunkidx004.txt (+1/-1)
t/pt-table-checksum/samples/chunkidx005.txt (+1/-1)
t/pt-table-checksum/samples/n-chunk-index-cols.txt (+1/-1)
Revision history for this message
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
Revision history for this message
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?

Revision history for this message
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
Revision history for this message
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

Revision history for this message
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

Revision history for this message
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

Revision history for this message
Steve Simpson (sksimpson) wrote :

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

Revision history for this message
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.

Frank Cizmich (frank-cizmich)
Changed in percona-toolkit:
status: Incomplete → In Progress
assignee: nobody → Frank Cizmich (frank-cizmich)
importance: Undecided → Medium
milestone: none → 2.2.12
Frank Cizmich (frank-cizmich)
Changed in percona-toolkit:
milestone: 2.2.12 → none
Frank Cizmich (frank-cizmich)
tags: added: pt-table-checksum
Frank Cizmich (frank-cizmich)
Changed in percona-toolkit:
milestone: none → 2.2.13
Frank Cizmich (frank-cizmich)
Changed in percona-toolkit:
status: In Progress → Fix Committed
Hrvoje Matijakovic (hrvojem)
Changed in percona-toolkit:
status: Fix Committed → Fix Released
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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  
Everyone can see this information.

Duplicates of this bug

Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.