prisma: Upsert errors with compound unique key with Date on MySQL: `Query ... is required to return data, but found no record(s)`

It’s not unique to upserts, happens on creates as well.

Here’s a distilled repro based on @pimeys code:

schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model UserActiveHistorySummary {
  summary_date                 DateTime                        @db.Date
  user_id                      String                          @db.VarChar(255)

  @@id([summary_date, user_id]) // or @@unique
}

index.js

import { PrismaClient } from "@prisma/client"

const prisma = new PrismaClient({
  log: ["query", "info", "warn", "error"],
})

async function main() {
  const data = await prisma.userActiveHistorySummary.create({
    data: {
      summary_date: "2022-03-29T00:00:00+09:00",
      user_id: "test",
    },
  })

  console.log(data)
}

main()
  .then(async () => {
    await prisma.$disconnect()
  })
  .catch(async (e) => {
    console.error(e)
    await prisma.$disconnect()
    process.exit(1)
  })

OK. I can open a new issue.

Thanks for that clarification @Simonpedro. Although there is a Date in your data, I don’t think that would fit our current theory why Prisma can not retrieve the data it just created. I think you could preemptively already post in https://github.com/prisma/prisma/issues/15264 that you are in the same/similar boat to make sure when we close this issue because we fix the primary key Date problem, your problem still is recorded somewhere. You should be fine just copying your information your shared here into that issue. Thanks!

model shipment_entry {
  client                      String                      @default(dbgenerated("(current_setting('app.client'::text))::character varying(16)")) @db.VarChar(16)
  warehouse                   String                      @default(dbgenerated("(current_setting('app.warehouse'::text))::character varying(16)")) @db.VarChar(16)
  id                          String                      @id(map: "PK_Shipment_Entry__id") @db.VarChar(64)
  notes                       String?
  is_validated                Boolean                     @default(false)
  created_date                DateTime                    @default(now()) @db.Timestamptz(6)
  arrived_date                DateTime?                   @db.Timestamptz(6)
  created_by                  String?                     @default(dbgenerated("(current_setting('app.user'::text))::uuid")) @db.Uuid
  client_warehouse_allocation client_warehouse_allocation @relation(fields: [client, warehouse], references: [client_code, warehouse_code], onDelete: NoAction, onUpdate: NoAction, map: "FK_ShipmentEntry_2_ClientWarehouseAllocation")
  users                       users?                      @relation(fields: [created_by], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "FK_Shipment_Entry__created_by_Users__id")
}

I also have a trigger on my pg db.

create or replace function shipment_entry_custom_id_generator() returns trigger as $$
	begin
		new.id = (select 
					current_setting('app.client') || current_setting('app.warehouse') || 'LPN' || LPAD(cast(count(*) + 1 as varchar(64)),8,'0') as "id"
					from 
						shipment_entry 
					where 
						client = current_setting('app.client')
						and warehouse = current_setting('app.warehouse')
				 );
		return new;
	end
	$$ language plpgsql;

I add it to the same table

create trigger shipment_entry_custom_generator_id_trigger
	before insert on shipment_entry
	for each row
	execute procedure shipment_entry_custom_id_generator(); 

However, if I drop my trigger, the operation succeeds.

Still seeing this with version 4.2.1.