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

I got no clue about your question, but why do you use aiohttp with sqlalchemy? a non-async orm with a async framework? — Amin Etesamian, Dec 11 '16 at 14:14
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](http://stackoverflow.com/questions/34369164/correct-usage-of-sqlalchemy-scoped-session-with-python-asyncio). — univerio, Dec 12 '16 at 00:35

score 18 · Accepted Answer · answered Feb 22 '17 at 16:01

18

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.

answered Feb 22 '17 at 16:01

knitti

6,817
31
42

2

I had this issue with straight SQLAlchemy and MS SQL Server. Running `session.rollback()` in an empty Jupyter cell fixed things. – Evan Mar 12 '19 at 17:04
@knitti wouldnt this imply that one needs to add session.rollback on every possible CRUD operation then cause they can throw the same error? – Imran Oct 18 '19 at 23:24

score 5 · Answer 2 · answered Mar 06 '19 at 05:55

5

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

answered Mar 06 '19 at 05:55

Patrick Mutuku

1,095
15
13

2

Where do you call it? – janpeterka Aug 05 '21 at 18:54

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

2 Answers2