List:Replication« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:October 27 2011 6:25pm
Subject:Re: Master-Master -> duplicate entry
View as plain text  
On 10/27/2011 13:22, Marcus Bointon wrote:
> On 27 Oct 2011, at 18:47, Ricardo Freitas wrote:
>
>> 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:)
>
> You shouldn't be starting replication on an empty server. The current autoinc value
> is effectively part of the table definition (notice it's in the output of 'show create
> table') so it travels with the table. So when you import this table it may have the
> current autoinc of 177, and the primary might assign 178 as the next pk, but your slave
> (with a bigger offset) might assign 179, thus you won't get a clash.
>
> Normal practice is to take a snapshot at a known binlog position, restore that
> snapshot on the new slave, start replication from the point that you got to in the binlog
> and let it catch up via replication. When it's in sync, (and no writes are going to the
> second master), you can close the replication loop by setting up replication in the other
> direction. Percona's Xtrabackup provides a really effective way of doing this with no
> downtime or locks.
>
> Marcus

The other thing to remember is that the auto_increment value to assign 
to the new row is actually part of the replication stream. If it wasn't, 
there would be no way to have a duplicate entry conflict. The receiving 
server would have simply assigned the next available number.

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
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