> ## Content Index > Fetch the complete content index at: https://anantafatur.dev/llms.txt > Use this file to discover other available public pages before exploring further. # Upgrading Ghost and hitting MariaDB's charset trap - URL: https://anantafatur.dev/upgrading-ghost-and-hitting-mariadbs-charset-trap/ - Published: 2026-07-10T10:07:34.000Z - Updated: 2026-07-10T10:07:34.000Z - Description: Ghost forces `DEFAULT CHARACTER SET utf8mb4` on every CREATE TABLE, which makes MariaDB use `utf8mb4_general_ci` instead of `utf8mb4_unicode_ci`, breaking foreign keys on collation-mismatched columns. - Author: Ananta 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: ```sql 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: ```sql 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: ```javascript 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: ```javascript 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: ```sql 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: ```sql CREATE TABLE test_knex_sim (id varchar(24) NOT NULL) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4; ``` Result: ```sql 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: ```yaml database__connection__collate: utf8mb4_unicode_ci ``` This makes Knex generate: ```sql 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: ```sql 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. ![](https://anantafatur.dev/content/images/2026/07/zed-1.png) 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.