Menu
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.
529
That is mysql db error...all you need to do is that, if you do your insertion and it is not true, just alter one of the columns of your table you want to insert into either from varchar to text or bigint and then redo the insertion. That will solve the problem.
Try to locate duplicate entry and delete that entry from slave DB. Once you have deleted the old entry then execute stop slave and then start slave on slave DB. Most probably replication will start again and come back to normal. If it gets stuck again for same error for some other record then repeat same steps.
When creating a primary key or unique constraint after loading the data, you can get a “Duplicate entry for key 'PRIMARY'” error. If the data in the source database is valid and there are no any duplicates you should check which collation is used in your MySQL database.
The MySQL 1064 error is a syntax error. This means the reason there's a problem is because MySQL doesn't understand what you're asking it to do. However, there are many different situations that can lead to this type of miscommunication between you and your database.
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.
126
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/
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
