aiohttp+sqlalchemy: Can't reconnect until invalid transaction is rolled back

Question

I'm using aiohttp and sqlalchemy, and I've created a Singleton that helps me to connect when I'm needed a instance of SQLAlchemy (code follows). Unfortunately, every once in awhile I get the following error (which I "solve" by restarting the server):

Dec 11 09:35:29 ip-xxx-xxx-xxx-xxx gunicorn[16513]: sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back [SQL: '... \nFROM ...\nWHERE ... = %(username_1)s \n LIMIT %(param_1)s'] [parameters: [{}]]```

Is there any way to fix the current code? Thanks :)

CONNECTION_DETAILS = {
    'driver': 'pymysql',
    'dialect': 'mysql',
    'host': os.environ.get('HOST'),
    'port': 3306,
    'user': 'master',
    'password': os.environ.get('PASSWORD'),
    'database': 'ourdb',
    'charset': 'utf8'
}

_instance = None

def __new__(cls, *args, **kwargs):
    if not cls._instance:
        con_str = '{dialect}+{driver}://{user}:{password}@' \
                  '{host}:{port}/{database}?charset={charset}'\
            .format(**cls.CONNECTION_DETAILS)
        try:
            engine = sqlalchemy.create_engine(con_str)

            Session = scoped_session(sessionmaker(bind=engine))
            session = Session()  # Create the ORM handle
        except sqlalchemy.exc.OperationalError:
            logger.exception('Establishing database connection error.')

        cls._instance = super().__new__(cls)
        logger.debug("Returning database's session.")
        cls._instance.session = session

        # Initializing tables
        cls._instance.Users = Users
        cls._instance.Services = Services
        cls._instance.APIKeys = APIKeys

    return cls._instance

Solution 1

This would be a fairly late answer. This is what happens: While using the session, a sqlalchemy Error is raised (anything which would also throw an error when be used as pure SQL: syntax errors, unique constraints, key collisions etc.).

You would have to find this error, wrap it into a try/except-block and perform a session.rollback().

After this you can reinstate your session.

Solution 2

Got here from a flask_sqlalchemy use case. The fix is to run db.session.rollback() just once and it clears. It is similar to the accepted answer of executing rollback

Comments

[Juggernaut]

I got no clue about your question, but why do you use aiohttp with sqlalchemy? a non-async orm with a async framework?

[univerio]

I imagine this is due to multiple aiohttp requests running in the same thread, so the Session is shared among multiple tasks due to the scopefunc of the scoped_session. See this question.

[Evan]

I had this issue with straight SQLAlchemy and MS SQL Server. Running session.rollback() in an empty Jupyter cell fixed things.

[Imran]

@knitti wouldnt this imply that one needs to add session.rollback on every possible CRUD operation then cause they can throw the same error?

[janpeterka]

Where do you call it?