recorder doesn't support MariaDB in Docker - error "No module named 'pymysql'"

[dgtal1]

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:

[Chaotic]

From the docs

On the Python side we use the mysqlclient:

For MySQL you may have to install a few dependencies. You can choose between pymysql and mysqlclient:

So you need to install it manually. Also you can just use mysql (I have mariaDB set up in a docker (with HA in a docker) this way)

[dgtal1]

From the docs

On the Python side we use the mysqlclient: For MySQL you may have to install a few dependencies. You can choose between pymysql and mysqlclient:

So you need to install it manually. Also you can just use mysql (I have mariaDB set up in a docker (with HA in a docker) this way)

Thanks for the hint, but I think installing anything in a Docker 'distro' is not the right solution. Rememeber that installing an upgrade in Docker erases everything what was in the container before. So when I install newer HA - I will loose this library.
Docker images are always delivered with all the possibly required software. Z-Wave - checked, Tensorflow - checked etc. So I really think some db-libraries should be there as well.

I'll try using mysql url instead of MariaDB, but I will keep this one open, as I think the missing libraryr should be in the Docker image as for all other components.

[cgarwood]

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.

[dgtal1]

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

[tanus10]

This is quite a deal-breaker for using official docker image. This should be documented somewhere in Recorder component page.

[d-rez]

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=utf8

Seems to work fine in docker

[Geoff571]

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 mysqlclient as 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 the sudo command. 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=utf8

The 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....

[dgtal1]

I guess the trick with adding port number and removing "+pymsql" from the access url only works with hass.io. I'm running my HA in Docker on a Synology NAS and I tried adding the port at the beginning when I first experimented with MariaDB, also removed the "+pymsql" form the path but it doesn't help and I'm getting error:

Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)")
(Background on this error at: http://sqlalche.me/e/e3q8)

After every HA update I always need to manually install mysqlclient in order to make HA connect to MariaDB. I wish I could update HA without these additional steps to have a painless experience as I had before switching to MariaDB (for good reasons of a huge performance gain).

[d-rez]

I'm not using hass.io. I'm using pure Docker. You're probably doing something else wrong

[dgtal1]

Interesting... I've tried the following config lines:

  db_url: mysql://u:p@localhost:3307/homeassistant?charset=utf8
  db_url: mysql://s:p@192.168.0.13:3307/homeassistant?charset=utf8

Neither works... Then this is maybe related to the MariaDB installation itself? Are you using a NAS? I'm running my HA inside a Docker on a Synology NAS and installed MariaDB as a Synology Package from the official Synology Pakcage respository. Here's its config: What else can be wrong here?

[Geoff571]

What error are you seeing in the logs now that you’re not getting the one about pymysql?

[d-rez]

@grzeg8102 drop the MariaDB Synology package and just spin up a container from linuxserver/mariadb:latest

Make sure you set up all required env variables, especially the following:

ENV	VALUE
MYSQL_DATABASE	HASS
TZ	yourCountry/yourCity
MYSQL_USER	hass
MYSQL_PASSWORD	whatever
MYSQL_ROOT_PASSWORD	whatever2

Then in db_url:

mysql://MYSQL_USER:MYSQL_PASSWORD@127.0.0.1:YOURPORT/HASS?charset=utf8

P.S. If this doesn't get you sorted I'm afraid we're outside of this issue scope and you should search for answers elsewhere, sorry

[dgtal1]

@Geoff571 The errors I'm getting:

ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (2002, 'Can\'t connect to local MySQL server through socket \'/var/run/mysqld/mysqld.sock\' (2 "No such file or directory")') I logged into my hass docker and checked the path from the error and indeed there is no such file inside /var/run directory.

When I checked the path from the MariaDB package UI (above) - the similar file exists within Synology filesystem, but not in HA docker filesystem. I am not familiar with linux, but it seems that there are 2 ways of communicating with MariaDB:

• TCP port
• so called linux socket - looks like a file in the 'ls' shell command

I'm guessing my HA tries to connect via linux socket rather than by using the TCP port and it fails because of that. But I don't know how to force HA to connect using a TCP port...

@d-rez I just tried that but getting the same error as above.

Can you guys check if you have the file '/var/run/mysqld/mysqld.sock\' in your HA docker file system?

[dgtal1]

And another question - are your HA docker containers using the host network or a bridged network setup? In Synology one can choose it (I know this is also a parameter of the 'docker' command line - when not using Synology and no UI) and I chose 'host'. Perhaps I need to use a bridged docker configuration to have it up and running?

[d-rez]

I'm using host network for hass container and a bridge/non-host config for mariadb one

Can you guys check if you have the file '/var/run/mysqld/mysqld.sock' in your HA docker file system?

it would make no sense to have this file in the hass container. This file belongs to mysql daemon so it wouldn't exist.

Your hass config file must be broken. Go over it again

[dgtal1]

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=utf8

Don'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.

[d-rez]

I mean... Sure, but that's exactly what I wrote in my comment over 2 months ago...

https://github.com/home-assistant/home-assistant/issues/21064#issuecomment-504528861

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=utf8

Seems to work fine in docker

And re:

Apparently localhost is not the same as 127.0.0.1 on some machines in Docker installations.

Of course it's not. Docker randomizes internal container IPs. 127.0.0.1 will always be docker container itself but if you want to reach to something outside the container, NAT translation will have to be done (and you need to call gateway / the actual LAN IP). This is basic Docker stuff though...

Anyway, glad you sorted this out and found the problem with your config file :)

