Relution Signet Customer Hub

MariaDB-Collation korrigieren

Relution erwartet in einer MariaDB-Datenbank für alle Tabellen und Textspalten den Zeichensatz utf8mb4 mit der Collation utf8mb4_general_ci. Weichen einzelne Tabellen, Spalten oder die Standard-Collation der Datenbank davon ab, brechen einzelne Vorgänge mit Datenbankfehlern ab. Im Gegensatz zum älteren Zeichensatz utf8 (utf8mb3) speichert utf8mb4 auch Zeichen mit vier Byte, z. B. Emojis.

Diese Anleitung gilt nur für Installationen mit MariaDB. Für PostgreSQL-Datenbanken sind die Skripte nicht anwendbar. Die unterstützten Datenbanken sind unter Systemanforderungen → aufgeführt.

Fehlerbild

Häufig fällt eine abweichende Collation 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. Derselbe Fehler kann auch bei anderen Massenänderungen auftreten, z. B. beim Löschen eines Benutzers. Wo die Log-Dateien liegen, ist unter Relution Logs → beschrieben.

Ursache

Relution legt seine Tabellen ohne eigene Collation an, sie übernehmen beim Anlegen die Standard-Collation der Datenbank. Die Datenbankverbindung von Relution arbeitet mit utf8mb4_general_ci.

Für Massenänderungen, etwa das Löschen mehrerer Geräteregistrierungen während der ADE-Synchronisierung, legt Relution temporäre Hilfstabellen mit dem Präfix TL_ an, die für die Dauer der jeweiligen Datenbankverbindung bestehen bleiben. Auch diese Tabellen übernehmen die Standard-Collation der Datenbank. Weicht sie von der Collation der eigentlichen Tabellen ab, kann MariaDB die Spalten nicht miteinander vergleichen und bricht die Abfrage ab. Die gespeicherten Daten sind davon nicht betroffen.

Abweichungen entstehen typischerweise in folgenden Fällen:

  • Die Datenbank wurde auf einem MariaDB-Server ohne collation-server=utf8mb4_general_ci angelegt. Neuere MariaDB-Versionen verwenden ohne explizite Konfiguration eine andere Standard-Collation, Tabellen aus späteren Relution-Updates erhalten dann ebenfalls diese Collation.
  • Die Tabellen wurden nachträglich auf utf8mb4_general_ci umgestellt, die Standard-Collation der Datenbank selbst aber nicht.
  • 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.
  • Die Datenbank stammt aus einer älteren Installation mit dem Zeichensatz utf8 und wurde nie umgestellt.

Skripte

Prüfung und Korrektur erfolgen in zwei Schritten. Das Skript generate_mariadb_utf8mb4_conversion.sql ändert selbst nichts an der Datenbank. Es prüft Datenbank, Tabellen und Spalten und erzeugt daraus das Skript convert_utf8mb4.sql, das nur Anweisungen für tatsächlich abweichende Objekte enthält:

  • Standard-Zeichensatz und Standard-Collation der Datenbank werden angeglichen, sofern sie abweichen.
  • Betroffene Tabellen werden auf utf8mb4 mit utf8mb4_general_ci und bei Bedarf auf InnoDB mit dem Zeilenformat DYNAMIC umgestellt.
  • Fremdschlüssel, die von einer betroffenen Tabelle ausgehen oder auf sie verweisen, werden vor der Umstellung entfernt und danach unverändert neu angelegt.

Tabellen, die bereits korrekt sind, bleiben unverändert.

Die Skripte sind für MariaDB 10.11 LTS und neuer ausgelegt. Die Anweisung SET SESSION character_set_collations übernimmt der Generator nur, wenn die MariaDB-Version sie unterstützt (ab 11.2). Generator und erzeugtes Skript werden deshalb auf demselben MariaDB-Server ausgeführt.

Wird diese Anleitung als PDF-Datei betrachtet, kann es sein, dass Anführungszeichen durch ihre typografischen, d.h. geschweiften Versionen ersetzt wurden. Dadurch wird die Syntax des Skripts falsch. Für die Skripte werden deshalb die Download-Links oder die HTML-Version dieses Dokuments verwendet.

Generator-Skript

generate_mariadb_utf8mb4_conversion.sql herunterladen →

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;

Beispielausgabe ohne Befund

Enthält die Datenbank keine Abweichungen, besteht das erzeugte Skript convert_utf8mb4.sql ausschließlich aus SET-Anweisungen wie in diesem Beispiel. In diesem Fall ist nichts zu korrigieren. Bei MariaDB-Versionen vor 11.2 fehlt die Zeile mit character_set_collations.

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;

