|
| 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 the database and each table of the Hibernate mapping uses use the expected 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
* the expected character set is the one configured on the database when it's {{utf8mb4}} or {{utf8mb3}}, and {{utf8mb4}} otherwise (e.g. {{latin1}}, which cannot hold most of the translations of the standard flavor). When it's {{utf8mb3}} but a standard table can contain characters {{utf8mb3}} does not support (e.g. a {{utf8mb4}} table), the database and all its tables are converted to {{utf8mb4}} instead, since having some standard tables in {{utf8mb3}} and others in {{utf8mb4}} does not make sense (and nothing can be lost that way)
* the expected collation is the one configured on the database when it's a binary collation of the expected character set (e.g. {{utf8mb4_bin}}), and the binary collation of the database expected character set otherwise (e.g. {{utf8mb4_bin}} for a database configured with {{utf8mb4_0900_ai_ci}}) . In that last case the
The default character set and collation of the database is are changed too when they don't match , so that the tables created later (starting with the ones created by the schema update right after, which covers fresh installs and new subwikis) use the expected collation. 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. Since the foreign keys are not checked, a row which only matched its referenced row case insensitively before the conversion silently stops matching it
* 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). The foreign keys which need one of those indexes are dropped and re-created too
* 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 Some tables are done not converted :
* the tables of the custom mappings of the XWiki classes ({{xwikicustom_*}}) and the tables which are not part of the Hibernate mapping are not converted, since their structure is controlled by the users, unless they are linked by a foreign key on string columns to a table to convert (that foreign key could not be re-created otherwise)
* a table linked by a foreign key on string columns to a table of another database is not converted, with a warning, and neither are the tables linked to it
* 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. Going from a NO PAD collation (e.g. {{utf8mb4_0900_ai_ci}}) to a PAD SPACE one (e.g. {{utf8mb4_bin}}) makes the unique keys which only differ by trailing spaces collide: that case is not supported (the conversion fails and the data has to be fixed manually). The conversion at startup can be disabled by setting {{store.hibernate.mysql.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. |
|
