Ghost: Can’t Upgrade from v2.27 to v3.8 ER_NO_SUCH_TABLE error

Created on 27 Feb 2020  ·  18Comments  ·  Source: TryGhost/Ghost

Issue Summary

I am trying to upgrade my Ghost v2.27 install to v3.8 and I am getting the following error

You can also check theme compatibility at https://gscan.ghost.org

? Are you sure you want to proceed with migrating to Ghost 3.8.0? Yes
✔ Updating to a major version

  • sudo systemctl stop ghost_chrometips-nspeaks-com
    ✔ Stopping Ghost
    ✔ Linking latest Ghost and recording versions
  • sudo systemctl start ghost_chrometips-nspeaks-com
  • sudo systemctl stop ghost_chrometips-nspeaks-com
    ✖ Restarting Ghost
    A GhostError occurred.

Message: show columns from emails - ER_NO_SUCH_TABLE: Table 'chrometips.emails' doesn't exist
Suggestion: journalctl -u ghost_chrometips-nspeaks-com -n 50

Debug Information:
OS: Ubuntu, v18.04.4 LTS
Node Version: v10.19.0
Ghost Version: 3.8.0
Ghost-CLI Version: 1.13.1
Environment: production
Command: 'ghost upgrade'

Additional log info available in: /home//.ghost/logs/ghost-cli-debug-2020-02-25T13_30_27_168Z.log

Contents of the Log file

Debug Information:
    OS: Ubuntu, v18.04.4 LTS
    Node Version: v10.19.0
    Ghost Version: 3.8.0
    Ghost-CLI Version: 1.13.1
    Environment: production
    Command: 'ghost upgrade'
Message: show columns from `emails` - ER_NO_SUCH_TABLE: Table 'chrometips.emails' doesn't exist
Suggestion: journalctl -u ghost_chrometips-nspeaks-com -n 50
Stack: Error: show columns from `emails` - ER_NO_SUCH_TABLE: Table 'chrometips.emails' doesn't exist
    at Server.server.close (/usr/lib/node_modules/ghost-cli/lib/utils/port-polling.js:38:28)
    at Object.onceWrapper (events.js:286:20)
    at Server.emit (events.js:198:13)
    at emitCloseNT (net.js:1619:8)
    at process._tickCallback (internal/process/next_tick.js:63:19)

Result of the journalctl command - http://bit.ly/2vZxJ6p 2

To Reproduce

  1. Tried ghost update
  2. Tried ghost update --force
  3. Followed https://ghost.org/update/?v=2.0

Technical details:

  • Ghost Version: 2.27
  • Node Version: 10.19
  • Browser/OS: Ubuntu 18.04.4
picture navjotjsingh

All 18 comments

Upgrade to Ghost 3.0.0 then to 3.9.0 :)

pascalandy picture pascalandy on 3 Mar 2020

I tried that but didn't work.

Here: https://forum.ghost.org/t/cant-upgrade-from-v2-27-to-v3-8-er-no-such-table-error/12271/5?u=nspeaks

Upgraded to 2.38 which worked but couldn't upgrade to 3.0.0.

Anyway, the only thing that worked was to uninstall 2.38 and install the latest version. Had taken backups so it was smooth.

navjotjsingh picture navjotjsingh on 3 Mar 2020

Re-opening because this still needs investigation, migration from 2.x to 3.x should not error.

kevinansfield picture kevinansfield on 4 Mar 2020

Was not successful reproducing this bug. Scenarios I've tried:

  1. Installing v2.27.0 with MySQL and running ghost update 3.8.0
  2. Installing v2.27.0 with SQLite and running ghost update 3.8.0
  3. Installing v2.27.0 with MySQL and running ghost upgrade 3.8.0
  4. Installing v2.27.0 with MySQL and running ghost upgrade 3.0.0

All resulted in fully working site.

My testing environment atm:

  • Ghost Version: 2.27
  • Node Version: 10.19
  • Browser/OS: Ubuntu 20.04
  • MySQL: 5.7.29
  • SQLite: 3.31.1 2020-01-27

This error is most likely coming from 3.1/10-add-email-error-data-column.js migration and for whatever reason it was run before 3.1/05-add-emails-table.js OR the 3.1/05-add-emails-table.js migration didn't succeed at all. Didn't see any good reason for this to happen. Next time a similar error occurs would need some more logs to examine it.

Also checked migration emailtable related migrations without spotting anything out of ordinary.

