python - MySQL 服务器已消失 ConnectionResetError 10054 Flask SqlAlchemy

Question

我有一个 Flask Rest api 应用程序，具有以下设置。

已安装的软件包

alembic==1.3.0
aniso8601==8.0.0
astroid==2.3.3
attrs==19.3.0
bcrypt==3.1.7
cffi==1.13.2
Click==7.0
colorama==0.4.1
Flask==1.1.1
Flask-Bcrypt==0.7.1
Flask-Migrate==2.5.2
flask-restplus==0.13.0
Flask-Script==2.0.6
Flask-SQLAlchemy==2.4.1
Flask-Testing==0.7.1
importlib-metadata==0.23
isort==4.3.21
itsdangerous==1.1.0
Jinja2==2.10.3
jsonschema==3.1.1
lazy-object-proxy==1.4.3
Mako==1.1.0
MarkupSafe==1.1.1
mccabe==0.6.1
more-itertools==7.2.0
mysqlclient==1.4.5
pycparser==2.19
PyJWT==1.7.1
pylint==2.4.3
PyMySQL==0.9.3
pyrsistent==0.15.5
python-dateutil==2.8.1
python-editor==1.0.4
pytz==2019.3
six==1.13.0
SQLAlchemy==1.3.11
Werkzeug==0.16.0
wrapt==1.11.2
zipp==0.6.0

我有以下生产配置，并将调试启用为 true 以了解堆栈跟踪。

class ProductionConfig(Config):
    DEBUG = True
    user = 'xxxxxx@xxxxxx-mysqldbserver'
    passs = 'xxxxxx'
    host = 'xxxxxx-mysqldbserver.mysql.database.azure.com'
    db = 'ifsc'
    SQLALCHEMY_DATABASE_URI = 'mysql+pymysql://{0}:{1}@{2}/{3}'.format(user, passs, host, db)

我有 __init__.py 初始化 SQLAlchemy 如下

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_bcrypt import Bcrypt

from .config import config_by_name

db = SQLAlchemy()
flask_bcrypt = Bcrypt()

def create_app(config_name):
    app = Flask(__name__)
    app.config.from_object(config_by_name[config_name])
    db.init_app(app)
    flask_bcrypt.init_app(app)
    return app

我有一个 app.py，其中包含实际运行 Flask 应用程序的以下代码。应用程序.py

from flask_cors import CORS, cross_origin
from api import blueprint

from api.main import create_app, db
from api.main.seed import seed_db

app = create_app(os.getenv('BOILERPLATE_ENV') or 'prod') # dev|prod
CORS(app)
app.register_blueprint(blueprint)
app.app_context().push()

if __name__ == '__main__':
    app.run()

问题:我遇到了 mysql 连接问题，在 azure 的数据库上出现以下错误。我尝试将 max_allowed_pa​​cket 服务器参数设置为 1073741824。但仍然看到这个错误。

症状:对于全新部署，它会做出响应，如果 API 空闲 5 到 10 分钟并发出相同的请求，则会出现以下错误。 60 分钟后它再次开始工作，并且该行为仍在继续。

堆栈跟踪:

sqlalchemy.exc.OperationalError sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None))") [SQL: SELECT bank_details.id AS bank_details_id, bank_details.bank AS bank_details_bank, bank_details.ifsc AS bank_details_ifsc, bank_details.branch AS bank_details_branch, bank_details.address AS bank_details_address, bank_details.district AS bank_details_district, bank_details.city AS bank_details_city, bank_details.state AS bank_details_state, bank_details.phone AS bank_details_phone, bank_details.micr AS bank_details_micr, bank_details.pin AS bank_details_pin FROM bank_details WHERE bank_details.bank = %(bank_1)s AND bank_details.city = %(city_1)s] [parameters: {'bank_1': 'axis bank', 'city_1': 'goa'}] (Background on this error at: http://sqlalche.me/e/e3q8)

