Migration error on directus_fields.group

[mickeymonz]

Preflight Checklist

• I have completed all Troubleshooting Steps.
• I'm on the latest version of Directus.
• There's no other issue that already describes my problem.

Describe the Bug

At first I haven't completed all Troubleshooting Steps. because when I used this command "directus database migrate:latest"
the log show ERROR "alter table directus_fields drop group - ER_DROP_INDEX_FK: Cannot drop index 'directus_fields_group_foreign': needed in a foreign key constraint"

The proplem is I couldn't find fields that I added in group field on Directus(CMS) after I had updated Directus version 9.0.0-rc.95
but field's data still in database.

I tried to add a new collection and field in a group field but It's still not working and show this error popup (Cannot read property 'slice' of undefined)

To Reproduce

1. Update Directus version from 9.0.0-rc.94 to 9.0.0-rc.95
2. use command => npx directus database migrate:latest
3. use command => npx directus start
4. open Directus CMS => find collection that have group field
5. create a new collection and add field in group field

What version of Directus are you using?

9.0.0-rc.95

What version of Node.js are you using?

14.17.6

What database are you using?

5.7.mysql_aurora.2.10.0

What browser are you using?

Chrome

What operating system are you using?

macOS

How are you deploying Directus?

locally

[micheleferraro]

Hi, I got the same error during database migration:

alter table `directus_fields` drop `group` - ER_DROP_INDEX_FK: Cannot drop index 'directus_fields_group_foreign': needed in a foreign key constraint 
Error: ER_DROP_INDEX_FK: Cannot drop index 'directus_fields_group_foreign': needed in a foreign key constraint
    at Query.Sequence._packetToError (/home/directus/directus/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/home/directus/directus/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/home/directus/directus/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/home/directus/directus/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/home/directus/directus/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/home/directus/directus/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/home/directus/directus/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/home/directus/directus/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:400:28)
    at addChunk (internal/streams/readable.js:290:12)
    --------------------
    at Protocol._enqueue (/home/directus/directus/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/home/directus/directus/node_modules/mysql/lib/Connection.js:198:25)
    at /home/directus/directus/node_modules/knex/lib/dialects/mysql/index.js:126:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/home/directus/directus/node_modules/knex/lib/dialects/mysql/index.js:120:12)
    at executeQuery (/home/directus/directus/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_MySQL.query (/home/directus/directus/node_modules/knex/lib/client.js:134:12)
    at Runner.query (/home/directus/directus/node_modules/knex/lib/execution/runner.js:130:36)
    at Runner.queryArray (/home/directus/directus/node_modules/knex/lib/execution/runner.js:224:21)
    at ensureConnectionCallback (/home/directus/directus/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:11:19)

[dstoyanoff]

I am also experiencing an issue with the group, that is related to #8113

I have checked the migration code and I found a bug here - https://github.com/directus/directus/blob/main/api/src/database/migrations/20210927A-replace-fields-group.ts#L7

Basically, if you don't have any groups in the app before the migration ran, it would just exit and not execute the alteration of the column.

@rijkvanzanten, FYI

[neo7-studio-web]

Same problem here

[chrisbartley]

Tip for others having the same problem when attempting to upgrade...

I got the error when trying to upgrade from rc.93, but I wasn't sure what to do to revert. I eventually found the docs to downgrade. I did this:

$ npm install directus@9.0.0-rc.93

And then tried to do the down migration:

$ npx directus database migrate:down
11:34:40 ✨ Loaded extensions: categories, search
11:34:40 ✨ Running migrations...
11:34:40 🚨 Couldn't find migration
Error: Couldn't find migration
...

Since that failed, I just tried this, and it appears to have worked:

$ npx directus database migrate:latest
11:34:52 ✨ Loaded extensions: categories, search
11:34:52 ✨ Running migrations...
11:34:52 ✨ Database up to date

[u12206050]

I still have this problem. Can't migrate at all. Have removed the migration but that doesn't help.
Still get alter table directus_fieldsdrop foreign keydirectus_fields_group_foreign- ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'directus_fields_group_foreign'; check that column/key exists

Event though it does exist.

[rijkvanzanten]

@u12206050 Mind opening a new issue? While related, that error seems different from the OP 👍🏻

[faberv]

Same issue:

Upgrading from rc.93 to rc.100

[azrikahar]

@faberv it does look somewhat similar for the first half, however your issue should be different since the error shows the column/key may not exist. OP's error is related to foreign key constraint, but the column still exists.

This is also fairly odd since the Replace Fields Groups migration will re-create the group column nearing the end of the migration process. It seems as though your migration ran half-way 🤔

I'd recommend making sure you have a backup of your data just to be safe, then re-run the migrations (upgrade from rc.93 to rc.100 again, only if you still have the rc.93 copy).

If that doesn't work, do create a new issue for it moving forward 👍

[faberv]

@azrikahar Thank you for your reply.

Finally I solved this problem that I upgraded and migrated through each version (93 -> 94 -> 95 -> ... -> 100) and everything works fine. Interesting 😄

Btw. you guys are doing great job. Directus is outstanding 👏 Thank you.

[azrikahar]

Finally I solved this problem that I upgraded and migrated through each version (93 -> 94 -> 95 -> ... -> 100) and everything works fine. Interesting 😄

@faberv That's interesting indeed, since technically the migrations order should still be the same without jumping 1 by 1. However there were some quirks with one or two migrations (which were fixed later on), so those could be it. Glad it's resolved! 👍

Just a minor note, perhaps you can check out this: #8675 (comment), since I half-suspect upgrading 1 by 1 may still end up applying the "faulty" version of that migration.