List:Replication« Previous MessageNext Message »
From:Marcus Bointon Date:October 1 2012 1:55pm
Subject:Re: Replication breaks - slave differs from master
View as plain text  
On 1 Oct 2012, at 14:33, "Stallmann, Andreas" <AStallmann@stripped> wrote:

> One single table out of many differs after a while between mysql master and slave.
> The statement altering the table is:
> 
> UPDATE SEQ_GEN SET SEQUENCE_VALUE = SEQUENCE_VALUE + 10 WHERE SEQUENCE_NAME =
> 'Client_Object_SEQ'
> 
> We already checked, that no other process but the replication is altering the slave.
> 
> The logs show, that some transactions are completed in a different order on the
> master and the slave. Still, it’s in no way the obvious reason for the difference
> between the two hosts.
> 
> Any ideas? Any (kind) RTFMs? :-)

I assume you're using InnoDB? InnoDB should treat that update like a transaction, so it
should be atomic and will lock the row, so there shouldn't be an opportunity to get out
of sync, and as you say, the order of transactions shouldn't make any difference to this
operation anyway. You could try making the transaction more literal:

BEGIN;
SELECT * FROM SEQ_GEN WHERE SEQUENCE_NAME = 'Client_Object_SEQ' FOR UPDATE;
UPDATE SEQ_GEN SET SEQUENCE_VALUE = SEQUENCE_VALUE + 10 WHERE SEQUENCE_NAME =
'Client_Object_SEQ';
COMMIT;

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



Thread
Replication breaks - slave differs from masterAndreas Stallmann1 Oct
  • Re: Replication breaks - slave differs from masterMarcus Bointon1 Oct
    • RE: Replication breaks - slave differs from masterRick James1 Oct