Corriger la collation MariaDB
Dans une base de données MariaDB, Relution attend pour toutes les tables et colonnes de texte le jeu de caractères utf8mb4 avec la collation utf8mb4_general_ci. Si certaines tables, certaines colonnes ou la collation par défaut de la base de données s’en écartent, certaines opérations s’interrompent avec des erreurs de base de données. Contrairement à l’ancien jeu de caractères utf8 (utf8mb3), utf8mb4 stocke également des caractères sur quatre octets, p. ex. des emojis.
Ce guide s’applique uniquement aux installations avec MariaDB. Les scripts ne peuvent pas être utilisés pour les bases de données PostgreSQL. Les bases de données prises en charge sont indiquées sous Exigences du système →.
Symptômes
Une collation divergente se remarque souvent d’abord avec l’Inscription automatisée des appareils (ADE, DEP dans le portail) : la synchronisation des appareils échoue pour toutes les organisations, tandis que le reste du fonctionnement se poursuit normalement. À chaque synchronisation, un message tel que le suivant apparaît dans le journal Relution :
Internal Exception: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
Error Code: 1267
Call: DELETE FROM `mdm_auto_enrllmnt_dflt_plcy` USING `mdm_auto_enrllmnt_dflt_plcy`, `TL_mdm_auto_enrllmnt` WHERE `mdm_auto_enrllmnt_dflt_plcy`.`auto_enrollment_uuid` = `TL_mdm_auto_enrllmnt`.`uuid`
Les collations indiquées peuvent varier selon l’installation, p. ex. utf8mb4_uca1400_ai_ci au lieu de utf8mb4_unicode_ci. La même erreur peut également survenir lors d’autres modifications en masse, p. ex. lors de la suppression d’un utilisateur. L’emplacement des fichiers journaux est décrit sous Relution Logs →.
Cause
Relution crée ses tables sans collation propre. Lors de leur création, elles reprennent la collation par défaut de la base de données. La connexion de Relution à la base de données utilise utf8mb4_general_ci.
Pour les modifications en masse, comme la suppression de plusieurs inscriptions d’appareils lors de la synchronisation ADE, Relution crée des tables auxiliaires temporaires avec le préfixe TL_, qui subsistent pendant toute la durée de la connexion à la base de données concernée. Ces tables reprennent elles aussi la collation par défaut de la base de données. Si celle-ci diffère de la collation des tables proprement dites, MariaDB ne peut pas comparer les colonnes entre elles et interrompt la requête. Les données enregistrées ne sont pas concernées.
Les divergences apparaissent généralement dans les cas suivants :
- La base de données a été créée sur un serveur MariaDB sans
collation-server=utf8mb4_general_ci. Sans configuration explicite, les versions récentes de MariaDB utilisent une autre collation par défaut, et les tables issues de mises à jour ultérieures de Relution reçoivent alors également cette collation. - Les tables ont été converties ultérieurement en
utf8mb4_general_ci, mais pas la collation par défaut de la base de données elle-même. - La base de données a été transférée par dump vers un nouveau serveur ou un nouveau conteneur. Le dump restaure les tables avec leur collation d’origine, mais la base de données nouvellement créée reçoit la valeur par défaut du nouveau serveur.
- La base de données provient d’une installation plus ancienne avec le jeu de caractères
utf8et n’a jamais été convertie.
Scripts
La vérification et la correction s’effectuent en deux étapes. Le script generate_mariadb_utf8mb4_conversion.sql ne modifie lui-même rien dans la base de données. Il vérifie la base de données, les tables et les colonnes et génère à partir du résultat le script convert_utf8mb4.sql, qui ne contient que des instructions pour les objets réellement divergents :
- Le jeu de caractères par défaut et la collation par défaut de la base de données sont alignés s’ils divergent.
- Les tables concernées sont converties en
utf8mb4avecutf8mb4_general_ciet, si nécessaire, enInnoDBavec le format de ligneDYNAMIC. - Les clés étrangères qui partent d’une table concernée ou qui y font référence sont supprimées avant la conversion, puis recréées à l’identique.
Les tables déjà correctes restent inchangées.
Les scripts sont conçus pour MariaDB 10.11 LTS et versions ultérieures. Le générateur n’intègre l’instruction SET SESSION character_set_collations que si la version de MariaDB la prend en charge (à partir de 11.2). Le générateur et le script généré sont donc exécutés sur le même serveur MariaDB.
Script générateur
Télécharger generate_mariadb_utf8mb4_conversion.sql →
generate_mariadb_utf8mb4_conversion.sql
SET @db = 'relution';
-- Base tables whose default charset/collation or any character column deviates from utf8mb4/utf8mb4_general_ci
WITH conv AS (
SELECT t.TABLE_NAME
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = @db AND t.TABLE_TYPE = 'BASE TABLE'
AND (t.TABLE_COLLATION <> 'utf8mb4_general_ci'
OR EXISTS (SELECT 1
FROM information_schema.COLUMNS c
WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND c.TABLE_NAME = t.TABLE_NAME
AND c.CHARACTER_SET_NAME IS NOT NULL
AND (c.CHARACTER_SET_NAME <> 'utf8mb4'
OR c.COLLATION_NAME <> 'utf8mb4_general_ci')))
),
-- Foreign keys where the referencing or the referenced table gets converted
fk AS (
SELECT rc.*
FROM information_schema.REFERENTIAL_CONSTRAINTS rc
WHERE rc.CONSTRAINT_SCHEMA = @db
AND (rc.TABLE_NAME IN (SELECT TABLE_NAME FROM conv)
OR (rc.UNIQUE_CONSTRAINT_SCHEMA = @db
AND rc.REFERENCED_TABLE_NAME IN (SELECT TABLE_NAME FROM conv)))
)
SELECT _sql FROM (
SELECT 1 AS step, '' AS tbl, '' AS obj, "SET SESSION innodb_strict_mode = OFF;" AS _sql
UNION ALL
-- Only on MariaDB 11.2+; older versions (e.g. 10.11 LTS) do not know this variable
SELECT 2, '', '', "SET SESSION character_set_collations = 'utf8mb4=utf8mb4_general_ci';"
FROM information_schema.SESSION_VARIABLES
WHERE VARIABLE_NAME = 'CHARACTER_SET_COLLATIONS'
UNION ALL
SELECT 3, '', '', "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';"
UNION ALL
SELECT 4, '', '', "SET foreign_key_checks = 0;"
UNION ALL
SELECT 5, '', '', CONCAT("ALTER DATABASE `", s.SCHEMA_NAME, "` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;")
FROM information_schema.SCHEMATA s
WHERE s.SCHEMA_NAME = @db
AND (s.DEFAULT_CHARACTER_SET_NAME <> 'utf8mb4' OR s.DEFAULT_COLLATION_NAME <> 'utf8mb4_general_ci')
UNION ALL
SELECT 6, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
CONCAT("ALTER TABLE `", fk.CONSTRAINT_SCHEMA, "`.`", fk.TABLE_NAME,
"` DROP FOREIGN KEY `", fk.CONSTRAINT_NAME, "`;")
FROM fk
UNION ALL
SELECT 7, t.TABLE_NAME, '',
CONCAT("ALTER TABLE `", t.TABLE_SCHEMA, "`.`", t.TABLE_NAME,
"` ENGINE=InnoDB ROW_FORMAT=DYNAMIC;")
FROM information_schema.TABLES t
JOIN conv ON conv.TABLE_NAME = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = @db
AND (t.ENGINE <> 'InnoDB' OR t.ROW_FORMAT <> 'Dynamic')
UNION ALL
SELECT 8, conv.TABLE_NAME, '',
CONCAT("ALTER TABLE `", @db, "`.`", conv.TABLE_NAME,
"` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;")
FROM conv
UNION ALL
SELECT 9, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
CONCAT("ALTER TABLE `", fk.CONSTRAINT_SCHEMA, "`.`", fk.TABLE_NAME,
"` ADD CONSTRAINT `", fk.CONSTRAINT_NAME, "` FOREIGN KEY (",
GROUP_CONCAT(CONCAT("`", k.COLUMN_NAME, "`") ORDER BY k.ORDINAL_POSITION SEPARATOR ", "),
") REFERENCES `", k.REFERENCED_TABLE_SCHEMA, "`.`", fk.REFERENCED_TABLE_NAME, "` (",
GROUP_CONCAT(CONCAT("`", k.REFERENCED_COLUMN_NAME, "`") ORDER BY k.ORDINAL_POSITION SEPARATOR ", "),
") ON DELETE ", fk.DELETE_RULE, " ON UPDATE ", fk.UPDATE_RULE, ";")
FROM fk
JOIN information_schema.KEY_COLUMN_USAGE k
ON k.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
AND k.TABLE_NAME = fk.TABLE_NAME
AND k.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
AND k.REFERENCED_TABLE_NAME IS NOT NULL
GROUP BY fk.CONSTRAINT_SCHEMA, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
k.REFERENCED_TABLE_SCHEMA, fk.REFERENCED_TABLE_NAME,
fk.DELETE_RULE, fk.UPDATE_RULE
UNION ALL
SELECT 10, '', '', "SET foreign_key_checks = 1;"
) AS script
ORDER BY step, tbl, obj;
Exemple de sortie sans anomalie
Si la base de données ne présente aucune divergence, le script généré convert_utf8mb4.sql se compose exclusivement d’instructions SET, comme dans cet exemple. Dans ce cas, il n’y a rien à corriger. Sur les versions de MariaDB antérieures à 11.2, la ligne contenant character_set_collations est absente.
SET SESSION innodb_strict_mode = OFF;
SET SESSION character_set_collations = 'utf8mb4=utf8mb4_general_ci';
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';
SET foreign_key_checks = 0;
SET foreign_key_checks = 1;
Procédure
Étapes requises :
- Le service Relution est arrêté.
- Une sauvegarde de la base de données est créée, voir aussi Reprise après sinistre →.
- Le script générateur est exécuté sur la base de données Relution, et la sortie est écrite dans le fichier
convert_utf8mb4.sql. - Le fichier
convert_utf8mb4.sqlest vérifié. S’il ne contient, comme l’exemple de sortie, que des instructionsSET, il n’y a rien à corriger et l’étape 5 est omise. - Le fichier
convert_utf8mb4.sqlest exécuté sur la base de données Relution. - Le service Relution est démarré.
Le redémarrage est impératif. Les tables auxiliaires temporaires subsistent chacune pendant la durée d’une connexion à la base de données ; sinon, les connexions déjà ouvertes continueraient d’utiliser la table auxiliaire créée avec la mauvaise collation. Sur les bases de données volumineuses, l’étape 5 peut prendre un certain temps, car MariaDB réécrit chaque table concernée.
Dans les commandes suivantes, les espaces réservés entre chevrons sont remplacés par les valeurs du fichier application.yml ou du fichier Compose (compose.yaml, docker-compose.yml pour les installations plus anciennes). Les commandes et le script générateur partent du nom de base de données relution. Si un autre nom de base de données a été choisi lors de l’installation, il est remplacé à la ligne 1 du script générateur (SET @db = 'relution';) et dans les commandes. Le nom figure à la fin de l’URL de la base de données dans application.yml, p. ex. relution pour jdbc:mariadb://localhost/relution?useServerPrepStmts=true.
Les options -N, -B et -r font en sorte que la sortie soit écrite sans en-tête de colonne, sans bordures de tableau et sans échappement, et qu’elle soit ainsi directement exécutable comme script.
Docker
Les commandes sont exécutées dans le répertoire /opt/relution/, dans lequel le script générateur est également enregistré.
docker compose stop relution
docker exec <containername> mariadb-dump -u <username> -p<password> --single-transaction relution > relution_backup.sql
docker exec -i <containername> mariadb -u <username> -p<password> -N -B -r relution < generate_mariadb_utf8mb4_conversion.sql > convert_utf8mb4.sql
cat convert_utf8mb4.sql
docker exec -i <containername> mariadb -u <username> -p<password> relution < convert_utf8mb4.sql
docker compose start relution
Le mot de passe est indiqué ici directement après -p, car les scripts sont transmis via l’entrée standard et une demande interactive du mot de passe n’est alors pas possible.
Natif
Sous Linux :
sudo systemctl stop relution
mariadb-dump -u <username> -p --single-transaction relution > relution_backup.sql
mariadb -u <username> -p -N -B -r relution < generate_mariadb_utf8mb4_conversion.sql > convert_utf8mb4.sql
cat convert_utf8mb4.sql
mariadb -u <username> -p relution < convert_utf8mb4.sql
sudo systemctl start relution
Sous Windows, les mêmes appels mariadb sont exécutés dans l’invite de commandes, p. ex. via le raccourci Command Prompt (MariaDB 10.11 (x64)). Dans PowerShell, la redirection avec < ne fonctionne pas. Le contenu du fichier généré s’y affiche avec type convert_utf8mb4.sql.
Vérifier le résultat
Après la conversion, le script générateur est exécuté à nouveau comme à l’étape 3. Si la nouvelle sortie ne contient plus que les instructions SET de l’exemple de sortie, la base de données et les tables sont entièrement en utf8mb4 avec utf8mb4_general_ci. Le script générateur n’effectue aucune modification et peut être exécuté à nouveau à tout moment.
Prévention
Pour que les bases de données et tables nouvellement créées reçoivent directement la bonne collation, le serveur MariaDB est exploité avec les paramètres suivants dans la section [mysqld] :
[mysqld]
collation-server=utf8mb4_general_ci
character-set-server=utf8mb4
character-set-collations=utf8mb4=utf8mb4_general_ci
À partir de MariaDB 11.5, la ligne character-set-collations est obligatoire. Sans elle, MariaDB utilise pour les connexions la collation utf8mb4_uca1400_ai_ci, même si la base de données et les tables sont en utf8mb4_general_ci. L’option existe à partir de MariaDB 11.2. Les versions plus anciennes comme MariaDB 10.11 LTS ne la connaissent pas et ne démarrent pas avec cette ligne ; elle y est omise.
Les paramètres actuels sont vérifiés dans la console MariaDB. La base de données Relution est d’abord sélectionnée :
USE relution;
SHOW VARIABLES LIKE '%collation%';
Les valeurs de collation_server et collation_database sont alors utf8mb4_general_ci. Sans USE, aucune base de données n’est sélectionnée (reconnaissable à l’invite MariaDB [(none)]), et collation_database n’affiche que la valeur par défaut du serveur au lieu du paramètre de la base de données Relution. Si collation_connection est sur utf8mb4_uca1400_ai_ci à partir de MariaDB 11.5, la ligne character-set-collations manque dans la configuration. La configuration complète est décrite dans l’article Obsolète : Linux (Alma) →.