MySQL Bugs: #87836: XA COMMIT/ROLLBACK rejected by non-autocommit session with no active transaction

Bug #87836	XA COMMIT/ROLLBACK rejected by non-autocommit session with no active transaction
Submitted:	22 Sep 2017 8:41	Modified:	5 Jan 2018 17:36
Reporter:	Wei Zhao (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: XA transactions	Severity:	S2 (Serious)
Version:	mysql-5.7.17, 5.7.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When I start and prepare an XA transaction branch in a session like this:

XA START 'abc';
insert into t1 values(123);
XA END 'abc';
XA PREPARE 'abc';

Then I quit above connection and reconnect to the mysql server, and either commit or abort the txn branch:

SET SESSION AUTOCOMMIT=0;
XA COMMIT 'abc';   --- or XA ROLLBACK 'abc';

It fails with error:
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state

When I see the code in
bool Sql_cmd_xa_commit::trans_xa_commit/(THD *thd)
and
bool Sql_cmd_xa_rollback::trans_xa_rollback(THD *thd)

I found the code snippet which rejects xa commit/rollback operations if current session is in multi transaction mode(there is NO active transaction in the session). 

This restriction makes it only possible to do XA COMMIT/ROLLBACK to end another prepared XA transaction branch when the current session is in AUTOCOMMIT mode, in non-AUTOCOMMIT mode, one can't do so even if there is no active transaction at all. Such a restriction is not necessary and inconvenient in my opinion.

So I think this is a bug, and I made a fix(as attached) and it works alright for me.

How to repeat:
As above.

Suggested fix:
See my patch.

this patch fixes the bug

Attachment: xa-multi-stmt-txn.diff (application/octet-stream, text), 901 bytes.

Hello Wei Zhao,

Thank you for the report and supplying patch along with the report.

Thanks,
Umesh

-- 5.7.19

[umshastr@hod03]/export/umesh/server/binaries/Trunk/87827/mysql-5.7.19:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock                                                       Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

root@localhost [(none)]> use test
Database changed
root@localhost [test]> create table t1(id int not null primary key);
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> XA START 'abc';
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> insert into t1 values(123);
Query OK, 1 row affected (0.01 sec)

root@localhost [test]> XA END 'abc';
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> XA PREPARE 'abc';
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/Trunk/87827/mysql-5.7.19:  bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> use test
Database changed
root@localhost [test]> SET SESSION AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [test]> XA COMMIT 'abc';  
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state

Posted by developer:
 
Fixed in 9.0.0.

With auto-commit disabled and an XA transaction in PREPARED state,
attempts to execute XA COMMIT or XA ROLLBACK failed.

Posted by developer:
 
Fixed in 8.0.5, not 9.0.0.