Migration failed to apply cleanly to shadow database about prisma HOT 5 OPEN

Is there a way in the short term to turn off the shadow db check?

No, it is not only a "shadow database check" but actually how Prisma figures out which SQL has to be executed to bring your database into the desired state. You can of course avoid that by not using migrate dev and creating the migration file by hand - but I assume that would defeat the purpose.

The real question here is why this is failing on some of your database servers. Any idea what could be configured differently there to cause a migration to fail? Can you maybe enable query logging on the database level, and see what is being executed under the hood - and where it fails with which error? Seems Can't reach database server at ... is a misleading error message on our side which somehow is triggered when something else goes wrong.

from prisma.

Yeah, we can enable some query logging. Since posting we've tried increasing the connect timeout and query timeouts on the db but with no success.

Is there a way to turn on query logging on the prisma side that would apply to the migrate dev script? Or should we do this on the DB side. Just thinking that client side would be a bit easier to figure out.

from prisma.

Unfortunately not for migrations, sorry.

from prisma.

Ok so did a little investigation here as this is becoming a bigger problem. I see that what's happening in the error log is I'm getting dropped connections.

error log

2024-06-11T17:09:45.784337Z 7118 [Note] [MY-010914] [Server] Aborted connection 7118 to db: 'unwrap_v2' user: 'admin' host: '174.75.65.122' (Got an error reading communication packets). (sql_connect.cc:828)
2024-06-11T17:11:54.424896Z 7120 [Note] [MY-010914] [Server] Aborted connection 7120 to db: 'prisma_migrate_shadow_db_35e045c5-4582-4547-aac0-11546b2486f4' user: 'admin' host: '174.75.65.122' (Got an error reading communication packets). (sql_connect.cc:828)
2024-06-11T17:12:10.307386Z 7119 [Note] [MY-010914] [Server] Aborted connection 7119 to db: 'unwrap_v2' user: 'admin' host: '174.75.65.122' (Got an error reading communication packets). (sql_connect.cc:828)
2024-06-11T17:14:37.623637Z 7146 [Note] [MY-010914] [Server] Aborted connection 7146 to db: 'unwrap_v2' user: 'admin' host: '174.75.65.122' (Got an error reading communication packets). (sql_connect.cc:828)
2024-06-11T17:16:42.727038Z 7148 [Note] [MY-010914] [Server] Aborted connection 7148 to db: 'prisma_migrate_shadow_db_3e1ed3f6-dca7-46df-b202-5d0ba119847a' user: 'admin' host: '174.75.65.122' (Got an error reading communication packets). (sql_connect.cc:828)
2024-06-11T17:16:56.823406Z 7147 [Note] [MY-010914] [Server] Aborted connection 7147 to db: 'unwrap_v2' user: 'admin' host: '174.75.65.122' (Got an error reading communication packets). (sql_connect.cc:828)

I look at RDS performance insights, our dev db's run in AWS RDS on very small machines, and I see that the migrate dev operation is smoking the CPU with operations that look like the shadow db migration (see screenshot).

We have 190 migrations in our migrations directory, is this too many? Anecdotally it seems like this problem has grown with our DB schema. migrate dev used to run very quickly and now takes forever 1-2 min to run and intermittently fails with this error mentioned above.

from prisma.

Interesting, it seems that this database is not up to the task to apply these 190 migrations in the shadow database regularly. That is the first time this has been reported to us. Can you test out if a bigger database has less trouble applying them?

If you do not need the migrations any more, it might be worth squashing them all together: https://www.prisma.io/docs/orm/prisma-migrate/workflows/squashing-migrations#how-to-create-a-clean-history-in-a-production-environment

from prisma.