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

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

jorfermojorfermo EntrantCurrent User Role Beginner
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!

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • jorfermojorfermo Entrant Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • jorfermojorfermo Entrant Current User Role Beginner
    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.nemesscott.nemes MySQL Sage Current User Role Patron
    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
  • jorfermojorfermo Entrant Current User Role Beginner
    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
  • jorfermojorfermo Entrant Current User Role Beginner
    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!
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.