0
<https://stackoverflow.com/posts/76521666/timeline>
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 SQLAlchemy uses the same session for all request because session
is created with same way we did in SQLAlchemy instance. Related code in
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 SQLAlchemy did it?
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/24b36433-5ff1-483b-a304-49bb451564dcn%40googlegroups.com.
