Issue Summary

Since updating to Ghost version 5.22.10 the following error message popped up when opening any of the admin post lists/views:
Request not understood error, cannot list posts. Could not understand request.

• Testing a bit around and downgrading showed, that the first affected version is 5.21.0.

• Version 5.22.0 through 5.22.10 are also affected.

• Version 5.20.0 is the last unaffected one.

Steps to Reproduce

1. Install Ghost version 5.22.10

   • Either through an update or a clean install.

   • It doesn't matter if there are already posts created or not.

2. Open the Ghost admin backend

3. Open one of the post lists/views.

Relevant log / error output

Browser HTTP Request

URL

Browser HTTP Request URL:

/ghost/api/admin/posts/?formats=mobiledoc%2Clexical&limit=30&page=1&filter=status%3A%5Bdraft%2Cscheduled%2Cpublished%2Csent%5D

Response

Browser HTTP Request Response:

{
  "errors": [
    {
      "message": "Request not understood error, cannot list posts.",
      "context": "Could not understand request.",
      "type": "BadRequestError",
      "details": null,
      "property": null,
      "help": null,
      "code": "ER_BAD_FIELD_ERROR",
      "id": "c1801070-6273-11ed-8e21-c102c3d5082c",
      "ghostErrorCode": null
    }
  ]
}

Server

Server/Backend log entry:

{
  "name": "Log",
  "hostname": "**REDACTED**",
  "pid": **REDACTED**,
  "level": 50,
  "req": {
    "meta": {
      "requestId": "88c4655a-1137-40fe-b3e4-9c2c85456430",
      "userId": "1"
    },
    "url": "/posts/?formats=mobiledoc%2Clexical&limit=30&page=1&filter=status%3A%5Bdraft%2Cscheduled%2Cpublished%2Csent%5D",
    "method": "GET",
    "originalUrl": "/ghost/api/admin/posts/?formats=mobiledoc%2Clexical&limit=30&page=1&filter=status%3A%5Bdraft%2Cscheduled%2Cpublished%2Csent%5D",
    "params": {},
    "headers": {
      "host": "**REDACTED**",
      "sec-ch-ua": "\"Google Chrome\";v=\"107\", \"Chromium\";v=\"107\", \"Not=A?Brand\";v=\"24\"",
      "dnt": "1",
      "sec-ch-ua-mobile": "?0",
      "app-pragma": "no-cache",
      "user-agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36",
      "content-type": "application/json; charset=UTF-8",
      "accept": "application/json, text/javascript, */*; q=0.01",
      "x-requested-with": "XMLHttpRequest",
      "x-ghost-version": "5.22",
      "sec-ch-ua-platform": "\"macOS\"",
      "sec-fetch-site": "same-origin",
      "sec-fetch-mode": "cors",
      "sec-fetch-dest": "empty",
      "referer": "https://**REDACTED**/ghost/",
      "accept-encoding": "gzip, deflate, br",
      "accept-language": "de,de-DE;q=0.9,en-GB;q=0.8,en;q=0.7,en-US;q=0.6,en-DE;q=0.5",
      "cookie": "**REDACTED**",
      "sec-gpc": "1",
      "x-forwarded-proto": "https",
      "x-forwarded-for": "**REDACTED**",
      "x-forwarded-host": "**REDACTED**",
      "x-forwarded-server": "**REDACTED**",
      "connection": "close"
    },
    "query": {
      "formats": "mobiledoc,lexical",
      "limit": "30",
      "page": "1",
      "filter": "status:[draft,scheduled,published,sent]"
    }
  },
  "res": {
    "_headers": {
      "x-powered-by": "Express",
      "vary": "Accept-Version, Accept-Encoding",
      "cache-control": "no-cache, private, no-store, must-revalidate, max-stale=0, post-check=0, pre-check=0",
      "content-type": "application/json; charset=utf-8",
      "content-length": "279",
      "etag": "W/\"117-g2i2/Ll57qIbCQdZFnvScK7Hl3c\""
    },
    "statusCode": 400,
    "responseTime": "30ms"
  },
  "err": {
    "id": "c1801070-6273-11ed-8e21-c102c3d5082c",
    "domain": "https://**REDACTED**/",
    "code": "ER_BAD_FIELD_ERROR",
    "name": "BadRequestError",
    "statusCode": 400,
    "level": "normal",
    "message": "Could not understand request.",
    "stack": "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 ('draft', 'scheduled', 'published', 'sent') and `posts`.`type` = 'post') order by CASE WHEN posts.status = 'scheduled' THEN 1 WHEN posts.status = 'draft' THEN 2 ELSE 3 END ASC,CASE WHEN posts.status != 'draft' THEN posts.published_at END DESC,posts.updated_at DESC,posts.id DESC limit 30 - Unknown column 'posts.id' in 'where clause'\n    at **REDACTED**/versions/5.22.10/node_modules/@tryghost/bookshelf-pagination/lib/bookshelf-pagination.js:259:27\n    at Packet.asError (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/packets/packet.js:728:17)\n    at Query.execute (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/commands/command.js:29:26)\n    at Connection.handlePacket (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/connection.js:456:32)\n    at PacketParser.onPacket (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/connection.js:85:12)\n    at PacketParser.executeStart (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/packet_parser.js:75:16)\n    at Socket.<anonymous> (**REDACTED**/versions/5.22.10/node_modules/mysql2/lib/connection.js:92:25)\n    at Socket.emit (node:events:513:28)\n    at addChunk (node:internal/streams/readable:315:12)\n    at readableAddChunk (node:internal/streams/readable:289:9)\n    at Socket.Readable.push (node:internal/streams/readable:228:10)\n    at TCP.onStreamRead (node:internal/stream_base_commons:190:23)",
    "hideStack": false
  },
  "msg": "Could not understand request.",
  "time": "2022-11-12T10:21:22.564Z",
  "v": 0
}

GitHub issue:

Workarounds, tips and hints