Hass.io MariaDB Error after Snapshot Restoration

Christian_Hollbjar · August 20, 2019, 7:32pm

This happend to me today to! i have tested all your tips without getting it back up. dont know what more to do…

Edit: i cant get it working again, have to fall back to the old default way untill someone have a answer for whats happening.

tom_l · October 8, 2019, 12:53am

Thank you @kerner that worked for me.

Stooovie · December 3, 2019, 2:26pm

It doesn’t work on restore for me too (same HA version, no changes in config or IPs). Can’t get it to work with any of these tips…

truglodite · December 30, 2019, 7:12pm

Looks like this is a known issue that is in the pipes for a fix:

Until then, it appears the accepted workaround is to stop the Mariadb service before taking a snapshot, then restarting it afterward. This is good to know… I just started using Mariadb and haven’t tried a restore yet. Retaking another snapshot after writing this… with maria off.

Richdem · January 4, 2020, 9:56pm

I just moved my HA instance to a laptop and restored a snapshot and had the same issue. I just did another snapshot this time stopping the Mariadb service before the snapshot. I just restored the image to my laptop, started the Mariadb service and then re started HA and all the errors were gone and the logbook is working again.

truglodite · January 4, 2020, 10:20pm

Perfect, thanks for posting your verification Richdem. Now I don’t have to actually test it to know it works lol. (famous last words… I really should be testing anyways)

Richdem · January 4, 2020, 11:35pm

I always have an old pc that I have a test environment setup, that way I can play with things and get it right before messing with my actual HA. When it all works everyone in the house is happy, when there are issues I am the only tolerant one lol

agners · January 19, 2020, 6:14pm

I did encounter the same issue. In my case it seems that the backup has been corrupted which made the MariaDB server not start correctly. There is also an issue for this case: https://github.com/home-assistant/hassio-addons/issues/667

jd1900 · July 26, 2020, 4:35pm

Same error here.

Solution was to stop MariaDB before doing the backup. But…, when an automatic backup is made, the DB data is lost.

MyKE · January 3, 2021, 8:34pm

Same issue here. What helped me was to use Portrainer > Execute mariadb container shell (addon_core_mariadb) and remove following:

rm /data/databases/ib_logfile*

Restart the MariaDB. It will report some issues regarding log file but in the end checks should be passed and MariaDB will startup again, so you don’t need to start over again with new DB.

Update: I realized even you remove log files and you’re able to startup DB, it may get corrupt during next snapshot creation. So steps above is temporary solution how to get some SQL data from DB if you need them. For me was crucial to export the nginxproxymanager DB and here are steps what I did:

Once you will get started MariaDB when you remove log files mentioned above, save your addon config with username/password to the DB and open portrainer and hook into the container:

addon_core_mariadb:

mysqldump --databases nginxproxymanager > /data/nginxproxymanager.sql

hassio_supervisor:

mv /data/addons/data/core_mariadb/nginxproxymanager.sql /data

Reinstall the mariadb + update addon config (username/password)

Move backed up sql file back to the mariadb data folder

Restore the database:

mysql -e ‘create database nginxproxymanager’
mysql < nginxproxymanger.sql
Restart nginxproxymanager addon

AndrewB · January 9, 2021, 2:27pm

Thanks @truglodite, this solved it for me.

For reference, I was migrating from Hassio 32-bit (RPi 3B+) to a fresh Hassio 64-bit (RPi 4) and was having the MariaDB problems upon restore.

gsemet · April 30, 2021, 9:34pm

hi. i had to uninstall / reinstall to make mariadb works after a restoration, as well.

Townsmcp · May 13, 2021, 3:28pm

Not sure if anyone is interested, but I have restored from a snapshot today. All suggestions above didnt work (Im running 2021.5.3) and I kept getting multiple errors all relating to using MariaDB. Managed to fix it in the the end with the following (potentially not all stages are needed but thought I would record it anyways):

Copy the MariaDB config out from Configuration page in Integrations.
Remove MariaDB integration.
Restart the host.
Go to Configuration - Users.
Reset the password to the one in point 1.
Install the integration again.
Paste the config in from point 1.
Start MariaDB.
Reboot HA.

bschatzow · May 13, 2021, 5:53pm

I have been doing a lot of snapshot restores lately as I have been testing different configurations. What I have found that works consistently is:
1.Stop the mariadb prior to the taking the snapshot.
2. Restore (I usually see errors in recorder and anything db related.
3. After HA completes the restor, go to the mariadb addon and check that it is started. Mine has not been. Start it here
4. Reboot HA. This had worked the last 5 times I have restored.

Paulbunyan · May 18, 2021, 6:36am

@Townsmcp I followed the same steps and that got me back up and running again also, thanks.

Doudar · May 18, 2021, 11:14am

Works for me too, but we need a permanent fix for the problem.

thundergreen · May 19, 2021, 11:10pm

I think the Devs should really look at this as the problem is already known along time. Only thing you can do is to stop mariadb before backup and before restore that should be easy with an automation bit restoring it without stopping it will fail in any case .never got a backup running directly without fiddling with mariadb .

laxarus · May 28, 2021, 6:07pm

Is there a github issue for this specific problem? Otherwise devs might skip this?

ABnSultan · August 22, 2021, 10:03am

is there a permanent fix for this yet?
old data is important and deleting it is not a good fix

ABnSultan · August 22, 2021, 10:34am

some suggested to do partial snapshot and exclude MariaDB instead of full snapshot
i’ll test, and hopefully that will work.