internetwhy
internetwhy

Reputation: 645

Flask-SQLAlchemy: Can't reconnect until invalid transaction is rolled back

So I am using Amazon Web Services RDS to run a MySQL server and using Python's Flask framework to run the application server and Flask-SQLAlchemy to interface with the RDS.

My app config.py

SQLALCHEMY_DATABASE_URI = '<RDS Host>'
SQLALCHEMY_POOL_RECYCLE = 60

My __ init __.py

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy

application = Flask(__name__)
application.config.from_object('config')
db = SQLAlchemy(application)

I have my main application.py

from flask import Flask
from application import db
import flask.ext.restless
from application.models import Person

application = Flask(__name__)
application.debug=True
db.init_app(application)

@application.route('/')
def index():
    return "Hello, World!"

manager = flask.ext.restless.APIManager(application, flask_sqlalchemy_db=db)
manager.create_api(Person, methods=['GET','POST', 'DELETE'])

if __name__ == '__main__':
    application.run(host='0.0.0.0')

The models.py

class Person(db.Model):
    __bind_key__= 'people'
    id = db.Column(db.Integer, primary_key=True)
    firstName = db.Column(db.String(80))
    lastName = db.Column(db.String(80))
    email = db.Column(db.String(80))

    def __init__(self, firstName=None, lastName=None, email=None):
        self.firstName = firstName
        self.lastName = lastName
        self.email = email

I then have a script to populate the database for testing purposes after db creation and app start:

from application import db
from application.models import Person

person = Person('Bob', 'Jones', '[email protected]')
db.session.add(person)
db.session.commit()

Once I've reset the database with db.drop_all() and db.create_all() I start the application.py and then the script to populate the database.

The server will respond with correct JSON but if I come back and check it hours later, I get the error that I need to rollback or sometimes the 2006 error that the MySQL server has gone away.

People suggested that I change timeout settings on the MySQL server but that hasn't fixed anything. Here are my settings:

innodb_lock_wait_timeout = 3000
max_allowed_packet       = 65536
net_write_timeout        = 300
wait_timeout             = 300

Then when I look at the RDS monitor, it shows the MySQL server kept the connection open for quite a while until the timeout. Now correct me if I'm wrong but isn't the connection supposed to be closed after it's finished? It seems that the application server keeps making sure that the database connection exists and then when the MySQL server times out, Flask/Flask-SQLAlchemy throws an error and brings down the app server with it.

Any suggestions are appreciated, thanks!

Upvotes: 29

Views: 59810

Answers (7)

mr.bug
mr.bug

Reputation: 402

if you're using "db" as imported object in your codes. you must always be sure that db is in the current flask context.

let's make an example:

flask app:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config.from_object('config')

with app.app_context():
    db = SQLAlchemy(app)
    from app.Model import Models
    db.create_all()
    from app import Rountes

how to use db with current app context in every where and in any thread?

1: using it with app.app_context() every time you want to access to db:

 from app import app

 def test_query():
     with app.app_context():
         db.session.commit()
         clients = Client.query.all()
         #etc...

2: using a decorator to make it easier and more readable:

from app import app

def flask_ctx(func):
    def decorated_view(*args, **kwargs):
        from app import app
        with app.app_context():
            return func(*args, **kwargs)
    return decorated_view

@flask_ctx
def test_query():
    db.session.commit()
    clients = Client.query.all()
    #etc...

Upvotes: 0

mili
mili

Reputation: 39

It seems not to be a problem with the transactions at the first place, but this is probably caused by an MySQL Error like Connection reset by peer beforehand. That means your connection is lost, probably because your application context was not setup correctly.

In general it is preferrable to use the factory pattern to create your app. This has a lot of advantages, your code is

  • easier to read and setup
  • easier to test
  • avoid circular imports

To prevent the invalid transaction error (that is probably caused by an OperationalError: Connection reset by peer) you should ensure that you are handling the database connection right.

The following example is based on this article which gives a nice explanation of the flask application context and how to use it with database connections or any other extensions.

application.py


from flask import Flask
from flask_sqlalchemy import SQLAlchemy

def create_app():
    """Construct the core application."""
    application = Flask(__name__)
    application.config.from_object('config')    # Set globals
    db = SQLAlchemy()

    with application.app_context():
        # Initialize globals/extensions in app context
        db.init_app(app)        

        # import routes here
        from . import routes

    return application


if __name__ == "__main__":
   app = create_app()
   app.run(host="0.0.0.0")

routes.py

from flask import current_app as application

@application.route('/', methods=['GET'])
def index():
   return "Hello, World!"

If you still run into disconnect-problems you should also check the SQLAlchemy documentation on dealing with disconnects and have a look at this question.

Upvotes: 3

Raghav salotra
Raghav salotra

Reputation: 870

This error usually appears when you create sqlalchemy the engine as a singleton. In that case after the connection is invalidated (in my case it was 3600sec) you get the InvalidTransaction error.

Best advice would be to initialise the db session at the time of application initialisation

db.init_app(app)

and import this db session when ever you have to do some CRUD operation.

Never faced this issue post this change on my application.

Upvotes: 0

Sheharyar Naseem
Sheharyar Naseem

Reputation: 71

Here you missing pool recycle as MySql closes session after some time so you need to add pool recycle so that connections in pool get reconnect after pool recycle time.

app.config['SQLALCHEMY_POOL_RECYCLE'] = 3600

Upvotes: 0

leejaycoke
leejaycoke

Reputation: 739

Everytime checking rollback or not is troublesome..

I made insert, update functions which need commit.

@app.teardown_request
def session_clear(exception=None):
    Session.remove()
    if exception and Session.is_active:
        Session.rollback()

Upvotes: 12

manychairs
manychairs

Reputation: 149

Alternatively, use this at the end of the script that populates your database:

db.session.close()

That should prevent those annoying "MySQL server has gone away" errors.

Upvotes: -1

internetwhy
internetwhy

Reputation: 645

I think what did it was adding

db.init_app(application)

in application.py, haven't had the error since.

Upvotes: 15

Related Questions