Unable to reset CockroachDB for table with foreign key relationship on v22.2.

[AndrewSouthpaw]

Bug description

On CRDB v22.2, it is not possible to run npx prisma migrate reset for a database that has a table with a foreign key relationship:

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error:
db error: ERROR: "Message" is referenced by foreign key from table "MessageLikedUser"
   0: sql_migration_connector::best_effort_reset
           with namespaces=None
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:329
   1: migration_core::state::Reset
             at migration-engine/core/src/state.rs:394

It works as expected on CRDB v22.1.x.

How to reproduce

Pull down this repo: https://github.com/AndrewSouthpaw/crdb-prisma-migration-repro-2022-12-12/tree/andrew/reset-repro. Be sure to use the branch reset-repro.

Instructions in the README to repro. Instructions here to download the CRDB binary and you could run the cluster with:

$ cockroach start-single-node --insecure --listen-addr=localhost

Expected behavior

npx prisma migrate reset works normally, but instead it errors out.

Prisma information

generator client {
  provider        = "prisma-client-js"
}

datasource db {
  provider = "cockroachdb"
  url      = env("DATABASE_URL")
}

model Message {
  id String @id @db.Uuid @default(dbgenerated("gen_random_uuid()"))
  likedUsers MessageLikedUser[]
}

model MessageLikedUser {
  id String @id @db.Uuid @default(dbgenerated("gen_random_uuid()"))
  messageId String @db.Uuid
  message Message @relation(fields: [messageId], references: [id], onDelete: Cascade, onUpdate: Cascade)
}

Environment & setup

• OS: macOS 12.4
• Database: CRDB v22.2
• Node.js version: 16.10.0

Prisma Version

4.8.0

[rafiss]

CockroachDB developer here:

This seems to be another manifestation of #16909

I see that the migration file already has SET enable_implicit_transaction_for_batch_statements = false;. That worked for allowing the npx prisma migrate dev command to succeed. But the issue is that when Prisma executes npx prisma migrate reset, it does not use the enable_implicit_transaction_for_batch_statements setting.

• The immediate workaround is to separate out each migration.sql file so it has only one command in each.
• The next short-term fix here is for Prisma to use the enable_implicit_transaction_for_batch_statements setting for prisma migrate reset.
• Longer term, CockroachDB will keep adding support for these types of statements when inside of a multi-statement transaction.

[Jolg42]

Note: I could not reproduce the same error message with the repo provided, I only get db error: ERROR: column "balance" being dropped, try again later like in #16909

npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": CockroachDB database "defaultdb", schema "public" at "localhost:26257"

Error: P3006

Migration `20221212204348_breaking_migration` failed to apply cleanly to the shadow database. 
Error:
db error: ERROR: column "balance" being dropped, try again later
   0: sql_migration_connector::validate_migrations
           with namespaces=None
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:297
   1: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:264

npx prisma migrate reset

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": CockroachDB database "defaultdb", schema "public" at "localhost:26257"

✔ Are you sure you want to reset your database? All data will be lost. … yes

Applying migration `20221212204329_setup`
Applying migration `20221212204348_breaking_migration`
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: 20221212204348_breaking_migration

Database error code: 55000

Database error:
ERROR: column "balance" being dropped, try again later

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E55000), message: "column \"balance\" being dropped, try again later", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("add_column.go"), line: Some(244), routine: Some("checkColumnDoesNotExist") }

[AndrewSouthpaw]

@Jolg42 could you confirm you're on the reset-repo branch? It should break on the subsequent migration, 20221217201313_migration_will_break_on_reset, which is the one that really matters (setting up a different schema). You could also just delete middle migration probably.

[ShavaShav]

I'm experiencing this issue, with an empty database and 0 migrations. The first db push succeeds, but any subsequent change to schema that results in db push dropping data fails like:

✔ To apply this change we need to reset the database, do you want to continue? All data will be lost. … yes

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
ERROR: "Message" is referenced by foreign key from table "MessageLikedUser"
   0: sql_schema_connector::best_effort_reset
           with namespaces=None
             at schema-engine/connectors/sql-schema-connector/src/lib.rs:356
   1: schema_core::state::Reset
             at schema-engine/core/src/state.rs:428

Afaik, onDelete=CASCADE is specifically made for dealing with this (by cascade deleting MessageLikeUser records which have a foreign key to the Message being deleted). It's even stranger as these tables are completely empty, so not sure why prisma is panicing in the first place.

[Jolg42]

@ShavaShav Could you open a new issue?

It would be interesting if you go get a SQL dump to verify that that database is really empty.