Admin portal Members page stuck loading [400 error bad request]

Developer help Self-hosting
1

I recently updated from v4 to v5 and am experiencing the members page in the admin portal does not load and stays stuck on the loading screen with the animation .

I see 400 ERRORS in the console

GET .../ghost/api/admin/members/?order=id&limit=1&page=1&include=newsletters%2Clabels 
400 (Bad Request)

and

GET .../ghost/api/admin/members/?order=id&limit=1&page=1&include=newsletters%2Clabels 
400 (Bad Request)

I also used Postman to test the API still no luck

body:

{"errors":[{"message":"Request not understood error, cannot list members.","context":"Could not understand
request.","type":"BadRequestError","details":null,"property":null,"help":null,"code":"ER_BAD_FIELD_ERROR","id":"0d786f70-02dd-11f0-9a71-27755cd7b7fd","ghostErrorCode":null}]}

test results:

FAILED : Response status code is 200 | AssertionError: expected 400 to equal 200
FAILED : Response time is less than 200ms | AssertionError: expected 950 to be below 200
FAILED : Response body has a valid schema and format | AssertionError: expected '{"errors":[{"message":"Request not un…' to be empty...
FAILED : Content-Type header is 'text/html' | AssertionError: expected 'application/json; charset=utf-8' to equal 'text/html'

Im I missing something here? Typically Id ignore this however ghost does not provide any simple feature to password protect a page so my only option is to use the members feature which is very overkill for my needs.

2

Can you share your setup? From the ER_BAD_FIELD_ERROR in the code, my guess is you’re using MariaDB or MySQL 5 and running into a database incompatibility

1 Like
3

Im running

mysql  Ver 8.0.41-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

on a digitalocean droplet

Server version: 8.0.41-0ubuntu0.22.04.1 (Ubuntu)

I just migrated from Ghost v4 to v5 recently and I think I also had to update mysql.
I cannot find an example of the members database table for ghost v5 anywhere but referencing this to get an idea: Ghost/core/server/data/schema/schema.js at v5.0.0 · TryGhost/Ghost · GitHub

Im not seeing any differences from my current mysql members database table which looks like this :

Table: members
Create Table: CREATE TABLE `members` (
  `id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(191) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `created_by` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` varchar(24) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(191) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `note` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `uuid` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `geolocation` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email_open_rate` int unsigned DEFAULT NULL,
  `email_count` int unsigned NOT NULL DEFAULT '0',
  `email_opened_count` int unsigned NOT NULL DEFAULT '0',
  `status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'free',
  `last_seen_at` datetime DEFAULT NULL,
  `email_disabled` tinyint(1) NOT NULL DEFAULT '0',
  `comment_notifications` tinyint(1) NOT NULL DEFAULT '0',
  `free_member_signup_notification` tinyint(1) NOT NULL DEFAULT '0',
  `paid_subscription_started_notification` tinyint(1) NOT NULL DEFAULT '0',
  `paid_subscription_canceled_notification` tinyint(1) NOT NULL DEFAULT '0',
  `mention_notifications` tinyint(1) NOT NULL DEFAULT '0',
  `recommendation_notifications` tinyint(1) NOT NULL DEFAULT '0',
  `milestone_notifications` tinyint(1) NOT NULL DEFAULT '0',
  `donation_notifications` tinyint(1) NOT NULL DEFAULT '0',
  `enable_comment_notifications` tinyint(1) NOT NULL DEFAULT '0',
  `expertise` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `last_commented_at` datetime DEFAULT NULL,
  `transient_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `locale` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `members_email_unique` (`email`),
  UNIQUE KEY `members_uuid_unique` (`uuid`),
  KEY `members_email_open_rate_index` (`email_open_rate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

there may be a few more keys added while I was trouble shooting.

I can see the GET request from the admin portal passes parameters for “news_letters” and “labels” but omitting those from the request and testing in Postman still shows 400 Error.

4

Can you check if there’s an ANSI_QUOTES issue?

Also, can you share the server logs which should include the query that failed? You can do this with ghost log -e

5

This was super helpful as Im not a database guy.

Running ghost log -e outputed the exact missing table column and the table it is missing from in the logs:

CODE: ER_BAD_FIELD_ERROR
MESSAGE: select * from `members_products` where `expiry_at` < '2025-03-19T00:00:00.000Z' - Unknown column 'expiry_at' in 'where clause'

After adding the expiry_at column into the members_products table everything seems to be working.

1 Like