(sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back

[Erol Merdanović]

Hello

I'm using latest sqlalchemy (this was also occurring with 1.1.x branch) with flask and flask-sqlalchemy. For some reason I'm randomly getting this error. 

1. Checking the group there were similar problems but some mentioned that problem is with database going away

2. Others mentioned that session is not correctly started/closed. As mentioned, I'm using flask-sqlalchemy. 

Is anyone else experiencing same problem? Any tips how to approach this? Is this flask-sqlalchemy problem? 

[Mike Bayer]

SQLAlchemy generates this error for a good reason, however it is less
typical when using the ORM Session. Would need at least a stack
trace to have any idea what is happening for you.

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Erol Merdanović]

Mike, thank you for the reply. Here is the stack trace

AttributeError: 'Connection' object has no attribute '_Connection__connection'

  File "sqlalchemy/engine/base.py", line 1112, in _execute_context

    conn = self.__connection

InvalidRequestError: Can't reconnect until invalid transaction is rolled back

  File "sqlalchemy/engine/base.py", line 1114, in _execute_context

    conn = self._revalidate_connection()

  File "sqlalchemy/engine/base.py", line 424, in _revalidate_connection

    "Can't reconnect until invalid "

StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: 'SELECT users.id AS users_id, users.email AS users_email, users.password AS users_password, users.first_name AS users_first_name, users.last_name AS users_last_name, users.status AS users_status, users.language_code AS users_language_code, users.must_change_password_on_login AS users_must_change_password_on_login, users.must_change_information_on_login AS users_must_change_information_on_login, users.was_sent_welcome_email AS users_was_sent_welcome_email, users.created_at AS users_created_at, users.creation_type AS users_creation_type, users.last_login_at AS users_last_login_at, users.login_key AS users_login_key, users.api_key AS users_api_key, users.settings AS users_settings, users.role AS users_role, users.search_vector AS users_search_vector, partner_users.user_id AS partner_users_user_id, partner_users.partner_id AS partner_users_partner_id, store_users.user_id AS store_users_user_id, store_users.dashboard AS store_users_dashboard \nFROM users LEFT OUTER JOIN partner_users ON users.id = partner_users.user_id LEFT OUTER JOIN store_users ON users.id = store_users.user_id \nWHERE users.api_key = %(api_key_1)s \n LIMIT %(param_1)s'] [parameters: [{}]]

  File "flask/app.py", line 1982, in wsgi_app

    response = self.full_dispatch_request()

  File "flask/app.py", line 1614, in full_dispatch_request

    rv = self.handle_user_exception(e)

  File "flask_cors/extension.py", line 161, in wrapped_function

    return cors_after_request(app.make_response(f(*args, **kwargs)))

  File "flask/app.py", line 1517, in handle_user_exception

    reraise(exc_type, exc_value, tb)

  File "flask/_compat.py", line 33, in reraise

    raise value

  File "flask/app.py", line 1612, in full_dispatch_request

    rv = self.dispatch_request()

  File "flask/app.py", line 1598, in dispatch_request

    return self.view_functions[rule.endpoint](**req.view_args)

  File "flask_login/utils.py", line 226, in decorated_view

    elif not current_user.is_authenticated:

  File "werkzeug/local.py", line 347, in __getattr__

    return getattr(self._get_current_object(), name)

  File "werkzeug/local.py", line 306, in _get_current_object

    return self.__local()

  File "flask_login/utils.py", line 26, in <lambda>

    current_user = LocalProxy(lambda: _get_user())

  File "flask_login/utils.py", line 302, in _get_user

    current_app.login_manager._load_user()

  File "flask_login/login_manager.py", line 313, in _load_user

    return self._load_from_request(request)

  File "flask_login/login_manager.py", line 370, in _load_from_request

    user = self.request_callback(request)

  File "core/app.py", line 81, in load_user_from_request

    api_key=api_key

  File "sqlalchemy/orm/query.py", line 2755, in first

    ret = list(self[0:1])

  File "sqlalchemy/orm/query.py", line 2547, in __getitem__

    return list(res)

  File "sqlalchemy/orm/query.py", line 2855, in __iter__

    return self._execute_and_instances(context)

  File "sqlalchemy/orm/query.py", line 2878, in _execute_and_instances

    result = conn.execute(querycontext.statement, self._params)

  File "sqlalchemy/engine/base.py", line 945, in execute

    return meth(self, multiparams, params)

  File "sqlalchemy/sql/elements.py", line 263, in _execute_on_connection

    return connection._execute_clauseelement(self, multiparams, params)

  File "sqlalchemy/engine/base.py", line 1053, in _execute_clauseelement

    compiled_sql, distilled_params

  File "sqlalchemy/engine/base.py", line 1121, in _execute_context

    None, None)

  File "sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception

    exc_info

  File "sqlalchemy/util/compat.py", line 203, in raise_from_cause

    reraise(type(exception), exception, tb=exc_tb, cause=cause)

  File "sqlalchemy/util/compat.py", line 186, in reraise

    raise value.with_traceback(tb)

  File "sqlalchemy/engine/base.py", line 1114, in _execute_context

    conn = self._revalidate_connection()

  File "sqlalchemy/engine/base.py", line 424, in _revalidate_connection

    "Can't reconnect until invalid "

[Mike Bayer]

On Wed, Mar 7, 2018 at 2:49 PM, Erol Merdanović <zase...@gmail.com> wrote:
> Mike, thank you for the reply. Here is the stack trace

your application has encountered an error which caused it to
invalidate the connection, such as the database was restarted or the
connection was lost. Then it failed to handle the error correctly and
continued to use the same invalidated connection, specifically within
the the test "current_user.is_authenticated:". If perhaps your
application is attempting to display an error page, which itself is
attempting to interrogate the database in the same request, that would
cause this problem. To test this you'd want to look in your logs for
errors that occur prior to this one as well as simplify your error
handling logic such that it shows a plain static page with no database
elements within it.

[Erol Merdanović]

I don't think the problem is with database, because sometimes I have to restart the application (flask running as uwsgi) to make it work.

There is no error page but default flask error page (it's running in debug = False mode / production mode, so error is just 500 response with no content). I checked the errors and there are not errors prior to this error. But I will check request logs.

I was thinking to play with configuration settings http://flask-sqlalchemy.pocoo.org/2.3/config/#configuration-keys, maybe SQLALCHEMY_POOL_RECYCLE would be good place to start? But I'm not sure if this would solve the problem. What do you think?

[Mike Bayer]

On Wed, Mar 7, 2018 at 5:46 PM, Erol Merdanović <zase...@gmail.com> wrote:
> I don't think the problem is with database, because sometimes I have to
> restart the application (flask running as uwsgi) to make it work.

that is exactly the symptom that your database has been restarted or
that the network connections between the application and database have
been severed.

>
> There is no error page but default flask error page (it's running in debug =
> False mode / production mode, so error is just 500 response with no
> content). I checked the errors and there are not errors prior to this error.
> But I will check request logs.
>
> I was thinking to play with configuration settings
> http://flask-sqlalchemy.pocoo.org/2.3/config/#configuration-keys, maybe
> SQLALCHEMY_POOL_RECYCLE would be good place to start? But I'm not sure if
> this would solve the problem. What do you think?

you can't solve this problem without understanding what it is. this
error message is only generated after a previous error occurs. you
need to see that previous error.