Works like a charm. The only thing I had to change was to allow TCP/IP connection on the MariaDB 10.
Thanks a lot.
Yep, thatās just because of the new version of MariaDB.
Iām getting these errors in my home-assistant.log:
ERROR (Recorder) [homeassistant.components.recorder] Error during connection setup: (MySQLdb._exceptions.OperationalError) (1815, āInternal error: rename index to addā)
[SQL: CREATE INDEX ix_events_time_fired ON events (time_fired)]
(Background on this error at: Error Messages ā SQLAlchemy 2.0 Documentation) (retrying in 3 seconds)
Anyone have any suggestions for what to do here? I tried reading about the error but itās over my head. I also checked under phpMyAdmin and the new database structure seems to have loaded, so Iām not sure what the problem is.
Hi! I think I am having a connection problem. It might be this of allowing TCT/IP connections, could you explain how did you enable it? I do not find a setting with something like that.
Thanks a lot!
Thanks for the helpful guide @bungo63! I initially couldnāt get it working, but after some debugging I discovered that for some reason the Maria DB āappā on my NAS had the TCP connection disabled by default. Once I checked āEnable TCP/IP connection on port 3307ā everything came up. Hopefully this helps others that encounter this issueā¦
4 Likes
bloody awsome post. took me long time to get it working because I made a typo and couldnt find it (read now at age of needing glasses).
Would have taken me under 40mins otherwise with no experience in dbās.
Log and History fly nowā¦
Cheers and thankyou
To all the people that have sent their thanks, thank you so much. Iām glad that I have been of some help and been able to give something back to an amazing and smart community. Thanks also to those that have fielded questions. Iāve not been doing anything with Hassio for a while now as it stealing too much time and there was too many gadgets beginning to clutter up the house. Now that I am self isolating (as a precaution) I may return to it all again!
Stay well everybody!
Just found an easier way to do this if you have a NAS that supports docker, just use the mariadb docker container, and then connect to it via HeidiSql to setup the users. This way you donāt need all the phpmyadmin stuff installed too
I have RPI4 and MariaDB on Synology over the network. I set the recorder 10/1 and after 10 days stopped writing some data. The database grew to 3.7 GB. Manually running a command via recorder.purge does not work. Now I delete DB manually and it takes stranÅ” long because there were over 5 million lines. How do you solve the recorder if you use MariaDB on NAS server? There are lots of postings on the internet that it doesnāt work for anyone and people have problems with it. Thanks for the reply.
1 Like
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.
1 Like
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