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

[austincrim]

Discussed in #12730

^{Originally posted by hirasaki1985 April 8, 2022}
Hi, there.

I have a error.
I tried to use upsert function with the multi @@unique key.
Could you please tell me how to deal with it?

schema.prisma

model UserActiveHistorySummary {
  summary_date                 DateTime                        @db.Date
  user_id                      String                          @db.VarChar(255)
  login_count                  Int                             @default(0)
  created_at                   DateTime                        @default(now()) @db.DateTime(0)
  updated_at                   DateTime                        @default(now()) @db.DateTime(0)
  users                        User                            @relation(fields: [user_id], references: [user_id], onDelete: Cascade, map: "fk_uash_user_id_users_user_id")

  @@unique([summary_date, user_id], map: "uq_uahs_summary_date_user_id")
  @@index([summary_date, login_count], map: "idx_uahs_summary_date_login_count")
  @@index([summary_date], map: "idx_uahs_summary_date")
  @@index([user_id], map: "idx_uahs_user_id")

  @@map("user_active_history_summaries")
}

source code

  const prisma = new PrismaClient()

  await prisma.userActiveHistorySummary.upsert({
    where: {
      summary_date_user_id: {
        summary_date: '2022-03-29T00:00:00+09:00',
        user_id: 'shimizu_test',
      },
    },
    create: {
      summary_date: '2022-03-29T00:00:00+09:00',
      user_id: 'shimizu_test',
      login_count: 3,
    },
    update: {
      login_count: 7,
    },
  })

console error

/Users/hirasaki/work/couger/sources/Ludens-analytics/server/node_modules/@prisma/client/runtime/index.js:45582
        throw new PrismaClientUnknownRequestError(message, this.client._clientVersion);
              ^
PrismaClientUnknownRequestError: 
Invalid `prisma.userActiveHistorySummary.upsert()` invocation:


  Query upsertOneUserActiveHistorySummary is required to return data, but found no record(s).
    at Object.request (/Users/hirasaki/work/couger/sources/Ludens-analytics/server/node_modules/@prisma/client/runtime/index.js:45582:15)
    at async PrismaClient._request (/Users/hirasaki/work/couger/sources/Ludens-analytics/server/node_modules/@prisma/client/runtime/index.js:46405:18) {
  clientVersion: '3.12.0'
}

generated table schema.

