I just tried with pymsql too and it didn’t work.
@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
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
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.
@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.
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.
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
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?
Prior to running pip3 install mysqlclient, yes.
sudo apt-get install libmysqlclient-dev is run as your ‘regular’ user (usually pi).
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
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.
This is how i got it working with the AIO Installer. Hopefully it’s of any help for you guys
7 Likes
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.
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?
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
Confirmed, same as mine.
With 14 days retention my SQLlite hovers around 450mb.
Does moving to MySQL or Postgresql (Howto: Postgresql on Raspberry Pi All-In-One Installer) make a big difference in performance? Would love to her some experiences.
YMMV, but for me it was night and day. At about 250mb with SQLite, bringing up detail on cards was agonizingly slow and I was purging on a 7 day basis. Almost 15-18 seconds before the graph data appeared.
Now, with a 14 day purge cycle and about 400+mb on MySQL there is about a 2-3 second wait at most.
I switched over to using MariaDB which is running on a different machine in my home network and graphs load much faster now. Also, HA seems a bit snappier too, particularly at startup, but I’m not sure if that is due to the new database or improvements that the devs are making (or both).
My db is in use:
Threads: 4 Questions: 54828 Slow queries: 0 Opens: 54 Flush tables: 1 Open tables: 44 Queries per second avg: 5.040
But when I do: pi@homepi:~ $ cd /var/lib/mysql
I get this: -bash: cd: /var/lib/mysql: Permission denied