Synology add-on MariaDB version

Hi,
since updating to the 2023.02 version, it gives me a critical error on the mariaDB version that I need to update to a 10.5.17 or later.
I use mariaDB as an addon on my synology NAS, but there is no newer version available (10.3.32)

Anyone find a good solution for this?

I looked into moving to the home-assistant add-on MariaDB, but then I will lose all statistics, mainly the energy statistics from the last 2 years.

3 Likes

Synology is horribly bad about updating packages. Like colossally bad. I finally just moved my MariaDB setup to a Docker container running on the Synology so that I can easily update as version updates come out. If youā€™re not familiar with Docker, that road will take a bit of time. In theory, you can export your current database and then reimport it into the Docker one, and the MariaDB would still have the same IP and port number.

Itā€™s probably worth noting that I donā€™t use MariaDB with Home Assistant, so Iā€™m not sure what the exact steps would look like to do this.

1 Like

Same here. However, I noticed in the issue that the problem was introduced in version 10.5.9, so since we are on 10.3.x we might be alright?

This is great, any suggestions about which image? I use a lot of the linuxserver images, but I see that MariaDB also has an own image. Are there any pro/cons against either one?

Iā€™ll go try this and post back some steps how to get this working.

Same issue than veenstrahiem1.

I have updated Maria DB to 10.3.28 0070 (the last one proposed by Synology).

Then MariaDB stopped working (impossible to launch nor repair the packet).

Had to reindtall from scratch with new database so I lost all my history and espacially the Energy ones :face_with_symbols_over_mouth: I found no way to secure a copy of the database file (canā€™t find MariaDB files on the Synology)

Even worst, I can connect again to MariaDB thru phpmyadmin but HA doesnā€™t want to connect anymore
(log: sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (1130, ā€œHost ā€˜Archer_C7ā€™ is not allowed to connect to this MariaDB serverā€)
(Background on this error at: Error Messages ā€” SQLAlchemy 1.4 Documentation)").

I do not understand why.

Well I seriously consider to buy a SSD, connect it directly to the Pi and use the local HA database because I do not see any benefit of MariaDB and the database management is not userfriendly at all, at least on a Synology.

I use the official MariaDB image.

When you say critical error does it not function? Or is it just complaining that you should upgrade?

I have a critical error displayed, and I noticed multiple times a flatline in history/recorded values.
Only after a reset will it work again. At the moment 2-3 a day a reset is needed.
I was unable to export the databases from the synology NAS and move it to the internal mariadb on the home-assistant, mainly due to file restrictions. My database is around 3Gig.
So still prefer a database separate from homeassistant.

1 Like

I can confirm that this is working (updating MariaDB on Synology with Docker).

You can follow this very well written guide, about installing MariaDB (latest version!) in a Docker container on your Synology NAS: Setting up MariaDB using Docker | GH2home

I was even able to export the existing HA database from the native Synology app and import it into the new Docker instance of MariaDB. It went more or less automatically with the free version of MySQL Workbench: https://dev.mysql.com/downloads/windows/installer/8.0.html

You can login with:
[email protected]
Z6oenYBiUtqaP!

Then do a Database - Migration Wizard.
Because the native app is listening on a exotic port (3307) vs the original port (3306), you can have them both running at the NAS at the same time.

After the migration you have to change your mqsql-configuration in Hassio and then youā€™re good to go. No more critical error!

5 Likes

Thank you, I have 2 separate HASS running 2022.12.7 on 2 NAS, looks like Iā€™m blocked on upgrade until I migrate MariaDB over to a docker container.

Hereā€™s a script I run through Synology task manager to create a backup of the database. My database name is ā€œhaā€. Iā€™ve tested restoring the backup to a separate database and it works.

now=$(date +"%m_%d_%Y")
 /volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/mysqldump --user=root --password=Pwd ha > "/volume1/mariadb_rage/backup/mariadb_$now.sql"
1 Like

Thanks,
I was able to get the database dump working. First I kept getting an -acces denied using user:root-
After some googling I logged in directly to the Synology using SSH and had to make the command: mysqldump -u root -p homeassistant
Then I was asked for the root password and it worked. I now have a DB dump.
Next step: installing Docker with new MariaDB and phpmyadmin.

1 Like

Yesterday evening I also fixed the Docker install, and installed MariaDB and phpmyadmin as containers. I used the bridged network with port 3306 as forwarding-port. This is possible with the old mariadb from the synology store still operational because that uses port 3307. When installing the phpmyadmin that is linked to the mariadb container, and in the setting I used the docker-internal ip-adres as connection.
phpmyadmin uses the port 8080 on the NAS to get to the container.
Go to the phpmyadmin page: http://nasipadres:8080 and login, create an empty db called homeassistant

After the install, I used SSH to login the synology and go to the Mariadb folder:
cd /volume1/@appstore/MariaDB10/usr/local/mariadb10/bin/

from there use the mysqldump command with the following command:
mysqldump -uroot -p homeassistant > /volume1/backup/mariadbexport.sql

This generates a file on my NAS in the folder backup which can be used to import it in the docker container. Give the command to import it:

sudo docker exec -i MariaDB mysql -uroot -p homeassistant < /volume1/backup/mariadbexport.sql

MariaDB is the container name, homeassistant is the empty db created earlier.
I used -p so that you need to enter your root password when is asks for it.

Restart your mariadb container to get it using the new files you just imported.
Then I changed the database link in my homeassistant configuration.yaml and restarted homeassistant. After that, all is looking good.

Thanks to @PeteRage and @tomster for the links.

7 Likes