"InvalidRequestError: Can't reconnect until invalid transaction is rolled back" error during "SELECT" query

[Josh Ha-Nyung Chung]

I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy
0.7.4 and occasionally encountered the following error.

Traceback (most recent call last):
File "/usr/local/lib/python2.7/site-packages/pyramid/router.py", line
176, in __call__
response = self.handle_request(request)
File
"/usr/local/lib/python2.7/site-packages/pyramid_debugtoolbar-0.9.7-py2.7.egg/pyramid_debugtoolbar/toolbar.py",
line 103, in toolbar_tween return handler(request)
File "/usr/local/lib/python2.7/site-packages/pyramid/tweens.py", line 17,
in excview_tween
response = handler(request)
File
"/usr/local/lib/python2.7/site-packages/pyramid_tm-0.3-py2.7.egg/pyramid_tm/__init__.py",
line 61, in tm_tween
response = handler(request) File
"/usr/local/lib/python2.7/site-packages/pyramid/router.py", line 153, in
handle_request
response = view_callable(context, request)
File "/usr/local/lib/python2.7/site-packages/pyramid/config/views.py",
line 187, in _secured_view
return view(context, request)
File "/usr/local/lib/python2.7/site-packages/pyramid/config/views.py",
line 320, in viewresult_to_response
result = view(context, request)
File "/usr/local/lib/python2.7/site-packages/pyramid/config/views.py",
line 403, in _requestonly_view
response = view(request)
File "/home/ec2-user/work/DP-MGMT/dp_mgmt/views/users.py", line 57, in
users
for user, snsuser in query.all():
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 1947,
in all
return list(self)
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2057,
in __iter__
return self._execute_and_instances(context)
File "build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py", line 2072,
in _execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1405,
in execute
params)
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1538,
in _execute_clauseelement
compiled_sql, distilled_params
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1605,
in _execute_context
None, None)
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1599,
in _execute_context
conn = self._revalidate_connection()
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1018,
in _revalidate_connection
"Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled back
(original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id,
dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email,
dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user,
dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id =
dp_sns_user.user_id AND dp_user.signup_date >= %s AND dp_user.signup_date <
%s ORDER BY dp_user.signup_date DESC' [immutabledict({})]

All queries what my web app is doing is SELECT. So I don't think I need to
explicitly call session.commit() at all. I can't understand why "invalid
transaction" ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with
pool_recycle of 3600.

after I restarted apache, which run my web app through wsgi, it starts
working again.

[Michael Bayer]

I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy 0.7.4 and occasionally encountered the following error.
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1599, in _execute_context
conn = self._revalidate_connection()
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1018, in _revalidate_connection
"Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id, dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email, dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user, dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id = dp_sns_user.user_id AND dp_user.signup_date >= %s AND dp_user.signup_date < %s ORDER BY dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need to explicitly call session.commit() at all. I can't understand why "invalid transaction" ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts working again.

this can only happen if an error is emitted from a query or other SQL operation inside of a transaction, and the connection continues to be reused subsequent to that error condition without any attendance being given to the invalid transaction.

The "restarting of apache" step as the only solution suggests you're sharing a single transaction between multiple requests, and the error was emitted in a previous request. You'd need to ensure that the Session is completely closed out at the end of each request, or at least rollback() is called, so that any remaining transactional state is released. Pyramid suggests integrating with the ZopeTransactionExtension which I believe should handle this (you should check on the Pyramid list).

[Piotr Deszyński]

Hello,

Is there a way to use ZopeTransactionExtension using ShardedSession? I just
cannot figure it out. When I use it then I'm getting exception during any
query:

unbound method after_begin() must be called with ZopeTransactionExtension
instance as first argument (got SessionMaker instance instead)

Best regards

W dniu ¶roda, 28 grudnia 2011 18:18:46 UTC+1 u¿ytkownik Michael Bayer

Post by Josh Ha-Nyung Chung
I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy
0.7.4 and occasionally encountered the following error.
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1599, in _execute_context
conn = self._revalidate_connection()
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1018, in _revalidate_connection
"Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled back
(original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id,
dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email,
dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user,
dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id =
dp_sns_user.user_id AND dp_user.signup_date >= %s AND dp_user.signup_date <
%s ORDER BY dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need to
explicitly call session.commit() at all. I can't understand why "invalid
transaction" ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts working again.
this can only happen if an error is emitted from a query or other SQL
operation inside of a transaction, and the connection continues to be
reused subsequent to that error condition without any attendance being
given to the invalid transaction.
The "restarting of apache" step as the only solution suggests you're
sharing a single transaction between multiple requests, and the error was
emitted in a previous request. You'd need to ensure that the Session is
completely closed out at the end of each request, or at least rollback() is
called, so that any remaining transactional state is released. Pyramid
suggests integrating with the ZopeTransactionExtension which I believe
should handle this (you should check on the Pyramid list).

[Michael Bayer]

test cases and stack traces would be a start

Post by Piotr Deszyński
Hello,
unbound method after_begin() must be called with ZopeTransactionExtension instance as first argument (got SessionMaker instance instead)
Best regards

I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy 0.7.4 and occasionally encountered the following error.
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1599, in _execute_context
conn = self._revalidate_connection()
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 1018, in _revalidate_connection
"Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled back (original cause: InvalidRequestError: Can't reconnect until invalid transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id, dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email, dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user, dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id = dp_sns_user.user_id AND dp_user.signup_date >= %s AND dp_user.signup_date < %s ORDER BY dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need to explicitly call session.commit() at all. I can't understand why "invalid transaction" ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts working again.

this can only happen if an error is emitted from a query or other SQL operation inside of a transaction, and the connection continues to be reused subsequent to that error condition without any attendance being given to the invalid transaction.
The "restarting of apache" step as the only solution suggests you're sharing a single transaction between multiple requests, and the error was emitted in a previous request. You'd need to ensure that the Session is completely closed out at the end of each request, or at least rollback() is called, so that any remaining transactional state is released. Pyramid suggests integrating with the ZopeTransactionExtension which I believe should handle this (you should check on the Pyramid list).
--
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/-/FHYesCKZhD4J.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Piotr Deszyński]

If it's necessary I will provide both, the question before that is:
Should ZopeTransactionExtension work with ShardedSession? Because if it
wasn't designed for that, then there's no need to write test cases.

Best regards

W dniu wtorek, 4 grudnia 2012 16:31:16 UTC+1 uÅŒytkownik Michael Bayer

Post by Michael Bayer
test cases and stack traces would be a start
Hello,
Is there a way to use ZopeTransactionExtension using ShardedSession? I
just cannot figure it out. When I use it then I'm getting exception during
unbound method after_begin() must be called with ZopeTransactionExtension
instance as first argument (got SessionMaker instance instead)
Best regards
W dniu środa, 28 grudnia 2011 18:18:46 UTC+1 uÅŒytkownik Michael Bayer

Post by Josh Ha-Nyung Chung
I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy
0.7.4 and occasionally encountered the following error.
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1599, in _execute_context
conn = self._revalidate_connection()
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1018, in _revalidate_connection
"Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled back
(original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id,
dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email,
dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user,
dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id =
dp_sns_user.user_id AND dp_user.signup_date >= %s AND dp_user.signup_date <
%s ORDER BY dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need
to explicitly call session.commit() at all. I can't understand why "invalid
transaction" ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts working again.
this can only happen if an error is emitted from a query or other SQL
operation inside of a transaction, and the connection continues to be
reused subsequent to that error condition without any attendance being
given to the invalid transaction.
The "restarting of apache" step as the only solution suggests you're
sharing a single transaction between multiple requests, and the error was
emitted in a previous request. You'd need to ensure that the Session is
completely closed out at the end of each request, or at least rollback() is
called, so that any remaining transactional state is released. Pyramid
suggests integrating with the ZopeTransactionExtension which I believe
should handle this (you should check on the Pyramid list).

--
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/-/FHYesCKZhD4J.
.
To unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.

[Piotr Deszyński]

A simple test case can be found here https://gist.github.com/4214092

Expected result:
Query returns a list of results from database

Current behaviour:
Query throws an exception:
https://gist.github.com/4214131

W dniu ¶roda, 5 grudnia 2012 07:54:48 UTC+1 u¿ytkownik Piotr Deszyñski

Post by Piotr Deszyński
Should ZopeTransactionExtension work with ShardedSession? Because if it
wasn't designed for that, then there's no need to write test cases.
Best regards
W dniu wtorek, 4 grudnia 2012 16:31:16 UTC+1 u¿ytkownik Michael Bayer

Post by Michael Bayer
test cases and stack traces would be a start
Hello,
Is there a way to use ZopeTransactionExtension using ShardedSession? I
just cannot figure it out. When I use it then I'm getting exception during
unbound method after_begin() must be called with ZopeTransactionExtension
instance as first argument (got SessionMaker instance instead)
Best regards

Post by Josh Ha-Nyung Chung
I've made web application using Pyramid 1.2.5 + Python 2.7.1 +
SQLAlchemy 0.7.4 and occasionally encountered the following error.
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1599, in _execute_context
conn = self._revalidate_connection()
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1018, in _revalidate_connection
"Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled
back (original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id,
dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email,
dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user,
dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id =
dp_sns_user.user_id AND dp_user.signup_date >= %s AND dp_user.signup_date <
%s ORDER BY dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need
to explicitly call session.commit() at all. I can't understand why "invalid
transaction" ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with
pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts working again.
this can only happen if an error is emitted from a query or other SQL
operation inside of a transaction, and the connection continues to be
reused subsequent to that error condition without any attendance being
given to the invalid transaction.
The "restarting of apache" step as the only solution suggests you're
sharing a single transaction between multiple requests, and the error was
emitted in a previous request. You'd need to ensure that the Session is
completely closed out at the end of each request, or at least rollback() is
called, so that any remaining transactional state is released. Pyramid
suggests integrating with the ZopeTransactionExtension which I believe
should handle this (you should check on the Pyramid list).

--
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/-/FHYesCKZhD4J.
To unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.

[Piotr Deszyński]

Ok, got it working.

Sorry for the problem.

W dniu wtorek, 4 grudnia 2012 16:31:16 UTC+1 u¿ytkownik Michael Bayer

Post by Michael Bayer
test cases and stack traces would be a start
Hello,
Is there a way to use ZopeTransactionExtension using ShardedSession? I
just cannot figure it out. When I use it then I'm getting exception during
unbound method after_begin() must be called with ZopeTransactionExtension
instance as first argument (got SessionMaker instance instead)
Best regards

Post by Josh Ha-Nyung Chung
I've made web application using Pyramid 1.2.5 + Python 2.7.1 + SQLAlchemy
0.7.4 and occasionally encountered the following error.
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1599, in _execute_context
conn = self._revalidate_connection()
File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
1018, in _revalidate_connection
"Can't reconnect until invalid "
StatementError: Can't reconnect until invalid transaction is rolled back
(original cause: InvalidRequestError: Can't reconnect until invalid
transaction is rolled back) 'SELECT dp_user.user_id AS dp_user_user_id,
dp_user.email AS dp_user_email, dp_user.new_email AS dp_user_new_email,
dp_sns_user.`SNS_name` AS `dp_sns_user_SNS_name` \\nFROM dp_user,
dp_sns_user \\nWHERE dp_user.status = %s AND dp_user.user_id =
dp_sns_user.user_id AND dp_user.signup_date >= %s AND dp_user.signup_date <
%s ORDER BY dp_user.signup_date DESC' [immutabledict({})]
All queries what my web app is doing is SELECT. So I don't think I need
to explicitly call session.commit() at all. I can't understand why "invalid
transaction" ever occurred.
mysqld's wait_timeout is 28800 and I create sqlalchemy engine with pool_recycle of 3600.
after I restarted apache, which run my web app through wsgi, it starts working again.
this can only happen if an error is emitted from a query or other SQL
operation inside of a transaction, and the connection continues to be
reused subsequent to that error condition without any attendance being
given to the invalid transaction.
The "restarting of apache" step as the only solution suggests you're
sharing a single transaction between multiple requests, and the error was
emitted in a previous request. You'd need to ensure that the Session is
completely closed out at the end of each request, or at least rollback() is
called, so that any remaining transactional state is released. Pyramid
suggests integrating with the ZopeTransactionExtension which I believe
should handle this (you should check on the Pyramid list).

--
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/-/FHYesCKZhD4J.
.
To unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.

[Jim Carroll]

I'm having the same issue with a Pyramid web app.
Did anyone find a solution?

I've tried Flup and Waitress as the containers for the pyramid app, and
I've tried Lighttpd and Nginx in front... no difference. My latest
exceptions look like:

Could not convert return value of the view callable function cornice.pyramidhook.handle_exceptions into a response object. The value returned was AttributeError("'Connection' object has no attribute '_Connection__connection'",).


and then on the next try


ValueError: Could not convert return value of the view callable function cornice.pyramidhook.handle_exceptions into a response object. The value returned was AssertionError('Transaction must be committed using the transaction manager',).



I'm using the latest of everything:

alembic0.6.0amqp1.0.13anyjson0.3.3argparse1.2.1Beaker1.6.4beautifulsoup4
4.2.1billiard2.7.3.31celery3.0.21Chameleon2.11cornice0.14coverage3.6
distribute0.7.3flup1.0.3.dev-20110405kombu2.5.12Mako0.8.1MarkupSafe0.18meld3
0.6.10mock1.0.1MySQL-python1.2.4nose1.3.0openpyxl1.6.2paramiko1.11.0
PasteDeploy1.5.0pip1.1pycrypto2.6Pygments1.6pyramid1.4.3pyramid-beaker0.8
pyramid-debugtoolbar1.0.6pyramid-mailer0.13pyramid-tm0.7Python2.7
python-dateutil2.1pytz2013brepoze.lru0.6repoze.sendmail4.1selenium2.33.0
Server0.0.6setuptools0.9.8simplejson3.3.0six1.3.0SQLAlchemy0.8.2supervisor
3.0transaction1.4.1translationstring1.1venusian1.0a8waitress0.8.5WebOb1.2.3
WebTest2.0.6wsgiref0.1.2zope.deprecation4.0.2zope.interface4.0.5
zope.sqlalchemy0.7.2