Correct health check for MySQL 5.7 to prevent connecting to temporary server

[scspijker]

The MySQL 5.7 image we use (a3d35804fa37) always starts a temporary server, then shutting it down, then starting the real server. We create a completely clean MySQL instance for our systemwide test and this temporary server creates intermittent failures.

We have startup/setup scripts that wait for the MySQL docker instance to be "healthy". The problem is they seem to connect to the "temporary server" which then immediately shuts down.

Is there a health check or another way for our scripts to wait on the real server?

I've already tried the following docker-compose.yml test checks, without success:

  test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
  test: ["CMD", "mysqladmin", "ping", "--protocol tcp", "-h", "127.0.0.1", "--silent"]
  test: "/usr/bin/mysql --user=root --execute \"SHOW DATABASES;\""

I'd like to not have to write retry logic in our setup scripts and/or have to implement a sleep 😅

[wglambert]

When the real server is ready it outputs [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. in the logs

Similar issue #547

[yosifkit]

We have an example healthcheck over in https://github.com/docker-library/healthcheck/blob/40afbf64d69cf933af0da4df6383958a29113601/mysql/docker-healthcheck. The summary is that you need to make sure that you are connecting via a network connection and not the local socket file since the temporary server is started with --skip-networking for exactly this purpose (i.e., when it is accessible/responding via a network connection, it is ready). You'll also need to be connecting with the password unless you set MYSQL_ALLOW_EMPTY_PASSWORD.

[chrissound]

We have an example healthcheck over in https://github.com/docker-library/healthcheck/blob/40afbf64d69cf933af0da4df6383958a29113601/mysql/docker-healthcheck. The summary is that you need to make sure that you are connecting via a network connection and not the local socket file since the temporary server is started with --skip-networking for exactly this purpose (i.e., when it is accessible/responding via a network connection, it is ready). You'll also need to be connecting with the password unless you set MYSQL_ALLOW_EMPTY_PASSWORD.

Isn't the commands in the original post connecting explicitly via TCP? Meaning those commands should not return success but they do in fact (misleading)?

In my case I do a mysql -h 127.0.0.1 ... and it will still connect successfully, however shortly after it'll fail to connect, which will throw the rest of my init scripts off track. This is using the mysql:8 tag as well.

[yosifkit]

I am unable to reproduce, connecting via -h127.0.0.1 via mysql or mysqladmin correctly uses the network connection (and not the socket file) and fails as expected during the temporary server.

$ # run a container that waits via an initdb.d script with a very long sleep
$ cat wait.sh
#!/bin/bash

sleep 1000
$ docker run -it --rm -e MYSQL_ROOT_PASSWORD=12345 --name sqly --volume="$PWD/wait.sh":/docker-entrypoint-initdb.d/wait.sh mysql:8
...

$ # now connect in another terminal
$ docker exec -it -e MYSQL_PWD=12345 sqly mysqladmin ping -h127.0.0.1
mysqladmin: connect to server at '127.0.0.1' failed
error: 'Can't connect to MySQL server on '127.0.0.1:3306' (111)'
Check that mysqld is running on 127.0.0.1 and that the port is 3306.
You can check this by doing 'telnet 127.0.0.1 3306'
$ echo $?
1
$ docker exec -it -e MYSQL_PWD=12345 sqly mysqladmin ping
mysqld is alive
$ docker exec -it -e MYSQL_PWD=12345 sqly mysql -h127.0.0.1
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (111)
$ echo $?
1
$ docker exec -it -e MYSQL_PWD=12345 sqly mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ^DBye

[scspijker]

We ended up using this health check, which works reliably for us:

    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 5s
      timeout: 3s
      retries: 2
      start_period: 0s

The timeout of 3s and 2 retries were crucial though.

[digglife]

We have an example healthcheck over in https://github.com/docker-library/healthcheck/blob/40afbf64d69cf933af0da4df6383958a29113601/mysql/docker-healthcheck. The summary is that you need to make sure that you are connecting via a network connection and not the local socket file since the temporary server is started with --skip-networking for exactly this purpose (i.e., when it is accessible/responding via a network connection, it is ready). You'll also need to be connecting with the password unless you set MYSQL_ALLOW_EMPTY_PASSWORD.

Thanks for your explaining. -h 127.0.0.1 works.
I was using localhost.