MySQL Bugs: #51564: Error ER_BINLOG_UNSAFE_NONTRANS_AFTER

Bug #51564	Error ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS is too eager after BUG#46364
Submitted:	26 Feb 2010 20:10	Modified:	26 Apr 2010 13:48
Reporter:	Alfranio Tavares Correia Junior	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.5.99-m3	OS:	Any
Assigned to:	Alfranio Tavares Correia Junior	CPU Architecture:	Any
Tags:	consistency, myisam, non-transactional changes

Description:
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.

< 5.5.2 (WL#2687), with the SBR mode, updates to non-transactional engines that happen within a transaction and after any update to a transactional engine are marked as unsafe and an warning message ER_BINLOG_UNSAFE_NONTRANS_AFTER_TRANS is printed out. This is used to alert users that a slave might go out of sync; 
and with the MIXED mode to automatically switch to rows and thus avoid any inconsistency issue.

Roughly speaking, inconsistency issues might arise because the master releases locks to non-transactional tables as soon as the statement completes the execution, while with the SBR mode, the statement is only written to the
binary log upon commit. On the other hand, with the MIXED and ROW mode, non-transactional changes written to the binary log as rows go to the binary
log as soon as the statement completes.

>= 5.5.2, BUG#46364 introduced the --binlog-direct-non-transactional-updates that causes updates to non-transactional engines to be written directly to
the binary log, rather than to the transaction cache.

So after BUG#46364, there is no need to mark updates to non-transactional engines that happen within a transaction and after any update to a transactional engine as unsafe.

However, mixed statements are still unsafe.

      1: INSERT INTO myisam_t SELECT * FROM innodb_t;

      2: INSERT INTO innodb_t SELECT * FROM myisam_t;

are classified as unsafe to ensure that in mixed mode the execution is completely safe and equivalent to the row mode. Consider the following statements and sessions (connections) to understand the reason:

      con1: INSERT INTO innodb_t VALUES (1);
      con1: INSERT INTO innodb_t VALUES (100);

      con1: BEGIN
      con2: INSERT INTO myisam_t SELECT * FROM innodb_t;
      con1: INSERT INTO innodb_t VALUES (200);
      con1: COMMIT;

The point is that the concurrent statements may be written into the binary log in a way different from the execution. For example,

      BINARY LOG:

      con2: BEGIN;
      con2: INSERT INTO myisam_t SELECT * FROM innodb_t;
      con2: COMMIT;
      con1: BEGIN
      con1: INSERT INTO innodb_t VALUES (200);
      con1: COMMIT;

      BINARY LOG:

      con1: BEGIN
      con1: INSERT INTO innodb_t VALUES (200);
      con1: COMMIT;
      con2: BEGIN;
      con2: INSERT INTO myisam_t SELECT * FROM innodb_t;
      con2: COMMIT;

Clearly, this may become a problem in the statement mode and setting the statement as unsafe will make rows to be written into the binary log in mixed mode and as such the problem will not stand.

How to repeat:
Execute the following test case with the RBR mode:

BEGIN;

UPDATE INNODB_TABLE;

UPDATE MYISAM_TABLE;  /* Generates a warning message */
 
COMMIT;

Suggested fix:
--- sql/sql_class.cc    2010-02-15 11:16:49 +0000
+++ sql/sql_class.cc    2010-02-24 21:40:57 +0000

-    if (mixed_engine ||
-        (trans_has_updated_trans_table(this) && !all_trans_engines))
-      lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_NONTRANS_AFTER_TRANS);
+    if (variables.binlog_direct_non_trans_update && mixed_engine)
+        lex->set_stmt_unsafe(LEX::BINLOG_STMT_UNSAFE_MIXED_STATEMENT);

     DBUG_PRINT("info", ("flags_all_set: 0x%llx", flags_all_set));
     DBUG_PRINT("info", ("flags_some_set: 0x%llx", flags_some_set));

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.

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.

Since there's no change to document, no changelog entry is needed.

Updated this Caution: 

          You should avoid transactions that update both 
          transactional and nontransactional tables in a replication 
          environment.

that appears at http://dev.mysql.com/doc/refman/5.1/en/replication-features-transactions.html (including 5.0, 5.4, 5.5, and 6.0 versions), so that it now reads:

          You should avoid transactions that update both 
          transactional and nontransactional tables in a replication 
          environment. You should also avoid using any statement that 
          reads from both transactional and nontransactional tables 
          and writes to any of them.

Closed.