1call mtr.add_suppression("Deadlock found when trying to get lock; try restarting transaction");
2drop table if exists t1, t2;
3create table t1 (a int) engine=innodb;
4xa start 'test1';
5insert t1 values (10);
6xa end 'test1';
7xa prepare 'test1';
8xa rollback 'test1';
9select * from t1;
10a
11xa start 'test2';
12xa start 'test-bad';
13ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
14insert t1 values (20);
15xa prepare 'test2';
16ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
17xa end 'test2';
18xa prepare 'test2';
19xa commit 'test2';
20select * from t1;
21a
2220
23xa start 'testa','testb';
24insert t1 values (30);
25commit;
26ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
27xa end 'testa','testb';
28begin;
29ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
30create table t2 (a int);
31ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
32connect  con1,localhost,root,,;
33connection con1;
34xa start 'testa','testb';
35ERROR XAE08: XAER_DUPID: The XID already exists
36xa start 'testa','testb', 123;
37ERROR XAE08: XAER_DUPID: The XID already exists
38xa start 0x7465737462, 0x2030405060, 0xb;
39insert t1 values (40);
40xa end 'testb',' 0@P`',11;
41xa prepare 'testb',0x2030405060,11;
42start transaction;
43ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
44xa recover;
45formatID	gtrid_length	bqual_length	data
4611	5	5	testb 0@P`
47connection default;
48xa prepare 'testa','testb';
49xa recover;
50formatID	gtrid_length	bqual_length	data
5111	5	5	testb 0@P`
521	5	5	testatestb
53xa commit 'testb',0x2030405060,11;
54ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
55xa rollback 'testa','testb';
56xa start 'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz';
57ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
58select * from t1;
59a
6020
61disconnect con1;
62xa rollback 'testb',0x2030405060,11;
63xa recover;
64formatID	gtrid_length	bqual_length	data
65connection default;
66xa start 'tr1';
67insert t1 values (40);
68xa end 'tr1';
69xa prepare 'tr1';
70xa recover format='SQL';
71formatID	gtrid_length	bqual_length	data
721	3	0	'tr1'
73xa rollback 'tr1';
74xa start 'tr1', 'bq';
75insert t1 values (40);
76xa end 'tr1', 'bq';
77xa prepare 'tr1', 'bq';
78xa recover format='SQL';
79formatID	gtrid_length	bqual_length	data
801	3	2	'tr1','bq'
81xa rollback 'tr1', 'bq';
82xa start 'tr1', 'bq', 3;
83insert t1 values (40);
84xa end 'tr1', 'bq', 3;
85xa prepare 'tr1', 'bq', 3;
86xa recover format='SQL';
87formatID	gtrid_length	bqual_length	data
883	3	2	'tr1','bq',3
89xa rollback 'tr1', 'bq', 3;
90xa start 'tr1#$';
91insert t1 values (40);
92xa end 'tr1#$';
93xa prepare 'tr1#$';
94xa recover format='SQL';
95formatID	gtrid_length	bqual_length	data
961	5	0	X'7472312324'
97xa rollback 'tr1#$';
98xa start 'tr1#$', 'bq';
99insert t1 values (40);
100xa end 'tr1#$', 'bq';
101xa prepare 'tr1#$', 'bq';
102xa recover format='SQL';
103formatID	gtrid_length	bqual_length	data
1041	5	2	X'7472312324',X'6271'
105xa rollback 'tr1#$', 'bq';
106xa start 'tr1#$', 'bq', 3;
107insert t1 values (40);
108xa end 'tr1#$', 'bq', 3;
109xa prepare 'tr1#$', 'bq', 3;
110xa recover format='RAW';
111formatID	gtrid_length	bqual_length	data
1123	5	2	tr1#$bq
113xa recover format='PLAIN';
114ERROR HY000: Unknown XA RECOVER format name: 'PLAIN'
115xa recover format='SQL';
116formatID	gtrid_length	bqual_length	data
1173	5	2	X'7472312324',X'6271',3
118xa rollback 'tr1#$', 'bq', 3;
119drop table t1;
120drop table if exists t1;
121create table t1(a int, b int, c varchar(20), primary key(a)) engine = innodb;
122insert into t1 values(1, 1, 'a');
123insert into t1 values(2, 2, 'b');
124connect  con1,localhost,root,,;
125connect  con2,localhost,root,,;
126connection con1;
127xa start 'a','b';
128update t1 set c = 'aa' where a = 1;
129connection con2;
130xa start 'a','c';
131update t1 set c = 'bb' where a = 2;
132connection con1;
133update t1 set c = 'bb' where a = 2;
134connection con2;
135update t1 set c = 'aa' where a = 1;
136ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
137select count(*) from t1;
138count(*)
1392
140xa end 'a','c';
141ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
142xa rollback 'a','c';
143disconnect con2;
144connect  con3,localhost,root,,;
145connection con3;
146xa start 'a','c';
147disconnect con1;
148disconnect con3;
149connection default;
150drop table t1;
151#
152# BUG#51342 - more xid crashing
153#
154CREATE TABLE t1(a INT) ENGINE=InnoDB;
155XA START 'x';
156SET SESSION autocommit=0;
157INSERT INTO t1 VALUES(1);
158SET SESSION autocommit=1;
159ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
160SELECT @@autocommit;
161@@autocommit
1620
163INSERT INTO t1 VALUES(1);
164XA END 'x';
165XA COMMIT 'x' ONE PHASE;
166DROP TABLE t1;
167SET SESSION autocommit=1;
168End of 5.0 tests
169xa start 'a';
170xa end 'a';
171xa rollback 'a';
172xa start 'a';
173xa end 'a';
174xa rollback 'a';
175xa start 'a';
176xa end 'a';
177xa prepare 'a';
178xa commit 'a';
179xa start 'a';
180xa end 'a';
181xa prepare 'a';
182xa commit 'a';
183CREATE TABLE t1(a INT, KEY(a)) ENGINE=InnoDB;
184INSERT INTO t1 VALUES(1),(2);
185connect con1,localhost,root,,;
186BEGIN;
187UPDATE t1 SET a=3 WHERE a=1;
188connection default;
189BEGIN;
190UPDATE t1 SET a=4 WHERE a=2;
191connection con1;
192UPDATE t1 SET a=5 WHERE a=2;
193connection default;
194UPDATE t1 SET a=5 WHERE a=1;
195ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
196ROLLBACK;
197connection con1;
198ROLLBACK;
199BEGIN;
200UPDATE t1 SET a=3 WHERE a=1;
201connection default;
202XA START 'xid1';
203UPDATE t1 SET a=4 WHERE a=2;
204connection con1;
205UPDATE t1 SET a=5 WHERE a=2;
206connection default;
207UPDATE t1 SET a=5 WHERE a=1;
208ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
209XA END 'xid1';
210ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
211XA ROLLBACK 'xid1';
212XA START 'xid1';
213XA END 'xid1';
214XA ROLLBACK 'xid1';
215disconnect con1;
216DROP TABLE t1;
217#
218# Bug#56448 Assertion failed: ! is_set() with second xa end
219#
220XA START 'x';
221XA END 'x';
222XA END 'x';
223ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
224XA PREPARE 'x';
225XA PREPARE 'x';
226ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
227XA ROLLBACK 'x';
228#
229# Bug#59986 Assert in Diagnostics_area::set_ok_status() for XA COMMIT
230#
231DROP TABLE IF EXISTS t1;
232CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a)) engine=InnoDB;
233INSERT INTO t1 VALUES (1, 1), (2, 2);
234connect  con1, localhost, root;
235XA START 'a';
236UPDATE t1 SET b= 3 WHERE a=1;
237connection default;
238XA START 'b';
239UPDATE t1 SET b=4 WHERE a=2;
240# Sending:
241UPDATE t1 SET b=5 WHERE a=1;
242connection con1;
243UPDATE t1 SET b=6 WHERE a=2;
244ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
245XA COMMIT 'a';
246ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
247connection default;
248# Reaping: UPDATE t1 SET b=5 WHERE a=1
249XA END 'b';
250XA ROLLBACK 'b';
251DROP TABLE t1;
252disconnect con1;
253#
254# Bug#11766752 59936: multiple xa assertions - transactional
255#              statement fuzzer
256#
257CREATE TABLE t1 (a INT) engine=InnoDB;
258XA START 'a';
259INSERT INTO t1 VALUES (1);
260SAVEPOINT savep;
261XA END 'a';
262SELECT * FROM t1;
263ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
264INSERT INTO t1 VALUES (2);
265ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
266SAVEPOINT savep;
267ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
268SET @a=(SELECT * FROM t1);
269ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
270XA PREPARE 'a';
271SELECT * FROM t1;
272ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
273INSERT INTO t1 VALUES (2);
274ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
275SAVEPOINT savep;
276ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
277SET @a=(SELECT * FROM t1);
278ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
279UPDATE t1 SET a=1 WHERE a=2;
280ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
281XA COMMIT 'a';
282SELECT * FROM t1;
283a
2841
285DROP TABLE t1;
286#
287# MDEV-14609 XA Transction unable to ROLLBACK TO SAVEPOINT
288#
289CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
290XA START 'xa1';
291SAVEPOINT savepoint1;
292INSERT INTO t1 (c1) VALUES (1),(2),(3),(4);
293ROLLBACK TO SAVEPOINT savepoint1;
294XA END 'xa1';
295XA ROLLBACK 'xa1';
296DROP TABLE t1;
297#
298# Bug#12352846 - TRANS_XA_START(THD*):
299#                ASSERTION THD->TRANSACTION.XID_STATE.XID.IS_NULL()
300#                FAILED
301#
302CREATE TABLE t1 (a INT) ENGINE=InnoDB;
303CREATE TABLE t2 (a INT) ENGINE=InnoDB;
304INSERT INTO t2 VALUES (1);
305COMMIT;
306BEGIN;
307INSERT INTO t2 VALUES (2);
308UPDATE t2 SET a=a+1;
309connect  con2,localhost,root;
310XA START 'xid1';
311INSERT INTO t1 VALUES (1);
312# Sending:
313DELETE FROM t2;
314connection default;
315DELETE FROM t1;
316connection con2;
317ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
318XA COMMIT 'xid1';
319ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
320connection default;
321COMMIT;
322connection con2;
323XA START 'xid1';
324XA END 'xid1';
325XA PREPARE 'xid1';
326XA ROLLBACK 'xid1';
327connection default;
328DROP TABLE t1, t2;
329disconnect con2;
330CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB;
331CREATE TABLE t2 (pk INT PRIMARY KEY) ENGINE=InnoDB;
332INSERT INTO t2 VALUES (1),(2);
333CREATE TABLE t3 (i INT) ENGINE=InnoDB;
334XA BEGIN 'xid1';
335REPLACE INTO t1 SELECT * FROM t2;
336connect  con1,localhost,root,,test;
337XA BEGIN 'xid2';
338INSERT INTO t1 SELECT * FROM t2;
339connection default;
340REPLACE INTO t2 SELECT * FROM t2;
341connection con1;
342disconnect con1;
343connect  con2,localhost,root,,test;
344INSERT INTO t3 VALUES (1);
345XA BEGIN 'xid3';
346disconnect con2;
347connection default;
348XA END 'xid1';
349XA ROLLBACK 'xid1';
350DROP TABLE t1, t2, t3;
351#
352# MDEV 15532 XA: Assertion `!log->same_pk' failed in
353# row_log_table_apply_delete
354#
355CREATE TABLE t1 (a INT) ENGINE=InnoDB;
356INSERT INTO t1 VALUES (1),(2);
357connect  con1,localhost,root,,test;
358XA START 'xid';
359UPDATE t1 SET a = 5;
360connection default;
361SET innodb_lock_wait_timeout= 2, lock_wait_timeout= 2;
362ALTER TABLE non_existing_table1;
363ERROR 42S02: Table 'test.non_existing_table1' doesn't exist
364ALTER TABLE t1 FORCE;;
365connection con1;
366ALTER TABLE non_existing_table2;
367ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
368DELETE FROM t1 LIMIT 1;
369connection default;
370ERROR HY000: Lock wait timeout exceeded; try restarting transaction
371connection con1;
372XA END 'xid';
373XA ROLLBACK 'xid';
374DROP TABLE t1;
375disconnect con1;
376connection default;
377#
378# MDEV-21766 - Forbid XID with empty 'gtrid'
379#
380CREATE TABLE t1(a INT) ENGINE=InnoDB;
381XA BEGIN '';
382ERROR XAE05: XAER_INVAL: Invalid arguments (or unsupported command)
383XA BEGIN '8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x',
384'8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x';
385INSERT INTO t1 VALUES(1);
386XA END '8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x',
387'8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x';
388XA PREPARE '8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x',
389'8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x';
390XA ROLLBACK '8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x',
391'8bytes1x8bytes2x8bytes3x8bytes4x8bytes5x8bytes6x8bytes7x8bytes8x';
392SET NAMES utf8;
393XA BEGIN 'Я_упала_с_сеновала_тормозила_головой';
394ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
395XA BEGIN 'Я_упaлa_c_сеновала_тормозила_головой';
396XA END 'Я_упaлa_c_сеновала_тормозила_головой';
397XA PREPARE 'Я_упaлa_c_сеновала_тормозила_головой';
398XA ROLLBACK 'Я_упaлa_c_сеновала_тормозила_головой';
399SET NAMES default;
400DROP TABLE t1;
401#
402# MDEV-21659 XA rollback foreign_xid is allowed inside active XA
403# MDEV-21854 - xa commit  one phase for already prepared transaction
404#              must always error out
405#
406BEGIN;
407XA COMMIT 'unknown';
408ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
409XA COMMIT 'unknown' ONE PHASE;
410ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
411BEGIN;
412XA ROLLBACK 'unknown';
413ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
414ROLLBACK;
415XA START 'xid1';
416XA COMMIT 'unknown';
417ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
418XA COMMIT 'unknown' ONE PHASE;
419ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
420XA ROLLBACK 'unknown';
421ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
422XA END 'xid1';
423XA PREPARE 'xid1';
424XA COMMIT 'xid1' ONE PHASE;
425ERROR XAE05: XAER_INVAL: Invalid arguments (or unsupported command)
426XA ROLLBACK 'xid1';
427#
428# MDEV-21856 - xid_t::formatID has to be constrained to 4 byte size
429#
430XA START 'gtrid', 'bqual', 0x80000000;
431ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0x80000000' at line 1
432#
433# XA states and SHOW commands
434#
435create table t1 (pk int primary key) engine=innodb;
436xa start 'foo';
437insert t1 set pk=1;
438xa end 'foo';
439xa prepare 'foo';
440show status like 'foo';
441Variable_name	Value
442select table_name,table_comment from information_schema.tables where table_schema='test';
443table_name	t1
444table_comment
445select table_name,table_rows,table_comment from information_schema.tables where table_schema='test';
446table_name	t1
447table_rows	NULL
448table_comment	XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
449Warnings:
450Level	Warning
451Code	1399
452Message	XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
453xa commit 'foo';
454drop table t1;
455#
456# MDEV-22445 Crash on HANDLER READ NEXT after XA PREPARE
457#
458CREATE TABLE t (a INT KEY) ENGINE=InnoDB;
459HANDLER t OPEN AS t;
460XA START '0';
461SELECT * FROM t;
462a
463XA END '0';
464XA PREPARE '0';
465HANDLER t READ NEXT;
466ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  PREPARED state
467# Cleanup
468XA COMMIT '0';
469DROP TABLE t;
470#
471# End of 10.2 tests
472#
473XA BEGIN 'xid';
474CREATE TEMPORARY SEQUENCE s;
475ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
476XA END 'xid';
477XA ROLLBACK 'xid';
478XA BEGIN 'xid';
479CREATE SEQUENCE s;
480ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
481XA END 'xid';
482XA ROLLBACK 'xid';
483#
484# End of 10.3 tests
485#
486#
487# Start of 10.5 tests
488#
489# MDEV-7974 related
490# Check XA state when lock_wait_timeout happens
491# More tests added to flush_read_lock.test
492connect  con_tmp,localhost,root,,;
493set session lock_wait_timeout=1;
494create table asd (a int) engine=innodb;
495xa start 'test1';
496insert into asd values(1);
497xa end 'test1';
498connection default;
499flush table with read lock;
500connection con_tmp;
501# PREPARE error will do auto rollback.
502xa prepare 'test1';
503ERROR HY000: Lock wait timeout exceeded; try restarting transaction
504show errors;
505Level	Code	Message
506Error	1205	Lock wait timeout exceeded; try restarting transaction
507Error	1402	XA_RBROLLBACK: Transaction branch was rolled back
508connection default;
509unlock tables;
510connection con_tmp;
511xa start 'test1';
512insert into asd values(1);
513xa end 'test1';
514xa prepare 'test1';
515connection default;
516flush tables with read lock;
517connection con_tmp;
518# LOCK error during ROLLBACK will not alter transaction state.
519xa rollback 'test1';
520ERROR HY000: Lock wait timeout exceeded; try restarting transaction
521show errors;
522Level	Code	Message
523Error	1205	Lock wait timeout exceeded; try restarting transaction
524Error	1401	XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
525xa recover;
526formatID	gtrid_length	bqual_length	data
5271	5	0	test1
528# LOCK error during COMMIT will not alter transaction state.
529xa commit 'test1';
530ERROR HY000: Lock wait timeout exceeded; try restarting transaction
531show errors;
532Level	Code	Message
533Error	1205	Lock wait timeout exceeded; try restarting transaction
534Error	1401	XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
535xa recover;
536formatID	gtrid_length	bqual_length	data
5371	5	0	test1
538connection default;
539unlock tables;
540connection con_tmp;
541xa rollback 'test1';
542xa recover;
543formatID	gtrid_length	bqual_length	data
544drop table asd;
545disconnect con_tmp;
546connection default;
547#
548# End of 10.5 tests
549#
550