Relution Signet Customer Hub

Illegal mix of collations

Sur les installations utilisant MariaDB, certaines opérations échouent avec l’erreur de base de données 1267. Cela 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. Le signe distinctif est une table avec le préfixe TL_ dans la requête. L’emplacement des fichiers journaux est décrit sous Relution Logs →.

Cause

Relution attend la collation utf8mb4_general_ci pour toutes les colonnes de texte. Les tables de la base de données sont créées en conséquence, et la connexion de Relution à la base de données utilise également cette collation.

Pour les opérations de masse, comme la suppression de plusieurs inscriptions d’appareils lors de la synchronisation ADE, Relution crée brièvement des tables auxiliaires temporaires avec le préfixe TL_. Aucune collation n’est spécifiée pour ces tables, elles héritent donc de la collation par défaut de la base de données. Si cette collation par défaut diffère de utf8mb4_general_ci, MariaDB ne peut pas comparer les colonnes de la table auxiliaire avec celles des tables réelles et interrompt la requête. Les données enregistrées ne sont pas affectées.

Une collation par défaut divergente apparaît généralement dans les cas suivants :

  • La base de données a été créée sur un serveur MariaDB dont le paramètre collation-server n’est pas défini sur utf8mb4_general_ci. Les versions récentes de MariaDB utilisent une autre collation par défaut en l’absence de configuration explicite.
  • La base de données a été transférée vers un nouveau serveur ou un nouveau conteneur au moyen d’un dump. 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.

Solution

La collation par défaut de la base de données est alignée sur celle des tables Relution. Seule la valeur par défaut pour les tables nouvellement créées est modifiée ; les tables et données existantes restent inchangées.

Étapes nécessaires :

  1. Le service Relution est arrêté.
  2. Une sauvegarde de la base de données est créée.
  3. Le script ci-dessous est enregistré sous repair_temp_table_collation_mariadb.sql et exécuté sur la base de données Relution.
  4. La sortie du script est vérifiée (voir ci-dessous).
  5. Le service Relution est démarré.

Le redémarrage est indispensable. Les tables auxiliaires temporaires existent pendant toute la durée d’une connexion à la base de données. Sinon, les connexions déjà ouvertes continuent d’utiliser la table auxiliaire créée avec la mauvaise collation.

Script

Le script détermine la collation des tables Relution, liste les colonnes divergentes et définit la collation par défaut de la base de données. Il peut être exécuté plusieurs fois sans risque.

repair_temp_table_collation_mariadb.sql
-- Corrige "Illegal mix of collations" (erreur 1267) sur les tables temporaires TL_* sous MariaDB.
-- Définit uniquement la collation par défaut de la base, les tables et données existantes restent inchangées.
-- Relution doit ensuite être redémarré.

-- 1) Diagnostic
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('mdm_auto_enrllmnt', 'mdm_auto_enrllmnt_dflt_plcy')
  AND COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME;

SELECT CHARACTER_SET_NAME, COLLATION_NAME
INTO @ref_charset, @ref_collation
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'mdm_auto_enrllmnt'
  AND COLUMN_NAME = 'uuid';

-- 2) Colonnes s'écartant de la collation de référence
SELECT @ref_collation AS reference_collation;

SELECT COLLATION_NAME, COUNT(*) AS column_count, COUNT(DISTINCT TABLE_NAME) AS table_count
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME IS NOT NULL
GROUP BY COLLATION_NAME
ORDER BY column_count DESC;

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME <> @ref_collation
ORDER BY TABLE_NAME, COLUMN_NAME;

-- 3) Aligner la collation par défaut de la base
SET @ddl := IF(
    @ref_collation IS NULL,
    'SELECT ''ERROR: mdm_auto_enrllmnt.uuid not found - wrong schema?'' AS notice',
    CONCAT('ALTER DATABASE `', DATABASE(), '` CHARACTER SET ', @ref_charset, ' COLLATE ', @ref_collation)
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

Dans les commandes suivantes, les espaces réservés entre chevrons sont remplacés par les valeurs du fichier application.yml ou docker-compose.yml.

Docker

Les commandes sont exécutées dans le répertoire /opt/relution/, dans lequel le script est également enregistré.

docker compose stop relution

docker exec <containername> mariadb-dump -u <username> -p<password> --single-transaction <database> > relution_backup.sql

docker exec -i <containername> mariadb -t -u <username> -p<password> <database> < repair_temp_table_collation_mariadb.sql

docker compose start relution

Le mot de passe est indiqué ici directement après -p, car le script est transmis via l’entrée standard et une saisie interactive du mot de passe n’est alors pas possible.

Natif

Sous Linux :

sudo systemctl stop relution

mariadb-dump -u <username> -p --single-transaction <database> > relution_backup.sql

mariadb -t -u <username> -p <database> < repair_temp_table_collation_mariadb.sql

sudo systemctl start relution

Le script peut également être exécuté sur la base de données Relution à l’aide d’un outil d’administration tel que HeidiSQL ou DBeaver.

Vérifier la sortie

La dernière sortie du script indique la nouvelle collation par défaut de la base de données. Elle correspond alors à la collation des tables Relution, généralement utf8mb4_general_ci.

Si le message ERROR: mdm_auto_enrllmnt.uuid not found - wrong schema? s’affiche à la place, le script n’a pas été exécuté sur la base de données Relution. Dans ce cas, rien n’a été modifié.

La section 2 du script liste toutes les colonnes de texte dont la collation s’écarte de la référence. Normalement, cette liste est vide. Si elle contient des entrées, des tables avec une mauvaise collation ont été créées depuis la mise en place de la base de données, par exemple par des mises à jour de la base lors de mises à jour de Relution. Ces tables peuvent provoquer la même erreur lors d’autres opérations. Elles sont entièrement converties en utf8mb4_general_ci à l’aide du guide Convertir la base en UTF8MB4 →.

Prévention

Pour que les bases de données 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

La vérification des paramètres actuels s’effectue avec :

SHOW VARIABLES LIKE '%collation%';

Les valeurs de collation_server et collation_database sont utf8mb4_general_ci. La configuration complète est décrite dans le guide d’installation pour Linux →.