Request not understood error, cannot read post. Could not understand request.

[Coding-Kiwi]

Issue Summary

I cannot click on a recent post in the admin dashboard

the link href is "#/posts/analytics/668866d8b977900001b0e561"

when I click, the request made in the network tab is https://my.domain/ghost/api/admin/posts/668866d8b977900001b0e561/?formats=mobiledoc%2Clexical&include=tags%2Cauthors%2Cauthors.roles%2Cemail%2Ctiers%2Cnewsletter%2Ccount.conversions%2Ccount.clicks%2Csentiment%2Ccount.positive_feedback%2Ccount.negative_feedback

Steps to Reproduce

1. Go to dashboard an click on a post in the "Recent posts" list
2. Error page 404 and a red error bar on the top

Ghost Version

5.87.0

Node.js Version

18 I guess? I use the docker image

How did you install Ghost?

docker container

Database type

Other

Browser & OS version

Brave 1.67.123 Chromium: 126.0.6478.126 Windows 11 (64-Bit)

Relevant log / error output

Request not understood error, cannot read post. Could not understand request.

{
    "errors": [
        {
            "message": "Request not understood error, cannot read post.",
            "context": "Could not understand request.",
            "type": "BadRequestError",
            "details": null,
            "property": null,
            "help": null,
            "code": "ER_BAD_FIELD_ERROR",
            "id": "0c220b90-3fab-11ef-9091-5f9a268e6b82",
            "ghostErrorCode": null
        }
    ]
}

Code of Conduct

• [X] I agree to be friendly and polite to people in this repository

[Coding-Kiwi]

I checked the console and have some more debug info

Unknown column 'posts.id' in 'where clause'

am I missing a migration or something?

ghost-1  | [2024-07-03 19:54:51] INFO Ghost is running in production...
ghost-1  | [2024-07-03 19:54:51] INFO Your site is now available on https://my.domain/
ghost-1  | [2024-07-03 19:54:51] INFO Ctrl+C to shut down
ghost-1  | [2024-07-03 19:54:51] INFO Ghost server started in 1.068s
ghost-1  | [2024-07-03 19:54:51] WARN Database state requires migration.
ghost-1  | [2024-07-03 19:54:52] INFO Creating database backup
ghost-1  | [2024-07-03 19:54:52] INFO Database backup written to /var/lib/ghost/content/data/codingkiwi.ghost.2024-07-03-19-54-52.json
ghost-1  | [2024-07-03 19:54:52] INFO Running migrations.
ghost-1  | [2024-07-03 19:54:52] INFO Adding newsletters.show_subhead column
ghost-1  | [2024-07-03 19:54:52] INFO Renaming column 'show_subhead' to 'show_subtitle' in table 'newsletters'
ghost-1  | [2024-07-03 19:54:52] INFO Adding post_revisions.custom_excerpt column
ghost-1  | [2024-07-03 19:54:52] WARN Skipping migration - noop
ghost-1  | [2024-07-03 19:54:52] INFO Renaming column 'show_subtitle' to 'show_excerpt' in table 'newsletters'
ghost-1  | [2024-07-03 19:54:52] INFO Adding index for 'updated_at' in table 'posts'
ghost-1  | [2024-07-03 19:54:53] INFO Adding index for 'post_id,tag_id' in table 'posts_tags'
ghost-1  | [2024-07-03 19:54:53] INFO Adding index for 'type,status,updated_at' in table 'posts'
ghost-1  | [2024-07-03 19:54:53] INFO Database is in a ready state.
ghost-1  | [2024-07-03 19:54:53] INFO Ghost database ready in 2.558s

this is the log of the last container startup

[9larsons]

@Coding-Kiwi Thanks for the report. Can you confirm that the post exists? Can you click on it and enter the editor?

yourdomain.../ghost/#/editor/post/668866d8b977900001b0e561

This error suggests that the ID doesn't actually exist.

[Coding-Kiwi]

@Coding-Kiwi Thanks for the report. Can you confirm that the post exists? Can you click on it and enter the editor?

yourdomain.../ghost/#/editor/post/668866d8b977900001b0e561

This error suggests that the ID doesn't actually exist.

hi, yes if I enter the url manually https://my.domain/ghost/#/editor/post/668866d8b977900001b0e561 it works, or if I virst visit the posts page and then click on it in that list

[Coding-Kiwi]

@Coding-Kiwi Thanks for the report. Can you confirm that the post exists? Can you click on it and enter the editor?

yourdomain.../ghost/#/editor/post/668866d8b977900001b0e561

This error suggests that the ID doesn't actually exist.

make sure to check the first comment I added, it suggests that there is an error in the SQL or more likely my database is somehow outdated but ghost does not migrate it

[9larsons]

The docker container will auto-run migrations when restarting Ghost. It seems likelier that this was interrupted. I cannot reproduce anything on my end, either in Ghost or within MySQL, even if I run the same query with a nonexistent post.

I would suggest trying to restore from a backup and see if that resolves the issue for this post. You can monitor the docker logs to see when the migrations and running and finish.

[Coding-Kiwi]

The docker container will auto-run migrations when restarting Ghost. It seems likelier that this was interrupted. I cannot reproduce anything on my end, either in Ghost or within MySQL, even if I run the same query with a nonexistent post.

I would suggest trying to restore from a backup and see if that resolves the issue for this post. You can monitor the docker logs to see when the migrations and running and finish.

The problem seems to be my database, it is mariadb which is unsupported by ghost. Should have noticed the big red warning but the claim of mariadb "MariaDB is a natural choice as it maintains compatibility with MySQL protocols, doesn’t require any changes to your application" seems to be wrong then

The query

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
    from posts where (
        posts.type = 'post'
        and posts.status in ('draft', 'published', 'scheduled', 'sent')
    )
    and posts.id = '668866d8b977900001b0e561'
    limit 1

does not work because posts.id seems to be undefined in the with k as( part

MariaDB handles common table expressions (CTEs) differently than MySQL. I spinned up a plain mysql docker container, exported and imported the database and the query works, so we can close this I guess?

[Coding-Kiwi]

Relevant upgrade guide in the docs https://forum.ghost.org/t/how-to-migrate-from-mariadb-10-to-mysql-8/29575

[9larsons]

Yep that's your best bet. Glad it's working for you now - surprised there wasn't more that wasn't working.