Hass.io MariaDB Error after Snapshot Restoration

brianjking · August 5, 2019, 7:20pm

Hey,

I have a Hass.io installation running 0.96.5 that I had to restore from a full snapshot the other day. Once the restoration came back online recorder, logbook, & history components don’t work.

I have the following Hass.io Add-ons loaded

MariaDB Add-on is up-to-date with version 1.2 and was working properly prior to having to restore a snapshot.

The snapshot was restored due to LetsEncrypt having problems renewing my certificate and my Hass.io frontend became unavailable. No other changes were made to my configuration.

Any leads on how I can get my database back up and running or setup again? Thanks!

Full Relevant Error Log

2019-07-31 12:55:14 INFO (MainThread) [homeassistant.setup] Setup of domain logger took 0.0 seconds.
2019-07-31 12:55:15 INFO (MainThread) [homeassistant.setup] Setting up http
2019-07-31 12:55:15 WARNING (MainThread) [homeassistant.components.http.auth] legacy_api_password support has been enabled.
2019-07-31 12:55:15 INFO (MainThread) [homeassistant.setup] Setup of domain http took 0.0 seconds.
2019-07-31 12:55:15 INFO (MainThread) [homeassistant.setup] Setting up system_log
2019-07-31 12:55:15 INFO (MainThread) [homeassistant.setup] Setup of domain system_log took 0.0 seconds.
2019-07-31 12:55:15 INFO (MainThread) [homeassistant.setup] Setting up recorder
2019-07-31 12:55:16 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:19 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:22 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:25 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2019-07-31 12:55:25 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:28 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:31 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:34 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:37 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:40 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:43 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-07-31 12:55:43 INFO (MainThread) [homeassistant.setup] Setup of domain recorder took 28.7 seconds.

GitHub Repository for Configuration

brianjking · August 6, 2019, 8:42pm

Ended up resolving this by uninstalling and re-installing the MariaDB add-on.

backcountrymountains · August 16, 2019, 12:03am

So, I just was fiddling with some stuff unrelated to any recorder or MariaDB things and when I restarted HA, I, too, received this error. I wasn’t restoring from a snapshot, just making a minor change to a camera config and restarting.

Unfortunately, uninstalling, rebooting, and reinstalling the MariaDB add-on didn’t fix the issue for me.

Not sure what else to do here. What is the root cause of that error? I hadn’t changed anything related to the recorder or MariaDB but it just decided to not connect.

Any idea what testing I can do?

Thanks,

Justin

backcountrymountains · August 16, 2019, 12:17am

So, I was able to get it working by hard-coding the IP of Hassio instead of ‘core-mariadb’

e.g.
with Hassio IP of 192.168.1.101:

recorder:
  db_url: mysql://hass:[email protected]/homeassistant?charset=utf8

instead of:

recorder:
  db_url: mysql://hass:password@core-mariadb/homeassistant?charset=utf8

I have no idea what is going on in the magic of the add-on that suddenly caused ‘core-mariadb’ to not work, but at least I’ve got a solution.

Side note:
I was able to test by ssh into Hassio and running

docker exec -it addon_core_mariadb /bin/bash
mysql -h 192.168.1.101 -u hass -p

which successfully connected to the MariaDB database.

No idea.

kolossboss · August 16, 2019, 7:49pm

Hi,
I have the same problem posted here.

I changed also to the ip-adress but its not working.

2019-08-16 21:24:26 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-08-16 21:24:29 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-08-16 21:24:32 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")
(Background on this error at: http://sqlalche.me/e/e3q8) (retrying in 3 seconds)
2019-08-16 21:24:35 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to MySQL server on 'core-mariadb' (115)")

I have also problems to access other apps local with manuell port and without ingress. (glances, portainer…)

brianjking · August 16, 2019, 9:29pm

@backcountrymountains

I actually tried the exact solution you did (to replace core-mariadb to the IP address hass.io runs on. Still had the same issue.

docker exec -it addon_core_mariadb /bin/bash
mysql -h 192.168.1.101 -u hass -p

You’re able to run this via the Core SSH add-on?

Thanks!

MikesHomeAssistant · August 19, 2019, 1:42pm

Same issue even after I added IP address and reinstalled MariaDB.

The issue appeared to start occurring after I updated Hass.io to a later vision

Spiritus · August 19, 2019, 10:09pm

The same for me with the last HASSIO version.

kerner · August 20, 2019, 11:40am

I’m noticing the same issue. It appears to be related to the hostname changing on each HASSIO restart.

My logs were showing:
Host 'something.something.local.hass.io' is not allowed to connect to this MariaDB server

I’ve fixed it by updating my MariaDB add on config, changing the host to:
%.local.hass.io for both logins and rights. Seems to have fixed it.

MikesHomeAssistant · August 20, 2019, 3:02pm

I found a fix that works for me.

I change the host name from “homeassistant” to “core-mariadb”.

“homeassistant” is what they have in https://www.home-assistant.io/addons/mariadb/
which worked before but not recently.

I am using a raspberry pi 3 with hass.io installed.

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