From: Rick James
Date: August 1 2012 8:10pm
Subject: RE: How often the slave to pull data from master?
List-Archive: http://lists.mysql.com/mysql/227927
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1489DB9D6D@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

BEGIN;
UPDATE Inno_tbl SET x =3D 100;
INSERT INTO Myisam_tbl SELECT x FROM Inno_tbl;
UPDATE Inno_tbl SET x =3D 200;
INSERT INTO Myisam_tbl SELECT x FROM Inno_tbl;
COMMIT;

To get the right values in Myisam_tbl, the transacted and non-transacted co=
mmands must be interleaved.  (I am assuming SBR, not RBR.)

> -----Original Message-----
> From: Shawn Green [mailto:shawn.l.green@stripped]
> Sent: Wednesday, August 01, 2012 12:07 PM
> To: Rick James
> Cc: David Lerer; Zhigang Zhang; mysql@stripped
> Subject: Re: How often the slave to pull data from master?
>=20
> On 8/1/2012 1:13 PM, Rick James wrote:
> > That leads to another question...
> > Does this 'separate cache' contain an extra copy (vs the 'only' copy)
> of the non-transacted events?
> > I would expect that it must be an extra copy.
> >
>=20
> No. The non-transacted events are applied to the tables they belong to
> as they are executed.  We track them in this cache for replication
> purposes. Are you worried about a race between a crash and the time
> where the cache is applied to the main binary logs?
>=20
> This is a very unlikely event for two reasons:
>=20
> 1) the non-transacted change must occur within a transaction
>=20
> 2) most users that use transactions only use transaction-enabled
> storage engines (InnoDB).
>=20
> So in order to lose non-transacted changes from a crash that happens
> before the cache is appended to the binary log, you must
> a) have a sequence of statements (or ROW-based changes) that is smaller
> than --binlog-cache-size limit
> b) have a mixed-mode transaction.
>=20
> If this is a valid concern for your usage pattern set --binlog-cache-
> size to a smaller value.
> http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-
> log.html#sysvar_binlog_cache_size
>=20
> --
> Shawn Green
> MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
> and Software, Engineered to Work Together.
> Office: Blountville, TN
>=20