Django can't connect to MySQL in Docker+Django+MySQL

Question

I'm trying to pack my working student application into containers. Here is my docker-compose.yaml:

version: "3.8"

services:
  mysql_db:
    build:
      context: .
      dockerfile: ./docker/mysql/Dockerfile
    restart: unless-stopped
    env_file: env.dev
    ports:
      - "33061:3306"
    networks:
      - autopark_network

  backend:
    build:
      context: .
      dockerfile: docker/django/Dockerfile
    env_file: env.dev
    restart: on-failure
    depends_on:
      - mysql_db
    working_dir: "/app/autopark"
    expose:
      - 800
    ports:
      - "8001:8000"
    command: ["python3", "manage.py", "runserver", "0.0.0.0:8000"]
    networks:
      - autopark_network

networks:
    autopark_network:

Here is env.dev:

DEBUG=1
SECRET_KEY=<...>
DJANGO_ALLOWED_HOSTS="localhost 127.0.0.1 [::1]"
MYSQL_ENGINE=django.contrib.gis.db.backends.mysql
MYSQL_DATABASE=autopark
MYSQL_DATABASE_HOST=mysql_db 
MYSQL_DATABASE_PORT=33061
MYSQL_ROOT_PASSWORD=<...>
MYSQL_USER=pavel
MYSQL_PASSWORD=<...>

I thought that both of my services will get those environment variables so I can be sure everything is ok. As if.

I need GDAL for my project, that's why I started backend not with python but with ubuntu (here's my Dockerfile for backend):

FROM ubuntu:22.04
MAINTAINER Pavel Vasilev <pvlvslv@yandex.ru>

RUN apt -y update
RUN apt -y install python3-dev python3-pip gdal-bin gdal-data

WORKDIR /app/autopark
ADD . /app/autopark

RUN pip install -r requirements.txt

