Update from 5.20.0 to 5.21.0 breaks my post list admin side

[nicgentile]

I just upgraded to 5.21.0 and everything went just fine. When I went to my posts list, I got this error. There is a 400 error where there should be a list of my posts. The problem does not seem to affect pages. Also note, it has been about two weeks since I last posted on the blog, I was working on new material to post today.

I have run Ghost Doctor and it comes up clean.

When I run Ghost Log, this was the error I got the following.

Relevant log / error output

[2022-10-29 05:58:58] INFO "GET /ghost/api/content/newsletters/?key=5c5caaf796d3a30af6eca401ea&limit=all" 304 7ms
[2022-10-29 05:58:59] INFO "GET /ghost/api/admin/tiers/?filter=type%3Apaid&limit=all" 200 26ms
[2022-10-29 05:58:59] INFO "GET /ghost/api/admin/stats/mrr/" 200 43ms
[2022-10-29 05:58:59] INFO "GET /ghost/api/admin/stats/member_count/" 200 48ms
[2022-10-29 05:58:59] INFO "GET /ghost/api/admin/stats/subscriptions/" 200 48ms
[2022-10-29 05:58:59] INFO "GET /ghost/api/admin/stats/referrers/" 200 17ms
[2022-10-29 05:58:59] INFO "GET /ghost/api/admin/members/?filter=last_seen_at%3A%3E2022-09-28T21%3A00%3A00.000Z&limit=1&page=1&include=newsletters%2Clabels" 200 58ms
[2022-10-29 05:59:00] INFO "GET /ghost/api/admin/members/?filter=last_seen_at%3A%3E2022-10-21T21%3A00%3A00.000Z&limit=1&page=1&include=newsletters%2Clabels" 200 22ms
[2022-10-29 05:59:00] INFO "GET /ghost/api/admin/members/?filter=newsletters.status%3Aactive%2Bstatus%3A-free&limit=1&page=1&include=newsletters%2Clabels" 200 34ms
[2022-10-29 05:59:00] INFO "GET /ghost/api/admin/members/?filter=newsletters.status%3Aactive%2Bstatus%3Afree&limit=1&page=1&include=newsletters%2Clabels" 200 43ms
[2022-10-29 05:59:00] ERROR "GET /ghost/api/admin/posts/?formats=mobiledoc%2Clexical&limit=5&filter=status%3A%5Bpublished%2Csent%5D&order=published_at%20desc" 400 29ms

NAME: BadRequestError
CODE: ER_BAD_FIELD_ERROR
MESSAGE: Could not understand request.

