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

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.

@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!

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.

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.

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: