prisma: `referentialIntegrity = prisma`: Broken query on `onUpdate: Cascade` | `symbol ... not found` | `The column ... does not exist in the current database.`

Bug description

I’m testing my existing schema on PlanetScale and i’m getting an error when i’m trying to connect relations

Message: 
Invalid `prisma.product.update()` invocation:


  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1105, message: "symbol tino.Product.id not found", state: "HY000" })) })

Query: 
prisma.product.update(
{
  where: {
    id: "69d7ee03-6d9e-46ec-a2ae-0b40e58158e1",
  },
  data: {
    category: {
      connect: {
        id: "0226b8aa-4fc5-4b20-801b-6a62b0ca8711",
      },
    },
  },
  select: {
    id: true,
    category: true,
    categoryId: true,
  },
}
)

Schema

  model Category {
    id          String       @id @default(uuid()) @db.Char(36)
    name        String       @db.VarChar(255)
    description String?
    createdAt   DateTime     @default(now())
    updatedAt   DateTime     @updatedAt
    active      Boolean      @default(true)
    products    Product[]
  
    @@unique([id, active])
  }
  
  model Product {
  id           String         @id @default(uuid()) @db.Char(36)
  sku          String?        @db.VarChar(255)
  name         String         @db.VarChar(255)
  categoryId   String?
  category     Category?      @relation(fields: [categoryId], references: [id], onDelete: SetNull)
}

I have no issues when testing locally so i assume it has to do something with planetScale. after a bit of fiddling with the code i found something interesting. in another relation of mine between user and roles that happens to be many to many relationship too.

model User {
  id                String              @id @default(uuid()) @db.Char(36)
  roles             Role[]
}
model Role {
  name        String       @id @db.VarChar(255)
  users       User[]
}

when i run this code:

await prisma.role.update({
          where: {
            name: 'admin',
          },
          data: {
            users: {
              connect: {
                id: '29b6051d-c7c2-4696-9c55-d1a3c2593bdd',
              },
            },
          },
        });

this works but if i do the opposite

await prisma.user.update({
          where: {
            id: '29b6051d-c7c2-4696-9c55-d1a3c2593bdd',
          },
          data: {
            roles: {
              connect: {
                name: 'admin',
              },
            },
          },
        });

i get the same error.

Update 19Dec 2021:

I have managed to replicate the error in a much more controlled enviorment, but still not sure why this is an error:

Prisma schema

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

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

model User {
  id            String    @id @default(uuid())
  name          String?
  profile       Profile?
}

model Profile {
  userId    String   @id
  user      User     @relation(fields: [userId], references: [id], onUpdate: Cascade)
}

when i run the following command

await prisma.user.update({
    where: {
      id: '6a509403-cef1-4fe7-8ef4-031475acec7c',
    },
    data: {
      name: 'name',
    },
});

DB Log shows this:

Query: BEGIN
Param: []
Query: SELECT `lito`.`User`.`id` FROM `listino`.`User` WHERE `lito`.`User`.`id` = ?
Param: ["6a509403-cef1-4fe7-8ef4-031475acec7c"]
Query: SELECT `lito`.`Profile`.`userId` FROM `lito`.`Profile` WHERE (1=1 AND `lito`.`Profile`.`userId` IN (?))
Param: ["6a509403-cef1-4fe7-8ef4-031475acec7c"]
Query: SELECT `lito`.`User`.`id`, `lito`.`Profile`.`userId` FROM `lito`.`Profile` WHERE 1=0
Param: []
Query: ROLLBACK
Param: []

