pt-table-sync => Duplicate entry '2876' for key 'PRIMARY'

[jorfermo]

Hello

I’m replicating the content of a table with pt-table-sync. Everything was working nice until I found this error constantly:

pt-table-sync => Duplicate entry ‘2876’ for key ‘PRIMARY’ [for Statement "REPLACE INTO…

I checked the destination table, truncate it, deleting and recreating… and that error stills keeps showing even if there are no rows in the destination table.

What’s going on?

Thank you!

[scott.nemes]

Hi jorfermo;

What is the command you are using for the sync? And what is the replication setup of the servers you are syncing?

-Scott

[jorfermo]

Hi Scott.

This is the command:

pt-table-sync --execute --nocheck-triggers $DSN_BACKEND,t=mytable $DSN_FRONTEND,t=mytable

BACKEND is a Maria DB cluster with 3 nodes
FRONTEND is a Maria DB cluster with 2 nodes

[scott.nemes]

Hi jorfermo;

So is the frontend a slave of the backend, or is the backend a slave of the frontend? With your command, you would be syncing what is on the backend to the frontend. The fact that your destination table is empty leads me to believe things are reversed from what you you are intending.

One thing you can do is use the --print option instead of --execute to see what it would actually be doing. That tends to help get your mind around what is happening.

-Scott

[jorfermo]

No… they are not reversed. There’s no master or slave. They are 2 separated clusters and we replicate some tables between them. The destination table is empty. The print command showed many INSERTS (as expected). But when running with --execute I get primary key violations. I don’t understand why it happens if the table is empty.

As I said it’s been working fine for weeks until i got this error.

EDIT:

The --print shows only INSERTs but when running --execute it complains about REPLACE INTO.

[scott.nemes]

Hi jorfermo;

Ah I did not get the impression that it had been working from your original post.

Does the --print output show the expected destination server and table (i.e. writing to the one you expected?) As you mentioned there is no reason for it to complain if it’s writing into an empty table, so something strange is going on. And has anything changed between the two tables with the schema since last time it worked properly?

-Scott

[jorfermo]

Yes the output of --print seems to be correct.

No, there hasn’t been any change in the schemas. In fact I deleted the table and copied the schema on the other database to be sure it’s exactly the same. After doing that the problem is still there

[jorfermo]

Ok… I found the problem. The table on destination server has a trigger that was trying to insert a duplicate entry in a different table.

Thanks for your help!