Mysql – Master – Master replication Duplicate entry error

master-master-replicationMySQLmysql-5.6replication

I've got a Master – Master replication setup and it worked fine for a couple of days but I keep getting: Duplicate entry errors after a few days.

My setup is as follows: One server in The Netherlands and one server in Washington D.C. both servers are running Windows Server 2012 R2 and MySQL 5.6.15 X64.

I've followed this tutorial.

My my.ini file contains the following:

Netherlands server

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=joomlatest
binlog_do_db=sapp
binlog_do_db=tcadmin
binlog_do_db=whmcs

Washington D.C. server

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_do_db=joomlatest
binlog_do_db=sapp
binlog_do_db=tcadmin
binlog_do_db=whmcs

Both servers keep stopping the slave and both report the same: Duplicate entry error. For example:

Error 'Duplicate entry '4353' for key 'PRIMARY''
on query.

How can I prevent this from happening?

Best Answer

The tutorial you linked to is, I'm afraid, very optimistic. It forgets to note that it only covers the case of an auto generated AUTO_INCREMENT PRIMARY KEY.

The methods it suggests do not cover the general case for a UNIQUE KEY, including, of course, a general PRIMARY KEY.

The article does mention that "The most common problem with replication is primary key collision". It then completely fails to mention all the other problems...

So, anyway, any UNIQUE KEY collision will render your replication broken, possibly in both directions.

Which table was it that failed your replication? Was that a table with an AUTO_INCREMENT PRIMARY KEY or a "general" PRIMARY KEY (ie on non-AUTO_ICNREMENT columns)?

To solve the general case of unique keys, your application must be very aware of its location (Netherlands or Washington DC), and must refrain from writing data that may cause collision. Alternatively it might want to use some synchronizing/locking mechanism; that would of course induce latency as data must cross the Atlantic.

Might I also suggest that the use of binlog_do_db is also naive and dangerous. Are you sure you don't want to replicate the mysql schema? What happens when you add new schemas? The general correct solution is to have full replication without filtering, or otherwise have a very good explanation why not to do so.

Finally, I highly discourage the use of active-active master-master replication. I had customers using it in the past. The amount of trouble they went into because of that setup, and in spite of my counter-recommendations, led to many ruined weekends and holidays.

You might want to look at Galera replication. It's a synchronized master replication solution, and I hear it works over WAN. This will allow your application to remain ignorant. Watch out for network failures, as one part of your cluster will turn immutable.

Related Question