Impossible to upgrade mysql

[Sinouplen]

I'm using docker-compose with existant datas on my mysql container.

Is it possible to execute mysql_upgrade without create my own image of mysql based on the official mysql?

In fact, when I change mysql version from 5.7.7 to 5.7.9, I'm obliger to call mysql_upgrade to have the right database, table, ... structures.

I'm trying in docker-compose to execute multiple command like:
command: mysqld && mysql_upgrade

But your default entrypoint add "&& mysql_upgrade" like parameters of mysqld and not to execute another process.

I was oblige to override the docker-entrypoint.sh to forced mysql_upgrade (see attach file)
docker-entrypoint.sh.zip

[ltangvald]

Did you try running just the mysql_upgrade command? i.e.
docker run [params] mysql:latest mysql_upgrade

I see this might have permission issues, since the entrypoint script will only try to ensure the correct datadir permissions if the supplied command is mysqld. Will look into that a bit more.

[Sinouplen]

It's not exactly that, I would like to start my container and force to upgrade if necessary.
For exemple, when we change version of container mysql with database save, we are obliger to upgrade the structure of all database and datatable some times.

It's not possible to do that automatically on start of the container?

[rdlugosz]

I was running into the same problem. In my case, I was attempting to load a dump file from an older version of mysql (v 5.1, though the dump was created with the tool from 5.7) per the instructions on mounting a .sql file when running the container. The import works fine, then the database crashes after restarting with:

2016-01-28T21:08:08.589842Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2016-01-28T21:08:08.589941Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.
2016-01-28T21:08:08.590167Z 0 [ERROR] Aborting

2016-01-28T21:08:08.590549Z 0 [Note] Binlog end
2016-01-28T21:08:08.591302Z 0 [Note] Shutting down plugin 'ngram'
...

I blew that container away & rebuilt it using the version 5.6 tag of the mysql image. Import worked fine and the container works as expected.

Since the container exits immediately upon the database failing to load, I suspect that I will not be able to upgrade this (easily) to Mysql 5.7 in the future... Would be great if the image was smart enough to run mysql_upgrade when needed.

[Important note: I'm new to Docker, so maybe I'm missing an easy way to run a specific command on this container without it currently running? Or, perhaps there's an easy way to mount the mysql data directory in this container from a different container that runs the upgrade command? Maybe I'd need to keep my data in a third "data volumes" container for that to be an option? Sorry.]

[ltangvald]

Hi,

In general, you can pick which command is run on an image by adding it to the end of the run command. The default for the mysql image is mysqld, but you can replace it with anything, i.e.
docker run ... mysql mysql_upgrade
You can also get an interactve shell by running bash:
docker run -it ... mysql /bin/bash

Note: Upgrading is definitely something we should add to docs

[ltangvald]

I only really considered a major upgrade such as 5.6 to 5.7, but our docs do recommend always running mysql_upgrade for any new version. We may want to try to detect any upgrades in server versions and run mysql_upgrade in the image script.

[rdlugosz]

ah - I think where I went wrong with this is that I used the /docker-entrypoint-initdb.d to build my db inside the container... Had I also used a data volume (in a separate container) or mounted a directory from my host system to store the actual database then I would be able to easily run a new mysql container that simply executes the mysql_upgrade command against that data volume. Where I (think) I went wrong was not keeping the data separate from the container running the database... The section in the docs about "Where to store your data" goes into this, but I guess I didn't quite grok it.

Thanks for the help.

[ltangvald]

We could store the version with the database, maybe. The init script could compare the version in the file with the current database, and run mysql_upgrade if they differ (and give some other error if the existing database is actually from a newer server version).

[netpoe]

I ran into the same problem, this worked for me:

docker exec -it container_name bash -c "mysql_upgrade -uroot -proot"

root must be your mysql username and password

[tianon]

@ltangvald mysql_upgrade works by connecting to a running mysqld instance, right? Or is it supposed to launch mysqld itself as needed? Couldn't it technically be run from a separate container as well?

