Debian Codegraph @

mysql-test/suite/rpl/t/rpl_xa.inc

raw
1 #
2 # This "body" file checks general properties of XA transaction replication
3 # as of MDEV-742.
4 # Parameters:
5 # --let rpl_xa_check= SELECT ...
6 #
7 connection master;
8 create table t1 (a int, b int) engine=InnoDB;
9 insert into t1 values(0, 0);
10 xa start 't';
11 insert into t1 values(1, 2);
12 xa end 't';
13 xa prepare 't';
14 xa commit 't';
15
16 sync_slave_with_master;
17 let $diff_tables= master:t1, slave:t1;
18 source include/diff_tables.inc;
19
20 connection master;
21
22 xa start 't';
23 insert into t1 values(3, 4);
24 xa end 't';
25 xa prepare 't';
26 xa rollback 't';
27
28 sync_slave_with_master;
29 let $diff_tables= master:t1, slave:t1;
30 source include/diff_tables.inc;
31
32 connection master;
33 --disable_warnings
34 SET pseudo_slave_mode=1;
35 --enable_warnings
36 create table t2 (a int) engine=InnoDB;
37 xa start 't';
38 insert into t1 values (5, 6);
39 xa end 't';
40 xa prepare 't';
41 xa start 's';
42 insert into t2 values (0);
43 xa end 's';
44 xa prepare 's';
45 --source include/save_master_gtid.inc
46
47 connection slave;
48 source include/sync_with_master_gtid.inc;
49 if ($rpl_xa_check)
50 {
51 --eval $rpl_xa_check
52 if ($rpl_xa_verbose)
53 {
54 --eval SELECT $rpl_xa_check_lhs
55 --eval SELECT $rpl_xa_check_rhs
56 }
57 }
58 sorted_result;
59 xa recover;
60
61 connection master;
62 xa commit 't';
63 xa commit 's';
64 --disable_warnings
65 SET pseudo_slave_mode=0;
66 --enable_warnings
67 sync_slave_with_master;
68 let $diff_tables= master:t1, slave:t1;
69 source include/diff_tables.inc;
70 let $diff_tables= master:t2, slave:t2;
71 source include/diff_tables.inc;
72
73 #
74 # Read-only XA remains prepared after disconnect and must rollback at XA-complete
75 # after recoonect. To the read-only also belongs non-transactional engine XA.
76 #
77 --connection master
78
79 --echo *** At the start of read-only section gtid list is:
80 flush logs;
81 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
82 --source include/show_gtid_list.inc
83
84
85 set @query1="select 1";
86 set @query2="select count(*) into @s2 from t1";
87 --let $ro_cases=2
88 --let $db=test
89
90 # No disconnect
91 --let $p_trx=$ro_cases
92 while ($p_trx)
93 {
94 --connection master
95 --let $xid=ro_$p_trx
96 --let $query=`SELECT @query$p_trx`
97 --disable_cursor_protocol
98 --source rpl_create_xa_prepared.inc
99 --enable_cursor_protocol
100 --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')`
101 --error 0
102 --disable_query_log
103 --disable_result_log
104 --eval xa $complete '$xid'
105 --enable_result_log
106 --enable_query_log
107
108 --disconnect master_$xid
109 --source include/wait_until_disconnected.inc
110
111 --dec $p_trx
112 }
113
114
115 --let $p_trx=$ro_cases
116 # With diconnect
117 while ($p_trx)
118 {
119 --connection master
120 --let $xid=ro_$p_trx
121 --let $query=`SELECT @query$p_trx`
122 --disable_cursor_protocol
123 --source rpl_create_xa_prepared.inc
124 --enable_cursor_protocol
125 --disconnect master_$xid
126 --source include/wait_until_disconnected.inc
127
128 --dec $p_trx
129 }
130
131 --echo *** $ro_cases prepared xa:s must be in the list:
132 --connection master
133 sorted_result;
134 xa recover;
135
136 --let $p_trx=$ro_cases
137 while ($p_trx)
138 {
139 --let $xid=ro_$p_trx
140 --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')`
141 --disable_query_log
142 --disable_result_log
143 --error ER_XA_RBROLLBACK
144 --eval xa $complete '$xid'
145 --enable_result_log
146 --enable_query_log
147
148 --dec $p_trx
149 }
150 --echo *** Zero prepared xa:s must be in the list:
151 xa recover;
152
153 --echo *** At the end of read-only section gtid list has 0 more compare with previous check:
154 flush logs;
155 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
156 --source include/show_gtid_list.inc
157
158
159 #
160 # XA logging cases while some of XA resources are read-only
161 #
162 # A1. Binlog filter
163
164
165 --let $db=test_ign
166 --eval create database $db
167 set @@sql_log_bin = 0;
168 --eval create table $db.t (a int) engine=InnoDB
169 set @@sql_log_bin = 1;
170
171 --let $xid=rw_no_binlog
172 --let $query=insert into $db.t set a=1
173 --source rpl_create_xa_prepared.inc
174 --disconnect master_$xid
175 --source include/wait_until_disconnected.inc
176
177 --echo *** $xid must be in the list:
178 --connection master
179 xa recover;
180
181 --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')`
182 --error 0
183 --disable_query_log
184 --disable_result_log
185 --eval xa $complete '$xid'
186 --enable_result_log
187 --enable_query_log
188
189 --echo *** Zero must be in the list:
190 --connection master
191 xa recover;
192 # restore for the following tests
193 --let $db=test
194
195 --echo *** At the end of --binlog-ignore-db section gtid list has 2 more:
196 flush logs;
197 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
198 --source include/show_gtid_list.inc
199
200 #
201 # A2. Opposite to A1, ineffective execution in Engine may create a
202 # binlog transaction
203 #
204 connection master;
205 create table t3 (a int) engine=innodb;
206
207 --echo *** the disconnected prepare case
208 --let $xid=rw_binlog_only
209 --let $query=delete from t3
210 --connect (master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,)
211 set @@binlog_format=statement;
212 # --source rpl_create_xa_prepared.inc
213 --eval xa start '$xid'
214 --eval $query
215 --eval xa end '$xid'
216 --eval xa prepare '$xid'
217
218 --disconnect master_$xid
219 --source include/wait_until_disconnected.inc
220
221 connection master;
222 --echo *** $xid must be in the list:
223 xa recover;
224
225 --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')`
226 --disable_query_log
227 --disable_result_log
228 --eval xa $complete '$xid'
229 --enable_result_log
230 --enable_query_log
231
232 --echo *** Zero must be in the list:
233 xa recover;
234
235 --echo *** the same connection complete case.
236 connection master;
237 --let $xid=rw_binlog_only
238 --let $query=delete from t3
239 --connect (master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,)
240 set @@binlog_format=statement;
241 # --source rpl_create_xa_prepared.inc
242 --eval xa start '$xid'
243 --eval $query
244 --eval xa end '$xid'
245 --eval xa prepare '$xid'
246
247 --echo *** $xid must be in the list:
248 xa recover;
249
250 --disable_query_log
251 --disable_result_log
252 --eval xa $complete '$xid'
253 --enable_result_log
254 --enable_query_log
255 --disconnect master_$xid
256 --source include/wait_until_disconnected.inc
257
258 --echo *** Zero must be in the list:
259 --connection master
260 xa recover;
261
262 --echo *** At the end of ineffective in engine section gtid list has 5 more:
263 flush logs;
264 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
265 --source include/show_gtid_list.inc
266
267 #
268 # A3 MyISAM "xa" logs empty XA-prepare group, followed by
269 # an XA-complete event
270 create table tm (a int) engine=myisam;
271
272 # No disconnect
273 --connection master
274 --let $xid=rw_myisam
275 --let $query=insert into tm set a=1
276 --source rpl_create_xa_prepared.inc
277 --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')`
278 --error 0
279 --disable_query_log
280 --disable_result_log
281 --eval xa $complete '$xid'
282 --enable_result_log
283 --enable_query_log
284
285 --disconnect master_$xid
286 --source include/wait_until_disconnected.inc
287
288 # With diconnect
289 --connection master
290 --source rpl_create_xa_prepared.inc
291 --disconnect master_$xid
292 --source include/wait_until_disconnected.inc
293
294 --echo *** $xid prepared must be in the list:
295 --connection master
296 xa recover;
297
298 --let $complete=`select if(floor(rand()*10)%2,'COMMIT','ROLLBACK')`
299 --disable_query_log
300 --disable_result_log
301 --eval xa $complete '$xid'
302 --enable_result_log
303 --enable_query_log
304
305 --echo *** Zero prepared xa:s must be in the list:
306 xa recover;
307
308 --echo *** At the end of MyISAM "xa" section gtid list has 7 more compare with previous check:
309 flush logs;
310 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
311 --source include/show_gtid_list.inc
312
313
314 # B. Session binlog disable does not log even empty XA-prepare
315 # Therefore XA-complete should be also run in sql_log_bin-OFF environment.
316
317 --let $db=test
318 --let $xid=skip_binlog
319 --let $query=insert into t2 values(1)
320 --connect (master_$xid, 127.0.0.1,root,,$db,$MASTER_MYPORT,)
321 set @@session.sql_log_bin = OFF;
322 --eval xa start '$xid'
323 --eval $query
324 --eval xa end '$xid'
325 --eval xa prepare '$xid'
326
327 --disconnect master_$xid
328 --source include/wait_until_disconnected.inc
329
330 --echo *** $xid must be in the list:
331 --connection master
332 xa recover;
333
334 # now commit it carefully to avoid binlogging as the prepare part did
335 set @@session.sql_log_bin = OFF;
336 --eval xa rollback '$xid'
337 set @@session.sql_log_bin = ON;
338 --source include/save_master_gtid.inc
339
340 --echo *** Zero must be in the list:
341 --connection master
342 xa recover;
343
344 --echo *** At the end of skip_log_binb section gtid list has 0 more:
345 flush logs;
346 --let $binlog_file= query_get_value(SHOW MASTER STATUS, File, 1)
347 --source include/show_gtid_list.inc
348
349 #
350 # sync slave successfully to prove its consistency
351 #
352 --connection slave
353 source include/sync_with_master_gtid.inc;
354
355
356 connection master;
357 --eval drop database test_ign
358 drop table t1, t2, t3, tm;
359
360 --echo #
361 --echo # MDEV-26682 slave lock timeout with XA and gap locks
362 --echo #
363 create table t1 (a int primary key, b int unique) engine=innodb;
364 insert t1 values (1,1),(3,3),(5,5);
365 sync_slave_with_master;
366
367 # set a strong isolation level to keep the read view below.
368 # alternatively a long-running select can do that too even in read-committed
369 set session transaction_isolation='repeatable-read';
370 start transaction;
371 # opens a read view to disable purge on the slave
372 select * from t1;
373
374 connect m2, localhost, root;
375 # now, delete a value, purge it on the master, but not on the slave
376 delete from t1 where a=3;
377 xa start 'x1';
378 # this sets a gap lock on <3>, when it exists (so, on the slave)
379 update t1 set b=3 where a=5;
380 xa end 'x1';
381 xa prepare 'x1';
382
383 connect m3, localhost, root;
384 # and this tries to insert straight into the locked gap
385 insert t1 values (2, 2);
386
387 echo -->slave;
388 sync_slave_with_master;
389 commit;
390 select * from t1;
391
392 connection m2;
393 xa rollback 'x1';
394 disconnect m2;
395 disconnect m3;
396
397 connection master;
398
399 drop table t1;
400
401 create table t1 (id int auto_increment primary key, c1 int not null unique)
402 engine=innodb;
403
404 create table t2 (id int auto_increment primary key, c1 int not null,
405 foreign key(c1) references t1(c1), unique key(c1)) engine=innodb;
406
407 insert t1 values (869,1), (871,3), (873,4), (872,5), (870,6), (877,7);
408 insert t2 values (795,6), (800,7);
409
410 xa start '1';
411 update t2 set id = 9, c1 = 5 where c1 in (null, null, null, null, null, 7, 3);
412
413 connect con1, localhost,root;
414 xa start '2';
415 delete from t1 where c1 like '3%';
416 xa end '2';
417 xa prepare '2';
418
419 connection master;
420 xa end '1';
421 xa prepare '1';
422
423 echo ->slave;
424
425 sync_slave_with_master;
426
427 connection slave;
428 source include/sync_with_master_gtid.inc;
429
430 connection con1;
431 xa commit '2';
432 disconnect con1;
433
434 connection master;
435 xa commit '1';
436 drop table t2, t1;