For most people (including me until recently) database XA transactions are a fuzzy concept. In over eight years with Percona, I have never had to deal with XA transactions. Then a few weeks ago I got two customers having issues with XA transactions. That deserves a post.
XA 101
What are XA transactions? XA transactions are useful when you need to coordinate a transaction between different systems. The simplest example could be simply two storage engines within MySQL. Basically, it follows this sequence:
- XA START
- Some SQL statements
- XA END
- XA PREPARE
- XA COMMIT or ROLLBACK
Once prepared, the XA transaction survives a MySQL crash. Upon restart, you’ll see something like this in the MySQL error log:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 2017-08-23T14:53:54.189068Z 0 [Note] Starting crash recovery... 2017-08-23T14:53:54.189204Z 0 [Note] InnoDB: Starting recovery for XA transactions... 2017-08-23T14:53:54.189225Z 0 [Note] InnoDB: Transaction 45093 in prepared state after recovery 2017-08-23T14:53:54.189244Z 0 [Note] InnoDB: Transaction contains changes to 2 rows 2017-08-23T14:53:54.189257Z 0 [Note] InnoDB: 1 transactions in prepared state after recovery 2017-08-23T14:53:54.189267Z 0 [Note] Found 1 prepared transaction(s) in InnoDB 2017-08-23T14:53:54.189312Z 0 [Warning] Found 1 prepared XA transactions 2017-08-23T14:53:54.189329Z 0 [Note] Crash recovery finished. 2017-08-23T14:53:54.189472Z 0 [Note] InnoDB: Starting recovery for XA transactions... 2017-08-23T14:53:54.189489Z 0 [Note] InnoDB: Transaction 45093 in prepared state after recovery 2017-08-23T14:53:54.189501Z 0 [Note] InnoDB: Transaction contains changes to 2 rows 2017-08-23T14:53:54.189520Z 0 [Note] InnoDB: 1 transactions in prepared state after recovery 2017-08-23T14:53:54.189529Z 0 [Note] Found 1 prepared transaction(s) in InnoDB 2017-08-23T14:53:54.189539Z 0 [Warning] Found 1 prepared XA transactions |
The command xa recover shows you an output like:
1 2 3 4 5 6 7 | mysql> xa recover; +----------+--------------+--------------+-----------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+-----------+ | 1234 | 4 | 5 | bqual | +----------+--------------+--------------+-----------+ 1 row in set (0.00 sec) |
There are some binary data that can’t be shown in HTML. The XA Xid is made of three fields: gtrid (global trx id), bqual (branch qualifier) and formatId. Java applications use all three fields. For my example above, I used “X’01020304′,’bqual’,1234”. You can trust Java application servers to be creative with Xid values. With MySQL 5.7, you can output the data part in hex with convert xid :
1 2 3 4 5 6 7 | mysql> xa recover convert xid; +----------+--------------+--------------+----------------------+ | formatID | gtrid_length | bqual_length | data | +----------+--------------+--------------+----------------------+ | 1234 | 4 | 5 | 0x01020304627175616C | +----------+--------------+--------------+----------------------+ 1 row in set (0.01 sec) |
The Problem
If you do nothing, the prepared transaction stays there forever and holds locks and a read view open. As a consequence, the history list grows without bound along with your ibdata1 file, where the undo entries are kept. If you have slaves, they all have the prepared transaction too (at least with 5.7). No fun.
As a consequence, if you are using XA transactions, you MUST check if there are prepared transactions pending after the server or mysqld restarted. If you find such transactions, you need to commit or roll them back, depending on what is involved.
But how do you commit these XA transactions? The problem here is the output of xa recover. As it is, the output is unusable if there is a bqual field or non-default formatID field:
1 2 | mysql> xa commit 0x01020304627175616C; ERROR 1397 (XAE04): XAER_NOTA: Unknown XID |
The Fix
Looking back at the xa recover convert xid output above, the gtrid_length and bqual_length are provided. With the use of these values, you can extract the parts of the data field which gives us:
- gtrid = 0x01020304
- bqual = 0x627175616C
And, of course, the formatID is 1234. Altogether, we have:
1 2 | mysql> xa commit 0x01020304,0x627175616C,1234; Query OK, 0 rows affected (0.15 sec) |
Which finally works! On 5.6 the convert xid option is not available. You have to be a bit more creative:
1 2 3 4 5 6 7 8 9 10 11 | root@master57:/var/lib/mysql# mysql -r -e 'xa recoverG' | hexdump -C 00000000 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a |****************| 00000010 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 20 31 2e 20 72 |*********** 1. r| 00000020 6f 77 20 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a |ow *************| 00000030 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 2a 0a 20 |**************. | 00000040 20 20 20 66 6f 72 6d 61 74 49 44 3a 20 31 32 33 | formatID: 123| 00000050 34 0a 67 74 72 69 64 5f 6c 65 6e 67 74 68 3a 20 |4.gtrid_length: | 00000060 34 0a 62 71 75 61 6c 5f 6c 65 6e 67 74 68 3a 20 |4.bqual_length: | 00000070 35 0a 20 20 20 20 20 20 20 20 64 61 74 61 3a 20 |5. data: | 00000080 01 02 03 04 62 71 75 61 6c 0a |....bqual.| 0000008a |
But there is a limitation in 5.6: you can only XA commit/rollback transactions that belong to your session. That means after a crash you are out of luck. To get rid of these you need to promote a slave or perform a logical dump and restore. The best plan is to avoid the use of XA transactions with 5.6.
I submitted this bug to Percona Server for MySQL in order to get a usable output out of xa recover convert xid. If you think this is important, vote for it!
Bug is at https://bugs.mysql.com/bug.php?id=87526
Good article for someone that suffered with XA before, thanks for writing it, Yves! Cheers!
I am getting error even after splitting based on the length
mysql> XA COMMIT 0x7023010095EDCC9A6369736861322C7365727665722C503130,0x306369736861322C7365727665722C503130302C00,4871251;
ERROR 1397 (XAE04): XAER_NOTA: Unknown XID
Looks like you’re missing the 3rd parameter: formatId (get it from “xa recover convert xid”)
Nope, sorry, I lied… you have the 3 parameters, just much longer than the ones I’ve seen. Not sure what the problem is.
Thanks for this article… helped a lot.