Debian Codegraph @

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

raw
1 # BUG #12161 Xa recovery and client disconnection
2 # the test verifies that
3 # a. disconnection does not lose a prepared transaction
4 # so it can be committed from another connection
5 # c. the prepared transaction is logged
6 # d. interleaved prepared transactions are correctly applied on the slave.
7
8 #
9 # Both replication format are checked through explict
10 # set @@binlog_format in the test.
11 #
12 --source include/have_binlog_format_mixed.inc
13 #
14 # Prepared XA can't get available to an external connection
15 # until a connection, that either leaves actively or is killed,
16 # has completed a necessary part of its cleanup.
17 # Selecting from P_S.threads provides a method to learn that.
18 #
19 --source include/set_privilege_checks_user_as_system_user.inc
20 --source include/master-slave.inc
21
22 --connection master
23 call mtr.add_suppression("Found 2 prepared XA transactions");
24 CALL mtr.add_suppression("Statement is unsafe because it is being used inside a XA transaction");
25 CREATE VIEW v_processlist as SELECT * FROM performance_schema.threads where type = 'FOREGROUND';
26
27 CREATE DATABASE d1;
28 CREATE DATABASE d2;
29
30 CREATE TABLE d1.t (a INT) ENGINE=innodb;
31 CREATE TABLE d2.t (a INT) ENGINE=innodb;
32
33 connect (master_conn1, 127.0.0.1,root,,test,$MASTER_MYPORT,);
34 --let $conn_id=`SELECT connection_id()`
35 SET @@session.binlog_format= statement;
36 XA START '1-stmt';
37 INSERT INTO d1.t VALUES (1);
38 XA END '1-stmt';
39 XA PREPARE '1-stmt';
40
41 --disconnect master_conn1
42
43 --connection master
44 --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id
45 --source include/wait_condition.inc
46
47 connect (master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,);
48 --let $conn_id=`SELECT connection_id()`
49 SET @@session.binlog_format= 'row';
50 XA START '1-row';
51 INSERT INTO d2.t VALUES (1);
52 XA END '1-row';
53 XA PREPARE '1-row';
54
55 --disconnect master_conn2
56
57 --connection master
58 --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id
59 --source include/wait_condition.inc
60
61 XA START '2';
62 INSERT INTO d1.t VALUES (2);
63 XA END '2';
64 XA PREPARE '2';
65 XA COMMIT '2';
66
67 XA COMMIT '1-row';
68 XA COMMIT '1-stmt';
69 source include/show_binlog_events.inc;
70
71 # the proof: slave is in sync with the table updated by the prepared transactions.
72 --source include/sync_slave_sql_with_master.inc
73 --source include/stop_slave.inc
74
75 #
76 # Recover with Master server restart
77 #
78 --connection master
79
80 connect (master2, 127.0.0.1,root,,test,$MASTER_MYPORT,);
81 --connection master2
82 SET @@session.binlog_format= statement;
83 XA START '3-stmt';
84 INSERT INTO d1.t VALUES (3);
85 XA END '3-stmt';
86 XA PREPARE '3-stmt';
87 --disconnect master2
88
89 connect (master2, 127.0.0.1,root,,test,$MASTER_MYPORT,);
90 --connection master2
91 SET @@session.binlog_format= 'row';
92 XA START '3-row';
93 INSERT INTO d2.t VALUES (4);
94 XA END '3-row';
95 XA PREPARE '3-row';
96 --disconnect master2
97
98 --connection master
99
100 #
101 # Testing read-only
102 #
103 connect (master2, 127.0.0.1,root,,test,$MASTER_MYPORT,);
104 --connection master2
105 XA START '4';
106 SELECT * FROM d1.t;
107 XA END '4';
108 XA PREPARE '4';
109 --disconnect master2
110
111 #
112 # Logging few disconnected XA:s for replication.
113 #
114 --let $bulk_trx_num=10
115 --let $i = $bulk_trx_num
116
117 while($i > 0)
118 {
119 --connect (master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,)
120 --let $conn_id=`SELECT connection_id()`
121
122 --eval XA START 'bulk_trx_$i'
123 --eval INSERT INTO d1.t VALUES ($i)
124 --eval INSERT INTO d2.t VALUES ($i)
125 --eval XA END 'bulk_trx_$i'
126 --eval XA PREPARE 'bulk_trx_$i'
127
128 --disconnect master_bulk_conn$i
129
130 --connection master
131 --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id
132 --source include/wait_condition.inc
133
134 --dec $i
135 }
136
137 #
138 # Prove the slave applier is capable to resume the prepared XA:s
139 # upon its restart.
140 #
141 --connection slave
142 --source include/start_slave.inc
143 --connection master
144 --source include/sync_slave_sql_with_master.inc
145 --source include/stop_slave.inc
146
147 --connection master
148 --let $i = $bulk_trx_num
149 while($i > 0)
150 {
151 --let $command=COMMIT
152 if (`SELECT $i % 2`)
153 {
154 --let $command=ROLLBACK
155 }
156 --eval XA $command 'bulk_trx_$i'
157 --dec $i
158 }
159
160 --let $rpl_server_number= 1
161 --source include/rpl_restart_server.inc
162
163 --connection slave
164 --source include/start_slave.inc
165
166 --connection master
167 --echo *** '3-stmt','3-row' xa-transactions must be in the list ***
168 --sorted_result
169 XA RECOVER;
170 XA COMMIT '3-stmt';
171 XA ROLLBACK '3-row';
172
173 --source include/sync_slave_sql_with_master.inc
174
175 #
176 # Testing replication with marginal XID values and in two formats.
177 #
178
179
180 # Empty XID
181 connect (master_conn1, 127.0.0.1,root,,test,$MASTER_MYPORT,);
182 --let $conn_id=`SELECT connection_id()`
183
184 XA START '';
185 INSERT INTO d1.t VALUES (4);
186 XA END '';
187 XA PREPARE '';
188
189 --disconnect master_conn1
190
191 --connection master
192 --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id
193 --source include/wait_condition.inc
194
195 # Max size XID
196 connect (master_conn2, 127.0.0.1,root,,test,$MASTER_MYPORT,);
197 --let $conn_id=`SELECT connection_id()`
198
199 --let $gtrid=0123456789012345678901234567890123456789012345678901234567890124
200 --let $bqual=0123456789012345678901234567890123456789012345678901234567890124
201 --eval XA START '$gtrid','$bqual',64
202 INSERT INTO d1.t VALUES (64);
203 --eval XA END '$gtrid','$bqual',64
204 --eval XA PREPARE '$gtrid','$bqual',64
205
206 --disconnect master_conn2
207
208 --connection master
209 --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id
210 --source include/wait_condition.inc
211
212 # Max size XID with non-ascii chars
213 connect (master_conn3, 127.0.0.1,root,,test,$MASTER_MYPORT,);
214 --let $conn_id=`SELECT connection_id()`
215
216 --let $gtrid_hex=FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
217 --let $bqual_hex=00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
218 --eval XA START X'$gtrid_hex',X'$bqual_hex',0
219 INSERT INTO d1.t VALUES (0);
220 --eval XA END X'$gtrid_hex',X'$bqual_hex',0
221 --eval XA PREPARE X'$gtrid_hex',X'$bqual_hex',0
222
223 --disconnect master_conn3
224
225 --connection master
226 --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id
227 --source include/wait_condition.inc
228
229 # Random XID
230 --disable_query_log
231
232 connect (master_conn4, 127.0.0.1,root,,test,$MASTER_MYPORT,);
233 --let $conn_id=`SELECT connection_id()`
234
235 --let $gtridlen=`SELECT 2*(1 + round(rand()*100) % 31)`
236 --let $bquallen=`SELECT 2*(1 + round(rand()*100) % 31)`
237 --let $gtrid_rand=`SELECT substring(concat(MD5(rand()), MD5(rand())), 1, $gtridlen)`
238 --let $bqual_rand=`SELECT substring(concat(MD5(rand()), MD5(rand())), 1, $bquallen)`
239 # formatID max is LONG_MAX
240 --let $formt_rand=`SELECT floor((rand()*10000000000) % 2147483648)`
241 --eval XA START X'$gtrid_rand',X'$bqual_rand',$formt_rand
242 INSERT INTO d1.t VALUES (0);
243 --eval XA END X'$gtrid_rand',X'$bqual_rand',$formt_rand
244 --eval XA PREPARE X'$gtrid_rand',X'$bqual_rand',$formt_rand
245
246 --enable_query_log
247
248 --disconnect master_conn4
249
250 --connection master
251 --let $wait_condition= SELECT count(*) = 0 FROM v_processlist WHERE PROCESSLIST_ID = $conn_id
252 --source include/wait_condition.inc
253
254
255 XA COMMIT '';
256 --eval XA COMMIT '$gtrid','$bqual',64
257 --eval XA COMMIT X'$gtrid_hex',X'$bqual_hex',0
258 --disable_query_log
259 --echo XA COMMIT 'RANDOM XID'
260 --eval XA COMMIT X'$gtrid_rand',X'$bqual_rand',$formt_rand
261 --enable_query_log
262
263 --source include/sync_slave_sql_with_master.inc
264
265 #
266 # Testing ONE PHASE
267 #
268 --let $onephase_trx_num=10
269 --let $i = $onephase_trx_num
270 while($i > 0)
271 {
272 --connect (master_bulk_conn$i, 127.0.0.1,root,,test,$MASTER_MYPORT,)
273
274 --connection master_bulk_conn$i
275 --eval XA START 'one_phase_$i'
276 --eval INSERT INTO d1.t VALUES ($i)
277 --eval INSERT INTO d2.t VALUES ($i)
278 --eval XA END 'one_phase_$i'
279 --eval XA COMMIT 'one_phase_$i' ONE PHASE
280
281 --disconnect master_bulk_conn$i
282 --dec $i
283 }
284 --connection master
285 --source include/sync_slave_sql_with_master.inc
286
287 #
288 # Overall consistency check
289 #
290 --let $diff_tables= master:d1.t, slave:d1.t
291 --source include/diff_tables.inc
292 --let $diff_tables= master:d2.t, slave:d2.t
293 --source include/diff_tables.inc
294 #
295 # cleanup
296 #
297 --connection master
298
299 DELETE FROM d1.t;
300 DELETE FROM d2.t;
301 DROP TABLE d1.t, d2.t;
302 DROP DATABASE d1;
303 DROP DATABASE d2;
304 DROP VIEW v_processlist;
305
306 --source include/sync_slave_sql_with_master.inc
307
308 --source include/rpl_end.inc