Periodic "Lost connection to MySQL server during query" after Dockerizing Flask Web App

Question

I have a Flask web app that used to run on a standalone server using the following:

• Flask/SQLAlchemy
• MariaDB
• uwsgi
• nginx

On the stand alone server this application ran fine.

I have since "dockerized" this application across two containers:

• uwsgi-nginx-flask
• MariaDB

Every since dockerizing I occasionally get this error (entire traceback posted at the end):

Lost connection to MySQL server during query

The MariaDB log shows the following errors with verbose logging:

2020-05-10 18:35:32 130 [Warning] Aborted connection 130 to db: 'flspection2' user: 'fl-server' host: '172.19.0.1' (Got an error reading communication packets)
2020-05-10 18:45:34 128 [Warning] Aborted connection 128 to db: 'flspection2' user: 'fl-server' host: '172.19.0.1' (Got timeout reading communication packets)

This is experienced by the user as a 502 Bad Gateway. If the user refreshes the page, this will often solve the problem. This issue arises at random. I have not been able to reproduce it at will, but over time it will inevitably show up.

What is causing this and how can I solve it?

What I've done:

• Verified that the MariaDB container has a timeout is 28800. I've seen the error occur much sooner than 28800 seconds after restarting all the containers, so I don't think it is actually a timeout issue.
• Set pool_recycle option to 120
• Verified that Flask-SQLAlchemy is using a scoped_session which should avoid these timing out issues.
• Changed network_mode to default per a comment. This did not solve the problem.

My thoughts are that it acts like the connection between flask and the database is unreliable, but as docker containers running on the same host, shouldn't that be quite reliable?

Relevant code:

database.py

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

config.py

class Config:
    ...
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://username:[email protected]/database?charset=utf8mb4'
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_ENGINE_OPTIONS = {
        'pool_recycle': 120
    }
    ...

docker-compose.yml

version: "3.7"
services:
  db:
    restart: "always"
    build: ./docker/db
    volumes:
      - "~/db:/var/lib/mysql"
    environment:
      MYSQL_ROOT_PASSWORD: "password"
      MYSQL_DATABASE: "database"
      MYSQL_USER: "user"
      MYSQL_PASSWORD: "password"
    ports:
      - '3306:3306'
  nginx-uwsgi-flask:
    restart: "always"
    depends_on:
      - "db"
    build:
      context: .
      dockerfile: ./docker/nginx-uwsgi-flask/Dockerfile
    volumes:
      - "~/data/fileshare:/fileshare"
    ports:
      - "80:80"
      - "443:443"
    network_mode: "host"

traceback

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 657, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 707, in _read_bytes
    CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.6/site-packages/flask/_compat.py", line 35, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.6/site-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.6/site-packages/flask_user/decorators.py", line 132, in decorator
    allowed = _is_logged_in_with_confirmed_email(user_manager)
  File "/usr/local/lib/python3.6/site-packages/flask_user/decorators.py", line 17, in _is_logged_in_with_confirmed_email
    if user_manager.call_or_get(current_user.is_authenticated):
  File "/usr/local/lib/python3.6/site-packages/werkzeug/local.py", line 347, in __getattr__
    return getattr(self._get_current_object(), name)
  File "/usr/local/lib/python3.6/site-packages/werkzeug/local.py", line 306, in _get_current_object
    return self.__local()
  File "/usr/local/lib/python3.6/site-packages/flask_login/utils.py", line 26, in <lambda>
    current_user = LocalProxy(lambda: _get_user())
  File "/usr/local/lib/python3.6/site-packages/flask_login/utils.py", line 335, in _get_user
    current_app.login_manager._load_user()
  File "/usr/local/lib/python3.6/site-packages/flask_login/login_manager.py", line 359, in _load_user
    return self.reload_user()
  File "/usr/local/lib/python3.6/site-packages/flask_login/login_manager.py", line 321, in reload_user
    user = self.user_callback(user_id)
  File "/usr/local/lib/python3.6/site-packages/flask_user/user_manager.py", line 130, in load_user_by_user_token
    user = self.db_manager.UserClass.get_user_by_token(user_token)
  File "/usr/local/lib/python3.6/site-packages/flask_user/user_mixin.py", line 51, in get_user_by_token
    user = user_manager.db_manager.get_user_by_id(user_id)
  File "/usr/local/lib/python3.6/site-packages/flask_user/db_manager.py", line 179, in get_user_by_id
    return self.db_adapter.get_object(self.UserClass, id=id)
  File "/usr/local/lib/python3.6/site-packages/flask_user/db_adapters/sql_db_adapter.py", line 48, in get_object
    return ObjectClass.query.get(id)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 924, in get
    ident, loading.load_on_pk_identity)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 1007, in _get_impl
    return db_load_fn(self, primary_key_identity)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 250, in load_on_pk_identity
    return q.one()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2954, in one
    ret = self.one_or_none()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2924, in one_or_none
    ret = list(self)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2995, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 248, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 657, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/local/lib/python3.6/site-packages/pymysql/connections.py", line 707, in _read_bytes
    CR.CR_SERVER_LOST, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: 'SELECT user.is_active AS user_is_active, user.id AS user_id, user.username AS user_username, user.password AS user_password, user.reset_password_token AS user_reset_password_token, user.email AS user_email, user.email_confirmed_at AS user_email_confirmed_at, user.first_name AS user_first_name, user.last_name AS user_last_name \nFROM user \nWHERE user.id = %(param_1)s'] [parameters: {'param_1': 13}] (Background on this error at: http://sqlalche.me/e/e3q8)

Answer 1

I solved this issue by migrating from a mariadb container to mysql. I still don't know what the root cause is.

Answer 2

Try the following to rule out that you have stale connections in your pool:

From https://docs.sqlalchemy.org/en/13/core/pooling.html#pool-disconnects

The approach adds a small bit of overhead to the connection checkout process, however is otherwise the most simple and reliable approach to completely eliminating database errors due to stale pooled connections. The calling application does not need to be concerned about organizing operations to be able to recover from stale connections checked out from the pool.

Pessimistic testing of connections upon checkout is achievable by using the Pool.pre_ping argument, available from create_engine() via the create_engine.pool_pre_ping argument:

engine = create_engine("mysql+pymysql://user:pw@host/db", pool_pre_ping=True)