Flask SQLAlchemy - 2013 Lost Connection

Question

So I'm attempting a build a site dynamically using a database and flask. The site works how I want it to as of right now, but only for about a minute before I get an error message saying:

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

The database contains 4 entries I want to grab for each page, and one main page where I only grab 2 entries from each entry in the database. Somewhere along the line it run into an error where it can no longer access the database and therefore does not allow any more pages that use the database to load.

Can anyone help me figure out why this might be happening? I'm very new to flask and sqlalchemy

Below is my code:

Database.py

from flask import Flask
from flask import render_template
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://databaseaddress'
db = SQLAlchemy(app)


class Kitchen(db.Model):
    __tablename__ = 'kitchens'
    id = db.Column('id', db.Integer, primary_key=True)
    description = db.Column('description')
    galleryId = db.Column('galleryId')
    cpo = db.Column('cpo')

    def __init__(self, description, galleryId, cpo):
        self.description = description
        self.galleryId = galleryId
        self.cpo = cpo

    def __repr__(self):
        return '<Gallery %r>' % self.description

    def get_description(self):
        return self.description

    def get_galleryId(self):
        return self.galleryId

    def get_cpo(self):
        return self.cpo

app.py

from flask import Flask
from flask import render_template
from Database import Kitchen

app = Flask(__name__)


@app.route('/')
@app.route('/index')
def main():
    return render_template('index.html')


@app.route('/kitchens', methods=['GET'], defaults={'n': 0})
@app.route('/kitchens/<n>', methods=['GET'])
def kitchens(n):
    row = Kitchen.query.count()
    if int(n) is 0:
        description = []
        for num in range(1, row+1):
            data = Kitchen.query.filter_by(id=num).first()
            description.append(Kitchen.get_description(data))
        return render_template('Gallery Directory.html', portfolio='Kitchens', row=row, description=description)
    elif int(n) >= 1:
        data = Kitchen.query.filter_by(id=n).first()
        description = Kitchen.get_description(data)
        galleryId = Kitchen.get_galleryId(data)
        cpo = Kitchen.get_cpo(data)
        return render_template('Gallery Basic.html', portfolio='Kitchens', description=description, id=int(n),
                               galleryId=galleryId, cpo=cpo, row=row)


if __name__ == "__main__":
    app.run()

Error Message

[2017-02-10 10:17:31,776] ERROR in app: Exception on /kitchens/ 1 [GET]
Traceback (most recent call last):
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in execute
result = self._query(query)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 835, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1019, in _read_query_result
result.read()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1302, in read
first_packet = self.connection._read_packet()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 961, in _read_packet
packet_header = self._read_bytes(4)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 998, in _read_bytes
2013, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1988, in wsgi_app
response = self.full_dispatch_request()
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1641, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1544, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/_compat.py", line 33, in reraise
raise value
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1639, in full_dispatch_request
rv = self.dispatch_request()
File "/home/devin-matte/.local/lib/python3.5/site-packages/flask/app.py", line 1625, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/home/devin-matte/Documents/Coralite/Coralite.py", line 18, in kitchens
row = Kitchen.query.count()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 3024, in count
return self.from_self(col).scalar()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2778, in scalar
ret = self.one()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2749, in one
ret = self.one_or_none()
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2719, in one_or_none
ret = list(self)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2790, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2813, in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 945, in execute
return meth(self, multiparams, params)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/elements.py", line 263, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1393, in _handle_dbapi_exception
exc_info
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 186, in reraise
raise value.with_traceback(tb)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 1182, in _execute_context
context)
File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/default.py", line 470, in do_execute
cursor.execute(statement, parameters)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 166, in execute
result = self._query(query)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 835, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1019, in _read_query_result
result.read()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 1302, in read
first_packet = self.connection._read_packet()
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 961, in _read_packet
packet_header = self._read_bytes(4)
File "/home/devin-matte/.local/lib/python3.5/site-packages/pymysql/connections.py", line 998, in _read_bytes
2013, "Lost connection to MySQL server during query")
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: 'SELECT count(*) AS count_1 \nFROM (SELECT kitchens.id AS kitchens_id, kitchens.description AS kitchens_description, kitchens.`galleryId` AS `kitchens_galleryId`, kitchens.cpo AS kitchens_cpo \nFROM kitchens) AS anon_1']
127.0.0.1 - - [10/Feb/2017 10:17:31] "GET /kitchens/%201 HTTP/1.1" 500 -

