List:Replication« Previous MessageNext Message »
From:Marcus Bointon Date:June 4 2009 8:35am
Subject:Re: Slave has change data without load data from master
View as plain text  
On 4 Jun 2009, at 02:14, saiful haqqi wrote:

>  Last_Error: Error 'Duplicate entry '5' for key 1' on query. Def
> ault database: 'replika'. Query: 'INSERT INTO `test` (`id`, `val`)  
> VALUES
> (NULL,
> 'sembarang')'


I'm fairly convinced that there are some major bugs with autoinc IDs  
in MySQL replication. I've usually only run into this under high load  
and concurrency, but it looks like you have a simpler case.

Make sure you have auto_increment_increment (should be > 1 and the  
same on each server) and auto_increment_offset (should be different on  
each server) set to sensible values. These settings generally only  
apply when using master-master, but setting them will mean that if you  
do inadvertently do inserts on a read-only slave, they will not cause  
clashes. You might also consider setting slaves to read-only (this  
still allows the replication user to write).

Generally you need to rebuild the slave to fix this problem properly  
(some will tell you to skip the problem query, but then master and  
slave will be out of sync).

Marcus
-- 
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/


Thread
Slave has change data without load data from mastersaiful haqqi4 Jun
  • Re: Slave has change data without load data from masterMarcus Bointon4 Jun