Re: [sqlalchemy] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
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? On 31 October 2011 17:16, Michael Bayer mike...@zzzcomputing.com wrote: 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-sessionsthis 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
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-sessionsthis 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 On 31 October 2011 14:38, Michael Bayer mike...@zzzcomputing.com wrote: 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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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] Debugging 'StatementError: Can't reconnect until invalid transaction is rolled back' error
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. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@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.
