Ghost was able to start, but errored during boot

Problem

This error were reported when updating and starting Ghost. The error message is similar to this:

Ghost was able to start, but errored during boot with: alter table `members_link_click_events` add constraint `members_link_click_events_member_id_foreign` foreign key (`member_id`) references `members` (`id`) on delete CASCADE - Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_link_click_events_member_id_foreign' are incompatible.
Suggestion: journalctl -u ghost_64-227-109-181 -n 50

The error was a lot reported when digitalOcean droplet users updated to Ubuntu 20. During the update, MySQL got updated to the version 8. During the update, the default collate for character set utf8mb4 changed from utf8mb4_general_ci to utf8mb4_0900_ai_ci.

Solution

  1. edit /etc/mysql/conf.d/mysql.cnf 
  2. add the following code
[mysqld]
init-connect='SET default_collation_for_utf8mb4=utf8mb4_general_ci'
  1. run service mysql restart
  2. go to your Ghost folder and run ghost update

I learned this solution from jebarjonet from this ghost forum.