Forums

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

Hello, all

I've been having this issue where apparently my query on my Flask app tries to run while the DB server is disconnected(?).

I've already looked for the answer both here on the forums and on SO, but nothing has worked so far. Basically, I get either one of these two errors:

  • Can't reconnect until invalid transaction is rolled back or
  • 'Lost connection to MySQL server during query'

I get 'Internal Server Error' when I go to username.pythonanywhere.com. If I reload the app the sites works again, but the errors reappear after a couple of minutes

Based on the possible solutions I found, I tried adjusting SQLALCHEMY_POOL_SIZE to 280 (just as pythonanywhere itself suggests), but no luck so far.

Here is the structure of the project:

Project/
    Project/
        Index/
        __init__.py
        config.py
        models.py
    run.py

Here is run.py:

from Project import create_app

app = create_app()
app.config['SQLALCHEMY_POOL_RECYCLE'] = 280
app.app_context().push()

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

Here is init.py:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

from Project.config import Config

db = SQLAlchemy()

def create_app(config_class=Config):
    app = Flask(__name__)
    app.config.from_object(Config)

    app.app_context().push()
    db.init_app(app)

    # Blueprints imports
    from .index import index
    app.register_blueprint(index)

    return app

Here is config.py:

class Config:
    SECRET_KEY = "secret_key"
    SQLALCHEMY_DATABASE_URI = "mysql://username:password@user.mysql.pythonanywhere-services.com/databse$default?charset=utf8mb4"
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SQLALCHEMY_POOL_RECYCLE = 280

Here is models.py:

from Project import db

class Model(db.Model):
    __tablename__ = "table_name"
    x = db.Column("col1", db.String, primary_key=True)
    y = db.Column("col2", db.Text)

I don't have many queries, in fact, there are only two: one of them just queries and they sends it to the front, and the other one just edits the record on the table. I don't think there's an issue here tho, like I said, it works perfectly for a couple of minutes and then error.

Here's how it is implemented in routes.py:

from Project.models import Model

@index.route("/")
def home():

    x = Index_posts.query.filter_by(x="x").first()
    y = Index_posts.query.filter_by(legado="y").first()

    x_y= {"x":x, "y":y}

    return render_template("index.html", x_y=x_y)

@index.route("/edit", methods=['GET', 'POST'])
def edit():

    form = Form()
    if form.validate_on_submit():

    z= Model.query.filter_by(z=form.z.data).first()
    z.x= form.z.data
    db.session.commit()

    return redirect(url_for("index.edit"))

Any help would really be appreciated!

Thank you

Have you tried just accessing the db from a script? (instead of from your webapp) Or accessing your db from a MySQLconsole (from the PythonAnywhere db tab)

If you don't have anything in it you could try deleting the database and recreating the tables?