include/only_with_option.inc [GLOBAL.xa_detach_on_prepare = 1]
# Make it easier to trace connections changes
# Simple rollback after prepare
CREATE TABLE t1 (a INT);
XA START 'test1';
INSERT INTO t1 VALUES (10);
XA END 'test1';
XA PREPARE 'test1';
XA ROLLBACK 'test1';
SELECT * FROM t1;
a
# Verify that new XA trx cannot be started before XA PREPARE
XA START 'test2';
XA START 'test-bad';
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
INSERT INTO t1 VALUES (20);
XA PREPARE 'test2';
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
XA END 'test2';
XA PREPARE 'test2';
XA COMMIT 'test2';
SELECT * FROM t1;
a
20
# Verify that COMMIT, BEGIN and CREATE TABLE is disallowed inside XA trx.
XA START 'testa','testb';
INSERT INTO t1 VALUES (30);
COMMIT;
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
XA END 'testa','testb';
BEGIN;
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
CREATE TABLE t2 (a INT);
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
# Test parallel XA trx on a different connection
connect  con1,localhost,root,,;
XA START 'testa','testb';
ERROR XAE08: XAER_DUPID: The XID already exists
XA START 'testa','testb', 123;
ERROR XAE08: XAER_DUPID: The XID already exists
XA START 0x7465737462, 0x2030405060, 0xb;
INSERT INTO t1 VALUES (40);
XA END 'testb',' 0@P`',11;
XA PREPARE 'testb',0x2030405060,11;
# Show that it is possible to start new transaction after XA PREPARE
START TRANSACTION;
INSERT INTO t1 VALUES (42);
COMMIT WORK;
XA RECOVER;
formatID	gtrid_length	bqual_length	data
11	5	5	testb 0@P`
connection default;
XA PREPARE 'testa','testb';
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	5	5	testatestb
11	5	5	testb 0@P`
# Can commit prepared XA trx from other connection
XA COMMIT 'testb',0x2030405060,11;
XA ROLLBACK 'testa','testb';
# XA trx from other connection not found (already committed)
XA COMMIT 'testb',0x2030405060,11;
ERROR XAE04: XAER_NOTA: Unknown XID
SELECT * FROM t1;
a
20
40
42
DROP TABLE t1;
connection con1;
disconnect con1;
connection default;
CREATE TABLE t1(a INT, b INT, c VARCHAR(20), PRIMARY KEY(a));
INSERT INTO t1 VALUES (1, 1, 'a');
INSERT INTO t1 VALUES (2, 2, 'b');
connect  con1,localhost,root,,;
connect  con2,localhost,root,,;
connection con1;
XA START 'a','b';
UPDATE t1 SET c = 'aa' WHERE a = 1;
connection con2;
XA START 'a','c';
UPDATE t1 SET c = 'bb' WHERE a = 2;
connection con1;
UPDATE t1 SET c = 'bb' WHERE a = 2;
connection con2;
UPDATE t1 SET c = 'aa' WHERE a = 1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
SELECT COUNT(*) FROM t1;
ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
XA END 'a','c';
ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
XA ROLLBACK 'a','c';
disconnect con2;
connect  con3,localhost,root,,;
connection con3;
XA START 'a','c';
connection con1;
disconnect con1;
disconnect con3;
connection default;
DROP TABLE t1;
#
# BUG#51342 - more xid crashing
#
CREATE TABLE t1(a INT);
XA START 'x';
SET SESSION autocommit=0;
INSERT INTO t1 VALUES(1);
SET SESSION autocommit=1;
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
SELECT @@autocommit;
@@autocommit
0
INSERT INTO t1 VALUES(1);
XA END 'x';
XA COMMIT 'x' ONE PHASE;
DROP TABLE t1;
SET SESSION autocommit=1;
XA START 'a';
XA END 'a';
XA ROLLBACK 'a';
XA START 'a';
XA END 'a';
XA ROLLBACK 'a';
XA START 'a';
XA END 'a';
XA PREPARE 'a';
XA COMMIT 'a';
XA START 'a';
XA END 'a';
XA PREPARE 'a';
XA COMMIT 'a';
CREATE TABLE t1(a INT, KEY(a));
INSERT INTO t1 VALUES(1),(2);
connect con1,localhost,root,,;
BEGIN;
UPDATE t1 SET a=3 WHERE a=1;
connection default;
BEGIN;
UPDATE t1 SET a=4 WHERE a=2;
connection con1;
UPDATE t1 SET a=5 WHERE a=2;
connection default;
UPDATE t1 SET a=5 WHERE a=1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
ROLLBACK;
connection con1;
ROLLBACK;
BEGIN;
UPDATE t1 SET a=3 WHERE a=1;
connection default;
XA START 'xid1';
UPDATE t1 SET a=4 WHERE a=2;
connection con1;
UPDATE t1 SET a=5 WHERE a=2;
connection default;
UPDATE t1 SET a=5 WHERE a=1;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
XA END 'xid1';
ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
XA ROLLBACK 'xid1';
XA START 'xid1';
XA END 'xid1';
XA ROLLBACK 'xid1';
connection con1;
disconnect con1;
connection default;
DROP TABLE t1;
#
# Bug#56448 Assertion failed: ! is_set() with second xa end
#
XA START 'x';
XA END 'x';
XA END 'x';
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
XA PREPARE 'x';
XA PREPARE 'x';
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state
XA ROLLBACK 'x';
#
# Bug#59986 Assert in Diagnostics_area::set_ok_status() for XA COMMIT
#
CREATE TABLE t1(a INT, b INT, PRIMARY KEY(a));
INSERT INTO t1 VALUES (1, 1), (2, 2);
connect  con1, localhost, root;
XA START 'a';
UPDATE t1 SET b= 3 WHERE a=1;
connection default;
XA START 'b';
UPDATE t1 SET b=4 WHERE a=2;
# Sending:
UPDATE t1 SET b=5 WHERE a=1;
connection con1;
UPDATE t1 SET b=6 WHERE a=2;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
XA COMMIT 'a';
ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
connection default;
# Reaping: UPDATE t1 SET b=5 WHERE a=1
XA END 'b';
XA ROLLBACK 'b';
DROP TABLE t1;
disconnect con1;
#
# Bug#11766752 59936: multiple xa assertions - transactional
#              statement fuzzer
#
CREATE TABLE t1 (a INT);
XA START 'a';
INSERT INTO t1 VALUES (1);
SAVEPOINT savep;
XA END 'a';
SELECT * FROM t1;
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
INSERT INTO t1 VALUES (2);
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
SAVEPOINT savep;
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
SET @a=(SELECT * FROM t1);
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
XA PREPARE 'a';
SELECT * FROM t1;
a
INSERT INTO t1 VALUES (2);
SAVEPOINT savep;
XA COMMIT 'a';
SELECT * FROM t1;
a
1
2
SET @a=(SELECT * FROM t1);
ERROR 21000: Subquery returns more than 1 row
UPDATE t1 SET a=1 WHERE a=2;
DROP TABLE t1;
#
# Bug#12352846 - TRANS_XA_START(THD*):
#                ASSERTION THD->TRANSACTION.XID_STATE.XID.IS_NULL()
#                FAILED
#
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
INSERT INTO t2 VALUES (1);
START TRANSACTION;
DELETE FROM t1;
connect  con2,localhost,root;
XA START 'xid1';
# Sending:
SELECT a FROM t2 WHERE a=1 FOR SHARE;
a
1
SELECT a FROM t1 WHERE a=1 FOR UPDATE;
connection default;
# Waiting for until a transaction with 'SELECT...FOR UPDATE'
# will be locked inside innodb subsystem.
SELECT a FROM t2 WHERE a=1 FOR UPDATE;
a
1
connection con2;
# Reaping: SELECT a FROM t1 WHERE a=1 FOR UPDATE
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
XA COMMIT 'xid1';
ERROR XA102: XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected
connection default;
COMMIT;
connection con2;
XA START 'xid1';
XA END 'xid1';
XA PREPARE 'xid1';
XA ROLLBACK 'xid1';
connection default;
DROP TABLE t1;
DROP TABLE t2;
disconnect con2;
#
# Bug#14670465 PLEASE PRINT HUMAN READABLE, ESCAPED
#              XID DATA IN XA RECOVER OUTPUT
#
#
# xa Recover command was not diplaying non printable ASCII
# characters in the XID previosuly. Now there is another column
# in the result set which is a Hex Encoded String of the XID.
#
# Check that XIDs which are not normally printable are displayed
# in readable format when CONVERT XID clause is used.
XA START 0xABCDEF1234567890, 0x01, 0x02 ;
XA END 0xABCDEF1234567890, 0x01, 0x02 ;
XA PREPARE 0xABCDEF1234567890, 0x01, 0x02 ;
XA RECOVER convert xid;
formatID	gtrid_length	bqual_length	data
2	8	1	0xABCDEF123456789001
XA ROLLBACK 0xABCDEF1234567890, 0x01, 0x02 ;
# Check that XID which has only printable characters are displayed
# correctly without using of CONVERT XID clause
XA START 0x4142434445, 0x46, 0x02 ;
XA END 0x4142434445, 0x46, 0x02 ;
XA PREPARE 0x4142434445, 0x46, 0x02 ;
XA RECOVER;
formatID	gtrid_length	bqual_length	data
2	5	1	ABCDEF
XA ROLLBACK 0x4142434445, 0x46, 0x02 ;
#
# WL#7155: Test 1: check that if a thread of control terminates then
# the Resource Manager dissociate and rollback any associated
# transaction branch (see ref. 3.6 on page 18 of XA spec)
#
connect  con1,localhost,root,,;
CREATE TABLE t1 (a INT) ENGINE=INNODB;
XA START 'xid1';
INSERT INTO t1 VALUES (1);
XA END 'xid1';
disconnect con1;
connection default;
SELECT * FROM t1;
a
DROP TABLE t1;
#
# WL#7155: Test 2: Check that if the Resource Manager is doing work outside
# any global transaction on behalf of the application,
# xa_start() returns XAER_OUTSIDE (see xa_start description on page 52 of XA spec)
#
connect  con1,localhost,root,,;
SET SESSION autocommit=0;
START TRANSACTION;
XA START 'xid1';
ERROR XAE09: XAER_OUTSIDE: Some work is done outside global transaction
COMMIT;
disconnect con1;
connection default;
#
# WL#7155: Test 3: Check that the Resource Manager returns error
# if the Transaction Manager tries to resume non-existent transaction.
#
XA START 'xid1' RESUME;
ERROR XAE05: XAER_INVAL: Invalid arguments (or unsupported command)
#
# WL#7155: Test 4: Check that the Resource Manager returns ok
# if the Transaction Manager tries to resume transaction
# that has been ended before.
#
connect  con1,localhost,root,,;
XA START 'xid1';
XA END 'xid1';
XA START 'xid1' RESUME;
XA END 'xid1';
disconnect con1;
connection default;
#
# WL#7155: Test 5: Check that the Resource Manager returns error
# if the Transaction Manager ends some XA transaction and
# starts another one with RESUME clause right after that.
#
connect  con1,localhost,root,,;
XA START 'xid1';
XA END 'xid1';
XA START 'xid2' RESUME;
ERROR XAE04: XAER_NOTA: Unknown XID
disconnect con1;
connection default;
#
# WL#7155: Test 6: Check that the SUSPEND clause isn't supported for XA END.
#
connect  con1,localhost,root,,;
XA START 'xid1';
XA END 'xid1' SUSPEND;
ERROR XAE05: XAER_INVAL: Invalid arguments (or unsupported command)
XA END 'xid1';
disconnect con1;
connection default;
#
# WL#7155: Test 7: Check that attempt to end non-existent XA transaction
# while another XA transaction is active leads to an error
#
connect  con1,localhost,root,,;
XA START 'xid1';
XA END 'xid2';
ERROR XAE04: XAER_NOTA: Unknown XID
XA END 'xid1';
disconnect con1;
connection default;
#
# WL#7155: Test 8: Check that XA ROLLBACK can't be called for active XA transaction
#
connect  con1,localhost,root,,;
XA START 'xid1';
XA ROLLBACK 'xid1';
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
XA END 'xid1';
disconnect con1;
connection default;
#
# WL#7155: Test 9: Check that XA PREPARE returns error for unknown xid
#
connect  con1,localhost,root,,;
XA START 'xid1';
XA END 'xid1';
XA PREPARE 'xid2';
ERROR XAE04: XAER_NOTA: Unknown XID
disconnect con1;
connection default;
#
# WL#7155: Test 10: Check that rollback of XA transaction with unknown xid
# leads to an error when there is other prepared XA transaction.
#
XA START 'xid1';
XA END 'xid1';
XA PREPARE 'xid1';
XA ROLLBACK 'xid2';
ERROR XAE04: XAER_NOTA: Unknown XID
XA ROLLBACK 'xid1';
#
# Behavior of XA transaction when autocommit = OFF
#
CREATE TABLE t1(i INT);
INSERT INTO t1 VALUES (1);
SET SESSION autocommit = OFF;
XA START 'xid3';
INSERT INTO t1 VALUES (2);
XA END 'xid3';
XA PREPARE 'xid3';
# XA COMMIT/ROLLBACK is ok as we are not in active multi-statement
# transaction immediately after XA PRPEARE
XA ROLLBACK 'xid3';
XA START 'xid4';
INSERT INTO t1 VALUES (3);
XA END 'xid4';
XA PREPARE 'xid4';
# This starts another multi-statement transaction
INSERT INTO t1 VALUES (4);
# Which causes XA COMMIT/ROLLBACK to fail
XA COMMIT 'xid4';
ERROR XAE07: XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state
ROLLBACK WORK;
# After ending multi-statement transaction, we can again commit
# XA transaction
XA COMMIT 'xid4';
SELECT * FROM t1 ORDER BY i;
i
1
3
SET SESSION autocommit = DEFAULT;
DROP TABLE t1;
#
# Bug#18107853 - XA LIST GETS CORRUPT, CRASH AND/OR HANG AND/OR ASSERTION
#
# Check that the server reports an error in case of too long input value of
# format ID overflows the type of unsigned long
XA START '1', 0x01, 18446744073709551615;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '18446744073709551615' at line 1
#
# Bug#25364178 - XA PREPARE INCONSISTENT WITH XTRABACKUP
#
# Check XA state when lock_wait_timeout happens
# More tests added to flush_read_lock.test
connect  con_tmp,localhost,root,,;
SET SESSION lock_wait_timeout=1;
CREATE TABLE asd (a INT);
XA START 'test1';
INSERT INTO asd VALUES (1);
XA END 'test1';
connection default;
FLUSH TABLE WITH READ LOCK;
connection con_tmp;
# PREPARE error will do auto rollback.
XA PREPARE 'test1';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW ERRORS;
Level	Code	Message
Error	1205	Lock wait timeout exceeded; try restarting transaction
Error	1402	XA_RBROLLBACK: Transaction branch was rolled back
connection default;
UNLOCK TABLES;
connection con_tmp;
XA START 'test1';
INSERT INTO asd VALUES (1);
XA END 'test1';
XA PREPARE 'test1';
connection default;
FLUSH TABLES WITH READ LOCK;
connection con_tmp;
# LOCK error during ROLLBACK will not alter transaction state.
XA ROLLBACK 'test1';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW ERRORS;
Level	Code	Message
Error	1205	Lock wait timeout exceeded; try restarting transaction
Error	3197	The resource manager is not able to commit the transaction branch at this time. Please retry later.
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	5	0	test1
# LOCK error during COMMIT will not alter transaction state.
XA COMMIT 'test1';
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SHOW ERRORS;
Level	Code	Message
Error	1205	Lock wait timeout exceeded; try restarting transaction
Error	3197	The resource manager is not able to commit the transaction branch at this time. Please retry later.
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	5	0	test1
connection default;
UNLOCK TABLES;
connection con_tmp;
XA ROLLBACK 'test1';
XA RECOVER;
formatID	gtrid_length	bqual_length	data
DROP TABLE asd;
disconnect con_tmp;
connection default;
There should be practically no error, but in theory
XAER_NOTA: Unknown XID can be returned if con1 disconnection
took for too long.
todo: consider to make this test dependent on P_S if
todo: such case will be ever registered.
XA RECOVER;
formatID	gtrid_length	bqual_length	data
#
# WL#7194 -- Define and implement authorization model to manage XA-transactions
#
CREATE USER u1;
GRANT XA_RECOVER_ADMIN ON *.* TO u1;
CREATE USER u2;
XA START 'xid1';
XA END 'xid1';
XA PREPARE 'xid1';
# Connect as user u1
# Since the privilege XA_RECOVER_ADMIN was granted to the user u1
# it is allowed to execute the statement XA RECOVER to get a list of
# xids for prepared XA transactions.
connect con1,localhost,u1,'',;
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	4	0	xid1
# Connect as user u2
# The privilege XA_RECOVER_ADMIN wasn't granted to the user u2.
# It leads to issuing the error ER_XAER_RMERR on attempt to run
# the statement XA RECOVER.
connect con2,localhost,u2,'',;
XA RECOVER;
ERROR XAE03: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
SHOW WARNINGS;
Level	Code	Message
Error	1401	XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
Error	1227	Access denied; you need (at least one of) the XA_RECOVER_ADMIN privilege(s) for this operation
connection con1;
disconnect con1;
connection default;
# The default connection was established on behalf the user root@localhost
# who has the XA_RECOVER_ADMIN privilege assigned by default.
# So for the user root@localhost the statement XA RECOVER
# can be executed successfully.
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	4	0	xid1
XA COMMIT 'xid1';
# Connect as the user u2 who wasn't granted privilege XA_RECOVER_ADMIN.
# Initiates a new XA transaction on behalf the user u2 and shows that
# call XA RECOVER made by the user u2 is failed with error ER_XAER_RMERR.
connection con2;
XA START 'xid2';
XA END 'xid2';
XA PREPARE 'xid2';
XA RECOVER;
ERROR XAE03: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
SHOW WARNINGS;
Level	Code	Message
Error	1401	XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
Error	1227	Access denied; you need (at least one of) the XA_RECOVER_ADMIN privilege(s) for this operation
# Although the user u2 can't get a list of prepared XA transaction this user
# can finalize a prepared XA transaction knowing its XID value.
XA COMMIT 'xid2';
disconnect con2;
connection default;
DROP USER u1, u2;
# Check that a user who has the privilege SUPER and hasn't the privilege
# XA_RECOVER_ADMIN isn't allowed to run the statement XA RECOVER
CREATE USER u1;
GRANT SUPER ON *.* TO u1;
Warnings:
Warning	1287	The SUPER privilege identifier is deprecated
SHOW GRANTS FOR u1;
Grants for u1@%
GRANT SUPER ON *.* TO `u1`@`%`
connect con1,localhost,u1,'',;
XA START 'xid1';
XA END 'xid1';
XA PREPARE 'xid1';
XA RECOVER;
ERROR XAE03: XAER_RMERR: Fatal error occurred in the transaction branch - check your data for consistency
XA COMMIT 'xid1';
disconnect con1;
connection default;
DROP USER u1;
# End of tests fro WL#7194
#
# Bug #26848877 -- XA COMMIT/ROLLBACK REJECTED BY NON-AUTOCOMMIT SESSION
#                  WITH NO ACTIVE TRANSACTION
#
connect con1,localhost,root,'',;
# Check that XA COMMIT finalizes XA transaction branch in case
# XA transaction was prepared successfully and after session reconnect
# a user turned off autocommit before running XA COMMIT
XA START 'xid1';
XA END 'xid1';
XA PREPARE 'xid1';
disconnect con1;
connection default;
SET autocommit = 0;
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	4	0	xid1
XA COMMIT 'xid1';
# Check that XA ROLLBACK finalizes XA transaction branch in case
# XA transaction was prepared successfully and after reconnect
# a user turned off autocommit before running XA ROLLBACK
connect con1,localhost,root,'',;
XA START 'xid1';
XA END 'xid1';
XA PREPARE 'xid1';
disconnect con1;
connection default;
SET autocommit = 0;
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	4	0	xid1
XA ROLLBACK 'xid1';
SET autocommit = DEFAULT;
#
# BUG 31030205 - XA PREPARED TXN WILL STAY AS "RECOVERED TRX" IF ROLLBACK
#                XID HAS WRONG FORMATID
#
#
connect con1,localhost,root,'',;
CREATE TABLE t1 (a INT) ENGINE=INNODB;
XA START X'1A2B3C4D5E6F',X'F6E5D4C3B2A1',12345;
INSERT INTO t1 VALUES (1);
XA END X'1A2B3C4D5E6F',X'F6E5D4C3B2A1',12345;
XA PREPARE X'1A2B3C4D5E6F',X'F6E5D4C3B2A1',12345;
disconnect con1;
connect con2, localhost, root, '',;
XA ROLLBACK X'1A2B3C4D5E6F',X'F6E5D4C3B2A1',2;
ERROR XAE04: XAER_NOTA: Unknown XID
XA RECOVER CONVERT XID;
formatID	gtrid_length	bqual_length	data
12345	6	6	0x1A2B3C4D5E6FF6E5D4C3B2A1
XA ROLLBACK X'1A2B3C4D5E6F',X'F6E5D4C3B2A1',12345;
DROP TABLE t1;
disconnect con2;
connection default;
# Verify that temporary tables are rejected
CREATE TEMPORARY TABLE temp1(i INT);
XA START 'xa1';
INSERT INTO temp1 VALUES (1),(2),(3);
ERROR HY000: XA: Temporary tables cannot be accessed inside XA transactions when xa_detach_on_prepare=ON
XA END 'xa1';
XA ROLLBACK 'xa1';
XA START 'xa2';
CREATE TEMPORARY TABLE temp2(i INT);
ERROR HY000: XA: Temporary tables cannot be accessed inside XA transactions when xa_detach_on_prepare=ON
XA END 'xa2';
XA ROLLBACK 'xa2';
XA START 'xa3';
SELECT * FROM temp1;
ERROR HY000: XA: Temporary tables cannot be accessed inside XA transactions when xa_detach_on_prepare=ON
XA END 'xa3';
XA ROLLBACK 'xa3';
# Verify that DROP TEMPORARY TABLE is rejected in XA transaction
INSERT INTO temp1 VALUES (1),(2),(3);
XA START 'xa3_';
DROP TEMPORARY TABLE temp1;
ERROR HY000: XA: Temporary tables cannot be accessed inside XA transactions when xa_detach_on_prepare=ON
XA END 'xa3_';
XA ROLLBACK 'xa3_';
DROP TEMPORARY TABLE temp1;
XA START 'xa4';
SET SESSION xa_detach_on_prepare=OFF;
ERROR HY000: The system variable xa_detach_on_prepare cannot be set when there is an ongoing transaction.
XA END 'xa4';
XA ROLLBACK 'xa4';
CREATE TABLE t1(c1 VARCHAR(128));
# Testing XA COMMIT/ROLLBACK on the same connection
# Rolling back basic XA transaction
connect xc1,localhost,root,,;
XA START 'xa1';
INSERT INTO t1 VALUES ('Inserted by xa1');
XA END 'xa1';
XA PREPARE 'xa1';
XA ROLLBACK 'xa1';
SELECT * FROM t1;
c1
disconnect xc1;
connection default;
# Committing basic XA transaction
connect xc2,localhost,root,,;
XA START 'xa2';
INSERT INTO t1 VALUES ('Inserted by xa2');
XA END 'xa2';
XA PREPARE 'xa2';
XA COMMIT 'xa2';
disconnect xc2;
connection default;
# SELECT after prepare
connect xc5,localhost,root,,;
XA START 'xa5';
INSERT INTO t1 VALUES ('Inserted by xa5');
XA END 'xa5';
XA PREPARE 'xa5';
SELECT * FROM t1;
c1
Inserted by xa2
XA COMMIT 'xa5';
disconnect xc5;
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
# DML after prepare
connect xc6,localhost,root,,;
XA START 'xa6';
INSERT INTO t1 VALUES ('Inserted by xa6');
XA END 'xa6';
XA PREPARE 'xa6';
INSERT INTO t1 VALUES ('Inserted after prepare');
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted after prepare
XA COMMIT 'xa6';
disconnect xc6;
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted by xa6
Inserted after prepare
# DDL after prepare
connect xc7,localhost,root,,;
XA START 'xa7';
INSERT INTO t1 VALUES ('Inserted by xa7');
XA END 'xa7';
XA PREPARE 'xa7';
CREATE TABLE t2(i INT);
INSERT INTO t2 VALUES (1), (2), (3);
ALTER TABLE t2 ADD COLUMN j INT;
INSERT INTO t2 VALUES (4,4),(5,5);
XA COMMIT 'xa7';
disconnect xc7;
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted by xa6
Inserted after prepare
Inserted by xa7
SELECT * FROM t2;
i	j
1	NULL
2	NULL
3	NULL
4	4
5	5
DROP TABLE t2;
# BEGIN WORK after prepare
connect xc8,localhost,root,,;
XA START 'xa8';
INSERT INTO t1 VALUES ('Inserted by xa8');
XA END 'xa8';
XA PREPARE 'xa8';
SET SESSION autocommit = OFF;
BEGIN WORK;
INSERT INTO t1 VALUES ('Inserted by normal transaction');
ROLLBACK WORK;
XA COMMIT 'xa8';
disconnect xc8;
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted by xa6
Inserted after prepare
Inserted by xa7
Inserted by xa8
# SAVEPOINT after prepare
connect xc10,localhost,root,,;
SET SESSION autocommit=OFF;
XA START 'xa10';
INSERT INTO t1 VALUES ('Inserted by xa10');
XA END 'xa10';
XA PREPARE 'xa10';
SAVEPOINT s1;
INSERT INTO t1 VALUES ('Inserted in savepoint s1');
ROLLBACK TO s1;
INSERT INTO t1 VALUES ('Inserted by normal transaction');
COMMIT WORK;
XA COMMIT 'xa10';
disconnect xc10;
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted by xa6
Inserted after prepare
Inserted by xa7
Inserted by xa8
Inserted by xa10
Inserted by normal transaction
# HANDLER read after prepare
connect xc11,localhost,root,,;
XA START 'xa11';
INSERT INTO t1 VALUES ('Inserted by xa11');
XA END 'xa11';
XA PREPARE 'xa11';
HANDLER t1 OPEN;
HANDLER t1 READ FIRST;
c1
Inserted by xa2
HANDLER t1 close;
XA COMMIT 'xa11';
disconnect xc11;
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted by xa6
Inserted after prepare
Inserted by xa7
Inserted by xa8
Inserted by xa10
Inserted by normal transaction
Inserted by xa11
### Test XA COMMIT/ROLLBACK from other connection
# Rolling back basic XA transaction from other connection
connect xc3,localhost,root,,;
XA START 'xa3';
INSERT INTO t1 VALUES ('Inserted by xa3');
XA END 'xa3';
XA PREPARE 'xa3';
SELECT object_type, object_name, lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks WHERE object_schema = 'test';
object_type	object_name	lock_type	lock_duration	lock_status
TABLE	t1	SHARED_WRITE	TRANSACTION	GRANTED
connect xc3r,localhost,root,,;
XA ROLLBACK 'xa3';
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted by xa6
Inserted after prepare
Inserted by xa7
Inserted by xa8
Inserted by xa10
Inserted by normal transaction
Inserted by xa11
SELECT object_type, object_name, lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks WHERE object_schema = 'test';
object_type	object_name	lock_type	lock_duration	lock_status
connection xc3r;
disconnect xc3r;
connection xc3;
disconnect xc3;
connection default;
# Committing basic XA transaction from other connection
connect xc4,localhost,root,,;
XA START 'xa4';
INSERT INTO t1 VALUES ('Inserted by xa4');
XA END 'xa4';
XA PREPARE 'xa4';
SELECT object_type, object_name, lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks WHERE object_schema = 'test';
object_type	object_name	lock_type	lock_duration	lock_status
TABLE	t1	SHARED_WRITE	TRANSACTION	GRANTED
connect xc4c,localhost,root,,;
XA COMMIT 'xa4';
connection default;
SELECT * FROM t1;
c1
Inserted by xa2
Inserted by xa5
Inserted by xa6
Inserted after prepare
Inserted by xa7
Inserted by xa8
Inserted by xa10
Inserted by normal transaction
Inserted by xa11
Inserted by xa4
SELECT object_type, object_name, lock_type, lock_duration, lock_status
FROM performance_schema.metadata_locks WHERE object_schema = 'test';
object_type	object_name	lock_type	lock_duration	lock_status
connection xc4c;
disconnect xc4c;
connection xc4;
disconnect xc4;
connection default;
DROP TABLE t1;
# Select-only transaction
CREATE TABLE t1(d VARCHAR(128));
INSERT INTO t1 VALUES ('Row 1'), ('Row 2');
# connect(con3)
connect con3,localhost,root,,;
XA START 'xa3';
SELECT * FROM t1;
d
Row 1
Row 2
XA END 'xa3';
XA PREPARE 'xa3';
XA START 'xa3x';
SELECT d FROM t1;
d
Row 1
Row 2
XA END 'xa3x';
XA PREPARE 'xa3x';
# disconnect(con3)
disconnect con3;
# connection default
connection default;
XA ROLLBACK 'xa3';
XA COMMIT 'xa3x';
DROP TABLE t1;
# Check that the necessary privileges are required for modifying the sysvar
CREATE USER xau;
connect con4,localhost,xau,,;
SET xa_detach_on_prepare = OFF;
ERROR 42000: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
disconnect con4;
connection default;
GRANT GROUP_REPLICATION_ADMIN ON *.* TO xau;
connect con5,localhost,xau,,;
SET xa_detach_on_prepare = OFF;
ERROR 42000: Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
disconnect con5;
connection default;
GRANT SESSION_VARIABLES_ADMIN ON *.* TO xau;
connect con6,localhost,xau,,;
SET xa_detach_on_prepare = OFF;
disconnect con6;
connection default;
DROP USER xau;
# Check that the set of XA transactions after restart
# Normal modifying transaction
CREATE TABLE t7(i INT);
connect con7,localhost,root,,;
XA START 'xa7';
INSERT INTO t7 VALUES (1),(2),(3);
XA END 'xa7';
XA PREPARE 'xa7';
disconnect con7;
connection default;
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	3	0	xa7
# restart:
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	3	0	xa7
XA ROLLBACK 'xa7';
SELECT * FROM t7;
i
DROP TABLE t7;
# Pure select transaction (optimized away in Innodb)
# This optimization is non-std and may change in the future.
CREATE TABLE t8(i INT);
INSERT INTO t8 VALUES (1),(2),(3);
connect con8,localhost,root,,;
XA START 'xa8';
SELECT * FROM t8;
i
1
2
3
XA END 'xa8';
XA PREPARE 'xa8';
disconnect con8;
connection default;
XA RECOVER;
formatID	gtrid_length	bqual_length	data
1	3	0	xa8
# restart:
XA RECOVER;
formatID	gtrid_length	bqual_length	data
XA ROLLBACK 'xa8';
ERROR XAE04: XAER_NOTA: Unknown XID
DROP TABLE t8;