MariaDB support

The analytics pages from posts will generate HTTP 400 errors thanks to a compatibility issue with MariaDB. Officially, only MySQL8 (and sqlite3) are supported.

It would show this error in the logs:

[2025-05-26 12:54:28] ERROR "GET /ghost/api/admin/posts/6834625e35802b06f1496305/?formats=mobiledoc%2Clexical&include=tags%2Cauthors%2Cauthors.roles%2Cemail%2Ctiers%2Cnewsletter%2Ccount.conversions%2Ccount.clicks%2Csentiment%2Ccount.positive_feedback%2Ccount.negative_feedback" 400 27ms


Could not understand request.


Error ID:

8f2b0d90-3a30-11f0-a25f-fd9c83e1cf02


Error Code:

ER_BAD_FIELD_ERROR


----------------------------------------


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`.`type` = 'post' and `posts`.`status` in ('draft', 'published', 'scheduled', 'sent')) and `posts`.`id` = '6834625e35802b06f1496305' limit 1 - Unknown column 'posts.id' in 'where clause'

at Child.<anonymous> (/srv/customer/sites/nodehello.tupeuxpastest.ch/versions/5.120.2/core/server/models/base/plugins/crud.js:194:31)

at Packet.asError (/srv/customer/sites/nodehello.tupeuxpastest.ch/versions/5.120.2/node_modules/mysql2/lib/packets/packet.js:740:17)

at Query.execute (/srv/customer/sites/nodehello.tupeuxpastest.ch/versions/5.120.2/node_modules/mysql2/lib/commands/command.js:29:26)

at Connection.handlePacket (/srv/customer/sites/nodehello.tupeuxpastest.ch/versions/5.120.2/node_modules/mysql2/lib/base/connection.js:475:34)

at PacketParser.onPacket (/srv/customer/sites/nodehello.tupeuxpastest.ch/versions/5.120.2/node_modules/mysql2/lib/base/connection.js:93:12)

at PacketParser.executeStart (/srv/customer/sites/nodehello.tupeuxpastest.ch/versions/5.120.2/node_modules/mysql2/lib/packet_parser.js:75:16)

at Socket.<anonymous> (/srv/customer/sites/nodehello.tupeuxpastest.ch/versions/5.120.2/node_modules/mysql2/lib/base/connection.js:100:25)

at Socket.emit (node:events:518:28)

at addChunk (node:internal/streams/readable:561:12)

at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)

at Readable.push (node:internal/streams/readable:392:5)

at TCP.onStreamRead (node:internal/stream_base_commons:189:23)

This syntax is valid in MySQL 8, which supports common table expressions (CTEs) inside subqueries, including scalar subqueries like the one you're using. However, MariaDB (as of version 10.6 and earlier) does not support using CTEs inside scalar subqueries.

I have written a small patch to address this issue.

Here is a simple way to use it:

cd current
curl -L https://gist.githubusercontent.com/reneluria/8cbbfbc001e542c77d6d5887fbafe5d3/raw/65e0ce31753b4687d0eb67fc030734a35d3ffbad/ghost-post.patch | patch -p0

Ghost uses knex to build query, an efficient way to not implement direct queries themselves.

SQL Query Builder for Javascript | Knex.js

Beta knex.js documentation.

Knex.js

The problem is with this part of the generated query

(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 the posts.id is not valid within the subquery in MariaDB

So this is the working code:

conversions(modelOrCollection) {
    modelOrCollection.query('columns', 'posts.*');

    modelOrCollection.query((qb) => {
        qb.leftJoin(
            ghostBookshelf.knex.raw(`(
                SELECT
                    COALESCE(COUNT(DISTINCT member_id), 0) AS count__conversions,
                    attribution_id as post_id
                FROM (
                    SELECT member_id, attribution_id
                    FROM members_subscription_created_events
                    UNION ALL
                    SELECT member_id, attribution_id
                    FROM members_created_events
                ) AS all_events
                GROUP BY attribution_id
            ) as conversions_sub`),
            'posts.id',
            'conversions_sub.post_id'
        )
        .select('conversions_sub.count__conversions');
    });
}

But, be careful, it may not work with future versions...

MariaDB Foundation - MariaDB.org

… Continue reading “MariaDB Foundation”

MariaDB.org