As to whether we should do it by default -- is it ever potentially destructive or expensive? Does it have any adverse effects for clustering, for example?

Will defer to your judgement on whether it's something we should do (and the best way to do so safely), but I also want to make sure we ask the right questions first to ensure we don't shoot ourselves in the foot down the line. 😅

[ltangvald]

Yes, mysql_upgade is simply a client, so it needs to connect to a running server with admin credentials.

It should be completely non-destructive, but I'll ask around a bit to make sure there aren't any corner cases where it can break things (it's worth noting that in native distro packages, mysql_upgrade is always run on package upgrades).
It will also write a little token file to datadir (if it has write access there) to indicate which version mysql_upgrade was last run, and will abort immediately if it finds a token for the current version (this returns a nonzero code. 2, I think).

So while it should be safe to run it every time, the main issue with running automatically is that it requires admin credentials to do so, and the only time we know these are when the database is new and therefore doesn't need upgrading :)

What we could do is add a check for the token file, and if it's not there, remind users to run mysql_upgrade?

[tianon]

Adding a reminder sounds like a sane middle-ground -- easy to upgrade from that to automated behavior if it more clearly makes sense in the future! 👍

[tianon]

@ltangvald ho ho ho 🎅 😄

This one's been sitting for a year, do you think it's still relevant?

[ltangvald]

I'm assigning this to myself, and make an implementation of the reminder :)

[rcdailey]

So 9 months later, it seems like this isn't resolved. Is this not important?

My concern is that if we're on latest tag, we may do a docker-compose pull and get an upgrade to a new major version of mysql without really noticing. In that case, isn't an upgrade mandatory for the container to continue to function normally?

How are users expected to upgrade? There is zero documentation about upgrades on the Docker Hub page, and I'm not sure what the current process is.

Ideally, the upgrade would be transparently done. Meaning, at startup, the container entrypoint script first checks if an upgrade is needed, performs that upgrade, and then continues running normally.

[ltangvald]

Right, the idea is to add this to/after pr #471 which splits up the entrypoint so we can reuse the logic for e.g. doing a temporary server startup

[jainprashantk]

I am waiting for the fix eagerly. I couldn't even upgrade from 5.7.22-1debian9 to 5.7.24-1debian9.

[PoweredByPeople]

Waiting for this fix as well. We run persistent mysql and are going from 5.6 -> 5.7 and a built in mysql_upgrade command would be very helpful.

[DanielWeigl]

just fyi, because I got here via google while trying to update a mysql-8 db which runs in docker/docker-compose:

Since mysql8 mysql_upgrade is deprected and the update is done by starting mysqld with the --upgrade - flag:

version: '3.3'

services:
  mysql8:
    container_name: mysql8
    image: 'mysql:8'
    ports:
      - "4406:3306"
    volumes:
      - /var/lib/mysql_8:/var/lib/mysql

    command: ["mysqld", "--upgrade=FORCE"]

Start it once, after it has updated, stop it and remove the command line again

[tianon]

@ltangvald now that we've got #471 (and given it's changed in 8+), what do you think we should do here? 😇 ❤️

[ltangvald]

I had a patch for this... somewhere... Oh, pr #416
We could add a version of that for 5.6 and 5.7, but that pr was 8.0-only since 8.0 needs the upgrade to be usable, while older versions don't. We'd also need to start the server without authentication, since mysql_upgrade needs to log in as an admin user, which is a bit iffy. Might be better to clearly document the upgrade process instead, as doing the upgrade manually shouldn't be any more complicated than a docker exec mysql_upgrade && docker restart command.

[ltangvald]

i.e. something like this: ltangvald/docker-library-docs@b98cacf

[yosifkit]

@ltangvald I think the content looks ok to me (though it will need to be in content.md rather than README.md for a PR). 👍

[tianon]

Is it finally time to close this one as "fixed in 8.0+" ? 🙈 ❤️

[tianon]

Closing per #416 (comment)