Ghost Blog Administration

John Hartley -

What to do when Ghost goes wrong (ie you loose a password, upgrade fails etc). A few tips on some common Ghost Site Adminstration tasks.

Status: Updated Sept 2022 following Ghost 4.x to 4.48 update

More Ghost 4.x -> 4.48 update failures ...

Unfortuately it now seems that ghost updates via "ghost update -v4" now almost always fail (mostly due to database migration related issues). So different from initial days of Ghost when keeping a self host instance up and running was a pretty effort activity, no it is a rather frustrating one.

As with other upgrades if your Ghost database was original based on older (pre 8.X) MySQL database then error's due to "collation" are a major issue. To help address this I have updated all my Ghost databases to now have MySQL default character set and collocation.

To do this requires:

  • Stoppingall your Ghost instances that are using the database
  • Disabling the "Foriegn Key Contraints" (logging into DB use: "set foreign_key_checks = 0;") - us without this your table collation updates will fail
  • Change the charater set/collation that applies to the database
  • Change the character set/collabotion that applies to each table within the database
  • Re-enable the "Foriegn Key Contrains" ("set foreign_key_checks = 1;")
  • Restart your Ghost instances

If you have multiple Ghost instances each with their own database, this requires updating hundreds of table. So some automatic means will be required. The follow SQL snippets provide a way to generate the required SQL via SQL and then apply this to the database. Right now I do not have a simple set of single SQL statements that do this all in one hit.

Facts to be aware of:

  • Ghost explicity creates table with character set: "utf8mb4"
  • MySQL has two collation schemes for this character set: "utf8mb4_0900_ai_ci" (the default for 8.X) and "utf8mb4_general_ci"
  • You can change the MySQL defaults in Ubuntu via: "/etc/mysql/conf.d/mysql.cnf" to avoid any future surprises by adding init-connect setting (if you want to force using "utf8mb4_general_ci" then change accordingly):
[mysqld]
init-connect='SET default_collation_for_utf8mb4=utf8mb4_0900_ai_ci'

