hi Gloria - unfortunately this doesn't tell me anything useful, except that your application is indeed checking out an ever growing number of connections. But we already knew that due to the original error you were getting.
The bug is very likely in your own application, such as an errant Session() being created somewhere and stored in a global collection, etc. you can try running your application entirely single-threaded, and instead of using QueuePool use sqlalchemy.pool.AssertionPool as the "poolclass" argument to create_engine(), i.e. from sqlalchemy.pool import AssertionPool engine = create_engine(url, poolclass=AssertionPool) An exception will be raised as soon as a second concurrent connection in the application is requested, indicating the usage of more than one connection per process at a time. - mike Gloria wrote: > > I have better log output now, and more concise test results. Here is > what I did. > I'm running only one thread at a time with pool logging on. I have > CherryPy sessions turned off, since my service is ReSTful, and by > design I get a different session ID each time (as expected). Until the > crash, I get results returned after each query, and the log shows the > SQLAlchemy session being closed. The log is below. It seems that > different vs. the same HTTP sessions would be irrelevant, but the HTTP > session id is logged as well. > I think we hit a bug. > > Thanks again, > Gloria > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > Aug 31 12:37:57: Stopping daemon member-search-rest > Aug 31 12:37:57: Daemon member-search-rest stopped > Aug 31 12:38:02: member-search-rest started > Aug 31 12:38:02: Daemon member-search-rest DAEMON_PID=29240 > HTTP Method: GET > Session is: a9adcf7d3e3228c222ed84bfe0900a078b1a50d4 > > 2009-08-31 12:38:13,347 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9b62f0c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:13,347 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9b62f0c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: ef5e2549d615d2cffce5649d0df3ca763b3a841c > > 2009-08-31 12:38:18,135 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9dba79c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:18,136 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9dba79c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: b58b6ba51c3eefeb952c265240da7357ec314ddb > > 2009-08-31 12:38:18,993 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9ed8aac; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:18,993 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9ed8aac; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 5ce7bb5cadeda4d81bce9cfa21f4393ba281b04e > > 2009-08-31 12:38:24,079 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa165e2c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:24,079 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa165e2c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: be7700a9d85cf6fdc9f0fb2451987024ad68fcf5 > > 2009-08-31 12:38:24,728 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa1691ec; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:24,728 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa1691ec; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 2aa52f0fee160ee1bc3385d82fa313ef01ffb619 > > 2009-08-31 12:38:32,849 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa16948c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:32,849 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa16948c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: c6ae2196593153f19b713ef8d8cad67154d7475d > > 2009-08-31 12:38:33,473 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9b5d02c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:33,473 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9b5d02c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 0be31eb6334db41ad80a504c61ea1202d3d72611 > > 2009-08-31 12:38:35,821 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa2aaf7c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:35,822 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa2aaf7c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 00d3040b94497c126fbb427f6027b22f7dac74f6 > > 2009-08-31 12:38:36,399 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa14ec6c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:36,400 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa14ec6c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 75432bc1d3160bd16085eb20e75ad5d190d62747 > > 2009-08-31 12:38:38,993 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9ed580c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:38,993 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9ed580c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 44d4a2478dbee124f740ca4baed654d01840e3a3 > > 2009-08-31 12:38:39,653 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9ed5b8c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:39,653 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9ed5b8c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 760bbb7dd3e7d972e599fd305e6f8b170af57966 > > 2009-08-31 12:38:43,196 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa619a3c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:43,196 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa619a3c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 245db880a302246612c3ace911bc701c0a97c748 > > 2009-08-31 12:38:43,862 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa82b8ec; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:43,862 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa82b8ec; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: d0f9569ccf43048fb364478bfb04f6acc791bc4b > > 2009-08-31 12:38:46,118 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa9785dc; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:46,118 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa9785dc; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 4217055db81e565894efeb63dd0f9ad4409a24c7 > > 2009-08-31 12:38:46,738 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xab5310c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:46,738 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xab5310c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 6f8a6bad927c88258dd9f2ca66cc0c07336a6679 > > 2009-08-31 12:38:48,853 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xad4809c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:48,853 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xad4809c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 86f3135cb7cb73689c1b4903aed648520538ade8 > > 2009-08-31 12:38:49,514 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xaee679c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:49,514 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xaee679c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 2896dbc1749bf78f8fecec409a730697d020c5cf > > 2009-08-31 12:38:52,127 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xb0d872c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:52,127 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xb0d872c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 73aa4b2fdbc3debad8c4c894739c8bc47e1e36e0 > > 2009-08-31 12:38:52,705 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xb1f679c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:52,706 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xb1f679c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 500cd841e0eb4584478dc9be922c85bdaba0addb > > 2009-08-31 12:38:55,694 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9b625dc; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:55,694 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9b625dc; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: e8459169d068cc5d4a6254244b6a9f46078cecdd > > 2009-08-31 12:38:56,362 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9b62aac; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:56,363 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9b62aac; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 258a78828d2e753b87fcc61744ed0fac409d9e23 > > 2009-08-31 12:38:58,854 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0x9c974fc; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:58,854 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0x9c974fc; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: 99e49a109827975ca40802c70ad725daa80415e8 > > 2009-08-31 12:38:59,535 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xb27387c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:38:59,536 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xb27387c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: b94379c638e339e88d11d63cf52f6279dfa1e15b > > 2009-08-31 12:39:01,895 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xa10a56c; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:39:01,895 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xa10a56c; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > HTTP Method: GET > Session is: f7f670b7b167244c59a1f0aa5e3192b224295337 > > 2009-08-31 12:39:02,674 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Created new connection <connection object at 0xad265dc; dsn: > 'dbname=bluesky host=db1.qa.fuwt user=bluesky password=xxxxxxx', > closed: 0> > 2009-08-31 12:39:02,674 INFO sqlalchemy.pool.QueuePool.0x...4b2c > Connection <connection object at 0xad265dc; dsn: 'dbname=bluesky > host=db1.qa.fuwt user=bluesky password=xxxxxxx', closed: 0> checked > out from pool > > > <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" > "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";> > <html> > <head> > <meta http-equiv="Content-Type" content="text/html; > charset=utf-8"></meta> > <title>500 Internal Server Error</title> > <style type="text/css"> > #powered_by { > margin-top: 20px; > border-top: 2px solid black; > font-style: italic; > } > > #traceback { > color: red; > } > </style> > </head> > <body> > <h2>500 Internal Server Error</h2> > <p>The server encountered an unexpected condition which > prevented it from fulfilling the request.</p> > <pre id="traceback">Traceback (most recent call last): > File "/usr/lib/python2.5/site-packages/CherryPy-3.2.0alpha-py2.5.egg/ > cherrypy/_cprequest.py", line 657, in respond > response.body = self.handler() > File "/usr/lib/python2.5/site-packages/CherryPy-3.2.0alpha-py2.5.egg/ > cherrypy/lib/encoding.py", line 193, in __call__ > self.body = self.oldhandler(*args, **kwargs) > File "/usr/lib/python2.5/site-packages/CherryPy-3.2.0alpha-py2.5.egg/ > cherrypy/_cpdispatch.py", line 25, in __call__ > return self.callable(*self.args, **self.kwargs) > File "MemberSearchDispatcher.py", line 65, in membersearch > result=(http_method)(fields) > File "/opt/rest-bluesky/trunk/src/MemberSearchReST.py", line 173, in > GET > for array in resultObj: > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/orm/query.py", line 1280, in __iter__ > return self._execute_and_instances(context) > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/orm/query.py", line 1283, in _execute_and_instances > result = self.session.execute(querycontext.statement, > params=self._params, mapper=self._mapper_zero_or_none()) > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/orm/session.py", line 754, in execute > return self.__connection(engine, close_with_result=True).execute( > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/orm/session.py", line 719, in __connection > return self.transaction._connection_for_bind(engine) > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/orm/session.py", line 329, in _connection_for_bind > conn = bind.contextual_connect() > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/engine/base.py", line 1229, in contextual_connect > return self.Connection(self, self.pool.connect(), > close_with_result=close_with_result, **kwargs) > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/pool.py", line 142, in connect > return _ConnectionFairy(self).checkout() > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/pool.py", line 304, in __init__ > rec = self._connection_record = pool.get() > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/pool.py", line 161, in get > return self.do_get() > File "/usr/lib/python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/ > sqlalchemy/pool.py", line 628, in do_get > raise exc.TimeoutError("QueuePool limit of size %d overflow %d > reached, connection timed out, timeout %d" % (self.size(), > self.overflow(), self._timeout)) > TimeoutError: QueuePool limit of size 20 overflow 5 reached, > connection timed out, timeout 30 > </pre> > <div id="powered_by"> > <span>Powered by <a href="http://www.cherrypy.org";>CherryPy > 3.2.0alpha</a></span> > </div> > </body> > </html> > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
