MySQL drop of unique index is doubled when using autogenerate

[sqlalchemy-bot]

Migrated issue, originally created by Johannes Erdfelt (@jerdfelt)

When autogenerate is used and a unique index is removed, it will appear as both a remove of a unique constraint and a unique index.

This effectively doubles the drop causing the second to fail.

The attached test case reproduces the problem:

[('remove_constraint',
UniqueConstraint(Column('s', VARCHAR(length=20), table=<unq_idx>))),
('remove_index',
Index('test_uc_1', Column('s', VARCHAR(length=20), table=<unq_idx>), unique=True))]

---

Attachments: alembic_mysql_double_drop_testcase.py

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

oddly enough all of the tests for this functionality were not ensuring that the "diffs" was just that one element. I would imagine that in the five or six releases over which MySQL / PG autogen indexes kept turning up more edge cases I left it open ended in that regard; the biggest focus of that whole system is to not turn up false positives.

Since MySQL doesn't really have unique constraints except syntactically, it will just have to behave differently than other backends; if you do CREATE TABLE and use UNIQUE, but later that table is caught up in autogenerate only on the "connection" side, it's going to be a DROP INDEX.

Special logic still has to remain for the case where one has an unnamed unique constraint on the Python side and the unique constraint to be dropped looks just like it column-wise, as that likely means it is not to be dropped.

The new flags in SQLA 1.0 would help with this but aren't strictly necessary as alembic already has a whole suite of database-specific rules for this.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• Fixed bug where MySQL backend would report dropped unique indexes
  and/or constraints as both at the same time. This is because
  MySQL doesn't actually have a "unique constraint" construct that
  reports differently than a "unique index", so it is present in both
  lists. The net effect though is that the MySQL backend will report
  a dropped unique index/constraint as an index in cases where the object
  was first created as a unique constraint, if no other information
  is available to make the decision. This differs from other backends
  like Postgresql which can report on unique constraints and
  unique indexes separately.
  fixes #276

→ 2108076

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed