Hey guys, I am struggling to fix a migration issue and tried all the advice on the https://pris.ly/d/migrate-resolve link. I am trying to update my db with the latest pulled migrations (from repo). However, Prisma is stuck on a particular migration. I tried: • Resetting the db (its dev env. so its ok for now)| • Tried the

rolled-back

command and then then the

migrate dev

/

migrate reset

again, to no success The errors I am getting are: • for `migrate reset`:

Error: P3018

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: 20210722071621_updated_enum_activity_type_to_include_invite_and_message

Database error code: 25001

Database error:
ERROR: ALTER TYPE ... ADD cannot be executed from a function or multi-command string

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("25001"), message: "ALTER TYPE ... ADD cannot be executed from a function or multi-command string", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("xact.c"), line: Some(3237), routine: Some("PreventTransactionChain") }

• for

rolled back

and then `migrate dev`:

Error: P3006

Migration `20210722071621_updated_enum_activity_type_to_include_invite_and_message` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: ALTER TYPE ... ADD cannot be executed from a function or multi-command string
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:373
   1: sql_migration_connector::validate_migrations
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:317
   2: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:89

How to resolve this?

Hey Skhan - I feel the comment is actually pretty clear on whats the problem. The question is how could we improve this so it becomes more clear. Should we consider generating two separate migration files? Any suggestion? what you would find most appropriate?

@SKhan also upgrading to PostgreSQL 12 or more recent would avoid this, is there anything blocking you for the upgrade maybe?

@Frederik, yes, the comment is very clear & is the reason I managed to resolve it. My suggestion was that if this is an error on postgres (<12) & as prisma is aware of this issue, the it should create 2 migration files instead of one. I did not occur to me to review the migration files as I just presumed that Prisma has done is correctly. @Joël I really should. I installed postgres 10 last year around October, and cannot recall how I managed to install version 10 instead of the latest whatever version was back then. I will definitely look into this.

Maybe the CLI could output a warning when the migration file with the comment is created. However, given the “old” version of postgres used as well as the already helpful comment, I feel its unlikely we will find the time to improve this. Sorry for the inconvenience!

Thats fair. Maybe even having some reference to comments in troubleshooting docs page might be a quick solution. Thanks for the wonderful product.

hey @SKhan - we have a proposal out for a new diffing tool. Please take a look and let us know what you think: https://github.com/prisma/prisma/issues/10561