Error 400 ER_CANT_DROP_FIELD_OR_KEY after failed upgrade

Splicer · March 17, 2021, 4:31am

I appear to have botched an upgrade and am hoping that there is some way to salvage this installation or at worst extract the content from the database to migrate into a new installation.

I’m tempted to go through the schema of a known good installation and try to manually rebuild whatever is missing from my database, but that sounds tedious and more importantly won’t get to the bottom of why this happened in the first place. That’s important because I have broken two sites: one I tried to get from 3.38.3 to 3.42.2 and then to 4; the second (described here) I realized was dead before I got to try to upgrade to 4.

Here’s most of what the help topic template asks for:

URL https://positronic.works/ but all you’ll see there is a 502 Bad Gateway error
What version of Ghost It was 3.38.3 when it was running. I tried to update to 3.42.2 in preparation for an update to Ghost 4
How was Ghost installed and configured? cli ghost install
Node version Node v12.21.0
Database MySQL 8.0.23-0ubuntu0.20.04.1,
OS Ubuntu 20.04.2 LTS (GNU/Linux 5.4.0-67-generic x86_64)
Browser Safari on Mac, elinks on ubuntu

I was behind on my updates, back at 3.38.3, and got the announcement about Ghost 4. So I decided it was time to spend a little time updating. If I’d been smart I’d have done a backup first thing, but I decided to do the point upgrade to 3.42.2 first and do a backup before upgrading to Ghost 4.I thought the point update was anodyne and risk-free. I was stupid, obviously.

I started with npm install -g ghost-cli@latest as root and then perforned the update with ghost update v3 as the user the instance runs under:

positronic@electra:/srv/positronic.works$ ghost update v3
+ sudo systemctl is-active ghost_positronic-works
? Sudo Password [hidden]
✔ Checking system Node.js version
✔ Ensuring user is not logged in as ghost user
✔ Checking if logged in user is directory owner
✔ Checking current folder permissions
✔ Checking folder permissions
✔ Checking file permissions
✔ Checking content folder ownership
✔ Checking memory availability
✔ Checking free space
✔ Checking for available migrations
✔ Checking for latest Ghost version
All up to date!

Looked good at that point but nginx was (and is) reporting 502 Bad Gateway. Attempting to access localhost:2370 directly (with elinks) resulted in Connection Refused. So I checked to see if it was in fact running:

positronic@electra:/srv/positronic.works$ ghost ls
+ sudo systemctl is-active ghost_positronic-works
┌──────────────────┬───────────────────────┬─────────┬──────────────────────┬──────────────────────────┬──────┬─────────────────┐
│ Name             │ Location              │ Version │ Status               │ URL                      │ Port │ Process Manager │
├──────────────────┼───────────────────────┼─────────┼──────────────────────┼──────────────────────────┼──────┼─────────────────┤
│ positronic-works │ /srv/positronic.works │ 3.42.2  │ running (production) │ https://positronic.works │ 2370 │ systemd         │
└──────────────────┴───────────────────────┴─────────┴──────────────────────┴──────────────────────────┴──────┴─────────────────┘

However, I don’t see anything listening (or anything else) on port 2370:

root@electra:~# lsof -i -P -n | grep LISTEN
systemd-r   535 systemd-resolve   13u  IPv4  21052      0t0  TCP 127.0.0.53:53 (LISTEN)
sshd        666            root    3u  IPv4  22876      0t0  TCP *:22 (LISTEN)
sshd        666            root    4u  IPv6  22878      0t0  TCP *:22 (LISTEN)
nginx       703            root    6u  IPv4  23180      0t0  TCP *:443 (LISTEN)
nginx       703            root    7u  IPv6  23181      0t0  TCP *:443 (LISTEN)
nginx       703            root    8u  IPv4  23182      0t0  TCP *:80 (LISTEN)
nginx       703            root    9u  IPv6  23183      0t0  TCP *:80 (LISTEN)
nginx       708        www-data    6u  IPv4  23180      0t0  TCP *:443 (LISTEN)
nginx       708        www-data    7u  IPv6  23181      0t0  TCP *:443 (LISTEN)
nginx       708        www-data    8u  IPv4  23182      0t0  TCP *:80 (LISTEN)
nginx       708        www-data    9u  IPv6  23183      0t0  TCP *:80 (LISTEN)
mysqld      745           mysql   32u  IPv4  25223      0t0  TCP 127.0.0.1:33060 (LISTEN)
mysqld      745           mysql   51u  IPv4  25230      0t0  TCP 127.0.0.1:3306 (LISTEN)
master      879            root   13u  IPv4  24288      0t0  TCP *:25 (LISTEN)
master      879            root   14u  IPv6  24289      0t0  TCP *:25 (LISTEN)
node       1042           ghost   27u  IPv4  26209      0t0  TCP 127.0.0.1:2369 (LISTEN)
root@electra:~# lsof -i -P -n | grep 2370
root@electra:~#