-- CreateTable
CREATE TABLE `user_active_history_summaries` (
    `summary_date` DATE NOT NULL,
    `user_id` VARCHAR(255) NOT NULL,
    `login_count` INTEGER NOT NULL DEFAULT 0,
    `created_at` DATETIME(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
    `updated_at` DATETIME(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),

    INDEX `idx_uahs_summary_date_login_count`(`summary_date`, `login_count`),
    INDEX `idx_uahs_summary_date`(`summary_date`),
    INDEX `idx_uahs_user_id`(`user_id`),
    UNIQUE INDEX `uq_uahs_summary_date_user_id`(`summary_date`, `user_id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

package.json

  "dependencies": {
    "@prisma/client": "^3.12.0",
  }
```</div>

[dimw]

In general, .upsert() seems to work fine in prisma@3.15.1 when using composite key in the where/create properties. However, the mentioned error is reproducible for tables where one part of the key is a temporal (e.g. DATE, aka JavaScript Date in Prisma).

In my case, I used the date-fns.parseISO() helper function to convert an ISO date 2022-02-01 to JavaScript Date which resulted in 2022-01-31T23:00:00Z. This was then stored as 2022-01-31 in the DATE-typed attribute in MySQL by Prisma. Obviously, this behavior is causing Prisma to fail because it cannot either find or validate the just created entry. Using moment.utc(date).toDate() immediately solved the issue.

Side note: I must admit that having some experience with Java, Hibernate, and LocalDate type (which does not include the time and time zone), it feels a bit awkward using the (potentially error-prone) JavaScript Date for DATE attributes. However, it looks like there was already a discussion on this topic which suggested creating a Prisma Client Middleware for that use-case.

[pimeys]

Very easy to reproduce. Using the data from the first post:

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)
  login_count                  Int                             @default(0)
  created_at                   DateTime                        @default(now()) @db.DateTime(0)
  updated_at                   DateTime                        @default(now()) @db.DateTime(0)

  @@unique([summary_date, user_id], map: "uq_uahs_summary_date_user_id")
  @@index([summary_date, login_count], map: "idx_uahs_summary_date_login_count")
  @@index([summary_date], map: "idx_uahs_summary_date")
  @@index([user_id], map: "idx_uahs_user_id")

  @@map("user_active_history_summaries")
}

import { PrismaClient } from '@prisma/client'

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

async function main() {
  const data = await prisma.userActiveHistorySummary.upsert({
    where: {
      summary_date_user_id: {
        summary_date: '2022-03-29T00:00:00+09:00',
        user_id: 'shimizu_test',
      },
    },
    create: {
      summary_date: '2022-03-29T00:00:00+09:00',
      user_id: 'shimizu_test',
      login_count: 3,
    },
    update: {
      login_count: 7,
    },
  })

  console.log(data)
}

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

  prisma:tryLoadEnv  Environment variables loaded from /prisma-12730/.env +0ms
  prisma:tryLoadEnv  Environment variables loaded from /prisma-12730/.env +1ms
  prisma:client  dirname /prisma-12730/node_modules/.prisma/client +0ms
  prisma:client  relativePath ../../../prisma +0ms
  prisma:client  cwd /prisma-12730/prisma +0ms
  prisma:client  clientVersion 4.3.1 +1ms
  prisma:client  clientEngineType library +0ms
  prisma:client:libraryEngine  internalSetup +0ms
  prisma:client:libraryEngine:loader  Searching for Query Engine Library in /prisma-12730/node_modules/.prisma/client +0ms
  prisma:client:libraryEngine:loader  loadEngine using /prisma-12730/node_modules/.prisma/client/libquery_engine-debian-openssl-3.0.x.so.node +0ms
  prisma:client  Prisma Client call: +44ms
  prisma:client  prisma.userActiveHistorySummary.upsert({
  where: {
    summary_date_user_id: {
      summary_date: '2022-03-29T00:00:00+09:00',
      user_id: 'shimizu_test'
    }
  },
  create: {
    summary_date: '2022-03-29T00:00:00+09:00',
    user_id: 'shimizu_test',
    login_count: 3
  },
  update: {
    login_count: 7
  }
}) +1ms
  prisma:client  Generated request: +0ms
  prisma:client  mutation {
  upsertOneUserActiveHistorySummary(
    where: {
      summary_date_user_id: {
        summary_date: "2022-03-29T00:00:00+09:00"
        user_id: "shimizu_test"
      }
    }
    create: {
      summary_date: "2022-03-29T00:00:00+09:00"
      user_id: "shimizu_test"
      login_count: 3
    }
    update: {
      login_count: 7
    }
  ) {
    summary_date
    user_id
    login_count
    created_at
    updated_at
  }
}
 +0ms
  prisma:client:libraryEngine  sending request, this.libraryStarted: false +46ms
  prisma:client:libraryEngine  library starting +0ms
prisma:info Starting a mysql pool with 33 connections.
  prisma:client:libraryEngine  library started +2ms
prisma:query BEGIN
prisma:query SELECT `prisma`.`user_active_history_summaries`.`summary_date`, `prisma`.`user_active_history_summaries`.`user_id` FROM `prisma`.`user_active_history_summaries` WHERE (`prisma`.`user_active_history_summaries`.`summary_date` = ? AND `prisma`.`user_active_history_summaries`.`user_id` = ?) /* traceparent=00-00-00-00 */
prisma:query INSERT INTO `prisma`.`user_active_history_summaries` (`summary_date`,`user_id`,`login_count`,`created_at`,`updated_at`) VALUES (?,?,?,?,?) /* traceparent=00-00-00-00 */
prisma:query SELECT `prisma`.`user_active_history_summaries`.`summary_date`, `prisma`.`user_active_history_summaries`.`user_id`, `prisma`.`user_active_history_summaries`.`login_count`, `prisma`.`user_active_history_summaries`.`created_at`, `prisma`.`user_active_history_summaries`.`updated_at` FROM `prisma`.`user_active_history_summaries` WHERE (`prisma`.`user_active_history_summaries`.`summary_date` = ? AND `prisma`.`user_active_history_summaries`.`user_id` = ?) LIMIT ? OFFSET ? /* traceparent=00-00-00-00 */
prisma:query ROLLBACK
  prisma:client:request_handler  PrismaClientUnknownRequestError: Query upsertOneUserActiveHistorySummary is required to return data, but found no record(s).
    at prismaGraphQLToJSError (/prisma-12730/node_modules/@prisma/client/runtime/index.js:20433:10)
    at LibraryEngine.buildQueryError (/prisma-12730/node_modules/@prisma/client/runtime/index.js:26363:12)
    at LibraryEngine.request (/prisma-12730/node_modules/@prisma/client/runtime/index.js:26297:22)
    at async RequestHandler.request (/prisma-12730/node_modules/@prisma/client/runtime/index.js:29873:24)
    at async PrismaClient._request (/prisma-12730/node_modules/@prisma/client/runtime/index.js:30864:16)
    at async main (/prisma-12730/script.ts:8:16) {
  clientVersion: '4.3.1'
} +0ms
PrismaClientUnknownRequestError: 
Invalid `prisma.userActiveHistorySummary.upsert()` invocation in
/prisma-12730/script.ts:8:54

  5 })
  6 
  7 async function main() {
→ 8   const data = await prisma.userActiveHistorySummary.upsert(
Query upsertOneUserActiveHistorySummary is required to return data, but found no record(s).
    at RequestHandler.handleRequestError (/prisma-12730/node_modules/@prisma/client/runtime/index.js:29913:13)
    at RequestHandler.request (/prisma-12730/node_modules/@prisma/client/runtime/index.js:29892:12)
    at async PrismaClient._request (/prisma-12730/node_modules/@prisma/client/runtime/index.js:30864:16)
    at async main (/prisma-12730/script.ts:8:16) {
  clientVersion: '4.3.1'
}
  prisma:client:libraryEngine  library stopping +40ms
  prisma:client:libraryEngine  library stopped +0ms
  prisma:client:libraryEngine:exitHooks  exit event received: exit +0ms

[janpio]

Following up on some details:

@dimw Thanks for these details! Can you maybe share a similar simplified example to what you are doing as what @hirasaki1985 did? That would help us to also rerun this - and then at least use it to test our fix of this issue. Potentially it will also show us a variant, that we might otherwise miss.

@Peter-Sparksuite Can you maybe do the same? Schema and upsert example? If not, does your model and upsert also use a compound unique or ID to identify the key that is used to decide if something already exists? Or does is the upsert key a date type in some form?

@Simonpedro Are you really getting the same upsert is required to return data, but found no record(s) error message or is upsert just failing? Your code example does not have a compount unique/id nor a date related type - which seems to be what unifies all other examples here. Did you maybe simplify your example to post it here? All information welcome.

@tlantli Did you also get this exact error message? Can you share your upsert call?

Thanks all.

(Internal discussion: https://prisma-company.slack.com/archives/C040N0UADF0/p1662665811153459)

[ulevitsky]

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)
  })

