flask-sqlalchemy lost connection to MySQL db

Question

Working with a MySQL database and flask-sqlalchemy I am encountering a lost connection error ('Lost connection to MySQL server during query'). I already have adapted the app.config['SQLALCHEMY_POOL_RECYCLE'] to be smaller than the engine timeout. I also added a pool_pre_ping, to ensure the database is not going away during two requests. Now I have no idea left, how this can still be an issue, since it is my understanding that flask-sqlalchemy should be taking care of opening and closing sessions correctly.

As a workaround, I thought about a way to tell flask-sqlalchemy to catch lost connection responses and restart the connection on the fly. But I have no idea how to do this. So, my questions are:

Do you know what could possibly cause my connection loss?
Do you think, my recent approach of catching is a good idea or do you have a better suggestion?
If this is a good idea, how can I do this most conveniently? I don't want to wrap all requests in try-catch-statements, since I have a lot of code.

are you using multithread in your application? which kind of mysql driver? — cyprx
– cyprx, Commented Sep 4, 2020 at 14:49

Srmsbrmnm · Accepted Answer · 2020-09-04 14:39:31Z

I do not know the answer to your 1st and 2nd questions, but for the 3rd question, I used a decorator to wrap all my functions instead of using try / except directly inside the functions. The explicit pre_ping and session rollback / close somehow also solved the problem of Lost Connection for me (mariadb was the backend I was using)!

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

and then wrapping my functions with the decorator:

@manage_session
my_funtion(*args, **kwargs):
    return "result"

Collectives™ on Stack Overflow

flask-sqlalchemy lost connection to MySQL db

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related