When Ghost upgrade dies on "ERROR alter table `products_benefits`"

There is a bit of a surprise in upgrading your Ubuntu to 20.04.2 , because that will include upgrading your MySQL to mysql Ver 8.0.25, and before you know it, you're upgrading your LTS Node Version: v14.16.1 , your Ghost-CLI Version: 1.16.3 and finally, your Ghost version.

And that's when your site completely breaks because you can not upgrade it. As Scooby Doo says: ruh-roh.

If you hit up the Ghost forums, they're familiar with this issue, and there's even a solution posted here.

"OK I think I got it ! It drove me crazy for a couple of hours. Like many people here I was using a Digital Ocean droplet, it updated to Ubuntu 20, MySQL updated to 8 without asking."
jebarjonet explains, before offering his solution.

That solution, clever as it is, did not work for me, so here's mine.

Start by your usual upgrading steps, such as making a backup of your themes and data with "cp -R content backup"

Make a copy of your database just in case of FUBAR.

Go straight to the source, and in MySQL try to change every table at once.

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

You will get Mysql complaining because the primary key is connected to another one. The primary key is in table "emails" it's "id".

 Referencing column 'email_id' and referenced column 'id' in foreign key constraint 'email_recipients_email_id_foreign' are incompatible.````

You can change the other table instead.

ALTER TABLE `email_recipients` CHANGE `id` `id` VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

That's about as close as you will get, doing it by hand. This may not be the only table rejecting your attempts, but after I had done that I could run this again, and had no errors.

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

So, after this, it was easy to run "ghost Run" again. Remember, this is what I saw before. Ghost was just not going to upgrade. It refused to co-operate.

ERROR alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'product_id' and referenced column 'id' in foreign key constraint 'products_benefits_product_id_foreign' are incompatible.

alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'product_id' and referenced column 'id' in foreign key constraint 'products_benefits_product_id_foreign' are incompatible.

{"config":{"transaction":false},"name":"03-add-products-benefits-table.js"}
"Error occurred while executing the following migration: 03-add-products-benefits-table.js"

Error ID:
    300

Error Code: 
    UNKNOWN_CODE_PLEASE_REPORT

----------------------------------------

InternalServerError: alter table `products_benefits` add constraint `products_benefits_product_id_foreign` foreign key (`product_id`) references `products` (`id`) on delete CASCADE - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'product_id' and referenced column 'id' in foreign key constraint 'products_benefits_product_id_foreign' are incompatible.


WARN Ghost is shutting down
WARN Ghost has shut down
WARN Your site is now offline
WARN Ghost was running for a few seconds

So, the struggle there is clearly an issue adding the table "benefits". So, let's add that table! Here it is;

--
-- Table structure for table `products_benefits`
--

CREATE TABLE `products_benefits` (
  `id` varchar(24) NOT NULL,
  `product_id` varchar(24) NOT NULL,
  `benefit_id` varchar(24) NOT NULL,
  `sort_order` int UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


--
-- Indexes for table `products_benefits`
--
ALTER TABLE `products_benefits`
  ADD PRIMARY KEY (`id`),
  ADD KEY `products_benefits_product_id_foreign` (`product_id`),
  ADD KEY `products_benefits_benefit_id_foreign` (`benefit_id`);
COMMIT;

This clears up any remaining issues.

So now when I run "ghost run" everything starts up just fine!