stamp database from another project fails with "Can't locate revision identified by ..."

[sqlalchemy-bot]

Migrated issue, originally created by Miguel Grinberg (@miguelgrinberg)

Let's say I have two projects, A and B, both with Alembic migration repositories. I want to take the database from A and move it to B.

With releases up to 0.6.7, I can go to project B, update the SQLAlchemy connection URL to point to A's database, and then issue alembic stamp head, and with this, B would take ownership of the database.

After the versioning changes introduced in 0.7, the stamp command fails, because the version number stored in the alembic_version table is validated before the stamp command executes. Obviously this version number is found to be unknown, since it points to a migration in project A, not B.

If this was an intentional change, then I apologize for writing a bug. But I find the stamp command implementation from the old 0.6 releases useful when teaching SQLAlchemy and Alembic, as it allows me to easily move SQLite databases between me and my students, where we all have built our own migration repositories for the class project.

Here is the output that includes the error, in case it is useful:

(venv) alembic $ alembic current
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [alembic.util.messaging] Can't locate revision identified by '45e389123f2a'
  FAILED: Can't locate revision identified by '45e389123f2a'
(venv) alembic $ alembic --raiseerr stamp head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
Traceback (most recent call last):
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 143, in _catch_revision_errors
    yield
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 206, in get_revisions
    return self.revision_map.get_revisions(id_)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 299, in get_revisions
    return sum([self.get_revisions(id_elem) for id_elem in id_], ())
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 299, in <listcomp>
    return sum([self.get_revisions(id_elem) for id_elem in id_], ())
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 304, in get_revisions
    for rev_id in resolved_id)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 304, in <genexpr>
    for rev_id in resolved_id)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 362, in _revision_for_ident
    resolved_id)
alembic.script.revision.ResolutionError: No such revision or branch '45e389123f2a'

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/bin/alembic", line 11, in <module>
    load_entry_point('alembic==0.9.6', 'console_scripts', 'alembic')()
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/config.py", line 479, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/config.py", line 473, in main
    self.run_cmd(cfg, options)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/config.py", line 456, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/command.py", line 504, in stamp
    script.run_env()
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 425, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 83, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 71, in <module>
    run_migrations_online()
  File "alembic/env.py", line 66, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/runtime/migration.py", line 321, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/command.py", line 493, in do_stamp
    return script._stamp_revs(revision, rev)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 358, in _stamp_revs
    heads = self.get_revisions(heads)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 206, in get_revisions
    return self.revision_map.get_revisions(id_)
  File "/usr/local/Cellar/python3/3.6.3/Frameworks/Python.framework/Versions/3.6/lib/python3.6/contextlib.py", line 99, in __exit__
    self.gen.throw(type, value, traceback)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 174, in _catch_revision_errors
    compat.raise_from_cause(util.CommandError(resolution))
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 194, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/util/compat.py", line 187, in reraise
    raise value.with_traceback(tb)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 143, in _catch_revision_errors
    yield
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/base.py", line 206, in get_revisions
    return self.revision_map.get_revisions(id_)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 299, in get_revisions
    return sum([self.get_revisions(id_elem) for id_elem in id_], ())
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 299, in <listcomp>
    return sum([self.get_revisions(id_elem) for id_elem in id_], ())
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 304, in get_revisions
    for rev_id in resolved_id)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 304, in <genexpr>
    for rev_id in resolved_id)
  File "/Users/migu7781/Documents/dev/tmp/alembic/venv/lib/python3.6/site-packages/alembic/script/revision.py", line 362, in _revision_for_ident
    resolved_id)
alembic.util.exc.CommandError: Can't locate revision identified by '45e389123f2a'

If I downgrade to Alembic 0.6.7, then this is the output:

(venv) alembic $ pip install alembic==0.6.7
Collecting alembic==0.6.7
  Using cached alembic-0.6.7.tar.gz
