# Need HELP for prevent downing mariadb server **URL:** \ **Category:** MySQL & MariaDB\ **Created:** [August 20, 2021, 1:59pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858 "2021-08-20T13:59:59Z")\ **Posts on this page:** 17\ **Page:** 1
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 20, 2021, 1:59pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/1 "2021-08-20T13:59:59Z")
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. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 20, 2021, 6:49pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/2 "2021-08-20T18:49:33Z")
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. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 21, 2021, 2:07pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/3 "2021-08-21T14:07:25Z")
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) ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 21, 2021, 8:45pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/4 "2021-08-21T20:45:17Z")
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. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 22, 2021, 9:19am UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/5 "2021-08-22T09:19:11Z")
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? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 22, 2021, 2:12pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/6 "2021-08-22T14:12:40Z")
> 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. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 22, 2021, 2:29pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/7 "2021-08-22T14:29:02Z")
I see in my.conf there is this directive: innodb\_flush\_log\_at\_ **trx** \_commit = 1 and you said i must use this: innodb\_flush\_log\_at\_tx\_commit=1 which must i use? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 22, 2021, 4:26pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/8 "2021-08-22T16:26:29Z")
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. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 28, 2021, 4:31pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/9 "2021-08-28T16:31:14Z")
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. I use aapanel hosting pannel. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 28, 2021, 9:12pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/10 "2021-08-28T21:12:07Z")
> [@arsse](#): > > tc-heuristic That is only used for XA transactions. I would remove it unless you are doing XA transactions. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 29, 2021, 12:21pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/11 "2021-08-29T12:21:06Z")
> [@matthewb](#): > > That is only used for XA transactions. I would remove it unless you are doing XA transactions. what is XA transaction? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 29, 2021, 2:15pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/12 "2021-08-29T14:15:35Z")
If you don’t know what an XA transaction is, then `--tc-heuristic` is not going to do anything for you. Remove it. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 30, 2021, 11:45am UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/13 "2021-08-30T11:45:01Z")
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. > **[XA Transactions](https://mariadb.com/kb/en/xa-transactions/)** > > Transactions designed to allow distributed transactions. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 30, 2021, 11:48am UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/14 "2021-08-30T11:48:39Z")
see: > For InnoDB, until [MariaDB 10.2](https://mariadb.com/kb/en/what-is-mariadb-102/), XA transactions can be disabled by setting the [innodb\_support\_xa](https://mariadb.com/kb/en/innodb-system-variables/#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? ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 30, 2021, 2:09pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/15 "2021-08-30T14:09:23Z")
Just ignore everything related to XA transactions. Just stop worrying about it. If you are not using them, then no need to worry about them. ---
**Author:** ![arsse](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/arsse/32/4272_2.png) [@arsse](https://forums.percona.com/u/arsse)\ **Post date:** [August 30, 2021, 4:16pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/16 "2021-08-30T16:16:01Z")
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. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 30, 2021, 4:31pm UTC](https://forums.percona.com/t/need-help-for-prevent-downing-mariadb-server/11858/17 "2021-08-30T16:31:01Z")
I recommend upgrading to the latest MariaDB or latest Percona Server. Your version is very old.