[sqlalchemy] correct way to handle 'MySQL server has gone away'?

2010-04-26 Thread Chris Withers 

Chris Withers wrote:
raise exc.InvalidRequestError(Can't reconnect until invalid 
transaction is rolled back)
sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid 
transaction is rolled back


Asking here as it sounds like it might be related.
It occurred transiently, though, and appears to have resolved itself 
without any intervention. Where would I look to find out what caused the 
invalid transaction and how it was rolled back?


We've had this occur again, wondered if anyone had any suggestions...

So, the first error is this:

return session.query(Feed).filter(Feed.enabled==True).all()
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, 
line 1267, in all

return list(self)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, 
line 1361, in __iter__

return self._execute_and_instances(context)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, 
line 1364, in _execute_and_instances
result = self.session.execute(querycontext.statement, 
params=self._params, mapper=self._mapper_zero_or_none())
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py, 
line 753, in execute

clause, params or {})
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 824, in execute

return Connection.executors[c](self, object, multiparams, params)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 874, in _execute_clauseelement

return self.__execute_context(context)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement, 
context.parameters[0], context=context)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 950, in _cursor_execute

self._handle_dbapi_exception(e, statement, parameters, cursor, context)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 931, in _handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e, 
connection_invalidated=is_disconnect)
sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server 
has gone away') u'SELECT ...


So, my guess is that we're not handling this error correctly.
How should we be handling it?

Anyway, subsequent to this, we got:

return session.query(Feed).filter(Feed.enabled==True).all()
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, 
line 1267, in all

return list(self)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, 
line 1361, in __iter__

return self._execute_and_instances(context)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py, 
line 1364, in _execute_and_instances
result = self.session.execute(querycontext.statement, 
params=self._params, mapper=self._mapper_zero_or_none())
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py, 
line 753, in execute

clause, params or {})
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 824, in execute

return Connection.executors[c](self, object, multiparams, params)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 872, in _execute_clauseelement

parameters=params
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 938, in __create_execution_context

return dialect.execution_ctx_cls(dialect, connection=self, **kwargs)
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/default.py, 
line 170, in __init__

self.cursor = self.create_cursor()
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/default.py, 
line 258, in create_cursor

return self._connection.connection.cursor()
  File 
/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py, 
line 576, in connection
raise exc.InvalidRequestError(Can't reconnect until invalid 
transaction is rolled back)
sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid 
transaction is rolled back


...until the app was restarted.

Any ideas?

cheers,

Chris

--
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] correct way to handle 'MySQL server has gone away'?

2010-04-26 Thread Michael Bayer 
Chris Withers wrote:
 Chris Withers wrote:
 raise exc.InvalidRequestError(Can't reconnect until invalid
 transaction is rolled back)
 sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid
 transaction is rolled back

 Asking here as it sounds like it might be related.
 It occurred transiently, though, and appears to have resolved itself
 without any intervention. Where would I look to find out what caused the
 invalid transaction and how it was rolled back?

 We've had this occur again, wondered if anyone had any suggestions...

 So, the first error is this:

  return session.query(Feed).filter(Feed.enabled==True).all()
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py,
 line 1267, in all
  return list(self)
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py,
 line 1361, in __iter__
  return self._execute_and_instances(context)
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/query.py,
 line 1364, in _execute_and_instances
  result = self.session.execute(querycontext.statement,
 params=self._params, mapper=self._mapper_zero_or_none())
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/orm/session.py,
 line 753, in execute
  clause, params or {})
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
 line 824, in execute
  return Connection.executors[c](self, object, multiparams, params)
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
 line 874, in _execute_clauseelement
  return self.__execute_context(context)
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
 line 896, in __execute_context
  self._cursor_execute(context.cursor, context.statement,
 context.parameters[0], context=context)
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
 line 950, in _cursor_execute
  self._handle_dbapi_exception(e, statement, parameters, cursor,
 context)
File
 /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.8-py2.5.egg/sqlalchemy/engine/base.py,
 line 931, in _handle_dbapi_exception
  raise exc.DBAPIError.instance(statement, parameters, e,
 connection_invalidated=is_disconnect)
 sqlalchemy.exc.OperationalError: (OperationalError) (2006, 'MySQL server
 has gone away') u'SELECT ...

 So, my guess is that we're not handling this error correctly.
 How should we be handling it?

you have to rollback the current transaction, and either throw an error or
try the whole transaction again.

 line 576, in connection
  raise exc.InvalidRequestError(Can't reconnect until invalid
 transaction is rolled back)
 sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid
 transaction is rolled back

 ...until the app was restarted.


MySQL disconnected.  your transaction was gone.  SQLA will reconnect, but
not until you've let it know that you've agreed to chuck your current
transaction on the app side, since its gone on the DB side.


as always, none of these problems ever occur (well disconnects, but the
app will recover), if you ensure that you are doing:


try:
do_my_stuff()
except:
Session.rollback()
raise





-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.