Then I need initialization for my mysql_db (here's my Dockerfile for mysql_db):

FROM mysql/mysql-server:latest

COPY ./docker/mysql/init_db.sh /docker-entrypoint-initdb.d/
COPY ./docker/mysql/init_db.sql /usr/local/
RUN chmod +x /docker-entrypoint-initdb.d/init_db.sh

And here is init_db.sh:

#!/bin/bash

mysql -u root --password="$MYSQL_ROOT_PASSWORD"  << EOF
SOURCE /usr/local/init_db.sql
USE ${MYSQL_DATABASE};
GRANT ALL PRIVILEGES ON ${MYSQL_DATABASE}.* TO '${MYSQL_USER}';
EOF

init_db.sql was made this way with mysqldump.

When I do docker-compose build everything goes smooth. When I go docker-compose up, I have this:

Recreating autopark_docker_mysql_db_1 ... done
Recreating autopark_docker_backend_1  ... done
Attaching to autopark_docker_mysql_db_1, autopark_docker_backend_1
mysql_db_1  | [Entrypoint] MySQL Docker Image 8.0.32-1.2.11-server
mysql_db_1  | [Entrypoint] Initializing database
mysql_db_1  | 2023-03-29T05:11:30.618219Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
mysql_db_1  | 2023-03-29T05:11:30.622277Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.32) initializing of server in progress as process 17
mysql_db_1  | 2023-03-29T05:11:30.653228Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
mysql_db_1  | 2023-03-29T05:11:31.643747Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
backend_1   | Watching for file changes with StatReloader
backend_1   | Exception in thread django-main-thread:
backend_1   | Traceback (most recent call last):
<... skip ...>
backend_1   | ConnectionRefusedError: [Errno 111] Connection refused
backend_1   | 
backend_1   | During handling of the above exception, another exception occurred:
backend_1   | 
backend_1   | Traceback (most recent call last):
<... skip ...>
backend_1   | pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'mysql_db' ([Errno 111] Connection refused)")
backend_1   | 
backend_1   | The above exception was the direct cause of the following exception:
backend_1   | 
backend_1   | Traceback (most recent call last):
<... skip ...>backend_1   | django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on 'mysql_db' ([Errno 111] Connection refused)")
mysql_db_1  | 2023-03-29T05:11:33.028156Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
mysql_db_1  | [Entrypoint] Database initialized
mysql_db_1  | 2023-03-29T05:11:36.080939Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
mysql_db_1  | 2023-03-29T05:11:36.084369Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 58
mysql_db_1  | 2023-03-29T05:11:36.120288Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
mysql_db_1  | 2023-03-29T05:11:36.339612Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysql_db_1  | 2023-03-29T05:11:36.668690Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
mysql_db_1  | 2023-03-29T05:11:36.669242Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
mysql_db_1  | 2023-03-29T05:11:36.689594Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
mysql_db_1  | 2023-03-29T05:11:36.690338Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MySQL Community Server - GPL.
mysql_db_1  | Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
mysql_db_1  | Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
mysql_db_1  | Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
mysql_db_1  | Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
mysql_db_1  | Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
mysql_db_1  | 
mysql_db_1  | [Entrypoint] running /docker-entrypoint-initdb.d/init_db.sh
mysql_db_1  | mysql: [Warning] Using a password on the command line interface can be insecure.
mysql_db_1  | ERROR 1046 (3D000) at line 22 in file: '/usr/local/init_db.sql': No database selected
mysql_db_1  | [Entrypoint] Starting MySQL 8.0.32-1.2.11-server
mysql_db_1  | 2023-03-29T05:11:40.431294Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
mysql_db_1  | 2023-03-29T05:11:40.433961Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 1
mysql_db_1  | 2023-03-29T05:11:40.443031Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
mysql_db_1  | 2023-03-29T05:11:41.798794Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysql_db_1  | 2023-03-29T05:11:41.939345Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
mysql_db_1  | 2023-03-29T05:11:41.958108Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
mysql_db_1  | 2023-03-29T05:11:42.002608Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
mysql_db_1  | 2023-03-29T05:11:42.002949Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
mysql_db_1  | 2023-03-29T05:11:42.016603Z 0 [ERROR] [MY-010259] [Server] Another process with pid 60 is using unix socket file.
mysql_db_1  | 2023-03-29T05:11:42.016825Z 0 [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
mysql_db_1  | 2023-03-29T05:11:42.016932Z 0 [ERROR] [MY-010119] [Server] Aborting
mysql_db_1  | 2023-03-29T05:11:43.579880Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32)  MySQL Community Server - GPL.
autopark_docker_mysql_db_1 exited with code 1
mysql_db_1  | [Entrypoint] Starting MySQL 8.0.32-1.2.11-server
mysql_db_1  | 2023-03-29T05:11:45.384210Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
mysql_db_1  | 2023-03-29T05:11:45.386889Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 1
mysql_db_1  | 2023-03-29T05:11:45.398475Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
mysql_db_1  | 2023-03-29T05:11:45.602149Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysql_db_1  | 2023-03-29T05:11:45.832458Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
mysql_db_1  | 2023-03-29T05:11:45.833092Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
mysql_db_1  | 2023-03-29T05:11:45.834110Z 0 [ERROR] [MY-010259] [Server] Another process with pid 60 is using unix socket file.
mysql_db_1  | 2023-03-29T05:11:45.834490Z 0 [ERROR] [MY-010268] [Server] Unable to setup unix socket lock file.
mysql_db_1  | 2023-03-29T05:11:45.834832Z 0 [ERROR] [MY-010119] [Server] Aborting
mysql_db_1  | 2023-03-29T05:11:47.380031Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.32)  MySQL Community Server - GPL.

And the last part goes on repeat.

I think I failed with initializing mysql after installation but I don't know where. Spend two days already and all ideas are mixed in my head now.

