Error in setting up recorder with MySQL

Configuration
1

I am not able to use recorder with MySQL. Despite successful installation of MySQL server, client, elements of HASS for connection with MySQL, passwords, connection from localhost and not only, etc…
I’m getting the following in the syslog:

Dec 14 22:58:06 raspberrypi hass[17359]: File "/home/hass/.homeassistant/deps/sqlalchemy/dialects/mysql/mysqldb.py", line 110, in dbapi

Line 110 in this file refers to:

return __import__('MySQLdb')

I’m guessing Python doesn’t have this package?
Any ideas will be appreciated.

2

Same here. Following the documentation but it did not help.

Jan 18 18:44:30 homeassistant hass[31560]: Exception in thread Thread-9:
Jan 18 18:44:30 homeassistant hass[31560]: Traceback (most recent call last):
Jan 18 18:44:30 homeassistant hass[31560]: File "/usr/lib/python3.4/threading.py", line 920, in _bootstrap_inner
Jan 18 18:44:30 homeassistant hass[31560]: self.run()
Jan 18 18:44:30 homeassistant hass[31560]: File "/srv/homeassistant/homeassistant_venv/lib/python3.4/site-packages/homeassistant/components/recorder/__init__.py", line 204, in run
Jan 18 18:44:30 homeassistant hass[31560]: self._setup_connection()
Jan 18 18:44:30 homeassistant hass[31560]: File "/srv/homeassistant/homeassistant_venv/lib/python3.4/site-packages/homeassistant/components/recorder/__init__.py", line 303, in _setup_connection
Jan 18 18:44:30 homeassistant hass[31560]: self.engine = create_engine(self.db_url, echo=False)
Jan 18 18:44:30 homeassistant hass[31560]: File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/__init__.py", line 387, in create_engine
Jan 18 18:44:30 homeassistant hass[31560]: return strategy.create(*args, **kwargs)
Jan 18 18:44:30 homeassistant hass[31560]: File "/home/homeassistant/.homeassistant/deps/sqlalchemy/engine/strategies.py", line 80, in create
Jan 18 18:44:30 homeassistant hass[31560]: dbapi = dialect_cls.dbapi(**dbapi_args)
Jan 18 18:44:30 homeassistant hass[31560]: File "/home/homeassistant/.homeassistant/deps/sqlalchemy/dialects/mysql/mysqldb.py", line 110, in dbapi
Jan 18 18:44:30 homeassistant hass[31560]: return __import__('MySQLdb')
Jan 18 18:44:30 homeassistant hass[31560]: ImportError: No module named 'MySQLdb'
Jan 18 1
3

Fixed:

pi@homeassistant:~ $ sudo -i
root@homeassistant:~# su homeassistant
homeassistant@homeassistant:/root$ cd /srv/homeassistant/
homeassistant_venv/ src/                
homeassistant@homeassistant:/root$ cd /srv/homeassistant/homeassistant_venv/
homeassistant@homeassistant:/srv/homeassistant/homeassistant_venv$ source bin/activate
(homeassistant_venv) homeassistant@homeassistant:/srv/homeassistant/homeassistant_venv$ pip3 install pymysql
 Collecting pymysql
  Downloading PyMySQL-0.7.9-py3-none-any.whl (78kB)
    100% |████████████████████████████████| 81kB 627kB/s 
Installing collected packages: pymysql
Successfully installed pymysql-0.7.9

And changing the URL to:

mysql+pymsql://...
4

While this is working a maybe better approach is to just install mysqlclient, a Python 3 compatible mysql-python fork. Because (as I understand) this package uses c-extensions and is thus theoretically faster then the pure python pymsql package.

TL:DR: Run in your hass virtual environment:

pip install mysqlclient

and leave your config at

db_url: 'mysql://user:psw@localhost/hass'
2 Likes
5

It works. Another pull request :wink:

1 Like
6

Nevermind, found it:
sudo apt-get install libmysqlclient-dev

In the virtual environment:
pip3 install mysqlclient

Trying to use mysql on my NAS.
HASS errors out with:

