Upsert error on MySQL: Query ... is required to return data, but found no record(s) when you try to write an overflowing unsigned int

[Peter-Sparksuite]

Bug description

I see the Query ... is required to return data, but found no record(s) error.

Similar in manifestation to #12783, but, this time without having a Date involved in the primary or unique keys.

However, the entity does have two date fields.

I don't have approval to share full details regarding our schema at present, but, I can tell you, regarding the table where this is happening: the primary key is a single column being an unsigned integer, and the value that it's set to is always provided externally, not auto-generated by the DB.

There are two columns that are DateTime columns. One is entirely optional, the other is required, and defaults to 'now()' via the schema having @default(now()).

The emailAddress column is required to be unique.

The error occurs with code that looks rather like this:

const user = await prisma.user.upsert({
  where: {
    emailAddress: '***@***.***',
  },
  update: {},
  create: {
    id: await generateUserID(),
    emailAddress: '***@***.***',
    firstGivenName: 'Fred',
    familyName: 'Flintstone',
    cellNumber: '+1800RUBBLE',
  },
});

The intent being to either retrieve (with no changes) the existing record (being retrieved by the unique column, not the primary ID column), or, create a new record (hence, the 'update' has an empty object). Either way, an object is supposed to be returned.

I believe, in most cases, the db record will already have been created, so it
should be heading down the 'update' path.

I kinda wonder if, due to the DateTime issue (ie: data mangling causing exact matches to fail to find results), this is somehow also playing a part here, but, it could be something else entirely.

How to reproduce

const user = await prisma.user.upsert({
  where: {
    emailAddress: '***@***.***',
  },
  update: {},
  create: {
    id: await generateUserID(),
    emailAddress: '***@***.***',
    firstGivenName: 'Fred',
    familyName: 'Flintstone',
    cellNumber: '+1800RUBBLE',
  },
});

Expected behavior

The expected behavior is that either the existing record is returned, or, the newly created record is returned.

Prisma information

