Same issue for me. Auto Purge which runs overnight seems to fail and then no more events are written to the database. I have a similar setup Pi4 Hassio writing to a mariadb on a synology NAS.
Okay, I think i worked it out. My particular NAS is on the lower end spec-wise and the auto purge that takes place is taking a long time to complete. I did some manual editing of the database via phpmyadmin and noticed that deleting lines from the events table took ages. it was a 10gb table with 7,000,000 lines. What I’ve opted to do now is run a 3 day purge interval and only include certain domains and specifically exclude a number of event types.
We shall see if that makes a difference.
MariaDB 10 has been updated several times since the original post (currently on version 10.3.21) and there have been a number of minor changes which affect the instructions above.
Step 1
Install MariaB10 and phpMyAdmin - as above.
Step 2
Open MariaDB 10 first.
You will be forced to set a password. New security policy requires all passwords to be at least 14 characters long and contain mixed-case letters, numbers and special characters; it does not allow common passwords, or usernames repeated as passwords. This is the root password that you will use in setting up phpMyAdmin.
Close MariaDB 10 and open it again. Make sure the Enable TCP/IP connection box is ticked (see post by @aicarmic above).
Close MariaDB10 and open phpMyAdmin. Set it up as described in the original post, using user root and the MariaDB 10 password.
Don’t create a new database yet.
Step 3
On the home screen of phpMyAdmin, create the new user first by clicking on the User Accounts tab at the top. Click on Add User Account about halfway down the page. Fill in the login details and tick the checkbox Create database with same name and grant all privileges
When you click GO the account and the database will be created.
Step 4
Assuming that the user/database you created was called home_assistant, your configuration.yaml entry should now look something like this:
Well worth doing if you have a Synology NAS - speeds up the system and extends the life of your SD card.
5 Likes
@bungo63 @Stiltjack
Guys, thank you so much for this! I was always worried a bit about the lifespan of the SD-card and having a quite powerful synology running, this is a perfect setup.
I was abit worried about the setup, but it was a breeze and took only 5 minutes and a reboot without any flaws. And i am not an experienced programmer 
History-calls are now smooth like butter and the Pi-Write-LED is not blinking anymore. 
Thanks again für the idea and the latest update in this thread.
1 Like
Hi Guys, thanks for the guide which is really useful and I’ve followed (a couple of times!). I wonder if you might be able to help with an odd issue I’m experiencing, since migrating the database i’m finding that some entities only show the current state in the History and whenever the state changes the old state is lost and doesn’t show on the bar chart. Additionally none of the default graphs (temp sensors, battery levels etc) are showing any information either.
If I switch back to sqlite on the Pi it works correctly. Any ideas what might be causing that? I’ve upped the log level to debug but not showing any errors and dropped the database and users and setup fresh but still the same, at a loss what could be causing it.
Might be a good idea to wait a bit - looks like everything’s changing in the new release (see release notes in the blog).
@bungo63 and @Stiltjack YOU ARE AMAZING PEOPLE.
Seriously though, thanks for the great writeups - I used the original directions by @bungo6 , but neglected to enable the TCP/IP connection. Could not figure out what was wrong.
To quote Mr. Burns in The Simpsons: ”Excellent”
My Hassio,that I thought was crashing, is finally better then fine again!
Thanks you guys for MariaDB on Synology!
I’m currently using sensor to report DB size, that I found somewhere on the forum:
db_url: mysql://_USERID_:[email protected]:3307/hassiodb?charset=utf8
queries:
- name: 'DataBase size'
query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="hassiodb" GROUP BY table_schema;'
column: 'value'
unit_of_measurement: MB
Recently I noticed, that this sensor reports DB size to be ~50% of what actual occupancy on Synology NAS is (easy to spot, as I have dedicated volume for DB). So for example sensor reported size is 3.7GB, while NAS shows 7.04GB of volume consumption. Could it be result of recent DB schema changes that are not reflected in query used by SQL sensor? Any other idea why such discrepancy?
where do you put this? Any instructions would help me very much!!
Thanks
You should put it directly into your configuration.yaml file. Structure from the top should look like this:
sensor:
# MariaDB Size
- platform: sql
db_url: mysql://_USERID_:[email protected]:3307/hassiodb?charset=utf8
queries:
- name: 'DataBase size'
query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024 / 1024, 2) "value" FROM information_schema.tables WHERE table_schema="hassiodb" GROUP BY table_schema;'
column: 'value'
unit_of_measurement: GB
Obviously you probably already have sensor: section in your file, so this shows at least what should be proper intendation. You should also adjust IP of machine where MariDB is installed (192.168.552.21 in example above) and name you gave your database (hassiodb in example).
Also plese note that I adjusted vs. original units to report in GB and changed rounding to 2 decimals to be more precise whiule using higher unit of measurement.
2 Likes
Thanks mate, i already figured it out. i also have it in MB (/1024 /1024) database is still to small to configure it for GB 
Kind Regars,
Iwan
Before I start messing about with a ‘working’ system can anyone confirms this still works with 0.115.x (I’m on 0.115.2)
Mine’s still running OK. 
1 Like
Hi,
Very silly question but where did you access the tcp screen? I can’t find anywhere a screen where I can tick or enable the TCP/IP. On mariadb page - (supervisor, mariadb addon, configuraition) I can see network “tcp disabled” but whatever I do there nothing changes. ? Thanks
From Synology Web Assistant desktop, go to package centre and installed packages, then click on Mariadb.
I enabled TCP/IP on the NAS, not in Home Assistant.
I am new to this all. Can some explain how to purge the dB? I don’t understand the difference between purge interval and purge days.
I did get this running. So, thanks to those that posted this.
For HA on an rpi I use the Synology DSM’s maria db database. Now a backup of the Synology including maria db is made every night. Then there are brief breaks between the rpi and the database on the Synology. The database sensor does not receive any data for a short time either.
The error messages then look like this:
Logger: homeassistant.components.recorder
Source: components/recorder/__init__.py:441
Integration: Recorder (documentation, issues)
First occurred: 11:57:44 (1 occurrences)
Last logged: 11:57:44
Error in database connectivity during keepalive: (MySQLdb._exceptions.OperationalError) (1927, 'Connection was killed') [SQL: SELECT 1] (Background on this error at: http://sqlalche.me/e/13/e3q8)
Logger: homeassistant.components.sql.sensor
Source: components/sql/sensor.py:146
Integration: sql (documentation, issues)
First occurred: 11:57:47 (1 occurrences)
Last logged: 11:57:47
Error executing query SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant_rpi" GROUP BY table_schema LIMIT 1;: (MySQLdb._exceptions.OperationalError) (1927, 'Connection was killed') [SQL: SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant_rpi" GROUP BY table_schema LIMIT 1;] (Background on this error at: http://sqlalche.me/e/13/e3q8)
The configuration.yaml looks like this:
recorder:
db_url: !secret mariadb_url
db_retry_wait: 120
The 120s for the reconnect are sufficient. However, the first error message remains if the connection fails briefly.
How can I prevent these error messages from occurring?
Probably I have to lower the log severity level for the recorder so that I don’t get any more errors. Or does someone have a better idea?