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).
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
