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?
@bungo63 Thank you for this guide! I set it up using my QNAP NAS using a very similar procedure. As @Stiltjack mentioned above, MariaDB now requires a strong password creation right away. I had no problems following your original post, modifying as necessary to accommodate QNAP and the new MariaDB. Then I saw users below documented the fixes I already needed to use.
After restarting HA, I immediately lost my logger and logbook. Although not IMO well documented, apparently creating a recorder entry overrides some settings of the ādefault config:ā entry in the configuration.yaml. In my case this seemed to stop both logger and logbook. I added logger: to the configuration.yaml, and it started logging again right away. However, since the default logger configuration is debug, the logs were much more detailed than before. I changed the entry to
logger:
default: info
logbook:
The logger now seems to work fine, but the last logbook entry is from 30 minutes ago despite a recent restart that should have given me many new logbook entries.
I kept trying things as I wrote this, and after many HA restarts the logbook started logging again. I also refreshed the web page, making me wonder if it was somehow a browser cache issue. After refreshing, I had logbook entries going back more than 45 minutes, meaning the logbook had been running, but not properly displaying, during that time. Logger, on the other hand, was definitely not logging, because the logfile time stamp stayed fixed at the time when I first restarted after changing to the MariaDB setup. Whew!
Thanks, this saved me a lot of time!
I was able to get it working very quickly thanks to your explanations.
As other users have been reporting, my config is now much faster and I do not have to keep changing SD cards all the time!
Just installed MariaDB and phpMyAdmin on my Synology RS816 and connected my Home Assistant 2021.2.3 running on a Pi4 with 4gb.
Everything runs must smoother and faster now. Thank you for this tutorial.
I changed some settings concerning authentication and to restrict access.
First of all, thanks for the steps on this. I discovered this shortly after setting up my HA instance on RPi4B last summer and as a solution architect at work I could definitely appreciate the advantages and as an HA user I could definitely appreciate the flexibility in writing queries to and other tools to analyze everything going on within my home.
However, with the winter storm that just hit the U.S. and the major power outages in the Dallas area, I have a new perspective on running a database for state logging on a different server. I wonāt get into the issues I ran into with coordinating shutdown of both a Synology NAS and an HA server off a single UPS - will save that for a post on a NUTS thread - but one issue related to this is that every time power came back up and the NAS and RPi restarted, HA would come up all the way before the NAS was up and had MariaDB running. That meant Recorder was basically brain-dead at a time when I was desperate to capture every piece of sensor data possible including temperature from my crawl-space, my hot water heater closet, etc., water sensors near all plumbing exposed on an external wall, etc. Without doing anything, I could see data at a single point in time but not over time. After the second or third extended outage when I realized what was happening, I just pulled the plug on my NAS and HA and manually started them whenever power returned (after dealing with the really important stuff first like making sure the smart thermostat was working so the house could heat up).
Based on another post, I am thinking about changing my setup so that the most recent day or so of recorder data is handled locally on the RPi with deeper history stored on NAS. However, a couple of concerns:
- The only option I can find for that would be to use InfluxDB. It looks like running that on Synology requires using Docker but thatās not an option on my ds216play.
- Even keeping a limited number of days on the SQLite DB on RPi will still take a toll on the SD card. At best, changing the commit frequency is only apparent way to limit that hit over the long haul.
It would be great if there were a way to have multiple recorder database instances using MariaDB with some things going to both and other things only going to the deep history instance on NAS. So far, I donāt see any sign that there is a way to do that.
As for the local write issue, if I also implemented SSD for the RPi, that wouldnāt be such a concern over time.
I am looking at revamping my UPS configuration, possibly having the HA RPi as master and the NAS as slave so there may be other options but so far I havenāt seen anything in NUTS documentation that would let me delay the HA start-up until MariaDB is up on the NAS. But if I find a way to do that then perhaps my recorder/database concerns are no longer an issue.
Thank you for your guides @Stiltjack and @bungo63!
Iām running Home Assistant on a Raspberry Pi 4 (2gb) with a 32GB sd-card.
With the newest versions at this moment:
- Core 2021.2.3
- Supervisor 2021.02.11
I have multiple ābackup-cardsā just in case, but have to say I never had a problem (running for 9 months now), but also I had excluded a lot of the recorder. Pretty sure this guide did buy all of my cards a lot of extra time
Processor use is about the same around 5% most of the time (again, my recorder almost didnāt record anythingā¦). But the difference in loading history is big for me. And also everything seems to react quicker (changing between tabs and so on).
Now have to look into Grafana on my Synology so I can get a little eye-candy.
Iām not using an RPi for HA, but Iāve made this change to run MariaDB on my Synology NAS anyway. Already things seem a little bit quicker now Iām handing off database duties to a box that isnāt doing a great deal.
Hi, was following your advise, but was not able to find the option āGrant all privileges on databaseā on the mysqladmin page. Was that an actual option or what did you mean by thatā¦ thanks in advance!
Iām not 100% sure but I think the setting I was referring to was under āUser Accounts > Edit Priveleges for a specific user (right side) > Database > Database-specific-privileges > Check all (checkbox)ā. Iām guessing the wording in the UI has changed in the last 3 years
To be honest, I moved off of using a Raspberry Pi for HA and Synology for the DB a very long time ago (~2yrs).
I moved to a NUC with an SSD (everything runs on the NUC) and havenāt looked back. The performance is incredible and no need to set up workarounds to compensate for underpowered (for the task) hardware. I wish you the best of luck and Happy Automating!
1 Like
Thanks man, I finally found itā¦ I was trying to create a user outside of the database, once I selected the database I was able to see the option.
First time I hear about the term NUC thoughā¦ a quick google search gave me some details hehe but being curious on what model youāre using? I could give it a try in the future
I wasnāt aware of them at the time either. One of my colleagues had purchased a NUC as a HTPC/secondary gaming machine and wasnāt happy with the graphics performance. He mentioned wanting to sell it and so I wound up with a NUC8i5 with 8GB RAM and an SSD. It is suuuuper over-powered for running HA but that just means Iāll never have to worry about upgrading hardware.
I run the āIntel NUCā image of āInstall Home Assistant Operating Systemā, have ~700 entities/sensors/etc., 22 addons installed (most of them running all the time), more than a few custom integrations from HACS, and the CPU reports as basically idle, 24/7.
That solved my problem, connection from home assistant in a vm now talking to maria10 database on synology now working