Django docker container failed to connect to mysql container with error "Can't connect to MySQL server on 'db' (111)")

Question

I am trying set up a Django development environment using docker. Though I can connect to the mysql on host machine. But the web container failed to connect to mysql container with following error:

django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on 'db' (111)")

Below are the docker configurations and django configuration:

---------docker-compose.yml---------

version: '2'
services:
  web:
    build: .
  volumes:
    - ".:/code/current"
  ports:
    - "8000:8000"
  depends_on:
    - db
    - redis
  links:
    - db
    - redis
  command: ./manage.py runserver 0.0.0.0:8000

db:
  image: mysql:5.7
  volumes:
    - "./.data/db:/var/lib/mysql"
  environment:
    - MYSQL_ROOT_PASSWORD=root
    - MYSQL_DATABASE=goat_db
  restart: always
  ports:
    - "3306:3306"

redis: restart: always image: redis:latest ports: - "6379:6379"

--------------Dockerfile for web image----------------------

FROM ubuntu:14.04
MAINTAINER Alice
RUN apt-get update
RUN apt-get install -y tar git curl wget emacs build-essential python   python-dev python-distribute python-pip libpcre3 libpcre3-dev libmysqlclient-dev python-m2crypto openssl libssl-dev swig freetds-dev python-pymssql nginx subversion
RUN mkdir -p var/www/goat.garenanow.com/current/log
WORKDIR /var/www/goat.garenanow.com/current
ADD requirements.txt /var/www/goat.garenanow.com/current
RUN pip install -r requirements.txt
ADD . /var/www/goat.garenanow.com/current
EXPOSE 8000

------django database configuration---

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.mysql',
    'NAME': 'goat_db',
    'USER': 'root',
    'PASSWORD': 'root',
    'HOST': 'db',
    'PORT': '3306',
},

Answer 1

If Django tries to connect to your database and MySql is not ready, then you will probably see this error:

django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on 'db' (111)")

---

The solution for this case is wait for the db server to be ready before run the web server.

How we can do that?

Instead of running the web server directly we create and run a script that it will wait for the db server to be ready. After that the script will run the web server.

---

docker-compose.yml file (The relevant part is when we overwrite the web:command directive and we run the script instead of run the web server immediately).

version: '3'
services:    
  db:
    restart: always
    image: mysql:5.7
    container_name: my_db
    env_file: env_db
    ports:
      - "3306:3306"

  web:
    restart: always
    build: ./web
    env_file: env_web
    command: ./wait_for_db_and_start_server.sh
    ports:
      - "8000:8000"
    depends_on:
      - db

Now env_web file (The relevant part is START_CMD variable).

# Add Environment Variables
DB_NAME=docker
DB_USER=docker_user
DB_PASS=docker_password
DB_SERVICE=db
DB_HOST=db
DB_PORT=3306
START_CMD=python manage.py runserver 0.0.0.0:8000

Now, our web Dockerfile (The relevant part is when we add wait_for_db_and_start_server.sh).

FROM python:2.7

ADD wait_for_db_and_start_server.sh .

ADD requirements.txt .
RUN pip install -r requirements.txt

ADD . .

Now wait_for_db_and_start_server.sh file. (Basically, while we cannot connect to our db server on the specified $DB_HOST:$DB_PORT defined in our env_web file, we wait 3 seconds and try again for M_LOOPS times. If we are able to connect to the server, then we run $START_CMD that was defined in our env_web file.)

#!/bin/sh
# Wait for database to get available

M_LOOPS="10"

#wait for mysql
i=0
# http://stackoverflow.com/a/19956266/4848859
while ! curl $DB_HOST:$DB_PORT >/dev/null 2>&1 < /dev/null; do
  i=`expr $i + 1`

  if [ $i -ge $M_LOOPS ]; then
    echo "$(date) - ${DB_HOST}:${DB_PORT} still not reachable, giving up"
    exit 1
  fi

  echo "$(date) - waiting for ${DB_HOST}:${DB_PORT}..."
  sleep 3
done

echo "$(date) - ${DB_HOST}:${DB_PORT} Reachable ! - Starting Daemon"
#start the daemon
exec $START_CMD

Answer 2

What's your my.cnf file look like for the db service?

Check the bind-address value and also make sure the grant permissions are correct connecting from the web container.

To test, you could docker exec ... into the web container and try to connect to db host from there.

Answer 3

If you are trying to keep your web container "thin", you may not want to add mysql or postgres client packages, just to check if your db is ready. Some of the popular wait scripts out there have client dependencies.

I just created a simple Python script which makes native calls to see if the DB is functional. In this case I avoid adding valid credentials and just look for an authentication failure message. The example here is for Postgres, but should be easily adapted for MySQL.

wait_for_db.py

import psycopg2.extras
from time import sleep

connect_str = "dbname='postgres' user='nobody' host='db' " + \
              "password='foobar'"

while True:
    try:
        conn = psycopg2.connect(connect_str)
    except psycopg2.OperationalError as e:
        if "authentication failed" in e.args[0]:
            print("Postgres is ready.  Launching site.")
            break
        else:
            print("Waiting for postgres...", e.args[0])
            sleep(1)

Then set your web container in your dev environment docker-compose.yml so that you have something like

command: bash -c "python wait-for-postgres.py; python manage.py runserver 0.0.0.0:8000"