SQLAlchemySession(create_tables=True) cannot create tables on MySQL due to unbounded String columns

[rioyu123]

The session guide lists MySQL as a supported database, and the API reference includes mysql+aiomysql:// and documents create_tables=True for development/testing. However, automatic table creation fails against a fresh MySQL database.

Reproduction

Reproduced with MySQL Community Server 8.0.46, Python 3.12.3 on Linux, openai-agents 0.22.0, SQLAlchemy 2.0.52, and aiomysql 0.3.2. The same failure occurs on main at 89c02c828ee8510fe9a84ee6675608193aa13b02.

Install the dependencies, then set MYSQL_URL to a mysql+aiomysql:// connection URL for an empty test database with permission to create tables. No model call or OpenAI API key is needed.

pip install 'openai-agents[sqlalchemy]==0.22.0' 'SQLAlchemy==2.0.52' 'aiomysql==0.3.2' cryptography

import asyncio
import os

from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine

from agents.extensions.memory import SQLAlchemySession


async def main():
    engine = create_async_engine(os.environ["MYSQL_URL"])
    try:
        async with engine.connect() as conn:
            print("MySQL:", await conn.scalar(text("SELECT VERSION()")))

        session = SQLAlchemySession(
            "mysql-create-tables",
            engine=engine,
            create_tables=True,
        )
        await session.add_items([{"role": "user", "content": "hello"}])
    finally:
        await engine.dispose()


asyncio.run(main())

Actual result

The connection succeeds and reports MySQL 8.0.46. On the first add_items() call, automatic schema initialization fails with:

sqlalchemy.exc.CompileError: (in table 'agent_sessions', column 'session_id'): VARCHAR requires a length on dialect mysql

This happens while SQLAlchemy compiles the first CREATE TABLE, before any CREATE statement reaches MySQL. No tables are created.

Both session_id columns in the current implementation use String without a length.

Expected behavior / scope clarification

Is SQLAlchemySession(create_tables=True) intended to support MySQL?

If so, I would expect the example above to create the required tables and store the message, and I'd be happy to submit a focused fix with MySQL regression coverage.

If automatic schema creation is intentionally unsupported on MySQL, I can instead submit a documentation clarification.

One possible implementation direction is a MySQL-specific bounded VARCHAR via with_variant(), which would preserve the existing PostgreSQL/SQLite types. I have not chosen a length here because doing so would introduce a MySQL-specific maximum session ID length that should be agreed first.

This report is limited to automatic table creation on a fresh database; it does not assess create_tables=False with a manually provisioned schema.

[linhongyu510]

I reproduced this on current main and have a focused fix ready. The patch uses a dialect-specific bounded VARCHAR(190) for session_id on MySQL and MariaDB only, while preserving the existing unbounded VARCHAR on PostgreSQL and SQLite. The length keeps the (session_id, created_at) index within the traditional 767-byte InnoDB key limit under utf8mb4.

Regression coverage runs MetaData.create_all() against SQLAlchemy mock engines for both MySQL and MariaDB, checking both tables, the foreign key with ON DELETE CASCADE, and the full (session_id, created_at) index. The full repository format, lint, typecheck, and test gates pass locally. I will open the PR shortly.

[rioyu123]

Thanks for picking this up and opening #4741. Keeping the bounded type specific to MySQL/MariaDB looks consistent with the compatibility concern in the original report.

One thing that may still be worth maintainer confirmation is whether the 190-character session ID limit on those dialects is the desired compatibility tradeoff.

Since the original report was reproduced against a fresh MySQL 8 database, it may also be worth running the same create-and-write repro against this branch once. The new mock-engine tests cover the DDL compilation path well, while a real database run would confirm that schema creation and the first write succeed end to end.