[MySQL] Co zrobić z błędem „Error caused by sql_mode=only_full_group_by”
![[MySQL] Co zrobić z błędem „Error caused by sql_mode=only_full_group_by” [MySQL] Co zrobić z błędem „Error caused by sql_mode=only_full_group_by”](https://porady-it.pl/wp-content/uploads/2015/01/mysql.png)
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.
SELECT country, city, COUNT(id) AS quantity FROM shops GROUP BY city
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:
SELECT country, city, COUNT(id) AS quantity FROM shops GROUP BY country, city
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