1
CREATE TABLE t1m (m INT, n INT) ENGINE=MYISAM;
2
CREATE TABLE t1b (b INT, c INT) ENGINE=BLACKHOLE;
3
CREATE TABLE t1n (e INT, f INT) ENGINE=NDB;
5
SET SESSION BINLOG_FORMAT=STATEMENT;
6
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
7
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
8
UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
9
*** Please look in binlog_multi_engine.test if you have a diff here ****
11
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
12
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
13
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
18
show binlog events from <binlog_start>;
19
Log_name Pos Event_type Server_id End_log_pos Info
20
master-bin.000001 # Query # # use `test`; BEGIN
21
master-bin.000001 # Query # # use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
22
master-bin.000001 # Query # # use `test`; COMMIT
23
master-bin.000001 # Query # # use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
24
master-bin.000001 # Query # # use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
25
master-bin.000001 # Query # # use `test`; BEGIN
26
master-bin.000001 # Query # # use `test`; INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2)
27
master-bin.000001 # Query # # use `test`; UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f
28
master-bin.000001 # Query # # use `test`; UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c
29
master-bin.000001 # Query # # use `test`; COMMIT
30
master-bin.000001 # Query # # BEGIN
31
master-bin.000001 # Table_map # # table_id: # (test.t1n)
32
master-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
33
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
34
master-bin.000001 # Query # # COMMIT
35
master-bin.000001 # Query # # use `test`; TRUNCATE t1m
36
master-bin.000001 # Query # # use `test`; TRUNCATE t1b
37
master-bin.000001 # Query # # use `test`; TRUNCATE t1n
39
SET SESSION BINLOG_FORMAT=MIXED;
40
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
41
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
42
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
43
UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
44
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
45
ERROR HY000: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging
46
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
47
ERROR HY000: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging
51
show binlog events from <binlog_start>;
52
Log_name Pos Event_type Server_id End_log_pos Info
53
master-bin.000001 # Query # # use `test`; BEGIN
54
master-bin.000001 # Query # # use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
55
master-bin.000001 # Query # # use `test`; COMMIT
56
master-bin.000001 # Query # # use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
57
master-bin.000001 # Query # # BEGIN
58
master-bin.000001 # Table_map # # table_id: # (test.t1n)
59
master-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
60
master-bin.000001 # Write_rows # # table_id: #
61
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
62
master-bin.000001 # Query # # COMMIT
63
master-bin.000001 # Query # # use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
64
master-bin.000001 # Query # # use `test`; TRUNCATE t1m
65
master-bin.000001 # Query # # use `test`; TRUNCATE t1b
66
master-bin.000001 # Query # # use `test`; TRUNCATE t1n
68
SET SESSION BINLOG_FORMAT=ROW;
69
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
70
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
71
ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
72
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
73
UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
74
ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
75
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
76
ERROR HY000: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging
77
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
78
ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
79
show binlog events from <binlog_start>;
80
Log_name Pos Event_type Server_id End_log_pos Info
81
master-bin.000001 # Query # # use `test`; BEGIN
82
master-bin.000001 # Table_map # # table_id: # (test.t1m)
83
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
84
master-bin.000001 # Query # # use `test`; COMMIT
85
master-bin.000001 # Query # # BEGIN
86
master-bin.000001 # Table_map # # table_id: # (test.t1n)
87
master-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
88
master-bin.000001 # Write_rows # # table_id: #
89
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
90
master-bin.000001 # Query # # COMMIT
92
DROP TABLE t1m, t1b, t1n;