Answer 1

Based on suggestions from this, this and many other articles on the internet, wrapping all my fuctions with the following decorator helped me resolve the "Lost Connection" issue with mariadb as the backend db. Please note that db below is an instance of flask_sqlalchemy.SQLAlchemy

def manage_session(f):
    def inner(*args, **kwargs):

        # MANUAL PRE PING
        try:
            db.session.execute("SELECT 1;")
            db.session.commit()
        except:
            db.session.rollback()
        finally:
            db.session.close()

        # SESSION COMMIT, ROLLBACK, CLOSE
        try:
            res = f(*args, **kwargs)
            db.session.commit()
            return res
        except Exception as e:
            db.session.rollback()
            raise e
            # OR return traceback.format_exc()
        finally:
            db.session.close()
    return inner

I also added pool_recycle of 50 seconds in Flask SQLAlchemy config, but that didnt visibly contribute to the solution.

Answer 2

I had the similar problem. Here is my solution:

Get wait_timeout from DB (MariaDB/MySQL)

SHOW GLOBAL VARIABLES LIKE "wait_timeout";

Set SQLALCHEMY_POOL_RECYCLE in Flask code:

app = Flask(__name__)
app.config['SQLALCHEMY_POOL_RECYCLE'] = <db_wait_timeout> - 1

Answer 3

This issue was not with sqlalchemy or flask. It was actually on the mysql serverside. The server that was being used was timing out and disconnecting from any connections that were not localhost.

Switching to a local sqlite database solved the issue for me until I was able to get a different server to host the mysql database on that allowed remote connections/hosted the site on the database's localhost.

Answer 4

First why are you instantiating the Flask object twice, in app.py and Database.py? Your project should only instantiate one Flask object. If you want to make your Flask project more modular, check the Blueprint object here http://flask.pocoo.org/docs/0.12/blueprints/#blueprints.

Now, try the following and be aware that I haven't tested this, if it doesn't work let me know:

Database.py

from flask import Flask
from flask import render_template
from app import db

class Kitchen(db.Model):
    __tablename__ = 'kitchens'
    id = db.Column('id', db.Integer, primary_key=True)
    description = db.Column('description')
    galleryId = db.Column('galleryId')
    cpo = db.Column('cpo')

    def __init__(self, description, galleryId, cpo):
        self.description = description
        self.galleryId = galleryId
        self.cpo = cpo

    def __repr__(self):
        return '<Gallery %r>' % self.description

    def get_description(self):
        return self.description

    def get_galleryId(self):
        return self.galleryId

    def get_cpo(self):
        return self.cpo

app.py

from flask import Flask
from flask import render_template
from Database import Kitchen
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://databaseaddress'
db = SQLAlchemy(app)

@app.route('/')
@app.route('/index')
def main():
    return render_template('index.html')


@app.route('/kitchens', methods=['GET'], defaults={'n': 0})
@app.route('/kitchens/<n>', methods=['GET'])
def kitchens(n):
    row = Kitchen.query.count()
    if int(n) is 0:
        description = []
        for num in range(1, row+1):
            data = Kitchen.query.filter_by(id=num).first()
            description.append(Kitchen.get_description(data))
        return render_template('Gallery Directory.html', portfolio='Kitchens', row=row, description=description)
    elif int(n) >= 1:
        data = Kitchen.query.filter_by(id=n).first()
        description = Kitchen.get_description(data)
        galleryId = Kitchen.get_galleryId(data)
        cpo = Kitchen.get_cpo(data)
        return render_template('Gallery Basic.html', portfolio='Kitchens', description=description, id=int(n),
                               galleryId=galleryId, cpo=cpo, row=row)


if __name__ == "__main__":
    db.create_all()
    app.run()