Mysql: Add HEALTHCHECK entry to Dockerfile to reliably monitor container readiness status

[zarbis]

As of Docker release 1.12.0:

New HEALTHCHECK Dockerfile instruction to support user-defined healthchecks #23218

This feature will greatly facilitate start of dependent containers only after mysql container completes initialization, both manually and with Compose (after it becomes healthcheck-aware compose/#3754).

[ltangvald]

Yeah, especially for Compose this sounds excellent!

[hyleung]

Had a quick stab at this - I have a _partial_ solution I think (https://github.com/hyleung/mysql/commit/8f7fc32ecdc4f244480130ab950bdfcd8bd4045b).

The problem is that the way I've done the health check, it only works if the container is started with MYSQL_ROOT_PASSWORD. This happens to be the way I start my containers most of the time, but the other startup options (e.g. MYSQL_ALLOW_EMPTY_PASSWORD, etc.) most likely won't work. Anybody have ideas about how to implement a health check with in those situations?

[tianon]

I think @yosifkit summarized adding HEALTHCHECK by default really well over in https://github.com/docker-library/cassandra/pull/76#issuecomment-246054271:

I do not feel that generalized healthchecks on the official images are really that useful.

• users will have their own idea of what is "healthy"
• it does not actually test that the service is listening to connections outside of localhost (see https://github.com/docker-library/healthcheck for some examples that do more than what's proposed here, including attempting to check whether the service is listening remotely)
  
  
  
  • some of the Official Images even purposely start in a localhost only mode for database initialization and then kill and start the main service with full network availability
  
  
• after upgrading their images, current users will have extra unexpected load on their systems for healthchecks they don't necessarily need/want and may be unaware of

(some of that can be abated, such as trying to connect to the external IP of the container instead of localhost, but the points about difficult generalization and added load still definitely apply)

[hyleung]

@tianon ic, makes sense (tho' similar to this comment on the same thread, I'm interested in knowing when the DB is ready so that I can start some integration tests).

In any case, should this issue be closed then?

[ltangvald]

I think the definition of "healthy" for a database can be agreed on by most as "ready to accept connections", but from the (very brief) look I had at the system, it doesn't quite fit with the way it's handled in Docker.
It is something that could be very useful for automated systems, and for frameworks like Compose, but we'd need a non-disruptive way of doing it (users who don't need it shouldn't notice it's there, and it can't add significant complexity to the image).
I'd like to keep the issue open for now, and see if we can find a solution.

[ioggstream]

How will this healtcheck stuff interact with kubernetes and other orchestration engines running on distributed systems?

We aren't goint to trigger anything in case of unhealth, right?

I think it may be usefult but not active by default (eg. we could distribute a healtcheck.sh but leave to the user the choice to enable it).

[hyleung]

@ioggstream I believe for Kubernetes, the health checks (readiness or liveness probes) are configured in the Podspec file (i.e. not baked into the container image) - http://kubernetes.io/docs/user-guide/liveness/.

[ioggstream]

@hyleung yes, this kind of checks may interfere with orchestrators (I use openshift/kubernetes) so I'm for providing an "enabler", not to activate them by default.

[iBobik]

If in Dockerfile will be HEALTHCHECK line will Kubernetes have problem with it? If not, why block this feature because some orchestrator have own solution?

[josephspurrier]

Here is how to check from docker-compose: http://stackoverflow.com/questions/42567475/docker-compose-check-if-mysql-connection-is-ready/42757250#42757250

[L0rD59]

Hi @josephspurrier do you have the same solution when using mounted volume /docker-entrypoint-initdb.d and import big database (i need to wait fully import before start other container)

Thx

[L0rD59]

I've try this one and works fine :

healthcheck:
      test: mysqladmin ping -h localhost -p$$MYSQL_ROOT_PASSWORD && test '0' -eq $$(ps aux | awk '{print $$11}' | grep -c -e '^mysql$$')

Test if mysql command is running (when importing dump from /docker-entrypoint-initdb.d

[ltangvald]

A fairly robust indication of the server's readiness is the presence of the .sock file, but image initialization will also do a temporary start of the server, so I've considered maybe having the entrypoint script write a simple flag file before the final startup, and having the image's healthcheck command check for both that file and the sock file.

[iBobik]

A fairly robust indication of the server's readiness is the presence of the .sock file

Are you sure this socketfile will be removed when server crashed or is not working?

[ltangvald]

If it crashes (exits) the container will stop, but a hang would break this, yes. Running «mysqladmin ping» should work, though.

[josephspurrier]

The most reliable way I found is using an actual select query. The ping command will return when the MySQL server is started, but not when it's actually ready to handle queries. If you want to use it for a health check, you'll have to modify it a bit.

while ! mysql -e "select 1" -u$MYSQL_USER -p$MYSQL_ROOT_PASSWORD >/dev/null 2>&1; do
sleep 1
done

[iBobik]

It seems the best healthcheck script is there: https://github.com/docker-library/healthcheck/blob/master/mysql/docker-healthcheck

Any reason why not just copy it there?

[ltangvald]

Those both require the script being able to authenticate, which is a very unreliable assumption. You only need to set the password the first time the container is started, and it's strongly recommended to change it right away.

mysqladmin ping will return success if you get access denied, failure if the process is running but the server is not accepting connections

[tianon]

Closing given that there's not a reliable way we can add this by default to the image, and there are now a decent number of examples for folks to build this for their own uses (matching their own valid assumptions per-environment).