P
Prisma2mo ago
uralsmh

ERROR: column "filename" of relation "ActionDependencyList" contains null values

Sorry for the noob question but I am a bit confused what should I do after this error. Can someone help me please?
npx prisma migrate dev  ✔  16s   22:16:18 
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "watchtower_db", schema "public" at "localhost:5432"

Applying migration `20240524201618_`
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: 20240524201618_

Database error code: 23502

Database error:
ERROR: column "filename" of relation "ActionDependencyList" contains null values

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E23502), message: "column \"filename\" of relation \"ActionDependencyList\" contains null values", detail: None, hint: None, position: None, where_: None, schema: Some("public"), table: Some("ActionDependencyList"), column: Some("filename"), datatype: None, constraint: None, file: Some("tablecmds.c"), line: Some(6046), routine: Some("ATRewriteTable") }
npx prisma migrate dev  ✔  16s   22:16:18 
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "watchtower_db", schema "public" at "localhost:5432"

Applying migration `20240524201618_`
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: 20240524201618_

Database error code: 23502

Database error:
ERROR: column "filename" of relation "ActionDependencyList" contains null values

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E23502), message: "column \"filename\" of relation \"ActionDependencyList\" contains null values", detail: None, hint: None, position: None, where_: None, schema: Some("public"), table: Some("ActionDependencyList"), column: Some("filename"), datatype: None, constraint: None, file: Some("tablecmds.c"), line: Some(6046), routine: Some("ATRewriteTable") }
8 Replies
uralsmh
uralsmh2mo ago
This is my model where I made some changes.
model ActionDependencyList {
repository_id BigInt
repository_name String
filename String
actions String[]
repository Repository @relation(fields: [repository_id], references: [repository_id])

@@id([repository_id, filename])
}
model ActionDependencyList {
repository_id BigInt
repository_name String
filename String
actions String[]
repository Repository @relation(fields: [repository_id], references: [repository_id])

@@id([repository_id, filename])
}
Output of migration.sql when I ran npx prisma migrate dev
/*
Warnings:

- The primary key for the `ActionDependencyList` table will be changed. If it partially fails, the table could be left without primary key constraint.
- The `actions` column on the `ActionDependencyList` table would be dropped and recreated. This will lead to data loss if there is data in the column.
- Added the required column `filename` to the `ActionDependencyList` table without a default value. This is not possible if the table is not empty.

*/
-- AlterTable
ALTER TABLE "ActionDependencyList" DROP CONSTRAINT "ActionDependencyList_pkey",
ADD COLUMN "filename" TEXT NOT NULL,
DROP COLUMN "actions",
ADD COLUMN "actions" TEXT[],
ADD CONSTRAINT "ActionDependencyList_pkey" PRIMARY KEY ("repository_id", "filename");

-- AddForeignKey
ALTER TABLE "ActionDependencyList" ADD CONSTRAINT "ActionDependencyList_repository_id_fkey" FOREIGN KEY ("repository_id") REFERENCES "Repository"("repository_id") ON DELETE RESTRICT ON UPDATE CASCADE;
/*
Warnings:

- The primary key for the `ActionDependencyList` table will be changed. If it partially fails, the table could be left without primary key constraint.
- The `actions` column on the `ActionDependencyList` table would be dropped and recreated. This will lead to data loss if there is data in the column.
- Added the required column `filename` to the `ActionDependencyList` table without a default value. This is not possible if the table is not empty.

*/
-- AlterTable
ALTER TABLE "ActionDependencyList" DROP CONSTRAINT "ActionDependencyList_pkey",
ADD COLUMN "filename" TEXT NOT NULL,
DROP COLUMN "actions",
ADD COLUMN "actions" TEXT[],
ADD CONSTRAINT "ActionDependencyList_pkey" PRIMARY KEY ("repository_id", "filename");

-- AddForeignKey
ALTER TABLE "ActionDependencyList" ADD CONSTRAINT "ActionDependencyList_repository_id_fkey" FOREIGN KEY ("repository_id") REFERENCES "Repository"("repository_id") ON DELETE RESTRICT ON UPDATE CASCADE;
jonfanz
jonfanz2mo ago
So, essentially what you're doing is adding the filename column along with other stuff (including a composite primary key) Since you already have rows in your database, all of those rows are going to have null for filename (since you just added it) But you've said that filename cannot be null, so that's where you get there error. To fix it you can either give the filename field a default value, make it nullable, or remove all rows from your database 😆
uralsmh
uralsmh2mo ago
@Jon Harrell is there any other command do I have to run before npx prisma migrate? And what I understood, I need to delete all records in my database where i ve some records, am I right? Should I run npx prisma reset before the npx prisma migrate dev?
uralsmh
uralsmh2mo ago
No description
jonfanz
jonfanz2mo ago
So, that's one option. I'm not sure if it's the one that I would take. It depends on your needs. Here are the Prisma Migrate docs Personally I would update your column to be nullable or to have a default value.
Prisma CLI reference | Prisma Documentation
This page gives an overview of all available Prisma CLI commands, explains their options and shows numerous usage examples.
uralsmh
uralsmh2mo ago
Seems the column is not created btw.. Sorry for dump question but how should I update the column to be nullabable?
Personally I would update your column to be nullable or to have a default value.
Shall I edit below part manually in migration.sql? ADD COLUMN "filename" TEXT NOT NULL Or run first DELETE FROM public."ActionDependencyList"; and then run again npx prisma migrate dev ?
jonfanz
jonfanz2mo ago
The column isn't created because the migration failed. When a migration fails, nothing inside of it succeeds. So that's why that is happening. Here's info on making a field optional And here's info on defining a default value
Models | Prisma Documentation
Learn about the concepts for building your data model with Prisma: Models, scalar types, enums, attributes, functions, IDs, default values and more.
uralsmh
uralsmh2mo ago
ok I deleted all the records and re-run the npx prisma migrate dev, where seems worked now