@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
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
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
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
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).