|
| 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 data migration, for MySQL and MariaDB only, which converts each table of the Hibernate mapping whose default collation or one of its columns does not match the collation configured on the database ({{CONVERT TO CHARACTER SET ... COLLATE ...}}):
* the conversion is done before the Hibernate schema update, after dropping the foreign keys involving string columns of the tables to convert (MySQL and MariaDB refuse to convert a column used in a foreign key) , so that the schema update re-creates them
* the tables linked 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 foreign key are converted together and {{VARCHAR(768)}} column plus a failure is fatal, since date) are dropped before the foreign key could not conversion, with a warning giving their definition so that they can be re-created between columns with different collations
* a failure on any other table shorter prefix ( e.g. a custom index except too long for the new charset unique indexes on MariaDB, which supports them ) does not prevent
* any failure of the conversion prevents XWiki from starting : , so that the table tables are guaranteed to use the database collation once the migration is left done
The logic is also exposed as it was and a warning lists reusable API on the tables MySQL Hibernate adapter, which could not can be converted used 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).
|
|
