Forums

The above error occurs every time I access my site after about 5 to 30 minutes. I am using Flask and SQLAlchemy. (Secondary question: should I be using ‘Flask-SQLAlchemy’ instead of SQLAlchemy and Flask together? If so, why?) I have my code in two files. The file ‘models.py’ creates the SQLAlchemy engine with the following: engine = create_engine('mysql+mysqldb://user:password@jhyman.mysql.pythonanywhere-services.com', pool_recycle=299, echo=True). I got the value 299 from a post on the forum. My Flask code with the request handlers is in a separate file ‘views.py’. Occasionally, I get a slightly different error message.

I’ve looked at many posts on StackExchange and PythonAnywhere, but have not been able to solve the problem. This is my first time using SQLAlchemy and Flask. Any help would be appreciated. If more information is needed, let me know.

Flask-sqlalchemy is a convenient way to use sqlalchemy from flask. From their web site: "It aims to simplify using SQLAlchemy with Flask by providing useful defaults and extra helpers that make it easier to accomplish common tasks."

As to the pool recycle: 299 may be a little too close to 300, for the connection handling. Other users have reported success with 280.

Hi Glenn, Thank you for your reply. Changing pool recycle to 280 didn't seem to change anything. Do you have any other suggestions? Thanks, Joe

Hhmm. That's weird. Do you have a single request that may take more than 5 min?

My requests simply read or write a few lines of text from or to the database. So unless I have an error that I am not aware of, the requests should be very short. Thanks.

Is there any way you might be caching connections between requests? For example:

connection = None

@app.route("/x")
def foo():
    global connection
    connection = engine.connect()
    # do something with connection

@app.route("/y")
def bar():
    # do something with connection

(I'm sure anything that might cache a connection would be more convoluted and indirect than this example, but hopefully you can see the kind of problem I'm trying to describe.)

Giles, I have taken the liberty of including most of my code below. A few request handlers are not included in views.py, but the code below will cause the problem. As I mentioned above, I am not using ‘Flask-SQLAlchemy’, but just SQLAlchemy with Flask. This code seems to run OK on my local machine. Thank you.

#models.py

from sqlalchemy import Column,  Integer, String,DateTime, Text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

import datetime

Base = declarative_base()

class Entry(Base):
    __tablename__ = 'entry'
    id = Column(Integer, primary_key = True)
    title =Column(String(256), nullable = False)
    content = Column(Text)
    date = Column(DateTime, default=datetime.datetime.now)

#pyanywhere
engine = create_engine('mysql+mysqldb://user:password@jhyman.mysql.pythonanywhere-services.com',
pool_recycle=280, echo=True)

engine.execute("USE jhyman$blogDB") # select new db


DBSession = sessionmaker(bind=engine)
db_session = DBSession()

#views.py

from flask import Flask, render_template, url_for, request, redirect, flash, session
from models import Entry, db_session

import datetime
from datetime import date

app = Flask(__name__)
app.debug = True

# Load default config and override config from an environment variable
app.config.update(dict(
    DEBUG=True,
    SECRET_KEY='...',
    USERNAME='...',
    PASSWORD='...'
))
app.config.from_envvar('FLASKR_SETTINGS', silent=True)


@app.route('/')
def show_entries():
    entries = db_session.query(Entry)
    entry = entries.first()
    return render_template('show_one_entry.html',entry=entry,entries=entries)

@app.route('/currentEntry/<int:entry_id>/')
def showOneEntry(entry_id):
    entries = db_session.query(Entry)
    entry = db_session.query(Entry).filter_by(id=entry_id).first()
    return render_template('show_one_entry.html',entry=entry,entries=entries)

Ah, I think I see the problem. When you create a DBSession, it asks the engine to give it a connection to the database. It then keeps trying to use that connection forever. So by doing this:

db_session = DBSession()

...at the global level, you've essentially told SQLAlchemy to only use one connection, which bypasses the whole "pool_recycle" thing. To put it another way, that recycle parameter is only consulted at the point when you create a DBSession object.

The fix is to get rid of the

db_session = DBSession()

...in models.py, and to change your import in views.py from

from models import Entry, db_session

...to

from models import Entry, DBSession

Then, at the start of each view that uses a database session, put a line saying

db_session = DBSession()

...so that you get a fresh one for each view.

I'm pretty much sure that will work.

The reason it seems to work OK on your local machine is probably because you've got a longer MySQL connection timeout. The default is quite long (I think 8 hours) so the local copy would probably start showing this problem after that long.

Giles, thanks for your help. When I make the changes you suggested, the website will appear on the first request, but fail on the second request with the error: “sqlalchemy.exc.OperationalError: (OperationalError) (1046, 'No database selected')” The same behavior occurred on my local machine.

I then removed the line

engine.execute("USE jhyman$blogDB") #select db

from models.py, and put it in each handler after each

db_session = DBSession()

