Docker-compose check if mysql connection is ready

mysql docker docker-compose dockerfile

116,659

Solution 1

version: "2.1"
services:
    api:
        build: .
        container_name: api
        ports:
            - "8080:8080"
        depends_on:
            db:
                condition: service_healthy
    db:
        container_name: db
        image: mysql
        ports:
            - "3306"
        environment:
            MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
            MYSQL_USER: "user"
            MYSQL_PASSWORD: "password"
            MYSQL_DATABASE: "database"
        healthcheck:
            test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
            timeout: 20s
            retries: 10

The api container will not start until the db container is healthy (basically until mysqladmin is up and accepting connections.)

Solution 2

condition was removed compose spec in versions 3.0 to 3.8 but is now back!

Using version of the compose spec v3.9+ (docker-compose v1.29), you can use condition as an option in long syntax form of depends_on.

Use condition: service_completed_successfully to tell compose that service must be running before dependent service gets started.

services:
  web:
    build: .
    depends_on:
      db:
        condition: service_completed_successfully
      redis:
        condition: service_completed_successfully
  redis:
    image: redis
  db:
    image: postgres

condition option can be:

service_started is equivalent to short syntax form
service_healthy is waiting for the service to be healthy. Define healthy with healthcheck option
service_completed_successfully specifies that a dependency is expected to run to successful completion before starting a dependent service (Added to docker-compose with PR#8122).

It is sadly pretty badly documented. I found references to it on Docker forums, Docker doc issues, Docker compose issue, in Docker Compose e2e fixtures. Not sure if it's supported by Docker Compose v2.

Solution 3

This should be enough

version: '2.1'
services:
  mysql:
    image: mysql
    ports: ['3306:3306']
    environment:
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypassword
    healthcheck:
      test: mysqladmin ping -h 127.0.0.1 -u $$MYSQL_USER --password=$$MYSQL_PASSWORD

Solution 4

Hi for a simple healthcheck using docker-compose v2.1, I used:

/usr/bin/mysql --user=root --password=rootpasswd --execute \"SHOW DATABASES;\"

Basically it runs a simple mysql command SHOW DATABASES; using as an example the user root with the password rootpasswd in the database.

If the command succeed the db is up and ready so the healthcheck path. You can use interval so it tests at interval.

Removing the other field for visibility, here is what it would look like in your docker-compose.yaml.

version: '2.1'

  services:
    db:
      ... # Other db configuration (image, port, volumes, ...)
      healthcheck:
        test: "/usr/bin/mysql --user=root --password=rootpasswd --execute \"SHOW DATABASES;\""
        interval: 2s
        timeout: 20s
        retries: 10

     app:
       ... # Other app configuration
       depends_on:
         db:
         condition: service_healthy

Solution 5

If you can change the container to wait for mysql to be ready do it.

If you don't have the control of the container that you want to connect the database to, you can try to wait for the specific port.

For that purpose, I'm using a small script to wait for a specific port exposed by another container.

In this example, myserver will wait for port 3306 of mydb container to be reachable.

# Your database
mydb:
  image: mysql
  ports:
    - "3306:3306"
  volumes:
    - yourDataDir:/var/lib/mysql

# Your server
myserver:
  image: myserver
  ports:
    - "....:...."
  entrypoint: ./wait-for-it.sh mydb:3306 -- ./yourEntryPoint.sh

You can find the script wait-for-it documentation here

View more solutions

116,659

Author by

John Kariuki

web designer, developer in Nairobi Kenya

Updated on July 08, 2022

Comments

John Kariuki almost 2 years
I am trying to make sure that my app container does not run migrations / start until the db container is started and READY TO accept connections.

So I decided to use the healthcheck and depends on option in docker compose file v2.

In the app, I have the following
```
app:
    ...
    depends_on:
      db:
      condition: service_healthy
```
The db on the other hand has the following healthcheck
```
db:
  ...
  healthcheck:
    test: TEST_GOES_HERE
    timeout: 20s
    retries: 10
```
I have tried a couple of approaches like :
1. making sure the db DIR is created test: ["CMD", "test -f var/lib/mysql/db"]
2. Getting the mysql version: test: ["CMD", "echo 'SELECT version();'| mysql"]
3. Ping the admin (marks the db container as healthy but does not seem to be a valid test) test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
Does anyone have a solution to this?
- Jorge Campos about 7 years
  
  You created a docker for a DB ? Please tell me that your data is outside of this container for the sake of your application health
- Jorge Campos about 7 years
  
  Or at least this is a test containter.
- John Kariuki about 7 years
  
  This is only for development/testing ONLY purposes actually.
- Jorge Campos about 7 years
  
  That's great to here :)
- Jorge Campos about 7 years
  
  I think you should use a command to connect and run a query in mysql, none of the samples you provided do this: something like: mysql -u USER -p PASSWORD -h MYSQLSERVERNAME -e 'select * from foo...' database-name
- BartoszK over 5 years
  
  Warning: With "version 3" of compose file, the "condition" support is not longer available. See docs.docker.com/compose/compose-file/#depends_on
- Thadeu Melo over 5 years
  
  @JorgeCampos could you give a more detailed explanation? I´m facing the same problem
- S.. over 4 years
  
  @JorgeCampos Why is having a database container bad?
- Jorge Campos over 4 years
  
  @S.. well, back in the days, almost 3 years back, when I added that comment it use to not be a good idea. Containers were not very reliable and mostly because people would forget to leave the data out of the container... nowadays I don't really think it is valid anymore... that comment...
- S.. over 4 years
  
  @JorgeCampos Okay thanks. Usually I have a db container, but map volumes for the data dir. So that if the container went down the data would persist to it's next instantiation.
John Kariuki about 7 years

I tried using wait-for-it.sh earlier but it overrides the default Dockerfile right? How does the entrypoint.sh look like?
nono about 7 years

The entrypoint depends on your image. You can check it with docker inspect <image id>. This should wait for the service to be available and call your entry point.
nono about 7 years

Is it ok ? Do you understand?
John Kariuki about 7 years

Make sense. Yea.
John Kariuki about 7 years

Done! This was helpful but I opted to use the default v2.1 health check instead.
datasci about 7 years

@JohnKariuki: Can you post your solution?
John Kariuki about 7 years

@datasci sure. Posting now.
halfpastfour.am almost 7 years

mysqladmin ping will return a false positive if the server is running but not yet accepting connections.
Mukesh Agarwal almost 7 years

@BobKruithof I am facing the same issue... is there any work around, something like sleep or exit status for retry
Mukesh Agarwal almost 7 years

@BobKruithof got the solution
halfpastfour.am almost 7 years

This may work for you but I am unsure whether or not this is supported in all MySQL engines.
halfpastfour.am almost 7 years

I'm talking about database engines like InnoDB, MyISAM etc. Is LastSchema.LastDBInsert a MySQL default or database engine specific?
Mukesh Agarwal almost 7 years

No it is not a default in mysql either. It was just a sample. a dummy query.
Mukesh Agarwal over 6 years

@dKen see my answer below stackoverflow.com/a/45058879/279272, I hope it will work for you also.
phil294 about 6 years

this keeps pinging, even when everything runs properly and spams the log. pretty sad that there is no other way around it right now, it seems. not to speak of that this is 2.x feature only
Philipp Kyeck about 6 years

@Blauhirn did you find a better way by now?
phil294 about 6 years

@pkyeck no i didnt. still looks like the most docker-like solution to me
Blaise almost 6 years

Warning: MySQL 5.5 (possibly newer versions as well) can respond while still initializing.
BartoszK over 5 years

Warning: With "version 3" of compose file, the "condition" support is not longer available. See docs.docker.com/compose/compose-file/#depends_on
BartoszK over 5 years

Warning: With "version 3" of compose file, the "condition" support is not longer available. See docs.docker.com/compose/compose-file/#depends_on
BartoszK over 5 years

Warning: With "version 3" of compose file, the "condition" support is not longer available. See docs.docker.com/compose/compose-file/#depends_on
BartoszK over 5 years

You should use command feature together with wait-for-it.sh script. Me doing it this way: command: ["/home/app/jswebservice/wait-for-it.sh", "maria:3306", "--", "node", "webservice.js"]
Thadeu Melo over 5 years

@BartoszKI don´t understand it. Could you please add a full answer with details? I´m facing the exact same problem, but I can´t make it work.
Sylhare over 5 years

Make sure you are using v2.1, otherwise follow the new guidelines for v3.0 and above.
Taku almost 5 years

--execute \"SHOW DATABASES;\" is what made it wait for me until the database was available for the application to access
laimison almost 5 years

To check this using password: test: ["CMD", 'mysqladmin', 'ping', '-h', 'localhost', '-u', 'root', '-p$$MYSQL_ROOT_PASSWORD' ] - if you defined MYSQL_ROOT_PASSWORD in environments section.
InsOp over 4 years

whats the double $ for?
Maksim Kostromin over 4 years

@InsOp special syntax you have to use in health check test command for escaping env variables starts with $, ie $$MYSQL_PASSWORD will result into $MYSQL_PASSWORD, which itself will result into mypassword in this concrete example
InsOp over 4 years

So with this im accessing the env variable inside the container? with a single $ Im accessing the env variable from the host then i suppose? thats nice thank you!
Mathias Brodala over 3 years

Notice that with the separated Compose Spec "condition" has been added to "depends_on" again: github.com/compose-spec/compose-spec/blob/a4a7e7c/… You'll need Compose 1.27.0 or newer for this: github.com/docker/compose/releases/tag/1.27.0
Sam Jones about 3 years

I'm using version 3.9, and the condition appears to work, despite what the documentation says.
Capripot about 3 years

@SamJones The problem addressed here is that depends_on does not wait for service to be ready before starting the dependent service, because V3 does not support the condition form of depends_on.
Sam Jones about 3 years

Right, and what I'm saying is that I can get one service to wait for another to be ready using the solution described in stackoverflow.com/a/42757250/1459532, in a Docker Compose file with version set to 3.9. The documentation says it's not supported, but it still works.
Sam Jones about 3 years

I am using a Compose file with version 3.9, and the condition field works.
Gideao about 3 years

Thank you so much, it saved me.
Yarrow about 3 years

"condition" seems to work again in v3 since docker-compose v1.27.0. This health check worked for me with mysql 8.0 as --execute="SHOW DATABASES;"
Kolyunya almost 3 years

@Mint it's not documented, but seems to be working fine. I wonder whether it's a lack of documentation or a feature which is going to be deprecated?
WhiteKnight almost 3 years

Thank you @laimison. There appears to be an extra & so instead use test: ["CMD", 'mysqladmin', 'ping', '-h', 'localhost', '-u', 'root', '-p$MYSQL_ROOT_PASSWORD' ] if MYSQL_ROOT_PASSWORD is defined in environment variables
Hantsy over 2 years

I have tried to use this wait-for-it script to check the host:port of dependent services, but it still faield. It seems when port is ready for connection, but the db intance is still in progress.
Hantsy over 2 years

The host:port checking seems not enough for most applications, I found when the db port is ready(it passed the wait-for scripts), but it still failed. From the docker-compose logs I found the db instance is not ready for connection. And I added a hard code sleep 10 to wait for the db, it worked. I think it must execute a ping query string on the database to enurse it is available.
leogoesger over 2 years

condition is added back
PanZWarzywniaka about 2 years

Doesn't work for me, with service_completed_successfully, I mean the database initalization works but the main app isn't starting. Any suggestions?
JackTheKnife almost 2 years

I'm getting access denied for provided user or root (both defined in the env vars)
Ryan Aquino almost 2 years

Hi @PanZWarzywniaka, were you able to make it work?