Relution Signet Customer Hub

Illegal mix of collations

Bei Installationen mit MariaDB brechen einzelne Vorgänge mit dem Datenbankfehler 1267 ab. Häufig fällt das zuerst bei der Automatischen Geräteregistrierung (ADE, im Portal: DEP) auf: Die Synchronisierung der Geräte schlägt für alle Organisationen fehl, während der übrige Betrieb normal weiterläuft. Im Relution-Log erscheint bei jeder Synchronisierung eine Meldung wie die folgende:

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`

Die genannten Collations können je nach Installation abweichen, z. B. utf8mb4_uca1400_ai_ci statt utf8mb4_unicode_ci. Kennzeichnend ist eine Tabelle mit dem Präfix TL_ in der Abfrage. Wo die Log-Dateien liegen, ist unter Relution Logs → beschrieben.

Ursache

Relution erwartet für alle Textspalten die Collation utf8mb4_general_ci. Die Tabellen der Datenbank sind entsprechend angelegt, und auch die Datenbankverbindung von Relution arbeitet mit dieser Collation.

Für Massenänderungen, etwa das Löschen mehrerer Geräteregistrierungen während der ADE-Synchronisierung, legt Relution kurzzeitig temporäre Hilfstabellen mit dem Präfix TL_ an. Für diese Tabellen wird keine Collation vorgegeben, sie übernehmen daher die Standard-Collation der Datenbank. Weicht diese Standard-Collation von utf8mb4_general_ci ab, kann MariaDB die Spalten der Hilfstabelle nicht mit den Spalten der eigentlichen Tabellen vergleichen und bricht die Abfrage ab. Die gespeicherten Daten sind davon nicht betroffen.

Eine abweichende Standard-Collation entsteht typischerweise in folgenden Fällen:

  • Die Datenbank wurde auf einem MariaDB-Server angelegt, dessen collation-server nicht auf utf8mb4_general_ci gesetzt ist. Neuere MariaDB-Versionen verwenden ohne explizite Konfiguration eine andere Standard-Collation.
  • Die Datenbank wurde per Dump auf einen neuen Server oder in einen neuen Container übertragen. Der Dump stellt die Tabellen mit ihrer ursprünglichen Collation wieder her, die neu angelegte Datenbank selbst erhält jedoch die Vorgabe des neuen Servers.

Lösung

Die Standard-Collation der Datenbank wird an die Collation der Relution-Tabellen angeglichen. Dabei ändert sich ausschließlich die Vorgabe für neu angelegte Tabellen, bestehende Tabellen und Daten bleiben unverändert.

Erforderliche Schritte:

  1. Der Relution-Dienst wird gestoppt.
  2. Ein Backup der Datenbank wird erstellt.
  3. Das nachfolgende Skript wird als Datei repair_temp_table_collation_mariadb.sql gespeichert und auf der Relution-Datenbank ausgeführt.
  4. Die Ausgabe des Skripts wird geprüft (siehe unten).
  5. Der Relution-Dienst wird gestartet.

Der Neustart ist zwingend erforderlich. Die temporären Hilfstabellen bestehen jeweils für die Dauer einer Datenbankverbindung. Bereits geöffnete Verbindungen verwenden sonst weiterhin die mit der falschen Collation angelegte Hilfstabelle.

Skript

Das Skript ermittelt die Collation der Relution-Tabellen, listet abweichende Spalten auf und setzt die Standard-Collation der Datenbank. Es kann gefahrlos mehrfach ausgeführt werden.

repair_temp_table_collation_mariadb.sql
-- Behebt "Illegal mix of collations" (Fehler 1267) bei temporären TL_*-Tabellen unter MariaDB.
-- Setzt nur die Standard-Collation der Datenbank, bestehende Tabellen und Daten bleiben unverändert.
-- Danach muss Relution neu gestartet werden.

-- 1) Diagnose
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) Spalten, die von der Referenz-Collation abweichen
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) Standard-Collation der Datenbank angleichen
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();

Die Platzhalter in spitzen Klammern werden in den folgenden Befehlen durch die Werte aus der application.yml bzw. docker-compose.yml ersetzt.

Docker

Die Befehle werden im Verzeichnis /opt/relution/ ausgeführt, in dem auch das Skript abgelegt ist.

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

Das Passwort wird hier direkt hinter -p angegeben, da die Eingabe des Skripts über die Standardeingabe erfolgt und eine interaktive Passwortabfrage dabei nicht möglich ist.

Nativ

Unter 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

Das Skript kann alternativ auch über ein Verwaltungstool wie HeidiSQL oder DBeaver auf der Relution-Datenbank ausgeführt werden.

Ausgabe prüfen

Die letzte Ausgabe des Skripts zeigt die neue Standard-Collation der Datenbank. Sie entspricht danach der Collation der Relution-Tabellen, in der Regel utf8mb4_general_ci.

Wird stattdessen die Meldung ERROR: mdm_auto_enrllmnt.uuid not found - wrong schema? ausgegeben, wurde das Skript nicht auf der Relution-Datenbank ausgeführt. In diesem Fall wurde nichts geändert.

Abschnitt 2 des Skripts listet alle Textspalten auf, deren Collation von der Referenz abweicht. Im Normalfall ist diese Liste leer. Enthält sie Einträge, wurden seit dem Anlegen der Datenbank Tabellen mit der falschen Collation erstellt, etwa durch Datenbank-Updates im Rahmen von Relution-Updates. Diese Tabellen können bei anderen Vorgängen denselben Fehler auslösen. Sie werden mit der Anleitung unter DB Konvertierung zu UTF8MB4 → vollständig auf utf8mb4_general_ci umgestellt.

Vorbeugung

Damit neu angelegte Datenbanken direkt die richtige Collation erhalten, wird der MariaDB-Server mit folgenden Einstellungen im Abschnitt [mysqld] betrieben:

[mysqld]
collation-server=utf8mb4_general_ci
character-set-server=utf8mb4

Die Prüfung der aktuellen Einstellungen erfolgt mit:

SHOW VARIABLES LIKE '%collation%';

Die Werte für collation_server und collation_database lauten dabei utf8mb4_general_ci. Die vollständige Konfiguration ist in der Installationsanleitung für Linux → beschrieben.