Ghost database error preventing launch

Developer help Installation
1

I have Ghost installed as a docker container on Unraid with MariaDB as the MySQL database. I have previously had the site up and functional, but at some point in the last month it has stopped launching successfully.

Below is my error log:

[2021-04-30 14:10:16] e[36mINFOe[39m Ghost is running in production...
[2021-04-30 14:10:16] e[36mINFOe[39m Your site is now available on https://blog.********.com/
[2021-04-30 14:10:16] e[36mINFOe[39m Ctrl+C to shut down
[2021-04-30 14:10:16] e[36mINFOe[39m Ghost server started in 0.634s
[2021-04-30 14:10:16] e[35mWARNe[39m Database state requires migration.
[2021-04-30 14:10:16] e[36mINFOe[39m Creating database backup
[2021-04-30 14:10:16] e[36mINFOe[39m Database backup written to: /var/lib/ghost/content/data/********-com.ghost.2021-04-30-14-10-16.json
[2021-04-30 14:10:16] e[36mINFOe[39m Adding table: products
[2021-04-30 14:10:17] e[36mINFOe[39m Adding table: members_products
[2021-04-30 14:10:17] e[36mINFOe[39m Adding product "Ghost Subscription"
[2021-04-30 14:10:17] e[36mINFOe[39m Removing all products
[2021-04-30 14:10:17] e[36mINFOe[39m Dropping table: members_products
[2021-04-30 14:10:17] e[36mINFOe[39m Dropping table: products
[2021-04-30 14:10:17] e[31mERRORe[39m select `id` from `members` where `status` in ('comped', 'paid') - ER_BAD_FIELD_ERROR: Unknown column 'status' in 'where clause'
e[31m
e[31mselect `id` from `members` where `status` in ('comped', 'paid') - ER_BAD_FIELD_ERROR: Unknown column 'status' in 'where clause'e[39m

e[37m{"config":{"transaction":true},"name":"04-attach-members-to-product.js"}e[39m
e[33m"Error occurred while executing the following migration: 04-attach-members-to-product.js"e[39m

e[1me[37mError ID:e[39me[22m
e[90m300e[39m

e[1me[37mError Code: e[39me[22m
e[90mER_BAD_FIELD_ERRORe[39m

e[90m----------------------------------------e[39m

e[90mMigrationScriptError: select `id` from `members` where `status` in ('comped', 'paid') - ER_BAD_FIELD_ERROR: Unknown column 'status' in 'where clause'
at MigrationScriptError.KnexMigrateError (/var/lib/ghost/versions/4.3.3/node_modules/knex-migrator/lib/errors.js:7:26)
at new MigrationScriptError (/var/lib/ghost/versions/4.3.3/node_modules/knex-migrator/lib/errors.js:25:26)
at /var/lib/ghost/versions/4.3.3/node_modules/knex-migrator/lib/index.js:1055:19
at processTicksAndRejections (internal/process/task_queues.js:93:5)

Error: ER_BAD_FIELD_ERROR: Unknown column 'status' in 'where clause'
at Query.Sequence._packetToError (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
at Protocol._parsePacket (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket.<anonymous> (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/Connection.js:88:28)
at Socket.<anonymous> (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/Connection.js:526:10)
at Socket.emit (events.js:315:20)
at addChunk (internal/streams/readable.js:309:12)
at readableAddChunk (internal/streams/readable.js:284:9)
at Socket.Readable.push (internal/streams/readable.js:223:10)
at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
--------------------
at Protocol._enqueue (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/var/lib/ghost/versions/4.3.3/node_modules/mysql/lib/Connection.js:198:25)
at /var/lib/ghost/versions/4.3.3/node_modules/knex/lib/dialects/mysql/index.js:134:18
at new Promise (<anonymous>)
at Client_MySQL._query (/var/lib/ghost/versions/4.3.3/node_modules/knex/lib/dialects/mysql/index.js:128:12)
at Client_MySQL.query (/var/lib/ghost/versions/4.3.3/node_modules/knex/lib/client.js:168:17)
at /var/lib/ghost/versions/4.3.3/node_modules/knex/lib/transaction.js:325:24
at new Promise (<anonymous>)
at Client_MySQL.trxClient.query (/var/lib/ghost/versions/4.3.3/node_modules/knex/lib/transaction.js:320:12)
at Runner.query (/var/lib/ghost/versions/4.3.3/node_modules/knex/lib/runner.js:151:36)
at /var/lib/ghost/versions/4.3.3/node_modules/knex/lib/runner.js:40:23
at /var/lib/ghost/versions/4.3.3/node_modules/knex/lib/runner.js:277:24
at processTicksAndRejections (internal/process/task_queues.js:93:5)
at async up (/var/lib/ghost/versions/4.3.3/core/server/data/migrations/versions/4.3/04-attach-members-to-product.js:8:36)
at async Object.up (/var/lib/ghost/versions/4.3.3/core/server/data/migrations/utils.js:278:13)e[39m
e[39m
[2021-04-30 14:10:17] e[35mWARNe[39m Ghost is shutting down
[2021-04-30 14:10:17] e[35mWARNe[39m Ghost has shut down
[2021-04-30 14:10:17] e[35mWARNe[39m Your site is now offline
[2021-04-30 14:10:17] e[35mWARNe[39m Ghost was running for a few seconds

I’ve seen some topics on the Ghost help site that say this error is due to improper handling of ANSI characters, but I’ve validated that my MariaDB instance is not running in ANSI or ANSI_QUOTES mode:

MariaDB [(none)]> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                        |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

I’m at a complete loss as to what to do to address this issue. The MariaDB instance is also the host for a few other applications, so I’m a little tentative about messing with it without understanding what it is I’m doing.

As a second question, separate from the correcting the error above, I’d like to maybe stand up a new dedicated instance of MariaDB to host Ghost on since this is the 3rd time I’ve run into DB issues with Ghost. What do I stand to lose if I just redirect my existing Ghost instance to an entirely new DB?

3

I find a way to manually fix this by alter the members table of ghost blog mysql database, here is how.

1.login to your mysql ghost database;

2.backup you database

3.then use the follow command

ALTER TABLE members ADD COLUMN status varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'free';

4.Then restart your ghost blog.

1 Like
4

Thanks @jackma!!!

Your command worked perfectly with one minor tweak. I had to swap the single quotes you used around ‘free’ because they copy/pasted weird. Just wanted to document the change in case someone else stumbles upon this thread.

5

Awesome, thanks for that fix. I wasn’t able to upgrade any higher then 4.2.x cause of this error… :+1:

6

I’m having the same issue although the quoted fix isn’t working

7

Thanks! Any idea how we all got into this state? Seems like the upgrade process should’ve handled this