Re: [sqlalchemy] MySQL has gone away
On Thursday, December 27, 2012 10:22:08 PM UTC-3, Michael Bayer wrote: On Dec 27, 2012, at 6:28 PM, Diego Woitasen wrote: Hi, I know that this was discussed several times in the past but I can't solve the problem with the tip that I read in this list. Every morning my application dies with the msg MySQL has gone away. My app has different modules (which are process) and this errors appears in the simplest one and in the complex one too. There is no activity usually at night, specially in one of the modules that has the issue more frequently. This lines setup the session: engine = create_engine(mysql_uri, pool_recycle=config.db_pool_recycle) # Configure in 300 seconds right now. DBSession = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) THe simplest module only do this with the DB: try: delivery = db.DBSession.query(db.Delivery).\ filter(db.Delivery.id == delivery_cmsg.delivery_id).one() except NoResultFound: print 'WARN: invalid delivery ID: ' + int(delivery_cmsg.delivery_id) return finally: db.DBSession.commit() print delivery.name, delivery.start, delivery.status And it has the issue every morning. I'm using SA 0.7.9 and MySQLdb 1.2.4c1. Is there a checklist of things to check that could cause this problem? so you've got pool_recycle, which will make sure the connection is refreshed when checked out from the pool. The other part is to make sure that when the app is idle, you have actually checked all connections back in. So this means every Session has been committed, rolled back, or closed, every Connection closed, every ResultProxy you might have gotten from executing a statement is fully exhausted of its rows and/or closed, and if you happen to be using the very old thread local engine system (which I don't recommend) you'd also have committed/rolled back any transaction there. I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/NXOOxkeCEWMJ. 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] MySQL has gone away
On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote: I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. you need to structure your application such that database operations occur within a consistent framework. This is the simplest when using web frameworks, as all frameworks include some kind of request end hook - that's where the close of the Session would go. Looking at your example again, I see you have a print delivery.name, delivery.status after you've done your commit(). That would be a likely source for the new connection here, as accessing those attributes means the Session needs to go back to the database post-commit to get their most recent value. -- 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] MySQL has gone away
On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote: I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. you need to structure your application such that database operations occur within a consistent framework. This is the simplest when using web frameworks, as all frameworks include some kind of request end hook - that's where the close of the Session would go. Looking at your example again, I see you have a print delivery.name, delivery.status after you've done your commit(). That would be a likely source for the new connection here, as accessing those attributes means the Session needs to go back to the database post-commit to get their most recent value. Hi Michael / Diego - I also have a couple of questions in this area, so I though I would jump on this thread. So, if you close down a Session with Session.close() is the Engine associated with that session (e.g. Session(bind=e) ) then freed up as far as the pool etc is concerned or is there something else I need to do to the Engine? If I have a result proxy and it simply gets garbage collected - is that 'freed' as far as SQLA is concerned or do I need to specifically do something to clean it up (other than exhausting it)? The issue I'm having is that 'sometimes' my app gets handed a dead connection to the MySQL server which tends to make things unhappy. It is a home grown framework, however it is well structured and has clear Entry and Exit points for closing down any Session objects etc. *Somewhere* I am leaving something dangling and it's getting closed down by MySQL with a connection timeout. Finally, I'm using multi Session objects (potentially) within a threaded environment. i.e. Each thread may or may not have one or more Sessions created using sessionmaker(). (A second Session would typically be because of some nested requirement) This appears to work fine, or am I missing something? Each new Session is bound to the engine directly - and it *could* be a different Engine to other Sessions. Thanks for all your help. Cheers Warwick -- 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] MySQL has gone away
On Jan 7, 2013, at 7:47 PM, Warwick Prince wrote: On Jan 7, 2013, at 7:20 PM, Diego Woitasen wrote: I'm back to this :) My problem was that I'm not closing the session properly. My new question is... is there a way to autoclose the session? My example, at the end of the scope? What's the recommend way to do this? I've read the FAQ, but it's not very clear for me. you need to structure your application such that database operations occur within a consistent framework. This is the simplest when using web frameworks, as all frameworks include some kind of request end hook - that's where the close of the Session would go. Looking at your example again, I see you have a print delivery.name, delivery.status after you've done your commit(). That would be a likely source for the new connection here, as accessing those attributes means the Session needs to go back to the database post-commit to get their most recent value. Hi Michael / Diego - I also have a couple of questions in this area, so I though I would jump on this thread. So, if you close down a Session with Session.close() is the Engine associated with that session (e.g. Session(bind=e) ) then freed up as far as the pool etc is concerned or is there something else I need to do to the Engine? the Session is a user of an Engine. it does basically this: connection = engine.connect() # check out from the pool connection.close() # return to the pool the connection.close() you see up there happens at three places: - session.rollback() - session.commit() - session.close() the Engine itself has a set of pooled connections, these are TCP/IP connections to your database. These stay there until the pool and/or engine is disposed, using engine.dispose(). However, the Engine wasn't really intended to be disposed() very often. The vast majority of applications that talk to a database need to continue talking to that DB through the lifespan of the app - hence the connection pool stays open so it can quickly start new conversations. if you want your app to not have any pooled connections when it's idle, then just turn off pooling. Use NullPool. Engine.dispose() is not something you typically need to call explicitly. If I have a result proxy and it simply gets garbage collected - is that 'freed' as far as SQLA is concerned or do I need to specifically do something to clean it up (other than exhausting it)? a ResultProxy is an object returned by a Connection (which is the thing you get from engine.connect()). This object refers to a DBAPI cursor also. The ResultProxy holds onto that cursor/connection as you read results from it. It then closes the connection/cursor when it has no more use for them. The ResultProxy does this close when: - all rows are exhausted - fetchall(), or enough calls to fetchone()/fetchmany(), first(), scalar(). - immediately, if there are no rows. the result you get from insert(), update(), delete() etc. assuming theres no RETURNING, that result is closed. - in all cases when you call result.close(). - when the garbage collector collects it. but its bad form to rely upon this. The only way you can get here is if the result returns rows, and you don't read them all. The issue I'm having is that 'sometimes' my app gets handed a dead connection to the MySQL server which tends to make things unhappy. It is a home grown framework, however it is well structured and has clear Entry and Exit points for closing down any Session objects etc. *Somewhere* I am leaving something dangling and it's getting closed down by MySQL with a connection timeout. there's a lot of ways to trace connection activity, there's echo_pool=debug, there's the AssertionPool which can be helpful in some situations (it even stores the stack trace where the connection was checked out, read its source and perhaps emulate this approach for a more involved debugging approach), there's a whole event system which you can use to make sure no Connection is older than a certain time (put a timestamp in connection.info and check it), so I'd look into these methods to figure out where this dead connection is coming from. Finally, I'm using multi Session objects (potentially) within a threaded environment. i.e. Each thread may or may not have one or more Sessions created using sessionmaker(). (A second Session would typically be because of some nested requirement) This appears to work fine, or am I missing something? its fine, just don't open up a Session then leave it hanging open. Each new Session is bound to the engine directly - and it *could* be a different Engine to other Sessions. shouldn't matter... -- 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
[sqlalchemy] MySQL has gone away
Hi, I know that this was discussed several times in the past but I can't solve the problem with the tip that I read in this list. Every morning my application dies with the msg MySQL has gone away. My app has different modules (which are process) and this errors appears in the simplest one and in the complex one too. There is no activity usually at night, specially in one of the modules that has the issue more frequently. This lines setup the session: engine = create_engine(mysql_uri, pool_recycle=config.db_pool_recycle) # Configure in 300 seconds right now. DBSession = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) THe simplest module only do this with the DB: try: delivery = db.DBSession.query(db.Delivery).\ filter(db.Delivery.id == delivery_cmsg.delivery_id).one() except NoResultFound: print 'WARN: invalid delivery ID: ' + int(delivery_cmsg.delivery_id) return finally: db.DBSession.commit() print delivery.name, delivery.start, delivery.status And it has the issue every morning. I'm using SA 0.7.9 and MySQLdb 1.2.4c1. Is there a checklist of things to check that could cause this problem? Regards, Diego -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/eCZCfjMB29gJ. 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] MySQL has gone away
On Dec 27, 2012, at 6:28 PM, Diego Woitasen wrote: Hi, I know that this was discussed several times in the past but I can't solve the problem with the tip that I read in this list. Every morning my application dies with the msg MySQL has gone away. My app has different modules (which are process) and this errors appears in the simplest one and in the complex one too. There is no activity usually at night, specially in one of the modules that has the issue more frequently. This lines setup the session: engine = create_engine(mysql_uri, pool_recycle=config.db_pool_recycle) # Configure in 300 seconds right now. DBSession = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=engine)) THe simplest module only do this with the DB: try: delivery = db.DBSession.query(db.Delivery).\ filter(db.Delivery.id == delivery_cmsg.delivery_id).one() except NoResultFound: print 'WARN: invalid delivery ID: ' + int(delivery_cmsg.delivery_id) return finally: db.DBSession.commit() print delivery.name, delivery.start, delivery.status And it has the issue every morning. I'm using SA 0.7.9 and MySQLdb 1.2.4c1. Is there a checklist of things to check that could cause this problem? so you've got pool_recycle, which will make sure the connection is refreshed when checked out from the pool. The other part is to make sure that when the app is idle, you have actually checked all connections back in. So this means every Session has been committed, rolled back, or closed, every Connection closed, every ResultProxy you might have gotten from executing a statement is fully exhausted of its rows and/or closed, and if you happen to be using the very old thread local engine system (which I don't recommend) you'd also have committed/rolled back any transaction there. -- 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.
[sqlalchemy] MySQL has gone away error
I have the follwing setting and I create engine from config.
sqlalchemy.url : mysql://sfdev:sf...@localhost:3306/sfdev
sqlalchemy.pool_recycle : 3600
sqlalchemy.convert_unicode : true
I am getting the following error. Seems like recycle required, but I
have mentioned recycle in the configuration.
Any one guess what is wrong? I use Sqlalchemy 5.6.
[ERROR/MainProcess] Task iris.jobschedulertask[0df86366-
fab8-402d-851a-921bfebcb2e6] raised exception: (OperationalError)
(2006, 'MySQL server has gone away')
Traceback (most recent call last):
File /home/krish/sf-env/lib/python2.6/site-packages/celery-0.8.2-
py2.6.egg/celery/execute.py, line 261, in execute
result = fun(*args, **kwargs)
File /home/krish/sf-env/lib/python2.6/site-packages/celery-0.8.2-
py2.6.egg/celery/task/base.py, line 149, in __call__
return self.run(*args, **kwargs)
File /home/krish/sf/iris/tasks/scheduler.py, line 18, in run
model.Task.scheduled datetime.now())).all()
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py, line 1267, in all
return list(self)
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py, line 1361, in
__iter__
return self._execute_and_instances(context)
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py, line 1364, in
_execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/session.py, line 755, in
execute
clause, params or {})
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py, line 824, in
execute
return Connection.executors[c](self, object, multiparams, params)
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py, line 874, in
_execute_clauseelement
return self.__execute_context(context)
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py, line 896, in
__execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py, line 950, in
_cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File /home/krish/sf-env/lib/python2.6/site-packages/
SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py, line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) (2006, 'MySQL server has gone
away')
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@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.
[sqlalchemy] MySql has gone away
Hi All, I was getting My Sql has gone away, then i set sqlalchemy.pool_recylce=3600 in prod.cfg. it was woking fine but but still some time i get the same error i.e MySql has gone away. Can any one tell me what should i do? Thanks anD Regards Reetesh Nigam --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
