SET BINLOG_FORMAT=MIXED; Warnings: Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release. RESET MASTER; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6); SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; UPDATE t1 SET b = 2*a WHERE a > 1; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; UPDATE t1 SET b = a * a WHERE a > 3; COMMIT; SET BINLOG_FORMAT=STATEMENT; Warnings: Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; BEGIN; UPDATE t1 SET b = 1*a WHERE a > 1; ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; UPDATE t1 SET b = 2*a WHERE a > 2; ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; UPDATE t1 SET b = 3*a WHERE a > 3; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; UPDATE t1 SET b = 4*a WHERE a > 4; COMMIT; SET BINLOG_FORMAT=MIXED; Warnings: Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; BEGIN; UPDATE t1 SET b = 1*a WHERE a > 1; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; UPDATE t1 SET b = 2*a WHERE a > 2; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; UPDATE t1 SET b = 3*a WHERE a > 3; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; UPDATE t1 SET b = 4*a WHERE a > 4; COMMIT; SET BINLOG_FORMAT='ROW'; Warnings: Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release. SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; BEGIN; UPDATE t1 SET b = 1*a WHERE a > 1; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; BEGIN; UPDATE t1 SET b = 2*a WHERE a > 2; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; BEGIN; UPDATE t1 SET b = 3*a WHERE a > 3; COMMIT; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN; UPDATE t1 SET b = 4*a WHERE a > 4; COMMIT; include/show_binlog_events.inc Log_name Pos Event_type Server_id End_log_pos Info binlog.000001 # Query # # use `test`; CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB binlog.000001 # Query # # BEGIN binlog.000001 # Query # # use `test`; INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6) binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 2*a WHERE a > 1 binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Table_map # # table_id: # (test.t1) binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3 binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4 binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Table_map # # table_id: # (test.t1) binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Table_map # # table_id: # (test.t1) binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 3*a WHERE a > 3 binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Query # # use `test`; UPDATE t1 SET b = 4*a WHERE a > 4 binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Table_map # # table_id: # (test.t1) binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Table_map # # table_id: # (test.t1) binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Table_map # # table_id: # (test.t1) binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F binlog.000001 # Xid # # COMMIT /* XID */ binlog.000001 # Query # # BEGIN binlog.000001 # Table_map # # table_id: # (test.t1) binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F binlog.000001 # Xid # # COMMIT /* XID */ DROP TABLE t1; flush status; show status like "binlog_cache_use"; Variable_name Value Binlog_cache_use 0 show status like "binlog_cache_disk_use"; Variable_name Value Binlog_cache_disk_use 0 create table t1 (a int) engine=innodb; show status like "binlog_cache_use"; Variable_name Value Binlog_cache_use 2 show status like "binlog_cache_disk_use"; Variable_name Value Binlog_cache_disk_use 1 begin; delete from t1; commit; show status like "binlog_cache_use"; Variable_name Value Binlog_cache_use 3 show status like "binlog_cache_disk_use"; Variable_name Value Binlog_cache_disk_use 1 drop table t1; End of tests