The output of 'XA recover convert xid' is not useful

[Yves Trudeau]

An XA transaction can use up to 3 fields in the Xid, gtrid (global trx id), bqual (branch qualifier) and Formatid. Here's an example:

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

mysql> insert into testtable (val) values (now());
Query OK, 1 row affected (0.00 sec)

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

mysql> xa prepare 'gtrid','bqual',1234;
Query OK, 0 rows affected (0.06 sec)

mysql> xa recover;
+----------+--------------+--------------+------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------------+
| 1234 | 5 | 5 | gtridbqual |
+----------+--------------+--------------+------------+
1 row in set (0.00 sec)

So, if you are stuck here, let's say after a server crash, you need to commit or rollback but:

mysql> xa commit 'gtridbqual';
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the PREPARED state

mysql> xa recover convert xid;
+----------+--------------+--------------+------------------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------------------------+
| 1234 | 5 | 5 | 0x6774726964627175616C |
+----------+--------------+--------------+------------------------+
1 row in set (0.00 sec)

mysql> xa commit 0x6774726964627175616C;
ERROR 1399 (XAE07): XAER_RMFAIL: The command cannot be executed when global transaction is in the PREPARED state

The answer is to parse the output correctly, as it is in the binlog.

# at 2511
#170823 17:02:20 server id 1 end_log_pos 2557 CRC32 0xbe8c0d98 XA PREPARE X'6774726964',X'627175616c',1234
XA PREPARE X'6774726964',X'627175616c',1234

mysql> xa commit X'6774726964',X'627175616C',1234;
Query OK, 0 rows affected (0.11 sec)

The output of the column data when using "convert xid" should be the value needed like here:

+----------+--------------+--------------+---------------------------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+---------------------------------+
| 1234 | 5 | 5 | X'6774726964,X'627175616C',1234 |
+----------+--------------+--------------+---------------------------------+

here a patch doing this (untested):

--- xa.cc.orig 2017-08-23 13:39:21.125823306 -0400
+++ xa.cc 2017-08-23 14:41:54.388030226 -0400
@@ -762,7 +762,7 @@
                                     MY_INT32_NUM_DECIMAL_DIGITS));
   field_list.push_back(new Item_int(NAME_STRING("bqual_length"), 0,
                                     MY_INT32_NUM_DECIMAL_DIGITS));
- field_list.push_back(new Item_empty_string("data", XIDDATASIZE*2+2));
+ field_list.push_back(new Item_empty_string("data", XIDDATASIZE*2+19));

   if (thd->send_result_metadata(&field_list,
                                 Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
@@ -883,18 +883,16 @@
   if (print_xid_as_hex)
   {
     /*
- xid_buf contains enough space for 0x followed by HEX representation
- of the binary XID data and one null termination character.
+ xid_buf contains enough space for twice X'', two comma, the string
+ representation of formatId and a null terminator
     */
- char xid_buf[XIDDATASIZE * 2 + 2 + 1];
-
- xid_buf[0]= '0';
- xid_buf[1]= 'x';
-
- size_t xid_str_len= bin_to_hex_str(xid_buf + 2, sizeof(xid_buf) - 2,
- const_cast<char*>(m_xid.data),
- m_xid.gtrid_length +
- m_xid.bqual_length) + 2;
+ char xid_buf[XIDDATASIZE * 2 + 8 + 10 + 1];
+
+ size_t xid_str_len=strlen(serialize_xid(xid_buf,m_xid.formatID,
+ m_xid.gtrid_length,
+ m_xid.bqual_length,
+ const_cast<char*>(m_xid.data)));
+
     protocol->store(xid_buf, xid_str_len, &my_charset_bin);
   }
   else

The serialize_xid function is the same function that write the xid to the binlog.

[Yves Trudeau (y-trudeau)]

patch is against PS 5.7

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

Verified as described.

[Sveta Smirnova (svetasmirnova)]

While versions 5.5 and 5.6 do not support XA RECOVER statement they still commit only if XA COMMIT written as xa commit X'6774726964',X'627175616C',1234; So I marked these versions "Confirmed" too.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1818