Inconsistent database state

Developer help Installation
1

Hi,

I’ll start right away. Thank you for your time to debug this with me.

I am running ghost in a docker container. Started with version 2.x, now running 4.1.2. While updating, I went to the latest version of 2.x and then bumped directly to 4.1.2. I understood that was the best way to approach database migration.

The problem - I wanted to use the members feature, but when I press the Add yourself as a member to test button in the admin console, I get the error message

Internal server error, cannot save member. insert into `members` (`created_at`, `created_by`, `email`, `email_count`, `email_open_rate`, `email_opened_count`, `geolocation`, `id`, `name`, `note`, `status`, `subscribed`, `updated_at`, `updated_by`, `uuid`) values ('2021-04-05 11:06:43', '1', '[snipped]', 0, NULL, 0, NULL, '[snipped]', 'Sofia', NULL, 'free', true, '2021-04-05 11:06:43', '1', '[snipped]') - ER_BAD_FIELD_ERROR: Unknown column 'status' in 'field list'

I am also hosting my own mysql database so I went in to check out the members table

+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| id                 | varchar(24)      | NO   | PRI | NULL    |       |
| email              | varchar(191)     | NO   | UNI | NULL    |       |
| created_at         | datetime         | NO   |     | NULL    |       |
| created_by         | varchar(24)      | NO   |     | NULL    |       |
| updated_at         | datetime         | YES  |     | NULL    |       |
| updated_by         | varchar(24)      | YES  |     | NULL    |       |
| name               | varchar(191)     | YES  |     | NULL    |       |
| note               | varchar(2000)    | YES  |     | NULL    |       |
| subscribed         | tinyint(1)       | YES  |     | 1       |       |
| uuid               | varchar(36)      | YES  | UNI | NULL    |       |
| geolocation        | varchar(2000)    | YES  |     | NULL    |       |
| email_open_rate    | int(10) unsigned | YES  | MUL | NULL    |       |
| email_count        | int(10) unsigned | NO   |     | 0       |       |
| email_opened_count | int(10) unsigned | NO   |     | 0       |       |
+--------------------+------------------+------+-----+---------+-------+
14 rows in set (0.001 sec)

There is indeed no status column in this table, which is odd because there is a migration script to make it here.

While debugging a bit more, I also see that it might be relevant to share my migrations table content. I will share the last few rows, basically everything starting from version 4.0

| 154 | 01-update-mobiledoc.js                                           | 4.0     | 4.1            |
| 155 | 02-add-status-column-to-members.js                               | 4.0     | 4.1            |
| 156 | 03-populate-status-column-for-members.js                         | 4.0     | 4.1            |
| 157 | 04-drop-apps-related-tables.js                                   | 4.0     | 4.1            |
| 158 | 05-add-members-subscribe-events-table.js                         | 4.0     | 4.1            |
| 159 | 06-populate-members-subscribe-events-table.js                    | 4.0     | 4.1            |
| 160 | 07-alter-unique-constraint-for-posts-slug.js                     | 4.0     | 4.1            |
| 161 | 08-add-members-login-events-table.js                             | 4.0     | 4.1            |
| 162 | 09-add-members-email-change-events-table.js                      | 4.0     | 4.1            |
| 163 | 10-add-members-status-events-table.js                            | 4.0     | 4.1            |
| 164 | 11-add-members-paid-subscription-events-table.js                 | 4.0     | 4.1            |
| 165 | 12-delete-apps-related-settings-keys.js                          | 4.0     | 4.1            |
| 166 | 13-add-members-payment-events-table.js                           | 4.0     | 4.1            |
| 167 | 14-remove-orphaned-stripe-records.js                             | 4.0     | 4.1            |
| 168 | 15-add-frontmatter-column-to-meta.js                             | 4.0     | 4.1            |
| 169 | 16-refactor-slack-setting.js                                     | 4.0     | 4.1            |
| 170 | 17-populate-members-status-events-table.js                       | 4.0     | 4.1            |
| 171 | 18-transform-urls-absolute-to-transform-ready.js                 | 4.0     | 4.1            |
| 172 | 19-remove-labs-members-setting.js                                | 4.0     | 4.1            |
| 173 | 20-refactor-unsplash-setting.js                                  | 4.0     | 4.1            |
| 174 | 21-sanitize-email-batches-provider-id.js                         | 4.0     | 4.1            |
| 175 | 22-solve-orphaned-webhooks.js                                    | 4.0     | 4.1            |
| 176 | 23-regenerate-posts-html.js                                      | 4.0     | 4.1            |
| 177 | 24-add-missing-email-permissions.js                              | 4.0     | 4.1            |
| 178 | 25-populate-members-paid-subscription-events-table.js            | 4.0     | 4.1            |
| 179 | 26-add-cascade-on-delete.js                                      | 4.0     | 4.1            |
| 180 | 27-add-primary-key-brute-migrations-lock.js                      | 4.0     | 4.1            |
| 181 | 28-add-webhook-intergrations-foreign-key.js                      | 4.0     | 4.1            |
| 182 | 29-fix-foreign-key-for-members-stripe-customers-subscriptions.js | 4.0     | 4.1            |
| 183 | 30-set-default-accent-color.js                                   | 4.0     | 4.1            |
| 184 | 01-fix-backup-content-permission-typo.js                         | 4.1     | 4.1            |
| 185 | 02-add-unique-constraint-for-member-stripe-tables.js             | 4.1     | 4.1            |
+-----+------------------------------------------------------------------+---------+----------------+

I also said ok, lemme drop the database completely and just re-import my blog in a fresh new installation, but the problem persists.

