Mysql checksum for master-slave replication returning different values, despite being identical

Question

I've set up a master slave replication in mysql and to make sure that I know if the data starts getting out of sync I created a script that:

1. Locks both databases on both servers.
2. Prints the checksums of all the tables to a text file.
3. Copy's the slave checksum text file to master.
4. Compares the both files to see if anything is different.

But on some tables I got different checksums. So I downloaded both those tables and created another script to find whats different on them. And Nothing is different! They're identical.

I only get the different checksums on tables that have float variables, so I suspect it has something to do with that.

I do have slightly different versions of mysql installed, could this be causing it?

+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.5.34                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| version                 | 5.5.34-0ubuntu0.13.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | debian-linux-gnu            |
+-------------------------+-----------------------------+

+-------------------------+-----------------------------+
| Variable_name           | Value                       |
+-------------------------+-----------------------------+
| innodb_version          | 5.5.37                      |
| protocol_version        | 10                          |
| slave_type_conversions  |                             |
| version                 | 5.5.37-0ubuntu0.14.04.1-log |
| version_comment         | (Ubuntu)                    |
| version_compile_machine | x86_64                      |
| version_compile_os      | debian-linux-gnu            |
+-------------------------+-----------------------------+

Anyone of you know what could be causing this?

Answer 1

There is better way to check whether the master and it's slaves are in sync.

Run pt-table-checksum on the master.

Then if it shows some tables are different use this script to see where exactly the difference is. http://bazaar.launchpad.net/~akuzminsky/+junk/stats-scripts/view/head:/compare_tables.sh

./compare_tables.sh master_ip slave_ip database table

Answer 2

ANALYSIS

You might be suffering from issues, not so much with FLOAT, but with VARCHAR. Why ? According to 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.

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 have written posts about this phenomenon before

• Mar 14, 2014 : Native checksum table says there's a difference, pt-table-checksum does not
• Nov 16, 2012 : Maatkit shows the MySQL replication error in one table, but won't fix it
• Jan 17, 2012 : How to check the consistency of tables in mysql

CAVEAT

Please note the For example, the storage format for VARCHAR changed between MySQL 4.1 and 5.0. This could possibly mean that there are other examples (scenarios) where FLOAT or some other data type may have issues with CHECKSUM TABLE. If your problem tables have FLOAT columns and no VARCHAR columns, then it is possible that FLOAT variables could be victimized by this issue.

CONCLUSION

You need to do the following

• Run pt-table-checksum
• Locate the problem tables
• Run applied pt-table-sync to fix differences
• Run pt-table-checksum again

If you get differences again, then the problem tables have data types with CHECKSUM TABLE issues. You will have to do one of two things

• Test the integrity of the tables in other ways
  • row counts
  • brute force SQL to compare column values
• Trust the physical/logical copy of the table you made

CAVEAT

There are those rare times (< 0.01%) that CHECKSUM TABLE does not produce consistent results. Therefore, due diligence may be necessary if syncing tables with tools. For all tables that are still not sync'd afterwards, you must resort to copying the data regardless of the CHECKSUM values after such copying of data.