Upgrading v4.10.2 to v4.11.0 error

Developer help
1

I’m experiencing an error when trying to upgrade a self-hosted Ghost instance from v4.10.2 to v4.11.0.

My blog is hosted here

I initially installed the Ghost instance a few years ago following these instructions

Node Version: v12.22.5
OS: Ubuntu, v20.04.2 LTS
Ghost-CLI Version: 1.17.3
Environment: production
DB: mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

Error message:

A GhostError occurred.
Message: Ghost was able to start, but errored during boot with: alter table `oauth` add constraint `oauth_user_id_foreign` foreign key (`user_id`) references `users` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'oauth_user_id_foreign' are incompatible.
Help: Error occurred while executing the following migration: 01-add-oauth-user-data.js
Suggestion: journalctl -u ghost_davidmichaelbarley-com -n 50

Anybody have suggestions?

2

Unfortunately I’m in this exact same situation. Upgrading from 4.10.2 to 4.11.0, and I’ve encountered the same error.

3

I hooked up to the MySQL DB my Ghost instance references

DESCRIBE users, for getting table schema information, returned:

+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| id               | varchar(24)   | NO   | PRI | NULL    |       |
| name             | varchar(191)  | NO   |     | NULL    |       |
| slug             | varchar(191)  | NO   | UNI | NULL    |       |
| password         | varchar(60)   | NO   |     | NULL    |       |
| email            | varchar(191)  | NO   | UNI | NULL    |       |
| profile_image    | varchar(2000) | YES  |     | NULL    |       |
| cover_image      | varchar(2000) | YES  |     | NULL    |       |
| bio              | text          | YES  |     | NULL    |       |
| website          | varchar(2000) | YES  |     | NULL    |       |
| location         | text          | YES  |     | NULL    |       |
| facebook         | varchar(2000) | YES  |     | NULL    |       |
| twitter          | varchar(2000) | YES  |     | NULL    |       |
| accessibility    | text          | YES  |     | NULL    |       |
| status           | varchar(50)   | NO   |     | active  |       |
| locale           | varchar(6)    | YES  |     | NULL    |       |
| visibility       | varchar(50)   | NO   |     | public  |       |
| meta_title       | varchar(2000) | YES  |     | NULL    |       |
| meta_description | varchar(2000) | YES  |     | NULL    |       |
| tour             | text          | YES  |     | NULL    |       |
| last_seen        | datetime      | YES  |     | NULL    |       |
| created_at       | datetime      | NO   |     | NULL    |       |
| created_by       | varchar(24)   | NO   |     | NULL    |       |
| updated_at       | datetime      | YES  |     | NULL    |       |
| updated_by       | varchar(24)   | YES  |     | NULL    |       |
+------------------+---------------+------+-----+---------+-------+

The error is this migration trying to add a foreign key from a new table, oauth, to users.id. Initial glance the code looks fine. It’s a string, with a max length of 24, not nullable. Which matches what the DESCRIBE command returned.

So, still hunting :)

4

Interesting that the oauth table doesn’t exist in the MySQL instance…

I’m not familiar with Node.js programming in general, and especially not whatever framework is handling the migrations to know if that’s a problem. But it’s interesting, at the very least.

5

I did SELCT * FROM migrations and the last executed migration in the list is from 4.7. Therefore the migrations from 4.8 and 4.9 haven’t run. That’s interesting!

However a ghost migrate says “No migrations needed :)”

6

Dunno if this is useful or not, but here’s some interesting things maybe

I did ghost run. Here are the relevant snippets from the log:

...
[2021-08-22 04:18:19] INFO Ghost server started in 0.934s
[2021-08-22 04:18:19] WARN Database state requires migration.
[2021-08-22 04:18:20] INFO Creating database backup
[2021-08-22 04:18:20] INFO Database backup written to: [...]
...
[2021-08-22 04:18:20] INFO Adding table: oauth
[2021-08-22 04:18:20] INFO Dropping table: oauth
...
[2021-08-22 04:18:21] ERROR alter table `oauth` add constraint `oauth_user_id_foreign` foreign key (`user_id`) references `users` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'user_id' and referenced column 'id' in foreign key constraint 'oauth_user_id_foreign' are incompatible.
...
1 Like
7

Okay I got my blog up and running again. Here’s what I did. I accept zero responsibility for anyone else who performs these same steps and things blow up.

This StackOverflow article was very helpful in troubleshooting. Turns out the collation was different between users and oauth, probably because I upgraded from MySQL 5 to 8 at some point in the lifetime of this blog. So I converted every table to the new default collation. It was a pain. I had to manually remove foreign keys, change collation, and manually re-add the foreign keys. Take a backup first, be careful, and go slow.

I emptied <ghost-home>/content/data because it had filled up the disk with DB backups.

A ghost start successfully ran the migrations and the blog started!

For some reason the port ghost binds to had changed, not sure why. So I updated my Nginx config to point to the new port.

1 Like
8

I’m in your same situation but very poor knowledge of mysql, can you be more specific about the steps you did to solve it?
I mean, I saw the stackOverflow post but it’s very specific, can you just recap a little bit more how did you edited the tables to the new default collation? Thanks!!!

9

So, I never managed to fix this using the method @ghuitster outlined; instead I had to completely blast out my ghost setup. This was fortunately easier than I thought it would be. First I made a backup of my ghost setup, used the site export feature to save what it could to a json, and then purged ghost, nginx and mysql. I then reinstalled all three. Once a basic ghost install was running again, I put the relevant content back (images, etc.) and imported the site json. That got me a fully functional install, that was back to where I was. Once I figured out what to do, it took less than an hour.

I have some additional stuff running on my server that also relied on nginx, so I took care to back those up and reinstate them separately. Most people wouldn’t need to worry about that though. The reason for purging nginx instead of just deleting the site files generated by ghost, is that for some reason, ghost refused to install with nginx setup the way it was. Purging it and reinstalling was all I could do to make it work.

1 Like