MySQL Bugs: #45856: can't switch from binlog_format=row to mix with open tmp tbl

Bug #45856	can't switch from binlog_format=row to mix with open tmp tbl
Submitted:	30 Jun 2009 14:18	Modified:	8 Mar 2010 19:54
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1+	OS:	Any
Assigned to:	Daogang Qu	CPU Architecture:	Any
Tags:	binlog_format, mixed, temporary table, unsafe

Description:
If binlog_format=ROW, there are open temporary tables, and an unsafe statement is executed, then the statement 'SET @@session.binlog_format = MIXED' generates the error:

"Cannot switch out of the row-based binary log format when the session has open temporary tables"

However, it is safe to switch to MIXED mode because events in row format are allowed.

How to repeat:
source include/have_binlog_format_row.inc;

CREATE TABLE t1 (a VARCHAR(100));
CREATE TEMPORARY TABLE temp (a VARCHAR(100));

INSERT INTO temp VALUES (UUID());
INSERT INTO t1 VALUES ('blah');

SET SESSION binlog_format = MIXED;

Suggested fix:
Allow switching to MIXED format (but not statement) even with open temp tables.

See also BUG#45855

Thank you for the bug report.

mysql 5.1 >INSERT INTO t1 VALUES ('blah');
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >
mysql 5.1 >SET SESSION binlog_format = MIXED;
ERROR 1559 (HY000): Cannot switch out of the row-based binary log format when the session has open temporary tables
mysql 5.1 >

This is a feature request.  

It is more safe (for now) to not allow that switch of format until we
have enough tests to show that the switch is not dangerous.

Correction: this bug has nothing to do with unsafe statements. The conditions for the bug are:

 - binlog_format = ROW
 - there are open temporary tables
 - the user executes 'SET binlog_format = MIXED'

Clarification:

When binlog_format=mixed and there are open temporary tables and we log a query in row format, the *current* behavior is that we keep logging in row format until all temporary tables are dropped.

So to allow switching from binlog_format=row to binlog_format=mixed when there are open temporary tables, all we need to do is use the existing mechanism that keeps logging in row format until all temporary tables are dropped. So I don't think we need to code a new feature, just make use of an existing feature.

Please set its target version.

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/97479

3164 Dao-Gang.Qu@sun.com	2010-01-20
      Bug #45855  	row events in binlog after switch from binlog_fmt=mix to stmt with open tmp tbl
      Bug #45856  	can't switch from binlog_format=row to mix with open tmp tbl
      
      
      If binlog_format=MIXED, there are open temporary tables, an unsafe statement
      is executed, and the user issues 'SET @@session.binlog_format = STATEMENT',
      then subsequent DML statements will be written in row format despite 
      binlog_format=STATEMENT. Because the binlog format can't be reset to
      statement based by 'reset_current_stmt_binlog_row_based' function.
      
      If binlog_format=ROW, there are open temporary tables, and an unsafe statement
      is executed, then the statement 'SET @@session.binlog_format = MIXED' generates
      the error:
      "Cannot switch out of the row-based binary log format when the session has open
      temporary tables"
      However, it is safe to switch to MIXED mode because events in row format are allowed.
      
      
      To fix the above two problems, generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
      and forbid switching from MIXED or ROW to STATEMENT when there are open temp
      tables and we are logging in row format. There is no error in any other case.
     @ mysql-test/suite/rpl/r/rpl_binlog_format_switch_in_tmp_table.result
        Test result for bug#45855 and bug#45856.
     @ mysql-test/suite/rpl/t/rpl_binlog_format_switch_in_tmp_table.test
        Added test file to verify if the program will generate
        ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR error and forbid
        switching from MIXED or ROW to STATEMENT when there are
        open temp tables and we are logging in row format. There
        is no error in any other 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/98160

3164 Dao-Gang.Qu@sun.com	2010-01-26
      Bug #45855  	row events in binlog after switch from binlog_fmt=mix to stmt with open tmp tbl
      Bug #45856  	can't switch from binlog_format=row to mix with open tmp tbl
      
      
      If binlog_format=MIXED, there are open temporary tables, an unsafe statement
      is executed, and the user issues 'SET @@session.binlog_format = STATEMENT',
      then subsequent DML statements will be written in row format despite 
      binlog_format=STATEMENT. Because the binlog format can't be reset to
      statement based by 'reset_current_stmt_binlog_row_based' function.
      
      If binlog_format=ROW, there are open temporary tables, and an unsafe statement
      is executed, then the statement 'SET @@session.binlog_format = MIXED' generates
      the error:
      "Cannot switch out of the row-based binary log format when the session has open
      temporary tables"
      However, it is safe to switch to MIXED mode because events in row format are allowed.
      
      
      To fix the above two problems, generate ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR
      and forbid switching from MIXED or ROW to STATEMENT when there are open temp
      tables and we are logging in row format. There is no error in any other case.
     @ mysql-test/suite/binlog/r/binlog_format_switch_in_tmp_table.result
        Test result for bug#45855 and bug#45856.
     @ mysql-test/suite/binlog/t/binlog_format_switch_in_tmp_table.test
        Added test file to verify if the program will generate
        ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR error and forbid
        switching from MIXED or ROW to STATEMENT when there are
        open temp tables and we are logging in row format. There
        is no error in any other case.

Pushed into mysql-5.1-rpl+2.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100209123439-1wao1hywtw0o6cej) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100213083327-cee4ao3jpg33eggv) (version source revid:luis.soares@sun.com-20100209115836-3m4f1celquf37g4q) (pib:16)

Documented together with BUG#45855 in the 6.0.14 changelog -- see BUG#45855 for changelog entry.

Set status = Need Merge, waiting for merge to 5.1(+?).

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)

Also documented in the 5.5.3 changelog (see BUG#45855 for docs info).

Closed.