Skip to content

Add installer support for avoiding MySQL utf8mb4_0900_ai_ci default collation #598

Description

@Toutouwai

If you install PW with MySQL 8.0+ then all tables default to utf8mb4_0900_ai_ci collation. This collation does not have wide support and causes issues if the database needs to be migrated to MariaDB. A utf8mb4_unicode_ci collation would be preferable.

Gemini says:

utf8mb4_0900_ai_ci is exclusively supported natively in MySQL 8.0+ and is not widely compatible across older legacy servers or MariaDB setups.
If you attempt to import a dump containing utf8mb4_0900_ai_ci into older MySQL engines (5.7 or lower) or standard MariaDB environments, you will trigger the common error: Error 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'.

MariaDB & utf8mb4_0900_ai_ci Support
Standard MariaDB (10.x and early 11.x): Does not support utf8mb4_0900_ai_ci. MariaDB chose to skip Unicode 9.0 collations and eventually moved toward Unicode 14.0 (uca1400_...) instead.
Very Recent MariaDB (11.4.5+): MariaDB added select alias support for utf8mb4_0900_ai_ci specifically to simplify replication and imports from MySQL 8.0. However, relying on this requires both environments to run bleeding-edge versions, making it risky for general cross-platform portability.

Gemini recommended setting

$config->dbEngine = 'InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';

or

$config->dbInitCommand = "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'";

but this is not obvious or easy to do via the PW installer.

Can we make it easier to achieve a widely supported utf8mb4_unicode_ci collation on install?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions