Was passierte am nullten nullten des Jahres Null, und warum steht dieses Datum, obwohl kalendarisch unsinnig, in der Datenbank, und wenn es schon da drin ist, warum steigt MySQL deswegen auf einmal mit einem Fehler aus? Das ist nur eine von mehreren Fragen, die ein Upgrate auf die MySQL-Version 5.7 aufwerfen kann, und die Antwort darauf steht in der Systemvariable sql_mode. Mit ihrer Hilfe lassen sich nämlich grundlegende Dinge wie die zu verwendende SQL-Syntax und die vorzunehmende Daten-Validierung konfigurieren. Diese SQL-Modi entscheiden also darüber, wie sich die Datenbank verhält. Weil MySQL 5.7 die Zügel mit strengeren Voreinstellungen anzieht, kann es mit älteren Datenbanken zu Problemen kommen.

Wer auf seinem Server ein Ubuntu mit Langzeit-Unterstützung (LTS) einsetzt, kommt erstmals in der im April 2016 erschienen Ubuntu-Version 16.04 „Xenial Xerus“ mit der verschärften MySQL-Konfiguration in Berührung; dort wird Version 5.7.12 des Datenbank-Servers installiert. Beim LTS-Vorgänger Ubuntu 14.04 wie auch beim ein Jahr später erschienenen Debian 8 „Jessie“ ist noch die MySQL-Version 5.5.49 an Bord. Ubuntu 16.04 bringt ja für Server-Betreiber einige Neuheiten; so auch das Upgrade auf PHP 7.

An der Installation von MySQL hat sich – verwendet man das Metapaket mysql-server – nichts geändert; nur dass Debian und Ubuntu eben früher mysql-server5.5 installiert haben; das neue Ubuntu zieht statt dessen mysql-server5.7 aus dem Paketarchiv:

$ apt install mysql-server mysql-client php-mysql

Diese Befehlszeile installiert auch noch den Kommandozeilen-Client von MySQL sowie die PHP-Erweiterung zur Kommunikation mit MySQL. A propos PHP: Mit PHP 7 ist die alte mysql-Erweiterung entgültig entsorgt worden. Statt dessen nutzen PHP-Programmierer MySQLi oder PDO.

Wer seine alten MySQL-Datenbanken unter der neuen Version betreiben will, sollte beachten, dass der offiziell unterstützte Upgrade-Pfad immer nur eine Versionsnummer (hinter dem Punkt) umfasst. Die Aktualisierung von Ubuntu 14.04 auf 16.04 verursacht zumindest auf dem Papier Probleme, denn der Sprung von MySQL 5.5 auf 5.7 umfasst zwei Versionsnummern. Ich habe trotzdem auf meinem Testsystem Dutzende von MySQL-Datenbanken, darunter uralte Datengräber, mit Ubuntu 16.04 unter MySQL 5.7 wiederbeleben können – wenn auch mit einigen Fehlermeldungen. Da Debian/Ubuntu so konfiguriert sind, dass bei einem Upgrade auch die alten Datenbank-Dateien (in /var/lib/mysql zu finden) aktualisiert und wenn nötig repariert werden, sollten sich die meisten Probleme automatisch lösen. (Für alle Fälle hat der Datenbank-Admin natürlich Backups gemacht und unternimmt solche Versuche ohnehin nicht ungeprüft an einem Produktiv-System.)

Wenn dann trotzdem nicht alles läuft, muss man früher oder später einen Blick auf die voreingestellten MySQL-Modi werfen. Die aktuelle globale Konfiguration des Servers ist leicht im MySQL-Client abrufbar:

$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.12-0ubuntu1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

Wie man sieht, wird zunächst der MySQL-Client im Terminal aufgerufen. Nach erfolgreichem Login werden die aktuell gültigen SQL-Modi in Form von Konstanten ausgegeben. Die Ausgabe entspricht der Voreinstellung von MySQL 5.7. Auf drei dieser voreingestellten Modi soll nun genauer geschaut werden, denn sie bergen das größte Konfliktpotential:

ONLY_FULL_GROUP_BY: Dieser Modus, der vor MySQL 5.7.5 deaktiviert war, gestattet eine Gruppierung nur dann, wenn alle in SELECT, HAVING oder ORDER BY aufgelisteten Spalten auch unter GROUP BY auftauchen – also explizit aggregiert werden – oder wenn sie zumindest funktional abhängig von einer in GROUP BY aufgelisteten Spalte sind. Eine solche Anhängigkeit ist gegeben, wenn beide zum Beispiel zur selben Tabelle gehören und die unter GROUP BY gelistete Spalte der Primärschlüssel dieser Tabelle ist. Die MYSQL-Dokumentation liefert dafür Beispiele und weitere Erläuterungen auf Englisch. Eine typische Fehlermeldung lautet so:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‚datenbank.tabelle.spalte‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Um Abhilfe zu schaffen, sollte man die in der Fehlermeldung genannte Spalte entweder aus der SELECT-Liste herausnehmen (bei einer Abfrage mit SELECT * sollte das Sternchen durch konkrete Spaltennamen ersetzt werden, aber eben ohne die in der Fehlermeldung genannte Spalte), oder aber in GROUP BY zusätzlich aufführen.

NO_ZERO_DATE: Per Definition beginnt der zulässige Datumsbereich in MySQL am 1. Januar 1000. Dennoch war es möglich, eine Spalte von Typ DATE mit DEFAULT ‚0000-00-00‘ (oder eine vom Typ DATETIME mit DEFAULT ‚0000-00-00 00:00:00) zu deklarieren, weil NO_ZERO_DATE vor MySQL 5.7 nicht aktiviert war. Die folgenden Queries liefern beide das gleiche Ergebnis:

