Replication: Error duplicate entry for key PRIMARY on query. Default database: ‘mydb’. Query: ‘INSERT INTO tb_order …
Sometimes your replication will get error because of network problem, storage full or RAM full. One of the errors is: Duplicate entry for key ‘PRIMAY’ on query. I am faced this error for several times. And in this tutorial i will show you how to solve it. (I use MySQL 5.7 here).
Note: I don’t use SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; to fix the error. Because this technique is bad. Your replication will get broken after running this query (i am really sure, because i had proven it. After your replication fixed by using sql_slave_skip_counter query and the next time you got error again, you need to SKIP again, if still error, you run SKIP again, still error, run SKIP again, until you frustated, the replication still error. *The last way what you should do is recreating a new slave)
Let’s Fix
Error 'Duplicate entry '31222217' for key 'PRIMARY'' on query. Default database: 'mydb'. Query: 'INSERT INTO
tb_order(…) VALUES(…)If you got an error like that on your slave, you have to delete row >= 31222217.
Select it first, to make sure the rows is exist:
SELECT * FROM tb_order WHERE order_id >= 31222217;
On there, we get 222 rows start from order_id 31222217. We have to delete these rows to fix duplicate row.
DELETE FROM tb_order WHERE order_id >= 31222217;After executing, restart you slave by the following command:
STOP SLAVE;
START SLAVE;And you can check replication status by:
SHOW SLAVE STATUS;
If there is no error on Last_SQL_Error, your replication is now fixed.
But if you get error again, similar to previous error (but in another table) like this:
Error 'Duplicate entry '355' for key 'PRIMARY'' on query. Default database: 'mydb'. Query: 'INSERT INTO
tb_item(…) VALUES(…)You have to delete that rows as well. *Repeat the previous steps.
CONCLUSION
Error 'Duplicate entry '31222217' for key 'PRIMARY'' on query is caused by network problem, stroage full, RAM full and it will make duplicate rows on your slave. So just delete the rows on the slave. Then restart your slave. Repeat deleting rows if you get similar error.
—
Hope this tutorial: Replication: Error duplicate entry for key PRIMARY on query helps you.
