SQLAlchemy sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'chat_sessions' expected to update 1 row(s); 0 were matched

I get this SQLAlchemy error, inconsistently, but with a reoccurring pattern.

My application is a Flask app using flask_sqlalchemy for MySQL. My database initialization pattern leverages reflection to load the data model into the app, and then uses a Model object to add additional table object relationships.

The app has a chat function, and on a solution response from the Support User, it updates a ChatSession table-row/object, where each row is a record of the chat_session, with the support document name, and support topic.

The error only occurs when I am running my platform in the cloud (GCP managed could MySQL 8.0), but not when I run it locally (docker hosted MySQL 9.0).

It also doesn’t happen with every transaction to the table-row, but on the SECOND instance of the update, that is on the second response from the Support User. On the first solution response the transactions execute with no error.

This makes me think that the code itself is not wrong, but that there is something about the table which is changing (perhaps growing) as the conversation transacts.

What I have checked/tested

1. The chat_session object is still attached.
2. Consolidating commits into a single transaction at the end of all table updates.
3. Refreshing the chat_session object after all commits to the table.
4. Comparing the database row attributes with the current application object attributes.
5. Checking the size of the JSON entry to confirm it is not greater than 4MB
6. Checking the size of the row to confirm it is not greater than 65KB
7. There is no versioning of the row itself.
8. I am using session_id as the key, which is correct.

The code:

Note that the method commit_changes() is run immediately after update_chat_session_product_info().

     def update_chat_session_product_info(self, sources):
        # Update the chat session with the product information.
        current_app.logger.info(f"Updating chat session product info for chat session ID: {self.chat_session.session_id}.")


        # Debugging to check if the size of the json being stored to the table has exceeded allowed size.
        session_chat_memory_json = json.dumps(self.chat_session.session_chat_memory)
        data_length = len(session_chat_memory_json.encode('utf-8'))
        current_app.logger.info(f"Length of session_chat_memory JSON data: {data_length} bytes")

        # Debugging to check that the application Object and the database table are consistent.
        current_app.logger.info(f"chat_session attributes before commit: {self.chat_session.__dict__}")

        db_chat_session = ChatSession.query.get(self.chat_session.session_id)
        if db_chat_session:
            current_app.logger.info(f"Database chat_session attributes: {db_chat_session.__dict__}")
            # Compare attributes
        else:
            current_app.logger.error("Chat session not found in database.")

        # Debugging to check that chat_session is attached. 
        from sqlalchemy import inspect
        state = inspect(self.chat_session)
        current_app.logger.info(f"chat_session state before update: {state}")
        if state.detached:
            current_app.logger.warning("chat_session is detached; re-attaching.")
            self.chat_session = db.session.merge(self.chat_session)

        document_name = sources[0]["document_filename"]
        current_app.logger.info(f"Document name: {document_name}")
        expert_document = ExpertDocuments.query.filter_by(document_name=document_name).first()

        # Handle the case where the document name returned by the AI is not an exact match.
        if expert_document is None:
            current_app.logger.warning(f"Expert document not found for document name: {document_name}")
            expert_topic = "Not Found"
            expert_topic_id = None
        else:
            current_app.logger.info(f"Expert topic product: {expert_document.document_topic}")
            expert_topic = expert_document.document_topic
            expert_topic_id = expert_document.expert_topic_id

        # THE ERROR OCCURS AS A RESULT OF COMMITTING THESE TRANSACTIONS
        # Assign product information to the chat session.
        self.chat_session.session_issue_product_name = expert_topic
        self.chat_session.session_expert_topic_id = expert_topic_id

    def commit_changes(self):
        try:
            # Commit all changes to the database.
            db.session.commit()
            db.session.refresh(self.chat_session)
        except Exception as e:
            current_app.logger.error(f"Error during chat session record database commit: {e}")
            db.session.rollback()
            raise

Logs:

I am logging both sqlalchemy.engine and sqlalchemy.dialects

2024-10-27 22:56:39,156 INFO sqlalchemy.engine.Engine UPDATE chat_sessions SET session_issue_product_name=%(session_issue_product_name)s WHERE chat_sessions.session_id = %(chat_sessions_session_id)s 
2024-10-27 22:56:39,156 INFO: UPDATE chat_sessions SET session_issue_product_name=%(session_issue_product_name)s WHERE chat_sessions.session_id = %(chat_sessions_session_id)s [in /usr/local/lib/python3.11/site-packages/sqlalchemy/engine
2024-10-27 22:56:39,156 INFO sqlalchemy.engine.Engine [cached since 3.202e+05s ago] {'session_issue_product_name': AK 98 Dialysis Machine, 'chat_sessions_session_id': 191}
2024-10-27 22:56:39,156 INFO: [cached since 3.202e+05s ago] {'session_issue_product_name': AK 98 Dialysis Machine, 'chat_sessions_session_id': 191} [in /usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1846]
2024-10-27 22:56:39,160 INFO sqlalchemy.engine.Engine ROLLBACK
2024-10-27 22:56:39,160 INFO: ROLLBACK [in /usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2704]
2024-10-27 22:56:39,169 ERROR: Error during chat session record database commit: UPDATE statement on table 'chat_sessions' expected to update 1 row(s); 0 were matched. [in /app/maiday_app/chat/session_chat.py:250]
2024-10-27 22:56:39,173 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-10-27 22:56:39,173 INFO: BEGIN (implicit) [in /usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2701]
2024-10-27 22:56:39,173 INFO sqlalchemy.engine.Engine SELECT users.user_id AS users_user_id, users.user_login AS users_user_login, users.user_name AS users_user_name, users.user_password AS users_user_password, users.user_type AS users ...
FROM users
WHERE users.user_id = %(pk_1)s
2024-10-27 22:56:39,173 INFO: SELECT users.user_id AS users_user_id, users.user_login AS users_user_login, users.user_name AS users_user_name, users.user_password AS users_user_password, users.user_type AS users_user_type, users.user_e ...
FROM users
WHERE users.user_id = %(pk_1)s [in /usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1846]
2024-10-27 22:56:39,174 INFO sqlalchemy.engine.Engine [cached since 3.202e+05s ago] {'pk_1': 1}
2024-10-27 22:56:39,174 INFO: [cached since 3.202e+05s ago] {'pk_1': 1} [in /usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:1846]
2024-10-27 22:56:39,178 ERROR: admin Failed to respond to question due to UPDATE statement on table 'chat_sessions' expected to update 1 row(s); 0 were matched. [in /app/maiday_app/chat/routes.py:254]
Traceback (most recent call last): File "/app/maiday_app/chat/routes.py", line 222, in process_text chat_session_manager.commit_changes() File "/app/maiday_app/chat/session_chat.py", line 247, in commit_changes db.session.commit() File 
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 4448, in _flush
flush_context.execute()
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
rec.execute(self)
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
util.preloaded.orm_persistence.save_obj(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 85, in save_obj
_emit_update_statements(
File "/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/persistence.py", line 948, in _emit_update_statements
raise orm_exc.StaleDataError(
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'chat_sessions' expected to update 1 row(s); 0 were matched.
2024-10-27 22:56:39,180 INFO sqlalchemy.engine.Engine ROLLBACK
2024-10-27 22:56:39,180 INFO: ROLLBACK [in /usr/local/lib/python3.11/site-packages/sqlalchemy/engine/base.py:2704]
2024-10-27 22:56:39,187 ERROR: Database error: UPDATE statement on table 'chat_sessions' expected to update 1 row(s); 0 were matched. [in /app/maiday_app/__init__.py:126]

You need to sign in to view this answers