maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #03390
Re: How to rollback XA Transactions
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?
Source file
#!/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;
}
Source file 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>> 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)
Attachment:
signature.asc
Description: OpenPGP digital signature
Follow ups
References