Bug #87836 XA COMMIT/ROLLBACK rejected by non-autocommit session with no active transaction
Submitted: 22 Sep 2017 8:41 Modified: 5 Jan 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

[22 Sep 2017 8:41] Wei Zhao
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.
[22 Sep 2017 8:42] Wei Zhao
this patch fixes the bug

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

[22 Sep 2017 14:04] Umesh Shastry
Hello Wei Zhao,

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

Thanks,
Umesh
[22 Sep 2017 14:15] Umesh Shastry
-- 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
[5 Jan 17:36] Paul Dubois
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.
[12 Feb 20:26] Paul Dubois
Posted by developer:
 
Fixed in 8.0.5, not 9.0.0.