Michael Widenius wrote:
>>>>>> "Alfranio" == Alfranio Correia <Alfranio.Correia@stripped>
> 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
> 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
> Alfranio> current code. In a nutshell, non-transactional operations are logged
> Alfranio> commit or rollback thus generating invalid sequence of operations.
> Alfranio> For instance, assume the following statements and sessions
> 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;
> The above assumption wasn't the case for MySQL 5.0-GA (but was changed
> recently without any good reason).
Not so. There has been a change in the behavior regarding non-transactional
changes that were *first* in the transaction, but this behavior was already
present for non-transactional changes that were inside a transaction.
> 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
Non-transactional changes were written to the binary log only when they were
first in the transaction. Also, the notion of when a statement was transactional
or not was decided on very loose grounds.
> 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.
I assume that you mean "transactional" instead of "non-transactional" in the
> 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.
I assume that you means "the whole transaction" above. I don't know if you
consider that recent, but the code for this is in 4.1.
> 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.
As mentioned, this behavior was in 4.1, and I don't know what inconsistencies
you are referring to, so it is hard for me to answer that.
I don't question that inconsistencies are there, but basically, statement based
replication cannot be made to behave in a consistent manner when mixing
non-transactional and transactional tables except in some controlled and limited
circumstances. This is what we are striving to change.
> 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.
As I said, this behavior was already in 4.1, and we want to change that so that
we are not dependent on the order and can replicate the data correctly to the
slave regardless of how transactional and non-transactional changes are made.
> 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.
... and in the following scenario, you will have inconsistency on the slave
because n2 is logged before the transaction:
T1: INSERT into t1 values (1);
T1: INSERT INTO n2 select * from t1;
> Bascily all INSERT SELECT, multi table updates and multi table delets
> may now produce wrong results on the slave.
Correction: will since 4.1 produce the wrong results on the slave, yes.
> 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.
You mean pre-4.1, I assume.
> 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.
With the risk of repeating myself too many times; this is not new behavior: it's
been there since at least 4.1, and we are striving to fix it.
Senior Software Engineer
Database Technology Group