Does pt-table-checksum work in circular replication environments?

[rodrigo]

Hello there,

I have 3 masters in circular replication. I run pt-table-checksum in one of the masters only.
A->B->C->A

The queries do get replicated the “next” server B but I don’t think they are being replicated “properly” to the third server C. The checksums are always identical to the B server.

Thanks,
Rodrigo.

[psong]

Please check binlog_format on server B. If it is row-based, then that would be the reason for server C to always have identical checksum to B while it should not.

[rodrigo]

Hello there!

We are using MIXED binlog_format in all our servers. Would that be a problem?

[rodrigo]

We are using MySQL 5.6.17 and Percona toolkit 2.2.12

[omhl]

Hey guys,

Our production db are also setup in the same way using mixed. We use mixed due to needing to connect with SQL over linked server connections and other benefits.

Were you able to find the answer to OP’s question?

Thanks,
Oscar

[mirfan]

It’s chain replication A->B->C->A and pt-table-checksum works great only with STATEMENT format for this particular setup AFAIK. You can read further here on LIMITATION section. http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#limitations
Checksumming relies on binlog_format=STATEMENT. B receives changes from it’s Master A, which will be STATEMENT (pt-table-checksum will force this into STATEMENT binlog_format in it’s session) B executes the checksumming queries. So far so good.
But then B will also write the changes to it’s own binary log, so it can replicate to C but It will write checksum queries as ROW events in binary log so It won’t replicate to C.
pt-table-checksum only works properly when STATEMENT based formatting is used (to generate the checksum), if it’s ROW/MIXED based, the row change to the checksums table is is just replicated from it’s master, no checksumming happens…