Hass.io MariaDB Error after Snapshot Restoration

Tags: #<Tag:0x00007f739908ea08> #<Tag:0x00007f739908e5a8>

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.
1 Like

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

1 Like

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

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:[email protected]/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.

1 Like

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…)

@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!

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

The same for me with the last HASSIO version.

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.

1 Like

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.

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.

Thank you @kerner that worked for me.

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…

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. :slight_smile:

1 Like

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.

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)

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

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

Same error here.

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