Ik heb mijn systeem geupgrade en heb MySql 5.7.9 met php geïnstalleerd voor een webapplicatie waar ik aan werk. Ik heb een query die dynamisch wordt aangemaakt, en wanneer deze wordt uitgevoerd in oudere versies van MySql werkt deze prima. Sinds de upgrade naar 5.7 krijg ik deze foutmelding:
Expression #1 van SELECT list is niet in GROUP BY clause en bevat
niet-geaggregeerde kolom 'support_desk.mod_users_groups.group_id' die is niet functioneel afhankelijk is van kolommen in GROUP BY-clausule; dit is onverenigbaar met sql_mode=only_full_group_by
Zie de Manual pagina voor Mysql 5.7 over het onderwerp Server SQL Modes.
Dit is de query die mij problemen geeft:
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
Ik heb wat gegoogeld op het probleem, maar ik begrijp only_full_group_by
niet goed genoeg om uit te vinden wat ik moet doen om de query te repareren. Kan ik gewoon de only_full_group_by
optie uitzetten, of is er iets anders wat ik moet doen?
Laat het me weten als je meer informatie nodig hebt.
U kunt proberen om de only_full_group_by
instelling uit te schakelen door het volgende uit te voeren:
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 accepteert NO_AUTO_CREATE_USER
niet, dus dat moet verwijderd worden.
Ik zou gewoon group_id
toevoegen aan de GROUP BY
.
Bij het SELECTEREN
van een kolom die geen deel uitmaakt van de GROUP BY
kunnen er meerdere waarden voor die kolom zijn binnen de groepen, maar er zal slechts ruimte zijn voor een enkele waarde in de resultaten. Dus, de database moet meestal precies verteld worden hoe van die meerdere waarden één waarde te maken. Meestal wordt dit gedaan met een aggregatie functie zoals COUNT()
, SUM()
, MAX()
enz... Ik zeg gewoonlijk omdat de meeste andere populaire database systemen hier op staan. Echter, in MySQL vóór versie 5.7 is het standaard gedrag meer vergevingsgezind omdat het niet zal klagen en dan willekeurig elke waarde zal kiezen! Het heeft ook een ANY_VALUE()
functie die gebruikt kan worden als een andere oplossing voor deze vraag als je echt hetzelfde gedrag als voorheen nodig hebt. Deze flexibiliteit komt met een prijs omdat het niet-deterministisch is, dus ik zou het niet aanraden, tenzij je een zeer goede reden hebt om het nodig te hebben. MySQL zet nu de only_full_group_by
instelling standaard aan om goede redenen, dus het's het beste om er aan te wennen en uw queries er aan te laten voldoen.
Dus waarom mijn eenvoudige antwoord hierboven? Ik'heb een paar veronderstellingen gemaakt:
de group_id
is uniek. Lijkt redelijk, het is immers een 'ID'.
de groeps_naam
is ook uniek. Dit is misschien niet zo'n redelijke aanname. Als dit niet het geval is en je hebt een aantal dubbele group_names
en je volgt dan mijn advies op om group_id
toe te voegen aan de GROUP BY
, dan kan het zijn dat je nu meer resultaten krijgt dan voorheen omdat de groepen met dezelfde naam nu aparte rijen in de resultaten krijgen. Voor mij zou dit beter zijn dan dat deze dubbele groepen verborgen blijven omdat de database stilletjes een waarde willekeurig heeft geselecteerd!
Het is ook een goede gewoonte om alle kolommen te kwalificeren met hun tabelnaam of alias wanneer er meer dan één tabel bij betrokken is...
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
Als u geen veranderingen wilt aanbrengen in uw huidige query, volg dan de onderstaande stappen -
vagrant ssh in uw doos
Type: sudo vim /etc/mysql/my.cnf
Scroll naar de onderkant van het bestand en type A
om in insert mode te gaan
Kopieer en plak
[mysqld] sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Type esc
om de invoermodus te verlaten
Type :wq
om op te slaan en vim te sluiten.
Type sudo service mysql restart
om MySQL opnieuw te starten.