Requirement already satisfied: SQLAlchemy>=0.7.3 in ./venv/lib/python3.6/site-packages (from alembic==0.6.7)
Requirement already satisfied: Mako in ./venv/lib/python3.6/site-packages (from alembic==0.6.7)
Requirement already satisfied: MarkupSafe>=0.9.2 in ./venv/lib/python3.6/site-packages (from Mako->alembic==0.6.7)
Installing collected packages: alembic
  Found existing installation: alembic 0.9.6
    Uninstalling alembic-0.9.6:
      Successfully uninstalled alembic-0.9.6
  Running setup.py install for alembic ... done
Successfully installed alembic-0.6.7
(venv) alembic $ alembic current
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
ERROR [alembic.util] No such revision '45e389123f2a'
  FAILED: No such revision '45e389123f2a'
(venv) alembic $ alembic stamp head
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
(venv) alembic $ alembic current
INFO  [alembic.migration] Context impl SQLiteImpl.
INFO  [alembic.migration] Will assume non-transactional DDL.
Current revision for sqlite:///alembic_sample.sqlite: da57c3eb4203 -> 897969014ee6 (head), empty message

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

why can't you make a file with revision "B" available in the alembic_versions directory of the target ?

[sqlalchemy-bot]

Miguel Grinberg (@miguelgrinberg) wrote:

Sure, I can. If I "fix" the migration history in project B to have A's head somewhere, then the stamp works, but that's super obscure, and definitely not something I want people who are learning see me do, or even less ask them to do.

When I do this on my own I don't really bother with the migrations, I just issue a SQL UPDATE direct to the database and bypass stamp altogether, but once again, I'm showing people how to work with an ORM, so that sort of gives the wrong message.

My impression is that stamp should work regardless of the current state of the database. Am I not correct? If the alembic_version table is missing, it creates it and does the stamp correctly, so this shouldn't be any different, in my opinion.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

it can't work that way anymore due to branching and merging. If I have revision C that is a merge of A and B, and I do a "stamp" of C, it will emit a DELETE for the A and B rows. It only knows this is necessary due to the file structure in the alembic_versions directory.

it also doesn't make any sense to do a STAMP of a version that doesn't actually exist; (EDIT: see below I misunderstood what you're trying to do) even with the old Alembic version, you've now created a broken alembic_versions table. I'm not sure why you'd want to teach students something that doesn't actually work ?

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

oh you mean the PREVIOUS version is the one it doesn't recognize. I would say that emitting DROP TABLE for alembic_version and then doing stamp is the best way to accommodate that use case. there might be a feature request for a flag that does that in here already.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

again, if it sees versions in the table already when it goes to stamp, it needs to know what those are as it is not a given that those versions are part of the branch in which you are stamping - it affects if your stamp is INSERTed or UPDATEd against that row. So only an explicit "please blow away everything here first" is an appropriate way that the stamp command knows what to do.

[sqlalchemy-bot]

Miguel Grinberg (@miguelgrinberg) wrote:

oh you mean the PREVIOUS version is the one it doesn't recognize

Correct. A's head is not recognized when I do a stamp head from B, and that interrupts the stamp.

Dropping the table before the stamp would work, I agree.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

so, STAMP is no longer a simple UPDATE. it behaves based on analyzing what's in the table already and how that relates to the actual versioning structure. so to use a brand new versioning structure the existing table needs to be dropped or emptied out fully. Easy enough to add a flag to "stamp" such as "--purge" or something like that, though such a flag, perhaps it wants to delete only unrecognized versions? or all of them? There's a lot of possibilities here.

[sqlalchemy-bot]

Miguel Grinberg (@miguelgrinberg) wrote:

I would think that with --purge you always start from scratch. Maybe the stamp command should also accept a list of revisions, then when combined with --purge it could set up all your branch versions in a single call.

[sqla-tester]

Mike Bayer has proposed a fix for this issue in the master branch:

Add multiple revision support to stamp, support purge https://gerrit.sqlalchemy.org/1469