And now for details on getting the databases and table details required to generate SQL to update tables (note some of this is repetition of what is documented below in "Preparation for MySQL 8 Collation Change", but I have decided to now go with MySQL default collation, as this is what will be created with any new Ghost instances.

#
# -- Finding the databases & tables which do not have right character set/collocation
# -- NOTE: I use convention of having all Ghost db with name -  ghost_<NAME> so they are easy to find
---
--- So to see the databases and tables
---
SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE 'ghost_%' ;
---
--- to see the default character set/collation for database
---
select SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME from information_schema.schemata where schema_name like 'ghost_%' ;
---
--- if you only want to include the subset that do now have
--- the right collation
---
select SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME from information_schema.schemata where SCHEMA_NAME like 'ghost_%' AND DEFAULT_COLLATION_NAME NOT LIKE 'utf8mb4_0900_ai_ci' ;
---
--- Or if you want to see the details down the the column name level..
---
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA LIKE 'ghost_%' AND COLLATION_NAME NOT LIKE 'utf8mb4_0900_ai_ci';
---
--- Now lets generate an SQL statement that will ALTER defalt collation
---  of all our ghost databases by putting result into CONCAT function
---  you can now feed this into sed or edit it with vi to hit all
---  your databases at once
---
SELECT CONCAT('ALTER DATABASE ', TABLE_SCHEMA, ' CHARACTER_SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE 'ghost_%' ;
--
-- Having hit the databases now hit the tables...
---  NOTE: This will hit the already ok tables, so if you want to avoid
---        then add an extra AND into the WHERE clause
---
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.', TABLE_NAME, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA LIKE 'ghost_%' ;

Once you have fixed up you tables remember to re-enable your "Foriegn Key Constraints" ..

More missing table failures on upgrade to 4.48

Missing tables is now another regular problem with upgrades of Ghost, most recently the missing table included:

  • benefits - missing
  • products_benefits - fails due to benefits not being there (it has foriegn key contraint)
  • oauth - fails again due to foreign key constraint
  • others ... the list keep growng, something is wrong here!

Here is the SQL to create the missing tables:

---
--- benefits from: core/server/data/migrations/versions/4.9/02-add-benefits-table.js
---
create table benefits ( id varchar(24) not null primary key, name varchar(191) not null, slug varchar(191) not null unique, created_at datetime not null, updated_at datetime) ;
---
--- products_benefits from: core/server/data/migrations/versions/4.9/03-add-products-benefits-table.js 
---
create table products_benefits ( id VARCHAR(24) NOT NULL PRIMARY KEY, product_id VARCHAR(24) NOT NULL, benefit_id VARCHAR(24) NOT NULL, FOREIGN KEY (benefit_id) REFERENCES benefits (id) ON DELETE CASCADE, sort_order int unsigned NOT NULL default 0 );
---
--- oauth from: core/server/data/migrations/versions/4.11/01-add-oauth-user-data.js
---
create table oauth ( id varchar(24) not null primary key, provider varchar(50) not null, provider_id varchar(191) not null, access_token text, refresh_token text, created_at datetime not null, updated_at datetime, user_id varchar(24) not null, foreign key(user_id) references users(id) );
---
--- members_product_events from: core/server/data/migrations/versions/4.13/02-add-members-products-events-table.js
---
create table members_product_events ( id varchar(24) not null primary key, member_id varchar(24) not null, product_id varchar(24) not null, action varchar(50), created_at datetime not null) ;
---
--- offers from: core/server/data/migrations/versions/4.17/02-add-offers-table.js
---
create table offers ( id varchar(24) not null primary key, name varchar(191) not null unique, code varchar(191) not null unique, product_id varchar(24) not null, foreign key (product_id) references products(id), stripe_coupon_id varchar(255) not null unique, `interval` varchar(50) not null, curency varchar(50), discount_type, varchar(50) not null, discount_amount int, duration_in_months int, portal_title varchar(2000), created_at datetime not null, updated_at datetime) ;
---
--- offer_redemptions from: core/server/data/migrations/versions/4.19/02-add-offer-redemptions-table.js
---
create table offer_redemptions ( id varchar(24) not null primary key, offer_id varchar(24) not null, member_id varchar(24) not null, foreign key(member_id) references members(id) on delete cascade, subscription_id varchar(24) not null ) ;

NOTE: Even if you create the missing tables the ghost "migrations" will drop the tables and re-create them. In creating them it seems to re-create the "offers" table with "utf8mb3" character set, which results incompatible forieign keys. Why Ghost is using utf8mb3 rather than utf8mb4 is likely due to nodejs SQL library...

So fixing the root cause of problem, need to look at the Ghost and nodejs code for managing database.This is covered in "Ghost & Nodejs Database Connection and Character Sets"

Ghost & Nodejs Database Connection and Character Sets

Ghost continues to fail the upgrade process due to Character Set and Collation issues with MySQL database.

Ghost does not use the "standard" MySQL client library to connect to the MySQL server, rather it uses the native Nodejs mysql library. The result is that you cannot configure the MySQL client side behaviour via /etc/mysql/conf.g/mysql.cnf .

I use a remote database so the sql topology is: <GHOST> <-> <NODEJS MYSQL CLIENT API> <—network--> <MYSQL SERVER>

To check the MySQL configuration, connect remotely via mysql client and do a check on character sets:

---
--- Connect to SQL Server and check character set detals
---
$ mysql -u ghost -p -h <DB-HOST>
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 482
Server version: 8.0.30-0ubuntu0.20.04.2 (Ubuntu)

Copyright (c) 2000, 2022, 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> use ghost_story ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show session variables like 'character_set_%' ;
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8mb3                    |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)

mysql> show session variables like 'collation_connection' ;
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
---
--- So it is using expected default: utf8mb4 / utf8mb4_0900_ai_ci
---
--- Creating a new table results in utf8mb4 / utf8mb4_tf8mb4_0900_ai_ci table
--- So this means that ghost/nodejs must be changing the character set / collation

Based on checking the database default behaviour it seems that ghost/nodejs mysql library is changing the character set. So to find out where in the code this is happening:

  • cd to ghost directory; "cd /var/www/ghost"
  • Do a find for "utf8mb3": "find . name '*.js' -exec grep -l -e utf8mb3 {} \;" - which was not found.  So
  • Do a find for "utf8mb4" - this finds file: "node_modules/mysql2/lib/constants/encoding_charset.js"

Taking the results of above as starting point I found:

  • Ghost configuration file ("/var/www/ghost/config.production.json") includes a "charset" option, which historically was set to "utf8". This results in Ghost not negotiating the right character set / collalation with the MySQL server, which is why I was getting utf8mb3 "offers" table being generated.
  • NodeJS has two distinct mysql libraries, the original mysql and mysql2. Looking at these it appears that support for "utf8mb4_0900_ai_ci" collation is only available in mysql2.

Resolution to hopefully avoid any future database character set failures are to either ensure that charset is set to "utf8mb4" or left out and mysql2 client is used:

{
  "url": "http://your.ghost.server.com",
  "server": {
    "port": 2368,
    "host": "XXX.XXX.XXX.XXX"
  },
  "database": {
    "client": "mysql",
    "connection": {
      "host": "your.mysql.server.com",
      "user": "your-db-userid",
      "password": "password",
      "database": "ghost_dbname",
      "charset": "utf8mb4"
    }
  },
  ...
  ...
  ...
}

Since changing this simple configuration, I have been able to update my various Ghost instances without all the missing tables and other issues.

Turning off the "GREAT BIG SUBSCRIBE" buttons

When I started to use Ghost one of the things I like was it streamlined appearence. It had small non distracting menus and buttons and then its Members features arrived, bringing with it HORRIBLE HUGE BUTTONS TO "SUBSCRIBE".

Even worse is that turnings these off is much harder than it should be. I guess this means that Ghost now has enough paying customers that it does not want to provide a product to published that do "free" ...

Here are the various settings that need to be used to turn off the "GREAT BIG SUBSCRIBE" buttons.

NOTE: This appears to be fixed with Ghost 5.X

Ghost 3.x -> 4.x update failure - missing columns and tables

In April 2021 I decided to try to do update on one of my 3.x Ghost instances to 4.x. This did not go well... I followed the requested steps of:

  1. Updated Ghost CLI: "sudo npm install -g ghost-cli@latest" - this would not "stick" unless I added the -g flag  "sudo npm install -g ghost-cli@latest -g"
  2. Updated 3.x to latest verson: "ghost update v3"
  3. When going further update step to 4.X result was failure to migrate the database.

Digging into this the issues I had were:

  1. Character set collation problems (as per section below "Preparation for MySQL 8 Collation Change")
  2. Missing column in table - "members_stripe_customers_subscriptions" which resulted in simillar problem to that described in section "Ghost Update: Missing Columns in members Table" below.
  3. Completely missing table: "members_products", which required looking at the github code to find the table definition and create this manually.

Here is the SQL comands log for fixing these problems 1, 2 & 3:

sudo mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 363
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

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> use ghost_story ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA LIKE 'ghost_XXXX' ;
+---------------+--------------+----------------------------------------+----------------------------+--------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                             | COLUMN_NAME                | COLLATION_NAME     |
+---------------+--------------+----------------------------------------+----------------------------+--------------------+
| def           | ghost_XXXXX  | actions                                | actor_id                   | utf8mb4_general_ci |
| def           | ghost_XXXXX  | actions                                | actor_type                 | utf8mb4_general_ci |
...
...
...
| def           | ghost_XXXXX  | webhooks                               | updated_at                 | NULL               |
| def           | ghost_XXXXX  | webhooks                               | updated_by                 | utf8mb4_general_ci |
+---------------+--------------+----------------------------------------+----------------------------+--------------------+
345 rows in set (0.01 sec)

---
--- Now find the tables which have wrong collation, as per section: "Preparation for MySQL 8 Collation Change"
--- And fix as detailed below: Preparation for MySQL 8 Collation Change
---

--- Next is to add column and missing table:
---
mysql> alter table members_stripe_customers_subscriptions add column stripe_price_id varchar(255) not null default '' unique key ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table members_stripe_customers_subscriptions add key (stripe_price_id) ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

---
--- Add missing table..
---
mysql> create table members_products (id varchar(24) not null primary key, member_id varchar(24) not null references members(id) on delete cascade, product_id varchar(24) not null references products(id) on delete cascade, sort_order integer unsigned not null default 0) ;
Query OK, 0 rows affected (0.03 sec)

These updates resulted in working 4.X Ghost Blog now working again.

Ghost Update: Missing Columns in members Table

In mid December 2020 doing update to 3.40.1 I found that in addition to the utf8mb4 Collation bug, I also hit bug with the member table not having all colums defined. Error on starting Ghost was:

...
...
...
2020-12-16 03:33:17] INFO Populating email counts on members
[2020-12-16 03:33:17] WARN Removing members.email_opened_count column
[2020-12-16 03:33:17] INFO Removing members.email_count column
[2020-12-16 03:33:17] INFO Removing members.email_open_rate column
[2020-12-16 03:33:18] ERROR update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'

