1

I am unable to connect to my MySQL database from Flask application.

I am learning to build web application with Python Flask from this tutorial but tried modifying some elements of it for experimenting with Docker. Even without using docker-compose, I was unable to connect to the database from the web application.

Let me first give the error traceback in the application log (flask_test container):

[2021-12-20 18:13:18 +0000] [1] [INFO] Starting gunicorn 20.1.0

[2021-12-20 18:13:18 +0000] [1] [INFO] Listening at: http://0.0.0.0:5000 (1)

[2021-12-20 18:13:18 +0000] [1] [INFO] Using worker: sync

[2021-12-20 18:13:18 +0000] [8] [INFO] Booting worker with pid: 8

[2021-12-20 18:13:19,105] INFO in __init__: Microblog startup

[2021-12-20 18:14:19,239] ERROR in app: Exception on /auth/register [POST]

Traceback (most recent call last):

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/connection.py", line 174, in _new_conn

    conn = connection.create_connection(

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/util/connection.py", line 96, in create_connection

    raise err

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/util/connection.py", line 86, in create_connection

    sock.connect(sa)

ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:


Traceback (most recent call last):

  File "/home/flasktest/venv/lib/python3.10/site-packages/elasticsearch/connection/http_urllib3.py", line 255, in perform_request

    response = self.pool.urlopen(

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/connectionpool.py", line 755, in urlopen

    retries = retries.increment(

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/util/retry.py", line 507, in increment

    raise six.reraise(type(error), error, _stacktrace)

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/packages/six.py", line 770, in reraise

    raise value

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/connectionpool.py", line 699, in urlopen

    httplib_response = self._make_request(

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/connectionpool.py", line 394, in _make_request

    conn.request(method, url, **httplib_request_kw)

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/connection.py", line 239, in request

    super(HTTPConnection, self).request(method, url, body=body, headers=headers)

  File "/usr/local/lib/python3.10/http/client.py", line 1282, in request

    self._send_request(method, url, body, headers, encode_chunked)

  File "/usr/local/lib/python3.10/http/client.py", line 1328, in _send_request

    self.endheaders(body, encode_chunked=encode_chunked)

  File "/usr/local/lib/python3.10/http/client.py", line 1277, in endheaders

    self._send_output(message_body, encode_chunked=encode_chunked)

  File "/usr/local/lib/python3.10/http/client.py", line 1037, in _send_output

    self.send(msg)

  File "/usr/local/lib/python3.10/http/client.py", line 975, in send

    self.connect()

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/connection.py", line 205, in connect

    conn = self._new_conn()

  File "/home/flasktest/venv/lib/python3.10/site-packages/urllib3/connection.py", line 186, in _new_conn

    raise NewConnectionError(

urllib3.exceptions.NewConnectionError: <urllib3.connection.HTTPConnection object at 0x7f73470be7d0>: Failed to establish a new connection: [Errno 111] Connection refused

And this is the MySQL container (mysql_test) log:

2021-12-20T18:13:14.094155Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.

2021-12-20T18:13:14.098891Z 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.

2021-12-20T18:13:14.110089Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2021-12-20T18:13:14.110149Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

mbind: Operation not permitted

mbind: Operation not permitted

2021-12-20 18:13:10+00:00 [Note] [Entrypoint]: Creating database test_db

2021-12-20 18:13:10+00:00 [Note] [Entrypoint]: Creating user test_user

2021-12-20 18:13:10+00:00 [Note] [Entrypoint]: Giving user test_user access to schema test_db


2021-12-20 18:13:10+00:00 [Note] [Entrypoint]: Stopping temporary server

2021-12-20 18:13:13+00:00 [Note] [Entrypoint]: Temporary server stopped


2021-12-20 18:13:13+00:00 [Note] [Entrypoint]: MySQL init process done. Ready for start up.

Here is the starting point of Python application (microblog.py):

from app_pkg import create_app, cli

app = create_app()
cli.register(app)

Here is the model class:

class User(UserMixin, SearchableMixin, db.Model):
    __searchable__ = ['username']
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(64), index=True, unique=True)
    email = db.Column(db.String(120), index=True, unique=True)
    password_hash = db.Column(db.String(128))
    posts = db.relationship('Post', backref='author', lazy='dynamic')
    about_me = db.Column(db.String(140))
    last_seen = db.Column(db.DateTime, default=datetime.utcnow)
    followed = db.relationship(
        'User', secondary=followers,
        primaryjoin=(followers.c.follower_id == id),
        secondaryjoin=(followers.c.followed_id == id),
        backref=db.backref('followers', lazy='dynamic'), lazy='dynamic')

This is my compose.yaml:

version: '3'

services:

  python_app:
    container_name: flask_test
    build: .
    env_file: .env
    ports:
      - 8000:5000
    links:
      - mysqldb:dbserver
    depends_on:
      mysqldb:
        condition: service_healthy

  mysqldb:
    container_name: mysql_test
    image: mysql:latest
    env_file: database.conf
    volumes:
      - db-data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 3s
      retries: 5
      start_period: 30s

volumes:
  db-data:

This is my Dockerfile:

FROM python:slim

RUN useradd flasktest

WORKDIR /home/flasktest

COPY requirements.txt requirements.txt
RUN python -m venv venv
RUN venv/bin/pip install -r requirements.txt
RUN venv/bin/pip install gunicorn pymysql cryptography

COPY app_pkg app_pkg
COPY migrations migrations
COPY microblog.py config.py boot.sh ./
RUN chmod +x boot.sh

ENV FLASK_APP microblog.py

RUN chown -R flasktest:flasktest ./
USER flasktest

EXPOSE 5000
ENTRYPOINT ["./boot.sh"]

And finally, this is the boot.sh:

#!/bin/bash

source venv/bin/activate
while true; do
    flask db upgrade
    if [[ "$?" == "0" ]]; then
        break
    fi
    echo Upgrade command failed, retrying in 5 secs...
    sleep 5
done
exec gunicorn -b :5000 --access-logfile - --error-logfile - microblog:app

And these are some necessary environment variables that are being used in the application:

DATABASE_URL=mysql+pymysql://test_user:abc123@dbserver/test_db
MYSQL_ROOT_PASSWORD=root123
MYSQL_DATABASE=test_db
MYSQL_USER=test_user
MYSQL_PASSWORD=abc123

Sorry if the question has become too lengthy. I wanted to give as much detail as possible in the question. Let me know if any other details is required. I have been trying to debug this issue for the past week, but am unable to find a way to connect the app with the sql server.

Also let me know if I should try any specific method to try to debug this issue.

Edit: create_app function:

from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_login import LoginManager
from flask_mail import Mail
from flask_bootstrap import Bootstrap
from flask_moment import Moment

db = SQLAlchemy()
migrate = Migrate()
login = LoginManager()
login.login_view = 'auth.login'
login.login_message = _l('Please log in to access this page.')
mail = Mail()
bootstrap = Bootstrap()
moment = Moment()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(config_class)

    db.init_app(app)
    migrate.init_app(app, db)
    login.init_app(app)
    mail.init_app(app)
    bootstrap.init_app(app)
    moment.init_app(app)

    from app_pkg.errors import bp as errors_bp
    app.register_blueprint(errors_bp)
    
    from app_pkg.auth import bp as auth_bp
    app.register_blueprint(auth_bp, url_prefix='/auth')
    
    from app_pkg.main import bp as main_bp
    app.register_blueprint(main_bp)

I tried changing DATABASE_URL from mysql+pymysql://test_user:abc123@dbserver/test_db to mysql+pymysql://test_user:abc123@mysqldb/test_db, but the issue still persists.

I also tried adding 'ports: -3306:3306' to compose.yaml and changing DATABASE_URL to 0.0.0.0 as host, but this is giving this error:

[+] Running 3/4
 - Network flask_tutorial_default   Created                                                                             0.7s 
 - Volume "flask_tutorial_db-data"  Created                                                                             0.0s
 - Container mysql_test             Starting                                                                            2.4s 
 - Container flask_test             Created                                                                             0.2s
Error response from daemon: Ports are not available: listen tcp 0.0.0.0:3306: bind: Only one usage of each socket address (protocol/network address/port) is normally permitted.
Improve this question

1 Answer 1

Reset to default
1

You are currently using the docker-compose dns feature in which you can use the container name as domain name for services running in docker-compose, thats neat - unless when you rename containers ;) Did you rename the mysqldb from dbserver?

If you want to continue using this feature, modify the env vars as so: (change dbserver to mysqldb)

DATABASE_URL=mysql+pymysql://test_user:abc123@mysqldb/test_db
...

If you instead what to use a more explicit approach:

In your docker-compose, you need to bind the port 3306 to your host network by

version: '3'

services:

  python_app:
    container_name: flask_test
    build: .
    env_file: .env
    ports:
      - 8000:5000
    links:
      - mysqldb:dbserver
    depends_on:
      mysqldb:
        condition: service_healthy

  mysqldb:
    container_name: mysql_test
    image: mysql:latest
    env_file: database.conf
    volumes:
      - db-data:/var/lib/mysql
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 3s
      retries: 5
      start_period: 30s
    ports:
      - 3306:3306
...

Then change the env var to:

DATABASE_URL=mysql+pymysql://test_user:[email protected]/test_db

Thanks for all the info, you did not post too much - needed it all :)

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hi, the first approach did not solve the issue. I changed DATABASE_URL from dbserver to mysqldb and changed 'links' of 'python_app' from mysqldb:dbserver to mysqldb, but still the same issue is coming. In the second approach, another error is coming, Error response from daemon: Ports are not available: listen tcp 0.0.0.0:3306: bind: Only one usage of each socket address (protocol/network address/port) is normally permitted. Could you please direct me to some source material where I can read about various arguments (e.g. links, network, depends_on) that can be used in 'compose.yaml'?
Btw, thanks a lot for taking the time and effort to read such a long question, I really appreciate it. I also added a few more details to the question in case you need it. If you need any more details, please let me know.
Hi Asif, no worries :) you already have something running on port 3306, probably mysql or another container. Can you check?
Hey again Asif, I see you are in India and Im in Europe, so the timediff might make it hard to debug together. If you want, you can add me to your repo, my Github handle is ChristofferNissen, and i will gladly help debug it locally to get you off the ground - when i have a PR waiting for colleagues :)
I think that might be the issue. I had a local MySQL instance running in the system. I will check it and let you know if terminating the local instance helps. My GitHub repo is: github.com/asif256000/flask_first_test/tree/Docker . And thank you again for all your help.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.