MySQL Bugs: #51291: Unfortunate effect around variable binlog_direct_non_transactional

Bug #51291	Unfortunate effect around variable binlog_direct_non_transactional_updates
Submitted:	18 Feb 2010 19:00	Modified:	3 May 2010 12:06
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.5.99-m3	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any

Description:
My script and how to execute it is in "How to repeat".

Result on 5.5.99-m3
mysql-next-mr revno: 2964 2010-02-17
------------------------------------
...
# --- MASTER ---
SELECT * FROM t1;
pk
1
# --- SLAVE ---
SELECT * FROM t1;
pk
   <= Empty result set

In case I use 
   SET SESSION binlog_direct_non_transactional_updates = ON ;
I get a nice result
...
# --- MASTER ---
SELECT * FROM t1;
pk
1
# --- SLAVE ---
SELECT * FROM t1;
pk
1

Result on 5.1.44
mysql-5.1 revno: 3315 2010-01-15
(binlog_direct_non_transactional_updates is not supported)
----------------------------------------------------------
...
# --- MASTER ---
SELECT * FROM t1;
pk
1
# --- SLAVE ---
SELECT * FROM t1;
pk
1

Result on 5.1.45
mysql-5.1-bugteam revno: 3345 2010-02-17
(binlog_direct_non_transactional_updates is OFF)
------------------------------------------------
...
# --- MASTER ---
SELECT * FROM t1;
pk
1
# --- SLAVE ---
# On slave server
SELECT * FROM t1;
pk
1

How to repeat:
./mysql-test-run.pl --mysqld=--binlog-format=row --skip-ndbcluster --record --mysqld=--innodb --mem  --no-check-testcases t/rpl_ml20.test
+
Please inspect r/rpl_ml20.result

Content of t/rpl_ml20.test:

--source include/have_innodb.inc
--source include/master-slave.inc
--source include/have_binlog_format_mixed_or_row.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings

CREATE TABLE t1 ( pk INTEGER, PRIMARY KEY (pk)) ENGINE = MyISAM;
CREATE TABLE t2 ( pk INTEGER, PRIMARY KEY (pk)) ENGINE = InnoDB;

SET SESSION binlog_direct_non_transactional_updates = OFF;
SET SESSION binlog_format = row;
SET AUTOCOMMIT = OFF;
INSERT INTO test.t2 ( pk ) VALUES ( 1 ) ;
--error ER_DUP_ENTRY
INSERT INTO test.t1 ( pk ) VALUES ( 1 ) , ( 1 );
COMMIT;

--echo # --- MASTER ---
SELECT * FROM t1;
--sync_slave_with_master
--echo # --- SLAVE ---
--connection slave
SELECT * FROM t1;

--connection master
DROP TABLE t1;
DROP TABLE t2;

Suggested fix:
I am not going to criticise
- the introduction of binlog_direct_non_transactional_updates
  in general
or
- the results I got in my tests.

But there is an IMHO not acceptable change of default behavior.

A customer using a release <= 5.1.45 gets a result where
the content of table t1 for master and slave is equal.
In case he starts to use 5.5.99-m3 he will get a different
annoying result. He can avoid this by setting GLOBAL
binlog_direct_non_transactional_updates = ON.
But this requires that he is aware of the problem and
of course he has to do an additional action.

Thinkable ways to avoid this:
1. Modify the server code so that we get the old nice
   result even if binlog_direct_non_transactional_updates
   is switched off.
or
2. Make binlog_direct_non_transactional_updates = ON
   the server startup default.

Patch

Attachment: patch.tar.gz (application/gzip, text), 95.85 KiB.

