List:Replication« Previous MessageNext Message »
From:Rick James Date:October 27 2011 5:56pm
Subject:Re: Master-Master -> duplicate entry
View as plain text  
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"<ricardo@stripped>
> 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

Thread
Master-Master -> duplicate entryRicardo Freitas26 Oct
  • Re: Master-Master -> duplicate entrynavid@navid.it26 Oct
    • Re: Master-Master -> duplicate entryRicardo26 Oct
  • Re: Master-Master -> duplicate entryRick James27 Oct
    • Re: Master-Master -> duplicate entryRicardo27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryJohan De Meersman27 Oct
          • Re: Master-Master -> duplicate entryRicardo27 Oct
          • Re: Master-Master -> duplicate entryRicardo27 Oct
          • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
            • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
              • Re: Master-Master -> duplicate entrynavid@navid.it27 Oct
              • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                  • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                    • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                    • Re: Master-Master -> duplicate entryRicardo27 Oct
                      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                        • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
                          • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
                            • Re: Master-Master -> duplicate entry (UUID lecture)Rick James27 Oct
            • Re: Master-Master -> duplicate entryJohan De Meersman28 Oct
        • Re: Master-Master -> duplicate entryRicardo27 Oct
  • Re: Master-Master -> duplicate entrylist27 Oct
    • RE: Master-Master -> duplicate entryRicardo Freitas27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryMySQL)27 Oct
    • Re: Master-Master -> duplicate entryRick James27 Oct
      • Re: Master-Master -> duplicate entryMarcus Bointon27 Oct
        • Re: Master-Master -> duplicate entryRick James27 Oct
          • Re: Master-Master -> duplicate entryJohan De Meersman28 Oct
            • Re: Master-Master -> duplicate entryRick James28 Oct
              • Re: Master-Master -> duplicate entryMats Kindahl31 Oct
      • Re: Master-Master -> duplicate entrylist27 Oct