List:Replication« Previous MessageNext Message »
From:Ricardo Freitas Date:October 26 2011 7:20pm
Subject:Master-Master -> duplicate entry
View as plain text  
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