implement type detection change for MySQL ENUM

[zzzeek]

this refers to native enum. per #329 (comment) this is not being detected right now and as of #605 we do generally compare arguments.

[grubbins]

Looking at this further (for mysql), I realised that if the number of enum values is the same, then changes in the list of possible enum values are detected - but, if you add an enum item, they are not. That's because in alembic.ddl.impl in DefaultImpl._column_args_match:

        if (
            len(meta_params.args) == len(inspected_params.args)
            and meta_params.args != inspected_params.args
        ):
            return False

i.e. if the length of the args list is different, then we ignore the args values and detect no change.

Detecting a change whenever the length of the args lists are different would make sense, but I'm sure it produces tons of false positives - I already saw that myself with spurious INTEGER(display_width=11) to Integer() conversions.

As a workaround I tried adding this:

        if (
                meta_params.token0 == 'enum'
                and len(meta_params.args) != len(inspected_params.args)
        ):
            return False

This does actually work for my experiment - I added an item to my enum, and the correct migration script was produced. I don't imagine this is the right answer in general though. Maybe we can always detect a change in num args as a change, but filter out false positives like the display_width case.

[zzzeek]

enum is a special case, so I would not rely upon the logic added as part of #605 for this datatype. as things worked previously, a new compare_type() method should be restored to https://github.com/sqlalchemy/alembic/blob/master/alembic/ddl/mysql.py#L141 which accommodates for ENUM explicitly before calling the superclass method.

[zzzeek]

that's essentially what your workaround is doing.

[hughhan1]

I am still having this problem as well in MySQL, using flask_sqlalchemy's db.Column to define the columns on my model. Regardless of whether I use db.Enum or sqlalchemy.dialects.mysql.enumerated.ENUM to define the type of the column, Alembic is not detecting any changes when I add new values to the enum.

But in contrast, if I define a new column using the db.Enum type, Alembic successfully generates the migrations necessary to create the enum column with the proper values.

I also confirmed that when reflecting, the column's type is sqlalchemy.dialects.mysql.enumerated.ENUM.

[zliebersbach]

Here is how I solved it:

def my_compare_type(context, inspected_column, metadata_column, inspected_type, metadata_type):
    # return False if the metadata_type is the same as the inspected_type
    # or None to allow the default implementation to compare these
    # types. a return value of True means the two types do not
    # match and should result in a type change operation.

    if isinstance(inspected_type, sqlalchemy.Enum) and isinstance(metadata_type, sqlalchemy.Enum):
        inspected_enum_values = set(inspected_type.enums)
        metadata_enum_values = set(metadata_type.enums)
        return inspected_enum_values != metadata_enum_values

    return None

...and then in context configuration:

context.configure(
    ...
    compare_type=my_compare_type,
)

Just edit in env.py!

[kevinschumacher]

This issue also applies to postgres. But the custom compare_type proposed by @zliebersbach at least results in some migration auto-generated, even if the operations don't necessarily work 100%

(In the case of native enum, it never actually emits a statement to update the type definition; in non-native varchar+CHECK enums, it correctly updates the length of the field and emits a create constraint statement that is correct, but because of other limitations I guess doesn't notice you have to first drop the existing constraint. )

[caniko]

@kevinschumacher is correct, I just hit the same issue.

[mehranz]

Here's how I solved it that gives a little more control:

alembic/enum_migration.py

import logging
from typing import Optional

from alembic.autogenerate.api import AutogenContext
from alembic.autogenerate.compare import comparators
from alembic.operations.ops import AlterColumnOp
from sqlalchemy import Enum as SQLAEnum
from sqlalchemy.dialects.mysql import ENUM as MySQLEnum
from sqlalchemy.sql.schema import Column

logger: logging.Logger = logging.getLogger(__name__)


@comparators.dispatch_for("column")
def compare_column(
    autogen_context: AutogenContext,
    modify_ops: AlterColumnOp,
    schema: Optional[str],
    table_name: str,
    column_name: str,
    db_column: Column[MySQLEnum],
    model_column: Column[SQLAEnum],
) -> None:
    """Compare a column's model definition with its database state and modify the AlterColumnOp accordingly."""

    if isinstance(model_column.type, SQLAEnum) and isinstance(db_column.type, MySQLEnum):
        model_values: set[str] = set(model_column.type.enums)
        db_values: set[str] = set(db_column.type.enums)

        logger.debug(f"Comparing {table_name}.{column_name} db_values={db_values}, model_values={model_values}")

        if model_values ^ db_values:
            if new_values := model_values - db_values:
                logger.info(f"New values detected for Enum {table_name}.{column_name}: {', '.join(new_values)}")

            if removed_values := db_values - model_values:
                logger.info(f"Removed values detected for Enum {table_name}.{column_name}: {', '.join(removed_values)}")

            modify_ops.modify_type = model_column.type
            modify_ops.existing_type = SQLAEnum(*db_values, name=db_column.type.name or model_column.type.name)
            modify_ops.existing_nullable = db_column.nullable

and then in the alembic/env.py:

import enum_migration  # NOQA