[MySQL] Co zrobić z błędem „Error caused by sql_mode=only_full_group_by”

Jeśli dokonaliście aktualizacji MySQL-a do wersji 5.7.5+ i zauważyliście, że część waszych zapytań wykorzystująca GROUP BY przestała działać to powinniście wiedzieć, że jest to spowodowane zmianami jakie zaszły w MySQL-u.

Zachęcam do zapoznania się bliżej z tematem pod linkiem: https://dev.mysql.com/../faqs-sql-modes.html

The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

The ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES modes were added in MySQL 5.7.5. The NO_AUTO_CREATE_USER mode was added in MySQL 5.7.7. The ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes were added in MySQL 5.7.8. For information about all available modes and MySQL’s default behavior, see Section 5.1.7, “Server SQL Modes”.

Co oznacza ONLY_FULL_GROUP_BY zostało wyjaśnione:

Reject 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 (uniquely determined by) GROUP BY columns.

Ale o co chodzi ?

Jako, że nie każdy przepada za czytaniem dokumentacji to może sam problem zilustruję prostym przykładem.

<br />
SELECT country, city, COUNT(id) AS quantity<br />
FROM shops<br />
GROUP BY city<br />

Powyższy kod jak widać grupuje sklepy po nazwie miasta i liczy ile ich jest dla danego miasta. W poprzednich wersjach MySQL-a to zapytanie nie sprawia żadnego problemu, w kolumnie:

• country otrzymujemy nazwę kraju,
• city nazwę miasta,
• quantity ilość sklepów w mieście

Nowa wersja MySQL-a nie jest jednak tak łaskawa i oczekuje, że w zapytaniach agregujących będziemy odwoływali się w klauzuli SELECT tylko do kolumn po których grupujemy lub które wykorzystywane są przez funkcje agregujące np. COUNT.

Tak więc w tym przypadku problemem jest kolumna country, po której nie grupujemy, a została wykorzystania w klauzuli SELECT.

Poprawne zapytanie by wyglądało następująco:

<br />
SELECT country, city, COUNT(id) AS quantity<br />
FROM shops<br />
GROUP BY country, city<br />

Rozwiązanie

Rozwiązaniem problemu będzie poprawienie zapytań lub modyfikacja konfiguracji serwera. Jako że same zapytania są uzależnione od schematu bazy danych to pokażę wam jak zmodyfikować ustawienia serwera, aby stare wersje zapytań działały 🙂

W tym celu dokonamy modyfikacji w pliku my.cnf, w zależności od dystrybucji plik ten może znajdować się w nieco innych lokalizacjach. Na potrzeby tego rozwiązania założymy że znajduje się on w lokalizacji /etc/my.cnf.

Krok 1 – Edycja pliku

Z poziomu konsoli edytujemy plik konfiguracyjny serwera MySQL.

sudo nano /etc/my.cnf

Ja do edycji pliku wykorzystałem edytor nano jednak śmiało możecie skorzystać z waszego ulubionego edytora.

Krok 2 – Zmiana ustawień

Odnajdujemy linię z wpisem sql_mode i modyfikujemy ją jak poniżej. Jeśli nie znajdziemy lini z wpisem sql_mode to go dodajemy.

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Krok 3 – Restart serwera

Na sam koniem restartujemy serwer poleceniem:

sudo /etc/init.d/mysql restart