[dgtal1]

I mean... Sure, but that's exactly what I wrote in my comment over 2 months ago...

#21064 (comment)

Well, in this thread no one mentioned 127.0.0.1 as the IP to try out before you earlier today. Also not 2 months ago. Perhaps in some other thread I wasn't aware of. But anyway I got 'inspired' exactly by your comment from today where you stated 127.0.0.1 and thought - I have nothing to loose, so why not even if it seemd crazy that it'll workwhen localhost didn't, Cause it's definitely not obvious that the docker environment makes the popular 'localhost' different from 127.0.0.1. I'm sure the common understanding is that these are synonymous and only can be different in some special circumstances like here.

Again anyway - I'm more than super happy having it solved! Github/community rules! :) THANK YOU ALL FOR YOUR SUPPORT AND COMMENTS!

[GoSpursGoNL]

I followed the above comments (use 127.0.0.1 as ip instead of localhost, add the port number, remove the +pymysql part) but in addition in docker-compose.yaml for MariaDB I had to explicitly map the ports (3306:3306) before HA was able to reach it. Now it is working fine!

I did not have to install any depencencies as suggested at the beginning of the thread.

[omegacore]

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=utf8

Seems to work fine in docker

I opened a pr to update the docs for this:

https://github.com/home-assistant/home-assistant.io/pull/11527

[potyt]

So, I have the homeassistant docker image, seems fine, runs with sqlite. I also have a separate mariadb image running in a container, also seems fine. The following db_url however doesn't work:

db_mysql: mysql://homeassistant:xxxxxxxxxx@ha-mariadb/ha_db

Checking HA logs shows:

ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'MySQLdb'

This isn't a host or port of any other issue, all hosts/containers can see each other, ping each other, connect to ports fine, etc. it is just that the MySQL driver is missing from the SQLAlchemy distribution in the docker image. Realistically, how would this ever work?

[simbus82]

So, I have the homeassistant docker image, seems fine, runs with sqlite. I also have a separate mariadb image running in a container, also seems fine. The following db_url however doesn't work:

db_mysql: mysql://homeassistant:xxxxxxxxxx@ha-mariadb/ha_db

Checking HA logs shows:

ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: No module named 'MySQLdb'

This isn't a host or port of any other issue, all hosts/containers can see each other, ping each other, connect to ports fine, etc. it is just that the MySQL driver is missing from the SQLAlchemy distribution in the docker image. Realistically, how would this ever work?

Do you have upgraded Python 3 to 3.9? Probably the mysql client in the new python's venv is missing. Try to install the client:

sudo systemctl stop home-assistant@homeassistant.service
sudo su -s /bin/bash homeassistant
source /srv/homeassistant/bin/activate
pip3 install mysqlclient
exit
sudo systemctl start home-assistant@homeassistant.service