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 expec开发者_如何学Pythont other than this.

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.

skip counter is not a viable solution always, you are skipping the records but it might affect the further records.

Here is the complete details on why sql slave skip counter is bad.

http://www.mysqlperformanceblog.com/2013/07/23/another-reason-why-sql_slave_skip_counter-is-bad-in-mysql/

You can delete bigger than duplicate rows in slave db;

DELETE FROM mydatabasename.atable WHERE ID>=174465; 

then

START SLAVE;