Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 follow: 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: http://www.neocodesoftware.com/replication/ My my.ini file contains the following. On the server in The Netherlands

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

On the server in D.C.

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?

share|improve this question
add comment

1 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.

share|improve this answer
    
I've looked in to the Galera replication but I'm unsure if I can use it on my MyISAM database. I can't find any details on a Windows version of Galera, can I run it on Windows? –  gijs007 Jan 4 at 21:05
1  
Galera replication is innodb only and not supported on Windows. Windows is not a good platform to host your database server (IMO) for many reasons that include licensing, limitations of 3rd party software such as Xtrabackup, Galera, MHA. It seriously limits your options. –  eroomydna Jan 4 at 23:03
    
@gijs007, please see also What if I want to keep using MyISAM –  Shlomi Noach Jan 5 at 5:52
    
I can't switch to Innodb for one of my databases because of an application limitation. I could run Linux in a VM but I prefer to have a Windows solution. –  gijs007 Jan 5 at 11:57
    
Out of curiosity, what is the application limitation that prevents you from converting to InnoDB? –  Shlomi Noach Jan 5 at 12:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.