Forums

Hi, I'm trying to put online my first webapp so please be patient. I have a problem with my database. It disconnects after few minutes (less than 5).

Error log:

  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/engine/base.py", line 391, in _revalidate_connection
"Can't reconnect until invalid "
sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

This is how it is configured:

from flask.ext.sqlalchemy import SQLAlchemy

SQLALCHEMY_DATABASE_URI = "mysql+mysqlconnector://{username}:{password}@{hostname}/{databasename}".format(
username="myUsername",
password="myPassword",
hostname="myUsername.mysql.pythonanywhere-services.com",
databasename="myDatabasename",
)
app.config["SQLALCHEMY_DATABASE_URI"] = SQLALCHEMY_DATABASE_URI
app.config["SQLALCHEMY_POOL_RECYCLE"] = 299

I followed the advice http://help.pythonanywhere.com/pages/UsingSQLAlchemywithMySQL/:

from sqlalchemy import create_engine

engine = create_engine('mysql+mysqldb://myUsername:myPassword@myUsername.mysql.pythonanywhere-services.com/myDatabasename', pool_recycle=280)

But in this way it doesn't load the db at all and of course I have the same error log. Should I restart the database once I changed the configuration? Any idea how to fix it?

Thanks a lot!

UPDATE I have done some tries and I realize that the db disconnects after the first query (which is a sleep query, btw) ends. All my queries into the processlist are "sleep".

I don't understand what you mean by "doesn't load the db". What happens, exactly? Have you reloaded your web app to load the new code?

Is it possible that the invalid transaction is an artefact of your code and not of the database disconnect? Have you tried simplifying your code to see whether there's a particular operation that causes the error?

Hi Glenn, yes, of course I have reloaded my web app after I load the new code,I was wondering if I had to restart mysql, too. I have made some test: I have simplified the code and left just one simple query in all the code. When I use that query, in the client side, it forms a "sleep query process" (and that is normal, I think) BUT I noticed that when this query reach the 299 sec and kill itself the database is no more reachable to the client (500 error, same error log I mentioned above) and I have to reload my web app to make it work again. Totally no clue of why it happens..

It sounds like that app.config["SQLALCHEMY_POOL_RECYCLE"] = 299 isn't working somehow. Is there any way of importing sqlalchemy directly and asking it what its pool recycle setting is? is there some kind of flask.app.engine variable you can see? I don't know flask very well I'm afraid...