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