Debian Codegraph @

mysql-test/suite/rpl/r/rpl_begin_commit_rollback.result

raw
1 include/master-slave.inc
2 [connection master]
3 connection slave;
4 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
5 connection master;
6 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
7 SET @@session.binlog_direct_non_transactional_updates= FALSE;
8 DROP DATABASE IF EXISTS db1;
9 DROP DATABASE IF EXISTS db2;
10 CREATE DATABASE db1;
11 CREATE DATABASE db2;
12 use db1;
13 CREATE TABLE db1.t1 (a INT) ENGINE=InnoDB;
14 CREATE TABLE db1.t2 (s CHAR(255)) ENGINE=MyISAM;
15 connection slave;
16 include/stop_slave.inc
17 connection master;
18 CREATE PROCEDURE db1.p1 ()
19 BEGIN
20 INSERT INTO t1 VALUES (1);
21 INSERT INTO t1 VALUES (2);
22 INSERT INTO t1 VALUES (3);
23 INSERT INTO t1 VALUES (4);
24 INSERT INTO t1 VALUES (5);
25 END//
26 CREATE PROCEDURE db1.p2 ()
27 BEGIN
28 INSERT INTO t1 VALUES (6);
29 INSERT INTO t1 VALUES (7);
30 INSERT INTO t1 VALUES (8);
31 INSERT INTO t1 VALUES (9);
32 INSERT INTO t1 VALUES (10);
33 INSERT INTO t2 VALUES ('executed db1.p2()');
34 END//
35 INSERT INTO db1.t2 VALUES ('before call db1.p1()');
36 use test;
37 BEGIN;
38 CALL db1.p1();
39 COMMIT;
40 INSERT INTO db1.t2 VALUES ('after call db1.p1()');
41 SELECT * FROM db1.t1;
42 a
43 1
44 2
45 3
46 4
47 5
48 SELECT * FROM db1.t2;
49 s
50 before call db1.p1()
51 after call db1.p1()
52 connection slave;
53 start slave until master_log_file='master-bin.000001', master_log_pos=MASTER_POS;
54 include/wait_for_slave_sql_to_stop.inc
55 #
56 # If we got non-zero here, then we're suffering BUG#43263
57 #
58 SELECT 0 as 'Must be 0';
59 Must be 0
60 0
61 SELECT * from db1.t1;
62 a
63 1
64 2
65 3
66 4
67 5
68 SELECT * from db1.t2;
69 s
70 before call db1.p1()
71 connection master;
72 INSERT INTO db1.t2 VALUES ('before call db1.p2()');
73 BEGIN;
74 CALL db1.p2();
75 Warnings:
76 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
77 ROLLBACK;
78 INSERT INTO db1.t2 VALUES ('after call db1.p2()');
79 SELECT * FROM db1.t1;
80 a
81 1
82 2
83 3
84 4
85 5
86 SELECT * FROM db1.t2;
87 s
88 before call db1.p1()
89 after call db1.p1()
90 before call db1.p2()
91 executed db1.p2()
92 after call db1.p2()
93 connection slave;
94 start slave until master_log_file='master-bin.000001', master_log_pos=MASTER_POS;
95 include/wait_for_slave_sql_to_stop.inc
96 #
97 # If we got non-zero here, then we're suffering BUG#43263
98 #
99 SELECT 0 as 'Must be 0';
100 Must be 0
101 0
102 SELECT * from db1.t1;
103 a
104 1
105 2
106 3
107 4
108 5
109 SELECT * from db1.t2;
110 s
111 before call db1.p1()
112 executed db1.p2()
113 START SLAVE;
114 include/wait_for_slave_sql_to_start.inc
115 #
116 # SAVEPOINT and ROLLBACK TO have the same problem in BUG#43263
117 # This was reported by BUG#50407
118 connection master;
119 BEGIN;
120 INSERT INTO db1.t1 VALUES(20);
121 #
122 # Verify whether this statement is binlogged correctly
123 /*comment*/ SAVEPOINT has_comment;
124 USE db1;
125 INSERT INTO db1.t1 VALUES(30);
126 INSERT INTO db1.t2 VALUES("in savepoint has_comment");
127 Warnings:
128 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
129 USE db2;
130 SavePoint mixed_cases;
131 USE db1;
132 INSERT INTO db1.t2 VALUES("in savepoint mixed_cases");
133 Warnings:
134 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
135 INSERT INTO db1.t1 VALUES(40);
136 USE db2;
137 ROLLBACK TO mixed_cases;
138 Warnings:
139 Warning 1196 Some non-transactional changed tables couldn't be rolled back
140 ROLLBACK TO has_comment;
141 Warnings:
142 Warning 1196 Some non-transactional changed tables couldn't be rolled back
143 USE db1;
144 INSERT INTO db1.t2 VALUES("after rollback to");
145 Warnings:
146 Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
147 INSERT INTO db1.t1 VALUES(50);
148 USE db2;
149 COMMIT;
150 include/show_binlog_events.inc
151 Log_name Pos Event_type Server_id End_log_pos Info
152 master-bin.000001 # Gtid # # BEGIN GTID #-#-#
153 master-bin.000001 # Query # # use `test`; INSERT INTO db1.t1 VALUES(20)
154 master-bin.000001 # Query # # SAVEPOINT `has_comment`
155 master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(30)
156 master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("in savepoint has_comment")
157 master-bin.000001 # Query # # SAVEPOINT `mixed_cases`
158 master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("in savepoint mixed_cases")
159 master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(40)
160 master-bin.000001 # Query # # ROLLBACK TO `mixed_cases`
161 master-bin.000001 # Query # # ROLLBACK TO `has_comment`
162 master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t2 VALUES("after rollback to")
163 master-bin.000001 # Query # # use `db1`; INSERT INTO db1.t1 VALUES(50)
164 master-bin.000001 # Xid # # COMMIT /* XID */
165 connection slave;
166 #
167 # Verify INSERT statements in savepoints are executed, for MyISAM table
168 # is not effected by ROLLBACK TO
169 SELECT * FROM db1.t2 WHERE s LIKE '% savepoint %';
170 s
171 in savepoint has_comment
172 in savepoint mixed_cases
173 #
174 # Verify INSERT statements on the Innodb table are rolled back;
175 SELECT * FROM db1.t1 WHERE a IN (30, 40);
176 a
177 #
178 # Clean up
179 #
180 connection master;
181 DROP DATABASE db1;
182 DROP DATABASE db2;
183 include/rpl_end.inc