Hi Sorry for bringing back this rather old thread. I am going to add some more information on the discussion of this issue.
We have constantly encountered the exactly same issue mentioned by Josh with Pylons and repoze.who 1.0 in a low traffic web application. After analysed the check out pattern of SQLAlchemy connection pool, we concluded that repoze.*, friendlyformplugin, and SQLAlchemyPlugin cause this problem. (For other people having the "MySQL server gone away" issue, I suggest you check pool_recycle directive of SQLAlachemy first if you have not) As Josh mentioned, SQLAlahcmey expect the session to be closed after use. For web application, SQLAlachemy recommend closing the session at the end of every request. Pylons followed this recommendation by removing the session in the "finally" clause in the BaseController. So when a request pass through the BaseController, the SQLAlachemy session would always be properly cleaned up. However it is not the case that every request would pass through the BaseController when using repoze.*, friendlyformplugin and SQLAlchemyPlugin. Here is a simplified view of how a **normal** request use a SQLAlchemy session (I assumed using default configuration of Pylons so sessions are in fact SQLAlchemy's scoped_session; and the user is logged in): == Normal Request == 1. Request come in 2. SQLAlchemyPlugin use Session to retrieve user object. scoped_session create the underlying session object and hence a connection is checked out from connection pool. 3. repoze.who's PluggableAuthenticationMiddleware pass control to downstream middleware. 4. Control pass to controller action, controller use Session to query the database. 5. Session.remove() called in BaseController to clean up the session and return connection to connection pool 6. Completing request and return result to user. 7. Thread wait for another request. The problem occurred when requesting friendlyform's login_handler and logout_handler url. Here is how the login/logout request use a SQLAlchemy session: == Problematic Request (Login) == 1. Request come in 2. Friendlyform handle the request 3. Invoke SQLAlchemyPlugin to check login credential, it use Session to query the user model. Hence, scoped_session create the underlying session object and hence a connection is checked out from connection pool. 4. Credential accepted, Friendlyform set environ['repoze.who.application'] to a HTTPFound instance (for redirecting user to the post_login_path) 5. repoze.who's PluggableAuthenticationMiddleware execute HTTPFound instead of statically configured downstream middleware, thus Pylons controllers are not executed 6. 302 Found send to user to redirect to post_login_path. Request completed. 7. Thread wait for another request. The about observation could be verified by setting the log level of sqlalchemy.pool to DEBUG, that will show how a connection is checked out and return to connection pool. The problem is, the session is not properly closed when going through the login_handler or logout_handler of friendlyform. Therefore, the same session and connection is used again in next request of this thread. MySQL server drop connection after several hours by default, so if next request for this thread come in after MySQL dropping the connection, you receive the "MySQL server gone away" error. We solve the problem by adding a middleware warping repoze.who's middleware, and it call Session.remove() when the request path is login_handler or logout_handler. Please suggest if it is better to handle this in repoze.who. Regards Edwin On Nov 19, 6:05 am, Gustavo Narea <m...@gustavonarea.net> wrote: > Hello, Josh. > > On Nov 17, 4:34 am, Josh Kelley <josh...@gmail.com> wrote: > > > > > > > > > > > On Nov 16, 5:45 pm, Gustavo Narea <m...@gustavonarea.net> wrote: > > > > Thanks for the information. > > > > I couldn't find the message "Can't reconnect until invalid transaction > > > is rolled back" in the output you pasted and I think the link to the > > > FAQ refers to another type of issue. > > > Sorry. There are actually two exceptions; the first I already posted, > > and here's the second, with the "Can't reconnect until invalid > > transaction is rolled back" error. > > >http://pastie.org/1304673 > > > I don't know why I got two exceptions on a single request? I assumed > > the Pylons / repoze stack would have aborted after the first. (Unless > > the second exception was while trying to render the error page? If > > that is what's happening, is there a way to keep repoze.who from > > breaking rendering the error page?) > > > > I've been reading about that error on the MySQL documentation and it > > > seems like all the possible causes are external to the > > > application:http://dev.mysql.com/doc/refman/5.0/en/gone-away.html > > > > You can try and tweak the Session if you want; it may or may not help. > > > I don't think handling the exception in repoze.who.plugins.sa is an > > > appropriate solution because we'd silencing a problem that should be > > > fixed. > > > I understand that the causes of the "MySQL server has gone away" are > > external to the app and will work on that later; my concern right now > > is that (as far as I can tell) repoze.who.plugins.sa isn't cleaning up > > when this happens, which causes the app to get stuck in the "Can't > > reconnect until invalid transaction is rolled back" state (and I have > > to restart the app to get anything working again). The FAQ I linked > > to does appear to be a different specific issue, but its solution of > > using a try/except block to properly handle rollbacks seems to apply > > here too. > > If I make repoze.who.plugins.sa handle the exception, I'd be silencing > that error, which I'd rather not do as that sort of things always make > debugging harder. > > If you had another WSGI middleware that uses SA, chances are you'd get > the same error, so I think a better solution for you would be to > subclass ErrorMiddleware like this: > """ > class MyErrorMiddleware(ErrorMiddleware): > > def exception_handler(self, exc_info, environ): > exception_class = exc_info[0] > if exception_class in (InvalidRequestError, OperationalError): > # rollback... > return super(MyErrorMiddleware, > self).exception_handler(exc_info, environ) > """ > > That should avoid the second exception, allowing the error page to be > returned without problems. And it'd also work if that error happens > within your application and you're not expecting it. > > I'm happy to reconsider handling the exception if more hit the same > problem, though. > > HTH, > > - Gustavo. -- You received this message because you are subscribed to the Google Groups "pylons-discuss" group. To post to this group, send email to pylons-disc...@googlegroups.com. To unsubscribe from this group, send email to pylons-discuss+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en.
