Make sure all MySQL/MariaDB tables use the collation configured on the database

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.

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 collation configured on the database, and convert the ones whose default collation or one of its columns does not match 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 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 by the Hibernate schema update 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

This makes the database default collation the single source of truth: a collation deliberately set on a specific table or column of the mapping is reverted to it.

The conversion is also exposed as a reusable API on the MySQL Hibernate adapter, which can be called by a script with programming right (in which case XWiki must be restarted to re-create the dropped foreign keys).