Unable to start ghost in docker with mysql on digitalocean

Developer help Installation
1

I am using the image ghost:3.35.5-alpine with the database client mysql. I am using mysql database from digitalocean and I getting an error which preventing the container from starting.

[2020-10-18 21:19:29] ERROR create table `migrations_lock` (`lock_key` varchar(191) not null, `locked` boolean default '0', `acquired_at` datetime null, `released_at` datetime null) default character set utf8mb4 - UNKNOWN_CODE_PLEASE_REPORT: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
create table `migrations_lock` (`lock_key` varchar(191) not null, `locked` boolean default '0', `acquired_at` datetime null, `released_at` datetime null) default character set utf8mb4 - UNKNOWN_CODE_PLEASE_REPORT: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
Error ID:
    9ba4fb70-1187-11eb-a9fc-a7a667559064
Error Code: 
    UNKNOWN_CODE_PLEASE_REPORT
----------------------------------------
InternalServerError: create table `migrations_lock` (`lock_key` varchar(191) not null, `locked` boolean default '0', `acquired_at` datetime null, `released_at` datetime null) default character set utf8mb4 - UNKNOWN_CODE_PLEASE_REPORT: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
    at new GhostError (/var/lib/ghost/versions/3.20.2/node_modules/@tryghost/errors/lib/errors.js:10:26)
    at /var/lib/ghost/versions/3.20.2/core/index.js:16:23
Error: UNKNOWN_CODE_PLEASE_REPORT: Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
    at Query.Sequence._packetToError (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:295:12)
    at readableAddChunk (_stream_readable.js:271:9)
    at Socket.Readable.push (_stream_readable.js:212:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)
    --------------------
    at Protocol._enqueue (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/var/lib/ghost/versions/3.20.2/node_modules/mysql/lib/Connection.js:198:25)
    at /var/lib/ghost/versions/3.20.2/node_modules/knex/lib/dialects/mysql/index.js:135:18
    at Promise._execute (/var/lib/ghost/versions/3.20.2/node_modules/bluebird/js/release/debuggability.js:384:9)
    at Promise._resolveFromExecutor (/var/lib/ghost/versions/3.20.2/node_modules/bluebird/js/release/promise.js:518:18)
    at new Promise (/var/lib/ghost/versions/3.20.2/node_modules/bluebird/js/release/promise.js:103:10)
    at Client_MySQL._query (/var/lib/ghost/versions/3.20.2/node_modules/knex/lib/dialects/mysql/index.js:129:12)
    at Client_MySQL.query (/var/lib/ghost/versions/3.20.2/node_modules/knex/lib/client.js:169:17)
    at Runner.query (/var/lib/ghost/versions/3.20.2/node_modules/knex/lib/runner.js:134:36)
    at Runner.queryArray (/var/lib/ghost/versions/3.20.2/node_modules/knex/lib/runner.js:231:31)
    at /var/lib/ghost/versions/3.20.2/node_modules/knex/lib/runner.js:38:25
    at /var/lib/ghost/versions/3.20.2/node_modules/knex/lib/runner.js:260:24

Digitalocean only allows setting the sql_require_primary_key value to OFF per session. I tried creating the table manually, but then I get an error saying ā€œan existing migration is in progressā€ even though the table is empty.

Technical details:

  • Ghost Version: 3.35.5
  • Database: MySQL v8
2

Hey @chekkan! This error is because not all of our tables have a primary key right now, so DigitalOceanā€™s managed DB isnā€™t supported yet (PKs on all tables are required on their platform).

It may be possible to work around, but it wouldnā€™t be supported and may cause issues with future upgrades.

3

Thanks for the explanation. Out of curiosity, what would be the work around?

1 Like
4

@matthanley any timeline on this? weā€™re looking to move to DOā€™s managed DB

5

@chekkan and @maxkoretskyi I was able to get around this issue. I unfortunately wasnā€™t able to find where to modify the migrations themselves so I created a sql export from my sqllite db and then imported that into my DO mysql instance using this command on the mysql client source './example.sql' (launched from my ghost project dir).

I did have to modify a few of the statements based on errors I got but once I got the sql imported I was able to run ghost start.

Hopefully this is useful to yā€™all.

1 Like
6

Managed MySQL requires a primary key on every table for clustering purposes for MySQL in DO.

But most of the tables in ghost migrations have the primary keys but not all.

Here is the workaround if you are want to use DOā€™s Managed Mysql.

  1. When you ā€˜ghost startā€™, the knex-migrator starts the process of migrating the table.

    First table it creates is ā€˜migrations_lockā€™,

    Note: Iā€™m using ghost-cli to install ghost

    From your ghost directory, Modify the ā€œcurrent/node_modules/knex-migrator/migrations/lock-table.jsā€

    change the primary line to below:

Screenshot 2021-10-17 at 12.02.45 PM
Screenshot 2021-10-17 at 12.02.45 PM1180Ɨ380 91 KB 

I know changing code in node_modules is not good practice, but I'm desperate to use DO DB.
  1. After that, knex-migrator start migrating ghost tables, most of the ghost tables have primary key, first it creates tables and adds primary key later, which is not support in digital ocean managed DB.

So, from your ghost directory go to ā€œcurrent/core/server/data/schema/commands.jsā€, you can find createTable function, change that to the below,(Check the COMMENTS):

function createTable(table, transaction, tableSpec = schema[table]) {
    return (transaction || db.knex).schema.hasTable(table)
        .then(async function (exists) {.       // ADD ASYNC KEYWORD HERE
            if (exists) {
                return;
            }
		
	   await (transaction || db.knex).raw('SET sql_require_primary_key=0');   // ADD THIS LINE

            return (transaction || db.knex).schema.createTable(table, function (t) {
                Object.keys(tableSpec)
                    .filter(column => !(column.startsWith('@@')))
                    .forEach(column => addTableColumn(table, t, column, tableSpec[column]));

                if (tableSpec['@@INDEXES@@']) {
                    tableSpec['@@INDEXES@@'].forEach(index => t.index(index));
                }
                if (tableSpec['@@UNIQUE_CONSTRAINTS@@']) {
                    tableSpec['@@UNIQUE_CONSTRAINTS@@'].forEach(unique => t.unique(unique));
                }
            });
        });
}

Now, Perform ā€˜ghost installā€™.

It should go smooth. I hope this is helpful.