naz picture naz on 1 Jun 2020

The only additional information I can give now is here: https://forum.ghost.org/t/cant-upgrade-from-v2-27-to-v3-8-er-no-such-table-error/12271/5

navjotjsingh picture navjotjsingh on 1 Jun 2020
👍1

@naz Could you please check additional diagnostic information on that issue in https://github.com/TryGhost/Ghost/issues/11855 ?

andrewmatveychuk picture andrewmatveychuk on 2 Jun 2020

Oh good point @andrewmatveychuk , will have a look :+1:

naz picture naz on 2 Jun 2020

Just one more thing. I used MariaDB 10.3. Not sure if that matters.

And I am pasting the content of the journalctl command here instead of the paste.org link in my original post above. Easier to track.

  | -- Logs begin at Mon 2019-02-11 12:08:42 UTC, end at Tue 2020-02-25 13:34:28 UTC. --
-- | --
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Client_MySQL.query (/var/www/chrometips/html/versions/3.8.0/node_modules/knFeb 25 13:30:26 nspeaks24 node[1805]:     at /var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modFeb 25 13:30:26 nspeaks24 node[1805]:     at tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migraFeb 25 13:30:26 nspeaks24 node[1805]:     at Function.Promise.attempt.Promise.try (/var/www/chrometips/html/versions/3.8Feb 25 13:30:26 nspeaks24 node[1805]:     at Client_MySQL.trxClient.query (/var/www/chrometips/html/versions/3.8.0/node_Feb 25 13:30:26 nspeaks24 node[1805]:     at Runner.<anonymous> (/var/www/chrometips/html/versions/3.8.0/node_modules/knFeb 25 13:30:26 nspeaks24 node[1805]:     at Runner.tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/kneFeb 25 13:30:26 nspeaks24 node[1805]:     at Runner.query (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migFeb 25 13:30:26 nspeaks24 node[1805]:     at Runner.queryArray (/var/www/chrometips/html/versions/3.8.0/node_modules/kneFeb 25 13:30:26 nspeaks24 node[1805]:     at /var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modFeb 25 13:30:26 nspeaks24 node[1805]:     at tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migraFeb 25 13:30:26 nspeaks24 node[1805]:     at /var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modFeb 25 13:30:26 nspeaks24 node[1805]:     at tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migraFeb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromiseFromHandler (/var/www/chrometips/html/versions/3.8.0/Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise (/var/www/chrometips/html/versions/3.8.0/node_moduleFeb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise0 (/var/www/chrometips/html/versions/3.8.0/node_modulFeb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromises (/var/www/chrometips/html/versions/3.8.0/node_modulFeb 25 13:30:26 nspeaks24 node[1805]:     at Promise._fulfill (/var/www/chrometips/html/versions/3.8.0/node_modules/knexFeb 25 13:30:26 nspeaks24 node[1805]:     at PromiseArray._resolve (/var/www/chrometips/html/versions/3.8.0/node_modulesFeb 25 13:30:26 nspeaks24 node[1805]:     at PromiseArray._promiseFulfilled (/var/www/chrometips/html/versions/3.8.0/nodFeb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise (/var/www/chrometips/html/versions/3.8.0/node_moduleFeb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise0 (/var/www/chrometips/html/versions/3.8.0/node_modulFeb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromises (/var/www/chrometips/html/versions/3.8.0/node_modulFeb 25 13:30:26 nspeaks24 node[1805]:     at _drainQueueStep (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-Feb 25 13:30:26 nspeaks24 node[1805]:     at _drainQueue (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrFeb 25 13:30:26 nspeaks24 node[1805]:     at Async._drainQueues (/var/www/chrometips/html/versions/3.8.0/node_modules/knlines 1-27...skipping...
  | -- Logs begin at Mon 2019-02-11 12:08:42 UTC, end at Tue 2020-02-25 13:34:28 UTC. --
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Client_MySQL.query (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/knex/lib/client.js:192:17)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at /var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/knex/lib/transaction.js:232:21
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/util.js:16:23)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Function.Promise.attempt.Promise.try (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/method.js:39:29)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Client_MySQL.trxClient.query (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/knex/lib/transaction.js:229:33)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Runner.<anonymous> (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:138:36)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Runner.tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/util.js:16:23)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Runner.query (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/method.js:15:34)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Runner.queryArray (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:207:40)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at /var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/knex/lib/runner.js:44:23
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/util.js:16:23)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at /var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/using.js:185:26
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at tryCatcher (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/util.js:16:23)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromiseFromHandler (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:547:31)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:604:18)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise0 (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:649:10)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromises (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:729:18)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._fulfill (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:673:18)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at PromiseArray._resolve (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise_array.js:127:19)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at PromiseArray._promiseFulfilled (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise_array.js:145:14)        Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:609:26)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromise0 (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:649:10)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Promise._settlePromises (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/promise.js:729:18)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at _drainQueueStep (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/async.js:93:12)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at _drainQueue (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/async.js:86:9)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Async._drainQueues (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/async.js:102:5)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at Immediate.Async.drainQueues (/var/www/chrometips/html/versions/3.8.0/node_modules/knex-migrator/node_modules/bluebird/js/release/async.js:15:14)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at runCallback (timers.js:705:18)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at tryOnImmediate (timers.js:676:5)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at processImmediate (timers.js:658:5)
  | Feb 25 13:30:26 nspeaks24 node[1805]:     at process.topLevelDomainCallback (domain.js:126:23)
  | Feb 25 13:30:26 nspeaks24 node[1805]:
  | Feb 25 13:30:26 nspeaks24 node[1805]: [2020-02-25 13:30:26] INFO Bootstrap client was closed.
  | Feb 25 13:30:26 nspeaks24 node[1805]: [2020-02-25 13:30:26] WARN Ghost has shut down
  | Feb 25 13:30:26 nspeaks24 systemd[1]: Stopping Ghost systemd service for blog: chrometips-nspeaks-com...
  | Feb 25 13:30:26 nspeaks24 node[1805]: [2020-02-25 13:30:26] WARN Your site is now offline
  | Feb 25 13:30:27 nspeaks24 node[1805]: /usr/lib/node_modules/ghost-cli/lib/process-manager.js:46
  | Feb 25 13:30:27 nspeaks24 node[1805]:         throw error;
  | Feb 25 13:30:27 nspeaks24 node[1805]:         ^
  | Feb 25 13:30:27 nspeaks24 node[1805]: [object Object]
  | Feb 25 13:30:27 nspeaks24 systemd[1]: ghost_chrometips-nspeaks-com.service: Main process exited, code=exited, status=1/FAILURE
  | Feb 25 13:30:27 nspeaks24 systemd[1]: ghost_chrometips-nspeaks-com.service: Failed with result 'exit-code'.
  | Feb 25 13:30:27 nspeaks24 systemd[1]: Stopped Ghost systemd service for blog: chrometips-nspeaks-com.
  | Feb 25 13:30:37 nspeaks24 systemd[1]: Started Ghost systemd service for blog: chrometips-nspeaks-com.
  | Feb 25 13:30:38 nspeaks24 node[1953]: - Inspecting operating system
  | Feb 25 13:30:43 nspeaks24 node[1953]: [2020-02-25 13:30:43] INFO Ghost is running in production...
  | Feb 25 13:30:43 nspeaks24 node[1953]: [2020-02-25 13:30:43] INFO Your site is now available on https://chrometips.nspeaks.com/
  | Feb 25 13:30:43 nspeaks24 node[1953]: [2020-02-25 13:30:43] INFO Ctrl+C to shut down
  | Feb 25 13:30:43 nspeaks24 node[1953]: [2020-02-25 13:30:43] INFO Ghost boot 4.511s
  | Feb 25 13:30:43 nspeaks24 node[1953]: [2020-02-25 13:30:43] INFO Bootstrap client was closed.
navjotjsingh picture navjotjsingh on 2 Jun 2020
👍1

Had a second look at the #11855. It wasn't clear at the beginning if current issue is definitely the same one or just result in same resulting error: 

Message: show columns from emails - ER_NO_SUCH_TABLE: Table 'chrometips.emails' doesn't exist

It was unclear because migration fails at some point and in both cases. Resulting error is caused by migrations never reached the point of creating emails table (this migration). It is not clear from the logs of current issue or referenced forum thread which migration caused the failure along the way.

From #11855 it is clear the error is happening when members_labels is trying to add foreign key to members table.:

MigrationScriptError: alter table `members_labels` add constraint `members_labels_member_id_foreign` foreign key (`member_id`) references `members` (`id`) - UNKNOWN_CODE_PLEASE_REPORT: Referencing column 'member_id' and referenced column 'id' in foreign key constraint 'members_labels_member_id_foreign' are incompatible.

Which is related to faulty migration which was fixed with https://github.com/TryGhost/Ghost/issues/11597.

The UNKNOW_CODE_PLEASE_REPORT tells this error comes from MariaDB because mysql driver has been lacking MariaDB-specific error codes for years. It also falls in line with what was mentioned above:

Just one more thing. I used MariaDB 10.3. Not sure if that matters.

To push this further would need to get MariaDB instance running locally or get some help from community debugging this issue further. How I _suspect_ one could reproduce the error from instance running from source:

  1. Get MariaDB 10.3. running locally (guide here)
  2. Initialize Ghost source at version v2.27 and connect to MariaDB (git checkout 2.27 and go through regular initialization)
  3. Update the codebase to v3.8 and run migrations with debug logs (git checkout 3.8 && yarn && DEBUG=ghost:migrations knex-migrator migrate --v 3.8 --force

Step 3 should throw same error as #11855 after this SQL statement - alter tablemembers_labelsadd constraintmembers_labels_member_id_foreignforeign key (member_id) referencesmembers(id) that comes from 2-add-members-labels-table.js migration.

NOTE: when running the migration to 3.8 it should have picked up a fix done here. Probably there's some other MariaDB quirk which we missed to test for.

naz picture naz on 8 Jun 2020

@naz Please, note that in the case of https://github.com/TryGhost/Ghost/issues/11855 I'm running Ghost with MySQL. The currently installed MySQL version is 8.0.20-0ubuntu0.20.04.1 amd64

Also, I can rerun the update process to reproduce the issue to supply you with additional debug information if you need any.

andrewmatveychuk picture andrewmatveychuk on 8 Jun 2020

@andrewmatveychuk do you have by any chance an export of the instance that could be safely shared (without sensitive information)? Would be awesome to have fully reproducible scenario for this :+1:

naz picture naz on 8 Jun 2020

@andrewmatveychuk do you have by any chance an export of the instance that could be safely shared (without sensitive information)? Would be awesome to have fully reproducible scenario for this 👍

I run my site on a DigitalOcean droplet. I can create a snapshot and transfer it to you: https://www.digitalocean.com/docs/images/snapshots/how-to/change-owners/

Does it make sense?

andrewmatveychuk picture andrewmatveychuk on 8 Jun 2020

@andrewmatveychuk yes that could work. I'm [email protected] on DO

naz picture naz on 10 Jun 2020

@andrewmatveychuk yes that could work. I'm [email protected] on DO

Hi @naz,
The snapshot transfer has been initiated. Please, check your mail for the access details. Let me know if there are any issues.

andrewmatveychuk picture andrewmatveychuk on 10 Jun 2020

Hi @naz,
Have you managed to spin up the DO snapshot?

andrewmatveychuk picture andrewmatveychuk on 12 Jun 2020

Hey @andrewmatveychuk I did receive the snapshot on my account but haven't have a chance to look into it yet. It is on my list of things to do.

naz picture naz on 12 Jun 2020
👍1

Have spent an hour playing with the snapshot provided by @andrewmatveychuk. The issue seems to be some sort of charset/collation bug in MySQL 8. I haven't investigated much further as that seems like a rabbit hole that is not worth of a time investment at this point, given this is an unsupported DB engine.

To catch the exact issue don't upgdate to the latest version. Instead upgrade to Ghost 3.0.0 from 2.38.1 using ghost update 3.0.0 migration fails on an earlier stage with:

Message: alter table `posts_meta` add constraint `posts_meta_post_id_foreign` foreign key (`post_id`) references `posts` (`id`) - UNKNOWN_CODE
_PLEASE_REPORT: Referencing column 'post_id' and referenced column 'id' in foreign key constraint 'posts_meta_post_id_foreign' are incompatible
e.                                
Help: Error occurred while executing the following migration: 04-add-posts-meta-table.js

After inspecting the DB these are schema definitions for posts_meta and posts tables:

mysql> show create table posts \G                                                                                                             
*************************** 1. row ***************************                                                                                
       Table: posts                                                                                                                           
Create Table: CREATE TABLE `posts` (                                                                                                          
  `id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,                                                                                       
  `uuid` varchar(36) COLLATE utf8mb4_general_ci NOT NULL,                                                                                     
  `title` varchar(2000) COLLATE utf8mb4_general_ci NOT NULL,                                                                                  
  `slug` varchar(191) COLLATE utf8mb4_general_ci NOT NULL,                                                                                    
  `mobiledoc` longtext COLLATE utf8mb4_general_ci,                                                                                            
  `html` longtext COLLATE utf8mb4_general_ci,                                                                                                 
  `comment_id` longtext COLLATE utf8mb4_general_ci,                                                                                           
  `plaintext` longtext COLLATE utf8mb4_general_ci,                                                                                            
  `feature_image` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                      
  `featured` tinyint(1) NOT NULL DEFAULT '0',                                                                                                 
  `page` tinyint(1) NOT NULL DEFAULT '0',                                                                                                     
  `status` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',                                                                   
  `locale` varchar(6) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                                
  `visibility` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'public',                                                              
  `meta_title` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                         
  `meta_description` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                   
  `author_id` varchar(24) 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,                                                                           
  `published_at` datetime DEFAULT NULL,                                                                                                       
  `published_by` varchar(24) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                         
  `custom_excerpt` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                     
  `codeinjection_head` text COLLATE utf8mb4_general_ci,                                                                                       
  `codeinjection_foot` text COLLATE utf8mb4_general_ci,                                                                                       
  `og_image` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                           
  `og_title` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                            
  `og_description` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                      
  `twitter_image` varchar(2000) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                      
  `twitter_title` varchar(300) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                       
  `twitter_description` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                 
  `custom_template` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,                                                                     
  `canonical_url` text COLLATE utf8mb4_general_ci,                     
  PRIMARY KEY (`id`),                                                  
  UNIQUE KEY `posts_slug_unique` (`slug`)                              
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci     

mysql> show create table posts_meta \G
*************************** 1. row ***************************
       Table: posts_meta
Create Table: CREATE TABLE `posts_meta` (
  `id` varchar(24) NOT NULL,
  `post_id` varchar(24) NOT NULL,
  `og_image` varchar(2000) DEFAULT NULL,
  `og_title` varchar(300) DEFAULT NULL,
  `og_description` varchar(500) DEFAULT NULL,
  `twitter_image` varchar(2000) DEFAULT NULL,
  `twitter_title` varchar(300) DEFAULT NULL,
  `twitter_description` varchar(500) DEFAULT NULL,
  `meta_title` varchar(2000) DEFAULT NULL,
  `meta_description` varchar(2000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `posts_meta_post_id_unique` (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
````

Even though `posts_meta.post_id` and  `posts.id` seem compatible from the first glance notice the difference in column definition charset:
```sql
`post_id` varchar(24) NOT NULL,

AND

`id` varchar(24) COLLATE utf8mb4_general_ci NOT NULL,

Similarly whole table definitions differ in charset:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci     

AND

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Both MySQL 8 and MariaDB 10 are not yet in recommended stack for Ghost installation. Think it's worth leaving further tackling of the issue to the community for now. @ErisDS let me know if you think otherwise.

naz picture naz on 16 Jun 2020

@naz thanks for the detailed write up, I think we can safely say this is not a migration bug as it originally appeared and we don't need to do anything further 👍

Summary of this issue:

Ghost does not yet officially support MySQL 8. This is because Node.js does not yet have support for MySQL 8.

There are two main points of incompatibility.

To the best of my knowledge neither of these things _actually_ with cause problems with Ghost.

What does cause problems, generally, is mixed collations. This is known to cause unique constraints to fail.

To repeat - MySQL 8 isn't supported in Ghost but probably works. MySQL 8 isn't the problem here, mixed collations is and that's a general administering mysql problem rather than a Ghost problem.

I don't know what happened here to end up with the different collations, but I assume mysql was upgraded or the content was transferred.

In MySQL 8 the default charset is utf8mb4 (what Ghost uses) & the default collation is utf8mb4_0900_ai_ci. utf8mb4_general_ci was the default in earlier versions of MySQL. I assume this is related.

Going to close this as figuring out how to fix it is definitely outside of scope here - although feel free to post in our community.

ErisDS picture ErisDS on 16 Jun 2020
👍1
Was this page helpful?
0 / 5 - 0 ratings

Related issues

pateskinasy picture pateskinasy  ·  3Comments
jliikala picture jliikala  ·  3Comments
ss4344 picture ss4344  ·  4Comments
HenryMarshall picture HenryMarshall  ·  4Comments
rishabhgrg picture rishabhgrg  ·  3Comments