Hi. Thanks for helping people by this forum.
Please guide me:
My server is MariaDB. The power was suddenly cut off during the insert query; Maria’s database did not turn on after that.
In the log I was suggested to use the --tc-heuristic command. It had no effect.
I had to reinstall Maria and re-create all the databases and import the backups.
my request:
How to configure Maria database in my.ini or another file so that if the data center power supply is cut off or the power-off button is pressed, during insert or update or other query , the database will not crash and continue to work after turning on the server computer and booting Linux.
Thanks a lot.
Hello @arsse,
All of your tables should be using the InnoDB engine. And you should have the setting innodb_flush_log_at_trx_commit=1. This will give you the highest recoverability from sudden power loss. Crash recovery is automatic. If your database did not start, you need to read the error log and read the reason why it failed to start.
Hello.Thanks for helping me.
Can we set directives in my.ini if the power off when inserting query or update query and …other queries, it leave that insert or update half-done and no need for recovery? (for example after sudden power off we will have some incomplete records in the database. And after power on mariadb service automatically will be on and no need using recovery)
The file is my.cnf, not my.ini (unless you’re on windows and I don’t recommend that).
No, there is no such parameter that will allow uncommitted transactions to recover. How would your application even handle that? How would you know what state a partially recovered transaction is in?
What you are describing is why transactions exist. Start a transaction, insert/update data, and then commit. If power goes off before you’ve committed, you lost the entire transaction. That’s just the way all RDBMS works (Oracle, MSSQL, PGSQL, MySQL, Maria). If you commit, that is guaranteed to be recovered in case of crash.
You may need to run, as root, systemctl enable mysqld, to get maria to start automatically after restart.
Thanks for your help.
I do not care if the information that is entering the database with a query is lost.
I want the database will continue working. That query information is not very important to me.
I want the unlucky query information Incompletely to remain in the database if the power goes out, but after the computer is turned on, Maria’s service will turn on and continue working.
I want after power on without doing any recovery mariadb will be start and works.
Is there a solution?
I want the unlucky query information Incompletely to remain in the database if the power goes out
This is not possible. No database does this. You are breaking the very rules that define what a RDBMS is. Transactions are either committed or not, there is no in-between state.
power on without doing any recovery mariadb will be start and works
There is no guarantee this will be the case every time. Just like your car, there is no guarantee it will start every time. When it does not start, you have to diagnose why it did not, just like a database.
innodb_flush_log_at_trx_commit=1 is correct. I made a mistake in my typing earlier. If you already have this in your my.cnf then that is as good as you can get. That parameter says to flush the transaction to disk on commit. If the server crashes after commit, the transaction will be automatically recovered and applied. If the server crashes before commit, the transaction will automatically roll back.
Thanks.
But my.conf had this directive and after sudenly power off , i used --tc-heuristic but it never start any prosses for roll-back or commit.
I used this command
service mysqld start --tc-heuristic-recover=ROLLBACK
and
service mysqld start --tc-heuristic-recover=COMMIT
Finaly i reinstall mariadb and import all databases last day backups.
XA transactions are designed to allow distributed transactions, where a transaction manager (the application) controls a transaction which involves multiple resources. Such resources are usually DBMSs, but could be resources of any type. The whole set of required transactional operations is called a global transaction. Each subset of operations which involve a single resource is called a local transaction. XA used a 2-phases commit (2PC). With the first commit, the transaction manager tells each resource to prepare an effective commit, and waits for a confirm message. The changes are not still made effective at this point. If any of the resources encountered an error, the transaction manager will rollback the global transaction. If all resources communicate that the first commit is successful, the transaction manager can require a second commit, which makes the changes effective.
For InnoDB, until MariaDB 10.2, XA transactions can be disabled by setting the innodb_support_xa server system variable to 0.
My mariadb is 10.2, if i disable xa tarnsactions after that if power off , when server start again, what will happen? will mysql service start again or will carsh and i must reinstal mariadb?
I am using aapanel panel and mariadb 10.2.
A few days ago, the power of server suddenly lost.
The mysqld service did not start after turning on the server.
It reject evry start command.
The message log said that the data insert operation in the database had brocken and I had to use the --tc-heuristic-recover command to restart the database.
I searched the web.
I saw that they wrote to use the following commands:
service mysqld start–tc-heuristic-recover = ROLLBACK
Or
service mysqld start–tc-heuristic-recover = COMMIT
But after type command it was sending a message that it could not start.
Finally, I reinstalled Maria 10.2. But 15 databases belonging to 15 sites did not work and I had to rebuild all those databases and import the backup of the previous days.
I want a solution to prevent this problem again.
please help.