I am trying to connect mysql container with my python flask app and I have this error when running docker-compose build --no-cache && docker-compose up:
docker-compose build --no-cache && docker-compose up
Creating network "hdbresalepricealert_sql_network" with the default driver Creating hdbresalepricealert_mysql_1 ... done Creating hdbresalepricealert_python_app_1 ... done Attaching to hdbresalepricealert_mysql_1, hdbresalepricealert_python_app_1 mysql_1 | 2023-12-22 04:48:45+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.2.0-1.el8 started. mysql_1 | 2023-12-22 04:48:46+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql' mysql_1 | 2023-12-22 04:48:46+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.2.0-1.el8 started. mysql_1 | '/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock' mysql_1 | 2023-12-22T04:48:46.269421Z 0 [System] [MY-015015] [Server] MySQL Server - start. mysql_1 | 2023-12-22T04:48:46.860627Z 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_1 | 2023-12-22T04:48:46.866169Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.2.0) starting as process 1 mysql_1 | 2023-12-22T04:48:46.891660Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. mysql_1 | 2023-12-22T04:48:47.157634Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. mysql_1 | 2023-12-22T04:48:47.482067Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. mysql_1 | 2023-12-22T04:48:47.482195Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. mysql_1 | 2023-12-22T04:48:47.485815Z 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_1 | 2023-12-22T04:48:47.512210Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock mysql_1 | 2023-12-22T04:48:47.512345Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.2.0' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL. python_app_1 | INFO:services.dbService:Entering create tables method from dbservice python_app_1 | ERROR:services.dbService:Unable to create tables due to 2003 (HY000): Can't connect to MySQL server on 'mysql:3307' (111) python_app_1 | * Serving Flask app 'app' python_app_1 | * Debug mode: on python_app_1 | INFO:werkzeug:WARNING: This is a development server. Do not use it in a production deployment. Use a production WSGI server instead. python_app_1 | * Running on all addresses (0.0.0.0) python_app_1 | * Running on http://127.0.0.1:5000 python_app_1 | * Running on http://172.22.0.3:5000 python_app_1 | INFO:werkzeug:Press CTRL+C to quit python_app_1 | INFO:werkzeug: * Restarting with stat python_app_1 | INFO:services.dbService:Entering create tables method from dbservice python_app_1 | ERROR:services.dbService:Unable to create tables due to 2003 (HY000): Can't connect to MySQL server on 'mysql:3307' (111) python_app_1 | WARNING:werkzeug: * Debugger is active! python_app_1 | INFO:werkzeug: * Debugger PIN: 111-520-781
My docker-compose file:
version: '3' services: mysql: image: mysql:latest ports: - "3307:3306" environment: MYSQL_ROOT_PASSWORD: root MYSQL_DATABASE: db MYSQL_USER: user MYSQL_PASSWORD: password networks: - sql_network volumes: - /mysql_data :/var/lib/mysql python_app: build: . ports: - "5000:5000" networks: - sql_network depends_on: - mysql networks: sql_network:
Relevant portion of the dbService.py:
config = { 'host': 'mysql', # This should match the service name in Docker Compose 'port': '3307', # This should match the exposed port on the host 'user': 'user', 'password': 'password', 'database': 'db', } def create_tables(): logger.info("Entering create tables method from dbservice") try: connection = mysql.connector.connect(**config) cursor = connection.cursor() # Check if 'emails' table already exists cursor.execute("SHOW TABLES LIKE 'emails'") table_exists = cursor.fetchone() if not table_exists: cursor.execute( ''' CREATE TABLE emails ( id INT PRIMARY KEY AUTO_INCREMENT, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, email VARCHAR(255) NOT NULL, verified BOOLEAN NOT NULL, flatType VARCHAR(255) NOT NULL, streetName VARCHAR(255) NOT NULL, blkFrom INT NOT NULL, blkTo INT NOT NULL, lastSent TIMESTAMP, token VARCHAR(255)) ''') connection.commit() connection.close() except Exception as e: logger.error(f"Unable to create tables due to {e}")
My dockerfile:
FROM python:3.12.1-bullseye ARG REPO_DIR="." ARG PROJECT_USER="randy" ARG HOME_DIR="/home/$PROJECT_USER" ARG DESTINATION_FOLDER="hdb" WORKDIR $HOME_DIR # The copy is from local (where the docker command is executed) to this COPY $REPO_DIR $DESTINATION_FOLDER RUN pip install -r $DESTINATION_FOLDER/app/run-requirements.txt RUN groupadd -g 2222 $PROJECT_USER && useradd -u 2222 -g 2222 -m $PROJECT_USER RUN chown -R 2222:2222 $HOME_DIR && \ rm /bin/sh && ln -s /bin/bash /bin/sh USER 2222 WORKDIR $HOME_DIR/${DESTINATION_FOLDER} CMD [ "python3", "app/app.py" ]
Relevant portion of my app.py:
time.sleep(10) create_tables()
Essentially I’m trying to create the tables first using the create tables function in the dbservice.py, I included the wait as I read that if I connected before the mysql container is up there might be some issues.
I’m able to connect to the sql container using docker exec -it <container image> bash and login, no problem, I see that the container is up and running in docker-compose ps. I just cannot connect to the mysql container when running docker-compose build --no-cache && docker-compose up. Its been bugging me for days and I have scoured the web for all possible solutions including chatgpt but to no avail. I think it may be some small gotcha that I’ve missed. Can anyone help?
docker exec -it <container image> bash
The error you’re encountering (2003 (HY000): Can't connect to MySQL server on 'mysql:3307' (111)) suggests that the Python application (python_app) is unable to connect to the MySQL server. Here are a few things you can check and try to resolve the issue:
2003 (HY000): Can't connect to MySQL server on 'mysql:3307' (111)
python_app
config
dbService.py
docker-compose.yml
'mysql'
config = { 'host': 'mysql', # Check if this matches the service name in Docker Compose 'port': '3307', # Check if this matches the exposed port on the host 'user': 'user', 'password': 'password', 'database': 'db', }
3307
services: mysql: ports: - "3307:3306"
docker-compose logs mysql
Look for any errors or issues reported in the MySQL container logs.
Wait for MySQL to be Ready: Although you have added a time.sleep(10) in your app.py to wait for MySQL, it might be beneficial to use a more robust solution. You can use tools like wait-for-it or dockerize to wait for the MySQL service to be ready before starting your Python application. Add one of these tools to your python_app Dockerfile and use it in your entry script.
time.sleep(10)
app.py
wait-for-it
dockerize
wait-for-it: https://github.com/vishnubob/wait-for-it
Example:
CMD ["./wait-for-it.sh", "mysql:3307", "--", "python3", "app/app.py"]
Check Firewall Settings: Ensure that there are no firewall issues preventing the Python application from connecting to the MySQL server. Check if any firewall rules are blocking the connection.
Network Connectivity: Ensure that the Python application container is in the same Docker network as the MySQL container. In your case, both containers are in the sql_network network, which seems correct.
sql_network
Try these steps and see if it resolves the connectivity issue. If the problem persists, checking the MySQL container logs (docker-compose logs mysql) might provide additional clues about what’s going wrong.
