Second run of prisma migrate deploy fails

[jacob-regan-tempus]

I have my startup script for CI setup to run prisma migrate deploy before running my main app process. On first run, the migration was successful. On second run (with no schema changes to the database) I ran into the following error: Error querying the database: db error: ERROR: relation "_prisma_migrations" already exists. Wondering why this table collision happens since the table should persist to track schema changes over time.

[janpio]

For some reason it thinks it does not exist and needs to be created again. Can you share some more information about your setup? Prisma version, what kind of migration is in the first run and so on? What database and version? Do you have some special permissions/grants/user setup on that database?

[janpio]

We had a look at that code, and it basically reads like this:

1. If _prisma_migrations exists: Exit OK
2. If there are non system tables: Exit with error message
3. Otherwise: CREATE TABLE _prisma_migration

(If you feel like reading some Rust code: https://github.com/prisma/prisma-engines/blob/f2e63a76e8eed99d99f0eb65596c7677b70a1e77/migration-engine/connectors/sql-migration-connector/src/sql_migration_persistence.rs#L16-L40)

And your second migrate deploy seems to fall straigt into 3) as it does not find the table but also no others (which is weird - you said it successfully created the tables, right?).

The only thing I can think of is that the database user you are runnig with can create the tables, but not see them later. Can you run npx prisma db pull with it and see what is output or happens to your schema file? That might be interesting if that also detects the database as empty.

[jacob-regan-tempus]

Thanks for linking the code. And yes, seems like its falling into that 3rd block. It is able to successfully create the tables on first run. I was able to log in to the db and confirm the schema was created. That would really surprise me if the user was not able to list the tables. Is it possible that self.flavour().migrations_table_name() evaluates to something unexpected? Seems unlikely...and you probably would have run into this before if it were the case, but its possible. I did notice that the flavour linked to was SQL. Is it possible that there is just a slight variation between that and Postgres in this case? My migration table is written with a leading underscore: _prisma_migrations. Could be that self.flavour().migrations_table_name() evaluates to prisma_migrations or something and so it doesn't catch it?

[janpio]

No, that is just an internal function name that then gets turned into the correct string. If this was a general problem with PostgreSQL, we doul have many more people contacting us about this - which is also why I suspect some more weird root cause.

This might be worth trying as we could learn something:
The only thing I can think of is that the database user you are runnig with can create the tables, but not see them later. Can you run npx prisma db pull with it and see what is output or happens to your schema file? That might be interesting if that also detects the database as empty.

[jacob-regan-tempus]

Ok, we have an update here! Finally got it working. Ended up adding &schema=<servicename> in our postgres DATABASE_URL variable. It defaults to public when you don't provide it. For some reason this worked fine locally but did not work properly in our lower deployed environments.

[jacob-regan-tempus]

Only took us 2 weeks to get here 😅 thanks again for jumping in and assisting. The Rust code you pointed us to ended up giving us enough context to debug in the right direction.

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that haven’t had any recent activity and appear to be outdated.

If this discussion is still important to you or unresolved, we’d love to hear from you! Feel free to reopen it or start a new one with updated details.

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and being part of the community!