SELECT datum FROM kalender WHERE datum='0000-00-00';
SELECT datum FROM kalender WHERE datum IS NULL;

Der Datums-String ‚0000-00-00‘ wird also von MySQL dem Wert „NULL“ gleichgesetzt, selbst wenn das Feld als NOT NULL definiert ist. Versucht man in MySQL 5.7 jedoch, per INSERT oder UPDATE das falsche Default-Datum in die Tabelle zu schreiben, kommt ein Fehler:

ERROR 1292 (22007): Incorrect date value: ‚0000-00-00‘ for column ‚datum‘ at row 1

STRICT_TRANS_TABLES: Aktiviert den strikten Modus für Transaktions-basierte Datenbank-Engines (also für INNO_DB, aber nicht für MY_ISAM). Die Definition des strikten Modus unterliegt leider einigem Hin und Her: Eine Verschärfung in MySQL 5.7.4 (durch Hinzunahme des bereits beschriebenen NO_ZERO_DATE sowie von NO_ZERO_IN_DATE und ERROR_FOR_DIVISION_BY_ZERO) wurde bei Version 5.7.8 – vorerst – wieder zurückgenommen (und ist auch in 5.7.12 nicht enthalten). Das Thema wird ausführlich in der MySQL-Dokumentationen im Kapitel SQL Mode Changes behandelt.

Das waren die Top 3 Troublemaker beim Upgrade auf MySQL 5.7, wobei STRICT_TRANS_TABLES im Augenblick entschärft ist, nach dem Willen der MySQL-Entwickler aber auf lange Sicht wieder verschärft werden soll.

Was tun? Bei Ärger mit den SQL-Modi sollte man tunlichst seine Queries so korrigieren, dass sie unter den verschärften Bedingungen keine Fehler mehr produzieren. Alte Datenbanken, die fehlerhafte Werte enthalten, sollte man bereinigen, und zwar noch auf einer alten Datenbank-Version. Wenn das nicht möglich oder zu aufwändig ist, muss man wohl oder übel die SQL-Modi wieder so entschärfen, wie sie vor dem Upgrade waren.

Um die Konfiguration der Datenbank zu ändern, setzt man einfach die Systemvariable sql_mode neu, diesmal aber ohne die Fehlermeldungen produzierenden Bestandteile:

mysql> SET GLOBAL sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Session vs. global: In den bisherigen Beispielen ist sql_mode immer global gelesen und gesetzt worden. Es ist jedoch auch möglich, diese Einstellung für jede Verbindung einzeln vorzunehmen. In diesem Fall ersetzt man GLOBAL durch SESSION (oder lässt es ganz weg):

mysql> SET SESSION sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Diese im MySQL-Client abgesetzte Anweisung gilt dann auch nur für die aktuelle Verbindung im MySQL-Client und lässt sich folglich nur dort kontrollieren:

mysql> SELECT @@SESSION.sql_mode;

Um diese Modi für die Datenbank-Verbindung einer PHP-Anwendung zu setzen, verwendet man im PHP-Code eine Zeile wie diese, wobei $connection eine von PDO oder mysqli aufgebaute Datenbank-Verbindung repräsentiert:

$connection->exec(
     'SET sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"'
);

Eine solche verbindungsbasierte Einstellung hält natürlich nur so lange wie die Verbindung, in der sie definiert wurde. Ein GLOBAL gesetzter sql_mode gilt zwar für alle Verbindungen, hat aber auch nur eine zeitlich begrenzte Haltbarkeit – bis nämlich der Datenbank-Server gestoppt wird. Um MySQL dauerhaft entspannte Voreinstellungen einzuimpfen, sollte man die Datei /etc/mysql/my.cnf um die folgenden Zeilen erweitern:

[mysqld]
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Verwendet eine Datenbank noch das alte Passwort-Hashing, dann hilft auch kein Schrauben am SQL-Modus mehr: Es gilt seit Jahren als „depreciated“, ist leicht zu knacken und wurde in MySQL 5.7.5 endlich entfernt. Damit funktioniert auch die Funktion OLD_PASSWORD() nicht mehr.

Die alten, vor MySQL 4.1 eingesetzten Passwörter sind von den neuen leicht zu unterscheiden: Sie sind nur 16 Zeichen lang; die neuen beginnen mit einem Sternchen, gefolgt von 40 Zeichen. Um Konten auf das neue Passwort-Format umzustellen, schlägt die MySQL-Doku zwei Upgrade-Varianten vor.

Weitere für ein Upgrade auf MySQL 5.7 relevante Änderungen und Warnungen vor „incompatible changes“ enthält ein spezielles Kapitel in der Dokumentation.

Zum Schluss noch ein Wort zum MySQL-Fork MariaDB: Nachdem Oracle die Datenbank MySQL geschluckt hat, erscheint Maria DB als politisch korrekte freie Alternative, zumal dahinter MySQL-Gründer Monty Widenius steht. MariaDB lässt sich zwar in Ubuntu 16.04 in der Version 10.0.25 installieren (nach der Version 5.5 hat MariaDB die gemeinsamen Versionsnummern mit MySQL aufgegeben), jedoch gilt das LTS-Versprechen (5 Jahre Support) nur für das im Main-Repository enthaltene MySQL-Server-Paket. Für MariaDB (Universe-Repository) wird gar kein Pflege-Zeitraum angegeben. Aus der Sicht vieler Server-Administratoren dürfte dies ein Ausschlusskriterium sein. Wer MariaDB statt MySQL produktiv auf seinem Server nutzen will, greift wohl besser zu CentOS oder Slackware, jedenfalls zu einer Distribution, die MariaDB standardmäßig installiert und ordentlich wartet.