Updating MySQL DB from 5 to 8-- which alter table commands to run?

Developer help Self-hosting
1

Hi there,

I’m working through the docs on updating from MySQL 5 to 8.

I used ghost_production as my database value for the command in the section titled “How to Update from MySQL 5 to 8.” In return, I got a largeeee number of ALTER TABLE commands to run. Like 2 pages worth of Google Docs.

They look like this:
ALTER TABLE ghost_production.webhooks CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; ALTER TABLE ghost_production.actions CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Do I really just copy-pasta over and over these commands like so?
mysql <database> -u <username> -p 'set foreign_key_checks=0; ALTER TABLE ghost_production.webhooks CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci set foreign_key_checks=1;'

Am I on the right track here?

2

I think so. Test on a scratch DB first if you’d like.

3

Before you mess with the DB, I’d (1) take a server snapshot if that’s a thing your hosting setup allows, and (2) export everything from within Ghost (content and members, both - separate export steps!)

I recall doing a huge pile of alter table commands, but ended up with Ghost unwilling to start after the MySQL upgrade. So I made a new database, installed Ghost in a new directory, did a content import, a members import, and moved the images folder. Bingo, old site on new version of the database. I’m not saying that you /should/ do it that way, but if you export before you start, you’ll have the option if you need it.

4

Ugh thanks all! going through it now.

I guess this is why people pay for managed hosting lol. Went to my old, infrequently updated blog, saw that I had a security warning, started a migration, and oof!

1 Like
5

Yeah, I hear ya. I’ve got the ‘best’ of both worlds. I get to support the awesome Ghost team by hosting my client demo / testing install on Ghost Pro, AND I get to wrangle self-hosting updates and upgrades on my production site. [Dumb-sounding, but true!]

6

o nice!

so i did this for ghost_production table, ghost starts, but i get a 502 error on my website. ghost doctor didn’t reveal anything and ghost status says it’s running.

may have to just export my posts from the database and start anew
:smiling_face_with_tear:

7

That’s a bad gateway error. Is Ghost listening on the expected IP and port?

8

Looks like gateway errors are not uncommon after updating ghost from a quick search.

And I was updating from Ghost v4 to latest.

Let me try some basic steps and report back! Thanks!!

9

Sometimes it decides to start on 2369 instead of -8. netstat -antp | grep ghost

10

interesting.

ghost status shows that it’s on port 2368.

sudo netstat -antp | sudo grep ghost returned nothing.

vanilla netstat -antp | sudo grep ghost returned (No info could be read for "-p": geteuid()=1000 but you should be root.) so I threw some sudo on it.

ghost log returnned:

[2024-02-09 19:47:55] INFO Worker for job "mentions-email-report" online
[2024-02-09 19:47:55] INFO Worker for job mentions-email-report sent a message: done
[2024-02-09 19:48:34] INFO "GET /" 301 2ms
[2024-02-09 19:48:51] INFO "GET /" 301 2ms
[2024-02-09 19:51:40] INFO "GET /" 200 502ms
[2024-02-09 19:55:28] INFO "POST /vpnsvc/connect.cgi" 404 79ms
[2024-02-09 20:04:39] INFO "GET /.env" 301 1ms
[2024-02-09 20:04:39] INFO "POST /" 301 2ms
[2024-02-09 20:08:11] INFO "GET /" 301 2ms

so I think it’s running? something going on with my nginx setup maybe?

11

in conclusion:

  1. yes, you gotta run a ton of alter table commands. i did this by copying them from the terminal, dumping them in a google doc, and then tracking which ones i ran as i went through. probably took me like 15-20 minutes of manually copying & pasting. it probably can be scripted by someone smarter than me but Gemini couldn’t manage advising me on how.

  2. then i had this 502 error. it turns out that my .conf file mismatched the port that ghost was listening on. must have happened as part of migration:

to fix
a) run ghost status and note which port is being reported. for me it was 2368.
b) cd /etc/nginx/sites-enabled
c) ls
d) sudo nano the conf files you see.
e) proxy_pass http://127.0.0.1:2368; see that 2368? mine originally was 2369. idk why. but change it to 2368 and save the file. repeat for the other .conf file.

1 Like