MySQL does not support onDelete: setDefault

[janpio]

When trying to migrate a super simple schema to MySQL with an onDelete: setDefault, this happens:

C:\Users\Jan\Documents\throwaway\setdefault>npx prisma db push     
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": MySQL database "purple_kingfisher" at "mysql-db-provision.cm0mkpwj8arx.eu-central-1.rds.amazonaws.com:3306"
Error: Cannot add foreign key constraint
   0: sql_migration_connector::sql_database_step_applier::apply_migration
             at migration-engine\connectors\sql-migration-connector\src\sql_database_step_applier.rs:11
   1: migration_core::api::SchemaPush
             at migration-engine\core\src\api.rs:187

model OnDeleteSetDefaultParent {
  id                Int                                @id @default(autoincrement())
  name              String                             @unique
  mandatoryChildren OnDeleteSetDefaultMandatoryChild[]
}

model OnDeleteSetDefaultMandatoryChild {
  id       Int                      @id @default(autoincrement())
  name     String                   @unique
  parent   OnDeleteSetDefaultParent @relation(fields: [parentId], references: [id], onDelete: SetDefault)
  parentId Int                      @default(1)
}

Per our documentation this should fundamentally work (if maybe a bit different than expected):

But looking at the MySQL docs, this does not seem to be supported at all:

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

• https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html#:~:text=SET%20DEFAULT%3A%20This%20action%20is%20recognized%20by%20the%20MySQL%20parser%2C%20but%20both%20InnoDB%20and%20NDB%20reject%20table%20definitions%20containing%20ON%20DELETE%20SET%20DEFAULT%20or%20ON%20UPDATE%20SET%20DEFAULT%20clauses.
• https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html#:~:text=SET%20DEFAULT%3A%20This%20action%20is%20recognized%20by%20the%20MySQL%20parser%2C%20but%20both%20InnoDB%20and%20NDB%20reject%20table%20definitions%20containing%20ON%20DELETE%20SET%20DEFAULT%20or%20ON%20UPDATE%20SET%20DEFAULT%20clauses.

Similar for MariaDB:

The SET DEFAULT action is not supported.

• https://mariadb.com/kb/en/foreign-keys/#:~:text=The%20SET%20DEFAULT%20action%20is%20not%20supported.

We even mention similar in our engines comments:
https://github.com/prisma/prisma-engines/blob/ccf3dc944acdabb431947150e12b984b34c538cd/query-engine/connector-test-kit-rs/query-engine-tests/tests/new/ref_actions/on_delete/set_default.rs#L1
https://github.com/prisma/prisma-engines/blob/ccf3dc944acdabb431947150e12b984b34c538cd/migration-engine/migration-engine-tests/tests/migrations/relations.rs#L565-L566

We should adapt our validation to not allow this, and update our documentation afterwards as well. No need for our users to waste their time with this.

[janpio]

Seems to already be a validation with preview feature referentialIntegrity and configuration referentialIntegrity = "foreignKeys".

[pimeys]

What exact MySQL version you are running?

[janpio]

My output was from a 5.7.33-log, but docs sound identical to 8.x.

[pimeys]

Yeah, whatever MySQL version we were using in the time of the writing of our docs silently just did not use the given action. This might've changed in a point release, as we know MySQL does this between x.x.x releases. Also, SetDefault should work with MyISAM and other MySQL engines, just not with InnoDB.

[janpio]

Prisma supports only InnoDB, so essentially does not support SET DEFAULT (which is fine, we should just validate it properly and document it as well).

[pimeys]

If we say we only support InnoDB, I'm OK validating against using SetDefault on MySQL.

[janpio]

I think it's the current reality when you create a schema via Migrate. As we do not have the table engine information in the schema (yet, there might be an issue floating around) I think this is the easiest option right now.

[Jolg42]

TODO check if it works with MySQL 8 (and not in 5.7) like mentioned in prisma/prisma-engines#2957

[jkomyno]

Considering that we only support InnoDB, I confirm that SET DEFAULT only works from mysql:8, as shown here.

[janpio]

The linked issue is a different topic, please pull in the relevant information that describes the support of SET DEFAULT on MySQL in here.

[jkomyno]

I confirm that ON DELETE SET DEFAULT is supported in CREATE TABLE statements for the following database versions:

• mysql:8 and beyond
• mariadb:10.9 and beyond (possibly earlier versions as well, but definitely not mariadb:10.0)

mysql:5.6 thus does not support it.

[Jolg42]

Decision: Document that setDefault does not work on older MySQL and MariaDB

[Jolg42]

Closing in favor of docs issue prisma/docs#3885

[jkomyno]

I have just found out that MySQL 8.0 docs report the following:

SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

[jkomyno]

I have summarised my findings in this internal notion doc.

[jkomyno]

We have realized that providing support for the SetDefault referential action on the mysql provider is problematic, as MySQL never truly supported such action. This means that the following schema, although valid, can cause runtime errors:

datasource db {
  provider = "postgres"
  url      = env("DATABASE_URL")
}

model User {
  id    Int    @id @default(autoincrement())
  posts Post[]
}

model Post {
  id     Int   @id @default(autoincrement())
  user   User? @relation(fields: [userId], references: [id], onUpdate: SetDefault, onDelete: SetDefault)
  userId Int?  @default(3)
}

The MySQL docs claim that table definitions with SET DEFAULT referential actions are rejected from InnoDB, which is the default storage engine and the only one supported by Prisma. In reality, mysql:8 users can successfully create/alter tables with SET DEFAULT referential actions, but those referential actions would fail at runtime when triggered. They’d fail with a foreign key constraint error, as they're confusingly interpreted as NO ACTION referential actions. On the other hand, mysql:5.6+ users can’t even create/alter tables containing SET DEFAULT referential actions, as they’d get a syntax error.

[jkomyno]

Closing this issue in favor of #16259, which will implement warnings in this issue's scenarios, as to avoid breaking changes.