Summary

Alembic's --autogenerate feature fails to detect when new values are added to, removed from, or reordered in MySQL native ENUM columns. It only detects superficial changes like comments but completely misses the ENUM value modifications, which are critical schema changes.

Environment

• Alembic Version: 1.13.2+ (affects all versions)
• SQLAlchemy Version: 2.0.x
• Database: MySQL 8.0+, MariaDB
• Python: 3.10+
• ENUM Configuration: native_enum=True (MySQL native ENUM)

Reproduction

1. Initial Model

from sqlalchemy import Column, Enum, Integer
from sqlalchemy.orm import declarative_base

Base = declarative_base()

class MyTable(Base):
    __tablename__ = "test_table"
    id = Column(Integer, primary_key=True)
    status = Column(Enum("PENDING", "OPEN", "CLOSED", native_enum=True))

2. Initial Migration (Applied)

alembic revision --autogenerate -m "create table"
alembic upgrade head

3. Update Model (Add ENUM Value)

# Add "CANCELLED" to the enum
status = Column(Enum("PENDING", "OPEN", "CLOSED", "CANCELLED", native_enum=True))

4. Generate Migration

alembic revision --autogenerate -m "add cancelled status"

Expected Behavior

Alembic should generate:

def upgrade():
    op.execute(
        "ALTER TABLE test_table MODIFY COLUMN status "
        "ENUM('PENDING', 'OPEN', 'CLOSED', 'CANCELLED') NOT NULL"
    )

Actual Behavior ❌

Alembic generates an empty migration or only detects comment changes:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    pass  # ← No ENUM modification detected!
    # ### end Alembic commands ###

Impact

Critical Issues:

1. Silent Schema Mismatch: Migration appears successful but database schema is not updated
2. Runtime Errors: Application crashes when trying to use new ENUM values:

   sqlalchemy.exc.DataError: (1265, "Data truncated for column 'status' at row 1")
   

3. Data Integrity Risk: No validation at migration time
4. Developer Confusion: Autogenerate is trusted to detect all changes

Root Cause

The DefaultImpl.compare_type() method in alembic/ddl/impl.py:

• Tokenizes column types into strings
• Compares type names but ignores ENUM values
• ENUM('A','B') vs ENUM('A','B','C') are considered identical

MySQL dialect (MySQLImpl in alembic/ddl/mysql.py) does not override compare_type() to handle ENUM-specific comparison.

Proposed Solution

Override compare_type() in MySQLImpl to:

1. Detect MySQL native ENUM types
2. Extract and compare actual enum values
3. Preserve order (critical for MySQL ENUM behavior)
4. Generate appropriate ALTER TABLE statements

Workaround (Manual)

Currently, developers must manually edit generated migrations:

def upgrade():
    op.execute(
        "ALTER TABLE test_table MODIFY COLUMN status "
        "ENUM('PENDING', 'OPEN', 'CLOSED', 'CANCELLED') NOT NULL"
    )

Additional Context

• Similar issue may exist for PostgreSQL native ENUMs
• CHECK constraint ENUMs (native_enum=False) might work correctly
• This affects all Alembic users using MySQL native ENUMs
• Bug has existed since early versions

Reproduction Repository

I can provide a minimal reproduction case if needed.

Severity: HIGH
Affected Users: All Alembic users with MySQL native ENUMs
Detection Difficulty: High (silent failure, discovered at runtime)