Flask SQLAlchemy "Lost connection to server during query"

Question

I already stepped through these StackOverflow posts, and my issue persists.

I am getting Lost connection to server during query

Here is my Flask __init__.py file.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import func, exc
from flask_login import UserMixin

db = SQLAlchemy()

class users(db.Model, UserMixin):
    id    = db.Column(db.Integer,     nullable=False, unique=True, primary_key=True) 
    email = db.Column(db.String(100), nullable=False, unique=True)

def app():
    app = Flask(__name__)

    app.config['SQLALCHEMY_DATABASE_URI'] = "mariadb+mariadbconnector://serviceid:itsasecret@ec2-1-2-3-4.compute-1.amazonaws.com:3306/mytable"
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
    app.config['SQLALCHEMY_POOL_SIZE'] = 100
    app.config['SQLALCHEMY_POOL_RECYCLE'] = 30
    app.config['SQLALCHEMY_POOL_TIMEOUT'] = 30
    app.config['SQLALCHEMY_POOL_PRE_PING'] = True
    app.config["SQLALCHEMY_ECHO"] = True
    app.config["SQLALCHEMY_RECORD_QUERIES"] = True

    db.init_app(app)    
    with app.app_context():
        db.create_all()

        data = users.query.filter(users.email.ilike('john.doe@example.com')).first()
        db.session.close()
        db.session.flush()
        db.session.expire_all()

        time.sleep(60)

        data = users.query.filter(users.email.ilike('john.doe@example.com')).first()
        db.session.close()
        db.session.flush()
        db.session.expire_all()
 
    return app

If I set time.sleep(59) or less, the issue does not occur.

If I set time.sleep(60) or more, the issue occurs.

Thus it appears to be some 60 second MariaDB timeout setting. Here are my MariaDB 60 second timeouts.

[ec2-user@ip-172-31-80-56 ~]$ sudo docker exec mariadb mysql -e "SHOW SESSION VARIABLES LIKE '%timeout%'" | grep 60
net_write_timeout       60
slave_net_timeout       60
thread_pool_idle_timeout        60

[ec2-user@ip-172-31-80-56 ~]$ sudo docker exec mariadb mysql -e "SHOW GLOBAL VARIABLES LIKE '%timeout%'" | grep 60
net_write_timeout       60
slave_net_timeout       60
thread_pool_idle_timeout        60

Here is my console output.

2023-08-12 05:17:46,349 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2023-08-12 05:17:46,350 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-08-12 05:17:46,558 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2023-08-12 05:17:46,559 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-08-12 05:17:46,660 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2023-08-12 05:17:46,661 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-08-12 05:17:46,867 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-08-12 05:17:46,869 INFO sqlalchemy.engine.Engine SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = ? AND table_name = ?
2023-08-12 05:17:46,870 INFO sqlalchemy.engine.Engine [generated in 0.00112s] ('mytable','users')
2023-08-12 05:17:46,968 INFO sqlalchemy.engine.Engine COMMIT
2023-08-12 05:17:47,178 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-08-12 05:17:47,186 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.email AS users_email FROM users WHERE lower (users.email) LIKE lower (?) LIMIT ?
2023-08-12 05:17:47,188 INFO sqlalchemy.engine.Engine [generated in 0.00224s] ('john.doe@example.com', 1)
2023-08-12 05:17:47,278 INFO sqlalchemy.engine.Engine ROLLBACK
time.sleep(60) here
2023-08-12 05:18:48,484 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-08-12 05:18:48,486 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.email AS users_email FROM users WHERE lower (users.email) LIKE lower (?) LIMIT ?
2023-08-12 05:18:48,491 INFO sqlalchemy.engine.Engine [cached since 61.31s ago] ('john.doe@example.com', 1)
2023-08-12 05:18:48,499 INFO sqlalchemy.pool.impl.QueuePool Invalidate connection <mariadb.connection connected to 'ec2-107-22-51-98.compute-1.amazonaws.com' at 0000023778900830> (reason: InterfaceError:Lost connection to server during query)
Traceback (most recent call last):
mariadb.InterfaceError: Lost connection to server during query

I am not sure if this is relevant but I do see SQLAlchemy cache, which seems unexpected to me since I called db.session.close() and db.session.flush() and db.session.expire_all().

2023-08-12 05:18:48,491 INFO sqlalchemy.engine.Engine [cached since 61.31s ago] ('john.doe@example.com', 1)

`python --version` returns 3.11.1 and `pip list` return mariadb version 1.1.5.post3 — JeremyCanfield, Aug 12 '23 at 17:36
There were several changes and fixes for connection pooling in 1.1.6 and 1.1.7 - can you try with latest MariaDB driver (=1.1.7) ? — Georg Richter, Aug 13 '23 at 07:07
I did `pip install mariadb==1.1.7` and the issue persists. Interesting, I also did `pip install mysql` and I get the same issue with the mysql connector. — JeremyCanfield, Aug 14 '23 at 10:13
Probably not relevant, but SQLALCHEMY_POOL_TIMEOUT is deprecated. I would try without TIMEOUT and RECYCLE settings. — Georg Richter, Aug 14 '23 at 11:34
Yeah, issue persists with `SQLALCHEMY_POOL_RECYCLE` and `SQLALCHEMY_POOL_TIMEOUT` commented out. Fortunately, this is in my development environment which I have full control over so I went ahead and setup a Postgres database and I do not have the issue with Postgres so I'm probably going to just move forward with Postgres. I really wanted to use mySQL or MariaDB since these databases support multi master replication whereas Postgres only supports master-slave replication, but alas I've been stuck on this for 30 days so I just have to move on. Thanks for sharing your ideas! — JeremyCanfield, Aug 15 '23 at 08:24

Flask SQLAlchemy "Lost connection to server during query"

0 Answers0