Traceback (most recent call last) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\connections.py", line 713, in _write_bytes self._sock.sendall(data) During handling of the above exception, another exception occurred: File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\base.py", line 1245, in _execute_context self.dialect.do_execute( File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\default.py", line 581, in do_execute cursor.execute(statement, parameters) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\cursors.py", line 170, in execute result = self._query(query) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\cursors.py", line 328, in _query conn.query(q) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\connections.py", line 516, in query self._execute_command(COMMAND.COM_QUERY, sql) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\connections.py", line 771, in _execute_command self._write_bytes(packet) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\connections.py", line 716, in _write_bytes raise err.OperationalError( The above exception was the direct cause of the following exception: File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 2463, in call return self.wsgi_app(environ, start_response) File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 2449, in wsgi_app response = self.handle_exception(e) File "E:\sampled\sample_api\env\Lib\site-packages\flask_restplus\api.py", line 584, in error_router return original_handler(e) File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 1866, in handle_exception reraise(exc_type, exc_value, tb) File "E:\sampled\sample_api\env\Lib\site-packages\flask_compat.py", line 38, in reraise raise value.with_traceback(tb) File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 2446, in wsgi_app response = self.full_dispatch_request() File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 1951, in full_dispatch_request rv = self.handle_user_exception(e) File "E:\sampled\sample_api\env\Lib\site-packages\flask_restplus\api.py", line 584, in error_router return original_handler(e) File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 1820, in handle_user_exception reraise(exc_type, exc_value, tb) File "E:\sampled\sample_api\env\Lib\site-packages\flask_compat.py", line 38, in reraise raise value.with_traceback(tb) File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 1949, in full_dispatch_request rv = self.dispatch_request() File "E:\sampled\sample_api\env\Lib\site-packages\flask\app.py", line 1935, in dispatch_request return self.view_functionsrule.endpoint File "E:\sampled\sample_api\env\Lib\site-packages\flask_restplus\api.py", line 325, in wrapper resp = resource(*args, **kwargs) File "E:\sampled\sample_api\env\Lib\site-packages\flask\views.py", line 89, in view return self.dispatch_request(*args, **kwargs) File "E:\sampled\sample_api\env\Lib\site-packages\flask_restplus\resource.py", line 44, in dispatch_request resp = meth(*args, **kwargs) File "E:\sampled\sample_api\env\Lib\site-packages\flask_restplus\marshalling.py", line 243, in wrapper resp = f(*args, **kwargs) File "E:\sampled\sample_api\api\main\controller\ifsc_controller.py", line 37, in get banks = get_banks_by_name_city(bank, city) File "E:\sampled\sample_api\api\main\service\ifsc_service.py", line 28, in get_banks_by_name_city return Ifsc.query.filter_by(bank = name, city = city).all() File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\orm\query.py", line 3211, in all return list(self) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\orm\query.py", line 3367, in iter return self._execute_and_instances(context) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\orm\query.py", line 3392, in _execute_and_instances result = conn.execute(querycontext.statement, self._params) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\base.py", line 982, in execute return meth(self, multiparams, params) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\sql\elements.py", line 287, in _execute_on_connection return connection._execute_clauseelement(self, multiparams, params) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\base.py", line 1095, in _execute_clauseelement ret = self._execute_context( File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\base.py", line 1249, in _execute_context self._handle_dbapi_exception( File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\base.py", line 1476, in _handle_dbapi_exception util.raise_from_cause(sqlalchemy_exception, exc_info) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\util\compat.py", line 398, in raise_from_cause reraise(type(exception), exception, tb=exc_tb, cause=cause) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\util\compat.py", line 152, in reraise raise value.with_traceback(tb) File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\base.py", line 1245, in _execute_context self.dialect.do_execute( File "E:\sampled\sample_api\env\Lib\site-packages\sqlalchemy\engine\default.py", line 581, in do_execute cursor.execute(statement, parameters) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\cursors.py", line 170, in execute result = self._query(query) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\cursors.py", line 328, in _query conn.query(q) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\connections.py", line 516, in query self._execute_command(COMMAND.COM_QUERY, sql) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\connections.py", line 771, in _execute_command self._write_bytes(packet) File "E:\sampled\sample_api\env\Lib\site-packages\pymysql\connections.py", line 716, in _write_bytes raise err.OperationalError( sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (ConnectionResetError(10054, 'An existing connection was forcibly closed by the remote host', None, 10054, None))") [SQL: SELECT bank_details.id AS bank_details_id, bank_details.bank AS bank_details_bank, bank_details.ifsc AS bank_details_ifsc, bank_details.branch AS bank_details_branch, bank_details.address AS bank_details_address, bank_details.district AS bank_details_district, bank_details.city AS bank_details_city, bank_details.state AS bank_details_state, bank_details.phone AS bank_details_phone, bank_details.micr AS bank_details_micr, bank_details.pin AS bank_details_pin FROM bank_details WHERE bank_details.bank = %(bank_1)s AND bank_details.city = %(city_1)s] [parameters: {'bank_1': 'axis bank', 'city_1': 'goa'}] (Background on this error at: http://sqlalche.me/e/e3q8) The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error. To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.

You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:

dump() shows all variables in the frame dump(obj) dumps all that's known about the object

Answer 1

经过 4 个月的研究和了解 Flask 的工作原理后，我通过推送 app_context 来初始化数据库进行了以下更改，解决了问题。

with app.app_context():
    db.init_app(app)

注意事项:我们必须确保应用程序上下文与 sqlalchemy 数据库初始化一起推送。在发布的问题中，我一直在初始化数据库，然后稍后推送应用程序上下文app.app_context().push()。

引用文献:Manually Push a Context