Błąd związany z only_full_group_by podczas wykonywania zapytania w MySql

Zaktualizowałem system i zainstalowałem MySql 5.7.9 z php dla aplikacji internetowej, nad którą pracuję. Mam zapytanie, które jest tworzone dynamicznie, a kiedy działa w starszych wersjach MySql, działa dobrze. Od aktualizacji do 5.7 pojawia się ten błąd:

Wyrażenie nr 1 listy SELECT nie znajduje się w klauzuli GROUP BY i zawiera niezagregowaną kolumnę „support_desk.mod_users_groups.group_id”, która nie jest funkcjonalnie zależna od kolumn w klauzuli GROUP BY; jest to niezgodne z trybem_sql = only_full_group_by

Zwróć uwagę na stronę Podręcznik dla MySQL 5.7 na temat trybów SQL serwera .

Oto pytanie, które sprawia mi problemy:

SELECT mod_users_groups.group_id AS 'value', 
       group_name AS 'text' 
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id 
WHERE  mod_users_groups.active = 1 
  AND mod_users_groups.department_id = 1 
  AND mod_users_groups.manage_work_orders = 1 
  AND group_name != 'root' 
  AND group_name != 'superuser' 
GROUP BY group_name 
HAVING COUNT(`user_id`) > 0 
ORDER BY group_name

Zrobiłem trochę googling na ten temat, ale nie rozumiem only_full_group_bywystarczająco dużo, aby dowiedzieć się, co muszę zrobić, aby naprawić zapytanie. Czy mogę po prostu wyłączyć tę only_full_group_byopcję, czy jest coś jeszcze, co muszę zrobić?

Daj mi znać, jeśli potrzebujesz więcej informacji.

Chciałbym tylko dodać group_iddo GROUP BY.

Kiedy SELECTing kolumnę, która nie jest częścią GROUP BYmoże istnieć wiele wartości dla tej kolumny w obrębie grupy, ale nie będzie tylko przestrzeń dla pojedynczej wartości w wynikach. Tak więc baza danych zwykle wymaga dokładnego powiedzenia, jak przekształcić te wielokrotne wartości w jedną wartość. Zwykle odbywa się to z zagregowanej funkcji takich jak COUNT(), SUM(), MAX()itp ... mówię zwykle , ponieważ większość innych popularnych systemów baz danych nalegać na to. Jednak w MySQL przed wersją 5.7 zachowanie domyślne było bardziej wybaczające, ponieważ nie narzeka, a następnie arbitralnie wybiera dowolną wartość ! Ma równieżANY_VALUE()funkcja, która może być wykorzystana jako inne rozwiązanie tego pytania, jeśli naprawdę potrzebujesz takiego samego zachowania jak wcześniej. Ta elastyczność wiąże się z pewnym kosztem, ponieważ jest niedeterministyczna, więc nie poleciłbym jej, chyba że masz bardzo dobry powód, aby jej potrzebować. MySQL teraz only_full_group_bydomyślnie włącza to ustawienie z dobrych powodów, więc najlepiej jest się do tego przyzwyczaić i dostosować zapytania.

Więc dlaczego moja prosta odpowiedź powyżej? Podjąłem kilka założeń:

1) group_idjest wyjątkowy. Wydaje się rozsądne, w końcu jest to „dowód osobisty”.

2) group_namejest również wyjątkowy. To może nie być tak rozsądne założenie. Jeśli tak nie jest i masz trochę duplikatów, group_namesa następnie postępujesz zgodnie z moją radą, aby dodać group_iddo GROUP BY, może się okazać, że teraz otrzymujesz więcej wyników niż wcześniej, ponieważ grupy o tej samej nazwie będą teraz miały osobne wiersze w wynikach. Dla mnie byłoby to lepsze niż ukrycie tych zduplikowanych grup, ponieważ baza danych po cichu wybrała dowolną wartość!

Dobrą praktyką jest również kwalifikowanie wszystkich kolumn za pomocą nazwy tabeli lub aliasu, gdy w grę wchodzi więcej niż jedna tabela ...

SELECT 
  g.group_id AS 'value', 
  g.group_name AS 'text' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name

Możesz spróbować wyłączyć to only_full_group_byustawienie, wykonując następujące czynności:

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

MySQL 8 nie akceptuje, NO_AUTO_CREATE_USERwięc należy go usunąć.

możesz wyłączyć komunikat ostrzegawczy, jak wyjaśniono w innych odpowiedziach, lub zrozumieć, co się dzieje, i naprawić.

Począwszy od MySQL 5.7.5, domyślny tryb SQL zawiera ONLY_FULL_GROUP_BY, co oznacza, że ​​kiedy grupujesz wiersze, a następnie wybierasz coś z tych grup, musisz wyraźnie powiedzieć, z którego wiersza należy dokonać tego wyboru.

