"Can't reconnect until invalid transaction is rolled back" after application sits idle overnight.
Ryan Parrish
I am having a problem with SQLAlchemy where after the application that
uses SQLAlchemy has been sitting overnight and a user makes the first
queries of the day thru the app that uses SA, SQLAlchemy throws an
error saying 'MySQL server has gone away', which I understand the
reason to be that my mysql server has cut the idle connections, but I
have pool_recycle = 3600 set and I thought that was the solution to
keeping connections alive?
After that error message I get two more exceptions when the user tries
the request again of "Can't reconnect until invalid transaction is
rolled back", which I don't understand at all because the application
only queries the DB and never deals with transactions. After those
three attempts, the forth request will go through just fine. :-\ Any
pointers on what I should look for or do?
Here is the full traceback of the exceptions...
Module vendormiddleware.model:47 in by_company_code
>> count = codes.count()
Module sqlalchemy.orm.query:1087 in count
>> return q._count()
Module sqlalchemy.orm.query:1108 in _count
>> return self.session.scalar(s, params=self._params, mapper=self.mapper)
Module sqlalchemy.orm.session:612 in scalar
>> return self.__connection(engine, close_with_result=True).scalar(clause, params or {}, **kwargs)
Module sqlalchemy.engine.base:836 in scalar
>> return self.execute(object, *multiparams, **params).scalar()
Module sqlalchemy.engine.base:846 in execute
>> return Connection.executors[c](self, object, multiparams, params)
Module sqlalchemy.engine.base:897 in execute_clauseelement
>> return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) > 1), distilled_params=params)
Module sqlalchemy.engine.base:909 in _execute_compiled
>> self.__execute_raw(context)
Module sqlalchemy.engine.base:918 in __execute_raw
>> self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context)
Module sqlalchemy.engine.base:962 in _cursor_execute
>> self._handle_dbapi_exception(e, statement, parameters, cursor)
Module sqlalchemy.engine.base:944 in _handle_dbapi_exception
>> raise exceptions.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect)
OperationalError: (OperationalError) (2006, 'MySQL server has gone
away') u'SELECT count(company.company_id) AS count_1 \nFROM company
\nWHERE company.company_code = %s' [u'CUSTOMER1']
Module vendormiddleware.model:47 in by_company_code
>> count = codes.count()
Module sqlalchemy.orm.query:1087 in count
>> return q._count()
Module sqlalchemy.orm.query:1108 in _count
>> return self.session.scalar(s, params=self._params, mapper=self.mapper)
Module sqlalchemy.orm.session:612 in scalar
>> return self.__connection(engine, close_with_result=True).scalar(clause, params or {}, **kwargs)
Module sqlalchemy.engine.base:836 in scalar
>> return self.execute(object, *multiparams, **params).scalar()
Module sqlalchemy.engine.base:846 in execute
>> return Connection.executors[c](self, object, multiparams, params)
Module sqlalchemy.engine.base:897 in execute_clauseelement
>> return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) > 1), distilled_params=params)
Module sqlalchemy.engine.base:906 in _execute_compiled
>> context = self.__create_execution_context(compiled=compiled, parameters=distilled_params)
Module sqlalchemy.engine.base:950 in __create_execution_context
>> return self.engine.dialect.create_execution_context(connection=self, **kwargs)
Module sqlalchemy.databases.mysql:1464 in create_execution_context
>> return MySQLExecutionContext(self, connection, **kwargs)
Module sqlalchemy.engine.default:178 in __init__
>> self.cursor = self.create_cursor()
Module sqlalchemy.engine.default:275 in create_cursor
>> return self._connection.connection.cursor()
Module sqlalchemy.engine.base:583 in connection
>> raise exceptions.InvalidRequestError("Can't reconnect until invalid transaction is rolled back")
InvalidRequestError: Can't reconnect until invalid transaction is
rolled back
Michael Bayer
On Jul 11, 2008, at 9:23 AM, Ryan Parrish wrote:
>
> Using SQLAlchemy 0.4.4
>
> I am having a problem with SQLAlchemy where after the application that
> uses SQLAlchemy has been sitting overnight and a user makes the first
> queries of the day thru the app that uses SA, SQLAlchemy throws an
> error saying 'MySQL server has gone away', which I understand the
> reason to be that my mysql server has cut the idle connections, but I
> have pool_recycle = 3600 set and I thought that was the solution to
> keeping connections alive?
>
> After that error message I get two more exceptions when the user tries
> the request again of "Can't reconnect until invalid transaction is
> rolled back", which I don't understand at all because the application
> only queries the DB and never deals with transactions. After those
> three attempts, the forth request will go through just fine. :-\ Any
> pointers on what I should look for or do?
this indicates your application has checked out a Connection from the
pool and is keeping it open, in a transaction. the connection then
times out, and upon reaccess the next day would like to reconnect
itself on next access; for a current Connection, this can reconnect
its internal DBAPI connection transparently and you get to use the
same Connection object. But Connection won't allow you to do this if
its in a transaction, i.e. begin(); when the disconnect exception is
thrown, it expects that to be within a try/except block which will do
a rollback on error.
The reason you're getting the disconnect exception in the first place
is because the pool_recycle feature only works upon checkout from the
pool. So the solution is the same, ensure all connections are
returned to the pool after operations are complete.
Ryan Parrish
> The reason you're getting the disconnect exception in the first place
> is because the pool_recycle feature only works upon checkout from the
> pool. So the solution is the same, ensure all connections are
> returned to the pool after operations are complete.
bind=engine) rather than transactional=True which it is now; I would
never have these implicit begin()'s started which are pointless since
I'm only doing selects on the DB? Thus my problem would be solved?
Michael Bayer
using transactional=False is one solution, but a better one is to
simply rollback(), commit(), or close() the Session when operations
are complete - transactional mode (which is called "autocommit=False"
in 0.5) has the advantage that a series of select operations will all
share the same isolated transactional context..this can be more or
less important depending on the isolation mode in effect and the kind
of application.
DBAPI has no implicit "autocommit" mode so there is always a
transaction implicitly in progress when queries are made.