update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'

{"config":{"transaction":true},"name":"03-populate-members-email-counts.js"}
"Error occurred while executing the following migration: 03-populate-members-email-counts.js"

Error ID:
    300

Error Code: 
    ER_BAD_FIELD_ERROR

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

MigrationScriptError: update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'
    at MigrationScriptError.KnexMigrateError (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/lib/errors.js:7:26)
    at new MigrationScriptError (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/lib/errors.js:25:26)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/lib/index.js:1055:19
    at processTicksAndRejections (internal/process/task_queues.js:97:5)

Error: ER_BAD_FIELD_ERROR: Unknown column 'email_opened_count' in 'field list'
    at Query.Sequence._packetToError (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:314:20)
    at Socket.EventEmitter.emit (domain.js:483:12)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:272:9)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    --------------------
    at Protocol._enqueue (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/var/www/ghost/versions/3.40.1/node_modules/mysql/lib/Connection.js:198:25)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:135:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:129:12)
    at Client_MySQL.query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/client.js:169:17)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/transaction.js:321:24
    at new Promise (<anonymous>)
    at Client_MySQL.trxClient.query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/transaction.js:316:12)
    at Runner.query (/var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/runner.js:40:23
    at /var/www/ghost/versions/3.40.1/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:24
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
    at async up (/var/www/ghost/versions/3.40.1/core/server/data/migrations/versions/3.40/03-populate-members-email-counts.js:7:9)
    at async Object.up (/var/www/ghost/versions/3.40.1/core/server/data/migrations/utils.js:236:13)

Looking at the member table I found that for some reason not all the columns had been created:

mysql> show columns in members;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| id          | varchar(24)   | NO   | PRI | NULL    |       |
| email       | varchar(191)  | NO   | UNI | NULL    |       |
| created_at  | datetime      | NO   |     | NULL    |       |
| created_by  | varchar(24)   | NO   |     | NULL    |       |
| updated_at  | datetime      | YES  |     | NULL    |       |
| updated_by  | varchar(24)   | YES  |     | NULL    |       |
| name        | varchar(191)  | YES  |     | NULL    |       |
| note        | varchar(2000) | YES  |     | NULL    |       |
| subscribed  | tinyint(1)    | YES  |     | 1       |       |
| uuid        | varchar(36)   | YES  | UNI | NULL    |       |
| geolocation | varchar(2000) | YES  |     | NULL    |       |
+-------------+---------------+------+-----+---------+-------+
11 rows in set (0.00 sec)   <<==== 11 table has columns defined mysql> show columns in members ;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| id                 | varchar(24)   | NO   | PRI | NULL    |       |
| email              | varchar(191)  | NO   | UNI | NULL    |       |
| created_at         | datetime      | NO   |     | NULL    |       |
| created_by         | varchar(24)   | NO   |     | NULL    |       |
| updated_at         | datetime      | YES  |     | NULL    |       |
| updated_by         | varchar(24)   | YES  |     | NULL    |       |
| name               | varchar(191)  | YES  |     | NULL    |       |
| note               | varchar(2000) | YES  |     | NULL    |       |
| subscribed         | tinyint(1)    | YES  |     | 1       |       |
| uuid               | varchar(36)   | YES  | UNI | NULL    |       |
| geolocation        | varchar(2000) | YES  |     | NULL    |       |
| email_open_rate    | int unsigned  | YES  | MUL | NULL    |       |
| email_count        | int unsigned  | NO   |     | 0       |       |
| email_opened_count | int unsigned  | NO   |     | 0       |       |
+--------------------+---------------+------+-----+---------+-------+
14 rows in set (0.01 sec)   <<==== 14 working ghost sample has columns < code>

Work around was to manually add the missing colums into the table and rerun ghost;

mysql> alter table members add column email_open_rate int unsigned;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table members add column email_count  int unsigned not null ;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table members add column email_opened_count  int unsigned not null
;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

NOTE: the working Ghost table definition also has index on the email_open_rate column, so this should also be added.

Preparation for MySQL 8 Collation Change

MySQL 8.0 changed to defalt 'utf8mb4' Character Set collation to 'utf8mb4_0900_ai_ci'. Currently Ghost uses 'utf8mb4' (it does not enforce the Collation) and tables that where created on Ubuntu prior to 8.x defaulted to   'utf8mb4_general_ci' collation.

The result is that when new tables get created they could be using incompatible collation sets, which result in SQL error (see below "ER_CANT_DROP_FIELD_OR_KEY" for example).

So to avoid surprises there are come things you can do to make sure your database does not get changed during either an OS , MySQL or Ghost Update.

  • Check the Collation for all table in ghost database - "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS ;" . If you have tables with "utf8mb4_0900_ai_ci" then you are likely to run into problems, so do an update on the problems tables Collation settings (see next).
  • Look for cases where have "utf8mb4_0900_ai_ci" collation - "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS where COLLATION_NAME like 'utf8mb4_0900_ai_ci';"
  • Update Collation for tables that have already have incorrect collation - "alter table <YOUR-TABLE> convert to character set utf8mb4 collate utf8mb4_general_ci ;"

To test I have done update on Ghost from 3.27.0 -> 3.40.1 and found that even if I do an update of the Ghost database to change the default collation, that the update still failed. In this case with a Migration lock failure:

[2020-12-16 00:26:25] ERROR Migration lock was never released or currently a migration is running.

Migration lock was never released or currently a migration is running.

"If you are sure no migration is running, check your data and if your database is in a broken state, you could run `knex-migrator rollback`."

Error ID:
    500

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

The offending table was again (see below): "email_recipients" and the fix was to:

  1. change the collation on this table
  2. manually update the migraton lock: "UPDATE migrations_lock set locked=0 where lock_key='km01';" and
  3. then restart ghost (I always run ghost in foreground when bugs appear): "cd /var/www/ghost" & "ghost run".

NOTE: I have logged bug on this as update fails consistently and for the same reason.

Ghost 3.40.1 Update Error: ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

December 2020, just before Christmas, I have not done a Ghost update for some time so thought I should check my ghost servers and did a ghost update from 3.27 -> 3.40.1.

This did not go well with ghost crashing on restart with an SQL error: ER_CANT_DROP_FIELD_OR_KEY.

To help diagnose the problem I had to do some checking on the ghost database and reading up on the use of Foreign Keys in SQL database, Database Character Set and Collaction orders: The character set and collation (sorting) order can be set on a Database, Table and Column level. To see what character set is being use at each of these levels do:

  • Database - "select * from information_schema.schemata where schema_name like '<YOUR-DB>' ;"
  • Database (to alter this) - "alter database <YOUR-DB> character set utf8mb4 collate utf8mb4_general_ci ;"
  • Tables - "show full columns from <YOUR-TABLE> ;"
  • Tables (to alter this) - "alter table <YOUR-TABLE> convert to character set utf8mb4 collate utf8mb4_general_ci ;"

The reason for the need to look at the Character Sets and Collating is because as part of doing update I also did an update on Ubuntu (via apt update / upgrade) and this appears to have changed the default character set and collating. The result is that new tables where being created with collating order of: 'utf8mb4_0900_ai_ci' and the old ones had a collating order of: 'utf8mb4_general_ci'.

This in-turn results in a failure to create a foreign key as part of the database update, which in turn result in failure in the subsequent drop of the foriegn key, which results in the error reporting:

...
...
...
2020-12-15 09:25:06] ERROR alter table `email_recipients` drop foreign key `email_recipients_email_id_foreign` - ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

