Restarting mysqld making tornado's next request to fail

[arLevi]

I have an issue which i'm not sure how to solve. using tornado-sqlalchemy was very easy my code looks like this pretty much:

#
# File: pages/homepage.py
#
from tornado_sqlalchemy  import SessionMixin

class HomePage(SessionMixin, web.RequestHandler):
 ...
 def get(self, *args, **kwargs):
      session = self.session() ... bla
      works_fine = session.query(MyTableORMModel).all()
      self.render(works_fine)

# 
# Starting tornado server
#
from tornado_sqlalchemy  import SQLAlchemy

settings = dict(
  ...
  # this is a regular string like: 
  # mysql+pymysql://{username}:{password}@{hostname}:3306/{database}"
  db=SQLAlchemy(get_db_url())
  ...
)

App = tornado.web.Application(
      application_routes,
     **settings
)

1st issue:

Everything works fine, i go to work he next day refresh the page, see error: MySql server gone away i refresh the page w/o doing anything - work ok for the rest of day, coming the next day - same thing.

i digged around it seems that after 8 hours, mysql close the connection on its behalf, tornado has no idea about it - so i guess this is why first query failed, something happens in the backend on tornado-sqlalchemy that refesh something inside it ( or torando itself makes a restart, don't know - but something solve it when the error is raised in tornado's logs.

2nd issue:

While trying to debug the issue on first issue - found something else that i'm trying to solve, which is basically the same thing I just manually restart mysqld while tornado is up and running - and then trying to refresh the page, i get a different error:

(pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query')

But same thing - after a refresh of the page - it works out just fine. And that is reproducable - every time !

What i've tried:

• NullPool - doesn't work ( tried like: create_engine(..., poolclass=NullPool)
• pre_pool_ping = True - doesn't work ( tried it like: create_engine(..., pre_pool_ping=True)
• pool_recycle = 180 - doesn't work ( tried it like: create_engine(..., pool_recycle=180)

Conclusion:

So both issues - have the same thing - mysql in the background is lost/gone - and tornado-sqlalchemy has no idea about it - so it continues with whatever it kept in cache/mem... until the next error is occured.

Any help solving the issue ?

[arLevi]

was able to solve 2nd issue

Actually, the ping I was trying to make was in my personal code/settings of SQLAlchemy I added to the from tornado_sqlalchemy import SQLAlchemy my engine_options:

settings = dict(
  ...
  db=SQLAlchemy(get_db_url(), engine_options={ "pre_pool_ping": True} )
  ...
)

Now restarting the service in the background - actually works ok.

[siddhantgoel]

Hey, yeah what you wrote in the second comment is also what I would've suggested.

Regarding the first issue, the MySQL server could be unreachable for quite a few reasons. But I'm not sure that that falls under the scope of this project. The ability to reconnect is something SQLAlchemy already provides through pre_pool_ping.