Alembic: Redundant Foreign Key migrations (again)

[rjaduthie]

Although there are other closed issues addressing this, they seem to deal with postgres and I have not found a solution to the problem I've seen. The problem is reproducable and I've created a minimal example: https://github.com/sqlalchemy/alembic/files/2625781/FK_test.tar.gz

On repeated alembic revision --autogenerate then alembic upgrade head commands, alembic always seems to want to drop and create the foreign keys.

Logging to stdout:

    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

Resulting migration script:

 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 ###

ORM code:

# [...imports 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'),
    )

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

I've documented this on StackOverflow here:
https://stackoverflow.com/questions/53523529/alembic-revision-autogenerate-produces-redundant-foreign-key-migrations

[zzzeek]

your attachment seems to have just an env.py file inside of it, there's no files for the "from test_fktdb.orm import Base as FKTDB_Base" part, can you just attach the zip to the issue here? github supports attachments

[rjaduthie]

Sorry, I think filedropper is doing something to the tar. Here it is unadulterated:

FK_test.tar.gz

I've also updated the issue above with this link.

[zzzeek]

nice effort on the test case here. since it's docker i can actually just run it, thanks

[zzzeek]

though I think I already see the problem just want to confirm

[rjaduthie]

Sounds promising.

[zzzeek]

OK, so here is the thing. you are connecting with "test_fktdb" in your database URL as the default schema. which means, alembic is going to find your tables in that schema, and when it finds the foreign key, it will see the "schema_name" field in that FK as empty, because this is the default schema. So it doesn't match what you have in your metadata. Also you aren't adding "include_schemas=True" to the environment, so you will definitely not get reasonable results when your ORM models have "schema='test_fktdb'" in them.

there's two general worlds you can go into to fix this.

1. easy one. take out "schema" from your tables/metadata/foreign keys entirely. then everything works in test_fktdb as the default and everything matches.

2. hard one. you need to connect to a different database on your URL, then set up include_schemas=True in your envrionment, you probably also need a reasonable include_object() scheme so that it doesnt read in all the other databases, set up version_table_schema='test_fktdb', then that works too:

env.py:

SCHEMA_NAME = "NOT_test_fktdb"

    def include_object(object, name, type_, reflected, compare_to):
        if (type_ == "table"):
            return object.schema == "test_fktdb"

        else:
            return True

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True,
            compare_server_default=True,
            include_schemas=True,
            version_table_schema="test_schema",
            include_object=include_object
        )

       # ...

the "schema" logic necessarily has to rely heavily on this concept of "default" schema being a blank string, so when you mix up the default schema also being present it confuses things.

[rjaduthie]

Thanks for this.

Firstly, I had already tried include_schemas=True without any change in behaviour. From the docs, this option seemed to be focussed on situations with multiple schemas, which I don't have.

you are connecting with "test_fktdb" in your database URL as the default schema. which means, alembic is going to find your tables in that schema

So, what part of the system is dictating this? SQLAlchemy? The PyMySQL driver? If this issue was cleared up, there'd be no need to remove metadata from the ORM.

Regarding the solutions
I've ruled out the second option, as there won't be another DB on the server and it doesn't seem like a good idea to introduce one for these purposes solely. I did try connecting without a schema specified (i.e. the sqlalchemy.url = "mysql+pymysql://bob:[email protected]") but SQLAlchemy doesn't like that.

In trying to get the first sugggestion working with the example, I've changed a couple of things:

# instead of [...]:
# declarative_base(metadata=sqlalchemy.MetaData(schema=test_fktdb.SCHEMA_NAME))
Base = sqlalchemy.ext.declarative.declarative_base()

# instead of [...]:
# ForeignKeyConstraint(["t1_id"], ["test_fktdb.table_one.id"], name="fk_table1"),
ForeignKeyConstraint(["t1_id"], ["table_one.id"], name="fk_table1"),

