PrismaClientUnknownRequestError in prisma.user.create(...) with trigger

[Datzu712]

Bug description

When I try to create a new user I got this error:

prisma:info Starting a mysql pool with 9 connections.
prisma:query BEGIN
prisma:query INSERT INTO `iosef_db`.`user` (`id`,`hash`,`salt`,`password`,`email`,`token`,`tokenExpiration`) VALUES (?,?,?,?,?,?,?)
prisma:query SELECT `iosef_db`.`user`.`id`, `iosef_db`.`user`.`hash`, `iosef_db`.`user`.`salt`, `iosef_db`.`user`.`password`, `iosef_db`.`user`.`email`, `iosef_db`.`user`.`token`, `iosef_db`.`user`.`tokenExpiration` FROM `iosef_db`.`user` WHERE `iosef_db`.`user`.`id` = ? LIMIT ? OFFSET ?
prisma:query ROLLBACK
PrismaClientUnknownRequestError: 
Invalid `prisma.user.create()` invocation in
/api/src/prisma/models/User.model.ts:150:44

  147 const salt = await bcrypt.genSalt(10);
  148 const hashedPassword = await bcrypt.hash(data.password, salt);
  149 
→ 150 const user = await prisma.user.create(
  Query createOneuser is required to return data, but found no record(s).
    at cb (/api/node_modules/@prisma/client/runtime/index.js:38679:17)
    at async Function.create (/api/src/prisma/models/User.model.ts:150:26)
    at async loginController.test (/api/src/controllers/login.controller.ts:31:25)
    at async /api/node_modules/@nestjs/core/router/router-execution-context.js:46:28
    at async /api/node_modules/@nestjs/core/router/router-proxy.js:9:17 {
  clientVersion: '3.5.0'
}

How to reproduce

await prisma.user.create({
    data: {
        email: 'someone@gmail.com',
        password: 'password',
        tokenExpiration: `1643896265257`,
        token: 'eyJhbGciOiJIUz…Nn0.ouT9RiI6X0vXEkjf9rxfUhIu_UUYZ-ZkRUCuqW-9tUk',
        salt: '$2b$10$q.y4.vrHKxDYMQkeWm9xW.',
        hash: '$2b$10$Qju63Il/erAV1epJyeEk6epdX5/zD.6kC4oTTjTtlHeUpCUDfpHay'
    }
});

Expected behavior

It should create the user without error

Prisma information

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

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

model user {
  id              String @id @default("") @db.VarChar(36)
  hash            String @db.VarChar(80)
  salt            String @db.VarChar(80)
  password        String @db.VarChar(22)
  email           String @db.VarChar(50)
  token           String @db.VarChar(280)
  tokenExpiration String @db.VarChar(50)
}

Environment & setup

• OS: Linux (Inside a docker container node:16.13.0)
• Database: MySQL
• Node.js version: 16.13.0

Prisma Version

3.5.0

Edit

I tried using prisma.user.createMany(...); and it works perfectly!

[janpio]

Is this consistently reproducible for you? Super weird.

[csanso-limit]

I think @Datzu712 hasn't configured the model correctly and that's why it's failing.

id String @id @default("") @db.VarChar(36)

Default is set to blank string, thus when you use create() Prisma thinks you are inserting an empty string "" as the id, thus when it tries to the select after inserting it tries to select the user with id = "" empty string and fails, and then rolls back.
Thus the failure is happening when running the SELECT query, it returns nothing, and so then prisma rolls back the insert, since the whole query sequence is in a trasaction.

What happens with createMany() is that it successfully inserts like with create(), but createMany() does not try and run a SELECT query afterwards with id = "" empty string.

Solution found, I think.

[csanso-limit]

However I am having a similar problem.
I amb IoT programmer, I save millions of timestamps in the database.

My problem is due to the fact that my primary key includes a timestamp, and Prisma thinks mysql and postgres save the exact timestamp (with milliseconds) that Prisma sent in the INSERT query, but mysql and postgres both round up or down the timestamp when it saves it. HOWEVER when running the SELECT query mysql and postgres do not round up or down the timestamps thus having an exact timestamp comparision, but it cannot find the inserted ROW because the timestamp was rounded up in the save process. And thus Prisma rolls back the INSERT query because it did not return any values.

TL;DR: Databases aproximate the timestamp when "INSERTING", but do not aproximate them when "SELECTING".
Thus Prisma cannot find the inserted row with that timestamp and thus fails.

I think this is a big problem.

[csanso-limit]

However I am having a similar problem. I amb IoT programmer, I save millions of timestamps in the database.

My problem is due to the fact that my primary key includes a timestamp, and Prisma thinks mysql and postgres save the exact timestamp (with milliseconds) that Prisma sent in the INSERT query, but mysql and postgres both round up or down the timestamp when it saves it. HOWEVER when running the SELECT query mysql and postgres do not round up or down the timestamps thus having an exact timestamp comparision, but it cannot find the inserted ROW because the timestamp was rounded up in the save process. And thus Prisma rolls back the INSERT query because it did not return any values.

TL;DR: Databases aproximate the timestamp when "INSERTING", but do not aproximate them when "SELECTING". Thus Prisma cannot find the inserted row with that timestamp and thus fails.

I think this is a big problem.

A practical example:

Table's primary key is @@id([taxiId, arrivedAt]).
Prisma executes these queries:

INSERT INTO `station_taxis` (`taxiId`, `stationId`, `arrivedAt`) VALUES ('55b59c58-3f51-46a3-9fca-a43ab786b9b5', '19117c22-f7c8-4f7a-86da-6d6a140987eb', '2022-01-27T16:49:54.9+01:00');

SELECT * FROM `station_taxis` WHERE `taxiId` = '55b59c58-3f51-46a3-9fca-a43ab786b9b5' AND `arrivedAt` = '2022-01-27T16:49:54.9+01:00';

Notice the .9 after the seconds, as we all know javascript allows for milliseconds and does not aproximate to the exact second.
However here unless the database schema allows for such precision it will round up, so basically it will in most setups.

So the database (both mysql and postgres) will round up the INSERTED timestamp, but when comparing it will use the EXACT timestamp.

So even though the INSERT and SELECT have the same exact value "2022-01-27T16:49:54.9+01:00", the SELECT statement will return NO RESULTS, and Prisma will throw an error and ROLLBACK.

The only possible solution if for Prisma to round up before sending the value to the database in the first place, it could do this by taking the schema.prisma @db.Timestamp into consideration, the developer would have to manually specify @db.Timestamp(0) which I've done. But Prisma does not take this into consideration, and just sends the full datetime.

[csanso-limit]

It doesn't matter in which ISO 8601 you send it as "2022-01-27T16:49:54.9+01:00" or "2022-01-27T15:49:54.9Z" both have the same problem.

const stationTaxi = await prisma.stationTaxi.create({
   data: {
      taxiId: taxiId,
      stationId: stationId,
      arrivedAt: new Date(),
  },
});

You cannot use new Date() directly. The field also includes a @default(now()), but if I leave it blank the error still occurs, because Prisma is still sending the date itself.

A simple workaround would be "const arrivedAt = new Date(parseVar)" and then call "arrivedAt.setMilliseconds(0)", and then send the date. Then the whole create() sequence works correctly.
But this is a flaw, most people won't know how to do this workaround.

This workaround also proves my point, that milliseconds are causing prisma create() to fail when the primary key includes a datetime or timestamp.

[janpio]

Can you please split your problem into its own issue @csanso-limit? Right now I can not apply the correct labels or ask the correct questions as this is in another user's issue which you already kind of debugged and solved. Very happy to take a look at what you write - but in its own issue with the full issue information filled out. Thanks.

[Datzu712]

I think @Datzu712 hasn't configured the model correctly and that's why it's failing.

id String @id @default("") @db.VarChar(36)

Default is set to blank string, thus when you use create() Prisma thinks you are inserting an empty string "" as the id, thus when it tries to the select after inserting it tries to select the user with id = "" empty string and fails, and then rolls back. Thus the failure is happening when running the SELECT query, it returns nothing, and so then prisma rolls back the insert, since the whole query sequence is in a trasaction.

What happens with createMany() is that it successfully inserts like with create(), but createMany() does not try and run a SELECT query afterwards with id = "" empty string.

Solution found, I think.

(Srry for my english)
Hi @csanso-limit Thanks for answering me!
I have the @default("") because after create a user in my database I have atrigger that assing a UUID to the user id automatically, but the id to have a primary key I cannot set it with allow null.

I tried adding the ID manually but I got the same error :(
Query createOnetest is required to return data, but found no record(s).

model test {
  id              String @id @db.VarChar(50)
  salt            String @db.VarChar(50)
  email           String @db.VarChar(50)
  token           String @db.VarChar(50)
  tokenExpiration String @db.VarChar(50)
  hash            String @db.VarChar(80)
}

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient()

prisma.test.create({
    data: {
        email: 'someone@gmail.com',
        tokenExpiration: `1643896265257`,
        token: 'eyJh9rxfUhIu_UUYZ-ZkRUCuqW-9tUk',
        salt: '$2b$10$q.y4.vrHKxDYMQkeWm9xW.',
        hash: '$2b$10$Qju63Il/erAV1epJyeEk6epdX5/zD.6kC4oTTjTtlHeUpCUDfpHay',
        id: '7bbf1f4a-7713-11ec-8968-0242ac120002'
    }
}).then(console.log).catch(console.error);

With others ORM I didn't have problems with this.

[codymihai]

Does anybody figure out to fix this problem? or a way to fix it?

[codymihai]

Does anybody figure out to fix this problem? or a way to fix it?

Resolve it. I've migrated from prisma 1 to prisma 2 and the length of Id(@id) was increased to 30 characters into prisma 2 due to cuid() generator

[pantharshit00]

I can reproduce the problem. This can be associated with #4246.

But the setup with trigger to assign IDs is not very common so this might not be as straightfoward.

[Jolg42]

Note:
The database has a trigger setting the UUID after the data was written by Prisma.
There is probably no way we can fix that in the current architecture because in a create() we always return the created row, and we need to fetch it. This doesn't work when the primary key changes.
(createMany() works because it doesn't return the data, only a count.)

Alternatively, Prisma could return the data that was created but the data would be incorrect.

We could make this work by disabling the fetch, see feature request #4246

[pimeys]

Ok, now I can reproduce this with 4.3.1.

What happens here is that we generate that empty string default value in the core, and we expect to find this new record with that value. The database generates a UUID, and our SELECT does not find anything.

It is unclear can you even do this with MySQL and an ORM. MySQL does not have RETURNING for an INSERT, so we have no idea what is the generated UUID value for the record.

Easy reproduction with the migration:

-- CreateTable
CREATE TABLE `user` (
    `id` VARCHAR(36) NOT NULL DEFAULT '',
    `hash` VARCHAR(80) NOT NULL,
    `salt` VARCHAR(80) NOT NULL,
    `password` VARCHAR(22) NOT NULL,
    `email` VARCHAR(50) NOT NULL,
    `token` VARCHAR(280) NOT NULL,
    `tokenExpiration` VARCHAR(50) NOT NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TRIGGER insert_guid 
BEFORE INSERT ON `user`
FOR EACH  ROW 
BEGIN 
    SET NEW.id = UUID(); 
END;

[janpio]

Probably related: #14918