How to find which transaction is being rolled back

Question

My MariaDB server crashed earlier today and is still recovering. The error log shows

InnoDB: To roll back: 1 transactions, 519345 rows

From the log entries it appears that the rollback is going to take approximately 4 hours to finish. And my website is barely responding since the server is busy with the rollback.

Based on the number of rows that were affected by the transaction that is being rolled back, I suspect that the server is trying to rollback a transaction involving a log table. If that is the case, I don't mind losing the data.

How can I find the tables that are affected by the transaction that is being rolled back? If it is indeed a non critical table, how can I skip the rollback?

Answer 1

... my website is barely responding since the server is busy with the rollback.

Frankly, I'm surprised this database is responding to anything else other than performing the Rollback.
If you have multiple databases then yes, they will all be badly affected by this.

One transaction affecting 519k records doesn't sound like a "Log table" issue to me or, if it is, then it's being updated by a process that seemingly never issues a commit! (Perhaps only on process shutdown?).
Transactions, including the logging thereof, should be as short (i.e. quick) as practically possible.
This sounds more like an interrupted Batch Job to me.

How can I find the tables that are affected ...

I'm not sure you can.

If it is indeed a non critical table, how can I skip the rollback?

Short answer - You can't.
Rollback is a fundamental part of Instance Recovery that your database must go through after a failure in order to get your data back into a good, "known state".

You might be able to make changes to that particular table "unlogged" (I can't recall if MariaDB supports that) but doing so might give you other "interesting" problems in the Database Recovery arena.

Answer 2

I would advise against it but only way to skip the rollback during start up is by forcing innodb recovery. The option we need is #3 (SRV_FORCE_NO_TRX_UNDO).

You will have to add following to cnf's [mysqld] section and restart:

innodb_force_recovery=3

Refer: https://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html