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?

Castanets

Just a guess: I would suspect that alembic tries to run the migrations in a transaction and rolls back on errors - but probably, your MySQL configuration doesn't support transactions, so you're out of luck. – Loisloise

That's what I was afraid of, MySQL does not support transactions for DDL statments :( dev.mysql.com/doc/refman/5.0/en/cannot-roll-back.html – Castanets

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

Loisloise

Why cant alembic put each step of the migration in a list of length n. Doing the migration, if it gets to step x (0 <= x < n) and fails, then it should enter the downgrade at n-x and downgrade? – Hoskins

Answer 2

If you have in version control the database model you were migrating from, you can temporarily use it to create a migration. (You might have to empty out your versions folder / put everything in a temp directory) Alembic will compare the current state of the database with the model, and give you migration commands for the database to reach that state. In this case, it should give you the instructions for reverting the database to the previous state. You will have to look at the migration commands that were generated to make sure it's exactly what you need, but you won't need to generate them yourself.

After that you can delete the migration, and roll back to the latest db model file. Then you should be back to the point you started

Huddleston

Answer 3

considering that "can't rollback DDL", I created an alternative to make the "rollback" process less ugly in case of fail

I modified alembic/env.py to perform the downgrade function of revision in case of failure:

env.py

...

def resolve_version_fails():
  stack = traceback.format_exc().splitlines()
  for line in stack:
    if 'alembic/versions' in line and '.py' in line:
        return line.split('File "')[1].split('",')[0]
  return None

def import_downgrade_function(full_file_path):
  file_name = os.path.basename(full_file_path)
  module_name = file_name.split('.py')[0]

  spec = importlib.util.spec_from_file_location(module_name, full_file_path)
  module = importlib.util.module_from_spec(spec)
  spec.loader.exec_module(module)
  return getattr(module, 'downgrade')

 def run_migrations_online() -> None:
   ...
    try:
        with context.begin_transaction():
            context.run_migrations()
    except Exception as e:
        # get file version that fails
        full_file_path = resolve_version_fails()
        config.print_stdout(f'ERROR running migration {full_file_path}')
        
        downgrade = import_downgrade_function(full_file_path)
        config.print_stdout(f'Running downgrade of {full_file_path}')
        downgrade()
        raise e

my migration file:

def upgrade() -> None:
  op.create_table('api_user',
    ...
  )


def downgrade() -> None:
  #op.drop_table('api_user')
  #drop table if exists
  op.execute('DROP TABLE IF EXISTS api_user')

So in case of upgrade fails, downgrade is executed

(when context.run_migrations() fails then run downgrade)

Ingesta