Saturday, December 27, 2014

Improvements for XA in MySQL 5.7

Today I was doing some tests with XA transactions in MySQL 5.6.

The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column:

The good news is that 5.7 has transaction information in performance_schema:
mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode 
    -> from performance_schema.events_transactions_current;
+-----------------+-----------------+--------+--------+----------+-------------+
| trx_id          | isolation_level | state  | xid    | xa_state | access_mode |
+-----------------+-----------------+--------+--------+----------+-------------+
|            NULL | REPEATABLE READ | ACTIVE | x-1    | PREPARED | READ WRITE  |
| 421476507015704 | REPEATABLE READ | ACTIVE | NULL   | NULL     | READ WRITE  |
|            NULL | REPEATABLE READ | ACTIVE | foo-1  | ACTIVE   | READ WRITE  |
|            NULL | REPEATABLE READ | ACTIVE | NULL   | NULL     | READ ONLY   |
|            NULL | REPEATABLE READ | ACTIVE | 0x0101 | PREPARED | READ WRITE  |
+-----------------+-----------------+--------+--------+----------+-------------+
5 rows in set (0.00 sec)

This is not only more readable but allows you to use functions on the output. It is also much more complete as all transactions are listed here, not only XA transactions.
 
Update: It's not only possible with performance_schema, In 5.7 there is also a new option to print the xid in hex for XA RECOVER:
mysql> XA RECOVER;
+----------+--------------+--------------+------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------+
|        1 |            2 |            0 |      |
+----------+--------------+--------------+------+
1 row in set (0.00 sec)

mysql> XA RECOVER CONVERT XID;
+----------+--------------+--------------+--------+
| formatID | gtrid_length | bqual_length | data   |
+----------+--------------+--------------+--------+
|        1 |            2 |            0 | 0x0001 |
+----------+--------------+--------------+--------+
1 row in set (0.00 sec)