#4 Unable To Use External Recorder SQL Database

I'm trying use a separate MariaDB instance for the Home Assistant Recorder DB, but I get error saying hass can't find mysql or pymysql module.

I've looked for solutions to this, but they seem to involve manually installing the modules into the installation. My understanding is that this is not possible, or at least different, when using a snap package.

Is there any way to accomplish this currently or a different database driver that is available?

Thanks! I'm really enjoying the snap for HA over the docker container I was using before.

Hi Jeremy,

I will have a look into it, but can you describe how you are configuring the DB? I'm just using the default sqlite yet.

Would be great with a description on how to reproduce it so I can investigate further. :)

I might be able to release another package with the SQL modules, if it requires building on run-time.

Hey Joachim,

Sorry about the late response. My setup is currently:

• Host machine running Ubuntu Core 20.04
• Home Assistant Snap
• Docker Snap
  • MariaDB Container

My HA Configuration for the Recorder (I followed the instructions here https://www.home-assistant.io/integrations/recorder/):

recorder:
  db_url: mysql://ha:[password]@172.17.0.3/hadb?charset=utf8mb4

And I get this error at startup:

Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:933
Integration: recorder (documentation, issues)
First occurred: 5:02:28 PM (9 occurrences)
Last logged: 5:02:52 PM

Error during connection setup to mysql://ha:[password]@172.17.0.3/hadb?charset=utf8mb4: No module named 'MySQLdb' (retrying in 3 seconds)
Traceback (most recent call last):
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 653, in _setup_recorder
    self._setup_connection()
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 933, in _setup_connection
    self.engine = create_engine(self.db_url, **kwargs)
  File "<string>", line 2, in create_engine
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 548, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 118, in dbapi
    return __import__("MySQLdb")
ModuleNotFoundError: No module named 'MySQLdb'

I've also tried (as suggested in the docs) adding the +pymysql extension to use the python module instead of the native library, but I get a similar error:

Error during connection setup to mysql+pymysql://ha:[password]@172.17.0.3/hadb?charset=utf8mb4: No module named 'pymysql' (retrying in 3 seconds)
Traceback (most recent call last):
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 653, in _setup_recorder
    self._setup_connection()
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 933, in _setup_connection
    self.engine = create_engine(self.db_url, **kwargs)
  File "<string>", line 2, in create_engine
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 548, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/snap/home-assistant-snap/283/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/pymysql.py", line 59, in dbapi
    return __import__("pymysql")
ModuleNotFoundError: No module named 'pymysql'

Please let me know if there's any more info I can provide.

Thanks!

Thanks, I'll look into it :)

Hi,

Are you using MySQL or MariaDB? Just wanna know so I'll have something to work with during development + testing, before I add additional support for other DB's

I'm using MariaDB, but my understanding is that it is a fork of MySQL so it uses the same client libraries as MySQL so it shouldn't make too much of a difference.

Hi,

The docs states otherwise.

Which version of MariaDB? :)

Apologies :)

I'm using the latest docker image v10.5.11

Which architecture are you using?

Intel NUC amd64

So... I'm pushing a snap now.

1. It contains what was included in the documentation, but only for MariaDB at this time.
2. I have been really busy and really haven't had time to test it - and don't have a machine available for testing atm. So I hope you can help me test this...

Even though this is an untested release, you shouldn't worry as you can roll back if anything fails.

First figure out which channel you are currently on:

snap info home-assistant-snap | grep "tracking:"

This release is pushed to the edge/dev channel. To test it out you have to switch:

sudo snap switch home-assistant-snap --channel=edge/dev

and refresh Home Assistant

sudo snap refresh home-assistant-snap

If you have to roll back just

sudo snap revert home-assistant-snap

Then change back to your previous channel.

Let me know how it goes - and include some data if anything doesn't work as expected.

See ya!:)

Thanks Joachim!

I'll test that when I get home this evening.

Hey Joachim,

Switched to the edge/dev channel and upgraded to version 2021.6.6.

No luck, but it looks like a different error.

Error when using mysql driver:

Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:933
Integration: Recorder (documentation, issues)
First occurred: 1:18:39 PM (10 occurrences)
Last logged: 1:19:06 PM

Error during connection setup to mysql://ha:[password]@172.17.0.3/hadb?charset=utf8mb4: name '_mysql' is not defined (retrying in 3 seconds)
Traceback (most recent call last):
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/MySQLdb/__init__.py", line 18, in <module>
    from . import _mysql
ImportError: libmariadb.so.3: cannot open shared object file: No such file or directory

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 653, in _setup_recorder
    self._setup_connection()
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 933, in _setup_connection
    self.engine = create_engine(self.db_url, **kwargs)
  File "<string>", line 2, in create_engine
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 548, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 118, in dbapi
    return __import__("MySQLdb")
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/MySQLdb/__init__.py", line 24, in <module>
    version_info, _mysql.version_info, _mysql.__file__
NameError: name '_mysql' is not defined

When trying the pymysql driver:

Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:933
Integration: recorder (documentation, issues)
First occurred: 1:24:07 PM (9 occurrences)
Last logged: 1:24:31 PM

Error during connection setup to mysql+pymysql://ha:[password]@172.17.0.3/hadb?charset=utf8mb4: No module named 'pymysql' (retrying in 3 seconds)
Traceback (most recent call last):
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 653, in _setup_recorder
    self._setup_connection()
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/homeassistant/components/recorder/__init__.py", line 933, in _setup_connection
    self.engine = create_engine(self.db_url, **kwargs)
  File "<string>", line 2, in create_engine
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 298, in warned
    return fn(*args, **kwargs)
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/sqlalchemy/engine/create.py", line 548, in create_engine
    dbapi = dialect_cls.dbapi(**dbapi_args)
  File "/snap/home-assistant-snap/295/lib/python3.8/site-packages/sqlalchemy/dialects/mysql/pymysql.py", line 59, in dbapi
    return __import__("pymysql")
ModuleNotFoundError: No module named 'pymysql'

Let me know if there's anything else I can look at for you. Thanks for working so hard on this.

I was able to get it working in a test build by appending the following sections in snapcraft.yaml

python-packages:
  ...
  - mysqlclient

build-packages:
  ...
  - libmariadb-dev
  - libmariadb-dev-compat

stage-packages:
  ...
  - libmariadb3

If you're ok with adding these I can create a pull request. I can also create one on the Github mirror (which seems to be more active).

Let me know. Thanks!

Hi,

Sorry for my late reply, but I struggled to build properly with mysqlclient the day I had some time to look into this (on a vacation with my family, so not so much time until next week). Wheel for mysqlclient failed...

So I started to make a new part for it instead, but if you can successfully build your version, that's great. You can push it to github, yes!

I will probably make separate packages for the various DB's in the future, to prevent the ha-package being very bloated. I will let you know when I do.