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-bin file that I assume contained some information required to perform tc-heuristic-recover=ROLLBACK recovery. Now when I run sudo mysqld --tc-heuristic-recover=ROLLBACK it 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2