prisma: `Error: Unique constraint failed on the (not available)` on PlanetScale/Vitess

This issue here is about the error message, which can not provide the actual column name as it is not provided by PlanetScale/Vitess

Digging more into this seems like when a raw query is executed via PlanetScale, it does return some data about the column on which the unique constraint first fails on.

Here’s an example with a users table with unique email and uuid (where the uuid is generated application side)

Failing with email when both email and uuid are duplicates:

target: {REDACTED}.-.primary: vttablet: rpc error: 
code = AlreadyExists desc = Duplicate entry 'testing@gmail.com' 
for key 'users.users_email_key' (errno 1062) (sqlstate 23000) 
(CallerID: planetscale-admin): Sql: "insert into 
users(email, first_name, `name`, slug, uuid, updated_at, meta) 
values (:vtg1, :vtg2, :vtg3, :vtg4, :vtg5, :vtg6, :vtg7)", BindVars: {REDACTED}

Failing with uuid when only uuid is a duplicate:

target: {REDACTED}.-.primary: vttablet: rpc error: 
code = AlreadyExists desc = Duplicate entry 'abc123' 
for key 'users.users_uuid_key' (errno 1062) (sqlstate 23000) 
(CallerID: planetscale-admin): Sql: "insert into users(email, first_name, 
`name`, slug, uuid, updated_at, meta)
values (:vtg1, :vtg2, :vtg3, :vtg4, :vtg5, :vtg6, :vtg7)", BindVars: {REDACTED}

Overall new to the Prisma codebase, but going to start looking into where in Prisma are these errors handled. Any directions would be super helpful.

This issue here is about the error message, which can not provide the actual column name as it is not provided by PlanetScale/Vitess, as described in the original issue. There is no real solution than to fix Vitess or implement some workaround that gets the column name from the original query.

If you have any code that causes the Unique constraint failed ... error message although it is unexpected, this issue here is not the correct one. Please open a new issue and describe your problem. (E.g. your problem @dyeoman2 would be a good new bug report so we can look into how this can happen)

Solution is to set skipDuplicates: true

eg : await prisma.user.createMany({ data: seed_users, skipDuplicates: true, });

I was getting this issue when seeding my database : npx prisma db seed Error message :

Unique constraint failed on the (not available) clientVersion: ‘4.11.0’, code: ‘P2002’

I can confirm with planetscale.

Facing the same issue here using Planetscale. For me it was creating the same object twice in a deep nested create.

@moaazsidat I checked a bit and:

• The error comes from https://github.com/prisma/prisma-engines/blob/ffceeccf256b1d7b039c6ceed46fa24d804943e4/libs/user-facing-errors/src/query_engine/mod.rs#L25
• When following it, I could find that it’s defined in quaint https://github.com/prisma/prisma-engines/blob/37eb6ab51d59be9ed1fbe46e5b95931a6e4ceac1/libs/user-facing-errors/src/quaint.rs#L12
• And here is the relevant file for MySQL for the parsing code in quaint https://github.com/prisma/prisma-engines/blob/main/quaint/src/connector/mysql/error.rs
• Relevant parsers code is most probably this: https://github.com/prisma/prisma-engines/blob/ffceeccf256b1d7b039c6ceed46fa24d804943e4/quaint/src/connector/mysql/error.rs#L18-L51

Still facing the same issue.

Anyone found a solution to this using pscale ?

After checking fields one at a time, I found out that pscale wanted the createdAt and updatedAt dates provided in my create request even though I had them set to automatically populate in my Prisma schema. Hope this narrows things down for you.

Let’s make this the last message about this problem here in this unrelated issue @KODerFunk, but in the past this sometimes happened when the database returned error messages in a non standard language. That can throw our logic off right now. (To respond to this, best open a new issue quickly - no need to already have investigated this further - but let’s not spam the other 30 participants in this Vitess/PlanetScale specific issues any more 😆 )

confirming that I am still running into this issue (specifically, lack of a column name in the error message when a unique constraint fails) on Planetscale.

Have also confirmed @moaazsidat’s finding that Planetscale does indeed have info in its error message indicating the column where the unique constraint fails. Could we get another look at this? Seems there should be a way to parse the column name from the Planetscale error and pull it into the prisma error.

I found my problem was sequence of id field, which i set an auto increment.

I reset my sequence to last column id + 1. For instance, if your last one is 100, reset it to 101.

ALTER SEQUENCE public."Product_id_seq" RESTART WITH 622;

*You can find sequence name with:

SELECT sequence_schema, sequence_name 
FROM information_schema.sequences 
ORDER BY sequence_name;

Any update on this? As of right now, I have to manually check for every property uniqueness in order to send a proper message to my front end. Did you find a better workaround than this ?

I had this issue with createMany. After adding skipDuplicates: true, it is working now.

In my case the issue was that I was doing a call to updateMany with two values with the same id (a violation of the constraint). I think the issue here is that the error message is helpful, but the (not available) is missing the details of the unique constraint violation.

I will try to repeat my instructions again:

If you have any code that causes the Unique constraint failed ... error message although it is unexpected, this issue here is not the correct one. Please open a new issue and describe your problem.

I am facing the same issue, has anyone found a way to figure this out?

I am currently having the same issue while using the update request.

My workaround for this issue: I used prisma.$executeRaw and everything works as expected.

Yeah, I have the same issue as well.

+1

+1