Hi All I m getting the below error when trying to run the `n

Question

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 1 sql migration connector validate migrations at migration engine connectors sql migration connector src 2 migration core api DevDiagnostic at migration engine core src ```

Arun Kumar · Answer

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 1 sql migration connector validate migrations at migration engine connectors sql migration connector src 2 migration core api DevDiagnostic at migration engine core src PS C Users ArunKadari Documents projects cradleAPI gt 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 ```

Ryan · Answer

< Arun Kumar> wave What was the change that you made in this migration

Arun Kumar · Answer

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

Ryan · Answer

Could you pls open an issue <https github com prisma prisma issues new assignees= amp labels=kind 2Fbug amp template=bug report yml|here> regarding this with the reproduction steps The serial error shouldn t occur normally so we would like to investigate this

Arun Kumar · Answer

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 ```

Ryan · Answer

Yes by default `serial` will work and not throw any error In your model was there any change you made before serial

Arun Kumar · Answer

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

Arun Kumar · Answer

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 ```

Arun Kumar · Answer

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 ```

Ryan · Answer

Could you open an <https github com prisma prisma issues new assignees= amp labels=kind 2Fbug amp template=bug report yml|issue> with the above steps We would like to have this recorded in the repo so that we can investigate this

Arun Kumar · Answer

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

Ryan · Answer

I think this might be happening because of change to serial so we might need to investigate the flow