"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]

On Dec 27, 2011, at 11:58 PM, Josh Ha-Nyung Chung wrote:

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

[Michael Bayer]

test cases and stack traces would be a start

--
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 post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
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

[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

[Piotr Deszyński]

Ok, got it working.

Sorry for the problem.

[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:

alembic	0.6.0
amqp	1.0.13
anyjson	0.3.3
argparse	1.2.1
Beaker	1.6.4
beautifulsoup4	4.2.1
billiard	2.7.3.31
celery	3.0.21
Chameleon	2.11
cornice	0.14
coverage	3.6
distribute	0.7.3
flup	1.0.3.dev-20110405
kombu	2.5.12
Mako	0.8.1
MarkupSafe	0.18
meld3	0.6.10
mock	1.0.1
MySQL-python	1.2.4
nose	1.3.0
openpyxl	1.6.2
paramiko	1.11.0
PasteDeploy	1.5.0
pip	1.1
pycrypto	2.6
Pygments	1.6
pyramid	1.4.3
pyramid-beaker	0.8
pyramid-debugtoolbar	1.0.6
pyramid-mailer	0.13
pyramid-tm	0.7
Python	2.7
python-dateutil	2.1
pytz	2013b
repoze.lru	0.6
repoze.sendmail	4.1
selenium	2.33.0
Server	0.0.6
setuptools	0.9.8
simplejson	3.3.0
six	1.3.0
SQLAlchemy	0.8.2
supervisor	3.0
transaction	1.4.1
translationstring	1.1
venusian	1.0a8
waitress	0.8.5
WebOb	1.2.3
WebTest	2.0.6
wsgiref	0.1.2
zope.deprecation	4.0.2
zope.interface	4.0.5
zope.sqlalchemy	0.7.2