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 Correia CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[18 Feb 2010 19:00] Matthias Leich
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.
[22 Feb 2010 5:41] Alfranio Correia
Patch

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

[22 Feb 2010 5:52] Alfranio Correia
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.
[23 Feb 2010 18:06] Bugs System
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.
[23 Feb 2010 21:18] Bugs System
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.
[26 Feb 2010 20:48] Bugs System
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.
[26 Feb 2010 20:57] Alfranio Correia
Patch for the test cases

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

[11 Mar 2010 16:49] Bugs System
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.
[15 Mar 2010 20:29] Bugs System
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.
[18 Mar 2010 13:11] Bugs System
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.
[31 Mar 2010 13:23] Bugs System
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.
[31 Mar 2010 23:17] Bugs System
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.
[1 Apr 2010 2:21] Alfranio Correia
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.
[27 Apr 2010 9:44] Bugs System
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)
[27 Apr 2010 9:47] Bugs System
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)
[27 Apr 2010 9:50] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100427094036-38frbg3famdlvjup) (version source revid:alik@sun.com-20100427093825-92wc8b22d4yg34ju) (pib:16)
[28 Apr 2010 10:23] Jon Stephens
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.
[3 May 2010 12:06] Jon Stephens
Alfranio confirms this isn't being pushed to 5.1 tree. Closed.