core: recorder doesn't support MariaDB in Docker - error "No module named 'pymysql'"
Home Assistant release with the issue: 0.87.1
Operating environment (Hass.io/Docker/Windows/etc.): Docker
Component/platform: https://www.home-assistant.io/components/recorder/
Description of problem: I decided to give a try to running recorder using MariaDB 10 in my Synology NAS. I followed instructions in the docs and when I restarted HA for the changes to take effect - I got errors in logs and no History component instead.
Problem-relevant configuration.yaml entries and (fill out even if it seems unimportant):
recorder:
db_url: mysql+pymysql://user:pass@IP_CENSORED/homeassistant?charset=utf8
Traceback (if applicable):
2019-02-14 13:24:25 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:28 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:31 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:33 WARNING (MainThread) [homeassistant.setup] Setup of recorder is taking over 10 seconds.
2019-02-14 13:24:34 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:37 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:40 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:43 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:47 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:50 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:53 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'pymysql' (retrying in 3 seconds)
2019-02-14 13:24:53 INFO (MainThread) [homeassistant.setup] Setup of domain recorder took 30.0 seconds.
2019-02-14 13:24:53 ERROR (MainThread) [homeassistant.setup] Setup failed for recorder: Component failed to initialize.
Additional information:
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Comments: 22 (1 by maintainers)
Just a friendly reminder to everyone that to use MariaDB all you need to do is remove the “+pymysql” part, e.g. just use
db_url: 'mysql://u:p@ip:port/db_name?charset=utf8Seems to work fine in docker
It’s really frustrating and I really can’t understand why is this not included in the official image. As I already mentioned Z-Wave. It’s also not required to run the whole HA and yet it is still provided as part of the image. It’s a lack of consistency in the approach to me. I also can’t understand why adding this to HA is a problem. I guess someone would need to bump its version - ok but this happens for the whole bunch of other components anyway
There are currently no intentions of adding pymsql to the official docker image since it’s not required for the recorder component to run. If you want to use mysql you’ll need to manually install the deps. See #20149 and the linked issues/PRs from that issue.
This is quite a deal-breaker for using official docker image. This should be documented somewhere in Recorder component page.
I landed on this thread after encountering much the same set of problems that others have hit along the way. After a couple of false starts, I’ve managed to get HA using the MariaDB on my NAS.
It wasn’t painless as I originally thought I needed to install the
mysqlclientas some others had indicated, this proved as bit of a dead-end as I’m running a Hassio install and it doesn’t have access to thesudocommand. The DB client install isn’t required on Hassio as it turned out.I was getting an error in the logs indicating that HA couldn’t connect to the DB. To cut a long story short, this turned out to be the port number on the DB server.
I finished up with the following config line in the recorder.yaml:
db_url: mysql://HASSUSER:PASSWORD@IP_ADDRESS:**PORT**/hass_db?charset=utf8The PORT was the key to getting it connecting.
Now, I just need to find out why the History component is failing to retrieve the data for the DB, it’s worked only once since making the change to MariaDB and that after a HA reboot. Logbook is working fine…
SOLVED! Unbelievable, but the problem was that I was passing ‘localhost’ or my NAS IP as my host in HA config, but the one that works is ‘127.0.0.1’! I always thought (and still do) these are the same things, but apparently not in some scenarios or machines. I think db host IP and localhost should also work and be valid, but they don’t as you see . I think I also understood the error I was getting - probably HA first tried to connect using IP and port, but when this didn’t work (impossible resolution of IP for ‘localhost’? or impossible to connect to my db host IP?) it tried using a linux socket as a fallback option, and thus the error. I noticed that ‘mysql’ shell command uses the linux socket connection by default even when one explicitly supplies port for it and the connection method must be specified explicitly using the ‘–protocol’ switch. Perhaps HA sql library uses a similar logic. Anyway a confusing error message I must say.
So to summarize things once again for the people, who will come to this thread in the future:
Use the following url in your HA config to connect to MariaDB without installing additional libraries and without using mysql+pymysql
db_url: mysql://DBUSER:PASSWORD@IP_ADDRESS:PORT/hass_db?charset=utf8Don’t forget about supplying the PORT of MariaDB and when you’ll be getting same error as mine - provide 127.0.0.1 as your IP_ADDRESS instead of ‘localhost’ or your db host IP Apparently localhost is not the same as 127.0.0.1 on some machines in Docker installations.