Not the answer you need?
Register and ask your own question!

pt-table-checksums with interrupted connection

Harald HartliebHarald Hartlieb EntrantCurrent User Role Beginner
My scenario:
Master-Server with a database named "management" and a table "teamdriveinvitation"
And there is a slave-server with row-based replication enabled. All logins and passwords are the same.

I find that the update of the checksum-table is NOT done with the logged-in session of the percona-tool, but the newly generated checksums are transported via the replication-statements.
Is this intended?

I logged this on the slave with the mysql.log:
[email protected]:/usr/local/scripts# tail -n1 -f mysql.log |grep -i teamdriveinvitation
17137 Query SHOW TABLES FROM `management` LIKE 'teamdriveinvitation'
17137 Query SHOW CREATE TABLE `management`.`teamdriveinvitation`
17137 Query EXPLAIN SELECT * FROM `management`.`teamdriveinvitation` WHERE 1=1
3 Query REPLACE INTO `percona`.`euirtmsql001_1a` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'management', 'teamdriveinvitation', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `teamdriveid`, `invitedby`, `invited`, `message`, `dateinvited`, `role`, CONCAT(ISNULL(`invitedby`), ISNULL(`message`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `management`.`teamdriveinvitation` /*checksum table*/
3 Query UPDATE `percona`.`euirtmsql001_1a` SET chunk_time = '0.001044', master_crc = '91204457', master_cnt = '32' WHERE db = 'management' AND tbl = 'teamdriveinvitation' AND chunk = '1'
17137 Query SELECT MAX(chunk) FROM `percona`.`euirtmsql001_1a` WHERE db='management' AND tbl='teamdriveinvitation' AND master_crc IS NOT NULL
17137 Query SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM `percona`.`euirtmsql001_1a` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='management' AND tbl='teamdriveinvitation')

As you can see, there is a connection "17137" which was recently opened by the percona-tool. Then suddenly the update of the checksum-table is done from the connection "3", which is the normal replication-stream. Then it switches back to connection "17137" to check for any differences.


  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Harald;

    Yes this is expected. The tool performs the checksums on the master, and then replicates them to the slaves, where it then checks for differences.

  • Harald HartliebHarald Hartlieb Entrant Current User Role Beginner
    Thank you Scott, that clarifies a lot.
    Then my next problem is, that one command won't be replicated: It is the "delete percona.checksumtableWithMySpecialName". It won't get replicated because on the executing Server, there is a "use percona" shortly before the delete, and having suppressed the replication of the percona-database, these commands are ignored.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi Harald;

    Are you saying that you set replication-ignore-db=percona and that is causing the issue, or do you mean that the tool is doing it all on its own?

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.