Drizzle Team•6mo ago

there is no unique or exclusion constraint matching the ON CONFLICT specification

Having this issue that I've seen others also getting, but without explanation. I'm only getting this error in production, not in my local dev environment.

.onConflictDoUpdate({
  target: publishedTasks.taskId,
  set: {
    showName: input.showName,
    showSchool: input.showSchool,
  },
})

.onConflictDoUpdate({
  target: publishedTasks.taskId,
  set: {
    showName: input.showName,
    showSchool: input.showSchool,
  },
})

I have a target, publishedTasks.taskId :

taskId: varchar("task_id")
  .primaryKey()
  .references(() => tasks.id, {
    onDelete: "cascade",
  })

taskId: varchar("task_id")
  .primaryKey()
  .references(() => tasks.id, {
    onDelete: "cascade",
  })

What am I doing wrong here?

28 Replies

rphlmr ⚡•6mo ago

taskId is a primary key and also a foreign key?

benjickOP•6mo ago

Is that not something I can do? It's a 1:1

rphlmr ⚡•6mo ago

Yes, I was just asking because without the full schema, I had a doubt. I can't reproduce too on local (with integer as PK but it should not matter)

benjickOP•6mo ago

Can't replicate it locally either, so annoying

rphlmr ⚡•6mo ago

I wonder if it is possible that a constraint is missing in prod 🤔

benjickOP•6mo ago

You're actually right Damn, I tried comparing before, but I didn't check index constraints, only the data structure

rphlmr ⚡•6mo ago

This should not happen though. Is it possible that something other than Drizzle has altered the schema? or maybe a wrong migration on a buggy version of Kit

benjickOP•6mo ago

I see an error when running the migrations now:

error: column publishedTasks.show_name does not exist

rphlmr ⚡•6mo ago

but it would not work on local too Nothing else than Drizzle should alter your db. Do you think it is a possibility?

benjickOP•6mo ago

No, I don't think so, only I have access and I don't change anything manually

rphlmr ⚡•6mo ago

I have seen some issues from other people with db viewer like the Jetbrains one is it present in the db? (never used drizzle-kit push?)

benjickOP•6mo ago

I'm trying to see if the latest migration is "marked as run", but I see the drizzle-migrations table have more rows than I have migrations Yes, I see it in the db, so it has ran. I don't use push for prod

rphlmr ⚡•6mo ago

More rows than migrations; it looks like some migrations were dropped in the folder.

benjickOP•6mo ago

[email protected], might be time to upgrade Hm, I don't think so, never removed anything after merging to main I think So annoying. And the weird part, is last migration was only adding two fields, booleans This:

ALTER TABLE "merlin_published_tasks" ADD COLUMN "show_name" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "merlin_published_tasks" ADD COLUMN "show_school" boolean DEFAULT true NOT NULL;

ALTER TABLE "merlin_published_tasks" ADD COLUMN "show_name" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "merlin_published_tasks" ADD COLUMN "show_school" boolean DEFAULT true NOT NULL;

rphlmr ⚡•6mo ago

I fell you. The migrations count mismatch can be your production "late" on your migrations?

benjickOP•6mo ago

How do you mean, late?

rphlmr ⚡•6mo ago

Pending migrations not yet applied on prod

benjickOP•6mo ago

I don't think so, I can see the new fields added when I browse the database But the issue now is really the missing index. Maybe it's been missing a while, since my latest migration doesn't touch it

rphlmr ⚡•6mo ago

there was issues with some drizzle kit version that missed the constraint

benjickOP•6mo ago

I think I recently changed to onConflictDoUpdate from DoNothing, that might have exposed this older issue I see, and now I've upgraded and that's why it works locally, because I use db:push

rphlmr ⚡•6mo ago

+ the constraint naming has changed (there was a breaking change in patch notes)

benjickOP•6mo ago

I'm gonna create a new local db and run migrations on it and see if it gets the index

rphlmr ⚡•6mo ago

in case you need to add yourself some cutom sql to fix it, you can create custom migration with drizzle kit generate --custom I don't know what is the best to do in this case and I will not provide wrong solutions. If you can't fix it or don't know what to do, I would ping the team for help/advices

benjickOP•6mo ago

It did not have the index. So should I remove it from the code, generate migrations, add it back? Ah okay, maybe a custom one will be fine I understand, I will think of something. I do appreciate finding on the reason for this

rphlmr ⚡•6mo ago

this index is tight to primary key I guess

benjickOP•6mo ago

Thanks a lot Yes, it is

rphlmr ⚡•6mo ago

Do not hesitate to ping the team in case you need some confirmation before a dangerous alteration

benjickOP•6mo ago

I wonder if it can't apply the constrains Thanks! I had duplicate ids in the table Fixed the data up and generated new migrations Big thanks for the help!

Gaming

Programming

there is no unique or exclusion constraint matching the ON CONFLICT specification