how to recover from failed transaction due to crash [mysql]?
I am trying to start MySQL server, but it keeps throwing errors. Upon inspecting I found out that the server was stopped unexpectedly and there was a transaction in the process that didn't finish. I had to delete some log files because the server was out of space and I deleted a
mariadb-binfile that I assume contained some information required to perform
tc-heuristic-recover=ROLLBACKrecovery. Now when I run
sudo mysqld --tc-heuristic-recover=ROLLBACKit aborts with error with the below message.
[ERROR] mysqld: File '/var/log/mysql/mariadb-bin.000486' not found (Errcode: 2 "No such file or directory") [ERROR] Failed to open log (file '/var/log/mysql/mariadb-bin.000486', errno 2) [ERROR] Could not open log file [ERROR] Can't init tc log [ERROR] Aborting
how to recover from failed transaction due to crash
The correct answer is "Let the database recover itself".
Instance Recovery is part and parcel of every major DBMS, including mysql/mariadb. It just happens automatically. You don't have to do anything.
I had to delete some log files because the server was out of space ...
Yep; it happens all too often.
... I deleted a mariadb-bin file ...
And that was probably the very worst choice you could have made.
[Text] log files that you or I might read?
Trash 'em with impunity.
[Binary] log files used internally by the database?
Let well alone. Leave them to the database to manage for itself.
You removed the very file that the database needed to perform its Instance Recovery and sort itself out. Anything that you do now (unless you took a a backup of that file before deleting it) will involve losing [some] data ... and that could land you in hot water with Management.