Didn’t manage to collect complete information for this post, so I’ll just write whatever I have.
It all began when our development database server ran out of disk space and crashed. We mounted a temporary virtual hard disk and moved the database there, and all was well for a while. With df and du we narrowed our culprit to mysql’s ibdata, which was growing so fast that we will run out of disk space again soon enough. Public information tells us ibdata is supposed to always grow, but we do not expect our dev ibdata to grow at this rate. After multiple searches this blog finally closes us in.
SHOW ENGINE INNODB STATUS
With the command, the innodb history list length was a very large number (>1mil) and keeps going up. A check with our test and production databases show that the length goes at most to a few hundred and drops back down — a significant difference. Restarting the database doesn’t help.
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX
This showed us we had 16 XA transactions, that were started 2 days ago, but never committed or rolled back. They are not locked, so their trx_mysql_thread_id is 0. We immediately linked the causes together. Stuck XA transactions -> history list growing.
XA RECOVER
According to mysql docs, this command can rollback the XA transactions. The user comment was especially helpful on how to reproduce the xid, reproduced verbatim here:
To rollback the transaction, first get its xid:
mysql> xa recover;
+----------+--------------+--------------+------------------------------------------------------------+
| formatID | gtrid_length | bqual_length | data |
+----------+--------------+--------------+------------------------------------------------------------+
| 131075 | 30 | 28 | 1-a00640d:c09d:4ac454ef:b284c0a00640d:c09d:4ac454ef:b284c2 |
+----------+--------------+--------------+------------------------------------------------------------+
1 row in set (2.13 sec)
The xid is present in this output, but you have to perform a little string manipulation to get it. The format of a xid is: gtrid,bqual,formatID. The column 'data' contains a concatenation of 'gtrid' and 'bqual'. The columns 'gtrid_length' and 'bqual_length' specify how many bytes each of these values uses; use them to split apart 'data'. In this example, the result is:
mysql> xa rollback '1-a00640d:c09d:4ac454ef:b284c0','a00640d:c09d:4ac454ef:b284c2',131075;
ERROR 1402 (XA100): XA_RBROLLBACK: Transaction branch was rolled back
The tricky part here was that, my data had binary characters, which I couldn’t directly copy and paste in the MySQL Workbench. I couldn’t bear to write a program to read the value and write it back either, so I was poking around for solutions on that. From the same mysql doc page,
gtrid and bqual must be string literals, each up to 64 bytes (not characters) long. gtrid and bqual can be specified in several ways. You can use a quoted string ('ab'), hex string (0x6162, X'ab'), or bit value (b'nnnn').
Good, I could write the xid in hex. So I right-clicked the data column, and “Open Value in Viewer”. In the binary tab I copied down the hex values and reconstructed the xid as described by the helpful comment.
XA ROLLBACK X'7e3ae860eb21de21b84d392cb03bf8363b41482b9b1207f6e6823355012e91858c',X'526801e7500b06fd05a2f5882d20be19982a46aed4b6c26dc63887',4264851;
Viola, one by one the transactions were gone. Once the last one was rolled back, the history list started to decrease and behave in a similar pattern as our other databases, and the ibdata stopped growing at the crazy rate. The one last part I haven’t figured out is: how do I copy the hex values from MySQL Workbench, or how do I show the XA RECOVER data column in hex?