Unable to start ghost in docker with mysql on digitalocean

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

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.

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

1 Like

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

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