When viewing the contents of my HASS database in my MariaDB instance, it looks like the purge_days value is respected. I have mine set to 14, and the oldest entry in my database is from the 18th of September.
Good to see others were also having the same issues as me!
As I’m running Home Assistant in a Docker container, I spun up another container for MySQL. Incase anyone is interested, here’s the commands I used
docker run --net=host --restart=always --name mysql -d \
-e 'DB_USER=hass' -e 'DB_PASS=pass' -e 'DB_NAME=homeassistant' \
sameersbn/mysql:latest
My configuration YAML file
recorder:
purge_days: 60
db_url: 'mysql://hass:[email protected]/homeassistant'
I have noticed my home_assistant_v2 file being touched. But at the moment that was a week ago when the MySQL server crashed for some reason.
6 Likes
Is there anyone that can write down a 'how do I use MySQL instead of SQLlite on an RPI AIO installer? I think many would be interested in doing this but don’t understand how/what to do step by step.
I now have a db of 720mb that build in a months so need to start to take some action.
Give me a bit of time and I’ll write something up. I’m about to do this tonight or tomorrow and I’ll put my instructions up here.
2 Likes
OK, so the main tow reasons for me considering a move to MySQL were the size of the SQLlite db and the r-e-a-l-l-y long times it took to load either the history page or to look at a card object with history on it.
I tried setting up MySQL and got it installed, created the db and the user, then gave the user full rights to the db and used this info to set up the configs in HA to point to the new db host. Everything worked and the recorder component initialized without a hitch. HA loaded faster than ever so I thought I was doing great. I was going to go back and document everything and do a write up.
But then I loaded the HA GUI and tried to look at an object, expecting to see it pop right up with a graph…
Nothing. It just sat there with the “wait” animation swirling forever. Went to the history page and same thing.
I know MySQL is working correctly as there was no errors from HA but something is obviously wrong here.
I went back, commented out the lines to go back to the standard db setup and renamed my original db to force it to create a new one. I’m back up and obviously faster with a new, clean db but I am at a loss for why the move to MySQL not only didn’t improve my situation but actually made it worse.
Any thoughts? I didn’t bother writing up my instructions because at this point I’m not sure if I did something wrong. MySQL, libmysqlclient-dev and mysqlclient installs executed without an error. No errors in HA logs. So I have to assume everything is working correctly. I know that if I used the wrong user credentials or didn’t give proper perms to the user, HA would have objected but I saw no issues in the logs.
1 Like
Hmm have you tried since renaming that old SQLLite file?
I’m wondering if MySQL was working, but when you clicked that object, HASS saw there was an SQL lite DB file and tried to load the history from that, instead of MySQL.
I didn’t try that but will rename the new one and give it a shot and let you know.
EDIT:
Same results. When I use SQLlite, even if HA has only just started, clicking on an object brings up the detail card and a state history graph. With MySQL, nothing. Just sits there with the processing animation.
If I had phpSQL installed, I’d take a look and see if the db was even being written to but I don’t know enough SQL to check it from the command line.
OK so I looked up some SQL commands and although the db is there in SHOW DATABASES; when I issued the following commands (the db name is “hass”):
USE hass;
SHOW TABLES;
I get the result “Empty set (0.00 sec)” so it doesn’t appear that HA is writing to the db at all and hasn’t even created the tables and schema. If this is the case though, it should have raised SOME kind of an error but I see nothing in the logs.
@rpitera I also tried to connect my recorder component to a mariadb instance with no luck.
I created a db and a user for ha, made the configuration in recorder but it doesn’t seem to work.
Database is totally empty and with no tables. I don’t get any error on home assistant log.
Did you manage to make it work?
I had the same experience as you @rpitera
I noticed a light kept turning on every time I restarted HASS and realised the database wasn’t quite working, it was pulling in an old state. Unfortunately I don’t know what I’m doing and was waiting for you to come back with a write up so I could see where I went wrong. I had thought perhaps I might need to force the database location somewhere more accessible but it was a stab in the dark.
@dimmanramone No and I saw your post; was going to comment on my situation in it but didn’t want to hijack your post. 
@BarryHampants I was going to prepare a nice write up but it seemed pretty pointless if it wasn’t actually working so I am in a holding pattern. I have MySQL and the dependencies installed and have a working SQL server and db, but unless I can get HA to write to it, I don’t see the point in doing a write up yet.
Maybe @jbardi or @fanaticDavid will check in as they both seem to have working systems.
I do have a working setup with a MariaDB instance but it’s been a while since I set it up. I think it was mid July or something, shortly after the feature was released. At this point, I don’t quite remember doing anything specific to get it to work, other than what’s in the docs. The hardest bit for me was getting used to phpMyAdmin again to set up the user for HASS, as that user needs access rights to the HASS database in the MariaDB instance.
@fanaticDavid Is your MariaDB instance in the same host as HA? Can you check the hass user in phpMyAdmin the access right for the user and if you have defined host for the user?
my database size is 0 mb … i disabled logbook and history and recorder then no databases are generated at all … but you also don’t see any graphs for sensors. I use emoncms for the data & graphs that i need. This is handy if you only want to use home assitant to control devices and see their current state but have no need for any kind of historical data
I set up my MySQL instance with a separate db for HASS and a HASS user with full rights to that db. But got nothing.
My MariaDB instance is running on my Synology NAS. In phpMyAdmin, the HASS user has the following access rights to the HASS db:
Is it the same user that you have in your HA installation? or just a db user?
I’m not sure I understand your question but my MariaDB has 2 user accounts: the default user account which has access to everything (and has nothing to do with HASS), and then the user account I created for HASS which only has access to the HASS db. The credentials of that account is what I put in my db_url in my HASS configuration.
1 Like
In my case, the credentials I used in the URL in configuration.yaml was the user and pass for my db. I have one user with full rights to the db and that was what I used.
Is there someone that has a working mySQL setup?