MariaDB no longer supported - admin post screen 400

[ngreve]

Issue Summary

I cannot access my posts. When I try to access my Posts (Drafts/Scheduled/Published) , I get the error message "Request not understood error, cannot list posts. Could not understand request. "

For example when I try to access the Drafts tab, the call to
https://nicogreve.de/ghost/api/admin/posts/?formats=mobiledoc%2Clexical&limit=30&page=1&filter=status%3Adraft fails with the 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": "<removed: dont know if sensitive>",
			"ghostErrorCode": null
		}
	]
}

But the website itself works just fine. I can also open the Pages tab without any problems.

The error also occurs with all browser Addons disabled and running in private mode.

Steps to Reproduce

See above

Ghost Version

5.21.0

Node.js Version

• v16.18.0
• v16.17.1
• v16.16.0

How did you install Ghost?

via npm

Database type

Other

Browser & OS version

ArchLinux - Firefox/Chromium

Relevant log / error output

No response

Code of Conduct

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

[iBotPeaches]

@ngreve - Are you using Maria? I had that and reported here #15727, but then I realized it was because of Maria and finally did the swap over to mysql8 and it went away.

[ngreve]

Yes, I am using MariaDB. Unfortunately switching to Mysql is not an option. My distro does not have an official package for mysql. If this is not fixable by the Ghost dev team, please close this issue and I will have to switch to another CMS.
(I know that MariaDB is officially not supported. But fortunately, in the past the dev team fixed some things anyway)

[b-turchyn]

This impacted me as well.

The issue occurs in the subquery for counting conversions. MariaDB seems to struggle with referencing a table that is the more than the "parent" of the subquery. If the post WHERE clause is done more than one child down, the issue occurs.

This Gist has the original query and a manually updated one that produces the same results. Haven't run a query plan on it to see what (if any) performance impact would arise.

A snippet of the original and updated queries:

Original:

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`,

Modified, working:

SELECT
    `posts`.*,
    (WITH `k` AS (
    SELECT
        `member_id`, `attribution_id`
    FROM
        `members_subscription_created_events`
UNION
    SELECT
        `member_id`, `attribution_id`
    FROM
        `members_created_events`)
    SELECT
        count(*)
    FROM
        `k` WHERE `k`.attribution_id = `posts`.id) AS `count__conversions`,

(Unrelated: it seems like this query is retrieving posts.* multiple times. Probably not inefficient from a query performance perspective, but inefficient from a data transfer perspective.)

Edit: the source of this seems to be at core/core/server/models/post.js#L1363 (conversions(modelOrCollection)).

[ErisDS]

Sorry for taking a couple of days to get to this. As of Ghost v5 the Ghost team are no longer maintaining support for any database in production other than MySQL 8.

I've explained this a few times on the forum, but I'll also explain here:

For MySQL5, MariaDB was a 100% compatible drop in replacement. Since then, the MariaDB team have opted to diverge and not keep compatibility with MySQL. Along side this, the ORM and query builder that we depended on to maintain compatibility have not matured enough or received enough contributions to fully handle the differences.

These things combined left our tiny team with the burden of handling incompatibilities, and it was actively detracting from us being able to deliver new features.

Therefore, as of v5 we declared MySQL8 the only DB supported in production knowing full well with the features we intended to build that at some point we'd hit a breaking change in MariaDB, which has now happened.

Ghost is an open source project, and if there's an individual or preferably a group of individuals who want to take on the burden of supporting MariaDB, that is of course possible however I think that realistically the effort belongs in knex.

[zblesk]

Thanks to everyone here in elaborating. I've just encountered this error today, when I'd been trying to switch from MySQL to MariaDB and wondered if I'd just messed something up.

Maybe I'll consider running an older version of Ghost instead. 🤔 I do not need any of the subscription features; and since I'm self-hosting low-traffic blogs on a small server I can't really enlarge, memory usage is becoming a problem. I run both MariaDB and MySQL. MySQL runs 3 Ghost blogs and nothing else, and has ~50% bigger memory use than MariaDB running 11 app DBs...

[TheManchineel]

Any advice to migrate from MariaDB to MySQLv8?

[ErisDS]

There's loads of discussions around this on the forum: https://forum.ghost.org/t/how-to-migrate-from-mariadb-10-to-mysql-8/29575

I'm going to close this issue now. If someone wants to take on the work of mariadb compatibility, we can reopen.

[TemaSM]

Introduced by:
22fe1c0#diff-3149f93d170c65cd0c97a204a2d19c6102351b3c542c357aa342874699521237R1363
Seems to be that can be fixed by simple escaping of table's name (posts.id to `posts`.id) inside whereRaw()