binlog_stm_row.result 3.03 KB
Newer Older
1
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
2 3
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
4 5
set @saved_global_binlog_format = @@global.binlog_format;
set @saved_local_binlog_format = @@session.binlog_format;
6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32
SET GLOBAL BINLOG_FORMAT = STATEMENT;
SET SESSION BINLOG_FORMAT = STATEMENT;
CREATE TABLE t1 (a INT);
CREATE TABLE t2 LIKE t1;
select @@SESSION.BINLOG_FORMAT;
@@SESSION.BINLOG_FORMAT
STATEMENT
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2);
#
# Ensure that INSERT INTO .. SELECT FROM under SBR takes a read
# lock that will prevent the source table from being modified.
#
# con1
SELECT GET_LOCK('Bug#34306', 120);
GET_LOCK('Bug#34306', 120)
1
# con2
PREPARE stmt FROM "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)";
EXECUTE stmt;;
# default
INSERT INTO t2 VALUES (3);;
# con1
SELECT RELEASE_LOCK('Bug#34306');
RELEASE_LOCK('Bug#34306')
1
# con2
33
Warnings:
34
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
SELECT RELEASE_LOCK('Bug#34306');
RELEASE_LOCK('Bug#34306')
1
# default
#
# Ensure that INSERT INTO .. SELECT FROM prepared under SBR does
# not prevent the source table from being modified if under RBR.
#
# con2
SET SESSION BINLOG_FORMAT = ROW;
# con1
SELECT GET_LOCK('Bug#34306', 120);
GET_LOCK('Bug#34306', 120)
1
# con2
EXECUTE stmt;;
# default
# con1
INSERT INTO t2 VALUES (4);
SELECT RELEASE_LOCK('Bug#34306');
RELEASE_LOCK('Bug#34306')
1
# con2
# default
# Show binlog events
show binlog events from <binlog_start>;
Log_name	Pos	Event_type	Server_id	End_log_pos	Info
master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t1
master-bin.000001	#	Query	#	#	use `test`; DROP TABLE IF EXISTS t2
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t1 (a INT)
master-bin.000001	#	Query	#	#	use `test`; CREATE TABLE t2 LIKE t1
66
master-bin.000001	#	Query	#	#	BEGIN
67
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES(1)
68 69
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
70
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES(2)
71 72
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
73
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)
74 75
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
76
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (3)
77 78
master-bin.000001	#	Query	#	#	COMMIT
master-bin.000001	#	Query	#	#	BEGIN
79
master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t2 VALUES (4)
80
master-bin.000001	#	Query	#	#	COMMIT
81
master-bin.000001	#	Query	#	#	BEGIN
82 83
master-bin.000001	#	Table_map	#	#	table_id: # (test.t1)
master-bin.000001	#	Write_rows	#	#	table_id: # flags: STMT_END_F
84
master-bin.000001	#	Query	#	#	COMMIT
85 86
DROP TABLE t1;
DROP TABLE t2;
87 88
SET GLOBAL BINLOG_FORMAT = @saved_global_binlog_format;
SET SESSION BINLOG_FORMAT = @saved_local_binlog_format;