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:

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:password@127.0.0.1/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)

Do you see anything in MariaDB logs? Can you try to increase the server logging level? — Mikko Ohtamaa, May 07 '20 at 20:19
@MikkoOhtamaa - I edited the question with what shows up in the MariaDB log when this happens. I'll look into increasing the logging level. I don't know how to do that off the top of my head. — noslenkwah, May 07 '20 at 21:40
Changed logging level (`log_warnings=9`). Will update when I capture the error again. — noslenkwah, May 07 '20 at 21:47
Also - this was not MariaDB related, but happened with me long time ago. PostgreSQL Python driver (written in C) was crashing. Switching to a Pure Python driver helped. It was hard to diagnose because it left no trace in logs. — Mikko Ohtamaa, May 08 '20 at 09:34
Changing the logging level didn't give much additional information, but I did see that there are two slightly different errors being throw (edited above). 1. "Got an error reading communication packets" and 2. "Got timeout reading communication packets" — noslenkwah, May 11 '20 at 13:01
@MikkoOhtamaa - Pymysql is the pure python driver. I am currently testing out mysqldb which is the C library. Maybe with Maria DB the issue will be resolved going the other direction. — noslenkwah, May 11 '20 at 14:08
Depending on the version, Flask-SQLalchemy has a few other default settings that could be affecting this. Have you tried setting SQLALCHEMY_POOL_SIZE, SQLALCHEMY_POOL_TIMEOUT, and SQLALCHEMY_MAX_OVERFLOW? — Matt L., May 11 '20 at 16:05
Why are you using `network_mode` as `host` and use `SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://username:password@db/database?charset=utf8mb4'` — Tarun Lalwani, May 11 '20 at 16:09
@TarunLalwani - I did it that way because after playing around that's what seemed to work. I infer from your question that there is a better way? — noslenkwah, May 11 '20 at 18:23
@MattL. - I have only tried the SQLALCHEMY_POOL_TIMEOUT. It's not a high use and I have experienced this problem after hours when I am certain no one else is using it, so I don't expect the pool_size or max_overflow to come in to play. — noslenkwah, May 11 '20 at 18:32
@TarunLalwani - I think I initially misinterpreted your comment. I have changed the `network_mode` to the default and updated the `SQLALCHEMY_DATABASE_URI` as suggested. I'll update when I have move information. — noslenkwah, May 11 '20 at 22:46
Sounds it might be docker's network issue between containers. Please have a look. https://success.docker.com/article/troubleshooting-container-networking — John, May 18 '20 at 00:12

noslenkwah · Accepted Answer · 2020-11-25T16:12:18.333

3

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

edited Nov 25 '20 at 16:12

answered Jun 02 '20 at 13:00

noslenkwah

1,702
1
17
26

After trying every recommended fix and losing a lot of money to this ridiculous bug, I think this is the most sensible option. – nicbou Nov 28 '20 at 09:56

score 2 · Answer 2 · answered May 18 '20 at 12:51

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)

score 0 · Answer 3 · answered May 14 '20 at 18:31

0

I dont knwo wich versions u are using but did u try to set 'SQLALCHEMY_POOL_SIZE and 'SQLALCHEMY_POOL_RECYCLE'

answered May 14 '20 at 18:31

user80867

50
1
3

score 0 · Answer 4 · edited May 18 '20 at 13:24

I was faced a similar problem because I have multi threaded django app. When thread try to access database connection was lost. There is a django bug. https://code.djangoproject.com/ticket/21597

you can solve it with this workaround.

from django.db import connection

def is_connection_usable():
    try:
        connection.connection.ping()
    except:
        return False
    else:
        return True

def do_work():
    while(True): # Endless loop that keeps the worker going (simplified)
        if not is_connection_usable():
            connection.close()
        try:
            do_a_bit_of_work()
        except:
            logger.exception("Something bad happened, trying again")

————————————————
版权声明：本文为CSDN博主「orangleliu」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/lzz957748332/java/article/details/41480417

First check your database connection, if connection is lost it will close your connection, when you attempt connection again, you will connect.

Bkz https://blog.csdn.net/lzz957748332/article/details/41480417

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

4 Answers4

Linked