MySQL hatte lange Zeit Probleme mit Zeichensätzen; der bekannte Zeichensalat (auch „Mojibake“ genannt) war die Folge. Dann kam endlich der universelle Zeichensatz Unicode, bzw. UTF8. Praktisch verborgen blieb jedoch bis heute, dass MySQL standardmässig nur einen „beschränkten Unicode Zeichensatz“ verwendet. Dieser besteht nur aus 3, anstelle von 4 Zeichen und war wohl ursprünglich als „Performace-Optimierung“ gedacht.
Das reicht zwar für die allermeisten Schriftzeichen und Symbole, beispielsweise aber nicht für die mittlerweile sehr häufig verwendeten emojis. Schlimmer noch, probiert man ein solches Zeichen (z.B. ein emoji) in die Datenbank einzufügen, wird alles was nach dem Zeichen kommt abgeschnitten. Und das kann wiederum zu schlimmen Sicherheitslücken führen.
Dabei hatte MySQL seit etwa 2010 einen „neuen“ (bzw. den vollen) UTF8-Zeichensatz eingeführt mit dem Namen: utf8mb4
.
Neuere Web-Applikationen, wie WordPress oder Typo3 verwenden diesen seit einiger Zeit standardmässig, allerdings nur bei einer Neuinstallation. Viele ältere Datenbanken haben also noch den unvollständigen Zeichensatz eingestellt. Höchste Zeit also die alten Datenbanken mit dem richtigen Unicode Zeichensatz zu aktualisieren.
Inhalt
Das Problem nachstellen – Oder auch „Proof of Concept“ (PoC)
Wie sich das auswirkt, lässt sich ganz einfach festellen, idem man bei einer bestehenden Datenbank einen Text mit einem Symbol oder Emoji einfügt:
UPDATE database_name.table_name SET column_name = 'foo𝌆bar' WHERE id = 9001; |
Die Ausgabe sieht dann so aus:
mysql> SELECT column_name FROM database_name.table_name WHERE id = 9001;
+-------------+
| column_name |
+-------------+
| foo |
+-------------+
Und mittels SHOW WARNINGS
wird auch eine Warnung angezeigt:
mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xF0\x9D\x8C\x86' for column 'column_name' at row 1 |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Von utf8 auf utf8mb4 wechseln
Der Wechsel ist relativ einfach und nicht besonders Risikoreich.
Trotzdem ist natürlich ein Backup aller involvierten Datenbanken Pflicht.
Zuerst ändert man die Datenbank selbst:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; |
Danach wird jede Tabelle in der Datenbank konvertiert:
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; |
Die Kollationen (Collations) sind zwar noch für jede Kolumne in den einzelnen Tabellen gesetzt und man könnte diese nun auch einzeln umwandeln (ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
), jedoch ist das nicht nötig, weil das CONVERT TO CHARACTER SET
der Tabelle automatisch auch alle Kolumnen transformiert.
[stextbox id=“note“ caption=“Achtung bei Feldern mit fixer Grösse“]
Nun wird man feststellen, dass man bei manchen Feldern eine Fehlermledung bekommt, dass es nicht grösser als 767 bytes sein darf.
Dies rührt daher, dass man mit dem vierten Unicode Zeichen nun auch etwas mehr Platz in der Datenbanktabelle braucht. Hat man also beispielsweise ein Feld VARCHAR(255) würde dies so überlaufen. Neu lassen sich da maximal noch 191 Zeichen setzen. Somit muss man alle Felder, die üblicherweise auf das Maximum on 255 Zeichen gesetzt sind auf maximal 191 Zeichen stellen.
[/stextbox]
Den Prozess automatisieren
Da viele Datenabken zahlreiche Tabellen haben und es nun ziemlich aufwändig wäre dies bei jeder einzelnen Tabelle zu machen, kann man sich ein kleines Script schreiben, welche das für eine komplette Datenbank automatisch macht:
#!/bin/bash # mycollate.sh <database> [<charset> <collation>] # changes MySQL/MariaDB charset and collation for one database - all tables and # all columns in all tables DB="$1" CHARSET="$2" COLL="$3" [ -n "$DB" ] || exit 1 [ -n "$CHARSET" ] || CHARSET="utf8mb4" [ -n "$COLL" ] || COLL="utf8mb4_general_ci" echo $DB echo "ALTER DATABASE \`$DB\` CHARACTER SET $CHARSET COLLATE $COLL;" | mysql echo "USE \`$DB\`; SHOW TABLES;" | mysql -s | ( while read TABLE; do echo $DB.$TABLE echo "ALTER TABLE \`$TABLE\` CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB done ) |
Dies kann man dann einfach aufrufen mittels:
./mycollate.sh <DATENBANK>
Als letztes muss nun für alle Tabellen noch ein REPAIR TABLE table_name;
gefolgt von einem OPTIMIZE TABLE table_name;
aufgerufen werden. Glücklicherweise gibt es dafür das Tool mysqlcheck, welches das automatisch für sämtliche Datenbanken und deren Tabellen macht:
mysqlcheck -u root -p --auto-repair --optimize --all-databases
Nach dieser Prozedur kann man dann mittels dem anfänglichen Beispiel testen- und feststellen, dass sich nun auch Symbole und Emojis wie dieses hier 💩 in die Datenbank einfügen lassen.
Zeichensatz im Server und Client anpassen
Nun muss noch in der mysql Konfiguration (my.cnf) angeben werden, dass beim verbinden auch der neue Zeichensatz verwendet wird:
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Nachdem der mysql Dienst neu gestartet wurde, lässt sich die Änderung mit folgender Query anzeigen:
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; |
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.00 sec)
Auch sollten die mysql Backup Scripte noch angepasst werden, dass diese den neuen Zeichensatz standardmässig verwenden.
Das geht mit dem Zusatz-Parameter --default-character-set=utf8mb4
für mysqldump, beispielsweise:
mysqldump --opt --complete-insert --events --routines --triggers --default-character-set=utf8mb4 --databases "$dbname" |$APP_GZIP > "$DIR_BACKUP_MYSQL/$dbname".sql.gz |
Zusatzaufgabe: Von MyISAM auf InnoDB wechseln
Wenn man grad schon bei der Datenbank-Optimierung ist, kann man auch gleich noch vom früher hauptsächlich verwendeten MyISAM auf InnoDB wechseln, welches deutliche Vorteile gegenüber MyISAM bringt.
Das geht mittels dem folgenden SQL welches widerum auf jede Tabelle in der Datenbank angewendet wird:
ALTER TABLE TABLE_NAME ENGINE=InnoDB; |
Um das auch automatisch zu machen kann man nun das obige Script etwas umschrieben um das zu erreichen:
#!/bin/bash # myisam2innodb.sh <database> # changes MySQL/MariaDB database engine from MyISAM to InnoDB, # creating a backup before DB="$1" [ -n "$DB" ] || exit 1 echo $DB echo "Creating backup of DB: $DB ..." mysqldump $DB > ${DB}.sql echo "USE \`$DB\`; SHOW TABLES;" | mysql -s | ( while read TABLE; do echo $DB.$TABLE echo "ALTER TABLE \`$TABLE\` ENGINE=InnoDB;" | mysql $DB done ) |
Als „Bonus“ macht dieses Script auch gleich automatisch ein Backup der Datenbank vor der Operation.