I have a flask rest api app with the following setup.
Packages installed
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
I have following production config with debug enabled to true to know stack trace.
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)
I have __init__.py that initializes SQLAlchemy as follows
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
I have an app.py with the following code that actually runs the flask app. app.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()
Problem: I'm facing the mysql connection issues with the following error on azure's db. I've tried setting max_allowed_packet server parameter to 1073741824. But still see this error.
Symptoms: For the fresh deployment, It responds and if the API is left idle for 5 to 10 mins and make the same request, it behaves with the following error. Again it starts working after 60 mins and the behavior continues.
Stack Trace:
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
