List:Replication« Previous MessageNext Message »
From:navid@navid.it Date:October 26 2011 7:49pm
Subject:Re: Master-Master -> duplicate entry
View as plain text  
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
>
>
>
>
>
>
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