Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error

[Benjamin Sims]

Hi all,

I'm seeing this error intermittently in my application since switching to MySQL - it occurs on several different pages and I can't track down why:

StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT [... the same select every time, which gets a user from the database]

I have searched on the error and read the various discussions, for example this one:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/a8031eefc4d5d0cd

My understanding is that somewhere in my code I need to be ensuring that I do rollback/commit/close when the operation is complete. The problem is... where? Is there a way I can get a log to see the original query/point in my code which triggers the error.

If it is relevant, I'm using Pyramid and have recently also suffered from 'MySQL has gone away errors'.

Thanks,

Ben

[also described this problem on the Pyramid list, apologies to those of you that subscribe to both]

[Michael Bayer]

On Oct 31, 2011, at 8:45 AM, Benjamin Sims wrote:

Hi all,

I'm seeing this error intermittently in my application since switching to MySQL - it occurs on several different pages and I can't track down why:

StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT [... the same select every time, which gets a user from the database]

how are you handling threading, as well as Session lifecycle ?  this kind of error usually occurs from one of two things:  a. sharing Sessions, or objects attached to them, between threads or b. no clear point of Session start/end, incorrect handling of exceptions, etc.

I have searched on the error and read the various discussions, for example this one:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/a8031eefc4d5d0cd

My understanding is that somewhere in my code I need to be ensuring that I do rollback/commit/close when the operation is complete. The problem is... where? Is there a way I can get a log to see the original query/point in my code which triggers the error.

sure you should turn on SQLAlchemy logging fully (http://www.sqlalchemy.org/docs/core/engines.html?highlight=logging#configuring-logging)  , and also make sure you aren't squashing exceptions (i.e. except MyException, e: pass type of thing) - also you'd want to add code such as log.error("exception occurred", exc_info=True) to the point at which exceptions are caught so that a full stack trace is written out to the log.

[Benjamin Sims]

I suspect that my understanding of both threading and Sessions is going to be found pretty wanting here; I've basically just lifted things from Pyramid/SQLAlchemy examples.

My understanding is that the framework handles threading, and that based on examples and http://www.sqlalchemy.org/docs/orm/session.html#contextual-thread-local-sessions this is the correct way to create a session available throughout the request:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

(this is in my models file)

I then use this either by doing:

DBSession.query()

or

from models import DBSession

session = DBSession()

session.query()

So, I'm not doing anything explicit in turns of ending the session - I guess I should be, but I had understood that this would happen on issue of next query/completion of request.

Thanks, I'll look into it - I was used to Paster, where I could see the logs in real time. I now get errors in the Apache error log... but not whatever is initially causing the one I can see.

Ben

PS: I do have a separate process that runs in a script via cron and uses the database which would seem like a candidate, but disabling that does not stop the errors

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

[Michael Bayer]

On Oct 31, 2011, at 12:56 PM, Benjamin Sims wrote:

I suspect that my understanding of both threading and Sessions is going to be found pretty wanting here; I've basically just lifted things from Pyramid/SQLAlchemy examples.

My understanding is that the framework handles threading, and that based on examples and http://www.sqlalchemy.org/docs/orm/session.html#contextual-thread-local-sessions this is the correct way to create a session available throughout the request:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))

(this is in my models file)

I then use this either by doing:

DBSession.query()

or

from models import DBSession

session = DBSession()

session.query()

So, I'm not doing anything explicit in turns of ending the session - I guess I should be, but I had understood that this would happen on issue of next query/completion of request.

that's a question for the Pyramid folks.     There should also be a single path for handling of exceptions - any unexpected exception would propagate to the same point, there's a Session.rollback() right there, and it gets reported.

[Benjamin Sims]

Thanks for this. As an update, I put in code that notifies me when an error occurs. The real cause of this seems to still be the 'MySQL has gone away' error. This occurs in various places, always as the first DB call in a request (obviously).

Is my understanding correct that in order to solve this I have to make sure that all DB calls do a session.close() or session.rollback() when finished? Any thoughts on how to track which call is leaving the connection open?

[Michael Bayer]

On Nov 1, 2011, at 5:49 PM, Benjamin Sims wrote:

> Thanks for this. As an update, I put in code that notifies me when an error occurs. The real cause of this seems to still be the 'MySQL has gone away' error. This occurs in various places, always as the first DB call in a request (obviously).
>
> Is my understanding correct that in order to solve this I have to make sure that all DB calls do a session.close() or session.rollback() when finished?

If you're doing a web app with pyramid, there really should be built in, or very easy to implement, a single event at the end of the request - it calls session.close(), and that's it. You shouldn't have to do defensive rollback()/close() calls anywhere else, it should be automatic, ultimately occurring on just one line of code, somewhere. My understanding was that the whole zope sqlalchemy thing would at least do this, and the folks on the Pyramid list should be able to show you how.

If some other part of your app is not within the usual "web request" system, such as a script that runs in the background, you'd need to ensure that system cleans up after itself too, but always, there should be an architecture in place that takes care of this, without the need for you to "remember to do it everywhere" - that's not how things like this should be done.

A "MySQL has gone away" error can be a symptom of out-of-sequence calls on the connection, which would occur with concurrent access among multiple threads. You should make sure that no SQLAlchemy mapped object that you got from a Session is shared among threads, or pulled from a dictionary that threads share, or part of any kind of global registry, without special handling to ensure that individual threads get a local copy of the state before manipulating or reading from it. This refers to instances of objects, not the classes or mappings themselves which aren't subject to these limitations.

[Phil]

To solve this problem, you should open a new db session at the beginning of the web request, and close the session at the end of the request.

See the pattern here:

http://docs.sqlalchemy.org/en/latest/orm/session.html#using-thread-local-scope-with-web-applications