Mysql musi wiedzieć, którego wiersza w grupie szukasz, co daje dwie opcje

• Możesz także dodać kolumnę, którą chcesz, do instrukcji grupy, group by rect.color, rect.valuektóra może być tym, czego chcesz w niektórych przypadkach, w przeciwnym razie zwrócą duplikaty wyników w tym samym kolorze, którego możesz nie chcieć
• możesz także użyć funkcji agregujących mysql, aby wskazać, którego wiersza szukasz w grupach, np. AVG() MIN() MAX() pełna lista
• I wreszcie możesz użyć, ANY_VALUE()jeśli masz pewność, że wszystkie wyniki w grupie są takie same. dok

Jeśli nie chcesz wprowadzać żadnych zmian w bieżącym zapytaniu, wykonaj następujące czynności -

1. włóczęga ssh do twojego pudełka
2. Rodzaj: sudo vim /etc/mysql/my.cnf
3. Przewiń do końca pliku i wpisz, Aaby przejść do trybu wstawiania

4. Kopiuj i wklej

   [mysqld]
   sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

5. Wpisz, escaby wyjść z trybu wprowadzania

6. Wpisz, :wqaby zapisać i zamknąć vim.
7. Wpisz, sudo service mysql restartaby zrestartować MySQL.

Użyj, ANY_VALUE()aby odnieść się do niezagregowanej kolumny.

SELECT name,           address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works

Z dokumentów MySQL 5.7 :

Możesz osiągnąć ten sam efekt bez wyłączania ONLY_FULL_GROUP_BY , używając ANY_VALUE()odniesienia do niezagregowanej kolumny.

...

To zapytanie może być nieprawidłowe z ONLY_FULL_GROUP_BYwłączonym, ponieważ kolumna niezagregowanego adresu na liście wyboru nie jest wymieniona w GROUP BYklauzuli:

SELECT name, address, MAX(age) FROM t GROUP BY name;

...

Jeśli wiesz, że dla danego zestawu danych każda wartość nazwy w rzeczywistości jednoznacznie określa wartość adresu, adres jest faktycznie funkcjonalnie zależny od nazwy. Aby powiedzieć MySQLowi, aby zaakceptował zapytanie, możesz użyć ANY_VALUE()funkcji:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Spróbuję wyjaśnić ci, na czym polega ten błąd.
Począwszy od MySQL 5.7.5, opcja ONLY_FULL_GROUP_BYjest domyślnie włączona.
Zatem zgodnie ze standardowym SQL92 i wcześniejszymi wersjami:

nie zezwala na zapytania, dla których lista wyboru, warunek HAVING lub lista ORDER BY odnoszą się do niezagregowanych kolumn, które nie są nazwane w klauzuli GROUP BY ani nie są funkcjonalnie zależne (jednoznacznie określone przez) kolumny GROUP BY

( czytaj więcej w dokumentach )

Na przykład:

SELECT * FROM `users` GROUP BY `name`;

Po wykonaniu zapytania powyżej pojawi się komunikat o błędzie.

# 1055 - Wyrażenie nr 1 listy SELECT nie znajduje się w klauzuli GROUP BY i zawiera niezagregowaną kolumnę „testsite.user.id”, która nie jest funkcjonalnie zależna od kolumn w klauzuli GROUP BY; jest to niezgodne z trybem_sql = only_full_group_by

Dlaczego?
Ponieważ MySQL nie rozumie dokładnie, jakie pewne wartości z zgrupowanych rekordów pobrać, i o to właśnie chodzi.

IE pozwala powiedzieć, że masz te rekordy w swojej userstabeli:

I wykonasz nieprawidłowe zapytanie pokazane powyżej.
Pojawi się błąd pokazany powyżej, ponieważ istnieją 3 rekordy z nazwą Johni jest to miłe, ale wszystkie mają różne emailwartości pól.
Tak więc MySQL po prostu nie rozumie, który z nich zwrócić w wynikowym zgrupowanym rekordzie.

Możesz rozwiązać ten problem, po prostu zmieniając zapytanie w następujący sposób:

SELECT `name` FROM `users` GROUP BY `name`

Możesz także chcieć dodać więcej pól do sekcji SELECT, ale nie możesz tego zrobić, jeśli nie są agregowane, ale istnieje kula, której możesz użyć (ale zdecydowanie nie jest to zalecane):

SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`

Teraz możesz zapytać, dlaczego używanie nie ANY_VALUEjest wysoce zalecane?
Ponieważ MySQL nie do końca wie, jaką wartość zgrupowanych rekordów pobrać, a korzystając z tej funkcji poprosisz ją o pobranie dowolnego z nich (w tym przypadku pobrano e-maila z pierwszym rekordem o nazwie = John).
Dokładnie nie mogę wymyślić żadnych pomysłów, dlaczego chcesz, aby takie zachowanie istniało.

Jeśli mnie nie rozumiesz, przeczytaj więcej o tym, jak działa grupowanie w MySQL, jest to bardzo proste.

I na koniec, oto jeszcze jedno proste, ale prawidłowe zapytanie.
Jeśli chcesz sprawdzić całkowitą liczbę użytkowników według dostępnych grup wiekowych, możesz zapisać to zapytanie

SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;

Co jest w pełni poprawne, zgodnie z zasadami MySQL.
I tak dalej.

Ważne jest, aby zrozumieć, na czym dokładnie polega problem, i dopiero wtedy zanotuj rozwiązanie.

Korzystam z Laravel 5.3, mysql 5.7.12, na laravel homestead (0,5.0, jak sądzę)

Nawet po jawnym ustawieniu edycji w /etc/mysql/my.cnfcelu odzwierciedlenia:

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Nadal otrzymywałem błąd.

Musiałem zmienić config/database.phpz truena false:

    'mysql' => [
        'strict' => false, //behave like 5.6
        //'strict' => true //behave like 5.7
    ], 

Dalsza lektura:

https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel -5-2

Jeśli używasz wamp 3.0.6 lub dowolnej wyższej wersji innej niż stabilna 2.5, możesz napotkać ten problem, po pierwsze, problem dotyczy sql. musisz odpowiednio nazwać pola. ale jest inny sposób na rozwiązanie tego problemu. kliknij zieloną ikonę wampa. mysql-> ustawienia mysql-> tryb_sql-> brak. lub z konsoli możesz zmienić wartości domyślne.

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Dodanie linii (wymienionych poniżej) w pliku: /etc/mysql/my.cnf

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Pracuj dobrze dla mnie. Wersja serwera: 5.7.18-0ubuntu0.16.04.1 - (Ubuntu)

Przejdź do mysql lub phpmyadmin i wybierz bazę danych, a następnie po prostu uruchom to zapytanie i będzie działać. Dla mnie działa dobrze.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Dla Maca:

1. Skopiuj domyślny plik my-default.cnf do /etc/my.cnf

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

2.Zmień tryb sql_mode w pliku my.cnf za pomocą swojego ulubionego edytora i ustaw go w ten sposób

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

3. Uruchom ponownie serwer MySQL.

mysql.server restart

To pomogło mi zrozumieć cały problem:

1. https://stackoverflow.com/a/20074634/1066234
2. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

A w kolejnym przykładzie problematycznego zapytania.

Problematyczny:

SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
                        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
                        AND cookieid = #

Rozwiązany przez dodanie tego na końcu:

  GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress

Uwaga: Zobacz komunikat o błędzie w PHP, który informuje, gdzie leży problem.

Przykład:

Błąd zapytania MySQL 1140: W zagregowanym zapytaniu bez GROUP BY, wyrażenie nr 4 z listy SELECT zawiera niezagregowaną kolumnę „db.gameplay.timestamp”; jest to niezgodne z trybem sql = only_full_group_by - Zapytanie: WYBIERZ LICZBĘ (*) jako próby, SUM (upłynął) jako upłynął suma, identyfikator użytkownika, znacznik czasu, pytajnik, odpowiedź, SUM (poprawny) jako poprawny, upłynął, adres IP Z gry GDZIE znacznik czasu> = DATA_SUB (TERAZ (), INTERWAŁ 1 DZIEŃ) ORAZ identyfikator użytkownika = 1

W tym przypadku brakowało wyrażenia # 4 w GROUP BY.

Dla localhost / wampserver 3 możemy ustawić tryb sql = tryb_użytkownika, aby usunąć ten błąd:

click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode

następnie uruchom ponownie wamp lub apache

Możesz dodać unique indexdogroup_id ; jeśli jesteś pewien, że group_idjest wyjątkowy.

Może rozwiązać twoją sprawę bez zmiany zapytania .

Późna odpowiedź, ale nie została jeszcze wspomniana w odpowiedziach. Może powinien wypełnić wyczerpujące już dostępne odpowiedzi. Przynajmniej rozwiązało to mój przypadek, gdy musiałem podzielić tabelę ze zbyt dużą liczbą pól.

Jeśli masz ten błąd w Symfony używającym konstruktora zapytań doktrynowych i jeśli błąd ten jest spowodowany przez orderBy :

Zwróć uwagę selectna kolumnę, którą chcesz groupBy, i użyj addGroupByzamiast groupBy:

$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');

Działa na Symfony3 -

Przepraszamy za nieużywanie dokładnego kodu SQL

Użyłem tego zapytania, aby pokonać ostrzeżenie MySQL.

SELECT count(*) AS cnt, `regions_id`
FROM regionables 
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1

zwróć uwagę na klucz dla mnie

count(*) AS cnt