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)
OS: FreeBSD, v13.0
Node Version: v14.20.0
Ghost Version: 5.21.0
Ghost-CLI Version: 1.23.1
DB: mysql Ver 15.1 Distrib 10.5.17-MariaDB, for FreeBSD13.1 (amd64) using EditLine wrapper
Any assistance would be appreciated.
####################### 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.
- Run ghost backup.
- Mysqldump your databases.
- Uninstall Mysql (whatever version you are running).
- 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).
- pkg install mysql80-server-8.0.30 mysql80-client-8.0.30
- Run mysql_secure_installation and make sure you do not enable password validation.
- Get into MySQL.
- 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>';
Build your databases back up using your dump files.
Run ghost start.