MySQL doesn't support 'existing primary key drop`

[klodoma]

Describe the Bug

I have a mysql server V8.0.21.

When I execute npx directus database migrate:latest I get the following error:

[11:14:28.329] INFO: Applying Marketplace...
[11:14:28.485] ERROR: alter table `directus_extensions` drop primary key - ER_NOT_SUPPORTED_YET: 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.'
    err: {
      "type": "Error",
      "message": "alter table `directus_extensions` drop primary key - ER_NOT_SUPPORTED_YET: 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.'",
      "stack":
          Error: ER_NOT_SUPPORTED_YET: 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.'
              at Sequence._packetToError (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
              at Query.ErrorPacket (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
              at Protocol._parsePacket (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:291:23)
              at Parser._parsePacket (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Parser.js:433:10)
              at Parser.write (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Parser.js:43:10)
              at Protocol.write (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:38:16)
              at TLSSocket.ondata (node:internal/streams/readable:809:22)
              at TLSSocket.emit (node:events:517:28)
              at addChunk (node:internal/streams/readable:368:12)
              at readableAddChunk (node:internal/streams/readable:341:9)
              --------------------
              at Protocol._enqueue (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/protocol/Protocol.js:144:48)
              at Connection.query (/directus/node_modules/.pnpm/mysql@2.18.1/node_modules/mysql/lib/Connection.js:198:25)
              at /directus/node_modules/.pnpm/knex@3.1.0_mysql@2.18.1_pg@8.11.4_sqlite3@5.1.7_tedious@17.0.0/node_modules/knex/lib/dialects/mysql/index.js:137:18
              at new Promise (<anonymous>)
              at Client_MySQL._query (/directus/node_modules/.pnpm/knex@3.1.0_mysql@2.18.1_pg@8.11.4_sqlite3@5.1.7_tedious@17.0.0/node_modules/knex/lib/dialects/mysql/index.js:131:12)
              at executeQuery (/directus/node_modules/.pnpm/knex@3.1.0_mysql@2.18.1_pg@8.11.4_sqlite3@5.1.7_tedious@17.0.0/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
              at Client_MySQL.query (/directus/node_modules/.pnpm/knex@3.1.0_mysql@2.18.1_pg@8.11.4_sqlite3@5.1.7_tedious@17.0.0/node_modules/knex/lib/client.js:154:12)
              at /directus/node_modules/.pnpm/knex@3.1.0_mysql@2.18.1_pg@8.11.4_sqlite3@5.1.7_tedious@17.0.0/node_modules/knex/lib/execution/transaction.js:375:24
              at new Promise (<anonymous>)
              at trxClient.query (/directus/node_modules/.pnpm/knex@3.1.0_mysql@2.18.1_pg@8.11.4_sqlite3@5.1.7_tedious@17.0.0/node_modules/knex/lib/execution/transaction.js:370:12)
      "code": "ER_NOT_SUPPORTED_YET",
      "errno": 1235,
      "sqlMessage": "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.'",
      "sqlState": "42000",
      "index": 0,
      "sql": "alter table `directus_extensions` drop primary key"
    }

To Reproduce

• use Mysql V8.0.21
• run the migrations npx directus database migrate:latest

Directus Version

V10.10.7

Hosting Strategy

Self-Hosted (Docker Image)

[br41nslug]

From which version are you upgrading? I was not able to reproduce this on a new installation of mysql 8

Does downgrading one or 2 times first help perhaps?

[klodoma]

I am upgrading from 10.6.2.
On a new installation of mysql, eg 8.3.* it works;
It fails on older versions of mysql. V8.0.21 in my case.

As I see Generated Invisible Primary Keys (GIPKs) was added in 8.0.30.
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-30.html

[br41nslug]

Then i am afraid a minor update of the database will have to do. We cannot make exceptions at the granularity of specific minor versions of database vendors unfortunately. Directus supports the LTS versions for its database vendors, I have tested this using the current LTS for mysql 8.0 which is 8.0.36 at the moment.

https://docs.directus.io/self-hosted/docker-guide.html#supported-databases
https://endoflife.date/mysql