MySQL Bugs: #73033: Not possible commit XA transaction from different connection

Bug #73033	Not possible commit XA transaction from different connection
Submitted:	18 Jun 2014 10:02	Modified:	26 Jul 2014 14:09
Reporter:	Ondrej Chaloupka	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.31	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hi,

I'm hitting issue with committing prepared transaction from different connection that from that one where it was prepared. I'm not fully sure whether the reason of error on commit came from the fact that two connections are used. Please, consult test case at:
https://github.com/ochaloup/narayana/blob/4.17-mysql-recovery-test/ArjunaJTA/jta/tests/cla...

The transaction is prepared but on commit following exception is shown:
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_NOTA: Unknown XID
 at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:601)
 at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:584)
 at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.commit(MysqlXAConnection.java:560)
 at RecoverMysql.recoverMysql(RecoverMysql.java:151)
...

The same scenario works with other databases.

Other strange thing is behaviour of the mysql when XaResource.end(xid, XAResource.TMSUCCESS) is not called during prepare phase. Then the test ends at the preparation phase wit error:
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state
 at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:601)
 at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.dispatchCommand(MysqlXAConnection.java:584)
 at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.prepare(MysqlXAConnection.java:386)
 at RecoverMysql.recoverMysql(RecoverMysql.java:131)
This is not fully correct code, in fact, but other databases are able to handle that but MySQL isn't.

How to repeat:
You can run the test as reproducer like:
1) build narayana
1a) git clone -b 4.17-mysql-recovery-test https://github.com/ochaloup/narayana.git
1b) cd narayana
1c) ./build.sh clean install -Prelease,community,all -fae -Dmaven.test.skip.exec=true -Didlj-enabled=true
2) cd ArjunaJTA/jta
3) open pom.xml and redefine dependency of jdbc driver to point on correct jar location
4) run the test: mvn clean verify -Dtest=RecoverMysql
5) stdout is put under target/surefire-results/RecoverMysql-out.txt

Hi Ondrej,

Please report your MySQL server version. If you use 5.7.x then please be aware that c/J is affected by incompatibly change of XA RECOVER output format. We're waiting for server backfix of this problem.

Also, please refer MySQL XA documentation http://dev.mysql.com/doc/refman/5.0/en/xa.html, we have some restrictions that could cause your problems:

"Note that if an XA transaction is in the ACTIVE state, you cannot issue any statements that cause an implicit commit. That would violate the XA contract because you could not roll back the XA transaction. You will receive the following error if you try to execute such a statement:

ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed
when global transaction is in the ACTIVE state

Statements to which the preceding remark applies are listed at Section 13.3.3, “Statements That Cause an Implicit Commit” http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html. "

Hi,

my MySQL server version is 5.5.34.

I do understand that the usage which gives 'transaction is in the  ACTIVE state' error is not correct. Just my point was that other vendors are able to handle that.
I do understand that the create, drop and similar commands mean implicit commit but the failure came from insert command in my use case.

And if I understand right the limitation I do not use any of the cases mentioned here: http://dev.mysql.com/doc/refman/5.0/en/xa-restrictions.html
In case that my test case falls to that group, please, let me know.

Thanks
Ondra

For 5.5.34 server you could face some XA bugs:

http://bugs.mysql.com/search.php?search_for=&bug_type[]=Server%3A+XA&status[]=All&severity...

Please ensure that your scenario work with other clients and 5.5.34 server.

Hi,

thanks for the link.

I'm sorry but I didn't understand what you mean by term 'clien' in the context of your phrase: checking with other clients and 5.5.34 server? Do you mean different jdbc driver versions or what exactly?

Thank you for help.

I mean the first of all a command-line client, ...server-install-dir/bin/mysql

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".