Forums

Problem: Despite configuring pool_pre_ping, pool_recycle, and other SQLAlchemy engine options to manage MySQL connections, I'm facing frequent disconnections due to inactivity somewhere near 5 minutes (I´ve read in this forum that 5 is the default setting for inactivity limit on PythonAnywhere and that MySQL configuration should override that and it should be fine but it does not seem to work, probably I am doing something wrong).

Details:

SQLAlchemy settings:

app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+mysqlconnector://user:password@host/database"
app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "pool_pre_ping": True,
    "pool_size": 20,
    "max_overflow": 10,
    "pool_timeout": 10,
    "pool_recycle": 280
}
engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'], **app.config["SQLALCHEMY_ENGINE_OPTIONS"])
db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine))
db.init_app(app)

Steps Taken:

Verified MySQL wait_timeout and interactive_timeout settings. Ensured no database access outside Flask view functions because I also followed this. Also saw this and tried without the pre_ping element but it still didn´t work.

I am not very experienced in web development much less in server or db configuration, but I think that the problem here lies in MySQL configuration not overriding PythonAnywhere default configuration and I am not being able to make it work on my own.

Greetings from Argentina. Thank you!

See https://help.pythonanywhere.com/pages/UsingSQLAlchemywithMySQL/

Thanks for your response but it does not seem to work. Maybe I did something wrong but I added the "hack" to every none view function but it still happens. Is there something else that I am missing or maybe another solution for this?

Maybe a stupid question, but did you reload the web app after making the changes? Also, what is the exact error you get?

Yes I did, always do. The error is the exact same that was pointed out in the post of the forum that the last answer told me to check: "(mysql.connector.errors.OperationalError) MySQL Connection not available." but the solution provided didn't work for me.

Do you create a connection at the web app startup? If so, do you close it afterwards with

db.session.close()
db.get_engine(app).dispose()

as per the help page?

Yes I do, and I close every non-view function with

db.session.close()
db.get_engine(app).dispose()

I made a function to "keep_alive" the connection with the database by pinging with a Select 1 but that did not work either, the database keeps disconnecting. Now I changed the function to actually add an element to the DB and then delete it and I got a new error which happens to appear at the exact moment that the DB disconnects:

Exception during reset or similar
    Traceback (most recent call last):
      File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 739, in _finalize_fairy
        fairy._reset(pool)
      File "/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 988, in _reset
        pool._dialect.do_rollback(self)
      File "/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 682, in do_rollback
        dbapi_connection.rollback()
      File "/usr/local/lib/python3.10/site-packages/mysql/connector/connection_cext.py", line 431, in rollback
        self._cmysql.rollback()
    _mysql_connector.MySQLInterfaceError: The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.

I´ve read online that it seems to be related to a python protocol, so maybe the responsible for this or at least part of this is some python package. Haven´t looked deep into it yet.

As you also contacted us via email, let's keep the conversation there.