Large homeassistant database files

Configuration
61

@philhawthorne It seems that I’m in the same situation as @rpitera.

I have a MariaDB instance on my nas so it is not in localhost, but I didn’t have any luck.
I tried with the user that I created for HA db and has full access rights in the db, I tried with root too.
When I’m trying remotely from console in another machine I can login in the db without any problems.

If I activate the recorder configuration with the db_url in HA the HA hangs and I don’t see anything related in the HA log.

Edit: I did try again and now HA doesn’t even start, no info at the log.

62

Hmm I don’t have any experience with MariaDB to be honest. Wish I knew of some magical option. The fact that you can access the DB from a remote host seems to tell me that you should be able to access it from Home Assistant. Perhaps there is indeed a bug somewhere in the recorder component. Now you guys are making me doubt if my own MySQL setup is actually working :wink:

@rpitera I noticed in the other thread created by @dimmanramone there was some other dependencies you might be able to try installing

sudo apt-get install libmysqlclient-dev
63

I’m using Postgres, its working flawlessly.

64

MariaDB and MySQL should work exactly the same way. I had the dependencies installed from the beginning, the only thing that I haven’t tried is to use pymysql.

65

I just tried with pymsql too and it didn’t work.

66

@dimmanramone, @rpitera Reading the posts I’m not sure this is the problem but… in the home-assistant configuration file the IP address is the IP address where the MySQL database is running but in MySQL the IP address is the IP address your connecting from.

i.e. if home-assistant is running on 192.168.1.1 and MySQL is on 192.168.1.2

home-assistant

db_url: mysql://hassuser:[email protected]/hass

MySQL

CREATE DATABASE hass;
USE hass;
CREATE USER 'hassuser'@'192.168.1.1' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON hass.* TO 'hassuser'@'192.168.1.1';

and for MySQL it matches on the string so as I think @philhawthorne was saying, ‘localhost’ is different to ‘127.0.0.1’ even though the IP address for localhost is 127.0.0.1

67

I had problems to connect the mySQL DB on my NAS.
My configuration
recorder:
purge_days: 60
db_url: ‘mysql://[email protected]/smarthome’

I tried different things.

For my one thing solved the Problem.
I had to install: pip3 install mysqlclient inside the virtual environment!

I hope this helps not only on my config.
Stefan

3 Likes
68

Sorry for the late replies - I’ve been down with a bad sinus infection that had me laid up in bed for two days. I came back to over 300 emails and numerous missed posts here and trying to catch up.

@rotor - This solved my problem and makes perfect sense. I don’t know why I didn’t think of it before - without this dependency how else would it connect. And it makes sense that it wasn’t included in the distro as the standard method is SQLlite. I still think that HA should capture this error in some way and list the dependency in the error.

@dimmanramone - Did you install pymysql in the venv? I’m guessing this is the problem; without a client in the venv, HA has no way to connect. Again, an error here from HA would help.

I’m wondering if HA should be raising errors in these cases? It seems to me that supporting another db also brings on the responsibility of dependency checking and raising the appropriate error if those dependencies aren’t met, but I’m not a dev and so my thinking may be off. I’d really like to see a core dev like @balloob weigh in on this before I enter a issue for it as they may feel that it is third party support and not their responsibility. If not, at least the docs should be updated to reflect this.

But I now have a fast load on all my detail cards AND my data graph! Yay! Much thanks to all for your help and special thanks to @rotor for the ultimate solution.

69

@rotot I installed mysqlclient inside the virtual enviroment but unfortunatelly I did not have any luck with that either.

@rpitera I haven’t tried to install pymysql in the virtual enviroment but I could try that to. It worths a try even if this is going to cause problems with my ha installation it doesn’t matter, I still have to reinstall everything because the usb ports died in my rpi3 and I bought a new one to replace it today.

70

I know that installing the client in my VENV was what fixed things for me. I’m resuming work on a write up on the steps I took and will post something soon.

71

Hi All,
Seemed to get this installed without too many issues on a HASSBIAN image. Only been running for approx 10 mins so far though.
Details below:

Complete the following outside the venv:

sudo apt-get install libmysqlclient-dev
sudo apt-get install python-dev python3-dev
sudo apt-get install mysql-server
** create root password when prompted **

mysql -u root -p  
** enter root password **

CREATE DATABASE hass_db;
CREATE USER 'hassuser'@'localhost' IDENTIFIED BY 'XXX';   <-------XXX is your password
GRANT ALL PRIVILEGES ON *.* TO 'hassuser'@'localhost';
FLUSH PRIVILEGES;

Enter the venv:
sudo su -s /bin/bash homeassistant
source /srv/homeassistant/bin/activate
pip3 install --upgrade mysqlclient


Add the following to your configuration.yaml file:
recorder:
  db_url: mysql://hassuser:[email protected]/hass_db
14 Likes
72

For the ones using the All in one installer and/or use HASS in a virtual env; Would you also need to start with:

Change to hass user: sudo su -s /bin/bash hass
Change to virtual enviroment: source /srv/hass/hass_venv/bin/activate

Or is that not needed?

73

Prior to running pip3 install mysqlclient, yes.

sudo apt-get install libmysqlclient-dev is run as your ‘regular’ user (usually pi).

74

I did the following steps in a new all in one installer installation and still doesn’t work.

sudo apt-get install libmysqlclient-dev
su -s /bin/bash hass
source /srv/hass/hass_venv/bin/activate
pip3 install --upgrade mysqlclient

75

Sorry I didn’t have time to get to finishing what I started here due to illness. Thanks @switched for doing a wonderful job of laying out the steps.

76

This is how i got it working with the AIO Installer. Hopefully it’s of any help for you guys

7 Likes
77

Nicely done - I like your idea of keeping these configs and commands on your github page for reference as well.

For me, the whole key was not having mysqlclient installed in my HA venv. Everything else was straightforward but this was the missing piece.

78

I’ve followed your guide, and I think it works.
Is there a way to check if the new db is used?
And what is the location of the db?

79

If you enter this:
$ mysql -udbname -p
$ status;

You should get something like this:
Threads: 3 Questions: 63484 Slow queries: 0 Opens: 51 Flush tables: 1 Open tables: 44 Queries per second avg: 0.829

Thats how I made sure the DB was in use. And if you deleted the .db file from you hass dir it should not be recreated on startup.

I think the DB is located at its default location /var/lib/mysql

80

Confirmed, same as mine.