List:Replication« Previous MessageNext Message »
From:Ricardo Date:October 26 2011 8:44pm
Subject:Re: Master-Master -> duplicate entry
View as plain text  
Hey that site seems exactly what I needed!

Will look into your application and try it out!!!

Thanks a lot for your help!

Ricardo


On 26 Oct 2011, at 20:49, "navid@stripped" <navid@stripped> wrote:

> Hi Ricardo,
> 
> Have a read here:
> 
> http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
> 
> Essentially you can avoid key conflicts by specifying an increment (10
> for example) and a different offset on each server. You shouldn't need
> to skip errors if this is done properly.
> 
> If you more than 2 servers, you can also try using a tool I have
> written called MYMR (http://code.google.com/p/mymr/) - I've been using
> it successfully for some time and it works well.. it would be good to
> get some more feedback.
> 
> If at the moment you're setting this up the two servers don't contain
> the same data, remember that you might have to manually correct the
> AUTO_INCREMENT variable *for each table* so that the B server doesn't
> attempt to reuse an ID which is already taken on the A server. MYMR
> would do this for you so if you understand Python and look at the code
> (fix_autoincrements method), you can probably understand what I mean.
> 
> Hope this helps.
> 
> -- Navid
> 
> On Wed, Oct 26, 2011 at 8:20 PM, 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
>> 
>> 
>> 
>> 
>> 
>> 
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication?unsub=1
> 
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