Prisma•6mo ago

Enum failed to apply cleanly to database

I'm having issues creating new migrations for my cockroachdb prisma layout. I have one migration named 20240119154759_tag_naming_update_grant_redef with the contents:

ALTER TYPE "ExampleType" ADD VALUE 'GRANT';

ALTER TYPE "ExampleType" ADD VALUE 'GRANT';

and 20240129071034_split_renaming_migrations with the contents:

UPDATE "Example"
SET "type" = 'GRANT'
WHERE "type" = 'CREATE';

ALTER TYPE "ExampleType" DROP VALUE 'CREATE';

UPDATE "Example"
SET "type" = 'GRANT'
WHERE "type" = 'CREATE';

ALTER TYPE "ExampleType" DROP VALUE 'CREATE';

I get the error running (npx prisma migrate dev --name unrelated_migration --create-only --schema prisma/cockroach/cockroach.prisma):

Error: P3006

Migration `20240129071034_split_renaming_migrations` failed to apply cleanly to the shadow database.
Error:
ERROR: invalid input value for enum ExampleType: "GRANT"
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:281

Error: P3006

Migration `20240129071034_split_renaming_migrations` failed to apply cleanly to the shadow database.
Error:
ERROR: invalid input value for enum ExampleType: "GRANT"
   0: schema_core::state::DevDiagnostic
             at schema-engine/core/src/state.rs:281

only when running migrations against the shadow db; running npx prisma migrate reset fully applies correctly Not sure if this is a regression with how the migration stuff works but it happened between the 5.22.0 -> 6.0.0 update The migration succeeds in 5.22.0 They're in different files because of some surrounding bits unrelated to that specific piece of the migration; however this migration setup itself should work. The initial table in the init migration looks like this which is timestamped before the prior 2:

CREATE TABLE "Example" (
    "type" "ExampleType" NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "exampleId" STRING NOT NULL,

    CONSTRAINT "Example_pkey" PRIMARY KEY ("exampleId")
);

CREATE TABLE "Example" (
    "type" "ExampleType" NOT NULL,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "exampleId" STRING NOT NULL,

    CONSTRAINT "Example_pkey" PRIMARY KEY ("exampleId")
);

this is my prisma schema config:

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

generator json {
  provider = "prisma-json-types-generator"
}

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

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

generator json {
  provider = "prisma-json-types-generator"
}

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

Looking forward for any help, I can make a github issue with this outlined as well, I didn't notice anything similar

4 Replies

Prisma AI Help•6mo ago

You opted for speed from this bot. Let's conjure up an answer right now! Your issue is likely related to how CockroachDB and Prisma handle enum migrations, especially when updating enum values and then dropping old ones in separate migration files. The error:

ERROR: invalid input value for enum ExampleType: "GRANT"

ERROR: invalid input value for enum ExampleType: "GRANT"

suggests that, during shadow database creation, the migration that updates the data (UPDATE "Example" SET "type" = 'GRANT' ...) is being run before the enum value 'GRANT' has actually been added to the type in the shadow DB. This ordering problem can occur if migrations are split across files and the shadow DB is rebuilt from scratch, as Prisma does during migrate dev. This is a known class of issue with enum migrations in both PostgreSQL and CockroachDB, where you must ensure that the enum value is added before any data updates or schema changes that reference it. If the migrations are in separate files, the shadow DB may not have the new value available when the update runs, causing the error you see. This is consistent with similar issues reported in the Prisma repo, where enum changes and their usage must be carefully ordered in migrations to avoid failures during shadow DB creation or migration application see related issue. Workaround:
- Combine the ALTER TYPE ... ADD VALUE and the UPDATE ... into a single migration file, ensuring the enum value is added before any data updates. - Alternatively, ensure that the migration adding the enum value is always applied before any migration that references it, and that your migration history is linear and complete. This issue is not unique to your setup and has been observed in other enum migration scenarios with CockroachDB and Prisma see also. If this worked in 5.22.0 but fails in 6.0.0, it may be due to changes in how Prisma applies migrations to the shadow database. If the problem persists, consider opening a GitHub issue with your detailed reproduc tion steps. 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

fixedOP•6mo ago

It seems like the github link recommends splitting up the enum creations, if you look at the migration timestamps they should be ordered in a way the first one is happening before the other one If these are happening in parallel then yeah that would break things but the order of the migrations is specific and necessary https://github.com/prisma/prisma/issues/8424#issuecomment-1500073780 https://github.com/prisma/prisma/issues/8424#issuecomment-1923714183 with those 2 issue comments in mind it seems unreasonable to push them both at the same time considering enums need to be added to and used in separate transactions (one of the initial issues of why it was separated in the first place) it seems to also fail if those 2 are in the same migration at all, not sure what's going on with it

Nurul•6mo ago

Are you still running into this issue?

fixedOP•6mo ago

I downgraded and got the migration in using 5.22, not sure if the issue is still a problem though

Gaming

Programming

Enum failed to apply cleanly to database

Did you find this page helpful?