flask-security + SQLAlchemy: can't reconnect until invalid transaction is rolled back

Question

Edit:

the environment:

Python 3.8.3

Flask==1.1.2
Flask-Login==0.5.0
Flask-Security==3.0.0
SQLAlchemy==1.3.19

The database is MySQL 5.6.45, InnoDB tables.

I'm new at flask-security and SQLAlchemy. I refer to the document of flask-security and try this:

engine = create_engine(
    db_source,
    encoding="utf-8",
    pool_size=64,
    pool_timeout=300,
    pool_recycle=3,
    max_overflow=64
)
db_session = scoped_session(
    sessionmaker(
        autocommit=False,
        autoflush=False,
        bind=engine
    )
)
Base = declarative_base()
Base.query = db_session.query_property()

# some codes define the classes Role and User for flask-security

user_datastore = SQLAlchemySessionUserDatastore(
    db_session,
    User,
    Role
)
security = Security(app, user_datastore)

   .
   .
   .

@app.route("/test/", methods=["GET", "POST"])
@login_required
def api_test():
    # do something

At the beginning the flask-security build-in login page and the login process work. However, after some time the login process gets "Internal Server Error" and the logs show that "sqlalchemy.exc.StatementError: (sqlalchemy.exc.InvalidRequestError) Can't reconnect until invalid transaction is rolled back".

I search some similar questions and they say I need to do something like this for doing rollback:

try:
    # this is where the "work" happens!
    yield session
    # always commit changes!
    session.commit()
except:
    # if any kind of exception occurs, rollback transaction
    session.rollback()
    raise
finally:
    session.close()

However, all my codes do not call session.commit() anywhere, so I'm not sure where to place the session.rollback().

My questions are:

1. If session.rollback() helps, where to place it even if I do not call session.commit() in my codes?
2. I guess that is something inside flask-security? If yes, how to fix this?
3. Maybe the problem is that I use scoped_session incorrectly? Does not scoped_session do the rollback/remove itself?

Edit:

Thanks to @jwag, I'll try my best to describe the problem more precisely.

1. Firstly, I start the application on server. At this time, the login process works well.
2. However, after some time (which means 'this' application never connect to the DB again in at least one day, but actually I'm not sure when the problem starts occurring) the 'Can't reconnect until invalid transaction is rolled back' problem occurs.
3. There is another application connect to the same DB for other tables by mysql.connector. That application always works well even if my application encounters the 'Can't reconnect until invalid transaction is rolled back' problem. This problem lasts until I restart the httpd service.

My problem seems very similar to this problem:

SQLAlchemy: Can't reconnect until invalid transaction is rolled back

The only difference is I not only use SQLAlchemy but also use flask-security, so I even don't know where to rollback/close the session -- or even the problem is different but I have no idea.

I think the problem is not at the User/Role models (the error message does not point to these), but anyway I show them below.

class RolesUsers(Base):
    __tablename__ = "roles_users"
    __table_args__ = {
        "mysql_engine": "InnoDB",
        "mysql_charset": "utf8mb4"
    }
    id = Column(Integer(), primary_key=True)
    user_id = Column("user_id", Integer(), ForeignKey("user.id"))
    role_id = Column("role_id", Integer(), ForeignKey("role.id"))

class Role(Base, RoleMixin):
    __tablename__ = "role"
    __table_args__ = {
        "mysql_engine": "InnoDB",
        "mysql_charset": "utf8mb4"
    }
    id = Column(Integer(), primary_key=True)
    name = Column(String(20), unique=True)
    description = Column(String(128))

class User(Base, UserMixin):
    __tablename__ = "user"
    __table_args__ = {
        "mysql_engine": "InnoDB",
        "mysql_charset": "utf8mb4"
    }
    id = Column(Integer, primary_key=True)
    email = Column(String(128), unique=True)
    username = Column(String(128))
    password = Column(String(128))
    last_login_at = Column(DateTime())
    current_login_at = Column(DateTime())
    last_login_ip = Column(String(100))
    current_login_ip = Column(String(100))
    login_count = Column(Integer)
    active = Column(Boolean())
    roles = relationship(
        "Role",
        secondary="roles_users",
        backref=backref("users", lazy="dynamic")
    )

Answer 1

You don't say what version of Flask-Security you are using. IN general - the actual solution to this is to figure out WHY you are getting the Invalid Statement Error and fix that. Not try to rollback. Depending on your backend DB (are you using pycopg2?) I have found that if you attempt to SELECT using incorrect types (e.g. the column is an int and you pass a string) - it will throw this sort of error.

In your code snippet you define 'db_session' but you define the query to be 'backstage_db_session'??

Finally - when you say 'after some time' and 'you don't commit' - does that mean your app never reads or write to the DB?

And really finally - if you need more help - please include your User model code.