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

Can't make changes on the master because no unique index exists

NickTailorNickTailor EntrantCurrent User Role Beginner
I want to run pt-table-sync to sync up my slave.

I ran a dry-run and got a few "Can't make changes on the master because no unique index exists"

What does this mean?

Second Question. Is pt-table-sync going to sync data from my slave to my master? Or is it going to push the existing data from my master to the slave through replication? Because the documentation makes it sound like its gonna to write from the slave to the master to get everything in sync again, which is a definitely not what I want.


  • mirfanmirfan Database Administrator Inactive User Role Beginner
    This means your table is not having primary or unique key. pt-table-sync uses REPLACE query to synchronize data from master to slave. In absence of primary or unique key it will be normal INSERT which means it can't be run from master. You need to make changes directly on slave via option --no-check-slave.

    By default pt-table-sync synchronizes the data from master to slave via --sync-to-master option. It runs REPLACE queries which will not affect master and through replication those queries runs on slave and changes data safely on slave. Let me quote relevant paragraph from the documentation.

    "This tool changes data, so for maximum safety, you should back up your data before using it. 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. This is in general the only safe way to bring a replica back in sync with its master; changes to the replica are usually the source of the problems in the first place. However, the changes it makes on the master should be no-op changes that set the data to their current values, and actually affect only the replica."

    For the First question, you will find details under REPLICATION SAFETY in pt-table-sync documentation here
  • NickTailorNickTailor Entrant Current User Role Beginner
    Thank you Mirfan, that is what I thought, however I just wanted to make sure, the writing at first glance does sound like it could screw ya over. So my next question in order to get around the errors I got from dry-run I would simply need to add the "-no-check-slave" option to the my line that I'm going to run from my master?

    Also is there anyway to run the pt-table-sync without specifying credentials? I have my mysql running without root password set since its not reachable from the outside world. I've had to set root/pass in order for pt-table-checksum to run through.


    Nick Tailor
  • mirfanmirfan Database Administrator Inactive User Role Beginner
    If you don't want to specify password on command line you can use ~/.my.cnf in [client] section or --defaults-file file.
  • BoilBoil Entrant Inactive User Role Beginner
    Hello!Is anybody here?I came with the same question now.When I try appending a "--no-check-slave" to the command,it keeps in vain.As following:
    /usr/local/PerconaToolkit/bin/pt-table-sync --print --charset=utf8 --no-check-slave \
    --replicate percona.checksums \
    --sync-to-master \
    h=,u=checksumuser,p=checksumuser,P=3306 \
    > ~/sync.log 2> ~/sync.err &
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.