Problem in Connecting FastAPI Server and MySQL databse using Docker-Compose

Question

I have been trying to connect my FastAPI server and MySQL Database using Docker-compose. However, it keeps showing the following error when I run docker-compose up --build.

Some of the posts (e.g., Post1) on the Internet and StackOverflow suggest changing the value of DB_HOST similar to the MySQL service name, specified in the docker-compose.yml file. I tried that option as well, but I am still getting the same error.

Could you please help me, with what changes I need to make in order to run MySQL and FastAPI server using docker-compose?

The following is my docker-compose.yml file

version: '3.8'

services:
  mysql-db:
    container_name: mysql-db
    image: mysql:latest
    platform: linux/amd64
    environment:
      - MYSQL_DATABASE='admin'
      - MYSQL_ROOT_PASSWORD='admin'
    expose:
      - "3306"
    ports:
      - "3306:3306"
  backend-server:
    container_name: "backend-server"
    build: .    
    ports:
      - "8000:8000"
    depends_on:
      - mysql-db

My database configuration file is the following

from sqlalchemy import create_engine, MetaData

DB_USER = "root"
DB_PASSWORD = "admin"
DB_HOST = "mysql-db"
DB_PORT = "3306"
DATABASE = "admin"


connect_string = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DATABASE)


engine = create_engine(connect_string)
meta = MetaData()
conn = engine.connect()

My Dockerfile for FastAPI Server

FROM python:3.9
WORKDIR '/backend' 
COPY ./requirements.txt  ./
RUN pip install --trusted-host pypi.python.org -r requirements.txt
COPY .  .   
CMD ["uvicorn", "main:app"]

My error log is as follows:

