Upgrading Ghost and hitting MariaDB's charset trap

I wanted to upgrade my Ghost blog from 6.26.0 to 6.49.0. Should be straightforward, right? Change the image tag, restart, watch migrations roll.

Here's what I got instead:

[2026-07-10 09:36:13] ERROR alter table `gifts` add constraint `gifts_buyer_member_id_foreign`
foreign key (`buyer_member_id`) references `members` (`id`) on delete SET NULL -
Can't create table `ghost`.`gifts` (errno: 150
"Foreign key constraint is incorrectly formed")

Ghost created a backup, tried to run migrations, and then immediately crashed. Then it restarted, created another backup, crashed again. Classic restart loop.

Environment

• Ghost: 6.26.0 to 6.49.0 (Docker)
• MariaDB: 10.6.24 (also Docker)
• Behind a reverse proxy.
• Database connection charset forced to utf8mb4 via env var (more on that later)

First thought: what's actually wrong?

Errno 150 with "Foreign key constraint is incorrectly formed" usually means one of two things: the column types don't match, or the charsets and collations don't match. The migration was trying to create a new gifts table with a foreign key pointing to members.id. Both columns should be varchar(24). Seemed simple enough.

I checked the members table:

CREATE TABLE `members` (
  `id` varchar(24) NOT NULL,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

varchar(24), utf8mb4_unicode_ci. Fine.

I checked the MariaDB defaults:

DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_unicode_ci

Also fine. So I did the obvious thing and tried creating the table manually:

CREATE TABLE gifts (
  id varchar(24) NOT NULL,
  buyer_member_id varchar(24) NULL,
  FOREIGN KEY (buyer_member_id) REFERENCES members(id) ON DELETE SET NULL
) ENGINE=InnoDB;

It worked. No error. So what was Ghost doing differently?

Following the breadcrumbs

Ghost's migration uses Knex 2.4.2 to create tables. The addTable utility calls commands.createTable, which generates SQL through Knex's MySQL table compiler. I pulled the source from Ghost's image to see what's happening.

The key code in Knex's MySQL table compiler:

const charset = this.single.charset || conn.charset || '';
const collation = this.single.collate || conn.collate || '';
const engine = this.single.engine || '';

if (charset && !like) sql += ` default character set ${charset}`;
if (collation) sql += ` collate ${collation}`;

So Knex reads conn.charset from the connection settings and adds DEFAULT CHARACTER SET utf8mb4 to the CREATE TABLE. I checked where that comes from in Ghost's database initialization:

if (client === 'mysql2') {
    dbConfig.connection.timezone = 'Z';
    dbConfig.connection.charset = 'utf8mb4';
    dbConfig.connection.decimalNumbers = true;
}

The database__connection__charset line I had in my compose file was redundant. Ghost hardcodes it anyway when it detects the mysql2 client.

The actual problem

Here's the catch. MariaDB 10.6's default collation for the utf8mb4 character set is utf8mb4_general_ci, not utf8mb4_unicode_ci. These are different.

When Knex generated:

CREATE TABLE `gifts` (...) DEFAULT CHARACTER SET utf8mb4 ENGINE = InnoDB

MariaDB interpreted that as "use utf8mb4 charset with its default collation," which is utf8mb4_general_ci. But the members table was explicitly created with COLLATE=utf8mb4_unicode_ci (from the database default that existed before Knex started overriding things).

I confirmed this by creating a test table with just the charset and no collation:

CREATE TABLE test_knex_sim (id varchar(24) NOT NULL)
  ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

Result:

CREATE TABLE `test_knex_sim` (...) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

There it is. utf8mb4_general_ci, not utf8mb4_unicode_ci. MariaDB rejects the foreign key because you can't reference a utf8mb4_unicode_ci column from a utf8mb4_general_ci column.

The fix

I needed Knex to also emit an explicit COLLATE clause in the CREATE TABLE, so the new table would match the existing tables. Knex's table compiler looks at conn.collate from the connection settings. Ghost's nconf-based config loader maps env vars to nested config keys, so I added this to the compose file:

database__connection__collate: utf8mb4_unicode_ci

This makes Knex generate:

CREATE TABLE `gifts` (...)
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci
  ENGINE = InnoDB

Which finally matches what the members table has.

But there was one more problem. The email_design_settings table (created during a previous upgrade) also had the wrong collation. Only one table in the whole database:

TABLE_NAME             TABLE_COLLATION
email_design_settings  utf8mb4_general_ci

So I converted it too:

ALTER TABLE email_design_settings
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After restarting, Ghost ran all the migrations from 6.27 through 6.49 successfully. The latest entry in the migrations table clocks in at version 6.48.

The warning that bugs me

There's a benign warning that shows up on every database query now:

Ignoring invalid configuration option passed to Connection: collate.
This is currently a warning, but in future versions of MySQL2,
an error will be thrown if you pass an invalid configuration option
to a Connection

The collate option is used by Knex's table compiler but mysql2's connection doesn't recognize it. It's harmless for now and Knex still reads it correctly from connectionSettings, but a future mysql2 version might reject the connection entirely. I'll probably need to revisit this if I upgrade the npm packages.

Honestly, the cleaner fix would be to patch this at the Ghost config level, but I'm not about to fork Ghost's container image for a collation issue. So this tech debt lives on my compose file until either Knex or Ghost adds official collation support in their database config. Hopefully not me who fixes this later.

What I learned

The root cause was a mismatch between how MariaDB resolves default collations and how Knex generates CREATE TABLE statements. When you specify DEFAULT CHARACTER SET utf8mb4 without an explicit collation, MariaDB uses the charset's hardcoded default collation (utf8mb4_general_ci), not whatever your server or database defaults are. This is a subtle trap because the database default collation is utf8mb4_unicode_ci, so most operations behave as if that's the system-wide setting. But Knex's explicit charset override short-circuits that inheritance.

If I were designing this from scratch, I'd either never set a connection-level charset in the first place (let the database defaults flow through), or always pair it with an explicit collation. Ghost does the first half (forces charset) but misses the second half.

Colophon

This whole thing, from root cause analysis to fix to this write-up, was done by Zed's AI agent using the deepseek-v4-flash model. I just described the problem and reviewed the results.

The agent dug through Knex source code inside the Docker image, queried the running MariaDB, ran test queries, traced Ghost's config loader, and wrote this post. Pretty wild.