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.
