Bug #44687 XA Transactions dissapear - almost
Submitted: 6 May 2009 10:23 Modified: 6 May 2009 10:54
Reporter: Karl Jorgensen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.32 OS:Linux (5.1.32-community-log, RHEL 5.2)
Assigned to: CPU Architecture:Any
Tags: xa

[6 May 2009 10:23] Karl Jorgensen
Description:
We have a strange problem with prepared(?) xa transactions holding locks:

mysql> xa recover;
+----------+--------------+--------------+------------------------------------------------------------+
| formatID | gtrid_length | bqual_length | data                                                       |
+----------+--------------+--------------+------------------------------------------------------------+
|   131075 |           30 |           28 | 1-4284dd38:de05:4a00a9a5:789df4284dd38:de05:4a00a9a5:79884 | 
|   131075 |           30 |           28 | 1-4284dd38:de05:4a00a9a5:789df4284dd38:de05:4a00a9a5:789ec | 
|   131075 |           30 |           28 | 1-4284dd38:de05:4a00a9a5:789df4284dd38:de05:4a00a9a5:789e6 | 
+----------+--------------+--------------+------------------------------------------------------------+
3 rows in set (0.00 sec)

OK. Fair enough, you may think. Let's try to roll one of them back:

mysql> xa rollback  '1-4284dd38:de05:4a00a9a5:789df','4284dd38:de05:4a00a9a5:789e6',131075;
ERROR 1397 (XAE04): XAER_NOTA: Unknown XID

huh? After double-checking: yes: the XID is correct:

mysql> xa start  '1-4284dd38:de05:4a00a9a5:789df','4284dd38:de05:4a00a9a5:789e6',131075; 
ERROR 1440 (XAE08): XAER_DUPID: The XID already exists
mysql> xa end '1-4284dd38:de05:4a00a9a5:789df','4284dd38:de05:4a00a9a5:789e6',131075;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the  NON-EXISTING state

So the XID is unknown. And it already exists !? Or does it?

About our set-up:
* RHEL 5.2
* MySQL Server version: 5.1.32-community-log MySQL Community Server (GPL)
* Kernel: Linux p7.fizzback.net 2.6.18-92.1.18.el5 #1 SMP Wed Nov 5 09:00:19 EST 2008 x86_64 x86_64 x86_64 GNU/Linux (= 64-bit)

The XA transactions are created by a JBoss application: 4.2.2.GA. We are quite used to the application "orphaning" XA transactions (which we roll back manually) - it is a known application bug (unrelated to MySQL). This application makes use of the JBoss JBPM framework, which in turn requires XA (operating on InnoDB tables).

How to repeat:
Not sure how to repeat this one.
[6 May 2009 10:24] Karl Jorgensen
Output from "show engine innodb status"

Attachment: show-engine-innodb-status.txt (text/plain), 16.94 KiB.

[6 May 2009 10:52] Karl Jorgensen
Strange: Re-starting the database cleared out the XA transactions !?

Not sure why, but I'm happy.
[6 May 2009 10:54] Karl Jorgensen
Closing this. Rationale:
* There is very little debug information available to developers
* Restarting the database "solved" the problem.
* Restarting the database probably made it impossible to obtain more diagnostic information
[6 May 2009 11:57] Sergei Golubchik
If you'll be able to repeat it by doing something in your JBoss application, you can try to enable MySQL general query log (mysqld --log) which will log all incoming statements into a file, it could help to create a "pure" test case in MySQL.
[29 Sep 2010 16:05] Brett Cave
Hi, I have the same problem, in 5.1.42 rhel5

The first line echo's the statement that is executed by mysql -e in the 2nd command:

[root@qa-db-01 ~]# echo "xa rollback '`mysql --skip-column-names -e 'xa recover' | head -1 | awk '{ print $4 }'`'"
xa rollback '1--3f57b3af:8546:4c6d3534:4146d-3f57b3af:8546:4c6d3534:41472'
[root@qa-db-01 ~]# mysql -e "xa rollback '`mysql --skip-column-names -e 'xa recover' | head -1 | awk '{ print $4 }'`'"
ERROR 1397 (XAE04) at line 1: XAER_NOTA: Unknown XID

The state occurred when either the disk that the mysql data dir was mounted on filled up or by killing the mysqld process without a clean shutdown.

Going to keep the db server up for diagnosis.