Bug #17343 XAConnection doesn't allow second XA START
Submitted: 13 Feb 2006 17:23 Modified: 28 Feb 2007 22:25
Reporter: Sergey Vladimirov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0.19-BK OS:Linux (Linux, Windows XP)
Assigned to:
Triage: Triaged: D2 (Serious)

[13 Feb 2006 17:23] Sergey Vladimirov
Description:
XAConnection doesn't allow second XA START-XA END pair to be executed in the same transaction over the same XAConnection. There is a part of log:

Mon Feb 13 12:13:03 EST 2006 DEBUG: Executing XA statement: XA START 0x01ddc8e6d7672a37331d6c307be08547dd20202020202020202020202020202020202020202020202020202020200000000425919aa39801,0x01ddc8e6d7672a37331d6c307be08547dd2020202020202020202020202020202020202020202020202020202020000000000000000000000000000000000000,0xbb14
...
Mon Feb 13 12:13:03 EST 2006 DEBUG: Executing XA statement: XA END 0x01ddc8e6d7672a37331d6c307be08547dd20202020202020202020202020202020202020202020202020202020200000000425919aa39801,0x01ddc8e6d7672a37331d6c307be08547dd2020202020202020202020202020202020202020202020202020202020000000000000000000000000000000000000,0xbb14
Mon Feb 13 12:13:03 EST 2006 DEBUG: Executing XA statement: XA COMMIT 0x01ddc8e6d7672a37331d6c307be08547dd20202020202020202020202020202020202020202020202020202020200000000425919aa39801,0x01ddc8e6d7672a37331d6c307be08547dd2020202020202020202020202020202020202020202020202020202020000000000000000000000000000000000000,0xbb14 ONE PHASE

it is ok. Start -> End -> Commit. But in the next part of log exception occured:

Mon Feb 13 12:13:08 EST 2006 DEBUG: Executing XA statement: XA START 0x01ddc8e6d7672a37331d6c307be08547dd20202020202020202020202020202020202020202020202020202020200000000425919ad87c03,0x01ddc8e6d7672a37331d6c307be08547dd2020202020202020202020202020202020202020202020202020202020000000000000000000000000000000000000,0xbb14
Mon Feb 13 12:13:08 EST 2006 DEBUG: Executing XA statement: XA END 0x01ddc8e6d7672a37331d6c307be08547dd20202020202020202020202020202020202020202020202020202020200000000425919ad87c03,0x01ddc8e6d7672a37331d6c307be08547dd2020202020202020202020202020202020202020202020202020202020000000000000000000000000000000000000,0xbb14
.........
Mon Feb 13 12:13:13 EST 2006 DEBUG: Executing XA statement: XA START 0x01ddc8e6d7672a37331d6c307be08547dd20202020202020202020202020202020202020202020202020202020200000000425919ad87c03,0x01ddc8e6d7672a37331d6c307be08547dd2020202020202020202020202020202020202020202020202020202020000000000000000000000000000000000000,0xbb14 JOIN
32136 [main] ERROR jotm Cannot send XA(com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@1bdcbb2) start:com.mysql.jdbc.jdbc2.optional.MysqlXAConnection$MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command) (error code = -5) --XAER_INVAL: Invalid arguments (or unsupported command)
32156 [main] ERROR JDBCExceptionReporter javax.transaction.SystemException: Cannot send XA(com.mysql.jdbc.jdbc2.optional.MysqlXAConnection@1bdcbb2) start:com.mysql.jdbc.jdbc2.optional.MysqlXAConnection$MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command) (error code = -5) --XAER_INVAL: Invalid arguments (or unsupported command)
Mon Feb 13 12:13:13 EST 2006 DEBUG: Executing XA statement: XA ROLLBACK 0x01ddc8e6d7672a37331d6c307be08547dd20202020202020202020202020202020202020202020202020202020200000000425919ad87c03,0x01ddc8e6d7672a37331d6c307be08547dd2020202020202020202020202020202020202020202020202020202020000000000000000000000000000000000000,0xbb14

How to repeat:
I will represent test case, if bug is not expected behaviour.
[13 Feb 2006 18:16] Mark Matthews
The XADatasource doesn't cause this, the server does:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.19-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> xa start 'abcdef','ab';
Query OK, 0 rows affected (0.00 sec)

mysql> xa end 'abcdef', 'ab';
Query OK, 0 rows affected (0.00 sec)

mysql> xa start 'ghijk', 'cd';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state

What version of the server are you using?
[13 Feb 2006 18:37] Sergey Vladimirov
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55 to server version: 5.0.15-nt
[13 Feb 2006 18:42] Sergey Vladimirov
In my case transaction has the same gtrid, but different bqual:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> xa start '1234', '56';
Query OK, 0 rows affected (0.01 sec)

mysql> xa end '1234', '56';
Query OK, 0 rows affected (0.00 sec)

mysql> xa start '1234', '78';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
[13 Feb 2006 19:10] Sergey Vladimirov
The same with 5.0.18:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> xa start '1234','56';
Query OK, 0 rows affected (0.00 sec)

mysql> xa end '1234','56';
Query OK, 0 rows affected (0.00 sec)

mysql> xa start '1234','78';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
mysql>
[21 Feb 2006 12:07] Valeriy Kravchuk
Verified just as described with 5.0.19-BK (ChangeSet@1.2062, 2006-02-20 14:26:32+04:00) on Linux:

mysql> xa start '1234','56';
Query OK, 0 rows affected (0.00 sec)

mysql> xa end '1234','56';
Query OK, 0 rows affected (0.00 sec)

mysql>  xa start '1234','78';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  IDLE state
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)
[6 Nov 2009 10:47] Jeremy Whiting
The resource enlistment by a transaction manager to join a top level transaction is not supported by MySQL server.
 I am trying to run profiling tests using JBossAS with it's (Arjuna) transaction manager. Using the latest TM version 4.8.0_GA. When the TM sends to the mysql server the xa_start message with the JOIN command the database server (version 5.1.37) responds with an unsupported operation message.

client > XA START 0x312337663030303030313a653136333a34616632643932383a32,0x37663030303030313a653136333a34616632643932383a35,0x20003 JOIN
server < java.sql.SQLException: XAER_INVAL: Invalid arguments (or unsupported command)

 Looking in the server documentation for the version I am using it does indicate this is unsupported.

MySQL 12.4.7.1. XA Transaction SQL Syntax
......
For XA START, the JOIN and RESUME clauses are not supported.
.....

This means two popular open source products are incompatible when nested global transactions are needed.

 Can you indicate which version this operation will be supported please ?
[2 Aug 2010 16:17] Sehee Lee
Is there any update to this being fixed? Is this going to be in 5.5?