ProgrammingError: (ProgrammingError) Cannot operate on a closed database. None [{}]

[ldiamond2]

Using Python 2.6.5 on Ubuntu 10.10 in a virtual env.
Everything set up as described in the documentation.
using this base template (no code modification): bin/paster create -t pyramid_routesalchemy
serving using: ../bin/paster serve --reload development.ini

Simply refresh quickly (can take up to 50 refresh) and this error comes up.

URL: http://127.0.0.1:6543/contacts
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/WebError-0.10.3-py2.6.egg/weberror/evalexception.py', line 431 in respond
app_iter = self.application(environ, detect_start_response)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/repoze.tm2-1.0b1-py2.6.egg/repoze/tm/init.py', line 23 in call
result = self.application(environ, save_status_and_headers)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/pyramid-1.0-py2.6.egg/pyramid/router.py', line 158 in call
response = view_callable(context, request)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/pyramid-1.0-py2.6.egg/pyramid/config.py', line 2824 in _rendered_view
response = wrapped_view(context, request)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/pyramid-1.0-py2.6.egg/pyramid/config.py', line 2916 in _requestonly_view
response = view(request)
File '/home/ldiamond/dev/proj/personalwebsite/env/lewisdiamond/lewisdiamond/views/views.py', line 11 in newmethod
return dict({'menu':getMenu(), 'homecss':static_url('lewisdiamond:static/home.css',req)}, **d)
File '/home/ldiamond/dev/proj/personalwebsite/env/lewisdiamond/lewisdiamond/models/menu.py', line 18 in getMenu
menu = dbsession.query(MainMenuItem).filter(MainMenuItem.parent==None).order_by(MainMenuItem.order).all()
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/orm/query.py', line 1589 in all
return list(self)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/orm/query.py', line 1699 in iter
return self._execute_and_instances(context)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/orm/query.py', line 1704 in _execute_and_instances
mapper=self._mapper_zero_or_none())
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/orm/session.py', line 724 in execute
clause, params or {})
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/engine/base.py', line 1191 in execute
params)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/engine/base.py', line 1269 in _execute_clauseelement
parameters=params
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/engine/base.py', line 1382 in __create_execution_context
None, None)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/engine/base.py', line 1377 in __create_execution_context
connection=self, **kwargs)
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/engine/default.py', line 392 in init
self.cursor = self.create_cursor()
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/engine/default.py', line 534 in create_cursor
return self._connection.connection.cursor()
File '/home/ldiamond/dev/proj/personalwebsite/env/lib/python2.6/site-packages/SQLAlchemy-0.6.7-py2.6.egg/sqlalchemy/pool.py', line 423 in cursor
c = self.connection.cursor(_args, *_kwargs)
ProgrammingError: (ProgrammingError) Cannot operate on a closed database. None [{}]

[mcdonc]

I saw this conversation after the fact on IRC. Salient: raydeo may have tracked this down to a SQLite-specific issue, and he said he could only see it when reloading via Firefox (he's on a Mac).

[mcdonc]

Also the OP indicated that the same error happened both with and without the presence of ZopeTransactionExtension (he originally indicated he thought it was a ZTE issue).

[mmerickel]

The problem is most definitely SQLite-related or at most SQLAlchemy. Regardless, judging by some quick googling it's definitely not specific to Pyramid.

[ceol]

Also had this issue running Ubuntu 10.04, Python 2.6. It doesn't happen often... although I can't figure out when.

URL: http://localhost:6543/FrontPage/edit_page
Module weberror.evalexception:431 in respond view

app_iter = self.application(environ, detect_start_response)
Module repoze.tm:23 in call view
result = self.application(environ, save_status_and_headers)
Module pyramid.router:158 in call view
response = view_callable(context, request)
Module pyramid.config:2824 in _rendered_view view
response = wrapped_view(context, request)
Module pyramid.config:2916 in _requestonly_view view
response = view(request)
Module tutorial.views:55 in edit_page view
page = session.query(Page).filter_by(name=name).one()
Module sqlalchemy.orm.query:1656 in one view
ret = list(self)
Module sqlalchemy.orm.query:1699 in iter view
return self._execute_and_instances(context)
Module sqlalchemy.orm.query:1704 in _execute_and_instances view
mapper=self._mapper_zero_or_none())
Module sqlalchemy.orm.session:724 in execute view
clause, params or {})
Module sqlalchemy.engine.base:1191 in execute view
params)
Module sqlalchemy.engine.base:1269 in _execute_clauseelement view
parameters=params
Module sqlalchemy.engine.base:1382 in __create_execution_context view
None, None)
Module sqlalchemy.engine.base:1377 in __create_execution_context view
connection=self, **kwargs)
Module sqlalchemy.engine.default:392 in init view
self.cursor = self.create_cursor()
Module sqlalchemy.engine.default:534 in create_cursor view
return self._connection.connection.cursor()
Module sqlalchemy.pool:423 in cursor view
c = self.connection.cursor(_args, *_kwargs)
ProgrammingError: (ProgrammingError) Cannot operate on a closed database. None [{}]

[cockroach]

Hey

I also suffered from this issue in a project where I use sqlite. It seems to have disappeared after changing SQLAlchemy's pool class to NullPool: "engine_from_config(config, 'sqlalchemy.', poolclass=NullPool)" instead of "engine_from_config(config, 'sqlalchemy.')" in environment.py (don't forget to "from sqlalchemy.pool import NullPool").

cheers

[marcinkuzminski]

I can confirm this happens for my project (rhodecode) written in pylons also. I got feedback from users saying about the very same error. What is interesting is that in my app i use tread_pool with threadpool_workers = 5 and threadpool_max_requests = 10. Which i think it's close to what pyramid is using.

Posted solution above works in my project also.

[tmatth]

I'm using Python 2.6.6 on Ubuntu 10.10 in a virtual env with SQLLite, sqlalchemy version 0.6.7, same issue. I reload a page or switch pages and I'm hit with that error. Also using SQLLite. Disabling connection pooling as described solved it for me. More info here:
http://www.sqlalchemy.org/docs/core/pooling.html

[mmerickel]

Should we update the tutorials and paster templates to use poolclass=NullPool?

It looks like sqlalchemy 0.7 (beta) fixes this issue already, and I'd be a little worried that if we fixed the tutorials people may use that code as a base with other engines, which would be bad.

[mcdonc]

Maybe we just wait for 0.7 final and pin when it comes out?

[mcdonc]

Wait, I didn't mean to close that.

[marcinkuzminski]

I (in my pylons project) overwrote get_engine_form config to force usage of NullPool for sqlite
example => https://hg.rhodecode.org/rhodecode/files/c33842b1bd384881f4da8c45d54e2415bd286c7c/rhodecode/lib/__init__.py#L161-175

[rockyburt]

Just a related update... I've been seeing the same error with khufu_sqlalchemy-based apps using sqlite. I put this fix in place (ugly I know).
khufuproject/khufu_sqlalchemy@d05dbef

[skrul]

This seems obvious now, but I ran into a problem with my functional tests failing after applying this fix. Turns out NullPool isn't usable with an in-memory sqlite database since it closes/re-opens connections, which causes the in-memory database to be reset. Whoops!

[mmerickel]

To clarify on @skrul's issues, SQLAlchemy 0.7 will use SingletonThreadPool if the database is ":memory:", otherwise it will default to the NullPool.

Also 0.7 is released now, so upgrade to it.

[mcdonc]

This issue is closed with the release of SQLAlchemy 0.7.