pt-table-sync script

  • Filter
  • Time
  • Show
Clear All
new posts

  • pt-table-sync script

    Hi All,

    I am working on master-slave replication.
    Some how two table in slave are showing the difference when i ran pt-table-chekcsum.

    The two tables which are having difference doesn't have unique index or primary key.
    i am getting follwoing error while running the table sync for one of the two tables.

    Can't make changes on the master because no unique index exists at /usr/bin/pt-table-sync line 9241.

    The pt-table-sync syntax i used is listed as follows:

    pt-table-sync --print --sync-to-master --no-check-trigger --no-foreign-key-checks --replicate percona.checksum h="slave_host_name",P=port_no,D="D B _NAME",t="TABLE_NAME ",u="username",p="password& quo t;.

    later i found the option --no-check-slave can be used if no unique index are present on a table.

    pt-table-sync --print --sync-to-master --no-check-trigger --no-foreign-key-checks --replicate percona.checksum --no-check-slave h="slave_host_name",P=port_no,D="D B _NAME",t="TABLE_NAME ",u="username",p="password& quo t;.

    Can some one please help with this issue.. ....
    Last edited by mageshkumar24; 06-19-2014, 02:43 AM.

  • #2
    Hi Magesh,

    As per the documentation, "When there is no unique key on the table, there is no choice but to change the data on the slave, and pt-table-sync will detect that you're trying to do so. It will complain and die unless you specify --no-check-slave"

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

    Generally, its unsafe to make changes on slave directly but if you don't have any other way, you have to use --no-check-slave option. Because by default, pt-table-sync will always give error when there is no unique index on table.

    Have you tried to use that option? are you facing any issue with that?


    • #3

      I have tested the same scenario locally and I'm also getting the same error. Looks like its bug but still need to check completely. will let you know, as soon as I get some more information on this.


      • #4
        Hi Magesh,

        While testing thoroughly, found out that you don't need to use --replicate and --sync-to-master options when you want to sync tables which doesn't have unique keys.

        Because, when synchronizing a server that is a replication slave, with the --replicate or --sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly. When it makes changes on master, it uses REPLACE which won't work unless there's a unique index.

        So, when you'll run pt-table-sync without --replicate and --sync-to-master options. It makes changes on directly slave rather than master and syncs the tables without any error. I have tested it successfully.

        Can you please check again without using above options and let me know if you face any issue?


        • #5

          Yes,you have to mentioned two DSNs, master and slave servers. i.e

          pt-table-sync h=, P= ,D="supplier_prod ",t="explore_market_results",u="backup",p="erplex1 ng" h="trprsl3db02.intra.searshc.com",P=6446 --print --no-check-trigger --no-foreign-key-checks --no-check-slave > sbd02_explore_market_results.sql

          I assume that trprsl3db02.intra.searshc.com is slave server.


          • #6

            Keep space between two DSN rather than comma and try again. (p="password",h=slave_ip) should be like (p="password" h=slave_ip)


            • #7
              Hello Joshi,

              I am trying to this delete this entire post, but not able to do so.
              since these post have few sensitive information i request you to delete this entire post.
              can you please remove this post