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.

Comments

  • 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.

    -Scott
  • 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?

    -Scott
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.