Caveats With pt-table-checksum Using Row-Based Replication, and Replication Filters

Caveats With pt-table-checksum Using Row-Based Replication, and Replication Filters

PREVIOUS POST
NEXT POST

pt-table-checksum row based replication caveatAs per the documentation, pt-table-checksum is a tool to perform online replication consistency checks by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master.

The master and each slave insert checksums into the percona.checksums table, and these are later compared for differences. It’s fairly obvious that the checksums need to be determined independently on each node, and so these inserts must be replicated as STATEMENT and not ROW. Otherwise, the slaves would just insert the same checksum as the master and not calculate it independently.

The tool only requires binlog_format=STATEMENT  for its own session. It sets this itself on the master, but will error if this isn’t already set on each slave node. The reason for the error is that the statement to change the session’s binlog_format will not be replicated. So if a slave has binlog_format=ROW then the slave itself will execute the checksum correctly, but the results will be written as a ROW. Any slaves of this slave in the chain will just insert the same result. See bug 899415.

This is only a problem if we have chained replication, and the error can be skipped with --no-check-binlog-format so for simple replication setups with ROW or MIXED replication we can still use the tool. If we do not have a slave-of-slave style chained topology, then there’s no need to worry about this.

However, there is one caveat to be aware of if you’re using replication filters: when a slave isn’t replicating a particular database due to binlog-ignore-db, this setting behaves differently with ROW based replication (RBR) vs. STATEMENT based.

Per the documentation, with RBR,

will cause all updates to testing.* to be skipped. With STATEMENT-based replication it will cause all updates after USE test_database;  to be ignored (regardless of where the updates were being written to).

pt-table-checksum operates in the following way:

Due to the use testing the slave will then skip these statements with no errors, and simply not write into percona.checksums.

As per the documentation:

The tool monitors replicas continually. If any replica falls too far behind in replication, pt-table-checksum pauses to allow it to catch up. If any replica has an error, or replication stops, pt-table-checksum pauses and waits.

In this case, you will see the tool continually wait, with the following debug output:

We don’t recommend using the tool with replication filters in place, but if --no-check-replication-filters is specified you should be aware of the differences in how different binlog formats handle these filters.

One workaround would be to replace

With the following which will just ignore writes to that database:

More resources

You can read more about pt-table-checksum in this blog post MySQL Replication Primer with pt-table-checksum and pt-table-sync

The latest version of Percona Toolkit can be downloaded from our website. All Percona software is open source and free.

Photo by Black ice from Pexels

 

PREVIOUS POST
NEXT POST

Share this post

Comments (2)

  • Mark Reply

    Hi James,
    thank you for the article.
    I have a replication chain MASTER 1 > SLAVE 1 > SLAVE 2 (ROW REPLICATION)
    I just want compare MASTER 1 with SLAVE 2

    1) Can I use pt-table-checksum command?

    2) You say pt-table-checksum changes replication to statement in its own session on the master.
    So if the master is live, will it be affected by the STATEMENT replication while performing non pt-table-checksum operations? Or will it continue replicating as ROW (except from checksum data)?

    December 1, 2018 at 3:08 pm
    • James Lawrie Reply

      Hi Mark,

      Thank you for the comment. You can’t directly compare Master 1 -> Slave 2 this way if using RBR because even if you specify the slave DSN and don’t connect to the first slave directly, the CHECKSUM query will still flow down the replication chain and be inserted as a row by the time it gets to the second slave (so it will look as though it’s comparing with slave2 but really it’s comparing against slave1).

      You could either run pt-table-checksum twice – once for master1 and slave1 and the second time for slave1 and slave2 – or you could temporarily set the binlog_format to be STATEMENT on slave1 so that the CHECKSUM insert was still a statement when it got to the second slave, and then compare Master 1 and Slave 2 directly.

      December 2, 2018 at 7:03 am

Leave a Reply