[sqlalchemy] Re: Can't shake off the 'MySQL server has gone away' Error
I still seem to get the 'MySQL server has gone away' Error. I am suspecting that the problem is in my use of FCGI in production. Thats the only difference between my Development environment and Production and it works 100% without the error in Development env. I guess I'll have to put some more debugging in my application in Production setup. Oh and, after the 'MySQL server has gone away' Error, I immediately get the InvalidRequestError: Can't reconnect until invalid transaction is rolled back. SQLAlchemy version I'm running by the way is '0.4.6'. I created a WSGI middleware class that handles the SQLAlchemy Connections and Sessions like so: Note: the middleware instance gets loaded only once on start of the application. And the get_engine() method lazy-loads the engine once and then returns existing thereafter. sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() models/meta.py from sqlalchemy import MetaData __all__ = ['engine', 'metadata', 'Session'] engine = None# Global metadata. If you have multiple databases with overlapping table # names, you'll need a metadata for each database. Session = None metadata = MetaData() Regards, -Alen On Aug 15, 4:44 pm, Alen Ribic [EMAIL PROTECTED] wrote: one-per-application level That seems to have worked. I'll monitor the log for the next day or so and see how it goes. Thx -Alen On Aug 15, 4:27 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 15, 2008, at 10:21 AM, Alen Ribic wrote: To me it seems like the connections are not being returned back to the pool. Here is my engine code which gets called on each user HTTP request: engine = create_engine( settings.SQLALCHEMY_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) if you call create_engine() on every request, then you're creating a new connection pool for every request. move this up to the module one- per-application level. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can't shake off the 'MySQL server has gone away' Error
On Aug 18, 2008, at 5:08 AM, Alen Ribic wrote: sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() The big mistake here is creating a brand new ScopedSession on each request. This is not how ScopedSession was intended to be used; its created, like Engine, once per application. Some details on this pattern are here: http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_lifespan The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Here's a more reasonable approach: class SQLAlchemyMiddleware(object): def __init__(self, application): self.application = application meta.engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) meta.Session = orm.scoped_session(orm.sessionmaker(autoflush=True, transactional=True, bind=meta.engine)) def __call__(self, environ, start_response): try: return self.application(environ, start_response) except: meta.Session.rollback() raise finally: meta.Session.remove() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can't shake off the 'MySQL server has gone away' Error
The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Ouch, that would be no good. Thank goodness my prod env aint really prod yet. Thank you for your help again. Much appreciated. -Alen On Aug 18, 3:13 pm, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 18, 2008, at 5:08 AM, Alen Ribic wrote: sql.py class SQLAlchemyMiddleware(object): Middleware for providing clean SQLAlchemy Session objects for each Request. def __init__(self, application): self.application = application self.__engine = None def get_engine(self): if self.__engine is None: self.__engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) return self.__engine def init_model(self, engine): Call before using any of the tables or classes in the model. sm = orm.sessionmaker(autoflush=True, transactional=True, bind=engine) meta.engine = engine meta.Session = orm.scoped_session(sm) def __call__(self, environ, start_response): try: engine = self.get_engine() self.init_model(engine) return self.application(environ, start_response) finally: if meta.Session is not None: meta.Session.remove() The big mistake here is creating a brand new ScopedSession on each request. This is not how ScopedSession was intended to be used; its created, like Engine, once per application. Some details on this pattern are here: http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_... The way you have it, a concurrent thread can easily interrupt the ScopedSession instance attached to meta and replace with a new one, with the old one being lost. Here's a more reasonable approach: class SQLAlchemyMiddleware(object): def __init__(self, application): self.application = application meta.engine = create_engine( settings.SQLALCHEMY_DEFAULT_URL, pool_recycle=3600, pool_size=20, echo_pool=True ) meta.Session = orm.scoped_session(orm.sessionmaker(autoflush=True, transactional=True, bind=meta.engine)) def __call__(self, environ, start_response): try: return self.application(environ, start_response) except: meta.Session.rollback() raise finally: meta.Session.remove() --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Can't shake off the 'MySQL server has gone away' Error
Alen Ribic wrote: I still seem to get the 'MySQL server has gone away' Error. I am suspecting that the problem is in my use of FCGI in production. Thats the only difference between my Development environment and Production and it works 100% without the error in Development env. I guess I'll have to put some more debugging in my application in Production setup. MySQL will also throw that error when a query needs more resources than the configuration allows. If there's more data in your production environment or the my.cnf differs that could be it. In any case, enabling error logging for the MySQL server process may shed some light on the root cause. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