It also looks to me like your application is trying to start up before the database is available (looking at timestamps, the whole database startup takes 17 seconds). Do the solutions in [Docker Compose wait for container X before starting Y](https://stackoverflow.com/questions/31746182/docker-compose-wait-for-container-x-before-starting-y) help you? — David Maze, Mar 29 '23 at 11:07
@david-maze, after recomendations of lemoana I somehow manage to get to db initialization, and then I changed docker-compose.yaml to: `depends_on: mysql_db: condition: service_healthy ` Now my mysql_db container starts fine but my backend container still gets: `django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on 'mysql_db' ([Errno 111] Connection refused)")` -_- — Pavel, Mar 29 '23 at 12:43
Your configuration has an extra `1` at the end of `MYSQL_DATABASE_PORT`, the standard MySQL port number is 3306. (Connections between containers don't use or require `ports:`.) — David Maze, Mar 29 '23 at 13:43
No, it's translating container ports to host ports, so it doesn't conflict with host's mysql and host's django. And I can connect from host like `https://localhost:8001` and I don't need to stop my django project, for example. — Pavel, Mar 29 '23 at 15:33

lemoana · Answer 1 · 2023-03-29T07:43:15.927

1

I think I failed with initializing mysql after installation

i usually make my mysql-related file structure like this:

e.g. MYSQL_DATABASE is robot

.
├── docker-compose.yml
└── mysql
    ├── 01_schema.sql
    ├── Dockerfile
    └── robot.sql

the content of 01_schema.sql is:

it will create the MYSQL_DATABASE

create database `robot` default character set utf8mb4 collate utf8mb4_0900_ai_ci;

the content of robot.sql likes:

it will specify the DATABASE used by docker compose.

-- i add this line ahead of the file
USE robot;

/*
 Navicat Premium Data Transfer

 Source Server         : D
 Source Server Type    : MySQL
*/
......

the content of Dockerfile likes:

add the sql script to the images and initialize it at startup

......
COPY ./01_schema.sql /docker-entrypoint-initdb.d
COPY ./robot.sql /docker-entrypoint-initdb.d

the content of docker-compose.yml file related with mysql likes this:

version: '3.7'
services:
......
  mysql:
    build:
      context: ./mysql
      dockerfile: Dockerfile
    restart: unless-stopped
    container_name: mysql
    environment:
      MYSQL_ROOT_HOST: "%"
      MYSQL_ROOT_PASSWORD: "Hlxx@123prod"
      DB_USER: "root"
      DB_NAMES: "robot"

wish this can make some help to you.

edited Mar 29 '23 at 07:43

answered Mar 29 '23 at 07:10

lemoana

158
1
9

thanks for your help. I got rid of _mysql_db_ errors but still have _backend_ error: `django.db.utils.OperationalError: (2003, "Can't connect to MySQL server on 'mysql_db' ([Errno 111] Connection refused)") ` – Pavel Mar 29 '23 at 12:47
Hi, i supposed you should connect to mysql_db using port 3306 not 33061(*your python settings or your environment* ?) in your project. Hope some help to you, otherwise can refer to here: [django-cannot-connect-mysql-in-docker-compose](https://stackoverflow.com/questions/47979270/django-cannot-connect-mysql-in-docker-compose) – lemoana Mar 29 '23 at 13:43
No, it's translating container ports to host ports. And it was working for once! New Dockerfile for mysql_db `FROM mysql/mysql-server:latest COPY ./docker/mysql/01_init_db.sql /docker-entrypoint-initdb.d/ COPY ./docker/mysql/02_schema.sql /docker-entrypoint-initdb.d/ COPY ./docker/mysql/03_data.sql /docker-entrypoint-initdb.d/ ` If I don't give 03_data.sql everything works but application has no data. If I give 03_data.sql I have such answer: ` ERROR: for backend Container "5e4ca222b8f7" is unhealthy. ERROR: Encountered errors while bringing up the project. ` – Pavel Mar 29 '23 at 15:34
1

I did `docker-compose up` once again and everything went perfectly fine. Don't know why but everything works. Thank you for your help. – Pavel Mar 29 '23 at 15:45

Django can't connect to MySQL in Docker+Django+MySQL

1 Answers1