Add option to auto upgrade user tables

[WhoAmI0501]

Hi,
from todays point, it is possible to automatically run mariadb-upgrade --upgrade-system-tables on container start, by setting MARIADB_AUTO_UPGRADE to TRUE.

Unfortunately, this is not covering my user tables, which leads to the fact, that theres still a human interaction required, to complete the MariaDB upgrade.

I am sure, that this is not the expected behavior for important production deployments, but for smaller projects/home use, it would be very nice, to have an optional way/variable, to also upgrade the user tables on container startup.

From my recent lookup in the current docker-entrypoint.sh, this can may be handled with only one additional if condition - but i am not sure, whether there is any greater impact or something different, that must be kept in mind, while implementing this feature.

If the maintainers are fine with a feature like that, i may also contribute it via PR.

[grooverdan]

I'm open to removing --upgrade-system-tables.

What upgrade path did you follow to necessitate the manual action? What errors resulted? Last I looked I couldn't find one and descriptions of need resulted "in theory we could .." answers without a concrete "its needed always case provide".

[WhoAmI0501]

What upgrade path did you follow to necessitate the manual action?

My normal upgrade path looks like the following

1. Stopping all services, that are depending on my MariaDB
2. Recreating the MariaDB container with the docker image of the new version
3. Manually running mariadb-upgrade -uroot -p.... in the container
4. Starting all services from step 1 again

For all MariaDB container updates, that do not require step 3, i am having a working automation. But the automation can not cover step 3, as it does not have the root password for the MariaDB. So normally, this should be solveable by passing MARIADB_AUTO_UPGRADE=TRUE - but, theres that one fact, that the mentioned parameter is restricting the upgrade/checks/fixes, that are done by mariadb-upgrade, to the system tables.

What errors resulted?

Currently, theres luckily no error - but i also dont want to wait for that one day, where errors happen, since my user tables werent upgraded since the last 8763847568 major releases of MariaDB and something becomes incompatible or something like that. Of course, i could prevent that, by using my current upgrade path, but i want to get rid of the manual interaction.

[grooverdan]

FYI: https://mariadb.com/kb/en/upgrading-between-major-mariadb-versions/#work-done-by-mariadb-upgrade

[joestump]

I think I've ran into the issue @WhoAmI0501 was fearing – root no longer has any privileges on my Docker MariaDB install. I've ran mariadb-upgrade with the --force option to no avail. root purportedly has all permissions, but is unable to GRANT privileges anymore.

[grooverdan]

Its a different issue I suspect.

From:
https://stackoverflow.com/questions/78221866/how-to-unstuck-mariadb-11-3-2-when-grant-all-stops-working-for-rootlocalhost/78222039#78222039

Container version of the same:

docker run --name mlatest --rm -v mariadbvol:/var/lib/mysql mariadb --skip-grant-tables
docker exec mlatest mariadb -e 'flush privileges; grant all on *.* to root@localhost with grant option'

[joestump]

@grooverdan unfortunately, the container doesn't appear to support running --skip-grant-tables. When I do so, I get the following error:

2024-04-11 10:09:35+00:00 [Note] [Entrypoint]: Temporary server started.
2024-04-11 10:09:41+00:00 [Note] [Entrypoint]: Securing system users (equivalent to running mysql_secure_installation)
ERROR 1290 (HY000) at line 7: The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

I looked at the entry point and it doesn't seem as though you can set an env var to skip this SQL.

EDIT: I managed to run those commands and get root back. Not entirely sure why this route worked, but using command: --skip-grant-tables in my docker-compose.yml got past the error above.

[grooverdan]

If a database doesn't exist you'll have troubles using a --skip-grant-tables, but and already exists database there shouldn't be an issue. Glad you got it back.