MySQL Replication Error(1062)

Question

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.

Husch

bugs.mysql.com/bug.php?id=38205 this link may help you. – Thermosetting

There are lots of reasons why this might happen, however it would have been useful if you'd checked the data store for pk=174465 on the master and slave after the error to see if the data was already the same. – Rubinrubina

Thanks, I check that one it is already there but the reason asking for the question how can it might have happened, there was some power failure before this, But doesn't mysql ensures consistency by using database transactions to update slave??? – Husch

I have the same exact issue that happens daily due to a cron job run on the master. Did you find a solution? – Schwaben

It was a long time ago, I didn't use mySQL after that so I don't remember exactly how I resolved that but I don't think that I could find a satisfactory solution or answer to that. – Husch

Answer 1

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.

Reneta

Thanks for reply, but Slave is not getting updated from anywhere I just used to connect a command line client to view the slave status no other application or connection ever opened to update the slave directly. – Husch

I solve my problem by this answer with conjunctions by this dmsimard.com/2013/12/02/fixing-mysql-fatal-error-1236 . – Darling

I deleted the duplicate record on slave. Fixed auto_increment value, then STOP SLAVE and START SLAVE; it is fixed. Thanks. – Wakeful

Answer 2

You can delete bigger than duplicate rows in slave db;

DELETE FROM mydatabasename.atable WHERE ID>=174465; 

then

START SLAVE;

Diandre

Answer 3

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/

Agustin

well could you provide an alternative solution to skip counter? – Gyre