Migration errors between v5 beta.1 and beta.2

[verdverm]

Bug report

pulling in convo from Discord...

Required System information

• Node.js version: v20.12.0
• NPM version: v10.5.0
• Strapi version: v5-beta.1 -> v5.beta.2
• Database: MySQL 8.3 via Dolt
• Operating system: mac m2 / sonoma 14.2.1
• Is your project Javascript or Typescript: TS

... Did you know you can run `yarn strapi report` or `npm run strapi report` to get this information easier? ... not until after I filled in all the information, perhaps this comment could be moved before the questions?

Describe the bug

Hit a couple of bugs during database migration after

• renameColumn issue (Altar table fails against MySQL, possibly invalid SQL generation knex/knex#6053)
• drop index issue, after fixing the issues from the 5.0.0-rename-identifiers-longer-than-max-length migration (ran the alter tables by hand)

yarn run v1.22.21
$ strapi develop
✔ Cleaning dist dir (7ms)
⠋ Loading Strapi⠋ Building build context
[INFO] Including the following ENV variables as part of the JS bundle:
    - ADMIN_PATH
    - STRAPI_ADMIN_BACKEND_URL
    - STRAPI_TELEMETRY_DISABLED
✔ Building build context (31ms)
✔ Creating admin (150ms)
⠴ Loading Strapi{
  event: 'migrating',
  name: '5.0.0-rename-identifiers-longer-than-max-length'
}
⠙ Loading Strapi{
  event: 'migrated',
  name: '5.0.0-rename-identifiers-longer-than-max-length',
  durationSeconds: 0.453
}
{ event: 'migrating', name: '5.0.0-02-created-document-id' }
⠴ Loading Strapi{
  event: 'migrated',
  name: '5.0.0-02-created-document-id',
  durationSeconds: 0.339
}
⠦ Loading Strapi[ERROR]  There seems to be an unexpected error, try again with --debug for more information 

┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                                            │
│   Error: alter table `admin_permissions_role_lnk` drop index `admin_permissions_role_links_inv_fk` - cannot drop index:    │
│   `admin_permissions_role_links_inv_fk` is used by foreign key `admin_permissions_role_links_inv_fk`                       │
│   at Packet.asError (/Users/tony/ts/services/strapi/node_modules/mysql2/lib/packets/packet.js:728:17)                      │
│   at Query.execute (/Users/tony/ts/services/strapi/node_modules/mysql2/lib/commands/command.js:29:26)                      │
│   at Connection.handlePacket (/Users/tony/ts/services/strapi/node_modules/mysql2/lib/connection.js:478:34)                 │
│   at PacketParser.onPacket (/Users/tony/ts/services/strapi/node_modules/mysql2/lib/connection.js:97:12)                    │
│   at PacketParser.executeStart (/Users/tony/ts/services/strapi/node_modules/mysql2/lib/packet_parser.js:75:16)             │
│   at Socket.<anonymous> (/Users/tony/ts/services/strapi/node_modules/mysql2/lib/connection.js:104:25)                      │
│   at Socket.emit (node:events:519:28)                                                                                      │
│   at Socket.emit (node:domain:488:12)                                                                                      │
│   at addChunk (node:internal/streams/readable:559:12)                                                                      │
│   at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)                                                   │
│                                                                                                                            │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
✨  Done in 5.11s

Steps to reproduce the behavior

1. Create new project with Strapi@v5-beta.1
2. Use MySQL 8.3
3. Update to strapi@v5-beta.2
4. yarn dev

I think

Expected behavior

migrations work and the app starts up normally

[verdverm]

Repro script for reproducing the database migration error between the beta versions

#!/bin/bash

# catch ctrl-c without exiting script
trap "echo 'stopping strapi'" SIGINT;

# cleanup any existing repro dir
rm -rf repro/

# start mysql
docker rm -f repro-mysql
docker run -d \
  -e MYSQL_ROOT_PASSWORD=strapi \
  -e MYSQL_PASSWORD=strapi \
  -e MYSQL_USER=strapi \
  -e MYSQL_DATABASE=strapi \
  -p 3306:3306 \
  --name repro-mysql \
  mysql:8.3.0

# create app at previos version
npx create-strapi-app@5.0.0-beta.1 repro --typescript --quickstart --no-run

# move to new app for doing work
cd repro

# setup strapi for mysql
yarn add mysql2
export DATABASE_CLIENT=mysql

# start app first time
yarn develop

# stop server with ctrl-c

# update strapi
yarn add @strapi/strapi@5.0.0-beta.2

# restart / upgrade app
yarn develop

The second (post update) has the following error

yarn run v1.22.21
$ strapi develop
✔ Cleaning dist dir (4ms)
⠋ Loading Strapi⠋ Building build context
[INFO] Including the following ENV variables as part of the JS bundle:
    - ADMIN_PATH
    - STRAPI_ADMIN_BACKEND_URL
    - STRAPI_TELEMETRY_DISABLED
✔ Building build context (39ms)
⠙ Creating adminRe-optimizing dependencies because lockfile has changed
✔ Creating admin (175ms)
⠼ Loading Strapi{
  event: 'migrating',
  name: '5.0.0-rename-identifiers-longer-than-max-length'
}
Transaction was implicitly committed, do not mix transactions and DDL with MySQL (#805)
⠴ Loading StrapiTransaction was implicitly committed, do not mix transactions and DDL with MySQL (#805)
⠇ Loading Strapi{
  event: 'migrated',
  name: '5.0.0-rename-identifiers-longer-than-max-length',
  durationSeconds: 0.306
}
{ event: 'migrating', name: '5.0.0-02-created-document-id' }
⠏ Loading Strapi{
  event: 'migrated',
  name: '5.0.0-02-created-document-id',
  durationSeconds: 0.081
}
⠙ Loading Strapi[ERROR]  There seems to be an unexpected error, try again with --debug for more information 

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                                       │
│   Error: alter table `admin_permissions_role_lnk` drop index `admin_permissions_role_links_fk` - Cannot drop index    │
│   'admin_permissions_role_links_fk': needed in a foreign key constraint                                               │
│   at Packet.asError (/Users/tony/ts/tmp/repro/node_modules/mysql2/lib/packets/packet.js:728:17)                       │
│   at Query.execute (/Users/tony/ts/tmp/repro/node_modules/mysql2/lib/commands/command.js:29:26)                       │
│   at Connection.handlePacket (/Users/tony/ts/tmp/repro/node_modules/mysql2/lib/connection.js:481:34)                  │
│   at PacketParser.onPacket (/Users/tony/ts/tmp/repro/node_modules/mysql2/lib/connection.js:97:12)                     │
│   at PacketParser.executeStart (/Users/tony/ts/tmp/repro/node_modules/mysql2/lib/packet_parser.js:75:16)              │
│   at Socket.<anonymous> (/Users/tony/ts/tmp/repro/node_modules/mysql2/lib/connection.js:104:25)                       │
│   at Socket.emit (node:events:518:28)                                                                                 │
│   at Socket.emit (node:domain:488:12)                                                                                 │
│   at addChunk (node:internal/streams/readable:559:12)                                                                 │
│   at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)                                              │
│                                                                                                                       │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
✨  Done in 3.31s.

[verdverm]

Note, the upgrade does work against sqlite, but it seems there are some escape hatches in the code that skip certain steps for that database

[innerdvations]

After investigating, the first part of the issue is caused by a bug in Dolt, but the second part with foreign keys unable to be dropped in the db sync appears to be valid. I've got a fix here.