Upgrading Ghost 3.X - 4.X

Upgrading to the latest version of 3.X was no problem, but then moving from 3.X to 4 everything blew up because of database schema changes with messages like this

A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table `members_status_events` add constraint `members_status_events_membelete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_statu
Help: Error occurred while executing the following migration: 10-add-members-status-events-table.js
Suggestion: journalctl -u ghost_siliconheaven-info -n 50

This is because I have been using ghost since pre 1.X version and the underlying backing mysql database running this is unable to run the migrations.  Why is that?  It is because MySql has moved to a default charset of utfmb4 as the charset and default collation of utf8mb4_general_ci

In this situation the migration scripts for ghost 4 which uses knex.js it appears assume that the MySql database has moved to this collation and when creating tables this underlying assumption means schema migration and creation must be in this format for their scripts.

What was my collation and charset?  I did not know so to log into MySql instance and find out I had to remember the nice ghost variable finders

ghost config get database.connection.host
ghost config get database.connection.user
ghost config get database.connection.password
ghost config get database.connection.database

Running these from the command line gives me the information I need to log into MySql and determine what my settings are with this command

mysql -u ghost -p in my case the user returned from above was ghost

Then at the prompt: Enter password: copy your password from above that ghost keeps for you and success.

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Run the command SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='ghost_production';

and I got

+------------------+----------------------------+------------------------+
| SCHEMA_NAME      | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+------------------+----------------------------+------------------------+
| ghost_production | latin1                     | latin1_swedish_ci      |
+------------------+----------------------------+------------------------+
1 row in set (0.00 sec)

What? Why Swedish?  Well one of the original creators of MySql was Swedish Michael Widenius so this default got placed in.

Now to change it to the expected utf8 formats

ALTER DATABASE ghost_production DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

Run the select command again from above and

+------------------+----------------------------+------------------------+
| SCHEMA_NAME      | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+------------------+----------------------------+------------------------+
| ghost_production | utf8mb4                    | utf8mb4_general_ci     |
+------------------+----------------------------+------------------------+
1 row in set (0.00 sec)

Now the MySql database is at least in the correct format for expected migrations so now run ghost update

Lots of things happen and in general it is fine, however at the end of the upgrade the instance is started and then boom!

✖ Starting Ghost: siliconheaven-info
A GhostError occurred.

Message: Ghost was able to start, but errored during boot with: alter table `members_status_events` add constraint `members_status_events_member_id_foreign` foreign key (`member_id`) references `members` (`idelete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_status_events_member_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 10-add-members-status-events-table.js
Suggestion: journalctl -u ghost_siliconheaven-info -n 50

Why?  The collation and charset is in the proper format and at this point ghost can't rollback migrations so you're stuck with a dead site until you resolve this or restore from a backup (you made a backup prior to this?  Haha nope, neither did I).

Secret answer it's not broken it just breaks at that step because the collation script thing is still acting funny, but if you rerun the command ghost start over and over it will proceed to each next script migration script and break until you get through all the migration creations and the site will pop back up.  Yes, this is a bit scary but it's possible because I know the knex.js migration steps are blowing up at the upgrade but are doing the work they say actually so really you can get past this with brute force start, start, start.  I had it fail on scripts 5, 7, 9, 10, 11, and finally 13 so I ran ghost start 6 times to get all the tables and schemas upgraded.  Scary, but possible.  I probably should make that backup and do a fresh install as I now have some weird unexpected settings, but that is for another time.

Finally I want my google search to show up properly so I'm doing the following.

Fix the google search bar integration by disabling the site navigation element and the ghost header nav elements

<script async src="https://cse.google.com/cse.js?cx=<my_google_id>"></script>
<div class="gcse-search"></div>
<style type="text/css">
  .site-nav-main {
      visibility: hidden !important;
  }
  .gh-head {
	visibility: hidden !important;
  }
</style>