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