MySQL Bugs: #52984: non-binlogged unsafe statement causes switch to row if binlog

Bug #52984	non-binlogged unsafe statement causes switch to row if binlog_format=mixed
Submitted:	20 Apr 2010 13:02	Modified:	20 Apr 2010 13:31
Reporter:	Sven Sandberg	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1+, mysql-trunk	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	binlog_format, mixed, temporary tables

Description:
If binlog_format = MIXED and a statement is logged in row format while there are temporary tables open, then subsequent statements are logged in row format too. This is good because when statements are logged in row format, updates to temporary tables are not logged.

However: when there are open temporary tables, we make subsequent statements log in row format even when executing a statement that does not update the database and does not write to the binlog. It would be better if we did not force a switch to row format after unlogged statements.

How to repeat:
---- begin test case ----

--source include/have_binlog_format_mixed.inc
--source include/master-slave.inc

create table t1 (a int);
create temporary table t2 (a int);
insert into t1 values (1);
select version();
insert into t1 values (1);
show binlog events;

---- begin test ouput ----
create table t1 (a int);
create temporary table t2 (a int);
insert into t1 values (1);
select version();
version()
5.1.47-debug-log
insert into t1 values (1);
show binlog events;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	4	Format_desc	1	106	Server ver: 5.1.47-debug-log, Binlog ver: 4
master-bin.000001	106	Query	1	192	use `test`; create table t1 (a int)
master-bin.000001	192	Query	1	288	use `test`; create temporary table t2 (a int)
master-bin.000001	288	Query	1	376	use `test`; insert into t1 values (1)
master-bin.000001	376	Query	1	444	BEGIN
master-bin.000001	444	Table_map	1	485	table_id: 23 (test.t1)
master-bin.000001	485	Write_rows	1	519	table_id: 23 flags: STMT_END_F
master-bin.000001	519	Query	1	588	COMMIT

Thank you for the problem report. Verified just as described, also - with recent mysql-trunk tree:

openxs@ubuntu:/home2/openxs/dbs/trunk/mysql-test$ ./mtr --mysqld=--binlog-format=MIXED bug52984
Logging: ./mtr  --mysqld=--binlog-format=MIXED bug52984
100420 16:28:13 [Note] Buffered information: Performance schema disabled (reason: start parameters).

100420 16:28:13 [Note] Plugin 'FEDERATED' is disabled.
100420 16:28:13 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.5.5
Using binlog format 'MIXED'
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /home2/openxs/dbs/trunk/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/home2/openxs/dbs/trunk/mysql-test/var'...
Installing system database...
Using server port 45576
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

main.bug52984                            [ fail ]
        Test ended at 2010-04-20 16:28:16

CURRENT_TEST: main.bug52984
--- /home2/openxs/dbs/trunk/mysql-test/r/bug52984.result	2010-04-20 16:24:10.000000000 +0300
+++ /home2/openxs/dbs/trunk/mysql-test/r/bug52984.reject	2010-04-20 16:28:16.000000000 +0300
@@ -0,0 +1,25 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+create table t1 (a int);
+create temporary table t2 (a int);
+insert into t1 values (1);
+select version();
+version()
+5.5.5-m3-debug-log
+insert into t1 values (1);
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	4	Format_desc	1	107	Server ver: 5.5.5-m3-debug-log, Binlog ver: 4
+master-bin.000001	107	Query	1	193	use `test`; create table t1 (a int)
+master-bin.000001	193	Query	1	289	use `test`; create temporary table t2 (a int)
+master-bin.000001	289	Query	1	357	BEGIN
+master-bin.000001	357	Query	1	445	use `test`; insert into t1 values (1)
+master-bin.000001	445	Query	1	514	COMMIT
+master-bin.000001	514	Query	1	582	BEGIN
+master-bin.000001	582	Table_map	1	623	table_id: 41 (test.t1)
+master-bin.000001	623	Write_rows	1	657	table_id: 41 flags: STMT_END_F
+master-bin.000001	657	Query	1	726	COMMIT