From: Rick James
Date: October 1 2012 9:55pm
Subject: RE: Replication breaks - slave differs from master
List-Archive: http://lists.mysql.com/replication/2387
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB148CF91FE3@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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 th=
e 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
>=20
> On 1 Oct 2012, at 14:33, "Stallmann, Andreas" <AStallmann@stripped>
> wrote:
>=20
> > 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 =3D SEQUENCE_VALUE + 10 WHERE
> SEQUENCE_NAME =3D '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? :-)
>=20
> 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:
>=20
> BEGIN;
> SELECT * FROM SEQ_GEN WHERE SEQUENCE_NAME =3D 'Client_Object_SEQ' FOR
> UPDATE; UPDATE SEQ_GEN SET SEQUENCE_VALUE =3D SEQUENCE_VALUE + 10 WHERE
> SEQUENCE_NAME =3D 'Client_Object_SEQ'; COMMIT;
>=20
> Marcus
> --
> Marcus Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/ UK
> info@hand CRM solutions marcus@stripped |
> http://www.synchromedia.co.uk/
>=20
>=20
>=20
>=20
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    http://lists.mysql.com/replication