maria-discuss team mailing list archive

[Marc Schöchlin <ms@xxxxxxxxxx>]

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
(https://www.256bit.org/keys/mschoechlin.pub.asc)

Attachment: signature.asc
Description: OpenPGP digital signature