Unable to start ghost in docker with mysql on digitalocean

chekkan · October 19, 2020, 6:55pm

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

matthanley · October 22, 2020, 12:13pm

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.

chekkan · October 22, 2020, 7:20pm

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

maxkoretskyi · October 27, 2020, 1:39pm

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

jasperboyd · November 13, 2020, 12:49am

@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.

nikhilbharadwaj005 · October 17, 2021, 6:49am

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.

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:

I know changing code in node_modules is not good practice, but I'm desperate to use DO DB.

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.

Topic		Replies	Views
DigitalOcean Database Connection Installation	4	1103	March 3, 2021
Ghost Database issue with DigitalOcean Database Installation	0	644	January 26, 2021
Error after update to 4.35.0 Self-hosting	6	2105	May 23, 2022
Broken DB after Ghost upgraded to 4.4.0 - Docker Developer help	1	717	December 18, 2021
Strange Error with Ghost Docker Developer help	4	872	September 11, 2021

Unable to start ghost in docker with mysql on digitalocean

Technical details:

Related topics