From: Michael Widenius
Date: May 14 2009 1:03pm
Subject: re: Feedback needed for WL#2687
List-Archive: http://lists.mysql.com/internals/36686
Message-Id: <18956.5770.502930.969186@narttu.askmonty.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


hi!

>>>>> "Alfranio" == Alfranio Correia <Alfranio.Correia@stripped> writes:

Alfranio> Hi all,
Alfranio> Find below a proposal on how to handle WL#2687.
Alfranio> There is also a draft of a patch available in a related bug (BUG#40278).

Alfranio> ACTIONS:

Alfranio> 1 - Feedback
Alfranio> 2 - Review of the patch (Jasonh, Luis).
Alfranio> Mats and Andrei, could you both also take a look?

Alfranio> cheers.

Alfranio> BACKGROUND
Alfranio> ----------
Alfranio> Mixing transactional and non-transactional tables is not supported by the
Alfranio> current code. In a nutshell, non-transactional operations are logged upon
Alfranio> commit or rollback thus generating invalid sequence of operations.
Alfranio> For instance, assume the following statements and sessions (connections):

Alfranio> con0: CREATE TABLE test (a int);
Alfranio> con0: INSERT INTO test VALUES(1);

Alfranio> con1: START TRANSACTION;
Alfranio> con2: START TRANSACTION;
Alfranio> con1: UPDATE test SET a = 10;
Alfranio> con2: DELETE FROM test;
Alfranio> con2: COMMIT;
Alfranio> con1: COMMIT;

<cut>

The above assumption wasn't the case for MySQL 5.0-GA (but was changed
recently without any good reason).

MySQL used to always log changes to non-transactional tables at once
and only buffer transactional changes.  This is something that MySQL
users have known and taken into account when writing their
applications.

Now things seams to work that everything is non-transactional tables
are logged at once until we change a non-transactional table in which
case everything is logged at commit.

The other change that was recently introduced, probably at the same
time, is that in case of ROLLBACK we send the whole statement to the
binary log, but with a ROLLBACK at end.  This will crash all setups
where you have transactional tables on the master and
non-transactional tables on the slave.

The changed behavior in MySQL 5.0-GA have caused some concerns among
MySQL users as it has caused their data to be inconsistent on their
slaves.  I got several requests at the MySQL users conference what
could be done to get back the old behavior.

It's clear that one can't mix non-transactional and transactional able
safely in any imaginable way;  Some are safe and some are not.

However the current way to do it will cause more problems as any
'random' concurrent usage of the non transactional tables will show up
with wrong data one the slave.

With random I mean that depending on in which order tables are used,
we get different results on the slave.

Example:

CREATE TABLE t1 (a int) engine=innodb;
CREATE TABLE n1 (a int) engine=myisam;
CREATE TABLE n2 (a int) engine=myisam;

T1: BEGIN;
T1: INSERT into t1 values (1);
T1: INSERT into n1 values (1);
T2: INSERT INTO n2 select * from n1;
T1: COMMIT;

In this case you will get wrong data on the slave for n2 as the insert
of 1 into n1 is not logged until the commit.

Bascily all INSERT SELECT, multi table updates and multi table delets
may now produce wrong results on the slave.

One should NEVER change behavior in a GA release (especially behavior
that customers are relying on), so I suggest strongly that you should
change back replication how it worked in earlier MySQL 5.0 releases.

The new behavior is more confusing and lead to more errors than the
original way.  I don't say that the original way was perfect, but at
least it worked in a predictable way that people could take care of.

Regards,
Monty