Description

Bug Description

When using the structured_model parameter in AgentScope agents (e.g., ReActAgent), which internally uses AsyncSQLAlchemyMemory to store messages with marks, a foreign key constraint error occurs:

sqlalchemy.exc.PendingRollbackError: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (pymysql.err.IntegrityError) (1452, 'Cannot add or update a child row: a foreign key constraint fails (`test`.`message_mark`, CONSTRAINT `1` FOREIGN KEY (`msg_id`) REFERENCES `message` (`id`) ON DELETE CASCADE)')
[SQL: INSERT INTO message_mark (msg_id, mark) VALUES (%s, %s)]
[parameters: ('user_913e9801-user_913e9801_itinerary-UqwuZZTuLozNPZrHM7fv3X', <_MemoryMark.HINT: 'hint'>)]

Root Cause Analysis

The issue is in AsyncSQLAlchemyMemory.add() method (line 444-489 in _sqlalchemy_memory.py):

# Add messages to message table
for i, m in enumerate(messages_to_add):
    message_record = self.MessageTable(...)
    self.session.add(message_record)  # ← Only marks as pending insert

# Create mark records if marks are provided
if marks:
    mark_records = [...]
    if mark_records:
        await self.session.run_sync(
            lambda session: session.bulk_insert_mappings(
                self.MessageMarkTable,
                mark_records,  # ← Executes SQL immediately
            ),
        )

await self.session.commit()  # ← Commits at the end

The Problem:

• session.add(message_record) only marks the message object for insertion (in-memory)
• bulk_insert_mappings() executes SQL immediately
• At this point, the message record doesn't exist in the database yet
• The foreign key constraint fails because msg_id references a non-existent message

Suggested Fix

Add await self.session.flush() before bulk_insert_mappings() to ensure messages are written to the database first