Das Symptom
Die Datenbank ist auf einen neuen Server umgezogen, MySQL 8.4 statt der alten
Installation. Der Import lief durch, die Anwendung startet, und dann bricht die erste
Auswertung ab: ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.
Die Abfrage ist seit Jahren unverändert. Sie hat auf dem alten Server jeden Tag ihren
Bericht geliefert, in dem je Kunde der höchste Umsatz, der Name und die Adresse stehen.
Der naheliegende Schluss: Die neue Version ist fehlerhaft oder zu streng, und
irgendwo gibt es einen Schalter, der sie wieder so freundlich macht wie die alte.
Die „Layer 8“-Wahrheit
Den Schalter gibt es, er heißt ONLY_FULL_GROUP_BY, und er stand auf dem alten Server
auf aus. Das Handbuch zu MySQL 8.4 zählt die voreingestellten SQL-Modi auf, und der erste
in der Liste ist genau dieser. Im Abschnitt zu GROUP BY steht, was er tut: „If the
ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries
for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated
columns that are neither named in the GROUP BY clause nor are functionally dependent
on them.“
Der Kern des Problems ist nicht der neue Server, sondern die Abfrage. Wer nach dem
Kundennamen gruppiert und daneben die Adresse ausgibt, verlangt vom Server, aus
mehreren Zeilen der Gruppe eine Adresse auszusuchen, ohne zu sagen, welche. Ohne den
Modus tut MySQL das auch. Das Handbuch beschreibt die Folge nüchtern: „the server is
free to choose any value from each group, so unless they are the same, the values chosen
are nondeterministic, which is probably not what you want.“ Der alte Bericht war
also nicht richtig, sondern unbeanstandet. Das sind verschiedene Dinge.
Die Fehlermeldung ist dabei ungewöhnlich hilfreich. Sie nennt die Nummer des Ausdrucks
in der SELECT-Liste und die vollständige Spalte. Der Begriff functionally dependent
meint, dass die Gruppierungsspalte den Wert eindeutig festlegt. MySQL erkennt diese
Abhängigkeit laut Handbuch, wenn die Gruppierungsspalte Primärschlüssel oder eine
eindeutige NOT-NULL-Spalte ist; dann hat jede Gruppe nur eine Zeile, und die Auswahl ist
keine Auswahl mehr. Wer also nach kunden.id gruppiert statt nach kunden.name, bekommt
Name und Adresse ohne Fehler, weil die id beides bestimmt.
Besonders ärgerlich ist eine zweite Stelle im Handbuch, die viele Altabfragen betrifft:
Die Auswahl des Werts innerhalb der Gruppe lässt sich nicht durch ORDER BY beeinflussen,
weil sortiert wird, nachdem die Werte gewählt sind. Das beliebte Muster, mit GROUP BY
kunde ORDER BY datum DESC den jeweils neuesten Datensatz zu bekommen, hat deshalb nie
funktioniert. Es sah nur oft so aus, weil die Daten zufällig in der richtigen
Reihenfolge lagen.
Für den Übergang gibt es ANY_VALUE(). Das Handbuch beschreibt die Funktion als Mittel
für Fälle, in denen MySQL eine Abfrage ablehnt, von der man weiß, dass sie gültig ist,
aus Gründen, die MySQL nicht erkennen kann. Ihr Ergebnis wird nicht gegen den Modus
geprüft. Damit wird aus dem stillen Raten ein sichtbares, dokumentiertes Raten, und
genau das ist der Unterschied zwischen einem Bericht, dem man traut, und einem, dem
man bisher nur getraut hat.
Die echte, verifizierte Lösung
1000 % faktenbasiert. Jeder Befehl ist real, jeder Pfad existiert. Schritt für Schritt:
1.Den aktiven SQL-Modus beider Server lesen
Erst nachsehen, bevor jemand den Umzug als Ursache abstreitet. Die Sitzungsvariable sql_mode zeigt die aktiven Modi als kommagetrennte Liste; im Handbuch erscheint dort zum Beispiel ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION. Taucht ONLY_FULL_GROUP_BY auf dem neuen Server auf und auf dem alten nicht, ist die Ursache gefunden. Die globale Variable lässt sich genauso abfragen; Unterschiede zwischen beiden weisen auf eine Anwendung hin, die den Modus pro Sitzung setzt.
SELECT @@session.sql_mode;
SELECT @@global.sql_mode;
2.Die Fehlermeldung bis zum Ende lesen
Die Meldung nennt die Nummer des Ausdrucks im SELECT und die vollständige Spalte, die das Problem macht, etwa Expression #2 of SELECT list mit der Spalte mydb.t.address. Das ist kein allgemeiner Hinweis, sondern der genaue Übeltäter. Fehlerreferenz: ER_WRONG_FIELD_WITH_GROUP, SQLSTATE 42000. Bei langen Abfragen mit zehn Spalten erspart das die Suche: Ausdruck Nummer zwei zählen, Spalte prüfen, fertig. Taucht nach dem Beheben der nächste Fehler 1055 mit einer anderen Nummer auf, ist das kein Rückschritt, sondern die nächste Spalte derselben Sorte.
3.Entscheiden, ob die Spalte wirklich abhängig ist
Für jede beanstandete Spalte die Frage stellen: Bestimmt die Gruppierungsspalte diesen Wert eindeutig? Das Handbuch nennt als erkannte Fälle den Primärschlüssel und eine eindeutige NOT-NULL-Spalte. Gruppiert die Abfrage nach einem Fremdschlüssel aus einer anderen Tabelle, erkennt MySQL die Abhängigkeit über den Schlüssel der verknüpften Tabelle. Hängt der Wert dagegen nur nach Gefühl mit der Gruppe zusammen, etwa die E-Mail-Adresse des Kunden, gruppiert nach Kundenname, dann ist er es nicht, und der alte Server hat geraten.
SHOW CREATE TABLE t\G
4.Die Abfrage korrigieren statt den Modus
Drei saubere Wege, in dieser Reihenfolge: Die Spalte in das GROUP BY aufnehmen, wenn sie zur Gruppe gehört. Die Spalte aggregieren (MAX, MIN, COUNT), wenn ein Kennwert gemeint ist. Oder nach dem Primärschlüssel gruppieren statt nach einer beliebigen Spalte, damit MySQL die Abhängigkeit der übrigen Spalten erkennt. Der vierte Weg ist ANY_VALUE() und gehört nur dorthin, wo nachweislich alle Werte je Gruppe gleich sind oder es wirklich egal ist, welcher kommt. Das Handbuch zeigt genau diese Form: SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name.
-- vorher (Fehler 1055)
SELECT name, address, MAX(age) FROM t GROUP BY name;
-- Variante 1: Spalte gehört zur Gruppe
SELECT name, address, MAX(age) FROM t GROUP BY name, address;
-- Variante 2: bewusst beliebiger Wert
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
5.Den Modus nur als letzten Ausweg und nur sichtbar abschalten
Wenn eine fremde Anwendung hunderte solcher Abfragen mitbringt und nicht angepasst werden kann, bleibt das Abschalten des Modus. Dann aber mit Augenmaß: pro Sitzung über SET SESSION sql_mode, damit nur diese Anwendung betroffen ist, nicht die ganze Datenbank. Das Handbuch zeigt für das Setzen die Funktion sys.list_add, mit der sich ein einzelner Modus zur bestehenden Liste hinzufügen lässt; zum Entfernen gibt es das Gegenstück sys.list_drop im sys-Schema. Wichtig ist, die Liste nicht komplett zu überschreiben, sonst verschwinden mit ONLY_FULL_GROUP_BY auch STRICT_TRANS_TABLES und die anderen Voreinstellungen.
SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');
SELECT @@session.sql_mode;