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/
