Bug #87526 The output of 'XA recover convert xid' is not useful
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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.122 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.