Ways to fix issue 2003 (HY000): Unable to connect to MySQL server 'db_mysql:3306' (111)

Question

I recently tried containerizing my FastAPI Python server (also for replication/doubling purposes). Before, I had just a MySQL server in a Docker container and everything was fine, but when I also made my web server a service, it couldn't connect to the MySQL server, so now the application doesn't work.

The following is a code snippet for the server database initialization connector in the application

<code>from fastapi import FastAPI
import mysql.connector


app = FastAPI()

dbconfig = {
  "host": "localhost",
  "database": "server_db",
  "user": "db_user",
  "password": "user-password"
}

# Check database connection
try:
    init_cnx = mysql.connector.connect(
        host='localhost',
        user='db_user',
        password='user-password'
    )
    cursor = init_cnx.cursor()
    cursor.execute("SHOW DATABASES LIKE 'server_db'")
    if cursor.fetchone() == None:
        # If the database does not exist, create the database
        cursor.execute("CREATE DATABASE server_db")
        cursor.execute("USE server_db")
        cursor.execute("CREATE TABLE Messages ("
                        "message_id INT NOT NULL AUTO_INCREMENT,"
                        "sender_name VARCHAR(32),"
                        "message_text VARCHAR(64),"
                        "created_at DATE,"
                        "user_messages_count INT,"
                        "PRIMARY KEY (message_id));")
        print('Database has been created!')
    cursor.close()
    init_cnx.close()
except mysql.connector.Error as err:
    print("An error occurred in init_cnx:", err)


# Database I/O function
async def execute_db_query(query, cursor_buffered=False):
    cnx = mysql.connector.connect(**dbconfig)
    try:
        cursor = cnx.cursor(buffered=cursor_buffered)
        cursor.execute("USE server_db")
        cursor.execute(query)
        result = cursor.fetchall()
        cnx.commit()
        print("Query executed successfully!")
        return result
    except Exception as e:
        print("Error while executing query:", e)
    finally:
        if cnx:
            cnx.close()


# Get the root directory function, just used to check whether the application is connected to the database
@app.get("/")
async def get_root():
    try:
        entries_count = await execute_db_query("SELECT COUNT(*) FROM Messages", cursor_buffered=True)
        return {"Messages entries": entries_count[0][0]}
    except Exception as e:
        return {"Error": e}
</code>

Server’s Dockerfile

FROM python:3.11.4-slim-bookworm

WORKDIR/app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY server.py .
EXPOSE 8000
CMD ["uvicorn", "server:app", "--host", "0.0.0.0", "--port", "8000"]

init.sql script

<code>CREATE USER 'db_user'@'%' IDENTIFIED BY 'user-password';
GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
</code>

and docker-compose.yml

<code>version: "3.8"
services:
  db_mysql:
    image: mysql:8
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: "root"
    volumes:
      - "./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql"
      - "./mysql/db_mysql_data:/var/lib/mysql"
      - "./mysql/mysql_logs:/var/log/mysql"
    networks:
      -dummy_network

  server_1:
    image: dummy_msg_server
    ports:
      - "8081:8000"
    networks:
      -dummy_network
    #command: sh -c "sleep 60s"
    depends_on:
      -db_mysql

  server_2:
    image: dummy_msg_server
    ports:
      - "8082:8000"
    networks:
      -dummy_network
    #command: sh -c "sleep 60s"
    depends_on:
      -db_mysql

volumes:
  db_mysql_data: #external: true

networks:
  dummy_network:
    driver: bridge
</code>

Although trying to use the API before the MySQL container has been fully initialized may result in an error, this is not a problem because I wait until the MySQL server indicates it is ready to handle the request. Other than that, I'm not trying to connect to the MySQL server.

I tried to connect using hostname/IP address. Try changing the python:3.11.4 image in the dockerfile to an earlier debian version and not using the slim image. Try to explicitly use a public network for the container. Docker keeps showing that the container is in a network, and the curl request from the server container returns something. Additionally, docker-compose.yml previously provided port 3306:3306 for the db_mysql service. Guess that's not a problem either.

Update 1. During investigation, it was discovered that if the database has already been created, the application has no problem sending requests to it and getting the correct response.The only problem with it is that the database cannot be created using the create script in the code.

(Guess, I should update the code block since the project is in another phase now.)

Answer 1

I ran into an issue where the server and database containers were started at the same time, causing issues. The first (and last) attempt to connect to the database server occurs before the database server is ready to accept the connection.

To solve this problem, I decided to add a health check in the docker-compose.yml file:

version: "3.8"
services:
  db_mysql:
    image: mysql:8
    restart: always
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: "root"
    volumes:
      - "./mysql/init.sql:/docker-entrypoint-initdb.d/init.sql"
      - "./mysql/db_mysql_data:/var/lib/mysql"
      - "./mysql/mysql_logs:/var/log/mysql"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-uroot", "-proot"]
      timeout: 1s
      interval: 40s
      retries: 5

  server_1:
    build:
      context: .
      dockerfile: Dockerfile
    restart: always
    ports:
      - "8081:8000"
    depends_on:
      db_mysql:
        condition: service_healthy

  server_2:
    build:
      context: .
      dockerfile: Dockerfile
    restart: always
    ports:
      - "8082:8000"
    depends_on:
      db_mysql:
        condition: service_healthy

volumes:
  db_mysql_data: #external: true

With this configuration, the server's container will not start until the health check confirms that the database server is ready.

However, there is a potentially better way of handling this situation, which involves using the wait-for-it.sh script. I personally know some experienced backend developers who also use Docker containers to split their applications into microservices. They expressed positive comments about using this script. Although I haven't personally tried it, I recommend considering it as an alternative solution.