Błąd związany tylko z pełną grupą przez podczas wykonywania zapytania w MySql
Zaktualizowałem swój system i zainstalowałem MySql 5.7.9 z php dla aplikacji internetowej, nad którą pracuję. Mam pytanie, które jest dynamicznie tworzone, a po uruchomieniu w starszych wersjach MySql działa dobrze. Od aktualizacji do 5.7 dostaję ten błąd:
Wyrażenie # 1 z listy SELECT nie znajduje się w klauzuli GROUP BY i zawiera kolumna nieagregowana " support_desk.mod_users_groups.group_id " czyli nie zależny funkcjonalnie od kolumn w klauzuli GROUP BY; to na niezgodny z sql_mode=only_full_group_by
Zwróć uwagę na stronę podręcznika dla Mysql 5.7 w temacie Server SQL Modes.
To jest zapytanie, które sprawia mi kłopoty:
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
Trochę googlowałem na ten temat, ale nie rozumiem only_full_group_by
wystarczająco, aby dowiedzieć się, co muszę zrobić, aby naprawić zapytanie. Czy mogę po prostu wyłączyć opcję only_full_group_by
, czy jest coś innego, co muszę zrobić?
18 answers
Dodałbym tylko group_id
do GROUP BY
.
Gdy SELECT
w kolumnie, która nie jest częścią GROUP BY
, może być wiele wartości dla tej kolumny w grupach, ale w wynikach będzie tylko miejsce na pojedynczą wartość. Tak więc, baza danych zazwyczaj musi być dokładnie poinformowana, jak zrobić te wiele wartości w jedną wartość. Zwykle odbywa się to za pomocą funkcji agregującej, takiej jak COUNT()
, SUM()
, MAX()
itd... Mówię zazwyczaj ponieważ większość innych popularnych baz systemy nalegają na to. Jednak w MySQL przed wersją 5.7 domyślne zachowanie było bardziej wyrozumiałe, ponieważ nie będzie narzekać, a następnie arbitralnie wybrać dowolną wartość! Posiada również funkcję ANY_VALUE()
, która może być użyta jako inne rozwiązanie tego pytania, jeśli naprawdę potrzebujesz takiego samego zachowania, jak wcześniej. Ta elastyczność ma swoją cenę, ponieważ nie jest deterministyczna, więc nie polecam jej, chyba że masz bardzo dobry powód, aby jej potrzebować. MySQL się włącza domyślne ustawienie only_full_group_by
z dobrych powodów, więc najlepiej przyzwyczaić się do niego i sprawić, by Twoje zapytania były zgodne z nim.
Więc dlaczego moja prosta odpowiedź powyżej? Zrobiłem kilka założeń:
1) group_id
jest unikalny. Wydaje się rozsądne, w końcu jest to "ID".
2) {[11] } jest również wyjątkowy. To może nie być takie rozsądne założenie. Jeśli tak nie jest i masz jakiś duplikat group_names
, a następnie postępuj zgodnie z moją radą, aby dodać group_id
do GROUP BY
, może się okazać, że teraz otrzymasz więcej wyników niż wcześniej, ponieważ grupy o tej samej nazwie będą teraz miały oddzielne wiersze w wynikach. Dla mnie byłoby to lepsze niż ukrycie tych duplikatów grup, ponieważ baza danych po cichu wybrała wartość arbitralnie!
Dobrą praktyką jest również kwalifikowanie wszystkich kolumn nazwą lub aliasem tabeli, gdy dotyczy to więcej niż jednej tabeli...
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-03 16:01:02
Możesz spróbować wyłączyć ustawienie only_full_group_by
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_USER
więc trzeba to usunąć.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-11-13 18:10:32
Możesz wyłączyć komunikat ostrzegawczy, jak wyjaśniono w innych odpowiedziach lub zrozumieć co się dzieje i naprawić go.
Począwszy od MySQL 5.7.5, domyślny tryb SQL zawiera ONLY_FULL_GROUP_BY co oznacza, że gdy grupujesz wiersze, a następnie wybierasz coś z tych grup, musisz jawnie 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 również dodać żądaną kolumnę do instrukcji group
group by rect.color, rect.value
, która może być tym, czego chcesz w niektórych przypadkach, w przeciwnym razie zwróci zduplikowane wyniki o tym samym kolorze, którego możesz nie chcieć - możesz również użyć funkcji agregujących mysql, aby wskazać, którego wiersza szukasz w grupach takich jak
AVG()
MIN()
MAX()
complete list - i wreszcie możesz użyć
ANY_VALUE()
jeśli jesteś pewien, że wszystkie wyniki wewnątrz grupy są takie same. doc
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-12 15:31:52
Jeśli nie chcesz wprowadzać żadnych zmian w bieżącym zapytaniu, wykonaj poniższe kroki -
- vagrant SSH into your box
- Typ:
sudo vim /etc/mysql/my.cnf
- przewiń do dołu pliku i wpisz
A
, aby przejść do trybu wstawiania -
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
-
Type
esc
to exit input mode - wpisz
:wq
Aby zapisać i zamknąć Vima. - wpisz
sudo service mysql restart
aby ponownie uruchomić MySQL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-21 20:07:11
Użyj ANY_VALUE()
, aby odnieść się do kolumny nieagregowanej.
SELECT name, address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works
From MySQL 5.7 docs:
Możesz osiągnąć ten sam efekt bez wyłączania
ONLY_FULL_GROUP_BY
używającANY_VALUE()
w odniesieniu do kolumny nieagregowanej....
To zapytanie może być nieprawidłowe z włączoną
ONLY_FULL_GROUP_BY
, ponieważ kolumna adresu nieagregowanego na liście select nie jest nazwana w klauzuliGROUP BY
:SELECT name, address, MAX(age) FROM t GROUP BY name;
...
Jeśli wiesz, że, dla biorąc pod uwagę zbiór danych, każda wartość nazwy w rzeczywistości jednoznacznie określa wartość adresu, adres jest efektywnie funkcjonalnie zależny od nazwy. Aby powiedzieć MySQL, aby zaakceptował zapytanie, możesz użyć funkcji
ANY_VALUE()
:SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-11-19 17:05:12
Postaram się wyjaśnić, o co chodzi z tym błędem.
PoczÄ…wszy od MySQL 5.7.5, opcja ONLY_FULL_GROUP_BY
jest domyślnie włączona.
Tak więc, zgodnie ze standardem SQL92 i wcześniejszymi:
Nie pozwala na zapytania, dla których lista select, mając warunek, lub kolejność według listy odnoszą się do nieagregowanych kolumn, które nie są nazwane w grupie przez klauzulę nor są funkcjonalnie zależne od (jednoznacznie określona przez) grupa według kolumn
Więc, na przykład:
SELECT * FROM `users` GROUP BY `name`;
Po wykonaniu powyższego zapytania otrzymasz komunikat o błędzie.
Dlaczego?# 1055-wyrażenie # 1 z listy SELECT nie znajduje się w klauzuli GROUP BY i zawiera nieagregowaną kolumnę 'testsite.user.id" co nie jest funkcjonalnie zależny od kolumn w klauzuli GROUP BY; jest to niezgodny z sql_mode=only_full_group_by
Ponieważ MySQL nie rozumie dokładnie, jakie pewne wartości z pogrupowanych rekordów do / align = "left" /
Powiedzmy, że masz te rekordy w tabeli users
:
I wykonasz niepoprawne zapytanie pokazane powyżej.
I pojawi się błąd pokazany powyżej, ponieważ istnieją 3 Rekordy o nazwie John
, i jest to miłe, ale wszystkie z nich mają różne wartości pola email
.
Tak więc MySQL po prostu nie rozumie, który z nich zwrócić w wynikowym rekordzie zgrupowanym.
Możesz rozwiązać ten problem, po prostu zmiana zapytania w ten sposób:
SELECT `name` FROM `users` GROUP BY `name`
Możesz również dodać więcej pól do sekcji SELECT, ale nie możesz tego zrobić, jeśli nie są one zagregowane, ale istnieje crutch, którego możesz użyć (ale wysoce nie polecany):
SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`
Teraz możesz zapytać, dlaczego używanie ANY_VALUE
jest wysoce nie zalecane?
Ponieważ MySQL nie do końca wie, jaką wartość zgrupowanych rekordów pobierać, A korzystając z tej funkcji, prosi się go o pobranie dowolnego z nich (w tym przypadku, e-mail pierwszego rekordu z name = John został pobrany).
Dokładnie nie mogę wymyślić żadnego pomysłu, dlaczego chcesz, aby takie zachowanie istniało.
Proszę, Jeśli mnie nie rozumiesz, przeczytaj więcej o tym, jak grupowanie w MySQL działa, jest to bardzo proste.
I na koniec, oto jeszcze jedno proste, ale poprawne zapytanie.
Jeśli chcesz zapytać całkowitą liczbę użytkowników według dostępnego wieku, możesz zapisać to zapytanie
SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;
Który jest w pełni poprawny, zgodnie z MySQL Zasady.
I tak dalej.
Ważne jest, aby zrozumieć, na czym dokładnie polega problem i dopiero wtedy zapisać rozwiązanie.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-04-29 19:03:23
Używam Laravel 5.3, mysql 5.7.12, na Laravel homestead (0.5.0, I believe)
Nawet po jawnym ustawieniu edycji /etc/mysql/my.cnf
na odzwierciedlenie:
[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
Wciąż otrzymywałem błąd.
Musiałem zmienić config/database.php
z true
na false
:
'mysql' => [
'strict' => false, //behave like 5.6
//'strict' => true //behave like 5.7
],
Czytaj dalej:
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-31 14:09:11
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, w jaki możesz to rozwiązać. kliknij na zieloną ikonę wamp. mysql - > mysql settings - > sql_mode - > none. lub z konsoli można 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';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-21 14:58:07
Przejdź do mysql lub phpmyadmin i wybierz bazę danych następnie po prostu wykonaj to zapytanie i będzie działać. To działa dobrze dla mnie.
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-10-11 10:44:48
Dodanie linii (wzmianka 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
Dla mnie działa dobrze.
Wersja serwera: 5.7.18-0ubuntu0. 16. 04. 1 - (Ubuntu)Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-06-20 10:34:35
Dla mac:
1.Skopiuj domyślne my-default.cnf do / etc / my.cnf
sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf
2.Zmień sql_mode w moim.cnf używając swojego ulubionego edytora i ustaw go na to
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-11-17 16:43:03
To właśnie pomogło mi zrozumieć cały problem:
- https://stackoverflow.com/a/20074634/1066234
- https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
I w kolejnym przykładzie problematycznego zapytania.
Problematyczne:
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ązano dodając to do końca:
GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress
Uwaga: Zobacz komunikat o błędzie w PHP, mówi, gdzie problem kłamstwa.
Przykład:
Błąd zapytania MySQL 1140: w zapytaniu zbiorczym bez grupy BY wyrażenie # 4 z listy SELECT zawiera nieagregowaną kolumnę ' db.rozgrywki.timestamp'; jest to niezgodne z sql_mode=only_full_group_by - Query: SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM (correct) as correct, elapsed, ipaddress FROM gameplay Gdzie timestamp > = DATE_SUB (NOW (), interwał 1 dzień) AND userid = 1
W tym przypadku, wyrażenie #4 brakowało w grupie BY.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:34:47
Dla localhost / wampserver 3 możemy ustawić SQL-mode = user_mode aby usunąć ten błąd:
click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode
Następnie uruchom ponownie wamp lub apache
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-01-14 17:47:08
Możesz dodać unique index
do group_id
; jeśli jesteś pewien, że group_id
jest unikalna.
Może rozwiązać Twoją sprawę bez modyfikowania zapytania .
Odpowiedź spóźniona, ale nie została jeszcze wymieniona w odpowiedziach. Może powinien uzupełnić już wyczerpujące odpowiedzi dostępne. Przynajmniej to rozwiązało moją sprawę, kiedy musiałem podzielić tabelę ze zbyt wieloma polami.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-10 16:07:10
Jeśli masz ten błąd z Symfony używając doctrine query builder , i jeśli ten błąd jest spowodowany przez orderBy:
Zwróć uwagę na select
kolumnę, którą chcesz groupBy
, i użyj addGroupBy
zamiast groupBy
:
$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');
Działa na Symfony3 -
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-09 12:30:50
Przepraszamy za nieużywanie dokładnego 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ę, że klucz do mnie jest
count(*) AS cnt
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-03-02 03:03:42
Musiałem edytować poniższy plik na moim Ubuntu 18.04:
/etc/mysql/mysql.conf.d/mysqld.cnf
Z
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
I
sudo service mysql restart
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-07-04 15:55:55
Powyższa odpowiedź jest dobra, ale jeśli masz problemy z uruchamianiem zapytań w procedurach przechowywanych po naprawieniu swojego my.plik cnf, a następnie spróbuj ponownie załadować SPs.
Podejrzewam, że MySQL musiał skompilować SPS z domyślnym only_full_group_by ustawionym pierwotnie. Dlatego, nawet kiedy zmieniłem swoje.cnf i ponownie uruchomiony mysqld nie miał wpływu na SPs, i nadal nie udało im się z " SELECT list nie jest w klauzuli GROUP BY i zawiera nieagregowaną kolumnę ... który nie jest funkcjonalny zależne od kolumn w klauzuli GROUP BY; jest to niezgodne z sql_mode=only_full_group_by".
Ponowne załadowanie SPs musiało spowodować ich rekompilację z wyłączonym only_full_group_by. Po tym, wydają się działać zgodnie z oczekiwaniami.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-08-11 14:42:05