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

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.

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

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

If you don’t want to specify password on command line you can use ~/.my.cnf in [client] section or --defaults-file file.

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
h=,u=checksumuser,p=checksumuser,P=3306 \

~/sync.log 2> ~/sync.err &