Default COLLATION setting on DB Migrations

[ichdasich]

Issue Summary

I just stumbled into the COLLATION based migration issue discussed in #27652 ; However, while debugging this, I stumbled over a potential documentation issue and/or feature request.

For the issue explanation, please see "Steps to Reproduce".

Suggestions:

• It would be good to better document this issue. Around Can't upgrade 6.26 to 6.27 or later because of a migration foreign key fail #27652 there is a lot of confusion by people on why what happens happens to them. The collation issue (and debugging steps, i.e., rechecking the collation Ghost creates tables with) are a bit unclear.
• Setting the default collation explicitly when creating tables in migrations, ideally from a configuration setting/environment variable that can be handed to Ghost/the Ghost container. That way, as an operator, I at least have explicit control over collation.

Steps to Reproduce

1. The database was configured with a default collation of utf8mb4_general_ci from IaC
2. Ghost got upgraded to 6.37.0, and ER_FK_INCOMPATIBLE_COLUMNS on migration 2026-04-06-07-48-06-add-gifts-table.js` in Ghost 6.27.0 #27263 occurred during the DB migration
3. I followed the recommendations in ER_FK_INCOMPATIBLE_COLUMNS on migration 2026-04-06-07-48-06-add-gifts-table.js` in Ghost 6.27.0 #27263 , but this did not lead to the issue disappearing
4. The migration steps in https://docs.ghost.org/faq/supported-databases#how-to-update-mysql-5-to-mysql-8 were in so far ineffective, that the ALTER TABLE commands could not be executed on all tables, as even disabling foreign key checks did not allow changing the collation on all tables
5. I dumped the DB, adjusted collation to the recommended utf8mb4_0900_ai_ci and re-imported. The issue persisted
6. I verified the collation new tables are created with, which turned out to be utf8mb4_uca1400_ai_ci; I hence set in [mysqld] in my.cnf:

init_connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;'
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

7. This did not have the desired effect, the issue persisted and the tables were still created with utf8mb4_uca1400_ai_ci:

character-set-server = utf8mb4
collation-server = utf8mb4_uca1400_ai_ci
init_connect = 'SET NAMES utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;'

9. I dumped the DB again, imported it with utf8mb4_uca1400_ai_ci set instead, changed the default collation of the server etc. to that as well; The issue was resolved.
   The core issue here seems to be that the migrations do not specify a COLLATION on table create, falling back to defaults. I did not find an option to force one via, e.g., a config option.

The behavior of the mysqld/docker client is somewhat tangential to the issue but related (I saw the init_connect being executed in the query log; No clue why this did not work. Same with a manual connection. Running the command there manual again hat the desired effect.)

Ghost Version

6.37.0

Node.js Version

docker container version

How did you install Ghost?

Docker

Database type

Other

Browser & OS version

Relevant log / error output

Code of Conduct

• I agree to be friendly and polite to people in this repository