Title
a

Arun Kumar

07/30/2021, 8:18 AM
Hi All, I'm getting the below error when trying to run the
npx prisma migrate dev
command.
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.
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

Ryan

07/30/2021, 10:58 AM
@Arun Kumar 👋 What was the change that you made in this migration?
a

Arun Kumar

07/30/2021, 11:09 AM
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

Ryan

07/30/2021, 11:19 AM
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

Arun Kumar

07/30/2021, 11:31 AM
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.
model test2 {
  id         Int @id @default(autoincrement())
  idReferral Int @unique
}
-- 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

Ryan

07/30/2021, 11:35 AM
Yes by default
serial
will work and not throw any error. In your model was there any change you made before serial?
a

Arun Kumar

07/30/2021, 11:41 AM
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.
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
ALTER TABLE "test2" DROP CONSTRAINT "test2_pkey",
ALTER COLUMN "id" SET DATA TYPE BIGSERIAL,
ADD PRIMARY KEY ("id");
Database error:
ERROR: type "bigserial" does not exist
Here is the schema state when this error occured
model test2 {
  id         BigInt @id @default(autoincrement())
  idReferral Int @unique
}
r

Ryan

07/30/2021, 12:13 PM
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

Arun Kumar

07/30/2021, 12:25 PM
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

Ryan

07/30/2021, 12:39 PM
I think this might be happening because of change to serial so we might need to investigate the flow.