List:Replication« Previous MessageNext Message »
From:Simon J Mudd Date:December 6 2009 3:15pm
Subject:Re: ERROR 1062: Duplicate entry - replication
View as plain text  
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?

If you skip the errors you are guaranteeing that the slave and master are
not consistent. Normally that's not what you want to do unless you know why.

The cause? I'm not sure.
a) could be you have a UNIQUE KEY INDEX which you are breaking with the update
b) could be you have a trigger on table which is generating perhaps 
an insert that is causing a duplicate key error.

However, if MySQL is giving you this error you really need to look at the
structure of the master and the slave. Usually an error like this is an
indication of a problem, one that you weren't aware of.

Also be ware of indexes varchars. They are usually case INSENSITIVE so
trying to set a value somewhere to "aaa" and a different value to "AAA"
could cause a duplicate key error. You can change the collation to avoid
this but the MySQL documentation is not very clear about this.

> Sometimes, errors should be ignored because they are not necessary at all.

If you are getting errors, SOMETHING IS WRONG. Where I work we replicate
to a large number of slaves and when we've had replication errors it's 
because of some sort of corruption in the table or the data having been
modified locally.

> In MySQL especially for replication, the general architecture is to have a
> host for write and other hosts for read. As a result, response time of the
> whole system should be more scalable. If something goes wrong at the master
> node where all write operations must be performed, the error may propagate
> to other replications. One of common error oftenly occurs during replication
> is duplicate entry. It is possible to prevent this error which may lead the
> replication to stop.

Again, if this happens then it certainly DID NOT happen on the master
so there is a difference between master and slave. That shouldn't happen.

I'd check your slave carefully and ensure that it is consistent with the
master. If not: rebuild it.

> This problem was reported for so long and it it supposed to be fixed
> already. However, I still got them sometimes. The solution is to specify
> slave-skip-error
> 
> Anyone know anyway to fix this? I don't want to skip that error.

Skipping the error may make the problem go away, but normally is not a 
solution.

> Running mysql 5.0.82

You need to do more extensive analysis on what is happening:

1. check the configuration on both boxes
2. check the tables are the same
3. check the affect of the statement being replicated.
   - it should lead to the same results.

If you can produce a repeatable error case then perhaps you need to file
a bug, but without more evidence I would not recommend that yet.

Simon
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