alembic util command error can't find identifier

Question

I'm trying to use alembic to handle local migrations on my project. It worked the first time, but then I needed to delete the folder and restart.(don't ask why, I just had to) I'm following this tutorial and I run the command

python manage.py db init

And it was ok. But when I try to run

python manage.py db migrate

I'm getting this error:

alembic.util.CommandError: Can't locate revision identified by '31b8ab83c7d'

Now, it seems that alembic is looking for a revision that doesn't exists anymore. There is anyway to make alembic forget that file? Or like restart the comparison from None to -> auto-generated again?

Answer 1

Alembic stores the version history in your database. Hence it is using the value stored in your database to search for the revision. The version number for my personal database is stored in the table alembic_version:

mysql> SELECT * FROM alembic_version;
+-------------+
| version_num |
+-------------+
| c8ad125e063 |
+-------------+
1 row in set (0.00 sec)

Hint: Use the command SHOW TABLES if it's a SQL based database to see the tables.

To solve your problem simply use the command:

DROP TABLE alembic_version;

Or whatever the name of database version table is. And then you need to re-init the migration folder using the command:

python manage.py db init

And then creating a new migration:

python manage.py db migrate

And then you should be good to go with working migrations in alembic.

Answer 2

SirKaiserKai's solution didn't work for me, likely because I made some stupid mistake last time I migrated and deleted a file that I should have kept.

Instead of dropping the alembic_revision table I just updated the value in version_num to match where I knew my DB was at.

Make sure you use the migration ID of the file that matches the current state of your database

1. Check the missing migration number

   psql=> SELECT * FROM alembic_version;
   +-------------------------+
   |      version_num        |
   +-------------------------+
   | <the missing migration> |
   +-------------------------+
   (1 row)
   

2. Update the value

   psql=> UPDATE alembic_version
   psql->    SET version_num = '<true state of DB>'
   psql->    WHERE version_num = '<the missing migration>';
   UPDATE 1
   

If your database is in a state other than the migration file <true state of DB> then you're just going to continue to have errors. However, you could run a alembic upgrade head if the <true state of DB> is a migration file continuing from where you left off previously, and that would run all the migrations post this state as well.

Answer 3

In case you face the same issue above run the following commands:

Drop your local Alembic table

 - drop table alembic_version;

Then run the following alembic commands:

 - alembic stamp head
 - alembic revision --autogenerate -m "New revision"
 - alembic upgrade head
 - alembic stamp head

The stamp head allows alembic to have the latest table structure in place. Before running your new revisions.

Answer 4

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
ERROR [alembic.util.messaging] Can't locate revision identified by 'c140cfa017b4'
  FAILED: Can't locate revision identified by 'c140cfa017b4'

Answer:
Note: I solved this in Ubuntu PostgreSQL

1. Open PostgreSQL in Ubuntu and it will ask password

   sudo -u postgres psql
   

2. Go to your database

   \c <databasename>
   

3. Show all tables in your database

   SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
   

4. Show all data in the table in the table if you find the revision number (e.g., c140cfa017b4)

   select * from table name;
   

5. Drop the table

   DROP TABLE <table name>;
   

You can continue your work if it resolved