Overflowing stack on cyclic actions

[pimeys]

Bug description

When handling the referential integrity in the Query Core, a referential action that cascades an action to the children can cause a stack overflow if at some point the reference causes a circle.

This issue can be solved in two places:

• Migrate team can utilize SQL Server validations, and prevent cyclical actions during validation.
• The Client team can handle cycles and stop the overflow if going to an endless recursion/loop.

How to reproduce

Simple example is a self relation:

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

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

model A {
  id     Int  @id
  child  A?   @relation(name: "aa")
  parent A?   @relation(name: "aa", fields: [aId], references: [id], onDelete: Cascade)
  aId    Int?
}

Here deleting an A will try to delete an A that deletes an A and the loop continues until we hit a stack overflow.

There are more examples in our SQL Server test suite for data model validator, that might or might not have the same issue. The first part of the issue is to find out what works and what not with Prisma: https://github.com/prisma/prisma-engines/blob/master/libs/datamodel/core/tests/attributes/relations/referential_actions/cycle_detection.rs

Expected behavior

Either throw a validation error in the data model parser, or handle the cycles without overflowing the stack in the Query Core. It is to be decided which path we take before working on the issue.

Prisma information

All versions which can emulate Cascade, SetNull or SetDefault are suspects.

Environment & setup

• OS: The Best OS

Prisma Version

at least 3.3.0

---

Originally reported here #9380 (comment) by @tastnt with this text:

I have a model which references itself as follows:

model api_dataobject {
  id                       Int                        @id @default(autoincrement())
  related_to_id            Int?
  related_to               api_dataobject?            @relation("api_dataobjectToapi_dataobject_related_to_id", fields: [related_to_id], references: [id], onDelete: Cascade, onUpdate: NoAction, map: "api_dataobject_related_to_id_6921f33f_fk_api_dataobject_id")
  related                  api_dataobject[]           @relation("api_dataobjectToapi_dataobject_related_to_id")
}

As shown above, I set the onDelete referential action of the self reference to Cascade. When I try to delete a row which is referenced by another one, Prisma Studio crashes without showing any error. I don't see any debug information either when I use the Prisma client through NodeJS, it just freezes as well.

Environment variables loaded from .env
prisma                  : 3.3.0
@prisma/client          : 3.3.0
Current platform        : windows
Query Engine (Node-API) : libquery-engine 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Migration Engine        : migration-engine-cli 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine    : introspection-core 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary           : prisma-fmt 33838b0f78f1fe9052cf9a00e9761c9dc097a63c (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Default Engines Hash    : 33838b0f78f1fe9052cf9a00e9761c9dc097a63c
Studio                  : 0.438.0
Preview Features        : referentialIntegrity

[tastnt]

@floelhoeffel Thank you for coming back to me so quickly. I think @pimeys already answered your questions with his detailed bug report above, but just in case, see my answers below.

So you have an entry which self-references and then the onDelete cascading crashes, correct?

Well, I have multiple rows in the same table, and some rows reference a single one as their parent (so not the same row referencing itself).

Could you explain the usecase for the self reference? What are you trying to achieve?

My specific use case is about having entries about an image and its thumbnails in the database. All the thumbnails in different sizes reference the main image as their parent.

Could you describe what kind of behavior you would expect?

When I delete the main image, I want all the thumbnails to be (cascade) deleted as well.

[pimeys]

So, I wouldn't expect a properly working cyclic cascades to happen very soon. Our query engine doesn't do JIT with the queries, so all the query planning is done first and only after that executed. This means a cascade that cycles back to itself will just generate a query until stack overflow, a bit like what Microsoft SQL Server would do... Therefore the first action will be that we just prevent cascading cycles completely if Prisma handles referential integrity...

The thing you can do right now is cut the cycle with NoAction for updates and deletes, then handling the deletion in your application code.