Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working

Question

I have several tables being reported by pt-table-checksum with CRC_DIFF between my master and slave servers using the following commands:

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --databases db --replicate systemadministration.checksums
$ pt-table-checksum h=master,u=user,p=password --databases db --replicate systemadministration.checksums --replicate-check 1

Results in:

Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
db         table2                          0        0        1 1=1
db         table3                          0        0        1 1=1
db         table4                          0        0        1 1=1
db         table5                          0        0        1 1=1
db         table6                          0        0        1 1=1
db         table7                          0        0        1 1=1

However, when I run pt-table-sync, the script returns an exit code 0 and says that there are no issues.

$ pt-table-sync --execute --verbose --no-bin-log --tables db.table1 h=master,u=user,p=password h=slave
# Syncing h=slave,p=...,u=user
# DELETE REPLACE INSERT UPDATE ALGORITHM START    END      EXIT DATABASE.TABLE
#      0       0      0      0 GroupBy   14:10:45 14:12:12 0    db.table1
$ echo $?
0

I've tried the different algorithms for the checksum command and have had no luck.

$ pt-table-checksum h=master,u=user,p=password --empty-replicate-table --algorithm=ACCUM --tables db.table1 --replicate systemadministration.checksums
DATABASE   TABLE                       CHUNK HOST                   ENGINE      COUNT         CHECKSUM TIME WAIT STAT  LAG
db         table1                          0 master                 MyISAM     141836 00141836D0139746   22 NULL NULL NULL
$ pt-table-checksum h=master,u=user,p=password --tables db.table1 --replicate systemadministration.checksums --replicate-check 1
Differences on P=3306,h=slave
DB         TBL                         CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
db         table1                          0        0        1 1=1
$ echo $?
1

Any hints or is there any other tools I can use to verify the data integrity?

Answer 1

The usual cause for this problem is that pt-table-sync is using CRC32 as a hash algorithm, because it's fast and cheap, but it can also give collisions. "codding" and "gnu" have the same crc32, for example. I recommend trying again with MD5 as the function.