Recently I was tasked with investigating slippage between master and slave in a standard replication setup.
The client was using Maatkit’s mk-table-checksum to check his slave data was indeed a fair copy of that of the master.
He could then examine the checksum.checksum table and see all was well, however there were various tables with different crc values.
So, now I needed to find out what was updating the table. Here is where tools like mysqlsla and Maatkit’s mk-log-parser come into their own as they both allow you to quickly parse the binary log files, extracting the relevant statements.
Check out http://hackmysql.com/mysqlsla_filters for how to filter by statement.
Looking through the binary logs I could see this table is actually an audit table for changes to the Foo table. The trail is kept using two triggers on that table.
So whats the problem with that?, well there is a situation where two overlapping transactions updating the Foo table can be reordered once serialized on the slave.
Here is an example:
I recreated the tables and triggers, populating the Foo table with a handful of rows and then ran the following.
Here is the update trigger:
Now when these statements get run on the slave they will be serialized, thus changing the order of the inserts made by the trigger. The Foo_History table is now out of sync.
Master:
Slave:
As you can see from the above, the updates were performed in a different order, with the inserts being assigned a different Foo_History_Id. This is because the statements are written to the binary log in commit order.
