Bug #87526 The output of 'XA recover convert xid' is not useful
Submitted: 24 Aug 2017 11:55 Modified: 24 Aug 2017 12:08
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: XA transactions Severity:S3 (Non-critical)
Version:5.7.19, 8.0.2 OS:Any
Assigned to:

[24 Aug 2017 11:55] Sveta Smirnova
Description:
Originally reported at https://bugs.launchpad.net/percona-server/+bug/1712649

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 |
+----------+--------------+--------------+---------------------------------+

How to repeat:
cat ~/src/tests/t/bug1712649.test
--source include/have_innodb.inc

create table testtable(val datetime, id int not null auto_increment primary key) engine=innodb;

xa start 'gtrid','bqual',1234;
insert into testtable (val) values (now());
xa end 'gtrid','bqual',1234;
xa prepare 'gtrid','bqual',1234;
xa recover;
--error 1399,1397
xa commit 'gtridbqual';
#--error 1064,0
xa recover convert xid;
--error 1399,1397
xa commit 0x6774726964627175616C;
xa commit X'6774726964',X'627175616C',1234;
drop table testtable;

Suggested fix:
Output of XA RECOVER should show ID which can be used in XA COMMIT statement.
[24 Aug 2017 12:08] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.

Thanks,
Umesh