Starting mysql-db ... done
Recreating backend-server ... done
Attaching to mysql-db, backend-server
mysql-db          | 2022-03-20 20:57:57+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.28-1debian10 started.
backend-server    | Traceback (most recent call last):
backend-server    |   File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 613, in connect
backend-server    |     sock = socket.create_connection(
backend-server    |   File "/usr/local/lib/python3.9/socket.py", line 844, in create_connection
backend-server    |     raise err
backend-server    |   File "/usr/local/lib/python3.9/socket.py", line 832, in create_connection
backend-server    |     sock.connect(sa)
backend-server    | ConnectionRefusedError: [Errno 111] Connection refused
backend-server    | 
backend-server    | During handling of the above exception, another exception occurred:
backend-server    | 
backend-server    | Traceback (most recent call last):
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3256, in _wrap_pool_connect
backend-server    |     return fn()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
backend-server    |     return _ConnectionFairy._checkout(self)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
backend-server    |     fairy = _ConnectionRecord.checkout(pool)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
backend-server    |     rec = pool._do_get()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
backend-server    |     self._dec_overflow()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
backend-server    |     compat.raise_(
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
backend-server    |     raise exception
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
backend-server    |     return self._create_connection()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
backend-server    |     return _ConnectionRecord(self)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
backend-server    |     self.__connect()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
backend-server    |     pool.logger.debug("Error on connect(): %s", e)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
backend-server    |     compat.raise_(
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
backend-server    |     raise exception
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
backend-server    |     self.dbapi_connection = connection = pool._invoke_creator(self)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
backend-server    |     return dialect.connect(*cargs, **cparams)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 597, in connect
backend-server    |     return self.dbapi.connect(*cargs, **cparams)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
backend-server    |     self.connect()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
backend-server    |     raise exc
backend-server    | pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on 'mysql-db' ([Errno 111] Connection refused)")
backend-server    | 
backend-server    | The above exception was the direct cause of the following exception:
backend-server    | 
backend-server    | Traceback (most recent call last):
backend-server    |   File "/usr/local/bin/uvicorn", line 8, in <module>
backend-server    |     sys.exit(main())
backend-server    |   File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1128, in __call__
backend-server    |     return self.main(*args, **kwargs)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1053, in main
backend-server    |     rv = self.invoke(ctx)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1395, in invoke
backend-server    |     return ctx.invoke(self.callback, **ctx.params)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/click/core.py", line 754, in invoke
backend-server    |     return __callback(*args, **kwargs)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/uvicorn/main.py", line 437, in main
backend-server    |     run(app, **kwargs)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/uvicorn/main.py", line 463, in run
backend-server    |     server.run()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/uvicorn/server.py", line 60, in run
backend-server    |     return asyncio.run(self.serve(sockets=sockets))
backend-server    |   File "/usr/local/lib/python3.9/asyncio/runners.py", line 44, in run
backend-server    |     return loop.run_until_complete(main)
backend-server    |   File "/usr/local/lib/python3.9/asyncio/base_events.py", line 647, in run_until_complete
backend-server    |     return future.result()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/uvicorn/server.py", line 67, in serve
backend-server    |     config.load()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/uvicorn/config.py", line 458, in load
backend-server    |     self.loaded_app = import_from_string(self.app)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/uvicorn/importer.py", line 21, in import_from_string
backend-server    |     module = importlib.import_module(module_str)
backend-server    |   File "/usr/local/lib/python3.9/importlib/__init__.py", line 127, in import_module
backend-server    |     return _bootstrap._gcd_import(name[level:], package, level)
backend-server    |   File "<frozen importlib._bootstrap>", line 1030, in _gcd_import
backend-server    |   File "<frozen importlib._bootstrap>", line 1007, in _find_and_load
backend-server    |   File "<frozen importlib._bootstrap>", line 986, in _find_and_load_unlocked
backend-server    |   File "<frozen importlib._bootstrap>", line 680, in _load_unlocked
backend-server    |   File "<frozen importlib._bootstrap_external>", line 850, in exec_module
backend-server    |   File "<frozen importlib._bootstrap>", line 228, in _call_with_frames_removed
backend-server    |   File "/backend/./main.py", line 2, in <module>
backend-server    |     from models.dbschema import  dbExceptionMessage, dbMaterialMaster
backend-server    |   File "/backend/./models/dbschema.py", line 3, in <module>
backend-server    |     from config.db import meta, engine
backend-server    |   File "/backend/./config/db.py", line 15, in <module>
backend-server    |     conn = engine.connect()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3210, in connect
backend-server    |     return self._connection_cls(self, close_with_result=close_with_result)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 96, in __init__
backend-server    |     else engine.raw_connection()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3289, in raw_connection
backend-server    |     return self._wrap_pool_connect(self.pool.connect, _connection)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3259, in _wrap_pool_connect
backend-server    |     Connection._handle_dbapi_exception_noconnection(
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2106, in _handle_dbapi_exception_noconnection
backend-server    |     util.raise_(
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
backend-server    |     raise exception
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 3256, in _wrap_pool_connect
backend-server    |     return fn()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 310, in connect
backend-server    |     return _ConnectionFairy._checkout(self)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 868, in _checkout
backend-server    |     fairy = _ConnectionRecord.checkout(pool)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 476, in checkout
backend-server    |     rec = pool._do_get()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 146, in _do_get
backend-server    |     self._dec_overflow()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
backend-server    |     compat.raise_(
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
backend-server    |     raise exception
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/impl.py", line 143, in _do_get
backend-server    |     return self._create_connection()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 256, in _create_connection
backend-server    |     return _ConnectionRecord(self)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 371, in __init__
backend-server    |     self.__connect()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 666, in __connect
backend-server    |     pool.logger.debug("Error on connect(): %s", e)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
backend-server    |     compat.raise_(
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
backend-server    |     raise exception
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/pool/base.py", line 661, in __connect
backend-server    |     self.dbapi_connection = connection = pool._invoke_creator(self)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/create.py", line 590, in connect
backend-server    |     return dialect.connect(*cargs, **cparams)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 597, in connect
backend-server    |     return self.dbapi.connect(*cargs, **cparams)
backend-server    |   File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 353, in __init__
backend-server    |     self.connect()
backend-server    |   File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 664, in connect
backend-server    |     raise exc
backend-server    | sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'mysql-db' ([Errno 111] Connection refused)")
backend-server    | (Background on this error at: https://sqlalche.me/e/14/e3q8)
backend-server exited with code 1
mysql-db          | 2022-03-20 20:57:57+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
mysql-db          | 2022-03-20 20:57:57+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.28-1debian10 started.
mysql-db          | 2022-03-20T20:57:59.047590Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.28) starting as process 1
mysql-db          | 2022-03-20T20:57:59.145611Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
mysql-db          | 2022-03-20T20:57:59.171249Z 1 [ERROR] [MY-012585] [InnoDB] Linux Native AIO interface is not supported on this platform. Please check your OS documentation and install appropriate binary of InnoDB.
mysql-db          | 2022-03-20T20:57:59.171538Z 1 [Warning] [MY-012654] [InnoDB] Linux Native AIO disabled.
mysql-db          | 2022-03-20T20:57:59.678151Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
mysql-db          | 2022-03-20T20:58:00.317516Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
mysql-db          | 2022-03-20T20:58:00.330970Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
mysql-db          | 2022-03-20T20:58:00.475268Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
mysql-db          | 2022-03-20T20:58:00.476155Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
mysql-db          | 2022-03-20T20:58:00.481201Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
mysql-db          | 2022-03-20T20:58:00.599555Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
mysql-db          | 2022-03-20T20:58:00.601446Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

The "connection refused" error, followed by the database startup messages, indicates your application is starting up before the database is ready to accept connections. Docker/Compose/`depends_on:` on its own isn't quite able to ensure the database is fully functional before the application starts up, but see [Docker Compose wait for container X before starting Y](https://stackoverflow.com/questions/31746182/docker-compose-wait-for-container-x-before-starting-y) for some alternatives. — David Maze, Mar 20 '22 at 21:22

score 2 · Accepted Answer · answered Mar 22 '22 at 14:44

As stated in the log, the database server refuse to connect, so it seems like the problem is with neither FastAPI nor docker-compose; because your configurations are correct. (Assuming that you tried to connect MySQL database from some other code or server. In other words, you are sure you can connect to database from a client.)

You are using PyMySQL connection for SQLAlchemy. So, as stated in the documentation, your database connection string should be like:

mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

The difference is the port attribute.

So, if you try a connection string like following, you should be able to connect to the database:

connect_string = 'mysql+pymysql://{}:{}@{}/{}?port={}?charset=utf8'.format(DB_USER, DB_PASSWORD, DB_HOST, DATABASE, DB_PORT)

NOTE: Also, in some examples, I saw adding ?host= option in addition to port; so you may try to use that too if it doesn't work.

Problem in Connecting FastAPI Server and MySQL databse using Docker-Compose

1 Answers1

Linked