Autogenerate wants to add existing indexes

[sqlalchemy-bot]

Migrated issue, originally created by Sjoerd Langkemper

The autogenerate functionality adds indexes that already exist, if the index name is the same as the column name.

We have explicit indexes in our metadata.py with the same name as the column:

Index(u'account_id', account_buildingblock.c.account_id)

When using alembic revision --autogenerate, alembic wants to add this index to the database, even though it already exists.

The index is correctly detected by Inspector.get_indexes, but then removed from the conn_indexes list by MySQLImpl.correct_for_autogen_constraints. This method removes all indexes where the name is equal to the first column. The result is that the index is created in the upgrade method of the migration:

op.create_index('account_id', 'account_event', ['account_id'], unique=False)

Running this migration gives an error:

sqlalchemy.exc.OperationalError: (OperationalError) (1061, "Duplicate key name 'account_id'") 'CREATE INDEX account_id ON account_event (account_id)' ()

It would be ok if _compare_indexes_and_uniques ignores these indexes, but it has to do so on the metadata side as well as the database side. Currently it pretends the indexes on the database don't exist, and tries to add them, which gives an error.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: high priority

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK I guess what we can do is for those indexes we found on the reflected side, remove the same ones from the metadata side as well. that would allow some support for detecting an "add" of these indexes. As you can see in the comment, MySQL has implicit column-named indexes placed on FK columns anyway, your approach hasn't run into problems with that ?

[sqlalchemy-bot]

Sjoerd Langkemper wrote:

You mean that MySQL would complain because it creates an index and we create the same index. We have not encountered any problems, maybe because the table and index are added in the same migration:

op.create_table('account_buildingblock',
    Column(u'account_id', mysql.INTEGER(unsigned=True), nullable=False),
    Index(u'account_id', u'account_id'),
    ...
    )

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• This releases' "autogenerate index detection" bug, when a MySQL table
  includes an Index with the same name as a column, autogenerate reported
  it as an "add" even though its not; this is because we ignore reflected
  indexes of this nature due to MySQL creating them implicitly. Indexes
  that are named the same as a column are now ignored on
  MySQL if we see that the backend is reporting that it already exists;
  this indicates that we can still detect additions of these indexes
  but not drops, as we cannot distinguish a backend index same-named
  as the column as one that is user generated or mysql-generated.
  fixes Autogenerate wants to add existing indexes #202

→ 55d97db

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK so this will no longer report an "add" when there isn't one, and it will still detect an actual "add" of such an index. We just can't detect when such an index is "dropped" because when we see it on the backend, we don't know if that index is an implicitly generated one or not.