The new option binlog_direct_non_transactional_updates does not mimic the behavior
  in 5.1 when set to FALSE. This happens due to the following reasons:

  . non-transactional changes executed before all transactional changes are stored
  in the non-trx-cache in order to avoid tracking the the trx-cache and figuring out
  when it might be flushed;

   M --> B TableMapN WriteN C B TableMapT WriteT WriTeN C

  . in mixed and row modes, the "binlog rollback" was not considering the fact that
  there might be non-transactional changes in the trx-cache and was truncating the
  cache.

  . in mixed and row modes, the "binlog rollback" was not considering the fact that
  there might be non-transactional changes in a failed statement and was truncating
  the cache.

  To fix the bug, we addressed the three issues presented.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101241

2975 Alfranio Correia	2010-02-23
      BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates
      
      Post-fix for BUG#51291.
      
      When the binlog_direct_non_transactional_updates is OFF, there is no need to generate
      unsafe messages due to changes on non-transactional tables in mixed statements or
      that happen after changes on transactional tables within a transaction's context.
      
      In other words, we ignore the unsafe state BINLOG_STMT_UNSAFE_NONTRANS_AFTER_TRANS.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/101257

2976 Alfranio Correia	2010-02-23
      BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates
      
      Post-fix for BUG#51291.
      
      In this patch, we differentiate unsafe statements that access non-transactional tables
      as follows: 
      
      (BINLOG_STMT_UNSAFE_NONTRANS_AFTER_TRANS) - statements where non-transactional reads or 
      writes occur after transactional reads or writes inside a transaction.
      
      (BINLOG_STMT_UNSAFE_MIXED_STATEMENT) - statements that read from both transactional and 
      non-transactional tables and write on one of them.
      
      This is done to easy the assessment of the binlog_direct_non_transactional_updates.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/101727

2974 Alfranio Correia 2010-02-26
BUG#51291, BUG#51564

---
BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates

The new option binlog_direct_non_transactional_updates does not mimic the behavior
in 5.1 when set to OFF. This happens due to the following reasons:

. non-transactional changes executed before all transactional changes are stored
in the non-trx-cache in order to avoid tracking the the trx-cache and figuring out
when it might be flushed. However, this could lead to the following scenario:

EXECUTION:

BEGIN; MIXED-STATEMENT; COMMIT;

BINLOG ENTRIES:

STMT-CACHE:
-----------

BEGIN;

TableMap on N;

Write to N;

COMMIT;

TRX-CACHE:
----------

BEGIN;

TableMap on T;

Write to T;

Write to N;

COMMIT;

This happens because changes to non-transactional engines go to the stmt-cache
when there is no changes to transactional engines. However as soon as the first
change to the transactional engine gets into the trx-cache, additional changes
to transactional engines go to the trx-cache. Either the non-transactional changes
go to the stmt-cache or to the trx-cache but using both caches may generate
inconsistencies as the TableMap on N is not in the trx-cache. So as an unfortunate
consequence, the slave simply ignores changes whose TableMap do no exist.

Recall that TableMaps have a life-cycle in the context of a transaction and as
such do not go beyond transaction's boundaries.

. in mixed and row modes, the "binlog rollback" was not considering the fact that
there might be non-transactional changes in the trx-cache and was truncating the
cache.

. in mixed and row modes, the "binlog rollback" was not considering the fact that
there might be non-transactional changes in a failed statement and was truncating
the cache.

. there is no need to generate warning messages for changes to non-transactional
engines in mixed statements or that happen after changes to transactional engines
within a transaction's context.

---
BUG#51564 Error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS is too eager after BUG#46364

Error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS is too eager after BUG#46364 because
warning messages are being printed out for statements that only update
non-transactional engines with the SBR mode.

To fix the problem, we only print warning messages when mixed statements are
executed with the SBR mode and create a new error message
BINLOG_STMT_UNSAFE_MIXED_STATEMENT for this case.

Patch for the test cases

Attachment: test-cases.diff.tar.gz (application/gzip, text), 64.97 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/103032

2975 Alfranio Correia 2010-03-11
BUG#51291, BUG#51564

---
BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates

The new option binlog_direct_non_transactional_updates does not mimic the behavior
in 5.1 when set to OFF. This happens due to the following reasons:

(1) non-transactional changes executed before all transactional changes are stored
in the non-trx-cache in order to avoid tracking the the trx-cache and figuring out
when it might be flushed. However, this could lead to the following scenario:

EXECUTION:

BEGIN; MIXED-STATEMENT; COMMIT;

BINLOG ENTRIES:

STMT-CACHE:
-----------

BEGIN;

TableMap on N;

Write to N;

COMMIT;

TRX-CACHE:
----------

BEGIN;

TableMap on T;

Write to T;

Write to N;

COMMIT;

This happens because changes to non-transactional engines go to the stmt-cache
when there is no changes to transactional engines. However as soon as the first
change to the transactional engine gets into the trx-cache, additional changes
to transactional engines go to the trx-cache. Either the non-transactional changes
should go to the stmt-cache or to the trx-cache but using both caches may generate
inconsistencies as the TableMap on N is not in the trx-cache. So as an unfortunate
consequence, the slave simply ignores changes whose TableMap do no exist.

Recall that TableMaps have a life-cycle in the context of a transaction and as
such do not go beyond transaction's boundaries.

(2) in mixed and row modes, the "binlog rollback" was not considering the fact that
there might be non-transactional changes in the trx-cache and was truncating the
cache.

(3) in mixed and row modes, the "binlog rollback" was not considering the fact that
there might be non-transactional changes in a failed statement and was truncating
the cache.

To fix the problems described above and to facilitate the use of the option,
we have decided that it will only affect the statement mode as in 5.1.
The mixed and row modes shall be behave after the WL#2687. The statment mode
shall behave as in 5.1 and, in this case, we use the stmt-cache to write
n-statments ahead of the transaction.

---
BUG#51564 Error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS is too eager after BUG#46364

Error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS is too eager after BUG#46364 because
warning messages are being printed out for statements that only update
non-transactional engines with the SBR mode.

To fix the problem, we only print warning messages when mixed statements are
executed with the SBR mode and create a new error message
ER_BINLOG_UNSAFE_MIXED_STATEMENT for this case.
@ mysql-test/extra/rpl_tests/rpl_mixing_engines.test
Updated the test case to avoid checking inconsistencies between the master and slave
when session.binlog_direct_non_transactional_updates is ON and the format is statement.

In this scenario, they will diverge because a counter (within a triger) is incremented
and associated to the issued statement. However, an n-statement is logged ahead of
the transaction and thus is not executed by the same order in the slave and thus gets
a different value from the counter.
@ mysql-test/suite/binlog/r/binlog_multi_engine.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT
and removed the eager error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS.
@ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT
and removed the eager error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS.
@ mysql-test/suite/ndb/r/ndb_binlog_format.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT
and removed the eager error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS.
@ mysql-test/suite/rpl/r/rpl_concurrency_error.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT
and removed the eager error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS.
@ mysql-test/suite/rpl/r/rpl_mixed_mixing_engines.result
Removed the effects of the error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS. In this case,
there is no need to switch from statement to rows as the plain statement is logged
ahead of the transaction.

Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT too.
@ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT
and removed the eager error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS.
@ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT
and removed the eager error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS.
@ mysql-test/suite/rpl/r/rpl_stm_stop_middle_group.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT
and removed the eager error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS.
@ sql/log.cc
Introduced a flag at_least_one_stmt_committed that identifies if any
statement was executed and its effects were written to the trx-cache.

So when this flag is false, and we are in statement mode and the
binlog_direct_non_trans_update, we can write to the
stmt-cache if necessary (i.e an n-statement).
@ sql/share/errmsg-utf8.txt
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_class.cc
Avoided printing ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS when
binlog_direct_non_transactional_updates is ON.

