Home > OS >  "Can't connect to MySQL server" While trying to connect MYSQL database to Django-REST
"Can't connect to MySQL server" While trying to connect MYSQL database to Django-REST

Time:01-11

My configuration is as follows:

I am running a Django-REST backend, with a MySQL database. I am trying to run the Django backend in its own Docker container, as well as running a MySQL database in its own Django container. It seems that Django is not able to connect to the MySQL database when my containers are running.

Database settings in Django:

DATABASES = {
    "default": {
        "ENGINE": os.environ.get("SQL_ENGINE", "django.db.backends.sqlite3"),
        "NAME": os.environ.get("SQL_DATABASE", BASE_DIR / "db.sqlite3"),
        "USER": os.environ.get("SQL_USER", "user"),
        "PASSWORD": os.environ.get("SQL_PASSWORD", "password"),
        "HOST": os.environ.get("SQL_HOST", "localhost"),
        "PORT": os.environ.get("SQL_PORT", "5432"),
    }
}

Dockerfile:

FROM python:3.10.2-slim-buster

ENV PYTHONUNBUFFERED 1
RUN mkdir /code
WORKDIR /code
RUN apt update \
    && apt install -y --no-install-recommends python3-dev \
    default-libmysqlclient-dev build-essential default-mysql-client \
    && apt autoclean
RUN pip install --no-cache-dir --upgrade pip
COPY ./requirements.txt /code/
RUN pip install --no-cache-dir -r requirements.txt
COPY ./neura-dbms-backend /code/
EXPOSE 7000

Requirements.txt:

Django
djangorestframework
django-cors-headers
requests
boto3
django-storages
pytest
mysqlclient==2.1.1
django-use-email-as-username
djangorestframework-simplejwt
gunicorn

docker-compose.yml:

version: "3.8"

services:
  neura-dbms-backend:
    build:
      context: ./DBMS/neura-dbms-backend

    command: [sh, -c, "python manage.py runserver 0.0.0.0:7000"]
    image: neura-dbms-backend
    container_name: neura-dbms-backend
    volumes: 
      - ./DBMS/neura-dbms-backend/neura-dbms-backend:/code
    ports:
      - 7000:7000
    networks:
      - docker-network
    environment:
      - DEBUG=1
      - SECRET_KEY=${SECRET_KEY_DBMS}
      - DJANGO_ALLOWED_HOSTS=${DJANGO_ALLOWED_HOSTS}
      - DJANGO_ALLOWED_ORIGINS=${DJANGO_ALLOWED_ORIGINS}
      - JWT_KEY=${JWT_KEY}

      - SQL_ENGINE=django.db.backends.mysql
      - SQL_DATABASE=db_neura_dbms
      - SQL_USER=neura_dbms_user
      - SQL_PASSWORD=super_secure_password
      - SQL_HOST=db_neura_dbms
      - SQL_PORT=5432
    
    depends_on:
      - "db_neura_dbms"

  db_neura_dbms:
    image: mysql:latest
    volumes:
      - mysql_data_db_neura_dbms:/var/lib/mysql/
    environment:
      - MYSQL_DATABASE=db_neura_dbms
      - MYSQL_USER=neura_dbms_user
      - MYSQL_PASSWORD=super_secure_password
      - MYSQL_ROOT_PASSWORD=super_secure_password
    networks:
      - docker-network

networks:
  docker-network:
    driver: bridge

volumes:
  mysql_data_db_neura_dbms:

I am able to build images for Django and the Database, but when I try to run the containers, I get the following error from the Django container:

neura-dbms-backend    | System check identified no issues (0 silenced).
neura-dbms-backend    | Exception in thread django-main-thread:
neura-dbms-backend    | Traceback (most recent call last):
neura-dbms-backend    |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/base.py", line 282, in ensure_connection
neura-dbms-backend    |     self.connect()
neura-dbms-backend    |   File "/usr/local/lib/python3.10/site-packages/django/utils/asyncio.py", line 26, in inner
neura-dbms-backend    |     return func(*args, **kwargs)
neura-dbms-backend    |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/base.py", line 263, in connect
neura-dbms-backend    |     self.connection = self.get_new_connection(conn_params)
neura-dbms-backend    |   File "/usr/local/lib/python3.10/site-packages/django/utils/asyncio.py", line 26, in inner
neura-dbms-backend    |     return func(*args, **kwargs)
neura-dbms-backend    |   File "/usr/local/lib/python3.10/site-packages/django/db/backends/mysql/base.py", line 247, in get_new_connection
neura-dbms-backend    |     connection = Database.connect(**conn_params)
neura-dbms-backend    |   File "/usr/local/lib/python3.10/site-packages/MySQLdb/__init__.py", line 123, in Connect
neura-dbms-backend    |     return Connection(*args, **kwargs)
neura-dbms-backend    |   File "/usr/local/lib/python3.10/site-packages/MySQLdb/connections.py", line 185, in __init__
neura-dbms-backend    |     super().__init__(*args, **kwargs2)
neura-dbms-backend    | MySQLdb.OperationalError: (2002, "Can't connect to MySQL server on 'db_neura_dbms' (115)")

What am I missing? Thanks!

CodePudding user response:

So I added a script so that Django waits for the mysql database to be ready before it connects:

#!/bin/bash

if [ "$SQL_HOST" = "db" ]
then
    echo "Waiting for mysql..."

    while !</dev/tcp/$SQL_HOST/$SQL_PORT; do sleep 1; done;

    echo "MySQL started"
fi

# python manage.py migrate

exec "$@"

When I first run the Docker containers, it seems that MySQL runs through some sort of setup, Django then tries to connect and fails.

If I then kill the containers, and run them again, the MySQL setup is finished, and Django is able to connect to the database. I wonder if there is a way for Django to wait for this setup to be finished as well?

CodePudding user response:

depends_on only waits till the database container is started, but in this case, after the container is started it still takes some time for mysql to make the system ready for connection.

what you can do is create a command file (This is for postgres, you can make one for yours, you will need to add raised mysql exception instead of Psycopg2Error )

import time
from psycopg2 import OperationalError as Psycopg2Error
from django.db.utils import OperationalError
from django.core.management.base import BaseCommand


class Command(BaseCommand):
    """
    Django command to wait for database
    """

    def handle(self, *args, **options):
        """
        Command entrypoint
        """

        self.stdout.write("Checking database availability\n")
        db_up = False
        seconds_cnt = 0
        while not db_up:
            try:
                self.check(databases=['default'])
                db_up = True
                self.stdout.write(
                    self.style.WARNING(
                        "Available within {} seconds".format(seconds_cnt)))
                self.stdout.write(self.style.SUCCESS("Database available!"))
            except(Psycopg2Error, OperationalError):
                seconds_cnt  = 1

                self.stdout.write(
                    self.style.WARNING(
                        "Database unavailable waiting... {} seconds"
                        .format(seconds_cnt)))
                time.sleep(1)

Your command can be updated with,

command: >
  sh -c "python manage.py wait_for_db &&
         python manage.py runserver 0.0.0.0:87000"
  • Related