Ghost migration error from 3.x to 5.x

Bugs
1

Issue Summary

  • I’m upgrading a ghost instance from 3.x to the latest. From the guide, it looks like the best way is to backup the content and reinstall ghost, but then ran into this during the last step of the installation. And now I can consistently reproduce this by running ghost start

Steps to Reproduce

ghost start

Setup information

Ghost Version
5.22.4

Node.js Version
16.18.0

How did you install Ghost?
Had a version of Ghost 3.x, and did the reinstallation of Ghost 5.x.

Provide details of your host & operating system
Ubuntu v18.04.6 LTS on DigitalOcean

Database type
*MySQL 5.7

Browser & OS version
N/A

Relevant log / error output

Debug Information:
    OS: Ubuntu, v18.04.6 LTS
    Node Version: v16.18.0
    Ghost Version: 5.22.4
    Ghost-CLI Version: 1.23.1
    Environment: production
    Command: 'ghost start'
Message: Ghost was able to start, but errored during boot with: Unable to run migrations
Help: Run 'ghost update v3' to get the latest v3.x version, then run 'ghost update' to get to the latest.
Suggestion: journalctl -u ghost_aaronhe-org -n 50
Stack: Error: Ghost was able to start, but errored during boot with: Unable to run migrations
    at Server.<anonymous> (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:73:28)
    at Object.onceWrapper (node:events:627:28)
    at Server.emit (node:events:513:28)
    at emitCloseNT (node:net:1857:8)
    at processTicksAndRejections (node:internal/process/task_queues:82:21)
2

reinstalled ghost again with a different mysql db name ghost_prod2 and restored a backup and the site is running okay. but when I try to click on Posts/Drafts/Scheduled or Published, it always give me 404. And when I try to write a new post, there’s an internal server error:

Internal server error, cannot save post. 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`.`id` = '6362078cfc88c120b3e8dba7' limit 1 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`k` as (select `member_id` from `members_subscription_created_events` where post' at line 1

3

I also tried ghost update --rollback but it tells me Message: 'No previous version found'

4

You need to be running MySQL version 8 with Ghost V5.

The steps to follow are:

  1. Backup the original database, or export your content, and copy the contents folder, theme, routes, and redirects.
  2. Install the latest versions of Ghost, MySQL, Node etc.
  3. Create a new Ghost site, and import the data.

Before doing this it’s a good idea to take a snapshot of the Droplet. Alternatively, create a new Droplet, setup Ghost, and migrate your data.

1 Like
5

Thanks! Upgraded to MySQL 8 solved this.

1 Like
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.