[janpio]

Internal note 🌯 : I am recategorizing this as "Date/Time related" from "Concurrency". It is still unclear if that applies to all people that have posted above, but the error message points more to that (so potentially some people were just in the wrong issue here).

[janpio]

Internal note: This is related to #10378 and #8982. The first one actually has a good analysis of why this is happening.

[janpio]

@Peter-Sparksuite From your description the problem does not have the same properties as the one we are investigating here - it would need a Date like column in the primary key to be explained. We will fix that problem, and then close this issue. Please open a new issue and provide some more information what exactly is happening for you. Thanks.

Thanks @ulevitsky - that allowed me to connect some dots. We are now treating these 3 issues (this and the 2 ones I linked) as 1 problem and will try to fix it.

@tlantli I think your problem is more similar to #10497 with the trigger. Please leave a comment there. Thanks.

[phoenixeliot]

I found this was addressed by stripping the milliseconds out of my time:

date: now.toISOString().replace(/\.\d\d\d/, ''),

I'm guessing because my DB model doesn't have milliseconds:

date   DateTime @db.Timestamp(0)

[AndreyMarchuk]

Same issue when using now() in the schema.
Started happening in prisma v4.4, didn't happen in v4.3.

Schema:

model TestTable {
  userId     String
  createdAt  DateTime @default(now())

  @@id([userId, createdAt])
}

Create call:

await prisma.testTable.create({ data: {userId: '12345'})

Error message:

Invalid `prisma.testTable.create()` invocation:
Query createOneTestTable is required to return data, but found no record(s).

[tomhoule]

Hi @AndreyMarchuk there is a known regression with @default(now()) fields that are part of a primary key in 4.4. It was fixed recently, can you try again on one of the recent dev releseases?

[Jolg42]

Note: It's not in a dev version yet, we'll keep you updated with a version you could try 🔜

[tomhoule]

prisma@4.5.0-dev.38 should have the fix. Can you check?

[AndreyMarchuk]

@tomhoule cannot reproduce the issue anymore, all good

[KnutHelstad]

Not fixt for mssql. Problem stil exist on 4.6.0-dev.42

[Jolg42]

@KnutHelstad Thanks for checking on a dev version. It could be that your issue is different from what was posted here. Could you provide more info / reproduction material (query / schema for example).
The best would actually be to open a new issue here

[KnutHelstad]

OK. I can open a new issue.