List:Replication« Previous MessageNext Message »
From:Ricardo Freitas Date:October 27 2011 4:47pm
Subject:RE: Master-Master -> duplicate entry
View as plain text  
Thanks a lot for the tip!!

Unfortunately I have several (replication included) tables that run myisam. Will look more
into it, though. Thanks:)

Guys, one last question as I just can't test it right now.

A practical example.

You have a table with several records already inserted. You configure a master-master (or
master-slave for that matter) and you tell the master_2(or slave) that it should start
slaving as of now.

When I insert a record into the Master_1, it will get, for instance, the pk autoincrement
value of 177.

Remember the Master_2 is empty. When the record is replicated to the Master_2, the id of
the pk passed on is 177 or does the Master 2 ignore this factor and just inserts the
record with a autoinc value of 1?

From what I learned from these precious exchange of e-mails, it will just ignore wherever
the autoinc value is and save the autoinc value from the Master 1.

Is this correct? If so, I can finally understand why this duplicate inconsistencies occur,
otherwise I still don't understand:)

Thanks a lot to everyone.

-----Original Message-----
From: list@stripped [mailto:list@stripped] 
Sent: quinta-feira, 27 de Outubro de 2011 17:09
To: replication@stripped
Cc: ricardo@stripped
Subject: Re: Master-Master -> duplicate entry

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!



-- 
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