level: normal

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`.`status` in ('published', 'sent') and `posts`.`type` = 'post') order by `posts`.`published_at` DESC limit 5 - Unknown column 'posts.id' in 'where clause'
    at /var/www/MY.DOMAIN:NAME/versions/5.21.0/node_modules/@tryghost/bookshelf-pagination/lib/bookshelf-pagination.js:259:27
    at Packet.asError (/var/www/MY.DOMAIN:NAME/versions/5.21.0/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/var/www/MY.DOMAIN:NAME/versions/5.21.0/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/var/www/MY.DOMAIN:NAME/versions/5.21.0/node_modules/mysql2/lib/connection.js:456:32)
    at PacketParser.onPacket (/var/www/MY.DOMAIN:NAME/versions/5.21.0/node_modules/mysql2/lib/connection.js:85:12)
    at PacketParser.executeStart (/var/www/MY.DOMAIN:NAME/versions/5.21.0/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/var/www/MY.DOMAIN:NAME/versions/5.21.0/node_modules/mysql2/lib/connection.js:92:25)

Setup information

Debug Info

OS: FreeBSD, v13.0
Node Version: v14.20.0
Ghost Version: 5.21.0
Ghost-CLI Version: 1.23.1
Environment: production

Database type

DB: mysql Ver 15.1 Distrib 10.5.17-MariaDB, for FreeBSD13.1 (amd64) using EditLine wrapper

Any assistance would be appreciated.

Thanks.

####################### This is my fix. I am not responsible for your stuff, so make sure you have backups everywhere.

So, if you run Freebsd, and run into this problem after installing using this guide do the following.

1. Run ghost backup.
2. Mysqldump your databases.
3. Uninstall Mysql (whatever version you are running).
4. rm -rf /var/db/mysql & rm -rf /usr/local/etc/mysql (Make sure you have backups of this in case things don’t work out).
5. pkg install mysql80-server-8.0.30 mysql80-client-8.0.30
6. Run mysql_secure_installation and make sure you do not enable password validation.
7. Get into MySQL.
8. This step is unnecessary, but I run it because I run other stuff that relies on it. This has to do with MySQL native authentication. If you run it makes sure you replace with your password.

select mysql;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

ALTER USER 'root'@'localhost' IDENTIFIED BY '<password>';

10. Build your databases back up using your dump files.

11. Run ghost start.

Cheers.

[mjw]

I’m not so sure this is a bug since you’re running Ghost with an unsupported database. You need to be running MySQL V8.

[ghosthonk]

having the same issue on debian 10 (buster) with mariaDB 10.9

Downgrading to 5.20.0 looks possible. At least post are visible again in admin if.

ghost update 5.20.0 --force

[mjw]

Breaking changes are likely every time you update if you run an unsupported database. This was made clear with the release of Ghost V5.

[nicgentile]

Did not know this. Have spent about three hours breaking stuff and then fixing it.

Thanks for the direction. I solved it.

[nicgentile]

Followed advice and moved to MySQL v8 and it solved my issues. You will likely need to do the same.

[StarManTheGamer]

Any other advice on how to fix this? I’d rather not switch from MariaDB as other processes I need require it, and I can’t run both MySQL and MariaDB.

Same issue, 400 on admin page, with the same error logs:

“NAME: BadRequestError
CODE: ER_BAD_FIELD_ERROR
MESSAGE: Could not understand request.”

[nicgentile]

As far as I can tell, you need to run MySQL v8. There are no other options.

[ghosthonk]

for the future there might be no other option if you want to continue with ghost.
You can (downgrade and) stay on 5.20.0 but this will leave you vulnerable to future security issues.

My mariadb is also used for multiple programs on my server. Docker might be an option. But my VPS is not powerful enough to host many docker containers. Most features of ghost I’m not using. It is just a blog with no user interaction for me. Therefore mostly static content for the reader.
On the other hand I’m a little bit terrified because in germany people get admonished because they crossload dependencies like fonts, css and js from CDNs and google.
I don’t want this crossload in the first place. But it is hard to turn this off in a CMS like ghost.

So far I liked ghost very much. It is easy to host and the editor was great. Especially for a free version. For me 5.20.0 will be the last version.

[jeff]

It is interesting that Ghost uses something in MySQL8 (not sure what) that is incompatible with MariaDB - esp. as MariaDB is generally more SQL standards compliant. Whatever that feature is must be important.

FOSS are mostly following the original writers of MySQL who are now MariaDB, for good reasons - both technical and philosophical. I found MariaDB to be fantastic with Galera multi-master replication / failover. I hate how MySQL has borked MyISAM performance.

So far, (touch !) having downgraded from MariaDB to MySQL8, I haven’t had any unsolvable issues with other apps - but I’m guessing that I will sadly have to switch away from Ghost over the next two or three years. Losing multi-master replication and scalable MyISAM performance was a PITA, and running MySQL alongside MariaDB is a cache fight - Meow.

Have to say that I will miss the beauty of Ghost when we have to move - and I know our clients will too.

[mjw]

MariaDB was a drop-in replacement for MySQL up to 5.5, but that’s no longer the situation, and MariaDB continues to implement differences.

Also, MySQL Community Edition is also FOSS, and available under GPL.

Couldn’t you run MySQL and /or Ghost on a different server?

[jeff]

The main difference with MariaDB and MySQL comes from the intention of the owners.

MariaDB are the people who wrote the original MySQL. They are passionate about FOSS - which is why they forked it when Oracle acquired MySQL. The Community edition of MySQL is also FOSS - but they don’t set the direction or priorities of the core MySQL development. E.g. the MyISAM performance issues - Oracle has no interest in fixing them as they arise from other dev decisions. MariaDB did fix them, and that’s why large read systems using MyISAM run about 50% faster on Maria.

The differences show in lots of ways - for example, MariaDB provides FOSS features like Galera multi-master replication that are only available in paid commercial subscriptions of MySQL. That’s why folks like Debian and Google etc. adopted MariaDB as their default mysql-server provider. Ubuntu was the outlier here - I understand why, as they have a pragmatic approach.

The real question here is what feature of MySQL does Ghost specifically require… my guess is an underlying ORM library - most of which support both DB engines. One day I might have the time to dig through…

[mjw]

MyISAM predates the Oracle acquisition, and is depreciated, i.e, InnoDB is the default storage engine for both MySQL 8 and MaraDB 10.2 or later.

If you wish to continue the discussion, I’ll move the relevant posts to Off-topic.

@jeff

The real question here is what feature of MySQL does Ghost specifically require…

This is largely immaterial since Ghost only supports MySQL 8.

[StarManTheGamer]

I’m guessing 5.20.0 will be the last version for me then, which is unfortunate. Or I’d have to find a different blog platform and go from there.

[nem]

This is a tricky path to take given Oracle’s history for changing licensing on a whim in 2019, constant VirtualBox extension pack litigation, and of course the untimely demise of Solaris. I don’t believe Oracle’s future involves MySQL any more than it involved Solaris.

8 does not allow patch-level rollbacks without dumping the entire database before downgrade. If there’s a bungled release by Oracle, the only recourse is to offline the daemon for a full data export. I’d encourage your team to explore continuing to support MariaDB in addition to MySQL 8 instead of relying on a potentially fatal vendor lock-in with Oracle.

[jeff]

You can upvote this proposal

MariaDB Support

I’ve noticed that multiple people have been having the same issues as I have, and that’s where there are groundbreaking changes with ghost that halts MariaDB support (Specifically 5.21.0), and I see multiple users who are struggling with this, that also don’t wanna switch to MySQL. Specifically, in my case, I cannot switch to MySQL, and I don’t have the choice of running both at the same time. So this is where I come in with my idea: You guys should support MariaDB, I think it would make a bunc…

• but it’s only got 3 votes so far…

[mjw]

@nem

This is a tricky path to take given Oracle’s history for changing licensing…

Oracle may or nay not decide to change the licence at some time in the future. However, this is largely irrelevant since MySQL is currently licensed under GPL V2, and therefore, the current release (and any future release using this licence) will always be open source.

What’s more, the current development team is small and is not resourced to support both MariaDB and MySQL, and never has, so as things stand, MySQL is the only supported database for Ghost.

The community is free to contribute to Ghost, and provide alternative database options.

Incidentally, the Java changes were for commercial customers only.

[StarManTheGamer]

Hey everyone,

This update must have been somehow fixed because I updated and my post list has returned to normal. Recommended option: Update your ghost to the latest version.