pt-table-checksum isn't atomic

[Brandon Johnson]

Much to my surprise, I realized that pt-table-checksum isn't completely atomic.

This causes issues on high load replicated systems using InnoDB where single rows may change, and lead to false diff positives.

Enabling the general log on the slave I found this (with --log-slave-updates on. table/db names obscured for security):

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ... FROM .... /*checksum table*/
  2 Query COMMIT /* implicit, from Xid_log_event */

followed by a few queries to the table being checksummed, and then shortly after:

UPDATE `percona`.`checksums` SET chunk_time = '0.041153', master_crc = 'a0a99a5', master_cnt = '1' WHERE db = 'xxxx' AND tbl = 'xxxx' AND chunk = '1'
  2 Query COMMIT /* implicit, from Xid_log_event */

This causes a problem for 2 reasons.

1.) This particular table has writes anywhere from 1-30 times a second.
2.) Even though I can confirm by hand checking the table (it's only a single row) that it is identical on slave/master, pt-table-checksums seems to always get it wrong and says they're different.

[Brandon Johnson (brandon-evan-johnson)]

Here's the relevant create table statement fwiw:

CREATE TABLE `site_stats` (
  `ss_row_id` int(8) unsigned NOT NULL DEFAULT '0',
  `ss_total_views` bigint(20) unsigned DEFAULT '0',
  `ss_total_edits` bigint(20) unsigned DEFAULT '0',
  `ss_good_articles` bigint(20) unsigned DEFAULT '0',
  `ss_total_pages` bigint(20) DEFAULT '-1',
  `ss_users` bigint(20) DEFAULT '-1',
  `ss_admins` int(10) DEFAULT '-1',
  `ss_images` int(10) DEFAULT '0',
  `ss_active_users` bigint(20) DEFAULT '-1',
  UNIQUE KEY `ss_row_id` (`ss_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

[Daniel Nichter (daniel-nichter)]

I'm not sure I see the error, or at least how to reproduce it? The first SQL is "atomic" because replication is serialized. That's the checksum query which executes on the master then executes on slaves in the same order relative to other transactions. So the values it writes for this_cnt and this_crc are from the same version/snapshot of data on master and slave as some point in time. The second query is the "update query" (for lack of better name) because the checksum query writes this_cnt and this_crc on both master and slave, whereas the update query then simply sets master_cnt and master_crc on the master then, due to it being replicated, on slaves so that on slaves master_* can be compared to this_* (where "this" is relative to each slave which had already executed the checksum query).

So even if the sequence of transactions is:

[write1][write2]<checksum query>[write3]<update query>[write4]

when the tool checks for diffs (some time after write4, for example), it's actually checking for diffs at the point in time when the checksum query happened. So if write4 did cause master and slave to become out-of-sync, then ptc may still report no diffs, but if you ran it again, it would see the diff caused by write4.

The problem may be bug 1080765 (bug 1059680 may also be related, but it may also be a dupe of the former bug).

Let me know if you can reproduce the problem in a controlled way, or if one of those other bugs applies.

[Brandon Johnson (brandon-evan-johnson)]

This was definitely a copy of 1059680. After a few hours it showed the correct results and hasn't changed since. I was running checksum by hand fairly frequently that day. Normally we schedule it twice a day.

[Daniel Nichter (daniel-nichter)]

Ok thanks Brandon. I'll mark this a dupe of bug 1059680.

If you're coming to Percona Live MySQL Conference & Expo in April, I'm giving a talk on pt-table-checksum wherein I talk a little about its internal if you're curious (and I talk about using it, but you know that stuff already).