binlog_multi_engine.result 4.45 KB
Newer Older
1 2 3
CREATE TABLE t1m (m INT, n INT) ENGINE=MYISAM;
CREATE TABLE t1b (b INT, c INT) ENGINE=BLACKHOLE;
CREATE TABLE t1n (e INT, f INT) ENGINE=NDB;
4
RESET MASTER;
5 6 7 8
SET SESSION BINLOG_FORMAT=STATEMENT;
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
9 10 11
*** Please look in binlog_multi_engine.test if you have a diff here ****
START TRANSACTION;
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
12 13
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
14
COMMIT;
15 16 17
TRUNCATE t1m;
TRUNCATE t1b;
TRUNCATE t1n;
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
master-bin.000001	#	Query	#	#	use `test`; BEGIN
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2)
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c
master-bin.000001	#	Query	#	#	use `test`; COMMIT
master-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1m
master-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1b
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1n)
master-bin.000001	#	Table_map	#	#	table_id: # (mysql.ndb_apply_status)
master-bin.000001	#	Write_rows	#	#	table_id: #
master-bin.000001	#	Write_rows	#	#	table_id: #
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1n
RESET MASTER;
39 40 41 42 43 44
SET SESSION BINLOG_FORMAT=MIXED;
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
45
ERROR HY000: Binary logging not possible. Message: Statement cannot be logged to the binary log in row-based nor statement-based format
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
TRUNCATE t1m;
TRUNCATE t1b;
TRUNCATE t1n;
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2)
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2)
master-bin.000001	#	Query	#	#	use `test`; UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c
master-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1m
master-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1b
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1n)
master-bin.000001	#	Table_map	#	#	table_id: # (mysql.ndb_apply_status)
master-bin.000001	#	Write_rows	#	#	table_id: #
master-bin.000001	#	Write_rows	#	#	table_id: #
61
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
62 63
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	use `test`; TRUNCATE t1n
64 65 66 67 68 69 70 71 72 73 74 75
RESET MASTER;
SET SESSION BINLOG_FORMAT=ROW;
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
ERROR HY000: Binary logging not possible. Message: Statement cannot be logged to the binary log in row-based nor statement-based format
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
76 77 78 79 80 81 82
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1m)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
master-bin.000001	#	Query	#	#	BEGIN
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1n)
master-bin.000001	#	Table_map	#	#	table_id: # (mysql.ndb_apply_status)
master-bin.000001	#	Write_rows	#	#	table_id: #
master-bin.000001	#	Write_rows	#	#	table_id: #
83
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
84
master-bin.000001	#	Query	#	#	COMMIT
85 86
RESET MASTER;
DROP TABLE t1m, t1b, t1n;