how to clean up incomplete alembic run

Question

I'm trying to use alembic with a MySQL engine to perform online migrations. I've found that when an operation in my onupgrade() method fails my database gets stuck in an inconsistent state and i can't use alembic until I manually clean up any operations that happened before the failure in onupgrade()

Example:

def upgrade():
    op.create_table('sometable',
            Column('id', INTEGER, primary_key=True),
            Column('name', VARCHAR(150), nullable=False, unique=True))
    op.add_column('anothertable' Column('id', INTEGER))
    op.create_table('secondtable')

So if I run this and the op.add_column fails, even if I fix the add_column line, now "sometable" exists so the first operation will always fail. I can't run my downgrade script, because alembic never updated the version since it didn't complete the upgrade.

I was thinking if there was a way to force run my ondowngrade(), that might be useful. I'd have to ignore errors, as there are sure to be some. Like dropping "secondtable". I couldn't find anyway to do this though.

Anyone have a good way to handle this?

Answer 1

The problem isn't with alembic but lies in your usage of MySQL, which can't rollback DDL statements.

So the only (ugly) way to achieve it would be to do manual exception handling and reversing the operations that were successful until that point.

Something like this (written out of my mind, so it's not the most elegant solution and maybe even a little wrong, but I hope you get the gist):

def upgrade():
    try:
        op.create_table('sometable',
            Column('id', INTEGER, primary_key=True),
            Column('name', VARCHAR(150), nullable=False, unique=True))
    except:
        try:
            op.drop_table('sometable')
        except:
            pass
        raise

    try:
        op.add_column('anothertable' Column('id', INTEGER))
    except:
        op.drop_table('sometable')
        try:
            op.drop_column('anothertable', 'id')
        except:
            pass
        raise

    try:
        op.create_table('secondtable')
    except:
        op.drop_table('sometable')
        op.drop_column('anothertable', 'id')
        try:
            op.drop_table('secondtable')
        except:
            pass
        raise