Forums

Frequent (MySQLdb.OperationalError) (2013, 'Lost connection to MySQL server during query')

Hi,

I have a Flask app but keep getting the sqlalchemy.exc.OperationalError: (MySQLdb.OperationalError) (2013, 'Lost connection to MySQL server during query') error after 5 minutes of inactivity. The SQLALCHEMY_POOL_RECYCLE is set to 299 so this should not be the problem. I've tried setting it to 280, for example, but that did not help.

This is how I set up my Flask app:

import os
from os import environ as env
from datetime import timedelta
from flask import Flask, render_template
from dotenv import load_dotenv

project_folder = os.path.expanduser('~/mysite/micro_caps_llc_active_portfolio')
load_dotenv(os.path.join(project_folder, '.env'))

SQLALCHEMY_DB_URI = env.get('MYSQL')

def create_app():
    app = Flask(__name__)

    app.config['SECRET_KEY'] = env.get('APP_SECRET_KEY')
    app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(minutes=1440)
    app.config['SQLALCHEMY_DATABASE_URI'] = SQLALCHEMY_DB_URI
    app.config['SQLALCHEMY_POOL_RECYCLE'] = 299
    app.config['SQLALCHEMY_POOL_TIMEOUT'] = 20
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

    from .extensions import oauth
    oauth.init_app(app)
    oauth.register( ///SOME SECRETS AND STUFF FROM ENV/// )

    from .extensions import db
    db.init_app(app)

    from .main import main_bp
    app.register_blueprint(main_bp)

    from .authorization import authorization_bp
    app.register_blueprint(authorization_bp)

   ///BUNCH OF OTHER BLUEPRINTS REGISTERED HERE///

    return app

And this is the extenstions.py file:

import os
import finnhub
from os import environ as env
from dotenv import load_dotenv
from flask_sqlalchemy import SQLAlchemy
from authlib.integrations.flask_client import OAuth

project_folder = os.path.expanduser('~/mysite/micro_caps_llc_active_portfolio')
load_dotenv(os.path.join(project_folder, '.env'))

db = SQLAlchemy()
oauth = OAuth()
finnhub_client = finnhub.Client(api_key=env.get('FINNHUB_API_KEY'))

Any help is really appreciated.

Thank you!

How are you managing connections with the db? Is it possible that you keep them open?

To be honest, I thought flask_sqlalchemy manages that for you and recycles the pool.

I am calling db.session.query() in some fcns that are outside the app/blueprint views. Could that be an issue?

I hope this isn't premature but adding app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {'pool_pre_ping': True} to the create_app() fcn seems to have done the trick.

Sounds promising! I agree, though, I would have thought that the SQLAlchemy plugin would handle all of that for you. It may well be the calls to the DB outside the view, though -- are any of them run at import time, or in any other way that isn't inside a view (including functions that are called from functions that are called from functions that are called from views, and more indirect things like that)?