Ghost Upgrade 4.3 Pain

Remember:

Make a backup every time before upgrading

Was writing a blog post and saw that a critical security feature was released for ghost this blog engine and found that my collation again is blowing up the migrations.

Ghost blog people fix this shit by establishing a script to do a default collation check and synchronization!

Poking around in the knex.js migration scripts and I find the failing script which is to create a table

const {addTable} = require('../../utils');

module.exports = addTable('members_products', {
    id: {type: 'string', maxlength: 24, nullable: false, primary: true},
    member_id: {type: 'string', maxlength: 24, nullable: false, references: 'members.id', cascadeDelete: true},
    product_id: {type: 'string', maxlength: 24, nullable: false, references: 'products.id', cascadeDelete: true},
    sort_order: {type: 'integer', nullable: false, unsigned: true, defaultTo: 0}
});

Ok time to translate this back into sql and then run it manually, found a pretty good translation here.  Thanks John Hartley

The sql comes out to this

create table members_products (id varchar(24) not null primary key, member_id varchar(24) not null references members(id) on delete cascade, product_id varchar(24) not null references products(id) on delete cascade, sort_order integer unsigned not null default 0);

Run this at my MySql login and then bam table is created under proper collation and then ghost restart runs through and adds the rest of the tables etc.  Yay!!

Ok but check the collations again SHOW TABLE STATUS FROM ghost_production; and goddamnit it has made the collations with utf8mb4_0900_ai_ci instead of my default utf8mb4_general_ci.  Ok time to fix the collations on those new tables.

mysql> ALTER TABLE products CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE stripe_prices CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE stripe_products CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Ok now all the tables are aligned to my utf8mb4_general_ci

Fuck you ghost + mysql