So all I can tell is that my database is probably in an inconsistent state, but I’m not sure why or what went wrong where. Any thoughts?

2

We do our best to make this the best, safest way yes. However as I’m sure you can imagine accounting for every possible state and MySQL config is tricky.

Some more info that would be useful to help debug:

  • mysql version, and has it changed recently?
  • mysql collations (search the forum for various ways to get that info)
  • logs from when ghost is running migrations - did it say the status was skipped or throw any errors?
3

Hi Hannah! Thank you for replying.

mysql version, and has it changed recently?

I am using mariadb Ver 15.1 Distrib 10.4.18-MariaDB. This is version 10.4 installed from the MariaDB repos for Debian. The version has not changed recently, or at least not the major or minor revisions.

mysql collations

+----------------------------------------+--------------------+
| TABLE_NAME                             | TABLE_COLLATION    |
+----------------------------------------+--------------------+
| actions                                | utf8mb4_general_ci |
| api_keys                               | utf8mb4_general_ci |
| app_fields                             | utf8mb4_general_ci |
| app_settings                           | utf8mb4_general_ci |
| apps                                   | utf8mb4_general_ci |
| brute                                  | utf8mb4_general_ci |
| email_batches                          | utf8mb4_general_ci |
| email_recipients                       | utf8mb4_general_ci |
| emails                                 | utf8mb4_general_ci |
| integrations                           | utf8mb4_general_ci |
| invites                                | utf8mb4_general_ci |
| labels                                 | utf8mb4_general_ci |
| members                                | utf8mb4_general_ci |
| members_email_change_events            | utf8mb4_general_ci |
| members_labels                         | utf8mb4_general_ci |
| members_login_events                   | utf8mb4_general_ci |
| members_paid_subscription_events       | utf8mb4_general_ci |
| members_payment_events                 | utf8mb4_general_ci |
| members_status_events                  | utf8mb4_general_ci |
| members_stripe_customers               | utf8mb4_general_ci |
| members_stripe_customers_subscriptions | utf8mb4_general_ci |
| members_subscribe_events               | utf8mb4_general_ci |
| migrations_lock                        | utf8mb4_general_ci |
| mobiledoc_revisions                    | utf8mb4_general_ci |
| permissions                            | utf8mb4_general_ci |
| permissions_apps                       | utf8mb4_general_ci |
| permissions_roles                      | utf8mb4_general_ci |
| permissions_users                      | utf8mb4_general_ci |
| posts                                  | utf8mb4_general_ci |
| posts_authors                          | utf8mb4_general_ci |
| posts_meta                             | utf8mb4_general_ci |
| posts_tags                             | utf8mb4_general_ci |
| roles                                  | utf8mb4_general_ci |
| roles_users                            | utf8mb4_general_ci |
| sessions                               | utf8mb4_general_ci |
| settings                               | utf8mb4_general_ci |
| snippets                               | utf8mb4_general_ci |
| tags                                   | utf8mb4_general_ci |
| tokens                                 | utf8mb4_general_ci |
| users                                  | utf8mb4_general_ci |
| webhooks                               | utf8mb4_general_ci |
| migrations                             | utf8mb4_general_ci |
+----------------------------------------+--------------------+

logs from when ghost is running migrations

Not sure how to get those, but when running docker logs [container name] I see the following output related to databases:

[2021-04-06 19:00:03] INFO Database is in a ready state.
[2021-04-06 19:00:03] INFO Ghost database ready in 2.921s

I also shell into the container and run ghost setup migrate --verbose, but it only prints

[19:05:02] Checking for Ghost-CLI updates [started]
[19:05:03] Checking for Ghost-CLI updates [completed]
[19:05:03] Ensuring correct ~/.config folder ownership [started]
[19:05:03] Ensuring correct ~/.config folder ownership [completed]

We do our best to make this the best, safest way yes. However as I’m sure you can imagine accounting for every possible state and MySQL config is tricky.

Yes that sounds reasonable. Thank you again for taking the time to look into this!

4

In the meantime I googled for utf8mb4_general_ci ghost and found this post. Based on an answer there I ran the following query

MariaDB [(none)]> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='[snipped]';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| latin1                     | latin1_swedish_ci      |
+----------------------------+------------------------+

This looked wrong. So I decided to export my website to JSON, drop all tables in the database and then (as suggested in the aforementioned post) run

ALTER DATABASE `[snipped]` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

After this I restarted my docker container and the members table now has a status column. So the ALTER DATABASE statement changed something important.

I imported the JSON back into the new website and all seems to work fine. The Add yourself as a member to test button also works.

Thanks Hannah for pointing me in the right direction.

5

I’m glad you were able to get this sorted!

6

I have a similar issue. I have deployed ghost (v4.1.2 but recently updated from a v3 release) in a Docker container and am using MariaDB (version 10.4.18). I believe this is causing the subscriber email links for confirmation to be invalid. Not sure how to address. The specific error from the ghost log is below.

[2021-04-15 08:35:12] WARN insert into members (created_at, created_by, email, email_count, email_open_rate, email_opened_count, geolocation, id, name, note, status, subscribed, updated_at, updated_by, uuid) values (‘2021-04-15 13:35:12’, ‘607841108d150300a870ada3’, ‘email@gmail.com’, 0, NULL, 0, NULL, ‘607841108d150300a870ada3’, ‘firstname lastname’, NULL, ‘free’, true, ‘2021-04-15 13:35:12’, ‘607841108d150300a870ada3’, ‘5dd71e6b-5942-4330-9aa2-67c376026c14’) - ER_BAD_FIELD_ERROR: Unknown column ‘status’ in ‘field list’