Drizzle Team•3mo 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 ⚡•3mo ago

taskId is a primary key and also a foreign key?

benjick•3mo ago

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

rphlmr ⚡•3mo 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)

benjick•3mo ago

Can't replicate it locally either, so annoying

rphlmr ⚡•3mo ago

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

benjick•3mo ago

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

rphlmr ⚡•3mo 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

benjick•3mo ago

I see an error when running the migrations now:

error: column publishedTasks.show_name does not exist

rphlmr ⚡•3mo ago

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

benjick•3mo ago

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

rphlmr ⚡•3mo 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?)

benjick•3mo 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 ⚡•3mo ago

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

benjick•3mo 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 ⚡•3mo ago

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

benjick•3mo ago

How do you mean, late?

rphlmr ⚡•3mo ago

Pending migrations not yet applied on prod

benjick•3mo 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 ⚡•3mo ago

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

benjick•3mo 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 ⚡•3mo ago

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

benjick•3mo ago

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

rphlmr ⚡•3mo 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

benjick•3mo 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 ⚡•3mo ago

this index is tight to primary key I guess

benjick•3mo ago

Thanks a lot Yes, it is

rphlmr ⚡•3mo ago

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

benjick•3mo 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