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
mysqld-bin.000001 # Query # # BEGIN
21
mysqld-bin.000001 # Query # # use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
22
mysqld-bin.000001 # Query # # COMMIT
23
mysqld-bin.000001 # Query # # use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
24
mysqld-bin.000001 # Query # # use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
25
mysqld-bin.000001 # Query # # BEGIN
26
mysqld-bin.000001 # Query # # use `test`; INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2)
27
mysqld-bin.000001 # Query # # use `test`; UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f
28
mysqld-bin.000001 # Query # # use `test`; UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c
29
mysqld-bin.000001 # Query # # COMMIT
30
mysqld-bin.000001 # Query # # BEGIN
31
mysqld-bin.000001 # Table_map # # table_id: # (test.t1n)
32
mysqld-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
33
mysqld-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
34
mysqld-bin.000001 # Query # # COMMIT
35
mysqld-bin.000001 # Query # # use `test`; TRUNCATE t1m
36
mysqld-bin.000001 # Query # # use `test`; TRUNCATE t1b
37
mysqld-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
49
show binlog events from <binlog_start>;
50
Log_name Pos Event_type Server_id End_log_pos Info
51
mysqld-bin.000001 # Query # # BEGIN
52
mysqld-bin.000001 # Query # # use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
53
mysqld-bin.000001 # Query # # COMMIT
54
mysqld-bin.000001 # Query # # use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
55
mysqld-bin.000001 # Query # # BEGIN
56
mysqld-bin.000001 # Table_map # # table_id: # (test.t1n)
57
mysqld-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
58
mysqld-bin.000001 # Write_rows # # table_id: #
59
mysqld-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
60
mysqld-bin.000001 # Query # # COMMIT
61
mysqld-bin.000001 # Query # # use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
62
mysqld-bin.000001 # Query # # use `test`; TRUNCATE t1m
63
mysqld-bin.000001 # Query # # use `test`; TRUNCATE t1b
64
mysqld-bin.000001 # Query # # use `test`; TRUNCATE t1n
66
SET SESSION BINLOG_FORMAT=ROW;
67
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
68
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
69
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
70
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
71
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
72
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
73
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
74
show binlog events from <binlog_start>;
75
Log_name Pos Event_type Server_id End_log_pos Info
76
mysqld-bin.000001 # Query # # BEGIN
77
mysqld-bin.000001 # Table_map # # table_id: # (test.t1m)
78
mysqld-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
79
mysqld-bin.000001 # Query # # COMMIT
80
mysqld-bin.000001 # Query # # BEGIN
81
mysqld-bin.000001 # Table_map # # table_id: # (test.t1b)
82
mysqld-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
83
mysqld-bin.000001 # Query # # COMMIT
84
mysqld-bin.000001 # Query # # BEGIN
85
mysqld-bin.000001 # Table_map # # table_id: # (test.t1n)
86
mysqld-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
87
mysqld-bin.000001 # Write_rows # # table_id: #
88
mysqld-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
89
mysqld-bin.000001 # Query # # COMMIT
91
DROP TABLE t1m, t1b, t1n;