I kept getting this error host 192.168.1.12 is not allowed to connect to this mariadb server
I fixed it by configuring MariaDB to allow remote connections. It was a pain in the butt because I kept restarting to see if my changes fixed anything. I ended up using MySQL Workbench to test my changes by connecting to the MariaDB on the Synology box
This is the command i use to enable remote connection
GRANT ALL PRIVILEGES ON *.* TO [email protected] IDENTIFIED BY ‘user_password‘ WITH GRANT OPTION;
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…
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.
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.
@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.
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