I don’t know why prisma generates SELECT lito`.`User`.`id`, `lito`.`Profile`.`userId` FROM `lito`.`Profile` WHERE 1=0and this seems to be the issue.

How to reproduce

Tested Using Prisma Studio and from the code itself.

Expected behavior

to create connection between many to many relationships

Prisma information

prisma: 3.6.0 @prisma/client: 3.6.0

Environment & setup

OS:MacOS
Database: PlanetScale MySql
Node.js version: 14

Prisma Version

prisma:  3.6.0
@prisma/client: 3.6.0

About this issue

Original URL
State: closed
Created 3 years ago
Reactions: 16
Comments: 27 (11 by maintainers)

Commits related to this issue

Temporary switch to NoAction for prisma referential actions LikedPosts There is a known bug when using prisma with planetcale https://github.com/prisma/prisma/issues/10758 The temporary fix is to sw... — committed to planetscale/beam by bogdansoare 2 years ago
feat: add update on issues setting the onUpdate prisma callback to NoAction to avoid a bug https://github.com/prisma/prisma/issues/10758 — committed to BearStudio/start-repo by yoannfleurydev 2 years ago
feat: add update on issues setting the onUpdate prisma callback to NoAction to avoid a bug https://github.com/prisma/prisma/issues/10758 — committed to BearStudio/start-repo by yoannfleurydev 2 years ago
Temporary switch to NoAction for prisma referential actions LikedPosts There is a known bug when using prisma with planetcale https://github.com/prisma/prisma/issues/10758 The temporary fix is to sw... — committed to jacklee2020716/beam by jacklee2020716 2 years ago

Most upvoted comments

Typical, you spend hours trying to fix it and when you finally post something… it works.

I’ve had this experience literally hundreds of times in the 2+ decades I’ve been doing this. For years I used the exact same language: “it works.” I wrote it off to some problem or other with the code, or whatever.

Over the years I’ve changed my reaction to these situations. Now, I find that these are pathways to deepening my understandings of the systems I’m using. Instead of “it didn’t work and now it works”, try “I don’t understand what changed.” It’s an exercise in understanding yourself as much as the software.

If you can find the difference between then and now, you’re likely to build reflexes so that next time you’ll intuit this and make it part of your workflow, avoiding the problem all together. You’ll learn from the painful experience. You’ll become a better engineer.

If you really can’t find the problem you shouldn’t trust your software, or at least it should lower your trust, or the other side of the same coin is you shouldn’t trust yourself to do this thing (yet). I’ve been in situations where I needed to figure things out that seemed to come from nowhere but were preventing millions of customers from doing the thing they came to do. You can’t look a ceo in the eye and say “it works and now it doesn’t”. Once you’ve made your way up to that level of responsibility you get allergic to uncertainty. You just don’t use that tech or that pattern anymore when it’s important… and everything is important.

Just my 2 cents.

+11

jde on Jan 17, 2022

No update on fixes to the referentialIntegrity feature right now unfortunately. We’ll get to it though, and seems you all have the same error here so at least this will be 1 fix for all of these.

janpio on Mar 24, 2022

@janpio @jde yes you’re right, i’ve moved all the onUpdates to NoAction and it seems like it has resolved the issue for now. so i guess this should be a workaround for now.

bardzh on Dec 27, 2021

Thanks for addressing this! I’ve been working around this in my project. I just switched to onUpdate: noAction and can confirm that all update queries are working as expected now (without the cascade logic of course.) This is a workaround I can live with for a while.

jde on Dec 26, 2021

janpio on Dec 18, 2021

Typical, you spend hours trying to fix it and when you finally post something… it works. Although it was strangely updating the queries and creating a new schema in planetscale (I could debug the newly generated files when switching from explicit to implicit relations) it was still picking up files either from the vscode local history extension (xyz.local-history) or dist folder at times and building incorrect queries randomly 🤷‍♂️

I can confirm that the workarounds mentioned here work for both implicit and explicit many-to-many relations.

smasala on Jan 16, 2022

Updated the report and found where the bug is coming from. Prisma is creating a query that doesn’t even exist

Added more detailed replication envrioment
Potentially found the root cause

bardzh on Dec 19, 2021

This also affects non PlanetScale (MySQL 8) databases: https://github.com/prisma/prisma/issues/12810 Note the full schema and script for reproduction there: https://github.com/prisma/prisma/issues/12810#issuecomment-1098427246

janpio on Apr 13, 2022

Facing a similar error as well – I am trying to delete a record that is connected to another record via onDelete: Cascade but it won’t let me delete – I’m getting the following error:

ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Server(ServerError { code: 1105, message: "symbol db.Model.attribute not found", state: "HY000" })) })

When I remove referentialIntegrity = "prisma" the delete of the first record goes through but the related records are not deleted.

steven-tey on Apr 11, 2022

I’m also experiencing this same issue with an explicit many-to-many relation. I can also confirm that the work-around posted here of removing referentialIntegrity = "prisma" and then re-generating the client without running db push work as expected.

Edit: Also setting onUpdate: NoAction in the join table of the many-to-many relation seems to fix it.

focux on Jan 16, 2022

Yeah @janpio, did it here https://github.com/prisma/prisma/issues/11191. Thank you very much for the attention. If you need any help ask me there.

edmbn on Jan 16, 2022

Yes, but isn’t that what you tried to achieve by after the fact changing the Prisma Schema file? That is very error prone (as e.g. the next db push will try to create foreign keys again) - so until this bug about onUpdate: Cascade is fixed (which it will, referentialintegrity currently is a preview feature expected to not be fully working) using NoAction might be the safer option.

janpio on Dec 26, 2021

Removing referentialIntegrity = "prisma" temporarily from the Prisma schema without running db push or similar (!) causes the same script to work fine on PlanetScale. The log output looks like this then:

  prisma:client Prisma Client call: +699ms
  prisma:client prisma.user.update({
  prisma:client   where: {
  prisma:client     id: 'c93eed48-b917-43a0-ae39-1a1df639bfde'
  prisma:client   },
  prisma:client   data: {
  prisma:client     name: 'name'
  prisma:client   }
  prisma:client }) +1ms
  prisma:client Generated request: +3ms
  prisma:client mutation {
  prisma:client   updateOneUser(
  prisma:client     where: {
  prisma:client       id: "c93eed48-b917-43a0-ae39-1a1df639bfde"
  prisma:client     }
  prisma:client     data: {
  prisma:client       name: "name"
  prisma:client     }
  prisma:client   ) {
  prisma:client     id
  prisma:client     name
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client:libraryEngine sending request, this.libraryStarted: true +302ms
prisma:query BEGIN
prisma:query SELECT `10569`.`User`.`id` FROM `10569`.`User` WHERE `10569`.`User`.`id` = ?
prisma:query UPDATE `10569`.`User` SET `name` = ? WHERE `10569`.`User`.`id` IN (?)
prisma:query SELECT `10569`.`User`.`id`, `10569`.`User`.`name` FROM `10569`.`User` WHERE `10569`.`User`.`id` = ? LIMIT ? OFFSET ?
prisma:query COMMIT

Same with a normal MySQL database, running db push to get a schema with foreign keys, and then running the script - all fine.

From that we can conclude that this is a problem of the emulated referential actions of the preview feature referentialIntegrity and setting its value to prisma (as required for PlanetScale).

janpio on Dec 22, 2021