Tengo una aplicación web Flask que solía ejecutarse en un servidor independiente usando lo siguiente:
En el servidor independiente, esta aplicación funcionó bien.
Desde entonces, he "dockerizado" esta aplicación en dos contenedores:
Desde la dockerización, ocasionalmente recibo este error (el seguimiento completo se publica al final):
Lost connection to MySQL server during queryEl registro de MariaDB muestra los siguientes errores con el registro detallado:
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) Esto es experimentado por el usuario como un 502 Bad Gateway . Si el usuario actualiza la página, esto a menudo resolverá el problema. Este problema surge al azar. No he podido reproducirlo a voluntad, pero con el tiempo aparecerá inevitablemente.
¿Qué está causando esto y cómo puedo solucionarlo?
Qué he hecho:
pool_recycle en 120scoped_session que debería evitar estos problemas de tiempo de espera.network_mode cambiado a predeterminado por un comentario. Esto no resolvió el problema.Mi opinión es que actúa como si la conexión entre el matraz y la base de datos no fuera confiable, pero como contenedores docker que se ejecutan en el mismo host, ¿no debería ser bastante confiable?
Código relevante:
base de datos.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"rastrear
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)No sé qué versiones estás usando, pero intentaste configurar 'SQLALCHEMY_POOL_SIZE y 'SQLALCHEMY_POOL_RECYCLE'
Resolví este problema migrando de un contenedor mariadb a mysql . Todavía no sé cuál es la causa raíz.
Me enfrenté a un problema similar porque tengo una aplicación django de múltiples subprocesos. Cuando el subproceso intenta acceder a la base de datos, se pierde la conexión. Hay un error de django. https://code.djangoproject.com/ticket/21597
puedes resolverlo con esta solución.
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/41480417Primero verifique la conexión de su base de datos, si se pierde la conexión, la cerrará, cuando intente conectarse nuevamente, se conectará.
Bkz https://blog.csdn.net/lzz957748332/article/details/41480417
Intente lo siguiente para descartar que tenga conexiones obsoletas en su grupo:
De https://docs.sqlalchemy.org/en/13/core/pooling.html#pool-disconnects
El enfoque agrega un poco de sobrecarga al proceso de verificación de la conexión; sin embargo, es el enfoque más simple y confiable para eliminar por completo los errores de la base de datos debido a conexiones agrupadas obsoletas. La aplicación que realiza la llamada no necesita preocuparse por organizar las operaciones para poder recuperarse de las conexiones obsoletas extraídas del grupo.
Se puede realizar una prueba pesimista de las conexiones al finalizar la compra utilizando el argumento Pool.pre_ping, disponible en create_engine() a través del argumento create_engine.pool_pre_ping:
engine = create_engine("mysql+pymysql://user:pw@host/db", pool_pre_ping=True)