From: Rick James Date: October 27 2011 5:56pm Subject: Re: Master-Master -> duplicate entry List-Archive: http://lists.mysql.com/replication/2259 Message-Id: <4EA99B6B.9050107@yahoo-inc.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Synchronous replication has a serious limitation -- If the second master exists for HA, and the building with both masters is hit by a tornado, earthquake, flood, etc, then dual-master did not help. If you move the other master to a remote location, then the delays to do the sync could be unacceptable. On 10/27/11 9:08 AM, list@stripped wrote: > On Wed, 26 Oct 2011 20:20:18 +0100, "Ricardo Freitas" > wrote: >> Hello, my fellow developers and admins >> >> >> >> I have a question regarding a master-master replication system. >> >> >> >> I implemented one with the help of some of you guys - however I had some >> problems regarding the agent so I have a master-master and not a mmm > system >> - and now I come across the awfull 1062 error - the 'Error 'Duplicate > entry >> '123020901' for key 'PRIMARY'' on query.' >> >> >> >> The issue here is I sometimes insert on master 1 (let's call it like > this) >> and sometimes on master 2. I thought this wouldn't be an issue as both >> database would talk to one another however I think this might be the > origin >> of the problem. >> >> >> >> I already changed the system to always force the inserts query to be on >> Master 1 however I would like to know how to avoid this. I read > something >> about configuring a skip-slave-error but I don't think this would be > that >> much of a solution as sometimes things happens and it's important to > have >> some kind of notion or warning. >> >> >> >> I read about the auto-increment-increment = 2 or even the increment > offset. >> >> >> If I configure Master 2 to auto-increment-increment = 2 and master 1 to >> auto-increment-increment = 1, would this help? >> >> >> >> Master 1 - example >> >> Row 1 >> >> Row 2 >> >> Row 3 >> >> >> >> Master 2 - example >> >> >> >> Row 2 >> >> Row 4 >> >> Row 6 >> >> >> >> As far as I'm concerned, eventually sometimes collisions would occur. I >> thought that PK's weren't replicated as the database would just receive > the >> insert query and viola. >> >> >> >> How can I avoid this in the future? >> >> >> >> I read about UUID as well but as I've inherit this system I have a lot > of >> sites already relaying on the pk (for query purposes) so I can't just >> changed this. >> >> >> >> Thanks a lot! >> >> >> >> Ricardo > Ricardo, > > I feel your pain as we had attempted to setup a master/active > master/passive replications strategy much like this, and ran into many of > the same problems. We eventually gave up after finding a really neat open > > source project called Galera, which gives you synchronous multi-master > replication (yes I said synchronous). We've been testing for a few weeks > and we're very impressed. > > Here is the home page: > > http://codership.com/products/mysql_galera > > And here is a really good explanation of Galera by the folks at > severalnines: > > http://www.severalnines.com/clustercontrol-mysql-galera-tutorial > > Hope this helps! > > > -- Rick James - MySQL Geek