One way I resolved this is, periodic dummy query 'select 1' to make sure
connection is held.
Using Tornado, I do something like following in my models.
class Db(object):
def __init__(self):
engine_string =
'mysql'+'://'+options.mysql_user+':'+options.mysql_password+'@
'+options.mysql_host+'/'+options.mysql_database
engine = create_engine(engine_string, \
pool_recycle=3600, \
echo_pool=False, \
echo=False)
Base.metadata.create_all(engine)
Session = sessionmaker()
Session.configure(bind=engine)
self.s = Session() # orm
self.c = engine.connect() # expr
PeriodicCallback(self.ping_db, 2400 * 1000).start()
def ping_db(self):
try:
self.s.execute("select 1")
except:
self.s.rollback()
On Sun, Jan 12, 2014 at 7:07 PM, Michael Bayer <mike...@zzzcomputing.com>wrote:
> pool_recycle applies to when the connection is checked in, and then you
> check it out. if the connection is older than N seconds, it gets recycled
> out before being returned.
>
> on the other hand, if you check out a connection and just hold onto it for
> hours, it’ll time out.
>
> other than that there’s not enough information to go on here.
>
>
> On Jan 12, 2014, at 9:33 PM, Ni Wesley <nisp...@gmail.com> wrote:
>
> Hi all,
> I hit a problem when use sqlalchemy with mysql.
> I found the problem is mysql's auto close to nonactive connection over 8
> hours.
>
> And , I googled a lot and find pool_recycle option for create_engine.
> But seems does not work.
>
> Here is my code:
> engine = create_engine(db_url, echo=engine_echo,pool_recycle=30)
> session = scoped_session(sessionmaker(bind=engine))
>
> And in mysql, I set wait_timeout to 60 secs for testing.
>
> After this, I still got exception here:
> 2014-01-12 22:32:45,325 INFO sqlalchemy.engine.base.Engine SELECT
> device.id AS device_id, device.devicetoken AS device_devicetoken
> FROM device
> WHERE device.devicetoken = %s
> LIMIT %s
> INFO:sqlalchemy.engine.base.Engine:SELECT device.id AS device_id,
> device.devicetoken AS device_devicetoken
> FROM device
> WHERE device.devicetoken = %s
> LIMIT %s
> 2014-01-12 22:32:45,326 INFO sqlalchemy.engine.base.Engine ('323', 1)
> INFO:sqlalchemy.engine.base.Engine:('323', 1)
> ERROR:tornado.application:Uncaught exception, closing connection.
> Traceback (most recent call last):
> File "/usr/lib/python2.6/site-packages/tornado/iostream.py", line 341,
> in wrapper
> callback(*args)
> File "/usr/lib/python2.6/site-packages/tornado/stack_context.py", line
> 331, in wrapped
> raise_exc_info(exc)
> File "/usr/lib/python2.6/site-packages/tornado/stack_context.py", line
> 302, in wrapped
> ret = fn(*args, **kwargs)
> File "deviceserver.py", line 182, in send_message
> tmp_usr =
> session.query(Device).filter_by(devicetoken=self._devicetoken).first()
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py",
> line 2282, in first
> ret = list(self[0:1])
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py",
> line 2149, in __getitem__
> return list(res)
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py",
> line 2353, in __iter__
> return self._execute_and_instances(context)
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/orm/query.py",
> line 2368, in _execute_and_instances
> result = conn.execute(querycontext.statement, self._params)
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
> line 662, in execute
> params)
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
> line 761, in _execute_clauseelement
> compiled_sql, distilled_params
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
> line 874, in _execute_context
> context)
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
> line 1024, in _handle_dbapi_exception
> exc_info
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/util/compat.py",
> line 196, in raise_from_cause
> reraise(type(exception), exception, tb=exc_tb)
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/engine/base.py",
> line 867, in _execute_context
> context)
> File
> "/usr/lib/python2.6/site-packages/SQLAlchemy-0.8.3-py2.6-linux-x86_64.egg/sqlalchemy/engine/default.py",
> line 324, in do_execute
> cursor.execute(statement, parameters)
> File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 201,
> in execute
> self.errorhandler(self, exc, value)
> File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line
> 36, in defaulterrorhandler
> raise errorclass, errorvalue
> OperationalError: (OperationalError) (2006, 'MySQL server has gone away')
> 'SELECT device.id AS device_id, device.devicetoken AS device_devicetoken
> \nFROM device \nWHERE device.devicetoken = %s \n LIMIT %s' ('323', 1)
>
>
> --
> 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/groups/opt_out.
>
>
>
--
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/groups/opt_out.
