Alembic (MySQL) - Always wants to drop/create indexes where index contains column that requires a size

Question

I have the following class..

class X( Base ):
    __tablename__   = "X"
    __table_args__  = (
        Index( "index1", "downloaded", text( "x_name( 255 )" ) ),
    )

    id:         Mapped[ int ]   = mapped_column( BIGINT( unsigned = True ), primary_key = True )
    downloaded: Mapped[ date ]  = mapped_column( DATE )
    x_name:     Mapped[ str ]   = mapped_column( TINYTEXT )

As you can see, i have an index, downloaded and x_name. x_name needs to have its size determined so I've used text() to denote this.

All is well until I try and perform a migration using alembic with this.

Every time I run the auto migration I get a warning along the lines of..

Generating approximate signature for index Index( 'index1', Column('downloaded', DATE(), table=<X>, nullable=False ), <sqlalchemy.sql.elements.TextClause object at 0x7b87bc1aafe0 ). The dialect implementation should either skip expression indexes or provide a custom implementation.

And the migration script drops and creates the index every time. Every migration I do, even if I don't make any changes to this table, gets the index dropped and recreated

I just don't know how to fix this. I have to have the "text()" in the index as it doesn't render the DDL correctly. It mentions providing a "custom implementation" but I've no idea what that means or where to start.

Answer 1

Use the mysql_length= argument when declaring the Index in your model, as documented here.

class X(Base):
    __tablename__ = "x"
    __table_args__ = (
        Index("index1", "downloaded", "x_name", mysql_length={"x_name": 255}),
    )