Vorgehen

Die Änderungen des Skripts lassen sich nicht per Rollback zurücknehmen. Vor dem Eingriff in die Datenbank wird ein vollständiges Backup erstellt.

Erforderliche Schritte:

  1. Der Relution-Dienst wird gestoppt.
  2. Ein Backup der Datenbank wird erstellt, siehe auch Disaster Recovery →.
  3. Das Generator-Skript wird auf der Relution-Datenbank ausgeführt, die Ausgabe wird in die Datei convert_utf8mb4.sql geschrieben.
  4. Die Datei convert_utf8mb4.sql wird geprüft. Enthält sie wie die Beispielausgabe nur SET-Anweisungen, ist nichts zu korrigieren und Schritt 5 entfällt.
  5. Die Datei convert_utf8mb4.sql wird auf der Relution-Datenbank ausgeführt.
  6. 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 würden sonst weiterhin die mit der falschen Collation angelegte Hilfstabelle verwenden. Bei großen Datenbanken kann Schritt 5 einige Zeit dauern, da MariaDB jede betroffene Tabelle dabei neu schreibt.

Die Platzhalter in spitzen Klammern werden in den folgenden Befehlen durch die Werte aus der application.yml bzw. der Compose-Datei (compose.yaml, bei älteren Installationen docker-compose.yml) ersetzt. Die Befehle und das Generator-Skript gehen vom Datenbanknamen relution aus. Wurde bei der Installation ein anderer Datenbankname gewählt, wird er in Zeile 1 des Generator-Skripts (SET @db = 'relution';) und in den Befehlen ersetzt. Der Name steht in der application.yml am Ende der Datenbank-URL, z. B. relution bei jdbc:mariadb://localhost/relution?useServerPrepStmts=true.

Die Optionen -N, -B und -r sorgen dafür, dass die Ausgabe ohne Spaltenüberschrift, Tabellenrahmen und Maskierung geschrieben wird und dadurch direkt als Skript ausführbar ist.

Docker

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

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

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

Um zu vermeiden, dass das Passwort im Klartext in der Shell-Historie gespeichert wird, kann dem Befehl ein Leerzeichen vorangestellt oder die Historie anschließend bereinigt werden.

Nativ

Unter 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

Unter Windows werden dieselben mariadb-Aufrufe in der Eingabeaufforderung ausgeführt, z. B. über die Verknüpfung Command Prompt (MariaDB 10.11 (x64)). In der PowerShell funktioniert die Umleitung mit < nicht. Der Inhalt der erzeugten Datei wird dort mit type convert_utf8mb4.sql angezeigt.

Ergebnis prüfen

Nach der Umstellung wird das Generator-Skript erneut wie in Schritt 3 ausgeführt. Enthält die neue Ausgabe nur noch die SET-Anweisungen der Beispielausgabe, stehen Datenbank und Tabellen vollständig auf utf8mb4 mit utf8mb4_general_ci. Das Generator-Skript nimmt keine Änderungen vor und kann jederzeit erneut ausgeführt werden.

Vorbeugung

Damit neu angelegte Datenbanken und Tabellen 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
character-set-collations=utf8mb4=utf8mb4_general_ci

Ab MariaDB 11.5 ist die Zeile character-set-collations erforderlich. Ohne sie verwendet MariaDB für Verbindungen die Collation utf8mb4_uca1400_ai_ci, auch wenn Datenbank und Tabellen auf utf8mb4_general_ci stehen. Die Option gibt es ab MariaDB 11.2. Ältere Versionen wie MariaDB 10.11 LTS kennen sie nicht und starten mit dieser Zeile nicht; dort entfällt sie.

Die Prüfung der aktuellen Einstellungen erfolgt in der MariaDB-Konsole. Dabei wird zuerst die Relution-Datenbank ausgewählt:

USE relution;
SHOW VARIABLES LIKE '%collation%';

Die Werte für collation_server und collation_database lauten dabei utf8mb4_general_ci. Ohne USE ist keine Datenbank ausgewählt (erkennbar am Prompt MariaDB [(none)]), und collation_database zeigt nur den Standard des Servers statt der Einstellung der Relution-Datenbank. Steht collation_connection ab MariaDB 11.5 auf utf8mb4_uca1400_ai_ci, fehlt in der Konfiguration die Zeile character-set-collations. Die vollständige Konfiguration ist im Artikel Veraltet: Linux (Alma) → beschrieben.

Wie hilfreich war dieser Artikel?