1# 2# WL#1756 3# 4-- source include/have_innodb.inc 5 6--source include/not_embedded.inc 7 8# Save the initial number of concurrent sessions 9--source include/count_sessions.inc 10 11--disable_warnings 12drop table if exists t1, t2; 13--enable_warnings 14create table t1 (a int) engine=innodb; 15xa start 'test1'; 16insert t1 values (10); 17xa end 'test1'; 18xa prepare 'test1'; 19xa rollback 'test1'; 20select * from t1; 21 22xa start 'test2'; 23--error ER_XAER_RMFAIL 24xa start 'test-bad'; 25insert t1 values (20); 26--error ER_XAER_RMFAIL 27xa prepare 'test2'; 28xa end 'test2'; 29xa prepare 'test2'; 30xa commit 'test2'; 31select * from t1; 32 33xa start 'testa','testb'; 34insert t1 values (30); 35 36--error ER_XAER_RMFAIL 37commit; 38 39xa end 'testa','testb'; 40 41--error ER_XAER_RMFAIL 42begin; 43--error ER_XAER_RMFAIL 44create table t2 (a int); 45 46connect (con1,localhost,root,,); 47connection con1; 48 49--error ER_XAER_DUPID 50xa start 'testa','testb'; 51--error ER_XAER_DUPID 52xa start 'testa','testb', 123; 53 54# gtrid [ , bqual [ , formatID ] ] 55xa start 0x7465737462, 0x2030405060, 0xb; 56insert t1 values (40); 57xa end 'testb',' 0@P`',11; 58xa prepare 'testb',0x2030405060,11; 59 60--error ER_XAER_RMFAIL 61start transaction; 62 63xa recover; 64 65# uncomment the line below when binlog will be able to prepare 66#disconnect con1; 67connection default; 68 69xa prepare 'testa','testb'; 70 71xa recover; 72 73# When this connection has already started an own XA transaction 74# it cannot execute XA COMMIT for another XA. 75--error ER_XAER_RMFAIL 76xa commit 'testb',0x2030405060,11; 77# nor XA ROLLBACK on "foreign" XA. 78--error ER_XAER_RMFAIL 79xa rollback 'testb',0x2030405060,11; 80xa rollback 'testa','testb'; 81 82# "Foreign" XA is not in an allowed state to be committed 83--error ER_XAER_NOTA 84xa commit 'testb',0x2030405060,11; 85# nor be rolled back 86--error ER_XAER_NOTA 87xa rollback 'testb',0x2030405060,11; 88 89--error ER_PARSE_ERROR 90xa start 'zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz'; 91 92select * from t1; 93 94disconnect con1; 95connection default; 96 97drop table t1; 98 99# 100# Bug#28323: Server crashed in xid cache operations 101# 102 103--disable_warnings 104drop table if exists t1; 105--enable_warnings 106 107create table t1(a int, b int, c varchar(20), primary key(a)) engine = innodb; 108insert into t1 values(1, 1, 'a'); 109insert into t1 values(2, 2, 'b'); 110 111connect (con1,localhost,root,,); 112connect (con2,localhost,root,,); 113 114--connection con1 115xa start 'a','b'; 116update t1 set c = 'aa' where a = 1; 117--connection con2 118xa start 'a','c'; 119update t1 set c = 'bb' where a = 2; 120--connection con1 121--send update t1 set c = 'bb' where a = 2 122--connection con2 123--sleep 1 124--error ER_LOCK_DEADLOCK 125update t1 set c = 'aa' where a = 1; 126--error ER_XA_RBDEADLOCK 127select count(*) from t1; 128--error ER_XA_RBDEADLOCK 129xa end 'a','c'; 130xa rollback 'a','c'; 131--disconnect con2 132 133connect (con3,localhost,root,,); 134--connection con3 135xa start 'a','c'; 136--connection con1 137--reap 138--disconnect con1 139--disconnect con3 140--connection default 141drop table t1; 142 143--echo # 144--echo # BUG#51342 - more xid crashing 145--echo # 146CREATE TABLE t1(a INT) ENGINE=InnoDB; 147XA START 'x'; 148SET SESSION autocommit=0; 149INSERT INTO t1 VALUES(1); 150--error ER_XAER_RMFAIL 151SET SESSION autocommit=1; 152SELECT @@autocommit; 153INSERT INTO t1 VALUES(1); 154XA END 'x'; 155XA COMMIT 'x' ONE PHASE; 156DROP TABLE t1; 157SET SESSION autocommit=1; 158 159--echo End of 5.0 tests 160 161# 162# Bug#44672: Assertion failed: thd->transaction.xid_state.xid.is_null() 163# 164 165xa start 'a'; 166xa end 'a'; 167xa rollback 'a'; 168xa start 'a'; 169xa end 'a'; 170xa rollback 'a'; 171 172# 173# Bug#45548: XA transaction without access to InnoDB tables crashes the server 174# 175 176xa start 'a'; 177xa end 'a'; 178xa prepare 'a'; 179xa commit 'a'; 180 181xa start 'a'; 182xa end 'a'; 183xa prepare 'a'; 184xa commit 'a'; 185 186# 187# BUG#43171 - Assertion failed: thd->transaction.xid_state.xid.is_null() 188# 189CREATE TABLE t1(a INT, KEY(a)) ENGINE=InnoDB; 190INSERT INTO t1 VALUES(1),(2); 191connect(con1,localhost,root,,); 192 193# Part 1: Prepare to test XA START after regular transaction deadlock 194BEGIN; 195UPDATE t1 SET a=3 WHERE a=1; 196 197connection default; 198BEGIN; 199UPDATE t1 SET a=4 WHERE a=2; 200 201connection con1; 202let $conn_id= `SELECT CONNECTION_ID()`; 203SEND UPDATE t1 SET a=5 WHERE a=2; 204 205connection default; 206let $wait_timeout= 2; 207let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST 208WHERE ID=$conn_id AND STATE='Searching rows for update'; 209--source include/wait_condition.inc 210 211--error ER_LOCK_DEADLOCK 212UPDATE t1 SET a=5 WHERE a=1; 213ROLLBACK; 214 215# Part 2: Prepare to test XA START after XA transaction deadlock 216connection con1; 217REAP; 218ROLLBACK; 219BEGIN; 220UPDATE t1 SET a=3 WHERE a=1; 221 222connection default; 223XA START 'xid1'; 224UPDATE t1 SET a=4 WHERE a=2; 225 226connection con1; 227SEND UPDATE t1 SET a=5 WHERE a=2; 228 229connection default; 230let $wait_timeout= 2; 231let $wait_condition= SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST 232WHERE ID=$conn_id AND STATE='Searching rows for update'; 233--source include/wait_condition.inc 234 235--error ER_LOCK_DEADLOCK 236UPDATE t1 SET a=5 WHERE a=1; 237--error ER_XA_RBDEADLOCK 238XA END 'xid1'; 239XA ROLLBACK 'xid1'; 240 241XA START 'xid1'; 242XA END 'xid1'; 243XA ROLLBACK 'xid1'; 244 245connection con1; 246REAP; 247disconnect con1; 248 249connection default; 250DROP TABLE t1; 251 252 253--echo # 254--echo # Bug#56448 Assertion failed: ! is_set() with second xa end 255--echo # 256 257XA START 'x'; 258XA END 'x'; 259# Second XA END caused an assertion. 260--error ER_XAER_RMFAIL 261XA END 'x'; 262XA PREPARE 'x'; 263# Second XA PREPARE also caused an assertion. 264--error ER_XAER_RMFAIL 265XA PREPARE 'x'; 266XA ROLLBACK 'x'; 267 268 269--echo # 270--echo # Bug#59986 Assert in Diagnostics_area::set_ok_status() for XA COMMIT 271--echo # 272 273--disable_warnings 274DROP TABLE IF EXISTS t1; 275--enable_warnings 276 277CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a)) engine=InnoDB; 278INSERT INTO t1 VALUES (1, 1), (2, 2); 279 280--echo # Connection con1 281connect (con1, localhost, root); 282XA START 'a'; 283UPDATE t1 SET b= 3 WHERE a=1; 284 285--echo # Connection default 286connection default; 287XA START 'b'; 288UPDATE t1 SET b=4 WHERE a=2; 289--echo # Sending: 290--send UPDATE t1 SET b=5 WHERE a=1 291 292--echo # Connection con1 293connection con1; 294--sleep 1 295--error ER_LOCK_DEADLOCK 296UPDATE t1 SET b=6 WHERE a=2; 297# This used to trigger the assert 298--error ER_XA_RBDEADLOCK 299XA COMMIT 'a'; 300 301--echo # Connection default 302connection default; 303--echo # Reaping: UPDATE t1 SET b=5 WHERE a=1 304--reap 305XA END 'b'; 306XA ROLLBACK 'b'; 307DROP TABLE t1; 308disconnect con1; 309 310 311--echo # 312--echo # Bug#11766752 59936: multiple xa assertions - transactional 313--echo # statement fuzzer 314--echo # 315 316CREATE TABLE t1 (a INT) engine=InnoDB; 317XA START 'a'; 318INSERT INTO t1 VALUES (1); 319 320SAVEPOINT savep; 321 322XA END 'a'; 323--error ER_XAER_RMFAIL 324SELECT * FROM t1; 325--error ER_XAER_RMFAIL 326INSERT INTO t1 VALUES (2); 327--error ER_XAER_RMFAIL 328SAVEPOINT savep; 329--error ER_XAER_RMFAIL 330SET @a=(SELECT * FROM t1); 331 332XA PREPARE 'a'; 333--error ER_XAER_RMFAIL 334SELECT * FROM t1; # used to cause InnoDB assert 335--error ER_XAER_RMFAIL 336INSERT INTO t1 VALUES (2); # used to cause InnoDB assert 337--error ER_XAER_RMFAIL 338SAVEPOINT savep; 339--error ER_XAER_RMFAIL 340SET @a=(SELECT * FROM t1); # used to cause InnoDB assert 341--error ER_XAER_RMFAIL 342UPDATE t1 SET a=1 WHERE a=2; 343 344XA COMMIT 'a'; 345SELECT * FROM t1; 346DROP TABLE t1; 347 348 349--echo # 350--echo # Bug#12352846 - TRANS_XA_START(THD*): 351--echo # ASSERTION THD->TRANSACTION.XID_STATE.XID.IS_NULL() 352--echo # FAILED 353--echo # 354 355--disable_warnings 356DROP TABLE IF EXISTS t1, t2; 357--enable_warnings 358 359CREATE TABLE t1 (a INT) ENGINE=InnoDB; 360CREATE TABLE t2 (a INT) ENGINE=InnoDB; 361 362START TRANSACTION; 363INSERT INTO t1 VALUES (1); 364 365--echo # Connection con2 366--connect (con2,localhost,root) 367XA START 'xid1'; 368--echo # Sending: 369--send INSERT INTO t2 SELECT a FROM t1 370 371--echo # Connection default 372--connection default 373--echo # Waiting for until a transaction with 'INSERT SELECT' 374--echo # will be locked inside innodb subsystem. 375 376let $wait_condition= 377 SELECT COUNT(*) = 1 FROM information_schema.innodb_trx 378 WHERE trx_query = 'INSERT INTO t2 SELECT a FROM t1' AND 379 trx_operation_state = 'starting index read' AND 380 trx_state = 'LOCK WAIT'; 381--source include/wait_condition.inc 382 383DELETE FROM t1; 384 385--echo # Connection con2 386--connection con2 387--echo # Reaping: INSERT INTO t2 SELECT a FROM t1 388--error ER_LOCK_DEADLOCK 389--reap 390--error ER_XA_RBDEADLOCK 391XA COMMIT 'xid1'; 392 393--echo # Connection default 394connection default; 395 396COMMIT; 397 398--echo # Connection con2 399connection con2; 400# This caused the assert to be triggered 401XA START 'xid1'; 402 403XA END 'xid1'; 404XA PREPARE 'xid1'; 405XA ROLLBACK 'xid1'; 406 407--echo # Connection default 408connection default; 409DROP TABLE t1, t2; 410disconnect con2; 411 412 413# Wait till all disconnects are completed 414--source include/wait_until_count_sessions.inc 415 416--echo # 417--echo # Bug#14670465 PLEASE PRINT HUMAN READABLE, ESCAPED 418--echo # XID DATA IN XA RECOVER OUTPUT 419--echo # 420--echo # 421--echo # xa Recover command was not diplaying non printable ASCII 422--echo # characters in the XID previosuly. Now there is another column 423--echo # in the result set which is a Hex Encoded String of the XID. 424--echo # 425 426--echo # Check that XIDs which are not normally printable are displayed 427--echo # in readable format when CONVERT XID clause is used. 428xa start 0xABCDEF1234567890, 0x01, 0x02 ; 429xa end 0xABCDEF1234567890, 0x01, 0x02 ; 430xa prepare 0xABCDEF1234567890, 0x01, 0x02 ; 431xa recover convert xid; 432xa rollback 0xABCDEF1234567890, 0x01, 0x02 ; 433 434--echo # Check that XID which has only printable characters are displayed 435--echo # correctly without using of CONVERT XID clause 436xa start 0x4142434445, 0x46, 0x02 ; 437xa end 0x4142434445, 0x46, 0x02 ; 438xa prepare 0x4142434445, 0x46, 0x02 ; 439xa recover; 440xa rollback 0x4142434445, 0x46, 0x02 ; 441 442--disable_query_log 443call mtr.add_suppression("Found 1 prepared XA transactions"); 444--enable_query_log 445 446--echo # 447--echo # WL#7155: Test 1: check that if a thread of control terminates then 448--echo # the Resource Manager dissociate and rollback any associated 449--echo # transaction branch (see ref. 3.6 on page 18 of XA spec) 450--echo # 451 452connect (con1,localhost,root,,); 453 454CREATE TABLE t1 (a INT) ENGINE=INNODB; 455XA START 'xid1'; 456INSERT INTO t1 VALUES (1); 457XA END 'xid1'; 458 459disconnect con1; 460--source include/wait_until_disconnected.inc 461connection default; 462 463SELECT * FROM t1; 464DROP TABLE t1; 465 466--echo # 467--echo # WL#7155: Test 2: Check that if the Resource Manager is doing work outside 468--echo # any global transaction on behalf of the application, 469--echo # xa_start() returns XAER_OUTSIDE (see xa_start description on page 52 of XA spec) 470--echo # 471 472connect (con1,localhost,root,,); 473 474SET SESSION autocommit=0; 475START TRANSACTION; 476--error ER_XAER_OUTSIDE 477XA START 'xid1'; 478COMMIT; 479 480disconnect con1; 481--source include/wait_until_disconnected.inc 482connection default; 483 484--echo # 485--echo # WL#7155: Test 3: Check that the Resource Manager returns error 486--echo # if the Transaction Manager tries to resume non-existent transaction. 487--echo # 488 489--error ER_XAER_INVAL 490XA START 'xid1' RESUME; 491 492--echo # 493--echo # WL#7155: Test 4: Check that the Resource Manager returns ok 494--echo # if the Transaction Manager tries to resume transaction 495--echo # that has been ended before. 496--echo # 497 498connect (con1,localhost,root,,); 499 500XA START 'xid1'; 501XA END 'xid1'; 502XA START 'xid1' RESUME; 503XA END 'xid1'; 504 505disconnect con1; 506--source include/wait_until_disconnected.inc 507connection default; 508 509--echo # 510--echo # WL#7155: Test 5: Check that the Resource Manager returns error 511--echo # if the Transaction Manager ends some XA transaction and 512--echo # starts another one with RESUME clause right after that. 513--echo # 514 515connect (con1,localhost,root,,); 516 517XA START 'xid1'; 518XA END 'xid1'; 519--error ER_XAER_NOTA 520XA START 'xid2' RESUME; 521disconnect con1; 522--source include/wait_until_disconnected.inc 523connection default; 524 525--echo # 526--echo # WL#7155: Test 6: Check that the SUSPEND clause isn't supported for XA END. 527--echo # 528 529connect (con1,localhost,root,,); 530 531XA START 'xid1'; 532--error ER_XAER_INVAL 533XA END 'xid1' SUSPEND; 534XA END 'xid1'; 535 536disconnect con1; 537--source include/wait_until_disconnected.inc 538connection default; 539 540--echo # 541--echo # WL#7155: Test 7: Check that attempt to end non-existent XA transaction 542--echo # while another XA transaction is active leads to an error 543--echo # 544 545connect (con1,localhost,root,,); 546 547XA START 'xid1'; 548--error ER_XAER_NOTA 549XA END 'xid2'; 550XA END 'xid1'; 551 552disconnect con1; 553--source include/wait_until_disconnected.inc 554connection default; 555 556--echo # 557--echo # WL#7155: Test 8: Check that XA ROLLBACK can't be called for active XA transaction 558--echo # 559 560connect (con1,localhost,root,,); 561 562XA START 'xid1'; 563--error ER_XAER_RMFAIL 564XA ROLLBACK 'xid1'; 565XA END 'xid1'; 566 567disconnect con1; 568--source include/wait_until_disconnected.inc 569connection default; 570 571--echo # 572--echo # WL#7155: Test 9: Check that XA PREPARE returns error for unknown xid 573--echo # 574 575connect (con1,localhost,root,,); 576 577XA START 'xid1'; 578XA END 'xid1'; 579--error ER_XAER_NOTA 580XA PREPARE 'xid2'; 581 582disconnect con1; 583--source include/wait_until_disconnected.inc 584connection default; 585 586--echo # 587--echo # WL#7155: Test 10: Check that rollback of XA transaction with unknown xid 588--echo # leads to an error when there is other prepared XA transaction. 589--echo # 590 591XA START 'xid1'; 592XA END 'xid1'; 593XA PREPARE 'xid1'; 594--error ER_XAER_RMFAIL 595XA ROLLBACK 'xid2'; 596XA ROLLBACK 'xid1'; 597 598--echo # 599--echo # Bug#18107853 - XA LIST GETS CORRUPT, CRASH AND/OR HANG AND/OR ASSERTION 600--echo # 601 602--echo # Check that the server reports an error in case of too long input value of 603--echo # format ID overflows the type of unsigned long 604 605--error ER_PARSE_ERROR 606XA START '1', 0x01, 18446744073709551615; 607 608--echo # 609--echo # Bug#25364178 - XA PREPARE INCONSISTENT WITH XTRABACKUP 610--echo # 611 612--echo # Check XA state when lock_wait_timeout happens 613--echo # More tests added to flush_read_lock.test 614connect (con_tmp,localhost,root,,); 615set session lock_wait_timeout=1; 616create table asd (a int); 617xa start 'test1'; 618insert into asd values(1); 619xa end 'test1'; 620connection default; 621flush table with read lock; 622connection con_tmp; 623--echo # PREPARE error will do auto rollback. 624--ERROR ER_LOCK_WAIT_TIMEOUT 625xa prepare 'test1'; 626show errors; 627connection default; 628unlock tables; 629 630connection con_tmp; 631xa start 'test1'; 632insert into asd values(1); 633xa end 'test1'; 634xa prepare 'test1'; 635connection default; 636flush tables with read lock; 637connection con_tmp; 638--echo # LOCK error during ROLLBACK will not alter transaction state. 639--ERROR ER_LOCK_WAIT_TIMEOUT 640xa rollback 'test1'; 641show errors; 642xa recover; 643--echo # LOCK error during COMMIT will not alter transaction state. 644--ERROR ER_LOCK_WAIT_TIMEOUT 645xa commit 'test1'; 646show errors; 647xa recover; 648connection default; 649unlock tables; 650connection con_tmp; 651xa rollback 'test1'; 652xa recover; 653drop table asd; 654disconnect con_tmp; 655--source include/wait_until_disconnected.inc 656connection default; 657 658# Finish off disconnected survived transaction 659--echo There should be practically no error, but in theory 660--echo XAER_NOTA: Unknown XID can be returned if con1 disconnection 661--echo took for too long. 662--echo todo: consider to make this test dependent on P_S if 663--echo todo: such case will be ever registered. 664xa rollback 'testb',0x2030405060,11; 665 666# There should be no prepared transactions left. 667XA RECOVER; 668