> Below is the list of changes that have just been committed into a local
> 5.1 repository of sven. When sven does a push these changes
> will be propagated to the main repository and, within 24 hours after the
> push, to the public repository.
> For information on how to access the public repository
> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
> ChangeSet@stripped, 2008-03-11 08:35:38+01:00, sven@riska.(none) +11 -0
> BUG#29288: myisam transactions replicated to a transactional slave leaves slave
> Problem: when replicating from non-transactional to transactional engine
> with autocommit off, no BEGIN/COMMIT is written to the binlog. When the
> slave replicates, it will start a transaction that never ends.
The fact that 5.0 replication is immune is due to changes made to 5.1
wrt BUG#22864 et al. These changes started replicating of OPTION_NOT_AUTOCOMMIT.
With your current fixes the bit will be replicated but with the
constant value ON that is de-facto deprecation of replicating it.
Earlier we discussed upgrading issue mentioned in a pasted snippet
that seemed to you as the reason to replicate the constant instead of
to assume on the slave the bit's value is ON.
+ Note: we must explicitly replicate AUTOCOMMIT=1 from master, not
+ assume AUTOCOMMIT=1 on slave. If slave assumed AUTOCOMMIT=1, it
+ would not work in upgrade scenarios with an old master (not
+ replicating explicit BEGIN/END) and a new slave. Also, when
+ replicating from a new master to an old slave, the old slave will
+ use the AUTOCOMMIT mode given by the master.
As I can see there is no such issue, and vice versa the slave would be
rather to assume to avoid this bug's scenarios (please find below more
to that) in replication from the pre-bug29288 master to the
Conversely, the assumption seems to be as the only way to
Let's consider use cases of replicating from a master to the assuming
In ordinary case such as a master replicates an autocommit query or
a multi-statement transaction (msta) that is not
interrupted/complicated with the CREATE..SELECT statement
there is no issue with either pre-bug#26395 or post-bug#26395 master.
Indeed, the former does not wrap the query but the slave's assumtion would
force to autocommit. For the msta, there are explict BEGIN/COMMIT in
the binlog and thus the bit value does not matter.
For the case of mentioned CREATE..SELECT we need to consider the bug#22864
and its scenarios.
About the reason of replicating of the bit we talked briefly on #rep
[19:36]<andrei> sven, as i understand that cset and related stuff, we might
have decided to not replicate the autocommit bit but rather
[19:37]<sven> andrei, yes, that would be possible, and indeed seems like a
more uniform way to do it.
<andrei> sven, we need to consider consequences on your fixes wrt to
the cset that introduced it.
I have made some efforts to recall why the bit started be replicated
and so far can say only with that was a decision instead of
insertion of the explict BEGIN.
From my questioning of Mats for bug#22864:
> >> sql/log_event.h@stripped, 2006-11-09 10:35:42+01:00, mats@romeo.(none) +9
> >> Replicating OPTION_NOT_AUTOCOMMIT flag.
> > Please explain why it's needed.
> That would be in the changeset comment, in that case. No reason to
> repeat text to much...
I seemed to give up that question... In time we must see
matter better, sigh...
Mats, do you have any comment, memories ..?
Now the promised new scenario.
Having a master connection with
set autocommit= off;
set up a query like
create table t1 select * from t0;
where t1 is supposed to be myisam on master innodb on slave. The query
unrolls in the following binlog sequence
create table t;
Notice no `commit' at the end.
However, the bit in OFF and that involves the pre-your-current-bug
masters, incl post-bug#26395, in a similar to the bug report problem
because events following the sequence won't commit without an explicit
COMMIT that is not generated.
So the slave has to stick with the assumption to order to avoid the
regression of this bug in the scenario with CREATE..SELECT.
The assumption is correct even in view of yet another scenario.
if tables are innodb on both sides, the sequence is
create table t;
and rows_log_events comrises a separate transaction that seemed to have
to be committed as the whole group. But, as the events are row-based
they can be committed one by one without any harm to consistency. You
current patch forcing the bit ON actually compies with that observation.
Please consider this analysis.
I am okay with not to replicate the bit in concept. We even may leave the
bit formally involved but ignored until for some better to clean up the code
(although that looks to be just a few lines of changes).
The assumption I defend is the best choice imo, and your cons- lines
seem to be incorrect.
One note following although it is not an important subject at the
> Fix: Force autocommit=1 on slave by always replicating autocommit=1 from
> the master.
> BUG#34541: mysqlbinlog prints 'set;' in stm mode after changing autocommit mode
> Problem: a typo in the code. When autocommit, foreign_key_checks,
> sql_auto_is_null, or unique_checks changes, it prints "SET", and then a
> comma-separated list of assignments. However, it does not print the
> assignment to the @@autocommit variable.
> Fix: print the @@autocommit variable.
Could you please mention the fact that DROP db which was used to
demonstrate the failure is not helpful anymore; there is no need to
explain why but as the bare minimum the mentioning would explain why there
is no drop db/table table in your tests while it is on the description.
<... the rest is ommitted >