alter table `email_recipients` drop foreign key `email_recipients_email_id_foreign` - ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

"OuterError: update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='"

Error ID:
    400

Error Code: 
    ER_CANT_DROP_FIELD_OR_KEY

----------------------------------------
...
...
...

So fix is to ensure you database and tables are all using the same Character Set amd Collation Order. For ghost this should be: utf8mb4 / utf8mb4_general_ci.

Ghost Error: Can't connect to the bootstrap socket (localhost 8000) ECONNREFUSED

Following upgrade of Ubuntu from 18.04 (LTS) -> 20.04 (LTS) which disables the nodejs repository, I did a new upate to nodejs 12.x (previous recommended version was 10.x:

$ curl -sL https://deb.nodesource.com/setup_12.x | sudo -E bash
sudo apt-get install -y nodejs
---
--- Followed by ghost-cli update and ghost update
$ sudo npm install ghost-cli@latest -g
$ cd /var/www/ghost
$ ghost update
$ ghost start

However ghost fails to start.. and I get the error:

Can't connect to the bootstrap socket (localhost 8000) ECONNREFUSED

You can see this by running: ghost run

Problems is that node executable changes from: "nodejs" to "node" so systemd script fails:

[Unit]
Description=Ghost systemd service for blog: localhost
Documentation=https://docs.ghost.org

[Service]
Type=simple
WorkingDirectory=/var/www/ghost
User=1001
Environment="NODE_ENV=production"
ExecStart=/usr/bin/nodejs /usr/bin/ghost run
Restart=always

[Install]
WantedBy=multi-user.target

Simply:

  • change "ExecStart" line to use /usr/bin/node,
  • do "sudo systemctl daemon reload"
  • ghost should now start correctly

Ghost V2 to V3 Database Migration Error

The Ghost team has released Ghost V3, so like other I have upgraded some of my ghost servers to V3.

In doing this I came across this error on doing inplace "ghost update" comment:

 $ ghost update
+ sudo systemctl is-active ghost_ghost-BLAH-com
✔ Checking system Node.js version
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking memory availability
✔ Checking for available migrations
✔ Checking for latest Ghost version
✔ Downloading and updating Ghost to v3.0.0


Checking theme compatibility for Ghost 3.0.0

✔ Your theme is compatible.

You can also check theme compatibility at https://gscan.ghost.org

? Are you sure you want to proceed with migrating to Ghost 3.0.0? Yes
✔ Updating to a major version
+ sudo systemctl is-active ghost_ghost-BLAH-com
+ sudo systemctl stop ghost_ghost-BLAH-com
✔ Stopping Ghost
✔ Linking latest Ghost and recording versions
+ sudo systemctl is-active ghost_ghost-BLAH-com
☱ Stopping Ghost✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
☲ Restarting Ghost✔ Checking current folder permissions
+ sudo systemctl is-active ghost_ghost-BLAH-com
✔ Validating config
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
+ sudo systemctl start ghost_ghost-BLAH-com
☲ Restarting Ghost+ sudo systemctl stop ghost_ghost-BLAH-com
✖ Restarting Ghost
A GhostError occurred.

Message: drop table if exists `members_stripe_customers_subscriptions` - ER_TABLEACCESS_DENIED_ERROR: DROP command denied to user 'GHOST_USER'@'GHOST_SERVER.com' for table 'members_stripe_customers_subscriptions'
Suggestion: journalctl -u ghost_ghost-BLAH-com -n 50

Debug Information:
    OS: Ubuntu, v19.04
    Node Version: v10.17.0
    Ghost-CLI Version: 1.12.0
    Environment: production
    Command: 'ghost update'

Additional log info available in: /home/ME/.ghost/logs/ghost-cli-debug-2019-10-26T03_10_33_463Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/api/ghost-cli/ for troubleshooting.



? Unable to upgrade Ghost from v2.31.1 to v3.0.0. Would you like to revert back to v2.31.1? Yes
+ sudo systemctl is-active ghost_ghost-BLAH-com
+ sudo systemctl reset-failed ghost_ghost-BLAH-com
✔ Checking system Node.js version
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking for available migrations
✔ Checking for latest Ghost version
ℹ Downloading and updating Ghost [skipped]
+ sudo /var/www/ghost/current/node_modules/.bin/knex-migrator-rollback --force --v 2.31.1 --mgpath /var/www/ghost/current
✖ Rolling back database migrations
A GhostError occurred.

Message: The database migration in Ghost encountered an error.
Help: https://ghost.org/faq/upgrade-to-ghost-2-0/#what-to-do-when-an-upgrade-fails
Suggestion: ghost update --rollback

Debug Information:
    OS: Ubuntu, v19.04
    Node Version: v10.17.0
    Ghost-CLI Version: 1.12.0
    Environment: production
    Command: 'ghost update'

Additional log info available in: /home/ME/.ghost/logs/ghost-cli-debug-2019-10-26T03_12_26_245Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/api/ghost-cli/ for troubleshooting.

As per prior posting I converted by sqlite3 ghost to MYSQL and in doing this set permission on database as: GRANT create, delete, insert, select, update, alter,references ON <your-db>.* TO GHOST_DB_USER@&lt;ip/subnet&gt;

Error is pretty clear and consistent with fact that I did not grant "drop" permissions, so fix is to add drop permissions to ghost db acount use:

mysql> show grants for 'USER'@'XXX.XXX.XXX.X/255.255.255.X' ;
mysql> GRANT DROP ON `GHOST_DB`.* TO 'USER'@'XXX.XXX.XXX.0/255.255.255.X' ;
-- or if you have local mysql
mysql> show grants for 'USER'@'localhost';
mysql> GRANT DROP ON `GHOST_DB`.* TO 'USER'@'localhost' ;
--- and flush
mysql> FLUSH PRIVILEGES;

Password Reset

This is likely the most common problem. You have forgotten ghost admin password and password reset mail is not avaiable or working, so you cannot use ghost tools to fix things. The reset requires you to do three things:

StepTaskNotes
1Get Database DetailsGet the database connection information from ghost by looking at config.production.json (or development) in Ghost root directory (typically /var/www/ghost). Config item is: "database".
2Get DB User DetailsLog into your MySQL host (may or may not be the same machine that ghost is on depending on your configuration).
sudo mysql -u root -p
mysql> use <db-name> ; <== (DB from step 1)
mysql> select * from users ;
3Reset PasswordGhost uses BCrypt Hash for password protection, so simply use known BCryptencrytped password "password" as reset password.
UPDATE users SET password='$2a$10$BQToDNdBtBKCvnrTmMi5m.NK.7i6Qx7YASs.jTkE86I5zqxzE8klC' WHERE email = '<email-as-per-step-2>' ;
4Reset via GhostNow just login to Ghost with use id as per Step 2/3 with password "password" (no quotes).
No use Ghost tools to reset the password.

These instruction also work with sqlite3 database, but rather than going into sql interpreter, you should change directory to <ghost-root>/content/data and then do:

sudo sqlite3 ghost.db

The is no need for the "use <DB>" step with sqlite3.

NOTE: If you have installed an MySQL DB Admin tools then you can do all this quickly and simplely, or just write a script...

Ghost Database Content Migration

Migrating from sqlite (development) to MySQL (production configuration) is documented here.

Reference & Links:

Acknowledgement: Ghost Writer Image from: Sue Tooth - Writing & Editing Services.

MySQL & Unicode - briefing on Unicode Charater Sets & Collation

MySQL / Ghost Unicode Collation Bug - the change in MySQL default character set collation results in failure during migration at restart.

The big long Ghost Database is "not my problem" forum thread, this is what I  would call shoddy ...

MySQL documentation - explaining the way character set / collation are negotiated between client/server

NodeJS - mysql library charset is missing utf8mb4_0900_ai_ci collation support, but mysql2 library has this and explicity flags UTF8_GENERAL50_CI as deprecated.