Forums

Hi all

After leaving my webapp (SQLAlchemy+MySQL) alone for a while, and hitting refresh, I get the PA error screen. If I hit F5, it works.

In the logs I'm getting the following:

sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

I'm on the free plan at the moment. Is everything shutting down after a few minutes? Do I need to get on a paid plan to stop this happening?

Thanks!

Rob

The MySQL connection is dropped after a period of inactivity -- I think we have it set to three minutes -- so that people don't accidentally leave connections open and leave the database server out of resources. But SQLAlchemy should transparently reconnect for you. How are you configuring SQLAlchemy? If you're using the Flask extension for it, you can probably fix the problem with this:

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

If you're creating the connection directly, try this instead:

engine = create_engine(mysql_connect_string, pool_timeout=20, pool_recycle=299)

Thanks! Actually a pyramid project; I added the following to the relevant ini file:

sqlalchemy.pool_recycle = 299
sqlalchemy.pool_timeout = 20

Will see how it goes.

Now on Flask I still get it :) I've added those config settings.

The SQLAlchemy docs seem to recommend a pool_recycle of 3600. What do you think?

I don't think 3600 will work due to our mysql database settings. Feel free to experiment though...

Hi, not sure anyone will read this so many years after the last message, but I have the same problem, and none of the solutions work for me unfortunately I tried

app.config['SQLALCHEMY_POOL_RECYCLE'] = 280
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 10
app.config['SQLALCHEMY_POOL_PRE_PING'] = True

and also

app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {"pool_recycle": 280, "pool_timeout": 10, "pool_pre_ping": True}.

First I wasn't setting the pre_ping option. Also tried different settings for recycling timing, pool_size etc. NOthing seems to help. After 5 mins can't access my website/database anymore.... Any ideas? Thanks in advance!

Slava

[edit by admin: formatting]

Just the pool recycle option should be fine. Are you setting those options before you create the SQLAlchemy engine? Perhaps you could share your code (with any passwords omitted, of course).

Many thanks for your response!! Here's a snapshot of flask_app.py (I kept only POOL_RECYCLE, as you suggest, and also TRACK_MODIFICATIONS, based on a pythonanywhere+flask tutorial):

app = Flask(__name__)
app.app_context().push() # this is to prevent context errors

app.config['SQLALCHEMY_DATABASE_URI'] ='mysql://libov:NNNN@libov.mysql.pythonanywhere-services.com/libov$expenses'
app.config["SQLALCHEMY_POOL_RECYCLE"] = 280
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

from datamodel import *
db.init_app(app)

app.config['SECRET_KEY'] = 'hard to guess string'

bootstrap = Bootstrap(app)

The rest is just the routes. The imported datamodel.py is like

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

class Balance(db.Model):
    __tablename__ = 'BALANCE'
    id = db.Column(db.Integer, primary_key = True)
    amount = db.Column(db.Numeric(10,2), nullable=False)
    date = db.Column(db.Date, nullable=False, unique=True)

(plus further table definitions)

So in summary, I first create an app, then create an SQLAlchemy object, and then "link" the two using init_app. Thanks in advance for any help!!

Cheers Slava

You're not calling the SQLAlchemy constructor with your app as an argument, so it's not getting the configuration that you've applied to your app.

This did the trick! Initially had the same problem, but removing app.app_context().push() seems to have resolved it (is this sth to be expected?)

What is really confusing me is that I thought I checked that SQLAlchemy gets the app configuration, namely I used db.engine.pool.status() to show the engine status and e.g. the printed pool size corresponded exactly to the setting I would apply to the app. So I was kinda sure that creating SQLAlchemy without the app argument is still okay.

Anyway, it works now so thanks a lot!!

Glad to hear that you made it work!

Hey guys, I'm little bit late to the party and yes I had same issue with my database and thank god I managed to find this post, because it was same line of code ("app.app_context().push()") that was causing this error, so I just wanted to ask PythonAnywhere staff if possible to add this solution to your help page ("https://help.pythonanywhere.com/pages/UsingSQLAlchemywithMySQL/") so others may find it faster, that all. :)

Sound like a great idea. PRs are welcome at https://github.com/pythonanywhere/help_pages