Autogenerate generates spurious indexes (and drops them immediately)

Issue #476 closed
MartinH
created an issue

Not sure since how long this happens, but it seems my colleagues have seen this for some time.

What happens: When autogenerating a migration, it is full of

    op.create_index('answer.fk_answer_question', 'answer', ['questionid'], unique=False)
    op.drop_index('answer.fk_answer_question', table_name='answer')

pairs. It seems one for every id in our data model.

I assume it's a bug, as the generated create/drop pair doesn't seem to do anything.

(It generates them all in the upgrade, and then again in the downgrade method...)

What kind of additional info can I provide?

version: Python 2.7.10, alembic (0.9.6), SQLAlchemy (0.9.10)

Now that I write this, the old version of SQLAlchemy is quite likely a problem - if you agree, I'm going to work on updating that dependency next.

Comments (14)

  1. Michael Bayer repo owner

    sqla 097 might not be the issue, need to know database backend and need table definition which causes the problem. Each database backend produces implicit indexes for things which must be corrected for. Latest master has a fix in this regard as well but there have been many over the years.

  2. MartinH reporter

    This happens with sqlite and mysql backends.

    Table definitions look like this:

    class Answer(Base, MetadataMixin):
        __tablename__ = 'answer'
    
        id = Column('idanswer', Integer, primary_key=True)
        question_id = Column('questionid', Integer, ForeignKey(Question.id, onupdate="CASCADE", ondelete="CASCADE"))
        title = Column(UnicodeText, default=u'')
        is_correct = Column("correct", Boolean, default=False)
    
        question = relationship(Question, backref=backref('answers', cascade='save-update, merge, delete', order_by='Answer.id'))
    

    Base and MetadataMixin add methods, properties and some columns (like creation, change time and which user did it). Should I provide more details?

  3. Michael Bayer repo owner

    You just need to show me all the columns I need to reproduce the issue. I test it by putting the table defs into an env.py, running autogen, update the database to that version, then running an autogen again. Second version w no bug would be blank, w the bug has the migrations you are seeing.

  4. MartinH reporter

    We do create the database with .create_all() calls, so I was not expecting uns to miss something.

    Related: Does this explain why the update and downgrade paths both generate create and drop calls fo the same indexes?

  5. Michael Bayer repo owner

    OK so....are those indexes actually in the database? A plain Index() like that is definitely working with autogenerate, it will only add this to your migration file if it sees it in your model and does not see it in the database.

  6. Robert Buchholz

    The index is indeed already defined.

    Here is what I am seeing with MariaDB 10.2.9:

    MariaDB [dbname]> show index from answer;
    +--------+------------+---------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table  | Non_unique | Key_name                  | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +--------+------------+---------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | answer |          0 | PRIMARY                   |            1 | idanswer       | A         |        1297 |     NULL | NULL   |      | BTREE      |         |               |
    | answer |          1 | modified_by_id            |            1 | modified_by_id | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
    | answer |          1 | created_by_id             |            1 | created_by_id  | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
    | answer |          1 | answer.fk_answer_question |            1 | questionid     | A         |        1297 |     NULL | NULL   | YES  | BTREE      |         |               |
    +--------+------------+---------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    

    With --autogenerate, alembic will create a migration containing these lines:

    def upgrade():
    ...
        op.create_index('answer.fk_answer_question', 'answer', ['questionid'], unique=False)
        op.drop_index('answer.fk_answer_question', table_name='answer')
    
    def downgrade():
    ...
        op.create_index('answer.fk_answer_question', 'answer', ['questionid'], unique=False)
        op.drop_index('answer.fk_answer_question', table_name='answer')
    

    When executing the migration, I get:

    sqlalchemy.exc.InternalError: (InternalError) (1061, u"Duplicate key name 'answer.fk_answer_question'") u'CREATE INDEX `answer.fk_answer_question` ON answer (questionid)' {}
    
  7. Michael Bayer repo owner

    Cannot reproduce. Next possibility would be debugging any special exclusion rules you've placed in your env.py that are making it miss this index during autogenerate.

    Using SQLAlchemy 0.9.11, Alembic 0.9.7, Mariadb 10.2.9:

    create blank alembic environment:

    $ .tox/py27-sqla09-mysql/bin/alembic  init foo
      Creating directory /home/classic/dev/alembic/foo ... done
      Creating directory /home/classic/dev/alembic/foo/versions ... done
      Generating /home/classic/dev/alembic/foo/README ... done
      Generating /home/classic/dev/alembic/foo/script.py.mako ... done
      Generating /home/classic/dev/alembic/foo/env.pyc ... done
      File /home/classic/dev/alembic/alembic.ini already exists, skipping
      Generating /home/classic/dev/alembic/foo/env.py ... done
      Please edit configuration/connection/logging settings in '/home/classic/dev/alembic/alembic.ini' before proceeding.
    

    put MySQL url into alembic.ini

    sqlalchemy.url =  mysql+pymysql://scott:tiger@mariadb102/test?charset=utf8
    

    in env.py, place model as follows (replace target_metadata = None with below):

    from sqlalchemy import *
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.ext import mutable
    import sqlalchemy as sa
    
    Base = declarative_base()
    
    target_metadata = m = Base.metadata
    
    class Question(Base):
        __tablename__ = 'question'
        id = Column('idquestion', Integer, primary_key=True)
    
    class Answer(Base):
        __tablename__ = 'answer'
    
        id = Column('idanswer', Integer, primary_key=True)
        question_id = Column('questionid', Integer, ForeignKey(Question.id, onupdate="CASCADE", ondelete="CASCADE"))
        title = Column(UnicodeText, default=u'')
        is_correct = Column("correct", Boolean, default=False)
    
    Index("answer.fk_answer_question", Answer.question_id)
    

    Create first revision, note log messages:

    $ .tox/py27-sqla09-mysql/bin/alembic  revision -m "rev1" --autogenerate
    INFO  [alembic.runtime.migration] Context impl MySQLImpl.
    INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
    INFO  [alembic.autogenerate.compare] Detected added table 'question'
    INFO  [alembic.autogenerate.compare] Detected added table 'answer'
    INFO  [alembic.autogenerate.compare] Detected added index 'answer.fk_answer_question' on '['questionid']'
      Generating /home/classic/dev/alembic/foo/versions/5e1311bfc542_rev1.py ... done
    

    Migration file contains:

    def upgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.create_table('question',
        sa.Column('idquestion', sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint('idquestion')
        )
        op.create_table('answer',
        sa.Column('idanswer', sa.Integer(), nullable=False),
        sa.Column('questionid', sa.Integer(), nullable=True),
        sa.Column('title', sa.UnicodeText(), nullable=True),
        sa.Column('correct', sa.Boolean(), nullable=True),
        sa.ForeignKeyConstraint(['questionid'], [u'question.idquestion'], onupdate='CASCADE', ondelete='CASCADE'),
        sa.PrimaryKeyConstraint('idanswer')
        )
        op.create_index('answer.fk_answer_question', 'answer', ['questionid'], unique=False)
        # ### end Alembic commands ###
    

    upgrade database to this version:

    $ .tox/py27-sqla09-mysql/bin/alembic  upgrade head
    INFO  [alembic.runtime.migration] Context impl MySQLImpl.
    INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
    INFO  [alembic.runtime.migration] Running upgrade  -> 5e1311bfc542, rev1
    

    run next migration. This is where we would see the index being detected again if there's a bug:

    $ .tox/py27-sqla09-mysql/bin/alembic  revision -m "rev2" --autogenerate
    INFO  [alembic.runtime.migration] Context impl MySQLImpl.
    INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
    /home/classic/dev/alembic/.tox/py27-sqla09-mysql/lib/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:2774: SAWarning: Unknown schema content: u'  CONSTRAINT `CONSTRAINT_1` CHECK (`correct` in (0,1))'
      return parser.parse(sql, charset)
      Generating /home/classic/dev/alembic/foo/versions/73d599ea3005_rev2.py ... done
    

    note it tripped over the boolean constraint a bit, I think this is improved in SQLAlchemy 1.0 or 1.1 for MySQL. Note there is no detection of an added index. Migration file contains:

    def upgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        pass
        # ### end Alembic commands ###
    
    
    def downgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        pass
        # ### end Alembic commands ###
    

    Please try these steps out on a blank environment, and/or look in your env.py for an "include_object" routine, or other environment extension of some kind, that might be excluding these indexes.

  8. Log in to comment