Upgrade ghost

Claire Furney

• 3 min read
Upgrade ghost

Check update

  1. cd /var/www/notes.dot.kitchen
  2. ghost -v to show current ghost-cli version and ghost version
  3. ghost check-update shows new version available - minor version upgrade from 5.33.8 to 5.97.3.
  4. ghost backup yields "Unrecognized command: 'backup'. Run ghost help for usage."
  5. Google the error, need to upgrade the cli, peruse previous notes

Upgrade ghost CLI

  1. Login as ghost user su - ghostuser
  2. sudo npm i -g ghost-cli@latest
  3. cli now upgraded from 1.18.1 to 1.24.0
  4. ghost backup now works ok
  5. ghost upgrade gives error that node 16 not supported, requires either 18 or 20

Upgrade node

  1. nvm install 18
  2. ghost upgrade now works, but shows message that ghost is running with node 16 and the current node version is 18 before restarting the service
  3. ghost doctor again reports the node version mismatch and suggests updating /lib/systemd/system/ghost_notes-dot-kitchen.service
  4. Update version in file mentioned
  5. ghost restart
  6. ghost doctor now reports a clean bill of health, but ghost restart still mentions a mismatch. Google mentions to uninstall other versions.
  7. nvm uninstall 16
  8. ghost restart hangs
  9. sudo systemctl daemon-reload as per https://hooshmand.net/ghost-cli-crash/
  10. ghost restart now works
  11. ghost doctor finally clean bill of health again
  12. BUT the little graph on the side is giving a 400 error

Fix 400 error

It never rains but it pours when trying to upgrade ghost. I should have known better...

Apparently they no longer support mariadb, which is fine, but couldn't they not have broken it between major upgrades - seems more obvious since the team was unofficially keeping it working.

Will have to debug this one myself by the looks of it - its only happening in admin and only for certain actions which I can work around at least.

Found the logs in the content/logs directory and extracted this error:

Error: select `posts`.*, (with `k` as (select `member_id` from `members_subscription_created_events` where posts.id = members_subscription_created_events.attribution_id union select `member_id` from `members_created_events` where posts.id = members_created_events.attribution_id) select count(*) from `k`) as `count__conversions`, `posts`.*, (select count(distinct `members_click_events`.`member_id`) from `members_click_events` inner join `redirects` on `members_click_events`.`redirect_id` = `redirects`.`id` where posts.id = redirects.post_id) as `count__clicks`, `posts`.*, (select COALESCE(ROUND(AVG(score) * 100), 0) from `members_feedback` where posts.id = members_feedback.post_id) as `count__sentiment`, `posts`.*, (select count(*) from `members_feedback` where posts.id = members_feedback.post_id AND members_feedback.score = 0) as `count__negative_feedback`, `posts`.*, (select sum(`score`) from `members_feedback` where posts.id = members_feedback.post_id) as `count__positive_feedback` from `posts` where (`posts`.`type` = 'post' and `posts`.`status` in ('draft', 'published', 'scheduled', 'sent')) and `posts`.`id` = '6719638ef47b620389090f7c' limit 1 - Unknown column 'posts.id' in 'where clause'\n    at Child.

Found this post: https://github.com/TryGhost/Ghost/issues/15729 where someone had managed to pinpoint their problem to the same SQL query and then highlighted which part of the code they thought would be responsible.

Edited said file /core/server/models/post.js where it was obviously constructing this database query and replace with any old thing since I don't use all the analytics bits and pieces:

/**
 * Combination of sigups and paid conversions, but unique per member
 */
conversions(modelOrCollection) {
  modelOrCollection.query('columns', 'posts.*', (qb) => {
    /* qb.count('*')
      .from('k')
      .with('k', (q) => {
          q.select('member_id')
              .from('members_subscription_created_events')
              .whereRaw('posts.id = members_subscription_created_events.attribution_id')
              .union(function () {
                  this.select('member_id')
                      .from('members_created_events')
                      .whereRaw('posts.id = members_created_events.attribution_id');
              });
      }) */
    // 2024-10-24 modding to try to get rid of 400 error
    // see: https://github.com/TryGhost/Ghost/issues/15729
    qb.count('member_id')
      .from('members_subscription_created_events')
      .as('count__conversions');
  });

Restarted ghost and VOILA!!

That'll learn me ...