https://www.prisma.io/ logo
Join SlackCommunities
Powered by
Hi All, I'm getting the below error when trying t...
# orm-help
a
Hi All, I'm getting the below error when trying to run the 
npx prisma migrate dev
command.
Copy code
Error: P3006

Migration `20210729200626_changing_the_id_and_id_referral_to_int` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: db error: ERROR: syntax error at or near ","
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine\connectors\sql-migration-connector\src\flavour\<http://postgres.rs:367|postgres.rs:367>
   1: sql_migration_connector::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\<http://lib.rs:322|lib.rs:322>
   2: migration_core::api::DevDiagnostic
             at migration-engine\core\src\<http://api.rs:89|api.rs:89>
The first error says that migration has failed and the next warning says that the migration was altered after it was applied. It's a bit confusing.
Copy code
Error: P3006
Migration `20210729200626_changing_the_id_and_id_referral_to_int` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: db error: ERROR: syntax error at or near ","
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine\connectors\sql-migration-connector\src\flavour\<http://postgres.rs:367|postgres.rs:367>
   1: sql_migration_connector::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\<http://lib.rs:322|lib.rs:322>
   2: migration_core::api::DevDiagnostic
             at migration-engine\core\src\<http://api.rs:89|api.rs:89>

PS C:\Users\ArunKadari\Documents\projects\cradleAPI> npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "cradle-local", schema "public" at "localhost:5432"

? - The migration `20210729200626_changing_the_id_and_id_referral_to_int` failed.
- The migration `20210729200626_changing_the_id_and_id_referral_to_int` was modified after it was applied.


We need to reset the PostgreSQL database "cradle-local" at "localhost:5432".
Do you want to continue? All data will be lost. » (y/N)
r
@Arun Kumar 👋 What was the change that you made in this migration?
a
Initially in the schema I made the the id as 
@default(autoinrement())
and ran the 
migrate dev
command. It generated a serial type and thrown an error that serial type doesn't exits. Then I manually edited the migration file to remove the serial type and made it as integer.
r
Could you pls open an issue here regarding this with the reproduction steps? The serial error shouldn’t occur normally so we would like to investigate this.
a
I created a test model in the schema to replicate the issue. It created a 
SERIAL
type in the migration file but the 
migrate dev
command didn't throw any error.
Copy code
model test2 {
  id         Int @id @default(autoincrement())
  idReferral Int @unique
}
Copy code
-- CreateTable
CREATE TABLE "test2" (
    "id" SERIAL NOT NULL,
    "idReferral" INTEGER NOT NULL,

    PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "test2.idReferral_unique" ON "test2"("idReferral");
r
Yes by default 
serial
will work and not throw any error. In your model was there any change you made before serial?
a
I don't remember exactly the sequence of changes I made that throwed the error. After it throwed that error I tried changing the id to just INT and apply the migration again.
Seems like if we alter the id schema, instead of 
serial
it's altering the migration file to a 
sequence
. For the other models the migration file is like below.
Copy code
ALTER TABLE "emergencyInfo" ALTER COLUMN "id" SET DEFAULT nextval('emergencyinfo_id_seq');
I tried changing the test2 model id to bigint and run the migration. This time it throwed the below error. Here is the migration file. The error code is 
Database error code: 42704
Copy code
ALTER TABLE "test2" DROP CONSTRAINT "test2_pkey",
ALTER COLUMN "id" SET DATA TYPE BIGSERIAL,
ADD PRIMARY KEY ("id");
Copy code
Database error:
ERROR: type "bigserial" does not exist
Here is the schema state when this error occured
Copy code
model test2 {
  id         BigInt @id @default(autoincrement())
  idReferral Int @unique
}
r
Could you open an issue with the above steps. We would like to have this recorded in the repo so that we can investigate this.
👍 1
a
Sure. Before that I want to be sure that I'm not doing the migration in a wrong way. I'll isolate this in a separate test app and test db and then raise the issue.
r
I think this might be happening because of change to serial so we might need to investigate the flow.
106 Views
Open in Slack
PreviousNext
Powered by