"Can't reconnect until invalid transaction is rolled back"

[HeliosH]

the issue still open.

use these way to create manager
manager_restless = APIManager(app, session=data_dict_session)

use blueprint to create api
blueprint_table = manager_restless.create_api_blueprint(***)
app.register_blueprint(blueprint_table)

add postprocessors to close session

def update_result_format(result, search_params=None, **kw):
session.remove()

but it still throw the Exception:
unexpected internal error occurs: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: u'SELECT count(*) AS count_1 \nFROM dw_table \nWHERE dw_table.id != %s'] [parameters: [{}]]

version: 0.17.0

[bac]

This issue is affecting my project. Anyone have a work-around?

[johaven]

+1

[UberMeatShield]

bac and I figured out a way to get some of this working cleanly for the 0.17.x release but you have to add a preprocessor step, and we had to modify the session code a bit. Hopefully for the 1.0 branch we can work on fixing some of this as well. The root of the problem is that a single "session" reference can go stale, and even if your app is using pooling or cleaning up the session it will have failed that 1 request (randomly or based on MySQL connection kills). That means the .session on all your APIs will go bad without some way of resetting it.

https://github.com/UberMeatShield/flask-restless/tree/0.17.1 . I am trying to figure out how to contribute this back but the 0.17 tag is super stale, and I cannot currently figure out all the versions of python / requirements that will get the tests to pass. Working on that, but hopefully having a branch that can work helps somebody if I cannot get a MR for 0.17 in a sensible state.

# This kind of code should be added as a pre-processor, or added around your creation of a session
def set_manager(api_manager):
     global API_MANAGER   # Reference to your APIManager instance
     API_MANAGER = api_manager

 def get_manager():
     return API_MANAGER

def check_session(**kwargs):
     """ Call this in a preprocessor function after you check auth to make sure you session is _actually_ working """
     manager = get_manager()
     if manager:
         try:
             session = Session()
             session.rollback()
             session.execute("SELECT 1")
         except Exception as error:
             current_app.logger.error("check session: session error: %s", error)
             try:
                 session.remove()
             except Exception as remove_err:
                 current_app.logger.error("Failed to remove session")
                 current_app.logger.exception(remove_err)
         manager.reset_session(Session())