docker-compose cannot wait for mysql database

Question

I am having real problems trying to get a docker-compose script to initiate a mysql database and a Django project, but get the Django project to wait until the mysql database is ready.

I have two files, a Dockerfile and a docker-compose.yml, which I have copied below.

When I run the docker-compose.yml, and check the logs of the web container, it says that it cannot connect to the database mydb. However the second time that I run it (without clearing the containers and images) it connects properly and the Django app works.

I have spent a whole day trying a number of things such as scripts, health checks etc, but I cannot get it to work.

Dockerfile

FROM python:3.6

ENV PYTHONUNBUFFERED 1

RUN mkdir /code
WORKDIR /code

COPY ./ /code/

RUN pip install -r requirements.txt
RUN python manage.py collectstatic --noinput

docker-compose.yml

version: '3'

services:
  mydb:
    environment:
      - MYSQL_ROOT_PASSWORD=password
      - MYSQL_USER=django
      - MYSQL_PASSWORD=secret
      - MYSQL_DATABASE=dbMarksWebsite
    image: mysql:5.7
    ports:
      # Map default mysql port 3306 to 3308 on outside so that I can connect
      # to mysql using workbench localhost with port 3308
      - "3308:3306"
  web:
    environment:
      - DJANGO_DEBUG=1
      - DOCKER_PASSWORD=secret
      - DOCKER_USER=django
      - DOCKER_DB=dbMarksWebsite
      - DOCKER_HOST=mydb
      - DOCKER_PORT=3306
    build: .
    command: >
      sh -c "sleep 10 &&
             python manage.py migrate &&
             python manage.py loaddata myprojects_testdata.json &&
             python manage.py runserver 0.0.0.0:8080"
    ports:
      - "8080:8080"
    depends_on:
      - mydb

First run (with no existing images or containers):

...
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/__init__.py", line 84, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 179, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
django.db.utils.OperationalError: (2002, "Can't connect to MySQL server on 'mydb' (115)")

Second run:

System check identified no issues (0 silenced).
March 27, 2020 - 16:44:57
Django version 2.2.11, using settings 'ebdjango.settings'
Starting development server at http://0.0.0.0:8080/
Quit the server with CONTROL-C.

Answer 1

I solved it using the following function in my entrypoint.sh:

function wait_for_db()
{
  while ! ./manage.py sqlflush > /dev/null 2>&1 ;do
    echo "Waiting for the db to be ready."
    sleep 1
  done
}

Answer 2

For anybody who is interested, I found a solution to this:

1 - I wrote a python script to connect to the database every second, but with a timeout. I set this timeout to be quite high at 60 seconds, but this seems to work on my computer.

2 - I added the command to wait into my compose file.

It should mean that I can bring up a set of test containers for my website, where I can specify the exact version of Python and MySQL used.

The relevant files are listed below:

Dockerfile:

FROM python:3.6

ENV PYTHONUNBUFFERED 1

RUN mkdir /code
WORKDIR /code

COPY ./ /code/

RUN pip install -r requirements.txt
RUN python manage.py collectstatic --noinput

docker-compose.yml

version: '3'

services:
  mydb:
    container_name: mydb
    environment:
      - MYSQL_ROOT_PASSWORD=password
      - MYSQL_USER=django
      - MYSQL_PASSWORD=secret
      - MYSQL_DATABASE=dbMarksWebsite
    image: mysql:5.7
    ports:
      # Map default mysql port 3306 to 3308 on outside so that I can connect
      # to mysql using workbench localhost with port 3308
      - "3308:3306"
  web:
    container_name: web
    environment:
      - DJANGO_DEBUG=1
      - DOCKER_PASSWORD=secret
      - DOCKER_USER=django
      - DOCKER_DB=dbMarksWebsite
      - DOCKER_HOST=mydb
      - DOCKER_PORT=3306
    build: .
    command: >
      sh -c "python ./bin/wait-for.py mydb 3306 django secret dbMarksWebsite 60  &&
             python manage.py migrate &&
             python manage.py loaddata myprojects_testdata.json &&
             python manage.py runserver 0.0.0.0:8080"
    ports:
      - "8080:8080"
    depends_on:
      - mydb

wait-for.py

'''
I don't like adding this in here, but I cannot get the typical wait-for scripts
to work with MySQL database in docker, so I hve written a python script that
either times out after ? seconds or successfully connects to the database
The input arguments for the script need to be:

    HOST, PORT, USERNAME, PASSWORD, DATABASE, TIMEOUT

'''
import sys, os
import time
import pymysql

def readCommandLineArgument():
    '''
    Validate the number of command line input arguments and return the
    input filename
    '''

    # Get arguments
    if len(sys.argv)!=7:
        raise ValueError("You must pass in 6 arguments, HOST, PORT, USERNAME, PASSWORD, DATABASE, TIMEOUT")

    # return the arguments as a tuple
    return (sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4], sys.argv[5], sys.argv[6]) 

def connectToDB(HOST, PORT, USERNAME, PASSWORD, DATABASE):
    '''
    for now, just try to connect to the database.
    '''

    con = pymysql.connect(host=HOST, port=PORT, user=USERNAME, password=PASSWORD, database=DATABASE)

    with con:
        cur = con.cursor()
        cur.execute("SELECT VERSION()")

def runDelay():
    '''
    I don't like passing passwords in, but this is only used for a test docker
    delay script
    '''

    # Get the database connection characteristics.
    (HOST, PORT, USERNAME, PASSWORD, DATABASE, TIMEOUT) = readCommandLineArgument()

    # Ensure timeout is an integer greater than zero, otherwise use 15 secs a default
    try:
        TIMEOUT = int(TIMEOUT)
        if TIMEOUT <= 0:
            raise("Timeout needs to be > 0")
    except:
        TIMEOUT = 60

    # Ensure port is an integer greater than zero, otherwise use 3306 as default
    try:
        PORT = int(PORT)
        if PORT <= 0:
            raise("Port needs to be > 0")
    except:
        PORT = 3306

    # Try to connect to the database TIMEOUT times
    for i in range(0, TIMEOUT):

        try:
            # Try to connect to db
            connectToDB(HOST, PORT, USERNAME, PASSWORD, DATABASE)

            # If an error hasn't been raised, then exit
            return True

        except Exception as Ex:
            strErr=Ex.args[0]
            print(Ex.args)
            # Sleep for 1 second
            time.sleep(1)

    # If I get here, assume a timeout has occurred
    raise("Timeout")


if __name__ == "__main__":

    runDelay()

Answer 3

Another option is to use a script to control the startup order, and wrap the web service's command.

In the docker-compose's documentation "wait-for-it" is one of the recommended tools, but other exists.

Answer 4

For testing/development purposes, you could use a version of the MySQL image that has health checks (I believe there's a healthcheck/mysql image), or configure your own (see example here: Docker-compose check if mysql connection is ready).

For production use, you don't want to upgrade the database schema on startup, nor do you want to assume the database is up. Upgrading schema automatically encourages you to not think about what happens when you deploy a bug and need to rollback, and parallel schema upgrades won't work. Longer version: https://pythonspeed.com/articles/schema-migrations-server-startup/