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?

Irreligion

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.

Enterostomy

As a note to people working on someone else's project, the path to the database is usually found in /alembic.ini – Curative

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 table alembic_version 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.

Ornithology

How can this solution be adapted for a sqllite database? – Neighboring

How would I find out the revision/version id of the current state of my Postgres-db? – Eastbourne

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.

Cassis

Thanks! Based on this I was able to fix my database without losing data, My database was on a diverged version which was removed, I did the steps mentioned in this answer, and after that I manually changed my alembic version to my latest valid migration. and it perfectly worked! – Banket

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

Psid