recover from mysql XA transaction issues

June 21, 2022 - 1 minutes read - 152 words

In the past 1 week, I encountered an issue that I couldn’t add a column to a table in one of our production database. I thought restart would resolve the issue. After a restart, however the issue still persist. I checked logs in error/mysql-error-running.log in rds and noticed there are two transactions in prepared state after recovery. In the first gooogle search result page, How to Deal with XA Transactions Recovery caught my attention. From there, I learnt xa transactions and figure out how to resolve my issue.

With hindsight, I should notice that transactions in the result of "SHOW ENGINE INNODB STATUS". and sought solutions.

-- no suspicious process here
SHOW FULL PROCESSLIST

-- the current transaction list
-- don't find any transactions
SELECT * FROM information_schema.innodb_trx

-- two transactions
SHOW ENGINE INNODB STATUS

-- show the xa transactions
xa recover;

Reference:

mysql
xa