Flask-SQLAlchemy error MySQL server has gone away

Question

I'm newbie running a Flask app connected to a MySQL remote server with Flask-SQLAlchemy.

The app has very little traffic and it's usual to stay idle for more than 8 hours, I then get disconnected from the MySQL server.

this is my app code:

from flask import Flask, render_template, request, redirect, jsonify, make_response
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import or_    

app = Flask(__name__)
app.config['DEBUG'] = True
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {'pool_pre_ping': True, 'pool_recycle': 300, 'echo':'debug',}
app.config['SQLALCHEMY_DATABASE_URI']='mysql+pymysql://user:pass@myipcode/db'
db = SQLAlchemy(app)

Everything works ok until no querys are performed for 8 hours, then I lose db connection and logs show this error code:

"MySQL server has gone away (%r)" % (e,)) sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))

I did some research and was adviced to set 'SQLALCHEMY_ENGINE_OPTIONS' as written in the code sample but it behaves the same with or without such engine options, connection is not recycled every 300 seconds as It should, and pool_pre_ping doesn't seem to make any difference. 'echo':'debug' option works as intended since I get every transaction logged.

What should I do to prevent the connection from being disconnected even after a long period of inactivty?

EDIT:

To add some additional info:

The database is hosted in Cloud SQL from GCP .

I'm lost... Any help would be greatly appreciated.

The issue might be related to settings on the mysql server. check out this question: https://stackoverflow.com/questions/7942154/mysql-error-2006-mysql-server-has-gone-away — Mohammad hp, Oct 27 '20 at 08:07

score 0 · Accepted Answer · answered Oct 29 '20 at 10:05

Finally I figured it out myself,

It had to do with the fact that my app was running on a mountpoint like http://ServerIP/app instead of http://ServerIP/ because it was initially intended as a staging server.

I was using uWSGI and, in order to make it work in the aforementioned path, I had to specify a mount parameter in the [UWSGI] block from app.ini file.

When the uWSGI server started it showed like it was mounting two apps, one in '' and the other in '/app', and I guess that created a conflict that made the app unable to manage connections on MySQL server.

Mounting the app in http://ServerIP/ worked like a charm.

Flask-SQLAlchemy error MySQL server has gone away

1 Answers1