Error: Packet sequence number wrong for read replica

[hopyar]

We have an environment with the following libraries and versions;

Python 3.8
Flask 1.0.2
Flask-SQLAlchemy 2.3.0
SQLAlchemy 1.2.10
PyMySQL 1.0.2

Normally, we run on a single RDS Mysql but because of the high load we needed to add a read replica and we have following configurations;

SQLALCHEMY_DATABASE_URI = //DB URL
SQLALCHEMY_ECHO = False
SQLALCHEMY_TRACK_MODIFICATIONS = False
SQLALCHEMY_POOL_RECYCLE = 60
SQLALCHEMY_POOL_SIZE = 10
SQLALCHEMY_MAX_OVERFLOW = 30  # Default to 10
SQLALCHEMY_POOL_TIMEOUT = 60  # Default to 30
SQLALCHEMY_BINDS = {
    'read': Read Db URL
}

Before creating the read replica we would use db and session like;

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy(session_options={"expire_on_commit": False})
db.session.query(MyEntity).filter_by(id=self.id).all()

After creating the read replica we have added a read_session like;

read_engine = db.get_engine(bind='read')
session = db.create_scoped_session(options={'bind': read_engine, 'expire_on_commit': False})
read_session = session()

and started use it like;

read_session.query(MyEntity).filter_by(id=self.id).all()

but after this point, our application started to produce errors like Packet sequence number wrong - got x but expected y and Can't reconnect until invalid transaction is rolled back .

When I googled the error, I see that the reason of this error is multi-threading and it says every request should be handled in separate sessions but I wonder how it would work before creating the read replica. It seems Flask-SQLAlchemy uses the same session for all request because session is created with same way we did in Flasck-SQLAlchemy instance. Related code in Flask-SQLAlchemy =>

def __init__(self, app=None, use_native_unicode=True, session_options=None,
         metadata=None, query_class=BaseQuery, model_class=Model):

  self.use_native_unicode = use_native_unicode
  self.Query = query_class
  self.session = self.create_scoped_session(session_options) # here is how session created in SQL Alchemy
  self.Model = self.make_declarative_base(model_class, metadata)
  self._engine_lock = Lock()
  self.app = app
  _include_sqlalchemy(self, query_class)

  if app is not None:
      self.init_app(app)

I wonder, what we did wrong and how we can create a session to use every where like Flask-SQLAlchemy did it?

[davidism]

This is a question about Flask-SQLAlchemy, not Flask, please ask it in the appropriate place.

Note that the answer will be along the lines of "you can't set binds that way". Flask-SQLAlchemy has it's own way to manage binds, so you need to override the Session.get_bind method.