#!/usr/bin/env perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('DBI:mysql:foodb', 'root', 'mypassword'
                    ) || die "Could not connect to database: $DBI::errstr";

my $sth = $dbh->prepare('xa rollback ?');
my $results = $dbh->selectall_hashref('xa recover;', 'data');
foreach my $id (keys %$results) {
     my $xid = $results->{$id}->{data};

     #$xid =~ s/(.)/sprintf("%x",ord($1))/eg;
     #$xid = "0x".$xid;

     print "XID >>>".$xid."<<<";
     $sth->execute($xid) or die "Couldn't execute statement: " . $sth->errstr;
}

Hi, Marc!

While not exactly user-friendly, you can still run

$ mysql -e 'xa recover'|hexdump -C

You'll see hex data there.

Regards,
Sergei

On Feb 22, Marc Schöchlin wrote:

Hi Justin,

how can i get the hex representation of the the transaction identification strings?
("XA RECOVER CONVERT XID" seems only to be available on MySQL 5.7)

I have not tested this, but I think you will need to convert the strings to hex then do:
xa rollback 0xabcdef <-- where 0xabcdef is the hex representation of the string.

--Justin

On Sat, Feb 20, 2016 at 5:53 AM, Marc Schöchlin <ms@256bit.org <mailto:ms@256bit.org>> wrote:

Hi,

i currently have the situation that i cannot modify tables/indices beaus of XA transactions which are in progress.

mysql://root@localhost:/(none) - 11:37:55 > xa recover;
+----------+--------------+--------------+------------------------------------------------------------------+
| formatID | gtrid_length | bqual_length | data                                                             |
+----------+--------------+--------------+------------------------------------------------------------------+
|  4871251 |           35 |           29 | ^W<89>^B <E8>XF+foo-bar-de05,server,P4837foo-bar-de05,server,P4837,^A |
|  4871251 |           35 |           29 | ^N<89>^B <E8>XF+foo-bar-de05,server,P4837foo-bar-de05,server,P4837,^A |
+----------+--------------+--------------+------------------------------------------------------------------+
2 rows in set (0.00 sec)

The documentation (https://mariadb.com/kb/en/mariadb/xa-transactions/) suggests that xa transaction can be rolled back by using "xa rollback xid" but it seems that this is not possible because of the non-ascii characters.
As you can see in the attached Innodb status, these transactions a active for a log time now and should not be relevant anymore.
Naturally restarting the server should not help.....

-- 
GPG encryption available: 0x670DCBEC/pool.sks-keyservers.net
(https://www.256bit.org/keys/mschoechlin.pub.asc)