(There is another site running on port 2369. There’s supposed to be one running on 2368 too but I’m trying to fix one installation at a time)

So I tried rolling back to 3.38.3 but that got me a 'No previous version found' message.

ghost run gave me an actual error message:

[2021-03-16 18:22:06] ERROR alter table `email_recipients` drop foreign key `email_recipients_email_id_foreign` - ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

alter table `email_recipients` drop foreign key `email_recipients_email_id_foreign` - ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists

"OuterError: update `members` set `email_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id), `email_opened_count` = (SELECT COUNT(id) FROM email_recipients WHERE email_recipients.member_id = members.id AND email_recipients.opened_at IS NOT NULL) - ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='"

Error ID:
    400

Error Code:
    ER_CANT_DROP_FIELD_OR_KEY

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

RollbackError: alter table `email_recipients` drop foreign key `email_recipients_email_id_foreign` - ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists
    at RollbackError.KnexMigrateError (/srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/lib/errors.js:7:26)
    at new RollbackError (/srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/lib/errors.js:31:26)
    at /srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/lib/index.js:440:23
    at tryCatcher (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues [as _onImmediate] (/srv/positronic.works/versions/3.42.2/node_modules/bluebird/js/release/async.js:15:14)
    at processImmediate (internal/timers.js:461:21)

Error: ER_CANT_DROP_FIELD_OR_KEY: Can't DROP 'email_recipients_email_id_foreign'; check that column/key exists
    at Query.Sequence._packetToError (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:314:20)
    at Socket.EventEmitter.emit (domain.js:483:12)
    at addChunk (_stream_readable.js:297:12)
    at readableAddChunk (_stream_readable.js:272:9)
    at Socket.Readable.push (_stream_readable.js:213:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:188:23)
    --------------------
    at Protocol._enqueue (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/srv/positronic.works/versions/3.42.2/node_modules/mysql/lib/Connection.js:198:25)
    at /srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:135:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/node_modules/knex/lib/dialects/mysql/index.js:129:12)
    at Client_MySQL.query (/srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/node_modules/knex/lib/client.js:169:17)
    at Runner.query (/srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/node_modules/knex/lib/runner.js:151:36)
    at Runner.queryArray (/srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/node_modules/knex/lib/runner.js:248:31)
    at /srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/node_modules/knex/lib/runner.js:38:25
    at /srv/positronic.works/versions/3.42.2/node_modules/knex-migrator/node_modules/knex/lib/runner.js:277:24
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
    at async down (/srv/positronic.works/versions/3.42.2/core/server/data/migrations/versions/3.39/06-add-email-recipient-index.js:46:13)
    at async Object.down (/srv/positronic.works/versions/3.42.2/core/server/data/migrations/utils.js:219:13)

So it looks to me like the upgrade process go hung up somewhere and and can’t proceed any further because the database changes that it wants to make either because it’s already been done or because it’s relying on something that should have been created but wasn’t.

Thanks for reading this far. I appear to have automatic backups for all my VPSs… except this one. One of the sites was in the beginning stages of development so I can start over from scratch. The other one has the content backed up not as recently as I’d like but it isn’t the end of the world. However, until I figure out what went wrong — specifically what I did wrong (other than not checking my backups first) I don’t dare try doing any of the other ghost site upgrades I need to do.

All suggestions are appreciated.

Topic		Replies	Views
Ghost 503 error after update to v4 Installation	9	931	March 18, 2021
502 Error and Missing Module for Update? Developer help	0	674	April 1, 2021
Error updating from 4.3.3 to 4.13 Developer help	2	287	September 10, 2021
Possible solution of error (502/503) when upgrading to Ghost v4 Installation	4	2063	March 19, 2021
Update went south 😞 Developer help	4	658	May 13, 2020

Error 400 ER_CANT_DROP_FIELD_OR_KEY after failed upgrade

Related Topics