Cms: Query regarding PK/FK index names in Craft CMS 3.6

[jamesmacwhite]

Description

I hit a problem with a Craft 2 DB migration with the Retour plugin installed when going from Craft 2 to Craft 3.6, the full details are here: https://github.com/nystudio107/craft-retour/issues/155

Up until Craft CMS 3.6 this migration ran fine however, so that's why I'm querying it at the core Craft CMS issue tracker to get some additional context if possible. From my understanding there is a behaviour change with the PK and FK names in Craft 3.6 which I may be hitting here #7153.

The migration in Retour has not changed in two years and I confirmed it worked fine on anything lower than Craft CMS 3.6, so it would seem something core in Craft CMS is the reason for the breakage, but I appreciate the fix/solution might not necessarily be at the core Craft CMS level either, but I'm just looking for further guidance until @khalwat has time to review the issue on the Retour issue tracker.

Is the way the migration is dropping and recreating indexes now no longer compatible with Craft CMS 3.6?

Steps to reproduce

1. Migrate Craft 2 DB to Craft 3.6 (app migration track)
2. Install retour plugin and run the plugin migration track
3. Retour breaks on this migration: https://github.com/nystudio107/craft-retour/blob/v3/src/migrations/m181216_043222_rebuild_indexes.php due to non existent index

Additional info

• Craft version: 3.6
• PHP version: 7.4
• Database driver & version: MySQL 8

[khalwat]

This is a very old migration, I'm confused as to why it would fail with Craft CMS 3.6 -- some input from Pixel & Tonic would be helpful.

[jamesmacwhite]

So am I, but I'm think it must be to do with Primary Key/foreign key changes possibly. I've confirmed it doesn't happen on anything below 3.6. I've ran it many times on various 3.x versions and only now has it failed.

[brandonkelly]

As of Craft 3.6, index names are now random (see 2ad90a6886ca87e2a37e43b2573c4740ad80c89b / #7153).

But your migration should never have been assuming that the indexes would have specific names, as those could be changed depending on the charset used, the length of the tablePrefix, or just because some developer decided to go in and delete/rename an index. So that’s why we have MigrationHelper::dropIndexIfExists() which should be used instead of $this->dropIndex(). Just submitted https://github.com/nystudio107/craft-retour/pull/157 with a fix.

[khalwat]

Wow, thank you!