Problembeschreibung

Wer komplexe CMS nutzt, wie Joomla oder Wordpress, wird, wenn er sich mal seine Datenbanken genauer anschaut, mitunter festellen, dass durchaus nicht alle Tabelle die gleiche Kollation verwenden. Selten stellt das ein Problem da, aber unter bestimmten Bedingungen z.B. "gejointe" SQL-Abfragen kann das dann doch mal zu einer Fehlermeldung führen und eine Seite lahmlegen. Solche Fehlermeldungen könnten folgenden Wortlaut haben:

1267 Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation 'like'

Was die Kollation bedeutet und welche man wan verwendet, will ich hier nicht ausführen, weil es dazu reichlich Erklärseiten im Internet gibt. Nur kurz: Die Kollation spielt eine Rolle bei der Sortierung von Treffern. Speziell für Sprachen mit Sonderzeichen wird damit eine Regelwerk und eine zugehöriger interner Allgorithmus festgelegt, über den Treffer sortiert werden - z.B. wie ein "ä" zu berücksichtigen ist. Üblicherweise sollte man für CMS die COLLATION utf8_unicode_ci verwenden.

Hier mal ein Link, wo es schön erklärt wird: https://www.pixelfriese.de/unterschied-zwischen-utf8_general_ci-und-utf8_unicode_ci/

Sollten Sie z.B. oben aufgeführte Fehlermeldung erhalten, dann müssen Sie natürlich handeln und z.B. über phpMyAdmin die Kollation von Tabellen ändern und vereinheitlichen. Betrifft es nur wenige Tabellen, ist das mit einzelnen SQL-Befehlen in der Form ALTER TABLE `praefix_action_logs` COLLATE utf8_unicode_ci; schnell erledigt. Sind aber viele Erweiterungen in Ihrem CMS installiert worden oder das Projekt ggf. schon mehrfach migriert worden, werden Sie evtl. sehr viele unterschiedliche Kollationen finden. Das wird dann mühseelig, diese einzeln Tabelle für Tabelle zu ändern.

Lösung

Das Problem ist, dass es keine einfache SQL-Anweisung gibt, mit der man das für alle betroffenen Tabellen mit einem mal datenbankweit erledigen kann.

Hier ein Vorschlag, wie man das halbwegs schnell mit einfachen Mittleln ohne Programmieraufwand erledigen kann. Wer programmieren kann, würde sich sicher schnell ein Bash- oder PHP-Script dafür schreiben, was sicher auch sinnvoller wäre, wenn man sehr viele Projekte mit dem gleichen wiederkehrenden Problem hat (z.B. hier beschrieben)

1. Schritt, wir brauchen alle Tabellen
   
   • Führen Sie in einem MySQL-SQL-Query-Fenster folgenden Befehl aus (IHR_DATENBANK_NAME ersetzen!):
   • SELECT CONCAT(TABLE_NAME, " " )
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = "IHR_DATENBANK_NAME"
     AND TABLE_TYPE = "BASE TABLE"
     LIMIT 0 , 300
   • Der CONCAT-Befehl kann auch schon hier den kompletten Ziel-Befehl erzeugen, wenn dieser so verwendet würde: SELECT CONCAT('ALTER TABLE ', TABLE NAMA, ' COLLATION utf8_unicode_ci'). Allerdings ist es beim meiner MySQL-Installation so, dass die Trefferliste nur verkürzte, abgeschnittenen Zeile ausgibt.
   • Sie erhalten eine Ausgabe aller Ihrer Tabellen in der Datenbank.
   • Diese mit der Maus markieren und in die Zwischenablagt kopieren.
2. Schritt, wir nutzen Excel zum Erzeugen der Queries
   • Nun öffnen wir eine neue Excel-Tabelle und fügen dort die Zwischenablage ein.
   • Evtl. störende Zwischenzeilen aus der SQL-Trefferliste löschen.
   • Im Feld hinter dem ersten Eintrag folgende Formel eingeben: =VERKETTEN("ALTER TABLE `";A1;"` COLLATE utf8_unicode_ci;").
   • Diese Zeile auf alle anderen darunter anwenden.
   • Die Ergebnisspalte enthält nun alle SQL-Befehle. Diese Spalte markieren und in die Zwischenablage kopieren.
3. Schritt, wir führen die ALTER TABLE - Queries aus
   • Nun gehen wir wieder in unser MySQL-Query-Fenster und fügen dort diese SQL-Befehle ein und lassen diese in einem Ruck ausführen.

Das wars.