Btukfyl

Software versions: alembic 1.0.5, SQLAlchemy 1.2.14, MySQL 5.7, Python 3.6.7

I am trying to use alembic to keep a MySQL database schema and the Python ORM representation in step.

The issue I am seeing is that the migrations always have redundant drop and create commands for foreign keys. It seems that autogenerate is seeing something as being different, but they are actually the same.

On repeated invocations of the commands:

alembic revision --autogenerate 

alembic upgrade head

...will produce the same drop and create commands.

The logging to stdout shows something like (e.g.):

INFO  [alembic.autogenerate.compare] Detected removed foreign key (t1_id)(id) on table table_two

INFO  [alembic.autogenerate.compare] Detected added foreign key (t1_id)(id) on table test_fktdb.table_two

and the migration script has:

def upgrade():

    # ### commands auto generated by Alembic - please adjust! ###

    op.drop_constraint('fk_table1', 'table_two', type_='foreignkey')

    op.create_foreign_key('fk_table1', 'table_two', 'table_one', ['t1_id'], ['id'], source_schema='test_fktdb', referent_schema='test_fktdb')

    # ### end Alembic commands ###





def downgrade():

    # ### commands auto generated by Alembic - please adjust! ###

    op.drop_constraint('fk_table1', 'table_two', schema='test_fktdb', type_='foreignkey')

    op.create_foreign_key('fk_table1', 'table_two', 'table_one', ['t1_id'], ['id'])

    # ### end Alembic commands ###

This issue can be replicated and I've made a minimal example (a tar.gz on https://github.com/sqlalchemy/alembic/files/2625781/FK_test.tar.gz). The ORM in the example goes something like:

[...import and bobs...]



class TableOne(Base):

    """Class representing a table with an id."""

    __tablename__ = "table_one"



    id = Column(UNSIGNED_INTEGER, nullable=False, autoincrement=True, primary_key=True)



    __table_args__ = (

        dict(mysql_engine='InnoDB'),

    )





class TableTwo(Base):

    """A table representing records with a foreign key link to table one."""

    __tablename__ = "table_two"



    id = Column(UNSIGNED_INTEGER, nullable=False, autoincrement=True, primary_key=True)

    t1_id = Column(UNSIGNED_INTEGER, nullable=False)



    __table_args__ = (

        ForeignKeyConstraint(["t1_id"], ["test_fktdb.table_one.id"], name="fk_table1"),

        dict(mysql_engine='InnoDB'),

    )

Is there anything that can be done to make alembic 'see' the FKs in the database as being the same as those in the ORM? Applying some configuration via env.py, for example?

I've had a look around for this problem and found some old issues in the alembic GitHub (see [1],[2],[3]). The issues that have solutions seem to deal with postgres databases and the schema being public. I'm not sure this applies to this case, as I am using MySQL; the related documentation for public postgres schemas is here: https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path

I've now added my own issue to the alembic GitHub repo: https://github.com/sqlalchemy/alembic/issues/519

Closed issues in alembic issue tracker, which show similar symptoms, but whose solutions don't apply (as far as I can see):

[1] https://github.com/sqlalchemy/alembic/issues/444

[2] https://github.com/sqlalchemy/alembic/issues/398

[3] https://github.com/sqlalchemy/alembic/issues/293