... and that works as you predicted! Thanks. However, I wonder if there will be any issue now that the tables don't have a scheam defined in their metadata, for example with normal SQL Alchemy operation. I had a thought that the metadata could be massaged in env.py to make it work. Although target_metadata.schema=None is easy, trawling through tables, etc., and changing their names becomes unwieldy.

Anyway thanks for your help. I'll see if the easy solution is suitable for my application.

[zzzeek]

Thanks for this.

Firstly, I had already tried include_schemas=True without any change in behaviour. From the docs, this option seemed to be focussed on situations with multiple schemas, which I don't have.

this only would work if the database you connect to is not the schema name, see my example:

   SCHEMA_NAME = "NOT_test_fktdb"

  # e.g.:

   e = create_engine("mysql://hostname/%s" % SCHEMA_NAME)

you are connecting with "test_fktdb" in your database URL as the default schema. which means, alembic is going to find your tables in that schema

So, what part of the system is dictating this? SQLAlchemy? The PyMySQL driver? If this issue was cleared up, there'd be no need to remove metadata from the ORM.

SQLAlchemy's convention is that table metadata is agnostic of the default schema in the database if the schema_name is not given. To work correctly, table reflection must assume the same convention, meaning, when it reflects database tables, if those tables are in the default schema, the "schema" parameter is not filled in. Without this assumption, all kinds of use cases become un-symmetrical. It is possible that SQLAlchemy reflection could be enhanced to allow "schema explicit" to be an option, however we've tried to stick to one single convention that, when followed, can work in all cases, rather than trying to support two conventions as this issue is confusing enough.

Regarding the solutions
I've ruled out the second option, as there won't be another DB on the server and it doesn't seem like a good idea to introduce one for these purposes solely. I did try connecting without a schema specified (i.e. the sqlalchemy.url = "mysql+pymysql://bob:[email protected]") but SQLAlchemy doesn't like that.

right, the "no database" use case is something that we also don't support right now, which is because most backends don't even support such a concept, it is idiosyncratic to MySQL. That is, I can't connect to a SQLite or Postgresql database without there being an implicit "no-schema" database that I can CREATE TABLE inside of without schema qualification.

In trying to get the first sugggestion working with the example, I've changed a couple of things:

# instead of [...]:
# declarative_base(metadata=sqlalchemy.MetaData(schema=test_fktdb.SCHEMA_NAME))
Base = sqlalchemy.ext.declarative.declarative_base()

# instead of [...]:
# ForeignKeyConstraint(["t1_id"], ["test_fktdb.table_one.id"], name="fk_table1"),
ForeignKeyConstraint(["t1_id"], ["table_one.id"], name="fk_table1"),

... and that works as you predicted! Thanks. However, I wonder if there will be any issue now that the tables don't have a scheam defined in their metadata, for example with normal SQL Alchemy operation.

They are now defined as schema agnostic. the database name you put in your engine URL in create_engine() is where those tables will be created.

I had a thought that the metadata could be massaged in env.py to make it work. Although target_metadata.schema=None is easy, trawling through tables, etc., and changing their names becomes unwieldy.

yes, people expect that they can change their database URL to some totally other thing, and their Table objects work the same way over there as in the other URL. That is, they are portable. When you add a schema name, the table is still portable, in that the schema name is relative to the default schema. This has to do with what the "schema" concept was modeled after, which is Postgresql schemas, that is, you can have a database, and then within it, multiple schemas.

When "schema" is used with a database that does not actually support PG-style "schemas", like MySQL or Oracle, it instead acts as kind of a cross-database accessor, but this usage is much less common.

Anyway thanks for your help. I'll see if the easy solution is suitable for my application.

any "user question" issue pretty much is going to mean the documentation is lacking so the section in https://docs.sqlalchemy.org/en/latest/core/metadata.html#specifying-the-schema-name which is very terse and old should be enhanced to include some of my information above as well as to draw from some of the material at https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path.

[zzzeek]

oh, but over on the SQLAlchemy side.