I’m not really seeing anything in the logs to identify a problem, however I have the MariaDB app on Synology back up each night about that time. I wonder if it shuts the server down while backing up. I’ll disable that back for tonight to see if it continues to happen.
Yup that’d cause it, in order to back it up it has to free the file lock so it shuts down the service, if you want to back it up you could setup a mirror database on your NAS and have it replicate nightly.
1 Like
Yup that was it! Everything working as expected.
Thanks!
Hi All,
A little late to the party , but I just moved the database to a MySQL database hosted on my network on a QNAP NAS.
I have HA in VirtualEnv on a pi and the user I use is hass for HA.
Here are the steps I took to get it working:
-
Create user in MySQL and create database (I used the same name ) and grant all privileges to that user on the database on the QNAP.
-
On the Pi (as pi user) run:
sudo apt-get install libmysqlclient-dev -
change to hass user:
sudo su -s /bin/bash hass -
Activate the VirtualEnv:
source /srv/hass/bin/activate -
Install the mySQL client libraries
pip install mysqlclient -
Exit the virtual environment and edit the configuration.yaml file:recorder:
db_url: mysql://ha_db:[email protected]:3306/ha_db?charset=utf8
purge_days: 14 -
Restart the HA services.
It worked for me 
Really quick now to load the history etc. I also deleted the original database to get back 5GB of data on the SD card.
2 Likes
So you have the database on another computer not the one running HASS right? It writes via the network to the database located on the other computer not on the SD card of the RPI? Do I understand it correctly?
Also I’d be interested on how to do the same stuff for InfluxDB/Graphana. (like having it on a separate single board computer).
Yep it is on a QNAP NAS drive over the network. Performance seems fine
Resurrecting this thread as I am finding that my home-assistant_v2.db is getting quite large. I’ve read several threads and did not find an answer that would match my needs. Although I could go down the path of installing some other databases, for now my needs are simple and could be quite satisfied with the sqlite that’s on the box by default.
Right now, my database is 1.35 GB in size. Two days ago, I’ve added the “purge_days: 1” to the recorder section of my configuration file it looks something like this:
recorder:
purge_days: 1
I haven’t seen a decrease in file size and just keep growing day to day. So my question is two fold.
Anyone had any issues with purge_days? Does it really work and when would the changes on the db file take place (once a day?) I’ve also rebooted the pi several times just to see if the action would take place at startup and since I made the changes two days ago, I would have assumed that the changes would have taken place by now.
Second, is there a way to manually purge the database? I had tried in the past to delete the file and recreate a blank file, but this never populated with new information.
Any hints/guidance would be appreciated
Deleting the file and restarting hass, creates a new empty database file.
Thanks sendorm, that worked this time. Most likely because I stopped the HA Service first, deleted the db file and restarted. Still seems to be populating quickly, I will see what I can do to exclude some items from being recorded. Still wondering if the purge_days will be working or if I will have to delete the file every so often.
Hi @berniebl, configure the logger with:
logger:
default: warn
logs:
homeassistant.components.recorder: debug
and you will see what’s going on with the recorder.
By default it purges every two days, resetting the timer on HA start.
Thanks @VDRainer I will definitely do that.
I wasn’t aware of the purge time (every 2 days) that’s really good to know. I am usually in the habit of rebooting my system after I make any significant changes just to make sure that it comes up fine, so I presume that I kept resetting the clock. Thanks for you input.
Here’s a closed issue about that problem, and a way you can set (don’t tell the devs 
) the purge interval.
Can you provide some more details on the above process, tried attempting this setup but not sure how/where to create user/db.
I tried using phpMyadmin but can’t login.
Thanks.
I used PHP admin on the QNAP (I needed to install that on the QNAP as well as MySQL). I did need to google the default password etc
Did you use localhost as the host name?
I’m getting the following recurring error, I even tried using the root account:
2017-08-05 13:30:02 ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (_mysql_exceptions.OperationalError) (1130, "Host 'Hassbian_Pi3.home' is not allowed to connect to this MariaDB server") (retrying in 3 seconds)
Hi
Is that on a QNAP? i have not seen that screen before?
Yes phpAdmin on Qnap.
Works great, thanks.
1 Like