List:Replication« Previous MessageNext Message »
From:MARK CALLAGHAN Date:December 6 2009 4:43pm
Subject:Re: ERROR 1062: Duplicate entry - replication
View as plain text  
On Sun, Dec 6, 2009 at 8:34 AM, Simon J Mudd <sjmudd@stripped> wrote:
> On Sun, Dec 06, 2009 at 08:27:28AM -0800, MARK CALLAGHAN wrote:
>> On Sun, Dec 6, 2009 at 7:15 AM, Simon J Mudd <sjmudd@stripped> wrote:
>> > song.voong@stripped (Song Voong) writes:
>> >
>> >> I been getting this error and my replication stoppped.
>> >>
>> >> ERROR 1062: Duplicate entry
>> >>  Last_Error: Error 'Duplicate entry '1010769-50-65% Cttn, 35%
> Rayon' for key
>> >> 2' on query. Default database: 'm3'. Query: 'Update item_attribute SET
>> >> lupdateby='TINAA',item_id='1010769',value='65% Cttn, 35%
>> >> Rayon',source=3,attribute_definition_id='50' WHERE item_attribute_ID =
>> >> 118838555'
>> >>
>> >> I know I can do a  slave-skip-errors 1062 and by pass it, I been
> reading the
>> >> forum and it is a common issues with replication, But what is causing
> this?
>> >> I mean it is updating stuff on the master, and slave should get updated
> too
>> >> . NO?
>>
>> Simon listed many good things to check. There are two other things to check:
>> 1) confirm the slave did not crash/restart (either mysqld restart or
>> server reboot). Slave replication state is not crashproof and on a
>> crash transactions can get replayed. This is easy to check.
>
> Note: I think even with the 5.0 version being used if you don't configure the
> binlogs to be synced with the commits you can find them up to 30 seconds
> behind, as mysql won't force them to disk. If the host crashes these changes
> will be lost and on restart mysql will try to replay these statements.
> Depending on what they are this can make the content of the different
> from what it should be. So watch this. Default MySQL behaviour is NOT safe.

With sync_binlog=1 on the master, crash recovery keeps InnoDB and the
binlog in sync. A future release of MySQL will support an option to
sync relay-log.info, but even that won't fix the problem on the slave.

The problem on the slave is that the commit to InnoDB by the slave SQL
thread and the update of relay-log.info that follows the commit are
not atomic. If the slave crashes after the commit and before
relay-log.info is updated (or flushed to disk), then the slave will
replay transactions on restart. MySQL has work in progress to fix this
in a future release.

I assume that the slave and master use InnoDB. If you use MyISAM then
you cannot expect to avoid problems like this.

-- 
Mark Callaghan
mdcallag@stripped
Thread
ERROR 1062: Duplicate entry - replicationSong Voong3 Dec
  • Re: ERROR 1062: Duplicate entry - replicationSimon J Mudd6 Dec
    • Re: ERROR 1062: Duplicate entry - replicationMARK CALLAGHAN6 Dec
      • Re: ERROR 1062: Duplicate entry - replicationSimon J Mudd6 Dec
        • Re: ERROR 1062: Duplicate entry - replicationMARK CALLAGHAN6 Dec
          • Re: ERROR 1062: Duplicate entry - replicationSimon J Mudd7 Dec