Error when trying to view analytics ER_BAD_FIELD_ERROR

Developer help
1

If you’re looking for help, please provide information about your environment. If you delete this template and don’t provide any information, your topic will be automatically closed.

If you aren’t running the latest version of Ghost, the first thing we’ll ask you to do is update to the latest version of Ghost.

And

  • How was Ghost installed and configured? It was installed via the terminal with cli tool

  • What Node version, database, OS & browser are you using? nodejs version 18.12.1 using MariaDB 10.6.11 using latest firefox at MacOS

  • What errors or information do you see in the console? When trying to view statistics of a post, 400 error happens. It seemed to work fine before update to Ghost 5.

    "id":"ed9ca9e0-add5-11ed-aa01-6f1cfec52e42",
"domain":"https://cocktaildetour.com",
"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`.`type` = 'post' and `posts`.`status` in ('draft', 'published', 'scheduled', 'sent')) and `posts`.`id` = '63e778e42ce6c5f177c33d15' limit 1 - Unknown column 'posts.id' in 'where clause'\n    at Child.<anonymous> (/var/www/cocktaildetour.com/versions/5.34.0/core/server/models/base/plugins/crud.js:175:31)\n    at Packet.asError (/var/www/cocktaildetour.com/versions/5.34.0/node_modules/mysql2/lib/packets/packet.js:728:17)\n    at Query.execute (/var/www/cocktaildetour.com/versions/5.34.0/node_modules/mysql2/lib/commands/command.js:29:26)\n    at Connection.handlePacket (/var/www/cocktaildetour.com/versions/5.34.0/node_modules/mysql2/lib/connection.js:488:32)\n    at PacketParser.onPacket (/var/www/cocktaildetour.com/versions/5.34.0/node_modules/mysql2/lib/connection.js:94:12)\n    at PacketParser.executeStart (/var/www/cocktaildetour.com/versions/5.34.0/node_modules/mysql2/lib/packet_parser.js:75:16)\n    at Socket.<anonymous> (/var/www/cocktaildetour.com/versions/5.34.0/node_modules/mysql2/lib/connection.js:101:25)\n    at Socket.emit (events.js:376:20)\n    at addChunk (internal/streams/readable.js:309:12)\n    at readableAddChunk (internal/streams/readable.js:284:9)\n    at Socket.Readable.push (internal/streams/readable.js:223:10)\n    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)",
"hideStack":false

    },

  • What steps could someone else take to reproduce the issue you’re having? They could view analytics in the posts

2

Any update on this issue?

3

I have the same issue, any update on this issue?

4

I’m seeing the same problem.
When clicking on the analytics button for any given post, I get a red error bar across the top of the screen:

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

URL: https://detlev.bluelf.me

Installed as per your online instructions - hey, you’re the expert!
Ghost-CLI version: 1.24.0
Ghost version: 5.51.0
Theme: Alto

OS: FreeBSD 13.2-RELEASE
Database: MariaDB (I was under the impression this fork of MySQL was of higher quality than the original, hence using it for many years.)

Name           : mariadb105-server
Version        : 10.5.20

$ npm version
{
npm: ‘9.7.1’,
node: ‘16.20.0’,
v8: ‘9.4.146.26-node.26’,
uv: ‘1.44.2’,
zlib: ‘1.2.12’,
brotli: ‘1.0.9’,
ares: ‘1.19.0’,
modules: ‘93’,
nghttp2: ‘1.52.0’,
napi: ‘8’,
llhttp: ‘6.0.10’,
openssl: ‘1.1.1o-freebsd’,
cldr: ‘43.0’,
icu: ‘73.1’,
tz: ‘2023c’,
unicode: ‘15.0’
}

Firefox v114 (flatpak)

At the end of the day, I could ignore these analytics since I use Matomo anyway. However if I ever do send out an email or two, it’d be useful to see whether these were opened. AND, since your UI is so nice, it’d be great to have everything working and displaying its info in all its glory.

5

Ghost officially ended support for MariaDB at 5.0. (Same for MySQL 5.) Alternate databases continued to work until about 5.24, and then one or more changes went in that broke things for non-MySQL8 users. There are several posts where the Ghost team says that they don’t have the bandwidth to support and optimize for multiple databases, and encourages folks who’d like Ghost to work on another DB to contribute to the knex project that would make that happen. Long story short, you probably need to do a migration to MySQL 8 to get full functionality and avoid things breaking on random minor versions, because you’re using a database that isn’t tested against.

(I write this with a lot of sympathy, because I rolled out Ghost last summer for my personal site on MySQL 5.7 and while I was able to to upgrade to MySQL 8 to avoid the breakage from 5.24, I’m stuck at a Ghost upgrade script that won’t run. A project for this summer! I think I’ll reinstall and import data.)

1 Like
6

Appreciate your thoughts and empathy. Suppose the last time I compared MySQL to MariaDB was probably a decade ago where I concluded that MariaDB made beneficial modifications to the software and left MySQL behind like a stick in the mud. Since then I’ve always used MariaDB always simply remembering the words “drop-in replacement”. This morning I sifted through MariaDB’s compatibility page and that burst my bubble for good :face_with_peeking_eye:

In the interests of time I read the first couple of articles I came across that offer a comparison between the two RDBMSs:

In short, MySQL is

  • No longer fully open-source
  • owned by Oracle with their own motivation
  • less efficient
  • less secure

I’ll see if I can get a hold of the developers of Ghost to understand more detail about how they are accessing the database because to me, going to MySQL = regression/downgrade.

In the end it makes more sense, to me at least, to drop Ghost than to regress to poorer technology. Before doing that, I’ll do a bit more reading and see what the devs have to say and see if there’s a way for me to adjust code on my end …

7

Hey Cathy,
… just so you don’t feel like your energy was wasted … I had to build a new server anyway because storage ran out on the VM. With that I just decided to install MySQL rather than MariaDB ~ problem solved. Now every function in Ghost is working smoothly.

This was also a lot less trouble than contacting Ghost devs and inquire about what db calls are special that I’d have to find an equivalent to tweak my install/config …

Thought to express my appreciation :smiley:

Cheers

1 Like