model User {
  id                            Int       @id @map("user_id")
  emailAddress        String    @unique @map("email_address")
  password               String
  firstGivenName     String    @map("first_given_name")
  familyName           String    @map("family_name")
  cellNumber            String?   @map("cell_number")
  created                   DateTime  @default(now())
  lastLogin                 DateTime? @map("last_login")

Environment & setup

• OS: macOS
• Database: MySQL
• Node.js version: 16.16.0

Prisma Version

4.3.0

[janpio]

Thanks for opening this separate issue.

Few clarification questions
Is update indeed empty?
In both create and update you are not writing any Date yourself?

[Simonpedro]

I have experienced the same issue with the following configuration.

  db.cacheEntry.upsert({
      where: { key },
      create: { key, value, ttl },
      update: { value, ttl, timestamp: new Date() }
    });

model CacheEntry {
  key       String   @id
  timestamp DateTime @default(now())
  ttl       Int?
  value     Json
}

Usage:

[Peter-Sparksuite]

Few clarification questions Is update indeed empty?

Yes, it actually is being passed an empty object. No updates are needed in this case, if the record already exists. The intent is: retrieve the existing record, or create a new record if needed, either way, get back a record, and do it using an atomic db operation, to minimize the possibility of the new record being added elsewhere.

In both create and update you are not writing any Date yourself?

Correct. There's no need to do that, as the defaulting behavior is supposed to be taking care of that (when creating the record), and, I do not want to update it if the record already existed, as, it's supposed to be a record indicating when the record was created. Wouldn't be right to update it after creation.

[Peter-Sparksuite]

Hey @janpio , any update on this?

[janpio]

No, this is waiting for a reproduction from us.
If you can somehow package it better so that it is easier for us, we could prioritize that higher and get to it sooner. The optimal would be a GitHub repository with a tiny demo project that only shows the broken behavior.

[Weakky]

Hey folks,

Unfortunately, I can't seem to reproduce your issues locally. I suspect some concurrency issues to be happening here.

[janpio]

When I read the error message Query ... is required to return data, but found no record(s) and look at the query await prisma.model.upsert({ where: { ... }, update: { (...) }, create: { ... }, }) I see no real way how any of the queries Prisma runs under the hood that should return some data, can not return any data. If the create or where had some weird dynamic value, but in your both cases that seem to be straight values.

Do you have parallel requests that could delete the data while it is being modified by the upsert? So can you system @Peter-Sparksuite delete Users? Or yours @Simonpedro delete cacheEntrys?

Any help is welcome to hopefully be able to reproduce this.

[Peter-Sparksuite]

Ok, I put some time into recreating this issue, and, I had, I would say, some success!

I'm not sure this is the answer for all possible vectors for this error, however, what I have found is:

1. I'm using MySQL, and, in the database, the primary key is defined as int(10) unsigned.
2. The prisma schema defined the ID column as 'Int @id'
3. the value being passed in as the ID (for a new record creation) 2173158296 is resulting in a new record being inserted, but, the resulting object shows the ID value as -2121809000.
4. repeating the upsert command, even when being passed the exact same values, results in the error being encountered.

So, somewhere the integer to unsigned int conversion is not symmetrical, I would say, which leads to this issue.

Realizing that this was happening, even if I add to the schema definition for the ID column @db.UnsignedInt, (ie: Int @db.UnsignedInt @id @map("key1")), I still see the same unsigned value being converted to signed int, and the error being encountered.

eg:

schema:

model CacheEntry {
  id                            Int       @db.UnsignedInt @id @map("key1")
  email                         String    @unique

  @@map("test_table")
}

code:

	for (const create of [
		{ email: 'betty@flintstone.com', id: 1041680182 },
		{ email: 'betty@flintstone.com', id: 301604023 },
		{ email: 'barney@flintstone.com', id: 1546252519 },
		{ email: 'wilma@flintstone.com', id: 2173158296 },
		{ email: 'wilma@flintstone.com', id: 2183158296 },
		{ email: 'fred@flintstone.com', id: 4249317952 },
		{ email: 'fred@flintstone.com', id: 692889259 },
	]) {
		
		console.log('id:', create.id);
		
		const cacheEntry = await prisma.cacheEntry.upsert({
			where: {
				email: create.email
			},
			update: {},
			create: create,
		});

		console.log('cacheEntry for ' + create.email, cacheEntry);
	}

output:

id: 1041680182
cacheEntry for betty@flintstone.com { id: 1041680182, email: 'betty@flintstone.com' }
id: 301604023
cacheEntry for betty@flintstone.com { id: 1041680182, email: 'betty@flintstone.com' }
id: 1546252519
cacheEntry for barney@flintstone.com { id: 1546252519, email: 'barney@flintstone.com' }
id: 2173158296
cacheEntry for wilma@flintstone.com { id: -2121809000, email: 'wilma@flintstone.com' }
id: 2183158296
/index.js:29913
      throw new PrismaClientUnknownRequestError(message, this.client._clientVersion);
            ^

PrismaClientUnknownRequestError: 
Invalid `prisma.cacheEntry.upsert()` invocation:


Query upsertOneCacheEntry is required to return data, but found no record(s).
    at RequestHandler.handleRequestError (/index.js:29913:13)
    at RequestHandler.request (/index.js:29892:12)
    at async PrismaClient._request (/index.js:30864:16)
    at async /entry.js:19:28 {
  clientVersion: '4.3.1'
}

At this point, the db has 3 rows in the db.

[Weakky]

Hey @Peter-Sparksuite,

Brilliant work on the repro! This made our job extremely easy to fix the issue. Turns out the issue was reproducible with a single upsert that contained an id that would overflow.

The fix will be available in the next Prisma release (unsigned ints won't overflow anymore).

Thanks again, cheers 🙏

[Peter-Sparksuite]

Cool. Thank you!

[janpio]

@Simonpedro Can you please have a look at the data connected to your requests if this could also be the underlying trigger here? If not, I might need to ask you (again) to create a new issue for your problem and try to supply as much data as possible.

[Simonpedro]

@janpio I spent some time digging into it.

Do you have parallel requests that could delete the data while it is being modified by the upsert?

Yes, it's likely that under certain conditions, we trigger an upsert followed by a delete. Nonetheless, I tried to replicate that case in different ways but failed in reproducing.

Regarding Peter's findings, I couldn't reproduce it either and don't think it's related, mainly because my @id column is of type String and I'm using Postgress.

I'll keep an eye on it. I just improved the catch branch of the screenshot I had shared by adding more debugging information. If I find any insights, I'll let you know via a new issue.

Thanks a lot!

[Haschikeks]

Hey,

I might want to add something to this problem.
When you use 0 as a value for an id column, the Query upsertOne... is required to return data, but found no record(s). error is thrown.
As far as I know, 0 is a valid value for the used schema.

If this is a separate issue, I can create a new one.

Repository to reproduce:
https://github.com/Haschikeks/prisma-zero-id

Schema for convenience:

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

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

model User {
  id Int @id @default(autoincrement())
}

Edit: I'm sorry, I just noticed that this issue was already closed. ☹️

[millsp]

I'm sorry, I just noticed that this issue was already closed. frowning_face

No worries, that does not mean we cannot reopen the issue. Is this only happening with 0?

[janpio]

Hey @Haschikeks, thanks for including a reproduction. This issue here was technically only about this error message when you try to write an overflowing unsigned int - the title just does not reflect that.

Can you please open a new bug report issue, include your current comment text from here and also the additional information it is asking for? Thanks! We will take a look as soon as possible then.