Feb 15 22:46:06 Condor1 hass[4805]: Exception in thread Thread-9:
Feb 15 22:46:06 Condor1 hass[4805]: Traceback (most recent call last):
Feb 15 22:46:06 Condor1 hass[4805]: File "/usr/lib/python3.4/threading.py", line 920, in _bootstrap_inner
Feb 15 22:46:06 Condor1 hass[4805]: self.run()
Feb 15 22:46:06 Condor1 hass[4805]: File "/srv/hass/hass_venv/lib/python3.4/site-packages/homeassistant/components/recorder/__init__.py", line 211, in run
Feb 15 22:46:06 Condor1 hass[4805]: self._setup_connection()
Feb 15 22:46:06 Condor1 hass[4805]: File "/srv/hass/hass_venv/lib/python3.4/site-packages/homeassistant/components/recorder/__init__.py", line 311, in _setup_connection
Feb 15 22:46:06 Condor1 hass[4805]: self.engine = create_engine(self.db_url, echo=False)
Feb 15 22:46:06 Condor1 hass[4805]: File "/home/hass/.homeassistant/deps/sqlalchemy/engine/__init__.py", line 387, in create_engine
Feb 15 22:46:06 Condor1 hass[4805]: return strategy.create(*args, **kwargs)
Feb 15 22:46:06 Condor1 hass[4805]: File "/home/hass/.homeassistant/deps/sqlalchemy/engine/strategies.py", line 80, in create
Feb 15 22:46:06 Condor1 hass[4805]: dbapi = dialect_cls.dbapi(**dbapi_args)
Feb 15 22:46:06 Condor1 hass[4805]: File "/home/hass/.homeassistant/deps/sqlalchemy/dialects/mysql/mysqldb.py", line 110, in dbapi
Feb 15 22:46:06 Condor1 hass[4805]: return __import__('MySQLdb')
Feb 15 22:46:06 Condor1 hass[4805]: ImportError: No module named 'MySQLdb'

Tried to install the mysqlclient in my virtuall environment:
This errors out with:

hass@Condor1:/home/pi$ source /srv/hass/hass_venv/bin/activate
(hass_venv) hass@Condor1:/home/pi$ pip install mysqlclient
Collecting mysqlclient
  Downloading mysqlclient-1.3.9.tar.gz (81kB)
100% |████████████████████████████████| 81kB 1.2MB/s
Complete output from command python setup.py egg_info:
/bin/sh: 1: mysql_config: not found
Traceback (most recent call last):
  File "<string>", line 1, in <module>
  File "/tmp/pip-build-4_26ji9c/mysqlclient/setup.py", line 17, in <module>
    metadata, options = get_config()
  File "/tmp/pip-build-4_26ji9c/mysqlclient/setup_posix.py", line 44, in get_config
    libs = mysql_config("libs_r")
  File "/tmp/pip-build-4_26ji9c/mysqlclient/setup_posix.py", line 26, in mysql_config
    raise EnvironmentError("%s not found" % (mysql_config.path,))
OSError: mysql_config not found

----------------------------------------
Command "python setup.py egg_info" failed with error code 1 in /tmp/pip-build-4_26ji9c/mysqlclient/

Any hints?

7

I am having the same issue.

8

Same issue for me,

Command “python setup.py egg_info” failed with error code 1

I simply can’t install mysql package and get recorder to work…

9

Same here,

Collecting mysqlclient
Downloading https://files.pythonhosted.org/packages/ec/fd/83329b9d3e14f7344d1cb31f128e6dbba70c5975c9e57896815dbb1988ad/mysqlclient-1.3.13.tar.gz (90kB)
100% |████████████████████████████████| 92kB 1.1MB/s
Complete output from command python setup.py egg_info:
/bin/sh: 1: mysql_config: not found
Traceback (most recent call last):
File “”, line 1, in
File “/tmp/pip-install-sy8hhwth/mysqlclient/setup.py”, line 18, in
metadata, options = get_config()
File “/tmp/pip-install-sy8hhwth/mysqlclient/setup_posix.py”, line 53, in get_config
libs = mysql_config(“libs_r”)
File “/tmp/pip-install-sy8hhwth/mysqlclient/setup_posix.py”, line 28, in mysql_config
raise EnvironmentError("%s not found" % (mysql_config.path,))
OSError: mysql_config not found

----------------------------------------

Command “python setup.py egg_info” failed with error code 1 in /tmp/pip-install-sy8hhwth/mysqlclient/

Solved: just do sudo apt install libmysqlclient-dev

then go back into the venv and pip3 install -u mysqlclient

10

I had to do this to get mysql to work on a pi HA.

In HA

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

on the pi

sudo apt install default-libmysqlclient-dev

then go back into the venv

pip3 install mysqlclient

I also had to change my mysql user to not use SHA256 password

 ALTER USER 'hass' IDENTIFIED WITH mysql_native_password BY 'hasspassword';

I should also note that this still works (can’t remember what was needed for it to work)

recorder:
  db_url:  mysql+pymysql://hass:[email protected]/WORKBASE
4 Likes