Debian Codegraph @

mysql-test/suite/rpl/t/rpl_begin_commit_rollback.test

raw
1 source include/have_innodb.inc;
2 source include/have_binlog_format_statement.inc;
3 source include/master-slave.inc;
4
5 connection slave;
6 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
7
8 connection master;
9 call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
10
11 SET @@session.binlog_direct_non_transactional_updates= FALSE;
12
13 disable_warnings;
14 DROP DATABASE IF EXISTS db1;
15 DROP DATABASE IF EXISTS db2;
16 enable_warnings;
17
18 CREATE DATABASE db1;
19 CREATE DATABASE db2;
20
21 use db1;
22
23 CREATE TABLE db1.t1 (a INT) ENGINE=InnoDB;
24 CREATE TABLE db1.t2 (s CHAR(255)) ENGINE=MyISAM;
25
26 sync_slave_with_master;
27 source include/stop_slave.inc;
28 connection master;
29
30 DELIMITER //;
31 CREATE PROCEDURE db1.p1 ()
32 BEGIN
33 INSERT INTO t1 VALUES (1);
34 INSERT INTO t1 VALUES (2);
35 INSERT INTO t1 VALUES (3);
36 INSERT INTO t1 VALUES (4);
37 INSERT INTO t1 VALUES (5);
38 END//
39
40 CREATE PROCEDURE db1.p2 ()
41 BEGIN
42 INSERT INTO t1 VALUES (6);
43 INSERT INTO t1 VALUES (7);
44 INSERT INTO t1 VALUES (8);
45 INSERT INTO t1 VALUES (9);
46 INSERT INTO t1 VALUES (10);
47 INSERT INTO t2 VALUES ('executed db1.p2()');
48 END//
49 DELIMITER ;//
50
51 INSERT INTO db1.t2 VALUES ('before call db1.p1()');
52
53 # Note: the master_log_pos is set to be the position of the BEGIN + 1,
54 # so before fix of BUG#43263 if the BEGIN is ignored, then all the
55 # INSERTS in p1 will be replicated in AUTOCOMMIT=1 mode and the slave
56 # SQL thread will stop right before the first INSERT. After fix of
57 # BUG#43263, BEGIN will not be ignored by the replication db rules,
58 # and then the whole transaction will be executed before slave SQL
59 # stop.
60 let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
61 let $master_pos= `SELECT $master_pos + 1`;
62
63 use test;
64 BEGIN;
65 CALL db1.p1();
66 COMMIT;
67
68 # The position where the following START SLAVE UNTIL will stop at
69 let $master_end_trans_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
70
71 INSERT INTO db1.t2 VALUES ('after call db1.p1()');
72 SELECT * FROM db1.t1;
73 SELECT * FROM db1.t2;
74
75 connection slave;
76
77 replace_result $master_pos MASTER_POS;
78 eval start slave until master_log_file='master-bin.000001', master_log_pos=$master_pos;
79 source include/wait_for_slave_sql_to_stop.inc;
80 let $slave_sql_stop_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1);
81 let $result= query_get_value(SELECT $slave_sql_stop_pos - $master_end_trans_pos as result, result, 1);
82
83 --echo #
84 --echo # If we got non-zero here, then we're suffering BUG#43263
85 --echo #
86 eval SELECT $result as 'Must be 0';
87 SELECT * from db1.t1;
88 SELECT * from db1.t2;
89
90 connection master;
91
92 INSERT INTO db1.t2 VALUES ('before call db1.p2()');
93
94 # See comments above.
95 let $master_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
96 let $master_pos= `SELECT $master_pos + 1`;
97
98 BEGIN;
99 CALL db1.p2();
100 disable_warnings;
101 ROLLBACK;
102 enable_warnings;
103 let $master_end_trans_pos= query_get_value(SHOW MASTER STATUS, Position, 1);
104
105 INSERT INTO db1.t2 VALUES ('after call db1.p2()');
106 SELECT * FROM db1.t1;
107 SELECT * FROM db1.t2;
108
109 connection slave;
110
111 replace_result $master_pos MASTER_POS;
112 eval start slave until master_log_file='master-bin.000001', master_log_pos=$master_pos;
113 source include/wait_for_slave_sql_to_stop.inc;
114
115 let $slave_sql_stop_pos= query_get_value(SHOW SLAVE STATUS, Exec_Master_Log_Pos, 1);
116 let $result= query_get_value(SELECT $slave_sql_stop_pos - $master_end_trans_pos as result, result, 1);
117
118 --echo #
119 --echo # If we got non-zero here, then we're suffering BUG#43263
120 --echo #
121 eval SELECT $result as 'Must be 0';
122 SELECT * from db1.t1;
123 SELECT * from db1.t2;
124
125 START SLAVE;
126 source include/wait_for_slave_sql_to_start.inc;
127
128 --echo #
129 --echo # SAVEPOINT and ROLLBACK TO have the same problem in BUG#43263
130 --echo # This was reported by BUG#50407
131 connection master;
132 let $binlog_start=query_get_value(SHOW MASTER STATUS, Position, 1);
133
134 BEGIN;
135 INSERT INTO db1.t1 VALUES(20);
136
137 --echo #
138 --echo # Verify whether this statement is binlogged correctly
139 /*comment*/ SAVEPOINT has_comment;
140 USE db1;
141 INSERT INTO db1.t1 VALUES(30);
142 INSERT INTO db1.t2 VALUES("in savepoint has_comment");
143 USE db2;
144 SavePoint mixed_cases;
145 USE db1;
146 INSERT INTO db1.t2 VALUES("in savepoint mixed_cases");
147 INSERT INTO db1.t1 VALUES(40);
148 USE db2;
149 ROLLBACK TO mixed_cases;
150 ROLLBACK TO has_comment;
151 USE db1;
152 INSERT INTO db1.t2 VALUES("after rollback to");
153 INSERT INTO db1.t1 VALUES(50);
154 USE db2;
155 COMMIT;
156 source include/show_binlog_events.inc;
157 sync_slave_with_master;
158
159 --echo #
160 --echo # Verify INSERT statements in savepoints are executed, for MyISAM table
161 --echo # is not effected by ROLLBACK TO
162 SELECT * FROM db1.t2 WHERE s LIKE '% savepoint %';
163 --echo #
164 --echo # Verify INSERT statements on the Innodb table are rolled back;
165 SELECT * FROM db1.t1 WHERE a IN (30, 40);
166
167 --echo #
168 --echo # Clean up
169 --echo #
170 connection master;
171 DROP DATABASE db1;
172 DROP DATABASE db2;
173 --source include/rpl_end.inc