MariaDB - Start mysqld with --tc-heuristic-recover switch

By xngo on February 18, 2020

I always upgrade to the latest version of MariaDB/MySQL database while still using the same database file(i.e /var/lib/mysql). In the process of doing this, I often get the error message: "... start mysqld with --tc-heuristic-recover switch ..."

systemctl status mariadb.service --no-pager -l
‚óŹ mariadb.service - MariaDB 10.4.12 database server
...
Main PID: 5206 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"
[Note] InnoDB: Starting recovery for XA transactions...
[Note] InnoDB: Transaction 303718 in prepared state after recovery
[Note] InnoDB: Transaction contains changes to 14325 rows
[Note] InnoDB: 1 transactions in prepared state after recovery
[Note] Found 1 prepared transaction(s) in InnoDB
[ERROR] Found 1 prepared transactions! It means that mysqld was not shut down properly last time and critical recovery information (last binlog or tc.log file) was manually deleted after a crash. You have to start mysqld with --tc-heuristic-recover switch to commit or rollback pending transactions.
[ERROR] Aborting
mariadb.service: Main process exited, code=exited, status=1/FAILURE
mariadb.service: Failed with result 'exit-code'.
Failed to start MariaDB 10.4.12 database server.

Solution

To resolve this issue, I ran the following commands:

mysqld --tc-heuristic-recover=ROLLBACK
systemctl start mariadb.service

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.