multiSchema: migrate dev fails to apply cleanly to shadow database after custom migration renames table in secondary schema

[andyjy]

Bug description

Observed using 4.8.0-dev.41 - this issue becomes visible now #16561 is resolved (thanks for fixing that one!)

Update: this issue only occurs when specifying shadowDatabaseUrl manually in schema.prisma - it works as expected when this is removed.

The fix to #16561 means migrate reset is now successful. However, subsequent attempts to run migrate dev immediately after migrate reset (i.e. no schema drift) and a migration history containing a `ALTER TABLE "second.table1" RENAME TO "table2" fails with error:

Error: P3006

Migration `<migration_name>` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: relation "table2" already exists
   0: sql_migration_connector::validate_migrations
           with namespaces=None
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:295
   1: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:264

I'm not sure what's going wrong here - via psql I can see the table now exists in the shadow database second under both names table1 and table2 - as if the rename migration is successful but then somehow the original table has been recreated. Perhaps a similar reset logic bug not resetting secondary schemas correctly during work on the shadow database? But I'm just speculating here (I don't know the full implementation details for how migrate dev interacts with the shadow database).

How to reproduce

previewFeatures = ["multiSchema"]

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("DATABASE_URL_SHADOW")
  schemas  = ["first", "second"]
}

model Example {
  @@schema("second")
  ...
}

migration.sql:

ALTER TABLE "second"."table1" RENAME TO "table2";

Expected behavior

No response

Prisma information

% npx prisma -v
prisma                  : 4.8.0-dev.41
@prisma/client          : 4.8.0-dev.41
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine ddfde0aabd6544e4740cc1b4ef9a682c02b813d2 (at ../../node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli ddfde0aabd6544e4740cc1b4ef9a682c02b813d2 (at ../../node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core ddfde0aabd6544e4740cc1b4ef9a682c02b813d2 (at ../../node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt ddfde0aabd6544e4740cc1b4ef9a682c02b813d2 (at ../../node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.8.0-28.ddfde0aabd6544e4740cc1b4ef9a682c02b813d2
Default Engines Hash    : ddfde0aabd6544e4740cc1b4ef9a682c02b813d2
Studio                  : 0.479.0

Environment & setup

• OS: macOS
• Database: PostgreSQL 14
• Node.js version: v16.15.1

Prisma Version

4.8.0-dev.41

[tomhoule]

Is your shadow database URL pointing to a different logical database, or to a different schema in the same database?

[andyjy]

Is your shadow database URL pointing to a different logical database, or to a different schema in the same database?

Different logical database on the same host. (specifically named _shadow_db-core)

DATABASE_URL=postgresql://postgres:password@localhost:5432/db-core?
DATABASE_URL_SHADOW=postgresql://postgres:password@localhost:5432/_shadow_db-core?

I am no longer blocked personally, having discovered this - I was specifying shadowDatabaseUrl within my schema.prisma to support remote development databases in some environments - so thought I needed to specify for all cases. I'm now passing empty string to shadowDatabaseUrl (not sure if "empty string = same as not specified" is supported behaviour or just happens to work). I'm not currently utilising remote dev databases so can simply remove this for now. But I think the issue remains for anyone wanting to do so.

Thanks!

[tomhoule]

Interesting, we should definitely dig deeper. Thanks for reporting the issue!

[pimeys]

Can confirm. You have to reset first, then migrate dev two times in a row to get the error.

[ridwansameer]

Sorry but what's the solution for this?

I'm baselining a new database so I've done a migrate diff to generate the original SQL file and marked it as resolved. The next migrate dev still creates a migration (Not sure, might be something wrong with my DB but its just changing some FK ID's). The next migrate dev however fails with the same error as OP. I'm specifying a shadow database on the same host but different logical database

[janpio]

The bug was closed, so we assume it was fixed. If it is not fixed for you, please open a new bug issue and include all the information the bug issue template asks for. Thanks.