Cannot migrate or upgrade database using Flask-Migrate (Alembic)

Question

I am using Flask-Migrate (Alembic) to update my database. I updated the models.py file, however I made a mistake. I migrated and went to update the database, however I got this error:

 sqlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1215, 'Cannot add foreign key constraint') [SQL: u'\nCREATE TABLE topics (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\t`subjectID` INTEGER, \n\ttopic VARCHAR(150) NOT NULL, \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(`subjectID`) REFERENCES subjects (id)\n)\n\n'] 

What I did was db.Text instead of db.Integer for the foreign key.

When I try to start a new migration, I get the following:

 alembic.util.CommandError: Target database is not up to date. 

So now I'm stuck. I cannot update the database or migrate. I tried to upgrade to an earlier version of the database using something like this:

 python manage.py db downgrade --sql b877018671c:36949b1cca31 

But when I run python manage.py db current , I get the latest version of the database I'm stuck in.

Is there a fix for this? Thanks.

Answer 1

Alembic stores the version of db in the table that it creates is called alembic_version . This table contains one field and alembic_version.version_num row. Make sure the value for this matches the file name of the last file in migrations/version . This version number is also contained within the revision file in the revision variable, which is usually displayed on line 26 of the file. Make sure it matches the db version.

Another option is to simply remove the db and create it using alembic. If this is a development environment where data is not important, this will be my recommendation.

Answer 2

I feel the accepted answer is a bit more complicated. I had the same problem, and I decided that it was easy to remove the migration containing the encoding errors. You do not need this because, again, it was incorrectly encoded. Locate the last migration in the migrations/versions folder, delete it, then migrate again and update. You do not need to delete data in your database in order to transfer it.