1call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
2CREATE TABLE t1m (m INT, n INT) ENGINE=MYISAM;
3CREATE TABLE t1b (b INT, c INT) ENGINE=BLACKHOLE;
4CREATE TABLE t1n (e INT, f INT) ENGINE=NDB;
5RESET MASTER;
6SET SESSION BINLOG_FORMAT=STATEMENT;
7INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
8INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
9UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
10The last event before the COMMIT is use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
11*** Please look in binlog_multi_engine.test if you have a diff here ****
12START TRANSACTION;
13INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
14UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
15Warnings:
16Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them.
17UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
18COMMIT;
19TRUNCATE t1m;
20TRUNCATE t1b;
21TRUNCATE t1n;
22include/show_binlog_events.inc
23Log_name	Pos	Event_type	Server_id	End_log_pos	Info
24mysqld-bin.000001	#	Query	#	#	BEGIN
25mysqld-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
26mysqld-bin.000001	#	Query	#	#	COMMIT
27mysqld-bin.000001	#	Query	#	#	BEGIN
28mysqld-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
29mysqld-bin.000001	#	Query	#	#	COMMIT
30mysqld-bin.000001	#	Query	#	#	BEGIN
31mysqld-bin.000001	#	Query	#	#	use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
32mysqld-bin.000001	#	Query	#	#	COMMIT
33mysqld-bin.000001	#	Query	#	#	BEGIN
34mysqld-bin.000001	#	Query	#	#	use `test`; UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c
35mysqld-bin.000001	#	Query	#	#	COMMIT
36mysqld-bin.000001	#	Query	#	#	BEGIN
37mysqld-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2)
38mysqld-bin.000001	#	Query	#	#	use `test`; UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f
39mysqld-bin.000001	#	Query	#	#	COMMIT
40mysqld-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1m
41mysqld-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1b
42mysqld-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1n
43RESET MASTER;
44SET SESSION BINLOG_FORMAT=MIXED;
45INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
46INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
47The last event before the COMMIT is use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
48INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
49UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
50UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
51ERROR HY000: Cannot execute statement: impossible to write to binary log since more than one engine is involved and at least one engine is self-logging.
52TRUNCATE t1m;
53TRUNCATE t1b;
54TRUNCATE t1n;
55include/show_binlog_events.inc
56Log_name	Pos	Event_type	Server_id	End_log_pos	Info
57mysqld-bin.000001	#	Query	#	#	BEGIN
58mysqld-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
59mysqld-bin.000001	#	Query	#	#	COMMIT
60mysqld-bin.000001	#	Query	#	#	BEGIN
61mysqld-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
62mysqld-bin.000001	#	Query	#	#	COMMIT
63mysqld-bin.000001	#	Query	#	#	BEGIN
64mysqld-bin.000001	#	Table_map	#	#	table_id: # (test.t1n)
65mysqld-bin.000001	#	Table_map	#	#	table_id: # (mysql.ndb_apply_status)
66mysqld-bin.000001	#	Write_rows	#	#	table_id: #
67mysqld-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
68mysqld-bin.000001	#	Query	#	#	COMMIT
69mysqld-bin.000001	#	Query	#	#	BEGIN
70mysqld-bin.000001	#	Query	#	#	use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
71mysqld-bin.000001	#	Query	#	#	COMMIT
72mysqld-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1m
73mysqld-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1b
74mysqld-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1n
75RESET MASTER;
76SET SESSION BINLOG_FORMAT=ROW;
77INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
78INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
79INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
80UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
81ERROR HY000: Cannot execute statement: impossible to write to binary log since more than one engine is involved and at least one engine is self-logging.
82UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
83ERROR HY000: Cannot execute statement: impossible to write to binary log since more than one engine is involved and at least one engine is self-logging.
84include/show_binlog_events.inc
85Log_name	Pos	Event_type	Server_id	End_log_pos	Info
86mysqld-bin.000001	#	Query	#	#	BEGIN
87mysqld-bin.000001	#	Table_map	#	#	table_id: # (test.t1m)
88mysqld-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
89mysqld-bin.000001	#	Query	#	#	COMMIT
90mysqld-bin.000001	#	Query	#	#	BEGIN
91mysqld-bin.000001	#	Table_map	#	#	table_id: # (test.t1b)
92mysqld-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
93mysqld-bin.000001	#	Query	#	#	COMMIT
94mysqld-bin.000001	#	Query	#	#	BEGIN
95mysqld-bin.000001	#	Table_map	#	#	table_id: # (test.t1n)
96mysqld-bin.000001	#	Table_map	#	#	table_id: # (mysql.ndb_apply_status)
97mysqld-bin.000001	#	Write_rows	#	#	table_id: #
98mysqld-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
99mysqld-bin.000001	#	Query	#	#	COMMIT
100RESET MASTER;
101DROP TABLE t1m, t1b, t1n;
102