List:Replication« Previous MessageNext Message »
From:Rick James Date:October 1 2012 9:55pm
Subject:RE: Replication breaks - slave differs from master
View as plain text  
Consider switching to AUTO_INCREMENT.  (It will require a number of source changes, but
probably will simplify the code.)

If you have this:
  BEGIN;
  UPDATE Seq_num...
  ...
  ROLLBACK;
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>
> 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/
> 
> 
> 
> 
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication

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