MySQL Replication Error(1062)
I am new to MySQL and after a long search I am able to configure master-slave ROW based replication. I thought it would be safe and I would not have to recheck it again and again.
But today when I did SHOW SLAVE STATUS; on slave then I found following
could not execute Write_rows event on table mydatabasename.atable; Duplicate entry '174465' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000004, end_log_pos 60121977
Can someone tell me how this can even come when master has no such error and schema on both server is the same then how could this happen. And how to fix it to make this work again and how to prevent such thing in future.
Please also let me know what else unexpected I should expect other than this.
Answer
It would never happen on master, why?
The series of SQL are replicated from master,
if the record already exist in master, mysql reject on master
but on slave, if fails and the replication position does not advanced to next SQL (it just halted)
Reason?
The insert query of that record is write directly into slave without using replication from the master
How to fix?
Skip the error on slave, like
SET GLOBAL sql_slave_skip_counter = N;
details - http://dev.mysql.com/doc/refman/5.0/en/set-global-sql-slave-skip-counter.html
Or delete the duplicate record on slave, resume the slave again (let the replication do the insertion)
The worse scenario, required you to re-do the setup again to ensure data integrity on slave.
How to prevent?
Check application level, make sure no write directly into slave
This including how you connect to mysql in command prompt
Split mysql user that can do write and read,
So, your application should use read user (master and slave) when does not require write.
Use write user (master only) for action require write to database.