Issues with firefly-iii database after backup restore / upgrade (Docker)

[Styleflix]

Hello,
I am looking for help about my firefly-iii installation and a current migration I want to do.
This scenario is rather specific but I thought I'll give it a shot anyway.

To give you an understanding of what my situation is, here are some deatils:

Goal:
Migration of docker-based firefly-iii instance from one debian server to antoher.

Approach:
As per documentation, creation of an docker volume backup. After that, fresh installation of firefly-iii on the new machine and then a restore of the backed up volumes (https://docs.firefly-iii.org/how-to/firefly-iii/advanced/backup/)

Issue:
After I first restored the backup data on the new host, the database wouldn't start correctly. I checked the logs and system configurations and found out, that I made a big mistake, when I set up firefly-iii on the old machine, 4 years ago. Due to a mistake in the update script I wrote, the database engine docker container has never been updated and is still running MariaDB version 10.4.15.

What I tried:
I tried first upgrading the database container on the old server to the current version 11.4.5. Then create a new backup and then restore it on the new server.
This resulted in two new problems.

1: While firefly seems to run normally on the old server, after the database upgrade from 10.4 to 11.4, the database container logs are full of messages saying:

Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255)
Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').

So I am not sure if everything is in order.

2.: When restoring a backup of the upgraded database version (11.4) on the new server, the database still fails to start correctly.
The logs are saying:

Could not access the database: SQLSTATE[HY000]: General error: 1877 Table `mysql`.`innodb_table_stats` is corrupted. Please drop the table and recreate. (Connection: mysql, SQL: select count(*) as aggregate from `users`) 

To be safe, I discarded the installation on the new server and went back to a snapshot on the old server from before the db-upgrade.
Now I am trying to figure out

1. How to upgrade my database engine without possibly corrupting the database
2. How to migrate my firefly-iii installation to the new server and get it running there

I would be very happy for any ideas on how to proceed.
Thank you!

[Styleflix]

So, after I managed to solve the issue on my own, I wanted to post my solution in case anyone else stumbles across the same problem.

The main issue was a very outdated database (10.4.15) and incompatibilities to newer DB engine versions due to structual changes that came with version upgrades of mariaDB.

So, thats what I did:

• (stop the docker containers on the old server)
• (make a volume backup of the containers and transfer them to the new server)
• modify the docker compose file to manually use the oldest avaliable version of yobasystems/alpine-mariadb (10.6.11)
• start all firefly containers and stop them again
• (restore the docker volumes)
• only start the DB container
• manually login to the database container shell (docker exec -it CONTAINER sh)
• (recover root user login because I lost the password, by creating a new temporary container with disabled mysql authentication and attach the database volume, then alter root user with new password)
• authenticate as root against the database and execute upgrade (mariadb-upgrade -u root -p)
• I then received several errors saying "Cannot load from mysql.proc. The table is probably corrupted"
• recreate sys database (first: "DROP DATABASE sys;", second "mysql_upgrade --force --upgrade-system-tables -u root -p"
• stop database container
• modify the docker compose file to use the latest version of yobasystems/alpine-mariadb and start database container again
• manually login to the database container shell (docker exec -it CONTAINER sh)
• authenticate as root against the database and execute upgrade again (mariadb-upgrade -u root -p)
• start the rest of firefly containers and check logs and functionality

[JC5]

Hi Stylefix, thanks for sharing your answer! I hope others will be able to learn from it.

[mzielinski]

I couldn’t start version 11.x when the database was version 10.x. I believe I’ve made it a bit easier by doing the following:

• I created a database dump file from version 10.x (from docker): mysqldump -u root -p --all-databases > /var/lib/mysql/dump-10.x.sql
• I started a MariaDB Docker container for version 11.x with a new volume.
• I imported the dump file into the new 11.x database: mysql -u root -p < /var/lib/mysql/dump-10.x.sql

Now, I’m using the yobasystems/alpine-mariadb:latest image, and all data is successfully connected to Firefly in the new database.