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 utf8 et 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 utf8mb4 avec utf8mb4_general_ci et, si nécessaire, en InnoDB avec le format de ligne DYNAMIC.
  • 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.

Si ce guide est consulté sous forme de fichier PDF, les guillemets peuvent avoir été remplacés par leurs versions typographiques (courbes). La syntaxe du script devient alors incorrecte. Pour les scripts, les liens de téléchargement ou la version HTML de ce document sont donc utilisés.

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

Les modifications du script ne peuvent pas être annulées par un rollback. Une sauvegarde complète est créée avant toute intervention sur la base de données.

Étapes requises :

  1. Le service Relution est arrêté.
  2. Une sauvegarde de la base de données est créée, voir aussi Reprise après sinistre →.
  3. 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.
  4. Le fichier convert_utf8mb4.sql est vérifié. S’il ne contient, comme l’exemple de sortie, que des instructions SET, il n’y a rien à corriger et l’étape 5 est omise.
  5. Le fichier convert_utf8mb4.sql est exécuté sur la base de données Relution.
  6. 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.

Pour éviter que le mot de passe soit enregistré en clair dans l’historique du shell, la commande peut être précédée d’un espace, ou l’historique peut être nettoyé ensuite.

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) →.

Cet article a-t-il été utile ?