as shown below.

@app.route('/')
def show_entries():
    db_session = DBSession()
    engine.execute("USE jhyman$blogDB") # select db
    entries = db_session.query(Entry)
    entry = entries.first()
    #db_session.close()
    return render_template('show_one_entry.html',entry=entry,entries=entries)

This permitted the program to process 7 requests, then I got the error:

“sqlalchemy.exc.OperationalError: (OperationalError) (1226, "User 'jhyman' has exceeded the 'max_user_connections' resource (current value: 6)")”

I tried putting "db_session.close()" at the end of the handler, but this didn’t seem to change anything.

Any ideas would be much appreciated. Thanks

Ah, sorry -- there's obviously more to it than I thought! (BTW this is why people use the Flask SQLAlchemy extension, it really does make things easier. See the PythonAnywhere/Flask tutorial I wrote a while back for sample code.)

So, according to this documentation page on SQLAlchemy engines, the correct way to specify the database that you want to use is to put it into the connection string -- so instead of putting the engine.execute("USE jhyman$blogDB") into all of your views, you put it in here:

engine = create_engine('mysql+mysqldb://user:password@jhyman.mysql.pythonanywhere-services.com/jhyman$blogDB', pool_recycle=280, echo=True)

Now, according to this documentation page on SQLAlchemy transactions and connection management, you need to commit or rollback each session. So I think that what you need (using your example view) is this:

@app.route('/')
def show_entries():
    db_session = DBSession()
    try:
        entries = db_session.query(Entry)
        entry = entries.first()
        result = render_template('show_one_entry.html',entry=entry,entries=entries)
        db_session.commit()
        return result
    except:
        db_session.rollback()

Note that I'm rendering the template (which uses entries) before the commit -- I don't know if that's necessary, but my gut instinct is that it's safer.

Giles, thank you! Your suggestions seem to have solved my problems. I sincerely appreciate your help, and your excellent explanations. My next step is to look at your Flask tutorial. I’ve only been using PythonAnywhere for a very short time, but I’m very impressed by the level of support! Thanks again.

Great! I'm glad we could sort that out :-)

I am facing similar issue with Flask and Sqlalchemy. I have declared a function in my Flask views file:

def getUserID(email):
        user = session.query(User_tt).filter(User_tt.usr_id == email).filter(User_tt.role == 'ADMIN').count()
        return user

When I call this function from another function, then after 5 mins I get this error "MySql server has gone away".

For example, when I try this, it works fine first time but errors out after 5 mins. On refresh, the error goes away.

@app.route('/upload')
def upload():
    try:
        if 'email' not in login_session or  getUserID(login_session['email']) == 0:
            session.close()
            return redirect(url_for('gdisconnect'))
        msg=''
        session.close()
        return render_template('xls_upload.html', msg=msg)
    except Exception:
        session.rollback()
        session.close()
        abort(500)

Hi there, just to check, have you set pool_recycle = 280? http://help.pythonanywhere.com/pages/UsingSQLAlchemywithMySQL

Yes, pool_recycle = 280:

engine=create_engine('mysql://julz9785:xpasswordx@julz9785.mysql.pythonanywhere-services.com/julz9785$atur-prod',pool_recycle=280)

This issue is being faced only when I call the function getUserID. Feel free to browse my code at my home directory/tingtongg/flask_app.py

This doesn't solve your immediate problem, but from my limited experience, I would highly recommend using Flask-SQLAlchemy. See Giles comment to my problem above: " (BTW this is why people use the Flask SQLAlchemy extension, it really does make things easier. See the PythonAnywhere/Flask tutorial I (Giles) wrote a while back for sample code.)"

Maybe you are not closing the db connection at the end of the request? So it stays open and then in 5 minutes gets arbitrarily closed?

The function getUserID() is being called from other functions such as upload() . I am not closing the session at the end of getUserID(). But as you can see in the code snippet above i am executing session.close() in upload() before returning a template. Feel free to browse my code at my home directory/tingtongg/flask_app.py .

It is possible that 2 separate sessions are being invoked by the 2 functions.

@julz9785 -- could you post the full code for your module? Or, if it's got stuff you'd rather not post on a public forum like this, may we take a look at your code from our side? (We can see your code directly, but we always ask for explicit permission first.)

[edit] Oops, didn't see your last post before I posted that -- I see you've given permission. Taking a look now.

Right, it looks like you've got the same problem as @jhyman. At the global level in your module, you're saying

session = DBSession()

This, if I understand correctly, tells the code to use the same connection for every request, and bypasses the logic that gets a fresh one if the old one has timed out.

The best solution is to use Flask-SQLAlchemy, like I said in my earlier answer to @jhyman. But if you really want to use the "raw" SQLAlchemy code, you need to create a new DBSession object in each one of your views -- that is, the first line of each view using a session should be:

session = DBSession()

...and then the lines like that that are outside the views should be deleted.