MySQL Checksum Table – Native vs pt-table-checksum Differences

MySQLpercona-toolsreplication

I was setting up a slave and doing some table checksums to check things were ok before getting them live.

I did a native mysql checksum tables on the slaves after syncing slaving to a stopped point and comparing against a preexisting slave.

Two tables differed. To drill down I ran pt-table-checksum which reported both the old and new slave had no differences and the same row counts.

To get a third opinion I did a select * into outfile ordered by PK and ran an md5sum on the outputs. Those too both matched.

One noticeable difference is the newer slave is running a slighter newer version (5.5.34 vs 5.5.29); both percona builds.

Is this version difference something to be expected in causing differences in checksums? If so it seems odd it was just two tables out have a couple dozen.

Best Answer

Don't worry, your situation is not unique. I encountered the exact same problem with mk-table-checksum years ago (See my Nov 16, 2012 post Maatkit shows the MySQL replication error in one table, but won't fix it)

Here is something frightening from the MySQL Documentation on CHECKSUM TABLE

The checksum value depends on the table row format. If the row format changes, the checksum also changes. For example, the storage format for VARCHAR changed between MySQL 4.1 and 5.0, so if a 4.1 table is upgraded to MySQL 5.0, the checksum value may change.

Important If the checksums for two tables are different, then it is almost certain that the tables are different in some way. However, because the hashing function used by CHECKSUM TABLE is not guaranteed to be collision-free, there is a slight chance that two tables which are not identical can produce the same checksum.

I meniotned this caveat before back in January 2012 : How to check the consistency of tables in mysql

Sometimes, I just like directly using mk-table-sync (sorry Percona, I'm old fashioned) with the --print options and tell me the SQL differences. As the MySQL Documentation says, there could be differences because of the VARCHAR alignment or ROW FORMAT. If mk-table-sync/pt-table-sync insists there are no changes, you could trust it 99.9 % of the time. If you don't trust it, mysqldump the table and reload it. Chances are the checksum may not match again.

Related Question