Prisma•8mo ago

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

uralsmhOP•8mo 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•8mo 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 😆

uralsmhOP•8mo 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?

uralsmhOP•8mo ago

jonfanz•8mo 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.

uralsmhOP•8mo 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•8mo 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.

uralsmhOP•8mo ago

ok I deleted all the records and re-run the npx prisma migrate dev, where seems worked now

Gaming

Programming

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