← Back to team overview

maria-discuss team mailing list archive

Re: How to rollback XA Transactions

 

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@xxxxxxxxxx> 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@xxxxxxxxxx <mailto:ms@xxxxxxxxxx> <ms@xxxxxxxxxx>> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>
>

References