|
| Description: |
h2. Problem
On MySQL and MariaDB, a table takes the default collation of its database at the moment it is created (Hibernate does not set any). An instance which went through upgrades, dump/restore cycles or extension installs often ends up with a mix of collations: most tables use the collation configured on the database (e.g. {{utf8mb4_bin}}) but a few still use an older one (e.g. {{utf8_general_ci}}, {{utf8mb4_general_ci}}).
Such tables cause subtle problems: case-insensitive collisions between references which differ only in case, characters that cannot be stored when the table is still on a 3-byte charset, joins and foreign keys between columns with different collations, etc. Fixing it by hand is not trivial, since MariaDB refuses to convert a column used in a foreign key, even with {{FOREIGN_KEY_CHECKS=0}}.
h2. Proposal
Add a Hibernate On MySQL and MariaDB, check at each startup (when the data migration system is enabled , for MySQL and MariaDB only each wiki , which converts right before the Hibernate schema update) that each table of the Hibernate mapping uses the collation configured on the database, and convert the ones whose default collation or one of its columns does not match the collation configured on the database it ({{CONVERT TO CHARACTER SET ... COLLATE ...}}) : . Being a systematic check rather than a one-time migration, it also fixes tables which get out of sync later (dump/restore, tables created by extensions, etc.). When nothing needs to be converted, the check costs a couple of {{INFORMATION_SCHEMA}} queries per wiki.
* the conversion is done before the Hibernate schema update, after dropping the foreign keys involving string columns of the tables to convert are dropped first (MySQL and MariaDB refuse to convert a column used in a foreign key), so that and re-created by the Hibernate schema update re-creates them which runs right after
* the indexes which would exceed the maximum key length with the new character set (typically custom indexes added by an administrator, such as an index on a {{VARCHAR(768)}} column plus a date) are dropped before the conversion, with a warning giving their definition so that they can be re-created with a shorter prefix (except too long unique indexes on MariaDB, which supports them)
* any failure of the conversion prevents XWiki from starting, so that the tables are guaranteed to use the database collation once
This makes the migration database default collation the single source of truth: a collation deliberately set on a specific table or column of the mapping is done reverted to it.
The logic conversion is also exposed as a reusable API on the MySQL Hibernate adapter, which can be used called by a later migration or by a script with programming right (in which case XWiki must be restarted to re-create the dropped foreign keys).
|
|
