Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. The query should appear in the output of SHOW SLAVE STATUS\G
For example, in your error it says Duplicate entry '174465' for key 'PRIMARY'
. This indicates that you should look up the value 174465
in the table you are either doing an INSERT or UPDATE. If the row does exist, can you have to decide if the query halted execution will change the row's contents. If the query will simply reproduce the exact same contents, and you believe that will be the case, you can perform one of two options:
OPTION 1
Skip the error, wait 5 seconds, and view the Slave Status. Here the 5 steps for Skipping an Error
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G
When you view the Slave Status, here is what to expect
OPTION 2
Remove the row to allow replication to continue
Delete the row from the table on the Slave and do the following 4 Steps for Skipping an Error:
STOP SLAVE;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G
At the risk of sounding redundant...
When you view the Slave Status, here is what to expect
What if there are just too many duplicate key issues? Here are some of my earlier posts concerning how to use MAATKIT's mk-table-checksum, mk-table-sync, pt-table-checksum, pt-table-sync:
Best Answer
It isn't saying that there is a duplicate entry in the table already, it is saying that there is already one entry in there with that value for the primary key and it is refusing to insert a second for that reason.
I suspect if you run:
or if that key is a composite
You will find one matching row, and that your code at the point of the error is trying to insert a second.
Dealing with duplicates on insert:
If you try
INSERT
a duplicate values for a primary key (or a unique index) you will always get that error. There are a couple of ways around it: check before you insert and either do an update (if something might have changed) or just don't do anything.There is also the mysql specific
ON DUPLICATE KEY UPDATE
option (see https://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql) if you are happy to sacrifice compatibility with other RDBMSs.As of version 9.5 Postgres supports a similar feature with slightly different syntax (
ON CONFLICT DO UPDATE/NOTHING
- see https://wiki.postgresql.org/wiki/UPSERT).MS SQL Server, Oracle and some other systems support
MERGE
statements as defined in the SQL:2003 standard (see https://en.wikipedia.org/wiki/Merge_(SQL)) which is intended to achieve the same functionality and more. Be careful with any of these options if cross database compatibility is (or is likely to be in future) one of your goals.