Prisma migration is bad state

Question

We seem to have gotten our prisma migrations into a bad state. When we get the latest code and run

prisma migrate dev

we get

Migration 20210819161149_some_migration failed to apply cleanly to the shadow database. Error code: P3018 Error: A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20210819161149_some_migration

Database error code: 1065

All the migrations in source control do match the ones in the _prisma_migrations table so I'm not sure why it thinks 20210819161149_some_migration failed. There is nothing in the logs column in _prisma_migrations for that record. I think what happened is a developer applied the migration then changed the migration.sql for it after the fact.

Anyway, we followed the steps outlined https://pris.ly/d/migrate-resolve but they don't seem to resolve the issue. It first suggests running

prisma migrate resolve --rolled-back "20210819161149_some_migration"

but that results in

Error: P3012

Migration 20210819161149_some_migration cannot be rolled back because it is not in a failed state.

So then we tried to mark it as applied

prisma migrate resolve --applied "20210819161149_some_migration"

but that results in this error

Error: P3008

The migration 20210819161149_some_migration is already recorded as applied in the database.

We also tried running

 prisma migrate deploy

Which gives

13 migrations found in prisma/migrations WARNING The following migrations have been modified since they were applied: 20210819161149_some_migration

but you still get the same issue above when running prisma migrate dev.

Is there any way to get prisma happy again without deleting all the data?

Footstalk

have you tried to use prisma format i think will help you! – Steck

@EliasSalom prisma format is not relevant or helpful at all for this question. – Engulf

Answer 1

A possible workaround would involve baselining your development database based on your current schema/migration history. You would need a separate backup database and you will lose your existing migration history, but it should retain your data.

Here's what the process would look like

1. Delete all migration history from your prisma folder as well as the _prisma_migrations table in your database.
2. Create a new backup database.
3. Connect the project to your backup database and run prisma migrate dev --name baseline_migration . This will generate a migration matching your current prisma schema.
4. Connect back to your main Database and baseline the generated migration by running prisma migrate resolve --applied 20210426141759_baseline_migration (The numbers at the beginning of your migration name will differ).

The reason you'd be creating a backup database and running the initial baseline migrations in that backup database is because you don't want to lose the data in your main database. I realize this is not an ideal solution, but it might work if it's very important for you to keep your data while retaining your existing dev workflow.

This article on Adding Prisma Migrate to an existing project is also worth a read.

Weatherspoon

Great answer. It's very useful. – Saturday

Answer 2

My advice for tricky situations like this is to go to the _prisma_migrations table in the database, find the failed migration row, and delete it. Usually, the failed migration row in the database is the last one when sorted by started_at in ascending order. Note that the row will have a logs column, which usually contains the reason why the database migration failed. Keep note of the message and then delete the migration row.

Then go to your code, delete the migration.sql file and its parent folder (e.g. /20231212000000_migration_name/migration.sql), and then try to re-deploy your app.

This should bring you back to your last stable state. Then, you can work from there to assess why your DB migration was problematic.

Dibromide

I don't know if this is the supported path but it worked for me. Thank you! – Coveney

Answer 3

I had a very similar problem.

I tried:

prisma migrate resolve --rolled-back "20210819161149_some_migration"

that succeeded, but didn't unblock prisma migrate.

After half an hour of confusion and pain, I realized I was running prisma migrate resolve against the wrong database.

Tiernan

Answer 4

https://www.prisma.io/docs/reference/api-reference/error-reference this site will provide you the the correct ways of troubleshooting your problem. I got a migration error (P3009) and when i investigate through the generated sql file and found that some syntax issue occured in the generated file and resolved them and fix the migration issue

Falsehood

Answer 5

I had a similar issue and it's probably not the best approach, but I ended up connecting to the db with the sqlite3 command and fixing stuff manually along with deleting the failed migration id from _prisma_migrations.

Wino

Answer 6

You can do how it`s described in this short video https://youtu.be/BIfvmEhbtBE

Lusterware

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Legendary