Migration error for site with no members

Developer help
1

My Ghost blogs fail to migrate and all have no members/subscribers. When migrating to ghost v4 via the latest cli, they fail to migrate and are left in an unusable state, forcing me to reinstall.

To Reproduce

  1. Have a ghost blog with no members on the latest Ubuntu, Node, etc.
  2. Upgrade from Ghost v3.42.4
Ghost was able to start, but errored during boot with: alter table `members_subscribe_events` add constraint `members_subscribe_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_subscribe_events_member_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 05-add-members-subscribe-events-table.js

Any other info e.g. Why do you consider this to be a bug? What did you expect to happen instead?

Technical details:

OS: Ubuntu, v20.04.2 LTS
Node Version: v14.16.0
Ghost Version: 4.1.2
Ghost-CLI Version: 1.16.3
Environment: production
Command: 'ghost upgrade'
```v
3

UNKNOWN_CODE_PLEASE_REPORT is usually caused by mixed collation across your database tables in MySQL. Did you upgrade MySQL or otherwise change collation?

This issue rears its head differently depending on what migration you run after changing collations, but there is a similar issue here:

and here

Both of which have suggestions for how to diagnose and resolve the issue.