Hi,

Perhaps try to (excuse me if my Perl is rusty)
$qxid = $dbh->quote($xid)
$sth = $dbh->prepare("XA ROLLBACK " . $xid)
$sth->execute();

If that doesn't work, and you are familiar with C, you could make a modification to the server so that after it initializes InnoDB to iterate and rollback all XA transactions manually.  You would have to look at the parser to see exactly how it dispatches XA rollback commands to do so.  MariaDB or Percona consulting might be the best solution here if you are not capable of such a modification.

--Justin

On Fri, Mar 4, 2016 at 2:34 AM, Marc Schöchlin <ms@256bit.org> wrote:
Hi,

i don't get rid of this :-)

I use mariadb "10.0.24+maria-1~trusty" on Ubuntu 14.04 and i tried to solve the situation by writing this hacky perl script :-)

The problem seems to be a bug. From my point of view the only solution might be to completely restore a existing mysqldump backup.
Are the other (quicker/better) solutions?

#!/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;
}

MariaDB complains:
(the same also after converting the xid to hex)

# ./xa-hassle
DBD::mysql::st execute failed: XAER_NOTA: Unknown XID at ./xa-hassle line 20.
Couldn't execute statement: XAER_NOTA: Unknown XID at ./xa-hassle line 20.
XID >>> � �XF+foo-bar-de05,server,P4837foo-bar-de05,server,P4837, <<<

Regards
Marc


Am 23.02.2016 um 17:50 schrieb Sergei Golubchik:
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)

_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp