CODEWITHSUNDEEP
pythonmysqlweb-applicationssqlalchemytornado
user3560105
user3560105

Reputation:

SqlAlchemy+Tornado: can't reconnect until invalid transaction is rolled back

i'm building a webapp with tornado+sqlalchemy and absolutely random i got this error

     File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 187, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 182, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 822, in _execute_context
    conn = self._revalidate_connection()
  File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 239, in _revalidate_connection
    "Can't reconnect until invalid "
sqlalchemy.exc.StatementError: Can't reconnect until invalid transaction is rolled back

I can't figure out how to solve this. I've put all db.commit into a

try:
  self.db.commit()
except Exception(e):
  self.db.rollback()

That's my class Application.

class Application
        [...]
        engine = create_engine(options.db_path, convert_unicode=True, echo=options.debug)
        models.init_db(engine)
        self.db = scoped_session(sessionmaker(bind=engine))
        tornado.web.Application.__init__(self, handlers, **settings)

but nothing. What is the best way to configure sqlalchemy and tornado for a web app like mysql+php?

Upvotes: 8

Views: 3983

Answers (2)

Shuguang Yang
Shuguang Yang

Reputation: 380

My way is do rollback on finish, add this to your BaseHandler:

def on_finish(self):
    if self.get_status() == 500:
        self.db_session.rollback()

Upvotes: 3

Pill
Pill

Reputation: 5395

I remember having same issuess a while ago. Seems there was some strange stuff related to connection pooling. Disabling pooling seemd to fixed it. Not the best idea in general, but it worked.

Try passing poolclass=NullPool to create_engine

...
from sqlalchemy.pool import NullPool
...
engine = create_engine(options.db_path, convert_unicode=True, echo=options.debug, poolclass=NullPool)

Upvotes: 2

Related Questions