Debian Codegraph @

mysql-test/suite/binlog/r/binlog_stm_row.result

raw
1 CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
2 DROP TABLE IF EXISTS t1;
3 DROP TABLE IF EXISTS t2;
4 set @saved_global_binlog_format = @@global.binlog_format;
5 set @saved_local_binlog_format = @@session.binlog_format;
6 SET GLOBAL BINLOG_FORMAT = STATEMENT;
7 SET SESSION BINLOG_FORMAT = STATEMENT;
8 CREATE TABLE t1 (a INT);
9 CREATE TABLE t2 LIKE t1;
10 select @@SESSION.BINLOG_FORMAT;
11 @@SESSION.BINLOG_FORMAT
12 STATEMENT
13 INSERT INTO t1 VALUES(1);
14 INSERT INTO t2 VALUES(2);
15 connect con1,localhost,root,,;
16 connect con2,localhost,root,,;
17 #
18 # Ensure that INSERT INTO .. SELECT FROM under SBR takes a read
19 # lock that will prevent the source table from being modified.
20 #
21 connection con1;
22 SELECT GET_LOCK('Bug#34306', 120);
23 GET_LOCK('Bug#34306', 120)
24 1
25 connection con2;
26 PREPARE stmt FROM "INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)";
27 EXECUTE stmt;;
28 connection default;
29 INSERT INTO t2 VALUES (3);;
30 connection con1;
31 SELECT RELEASE_LOCK('Bug#34306');
32 RELEASE_LOCK('Bug#34306')
33 1
34 connection con2;
35 Warnings:
36 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
37 SELECT RELEASE_LOCK('Bug#34306');
38 RELEASE_LOCK('Bug#34306')
39 1
40 connection default;
41 #
42 # Ensure that INSERT INTO .. SELECT FROM prepared under SBR does
43 # not prevent the source table from being modified if under RBR.
44 #
45 connection con2;
46 SET SESSION BINLOG_FORMAT = ROW;
47 connection con1;
48 SELECT GET_LOCK('Bug#34306', 120);
49 GET_LOCK('Bug#34306', 120)
50 1
51 connection con2;
52 EXECUTE stmt;;
53 connection default;
54 connection con1;
55 INSERT INTO t2 VALUES (4);
56 SELECT RELEASE_LOCK('Bug#34306');
57 RELEASE_LOCK('Bug#34306')
58 1
59 connection con2;
60 disconnect con1;
61 disconnect con2;
62 connection default;
63 # Show binlog events
64 include/show_binlog_events.inc
65 Log_name Pos Event_type Server_id End_log_pos Info
66 master-bin.000001 # Gtid # # GTID #-#-#
67 master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t1` /* generated by server */
68 master-bin.000001 # Gtid # # GTID #-#-#
69 master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS `t2` /* generated by server */
70 master-bin.000001 # Gtid # # GTID #-#-#
71 master-bin.000001 # Query # # use `test`; CREATE TABLE t1 (a INT)
72 master-bin.000001 # Gtid # # GTID #-#-#
73 master-bin.000001 # Query # # use `test`; CREATE TABLE t2 LIKE t1
74 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
75 master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES(1)
76 master-bin.000001 # Query # # COMMIT
77 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
78 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES(2)
79 master-bin.000001 # Query # # COMMIT
80 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
81 master-bin.000001 # Query # # use `test`; INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)
82 master-bin.000001 # Query # # COMMIT
83 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
84 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (3)
85 master-bin.000001 # Query # # COMMIT
86 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
87 master-bin.000001 # Query # # use `test`; INSERT INTO t2 VALUES (4)
88 master-bin.000001 # Query # # COMMIT
89 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
90 master-bin.000001 # Annotate_rows # # INSERT INTO t1 SELECT * FROM t2 WHERE GET_LOCK('Bug#34306', 120)
91 master-bin.000001 # Table_map # # table_id: # (test.t1)
92 master-bin.000001 # Write_rows_v1 # # table_id: # flags: STMT_END_F
93 master-bin.000001 # Query # # COMMIT
94 DROP TABLE t1;
95 DROP TABLE t2;
96 SET GLOBAL BINLOG_FORMAT = @saved_global_binlog_format;
97 SET SESSION BINLOG_FORMAT = @saved_local_binlog_format;