Michael, Thanks very much. This helps.
I'm using InnoDB without autocommit. The reason for the odd, long-idling connection is that I'm actually using Flask-SQLAlchemy, and the long-idling connection is held by Flask-SQLAlchemy's primary, request-scoped session. I do some long-running, non-transactional work in my request, then come back to the session and try to commit, and the connection, checked out at the start of the request, has become stale and been dropped by MySQL. Although this is specific to Flask-SQLAlchemy, the actual events are reproducible in SQLAlchemy (as above) so I figured it must be common and asked here. I recognize that session.connection().invalidate(), or close(), would result in loss of session state, but I need a way to pick up at this point and continue with additional queries even after the disconnect, so getting a fresh connection at that point seemed like the best approach. Thanks, Andy On Monday, June 29, 2015 at 4:14:11 PM UTC-4, Michael Bayer wrote: > > > > On 6/29/15 2:33 PM, Andy Crain wrote: > > > But these seem heavy handed and wrong. What I’d like to do is just discard > this stale connection (in my session and in the pool) and get a fresh one, > but I can’t determine how to. > > > at the bottom of that section, the important part is about invalidation: > > > http://docs.sqlalchemy.org/en/rel_1_0/core/pooling.html#more-on-invalidation > > specifically invalidate(): > > > http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html?highlight=invalidate#sqlalchemy.engine.Connection.invalidate > > > from session: > > session.connection().invalidate() > > however, you've now killed off your transaction, and you will lose > whatever isn't committed on it. If you don't need the transaction to > last this long, then you shouldn't have a connection checked out - the > Session only has a connection checked out when you are in a transaction. > So I think the original assertion "due to some unavoidable long-running > code" doesn't really make any sense, especially if you are using an ORM > Session. If this isn't impacting you because you are on MyISAM and are > essentially using autocommit, then just set your Session to use autocommit: > http://docs.sqlalchemy.org/en/rel_1_0/orm/session_transaction.html#autocommit-mode; > > no more stale connections. > > > > > > > Thanks for any help. > > > Andy > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
