Forums

Flask/sqlalchemy/mysql - Lost connection to MySQL server during query

The problem started when I switched from sqlite to mysql. I use sqlalchemy and have tried configuring the pooling and time out using the code below, but no luck.

SQLALCHEMY_POOL_RECYCLE = 499
SQLALCHEMY_POOL_TIMEOUT = 20

I've took a look at the sqlalchemy docs for pooling http://docs.sqlalchemy.org/en/latest/core/pooling.html but I'm not sure which part of my code I'd implement it in. I see people using TRY and EXCEPT or some type of loop to reconnect.

This is what I'm working with. Any advice would be great =)

manage.py

import os
from app import create_app, db
from app.models import *
from flask.ext.script import Manager, Shell

app = create_app('default')
manager = Manager(app)

def make_shell_context():
    return dict(app=app, db=db, Comment=Comment, ActivityLog=ActivityLog, 
                Appointment=Appointment)

manager.add_command("shell", Shell(make_context=make_shell_context))

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

app/__init__.py

from flask import Flask, render_template
import os
from flask_bootstrap import Bootstrap
from flask.ext.login import LoginManager
from flask.ext.mail import Mail
from flask.ext.sqlalchemy import SQLAlchemy
from flask.ext.moment import Moment
from config import config

bootstrap = Bootstrap()
mail = Mail()
db = SQLAlchemy()

login_manager = LoginManager()
login_manager.session_protection = 'strong'
login_manager.login_view = 'main.login'

def create_app(config_name):
    app = Flask(__name__)
    app.config.from_object(config[config_name])
    config[config_name].init_app(app)
    bootstrap.init_app(app)
    mail.init_app(app)
    db.init_app(app)
    login_manager.init_app(app)

from .main import main as main_blueprint
app.register_blueprint(main_blueprint)

from .promo import promo as promo_blueprint
app.register_blueprint(promo_blueprint)

return app
deleted-user-548371 | 42 posts | June 20, 2015, 6:49 a.m. | permalink

Those variables are flask configuration variables and need to be set in the Flask config dictionary.

Staff glenn | 10043 posts | PythonAnywhere staff | June 20, 2015, 12:06 p.m. | permalink

You are correct. Updated manage.py to below and no more disconnects. Thanks!

import os
from app import create_app, db
from app.models import *
from flask.ext.script import Manager, Shell

app = create_app('default')

app.config['SQLALCHEMY_POOL_RECYCLE'] = 499
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 20

manager = Manager(app)

def make_shell_context():
    return dict(app=app, db=db, Comment=Comment, ActivityLog=ActivityLog, 
                Appointment=Appointment)

manager.add_command("shell", Shell(make_context=make_shell_context))

if __name__ == '__main__':
    manager.run()
deleted-user-548371 | 42 posts | July 5, 2015, 11:16 p.m. | permalink

Excellent, thanks for confirming!

Staff giles | 12671 posts | PythonAnywhere staff | July 6, 2015, 12:02 p.m. | permalink

Well, at least I thought it was solved... The frequent disconnects persist. I see here https://www.pythonanywhere.com/forums/topic/2465/ you suggested using

engine = create_engine(mysql_connect_string, pool_timeout=20, pool_recycle=299)
Base = declarative_base()
db = sessionmaker(bind=engine)

I tried implementing it, but I ran into problems with the models now I have to use 

class User(UserMixin, Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

instead of 

class User(UserMixin, db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)

Then I run into errors like so

AttributeError: 'Session' object has no attribute 'Column'

Any tips or suggestions?

deleted-user-548371 | 42 posts | July 17, 2015, 5:27 a.m. | permalink

Are you trying to follow something like this? Maybe you need to do something like this?

db = scoped_session(sessionmaker(bind=engine))
Staff conrad | 4232 posts | PythonAnywhere staff | July 17, 2015, 3:38 p.m. | permalink

UPDATE: the problem with the settings as posted by leapfrog was the values; app.config['SQLALCHEMY_POOL_RECYCLE'] should be 299, not 499.

Staff giles | 12671 posts | PythonAnywhere staff | July 20, 2015, 4:13 p.m. | permalink

@conrad - this looks like it may be the solution. I'm trying the easier fix of changing app.config['SQLALCHEMY_POOL_RECYCLE'] to 299 as @giles suggested.

Why would 499 not work? Isn't it just extending the length of the session? Does PA limit the session to 299 and anything exceeding it will timeout?

Is there a way to test the effectiveness of the new settings instead of waiting for the session to timeout?

deleted-user-548371 | 42 posts | July 20, 2015, 4:35 p.m. | permalink

Hi there,

I believe this is because MySQL automatically removes idle connections after a certain time (which is a good practice). The default is to close it after 8 hours, but for performance tuning, a lot of people set their mysql servers to time out in much less time (I think say 30 seconds?) PythonAnywhere sets the timeout at 5 mintues. So you want to make sure that you recycle in less than 300 seconds, otherwise it would have no effect.

Conrad

Staff conrad | 4232 posts | PythonAnywhere staff | July 21, 2015, 7:12 a.m. | permalink

Follow up three weeks later... I changed the app settings for app.config['SQLALCHEMY_POOL_RECYCLE'] to 299 and haven't had a disconnect since. Thanks guys.

deleted-user-548371 | 42 posts | Aug. 12, 2015, 3:54 p.m. | permalink

Excellent. That's good news.

Staff glenn | 10043 posts | PythonAnywhere staff | Aug. 12, 2015, 4:51 p.m. | permalink