|
| Description: |
h2. Problem
On MySQL and MariaDB, a table takes the default character set and 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}}). A database can also be configured with a non binary collation (e.g. {{utf8mb4_0900_ai_ci}}, the MySQL 8 default), while XWiki requires a binary one.
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
On MySQL and MariaDB, check at each startup (when the data migration system is enabled, for each wiki, right before the Hibernate schema update) that each table of the Hibernate mapping uses the character set configured on the database with a binary collation, and convert the ones whose default collation or one of its columns does not match ({{CONVERT TO CHARACTER SET ... COLLATE ...}}). The target collation is the one configured on the database when it's binary (e.g. {{utf8mb4_bin}}), and the binary collation of the database character set otherwise (e.g. {{utf8mb4_bin}} for a database configured with {{utf8mb4_0900_ai_ci}}). 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 few {{INFORMATION_SCHEMA}} queries per wiki.
* 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), and re-created right after the conversion, even when it fails, without the foreign key checks so that the tables are not rebuilt a second time. The definition of each of them is logged before dropping it, and the Hibernate schema update, which runs right after, re-creates the ones of the mapping which could not be re-created
* 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)
* a table using a legacy row format ({{COMPACT}} or {{REDUNDANT}}, which limit the index keys to 767 bytes) or not the expected one (e.g. {{COMPRESSED}} for the compressed entities) is switched to the expected row format in the same statement, so that it's rebuilt only once
* any failure of the conversion prevents XWiki from starting, so that the tables are guaranteed to use the expected collation. Since what needs to be converted is recomputed from the current state of the database, a restart resumes the conversion
Only the conversions which cannot lose anything are done:
* the tables of the custom mappings of the XWiki classes ({{xwikicustom_*}}) are not converted, since their structure is controlled by the users
* a table which can contain characters not supported by the database character set (e.g. a {{utf8mb4}} table in a {{utf8mb3}} database) is not converted, and neither are the tables linked to it by a foreign key on string columns, since that foreign key could not be re-created. A warning is logged when such a table uses a non binary collation
* a table which does not use the InnoDB storage engine is not converted, with a warning, since the maximum key length and the row formats taken into account are the InnoDB ones
A collation deliberately set on a specific table or column of the mapping is reverted to the target one. The conversion at startup can be disabled by setting {{store.hibernate.mysql. fixTableCollation fixTableEncoding }} to {{false}} in {{xwiki.properties}}.
The conversion is also exposed as a reusable API on the MySQL Hibernate adapter, which can be called by a script with programming right. |
|
