maria-discuss team mailing list archive

[Marc Schöchlin <ms@xxxxxxxxxx>]

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)

Regards
Marc

Am 22.02.2016 um 15:27 schrieb Justin Swanhart:
> Hi,
>
> 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.....
>
>     Do you have a suggestion how to prevent, solve or analyze this situation?
>
>     Regards
>     Marc
>
>     ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>
>     mysql://root@localhost:/(none) - 11:34:25 > SHOW ENGINE INNODB STATUS\G
>     *************************** 1. row ***************************
>       Type: InnoDB
>       Name:
>     Status:
>     =====================================
>     2016-02-20 11:34:50 7fba401d3700 INNODB MONITOR OUTPUT
>     =====================================
>     Per second averages calculated from the last 31 seconds
>     -----------------
>     BACKGROUND THREAD
>     -----------------
>     srv_master_thread loops: 57 srv_active, 0 srv_shutdown, 171810 srv_idle
>     srv_master_thread log flush and writes: 171823
>     ----------
>     SEMAPHORES
>     ----------
>     OS WAIT ARRAY INFO: reservation count 2076
>     OS WAIT ARRAY INFO: signal count 2072
>     Mutex spin waits 339, rounds 174, OS waits 3
>     RW-shared spins 2133, rounds 63046, OS waits 2069
>     RW-excl spins 0, rounds 0, OS waits 0
>     Spin rounds per wait: 0.51 mutex, 29.56 RW-shared, 0.00 RW-excl
>     ------------
>     TRANSACTIONS
>     ------------
>     Trx id counter 1969875
>     Purge done for trx's n:o < 1965495 undo n:o < 0 state: running but idle
>     History list length 316
>     LIST OF TRANSACTIONS FOR EACH SESSION:
>     ---TRANSACTION 0, not started
>     MySQL thread id 4362, OS thread handle 0x7fba401d3700, query id 23179 localhost root init
>     SHOW ENGINE INNODB STATUS
>     ---TRANSACTION 1969667, not started
>     MySQL thread id 1, OS thread handle 0x7fba41298700, query id 0 Waiting for background binlog tasks
>     ---TRANSACTION 413370, ACTIVE (PREPARED) 172053 sec recovered trx
>     , undo log entries 9
>     Trx #rec lock waits 0 #table lock waits 0
>     Trx total rec lock wait time 0 SEC
>     Trx total table lock wait time 0 SEC
>     ---TRANSACTION 413360, ACTIVE (PREPARED) 172053 sec recovered trx
>     , undo log entries 11
>     Trx #rec lock waits 0 #table lock waits 0
>     Trx total rec lock wait time 0 SEC
>     Trx total table lock wait time 0 SEC
>     --------
>     FILE I/O
>     --------
>     I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
>     I/O thread 1 state: waiting for completed aio requests (log thread)
>     I/O thread 2 state: waiting for completed aio requests (read thread)
>     I/O thread 3 state: waiting for completed aio requests (read thread)
>     I/O thread 4 state: waiting for completed aio requests (read thread)
>     I/O thread 5 state: waiting for completed aio requests (read thread)
>     I/O thread 6 state: waiting for completed aio requests (write thread)
>     I/O thread 7 state: waiting for completed aio requests (write thread)
>     I/O thread 8 state: waiting for completed aio requests (write thread)
>     I/O thread 9 state: waiting for completed aio requests (write thread)
>     1 row in set (0.00 sec)
>
>
>     Regards
>     Marc
>
>     -- 
>     GPG encryption available: 0x670DCBEC/pool.sks-keyservers.net <http://pool.sks-keyservers.net>
>     (https://www.256bit.org/keys/mschoechlin.pub.asc)
>
>
>     _______________________________________________
>     Mailing list: https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss>
>     Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
>     Unsubscribe : https://launchpad.net/~maria-discuss <https://launchpad.net/%7Emaria-discuss>
>     More help   : https://help.launchpad.net/ListHelp
>
>

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

Attachment: signature.asc
Description: OpenPGP digital signature