GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

How can I get Pt-table-checksum to show diffs?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can I get Pt-table-checksum to show diffs?

    I am using Pt-table-checksum to verify replication integrity. I have intentionally changed a column in a row in one of my replicas. Here is what I did-

    pt-table-checksum "h=192.168.1.86,u=replication,p=replication,P=3320 " --function=MD5 --databases=test


    and the output I get is

    TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE09-04T18:21:46 0 0 3 1 0 0.112 test.xyz


    My question is, should not the DIFFS column in the output say 1 instead of 0? What am I missing here?

    Can Pt-table-checksum also tell the diffs in the replicas by pin pointing to that row?

  • #2
    Hi, It should work. I have tested locally with simple master-slave replication.

    On Master,


    mysql> select count(*) from nil.test1;+----------+| count(*) |+----------+| 12 |+----------+1 row in set (0.00 sec)


    On slave,


    mysql> select count(*) from nil.test1;+----------+| count(*) |+----------+| 8 |+----------+1 row in set (0.00 sec)


    See below,


    nil@Dell-System-XPS-L502X:~$ pt-table-checksum -h 127.0.0.1 -u user -ppass --port=19771 --databases=nil TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE09-13T13:59:25 0 1 12 1 0 0.716 nil.test109-13T13:59:26 0 0 0 1 0 0.634 nil.test2

    Can you try again to check that pt-table-checksum can able to connect to slave or not?

    pt-table-checksum, only tell the differences in the replicas but If you want to check which data differences are there than you can use pt-table-sync utility with --print option. It will give you "REPLACE INTO" queries of differences.

    http://www.percona.com/doc/percona-toolkit/2.1/pt-table-sync .html

    Comment


    • #3
      Tried again, but it simply does not work for me. But I see that this_crc is different from master_crc.

      Both Master and Slave have MySQL version: 5.1.36-community-log

      Quote:
      Can you try again to check that pt-table-checksum can able to connect to slave or not?
      As far as I have understood pt-table-checksum, replication integrity is found *only* through replication. And Pt-table-checksum has to connect to the Master server only. Am I mistaken?

      Comment


      • #4
        Hi, you can also run below query to find out the difference.

        SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks
        FROM percona.checksums WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc))
        GROUP BY db, tbl;

        >As far as I have understood pt-table-checksum, replication integrity is found *only* through replication. And Pt-table-checksum has to connect to the Master server only. Am I mistaken?

        Nope, you are right. pt-table_checksum will connect to Master server and checked integrity through replication.

        Can you provide all your steps to test this utility? It might help to troubleshoot the issue.

        Comment


        • #5
          Nothing special. I have a simple Master-Slave set up both running version: 5.1.36-community-log I changed a column on the slave and pt-table-checksum does not show any any difference in the DIFF column in the output.

          Percona Toolkit version: v2.1.3

          Is there anything specific that you are looking for? I will be happy to provide more details.

          Comment

          Working...
          X