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.

I am sorry to revive this thread, but I am experiencing the same issue, desipite having a low pool_recycle and setting pre_ping. Do you know if this issue was resolved?

Hey there. No I couldn´t figure it out. Ended up setting a free MongoDB sv.

@testuserbiuer we would need more details to help you as often "same issue" could be actually different. Email us at support@pythonanywhere.com if you don't want to share details on public forums.

Hi, thanks for the support on email, just wanted to update this thread with my solution.

I have two databases, so I set up the app such, that SQLAlchemy binds to both databases - doing this, I need to manually apply the settings to each bind like so:

SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_pre_ping": True,
    "pool_recycle": 120,
    "pool_size": 1,
    "max_overflow": 5,
}

SQLALCHEMY_BINDS = (
    {
        "database_one": {
            "url": DATABASE_ONE_URI,
            **SQLALCHEMY_ENGINE_OPTIONS,
        },
        "database_two": {
            "url": DATABASE_TWO_URI,
            **SQLALCHEMY_ENGINE_OPTIONS,
        },
    },
)
SQLALCHEMY_ENGINE_OPTIONS = (SQLALCHEMY_ENGINE_OPTIONS,)

Prior to this, only one of the databases would time out, as applying the settings normally will only work for the main bind. Just wanted to share in case anyone else stumbles upon this :-)