Announcement

Announcement Module
Collapse
No announcement yet.

pt-table-sync script

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • 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="DB_NAME",t="TABLE _NAME ",u="username",p="password".

    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="DB_NAME",t="TABLE _NAME ",u="username",p="password".

    Can some one please help with this issue..

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

    Comment


    • #3
      Hi,

      I tried the with the option --no-check-slave and got the same error.

      Below is the syntax i used.

      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="DB_NAME",t="TABLE _NAME ",u="username",p="password".

      Comment


      • #4
        Hi,

        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.

        Comment


        • #5
          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?

          Comment


          • #6
            Hi,


            I had removed the --sync-to-master and --replicate option and tried the executing the same.

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


            I got the following error:

            Usage: pt-table-sync [OPTION...] DSN [DSN...]

            Errors in command-line arguments:
            * At least one DSN is required, and at least two are required unless --sync-to-master or --replicate is specified

            pt-table-sync synchronizes data efficiently between MySQL tables. For more
            details, please use the --help option, or try 'perldoc /usr/bin/pt-table-sync'
            for complete documentation.


            Do i need to mention the master's IP, if it has to mentioned then should i just mention it before slave ip?

            Comment


            • #7
              Hi,

              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.

              Comment


              • #8
                Hi Joshi,

                I tried the following syntax:

                pt-table-sync h=master_IP,P=master_port,D="database_name",t="tab le_name ",u="usename",p="password",h=slave_ip,P=slave_ port --print --no-check-trigger --no-foreign-key-checks --no-check-slave

                got the below output

                Usage: pt-table-sync [OPTION...] DSN [DSN...]

                Errors in command-line arguments:
                * At least one DSN is required, and at least two are required unless --sync-to-master or --replicate is specified

                pt-table-sync synchronizes data efficiently between MySQL tables. For more
                details, please use the --help option, or try 'perldoc /usr/bin/pt-table-sync'
                for complete documentation.

                Comment


                • #9
                  Hi,

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

                  Comment

                  Working...
                  X