Consider switching to AUTO_INCREMENT. (It will require a number of source changes, but
probably will simplify the code.)
If you have this:
your code could get confused. Your logic should probably never ROLLBACK the update;
instead, it should probably be prepared to 'burn' numbers.
If you are using MyISAM, well...
Was there a crash at any time? On the Master or on the Slave?
> -----Original Message-----
> From: Marcus Bointon [mailto:marcus@stripped]
> Sent: Monday, October 01, 2012 6:55 AM
> To: replication@stripped replication
> Subject: Re: Replication breaks - slave differs from master
> On 1 Oct 2012, at 14:33, "Stallmann, Andreas" <AStallmann@stripped>
> > 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:
> 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 Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/ UK
> info@hand CRM solutions marcus@stripped |
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe: http://lists.mysql.com/replication