Prisma•3mo ago

Migrate dev failing in a project it used to work in. Says issue w/ user defined functions.

Hi there, I'm trying to update my schema, it hasn't been touched in this project for about a year and a half. when i run prisma migrate dev --name add_rbac_tables --create-only i get this error:

Error: P3006

Migration `20220617231725_update_bigint_fields_to_be_numbers` failed to apply cleanly to the shadow database.
Error:
ERROR: function date_in_user_timezone(timestamp without time zone, character varying) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:276

Error: P3006

Migration `20220617231725_update_bigint_fields_to_be_numbers` failed to apply cleanly to the shadow database.
Error:
ERROR: function date_in_user_timezone(timestamp without time zone, character varying) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:276

I have checked out the other posts with issues relating to 'failed to apply cleanly to the shadow database' both on github and here in the discord and haven't found anything that has helped me out of this situation. there are 80 migrations in this project, and there are many after the one that is giving trouble right now so i'm very confused why this is happening now. i know for certain that the function that says it does not exist, does exist in the actual database. also, dataloss matters here, i don't want to lose data in this case. any suggestions on how i might proceed? many thanks.

1 Reply

dekinaiyo•3mo ago

I managed to apply new migrations but the way I did it just feel so gross 😖 What I did was rename the migrations folder in the project, then delete all the migration records from the database. After that I ran pg_dump to get the schema from the database. Then I created a new migrations folder and put a timestamp_fresh_init folder in there and moved the schema.sql file from the pg_dump in there and renamed it to migrations.sql. Then I removed some of the stuff from the file that was not need, some of the setup at the top. After that I ran migrate resolve for the fresh init migration. I then checked the Prisma table in the database to be sure and there was indeed only one migration, the new baseline that contains the entire schema from the dump file. After doing that I was able to successfully run migrate dev create only. When I did so it found some changes that it said would need to be run but I was confused because it wanted to create a few tables that already existed. What I did was just edit the new migration file that was created to only have the info I wanted to put in (the rbac tables I was creating). Then I ran migrate deploy and my changes were successfully applied to the database. I was able to do it twice in a row, I added some sessions and connections tables after. The same odd thing happened where it was like “new index will be created” but the index for sure already exists in the database and the index wasn’t related to the change I had made in my Prisma schema file so I just manually discarded them and then ran migrate deploy and it also worked. So this seems to have worked but I don’t really like that I had to essentially disregard my entire migration history and re-baseline. I don’t know what happened that made it work a year and a half ago but not work now without the re-baseline.

Gaming

Programming

Migrate dev failing in a project it used to work in. Says issue w/ user defined functions.