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.
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.
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.
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 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 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.
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
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!
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"
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.
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:
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.