Compatibility with sql_generate_invisible_primary_key MySQL setting on v8.0.32

[simmons-kyle]

Bug description

Recently our Microsoft Azure hosted MySQL server infrastructure was upgraded to 8.0.32 from 8.0.30 which had set the MySQL server variable for "sql_generate_invisible_primary_key" from 0 to 1.

We noticed that since that had happened any table creation via scripts generated through prisma migrate for many-to-many joining tables were gaining an extra column being set as the primary key named my_row_id.

This additional column then causes any subsequent migrations to generate additional scripts to remove these fields. Running this migration then fails with the error:

This version of MySQL doesn't yet support 'existing primary key drop without adding a new primary key. In @@sql_generate_invisible_primary_key=ON mode table should have a primary key. Please add a new primary key to be able to drop existing primary key.'

I'm unsure whether there is anything we can use from a Prisma POV to ensure compatibility with this so that columns are in sync with what the prisma migration files suggest, for the time being we are looking to make sure this setting is set back to 0 to workaround it though. Thankfully we don't think any tables were created with this setting in place in a production setting so we're good to reset DBs accordingly to revert the incompatible changes.

How to reproduce

• Ensure MySQL is running v8.0.32
• Ensure sql_generate_invisible_primary_key setting is set to 1
• Create a many-to-many relationship in schema.prisma which would result in a joining table being created via the generated migration scripts
• Run prisma migrate CLI to apply changes to the database
• Upon checking the created table, a new PK column titled my_row_id is created
• Make any change to the schema.prisma file and generate the migration script for it
• Execute the migration script through the prisma migrate CLI
• The error mentioned above is thrown

Expected behavior

No response

Prisma information

Example of many-to-many relationship syntax which causes issue:

model Category {
  id        String           @id @default(cuid()) @db.Char(30)
  ...
  subTopics SubTopicFields[] @relation("categoriesinsubtopic")

  @@map("category")
}

model SubTopicFields {
  id         String     @id @default(cuid()) @db.Char(30)
  ...
  categories Category[] @relation("categoriesinsubtopic")

  @@map("subTopicfields")
}

Environment & setup

Database: MySQL v8.0.32

Prisma Version

4.11.0

[simmons-kyle]

We've done a bit more investigation our side and the MySQL version upgrade to 8.0.32 might be a red herring. We noticed that Microsoft published an article on 30th August 2023 claiming this server variable is enabled by default (previously it was disabled) so we suspect this might've been silently applied but yet to confirm that.

However we have confirmed that disabling the variable does reinstate expected behaviour for creation of the joining tables based on the scripts generated from prisma migrate.

[janpio]

Prisma is currently definitely not compatible with sql_generate_invisible_primary_key. We would have to dig into this feature to understand how we can make it work with Prisma, and also adapt our tests. That is not very high on our priority list, so you are better off disabling it.

PS: Wow, that is quite a change from Azure.

[simmons-kyle]

Thanks for confirming @janpio - I figured that might be the case, thought it'd be worth getting an issue raised here in case anyone else faces the same problem.

And yes on the Azure front they're certainly keeping us on our toes!

[janpio]

Yes, let's keep this issue open for dealing with the edge case. Hopefully not that many people run into this, but if they do, they can comment upvote here.

[rahulbhadja]

@simmons-kyle did you get any solution for this, I'm having the same problem, any help would save my day. :)

[simmons-kyle]

@rahulbhadja the only solution we had was ensuring that the sql_generate_invisible_primary_key variable is set to false. Typically it was false for us but an enforced change to the Azure hosted MySQL instances modified it to true - we just reverted that change back to resolve.