Edit File: binlog_stm_row.result
CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; set @saved_global_binlog_format = @@global.binlog_format; set @saved_local_binlog_format = @@session.binlog_format; 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 Warnings: 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. 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` /* generated by server */ master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */ master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT) master-bin.000001 # Query # # use `test`; CREATE TABLE t2 LIKE t1 master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES(1) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES(2) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (3) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (4) master-bin.000001 # Query # # COMMIT master-bin.000001 # Query # # BEGIN master-bin.000001 # Table_map # # table_id: # (test.t1) master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F master-bin.000001 # Query # # COMMIT DROP TABLE t1; DROP TABLE t2; SET GLOBAL BINLOG_FORMAT = @saved_global_binlog_format; SET SESSION BINLOG_FORMAT = @saved_local_binlog_format;
Back to File Manager