Started printing ER_BINLOG_UNSAFE_MIXED_STATEMENT, when there
is a mixed-statement.
@ sql/sql_lex.cc
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_lex.h
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/103311

2975 Alfranio Correia 2010-03-15
BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates

The new option binlog_direct_non_transactional_updates did not mimic the behavior
in 5.1 when set to OFF:

(1) non-transactional changes executed before all transactional changes were stored
in the non-trx-cache (i.e. stmt-cache) in order to avoid tracking the the trx-cache
and figuring out when it might be flushed. However, this was leading, sometimes, to
the following scenario:

EXECUTION:

BEGIN; MIXED-STATEMENT; COMMIT;

BINLOG ENTRIES:

STMT-CACHE:
-----------

BEGIN;

TableMap on N;

Write to N;

COMMIT;

TRX-CACHE:
----------

BEGIN;

TableMap on T;

Write to T;

Write to N;

COMMIT;

This happened because changes to non-transactional engines went to the
stmt-cache when there was no change to any transactional engine. However as
soon as the first change to a transactional engine went into the trx-cache,
additional changes to non-transactional engines went to the trx-cache. Either
the non-transactional changes should go to the stmt-cache or to the trx-cache
but using both caches might generate inconsistencies as described above. So as
an unfortunate consequence, the slave was ignoring such changes.

(2) in mixed and row modes, the "binlog_rollback()" was not considering the
fact that there might be non-transactional changes in the trx-cache and was
truncating the cache.

To fix these problems, we have decided to do what follows:

(1) The binlog_direct_non_transactional_updates will only affect the STMT mode
and we will do the best to keep backwards compatibility when such mode is in
use.

(2) In MIXED and ROW modes, the behavior will be based on the WL#2687.

(3) We will differentiate the following types of unsafeness: B T N C and
B TN T C by introducing a new error message ER_BINLOG_UNSAFE_MIXED_STATEMENT.

B T N C triggers ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS

B TN T C triggers ER_BINLOG_UNSAFE_MIXED_STATEMENT

In particular, the current code along with the changes in this patch do not
preserve backwards compatibility with Betony due to the following reasons:

(1) In STMT mode, Warning messages are being printed out whereas previous
versions do not do this. (Change introduced in WL#2687)

(2) In MIXED and ROW modes, non-transactional changes are always flushed
upon finishing the statement's execution. (Change introduced in WL#2687)

(3) The ER_BINLOG_UNSAFE_MIXED_STATEMENT is being printed out. (Change
introduced in this patch)

(4) In STMT mode, a mixed-statement (i.e. NT) that fails is always cached
and flushed upon committing the transaction. In Betony, a mixed-statement
that fails and happens before any transactional change are logged ahead
of the transaction upon finishing the statement's execution.
@ mysql-test/extra/rpl_tests/rpl_mixing_engines.test
Updated the test case to avoid checking inconsistencies between the master and slave
when session.binlog_direct_non_transactional_updates is ON and the format is statement.

In this scenario, they will diverge because a counter (within a triger) is incremented
and associated to the issued statement. However, an n-statement is logged ahead of
the transaction and thus is not executed by the same order in the slave and thus gets
a different value from the counter.
@ mysql-test/suite/binlog/r/binlog_multi_engine.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/ndb/r/ndb_binlog_format.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_concurrency_error.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_stop_middle_group.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/log.cc
Checked if either a trx-cache or a non-trx-cache should be used.

If bin_log_direct_non_trans_update is active or the format is either
MIXED or ROW, the cache to be used depends on the flag is_transactional.

When the format is STMT, the non-trx-cache should be used if the statement
is non-transactional and the trx-cache is empty, i.e. if any transactional
statement has not committed yet. Otherwise, the trx-cache should be used.
@ sql/share/errmsg-utf8.txt
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_class.cc
Started printing ER_BINLOG_UNSAFE_MIXED_STATEMENT, when there
is a mixed-statement.

Organized the names of the variables and added comments.
@ sql/sql_lex.cc
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_lex.h
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/103695

2975 Alfranio Correia 2010-03-18
BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates

BUG#46364 introduced the flag binlog_direct_non_transactional_updates which
would make N-changes to be written to the binary log upon committing the
statement when "ON". On the other hand, when "OFF" the option was supposed
to mimic the behavior in 5.1. However, the implementation was not mimicking
the behavior correctly and the following bugs popped up:

Case #1: N-changes executed within a transaction would go into
the S-cache. When later in the same transaction a
T-change occurs, N-changes following it were written
to the T-cache instead of the S-cache. In some cases,
this raises problems. For example, a
Table_map_log_event being written initially into the
S-cache, together with the initial N-changes, would be
absent from the T-cache. This would log N-changes
orphaned from a Table_map_log_event (thence discarded
at the slave). (MIXED and ROW)

Case #2: When rolling back a transaction, the N-changes that
might be in the T-cache were disregarded and
truncated along with the T-changes. (MIXED and ROW)

Case #3: When a MIXED statement (TN) is ahead of any other
T-changes in the transaction and it fails, it is kept
in the T-cache until the transaction ends. This is
not the case in 5.1 or Betony (5.5.2). In these, the
failed TN statement would be written to the binlog at
the same instant it had failed and not deferred until
transaction end. (SBR)

To fix these problems, we have decided to do what follows:

For Case #1 and #2, we circumvent them:

1. by not letting binlog_direct_non_transactional_updates
affect MIXED and RBR. These modes will keep the behavior
provided by WL#2687. Although this will make Celosia to
behave differently from 5.1, an execution will be always
safe under such modes in the sense that slaves will never
go out sync. In 5.1, using either MIXED or ROW while
mixing N-statements and T-statements was not safe.

For Case #3, we don't actually fix it. We:

1. keep it and make all MIXED statements whether they end
up failing or not or whether they are up front in the
transaction or after some transactional change to always
be stored in the T-cache. This means that it is written
to the binary log on transaction commit/rollback only.

2. We make the warning message even more specific about the
MIXED statement and SBR.
@ mysql-test/extra/rpl_tests/rpl_mixing_engines.test
Updated the test case to avoid checking inconsistencies between the master and slave
when session.binlog_direct_non_transactional_updates is ON and the format is statement.

In this scenario, they will diverge because a counter (within a triger) is incremented
and associated to the issued statement. However, an n-statement is logged ahead of
the transaction and thus is not executed by the same order in the slave and thus gets
a different value from the counter.
@ mysql-test/suite/binlog/r/binlog_multi_engine.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/ndb/r/ndb_binlog_format.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_concurrency_error.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_stop_middle_group.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/log.cc
Checked if either a trx-cache or a non-trx-cache should be used.

If bin_log_direct_non_trans_update is active or the format is either
MIXED or ROW, the cache to be used depends on the flag is_transactional.

When the format is STMT, the non-trx-cache should be used if the statement
is non-transactional and the trx-cache is empty, i.e. if any transactional
statement has not committed yet. Otherwise, the trx-cache should be used.
@ sql/share/errmsg-utf8.txt
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_class.cc
Started printing ER_BINLOG_UNSAFE_MIXED_STATEMENT, when there
is a mixed-statement.

Organized the names of the variables and added comments.
@ sql/sql_lex.cc
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_lex.h
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

http://lists.mysql.com/commits/104716

2975 Alfranio Correia 2010-03-31
BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates

BUG#46364 introduced the flag binlog_direct_non_transactional_updates which
would make N-changes to be written to the binary log upon committing the
statement when "ON". On the other hand, when "OFF" the option was supposed
to mimic the behavior in 5.1. However, the implementation was not mimicking
the behavior correctly and the following bugs popped up:

Case #1: N-changes executed within a transaction would go into
the S-cache. When later in the same transaction a
T-change occurs, N-changes following it were written
to the T-cache instead of the S-cache. In some cases,
this raises problems. For example, a
Table_map_log_event being written initially into the
S-cache, together with the initial N-changes, would be
absent from the T-cache. This would log N-changes
orphaned from a Table_map_log_event (thence discarded
at the slave). (MIXED and ROW)

Case #2: When rolling back a transaction, the N-changes that
might be in the T-cache were disregarded and
truncated along with the T-changes. (MIXED and ROW)

Case #3: When a MIXED statement (TN) is ahead of any other
T-changes in the transaction and it fails, it is kept
in the T-cache until the transaction ends. This is
not the case in 5.1 or Betony (5.5.2). In these, the
failed TN statement would be written to the binlog at
the same instant it had failed and not deferred until
transaction end. (SBR)

To fix these problems, we have decided to do what follows:

For Case #1 and #2, we circumvent them:

1. by not letting binlog_direct_non_transactional_updates
affect MIXED and RBR. These modes will keep the behavior
provided by WL#2687. Although this will make Celosia to
behave differently from 5.1, an execution will be always
safe under such modes in the sense that slaves will never
go out sync. In 5.1, using either MIXED or ROW while
mixing N-statements and T-statements was not safe.

For Case #3, we don't actually fix it. We:

1. keep it and make all MIXED statements whether they end
up failing or not or whether they are up front in the
transaction or after some transactional change to always
be stored in the T-cache. This means that it is written
to the binary log on transaction commit/rollback only.

2. We make the warning message even more specific about the
MIXED statement and SBR.
@ mysql-test/extra/rpl_tests/rpl_mixing_engines.test
Updated the test case to avoid checking inconsistencies between the master and slave
when session.binlog_direct_non_transactional_updates is ON and the format is statement.

In this scenario, they will diverge because a counter (within a triger) is incremented
and associated to the issued statement. However, an n-statement is logged ahead of
the transaction and thus is not executed by the same order in the slave and thus gets
a different value from the counter.
@ mysql-test/suite/binlog/r/binlog_multi_engine.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/ndb/r/ndb_binlog_format.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_concurrency_error.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_binlog_max_cache_size.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_mixing_engines.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ mysql-test/suite/rpl/r/rpl_stm_stop_middle_group.result
Updated the test case with the new error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/log.cc
Checked if either a trx-cache or a non-trx-cache should be used.

If bin_log_direct_non_trans_update is active or the format is either
MIXED or ROW, the cache to be used depends on the flag is_transactional.

When the format is STMT, the non-trx-cache should be used if the statement
is non-transactional and the trx-cache is empty, i.e. if any transactional
statement has not committed yet. Otherwise, the trx-cache should be used.
@ sql/share/errmsg-utf8.txt
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_class.cc
Started printing ER_BINLOG_UNSAFE_MIXED_STATEMENT, when there
is a mixed-statement.

Organized the names of the variables and added comments.
@ sql/sql_lex.cc
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.
@ sql/sql_lex.h
Added the new unsafe error ER_BINLOG_UNSAFE_MIXED_STATEMENT.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/104751

3011 Alfranio Correia	2010-04-01
      BUG#51291 Unfortunate effect around variable binlog_direct_non_transactional_updates
      
      Post-merge fix.

Behavior after the patch:

The use of the option binlog_direct_non_transactional_updates affects only the STATEMENT format.

In 5.1 <= version < next-mr:

  (1) ROW/MIXED
    (1.1) are not affect by the option.
    (1.2) changes to non-transactional engines due to an N-Statement and that
          happen before every change to transactional engines are written
          outside the transaction boundaries.

        Execution:

        BEGIN;
          CHANGE t_my_isam;
          CHANGE t_innodb;
        COMMIT;

        Binary log:

        BEGIN;
          ROWS from t_my_isam;
        COMMIT;

        BEGIN;
          ROWS from t_innodb;
        COMMIT;

    (1.3) changes to non-transactional engines due to an M-Statement that
          fails and that happen before every change to transactional engines
          are written outside the transaction boundaries.

        BEGIN;
          CHANGE t_my_isam, t_innodb; /* FAILURE */
          CHANGE t_innodb;
        COMMIT;

        Binary log:
        BEGIN;
          ROWS from t_my_isam, t_innodb;
        ROLLBACK;

        BEGIN;
          ROWS from t_innodb;
        COMMIT;

    (1.4) in any other case, changes to non-transactional engines are always
          written to the binary log upon committing the transaction, if
          there is any.

    (1.5) There is no unsafe warning message.

  (2) STATEMENT
    (2.1) is affect by the option

    (2.2) If the option is OFF (i.e. default), the system behaves as in the
          ROW/MIXED formats.

    (2.3) If the option is ON, N-Statements are written to the binary log
          outside the transaction boundaries.

    (2.4) (1.2) and (1.3) are valid.

    (2.5) There is no unsafe warning message.

In next-mr <= version
  (3) ROW/MIXED
    (3.1) are not affect by the option.

    (3.2) Changes to non-transactional engines are always written to the binary
          log outside the transaction boundaries.

    (3.4) There is no need for unsafe warning messages as the execution is
          supposed to be 100% safe.

  (4) STATEMENT
    (4.1) If the option is OFF (i.e. default), the behavior will be similar to 
          the 5.1. However, (1.3) is not handled.

    (4.2) If the option is ON, N-Statements are written to the binary log 
          outside the transaction boundaries.

    (4.3) Unsafe warning messages are always printed out if a M-Statement or a 
          N-Statement that happens after a T- or M-Statement within a
          transaction.

    (4.4) The status of the option does not affect when an unsafe warning
          message is printed out.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100427094135-5s49ecp3ckson6e2) (version source revid:alik@sun.com-20100427093843-uekr85qkd7orx12t) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (version source revid:alik@sun.com-20100427093804-a2k3rrjpwu5jegu8) (merge vers: 5.5.5-m3) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)

Documented in the 5.5.5 and 6.0.14 changelogs as follows:

      Enabling binlog_direct_non_transactional_updates causes 
      nontransactional changes to be written to the binary log upon 
      committing the statement. However, even when not enabled, the 
      addition of this variable introduced a number of undesired changes 
      in behavior:
      
      1. When using ROW or MIXED logging mode: Nontransactional changes
      executed within a transaction prior to any transactional changes 
      were written to the statement cache, but those following any 
      transactional changes were written to the transactional cache 
      instead, causing these (later) nontransactional changes to be lost. 
            
      2. When using ROW or MIXED logging mode: When rolling back a 
      transaction, any nontransactional changes that might be in the 
      transaction cache were disregarded and truncated along with the  
      transactional changes.
      
      3: When using STATEMENT logging mode: A statement that combined
      transactional and nontransactional changes prior to any other
      transactional changes within the transaction, but failed, was 
      kept in the transactional cache until the transaction ended, rather 
      than being written to the binary log at the instant of failure (and 
      not deferred to the end of the transaction).
      
      
      These problems have been addressed as follows:
      
      1. The setting for binlog_direct_non_transactional_updates no 
      longer has any effect when the value of binlog_format is either 
      ROW or MIXED. This fixes the first two issues previously listed.
      
      2. When using statement-based logging with
      binlog_direct_non_transactional_updates set to ON, any statement 
      combining transactional and nontransactional changes within the 
      same transaction is now stored in the transaction cache, whether 
      it succeeds or not, and regardless of its order of execution 
      amongst any transactional statements within that transaction. This 
      means that such a statement is now written to the binary log only 
      on transaction commit or rollback.    

Set status = NM, waiting for 5.1 merge.

Alfranio confirms this isn't being pushed to 5.1 tree. Closed.