Jeg har oppgradert systemet mitt og har installert MySql 5.7.9 med php for en webapplikasjon jeg jobber med. Jeg har en spørring som er dynamisk opprettet, og når den kjøres i eldre versjoner av MySql fungerer den fint. Siden oppgradering til 5.7 får jeg denne feilen:
Uttrykk nr. 1 i SELECT-listen er ikke i GROUP BY-klausulen og inneholder ikke-aggregert kolonne 'support_desk.mod_users_groups.group_id' som er ikke funksjonelt avhengig av kolonner i GROUP BY-klausulen; dette er uforenlig med sql_mode=only_full_group_by
Legg merke til Manual-siden for Mysql 5.7 om emnet Server SQL Modes.
Dette er spørringen som gir meg problemer:
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
Jeg googlet litt på problemet, men jeg forstår ikke only_full_group_by
nok til å finne ut hva jeg trenger å gjøre for å fikse spørringen. Kan jeg bare slå av only_full_group_by
-alternativet, eller er det noe annet jeg må gjøre?
Gi meg beskjed hvis du trenger mer informasjon.
Du kan prøve å deaktivere innstillingen only_full_group_by
ved å utføre følgende:
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 aksepterer ikke NO_AUTO_CREATE_USER
, så den må fjernes.
Jeg ville bare legge til group_id
i GROUP BY
.
Når SELECT
velger en kolonne som ikke er en del av GROUP BY
, kan det være flere verdier for den kolonnen i gruppene, men det vil bare være plass til en enkelt verdi i resultatene. Databasen må derfor vanligvis få beskjed om nøyaktig hvordan de flere verdiene skal slås sammen til én verdi. Vanligvis gjøres dette med en aggregeringsfunksjon som COUNT()
, SUM()
, MAX()
etc.... Jeg sier vanligvis fordi de fleste andre populære databasesystemer insisterer på dette. I MySQL før versjon 5.7 har imidlertid standardoppførselen vært mer tilgivende fordi den ikke vil klage og deretter vilkårlig velge hvilken som helst verdi ! Den har også en ANY_VALUE()
-funksjon som kan brukes som en annen løsning på dette spørsmålet hvis du virkelig trenger samme oppførsel som før. Denne fleksibiliteten har en kostnad fordi den er ikke-deterministisk, så jeg vil ikke anbefale den med mindre du har en veldig god grunn til å trenge den. MySQL slår nå på only_full_group_by
-innstillingen som standard av gode grunner, så det er best å bli vant til det og få spørsmålene dine til å overholde det.
Så hvorfor mitt enkle svar ovenfor? Jeg har gjort et par antagelser:
group_id
er unik. Virker rimelig, det er tross alt en 'ID'.
"group_name" er også unikt. Dette er kanskje ikke en så rimelig antagelse. Hvis dette ikke er tilfelle og du har noen dupliserte group_names
og du deretter følger mitt råd om å legge til group_id
til GROUP BY
, kan du oppleve at du nå får flere resultater enn før fordi gruppene med samme navn nå vil ha separate rader i resultatene. For meg ville dette være bedre enn å ha disse dupliserte gruppene skjult fordi databasen har valgt en verdi vilkårlig!
Det er også god praksis å kvalifisere alle kolonnene med deres tabellnavn eller alias når det er mer enn én tabell involvert ...
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
Hvis du ikke vil gjøre noen endringer i den aktuelle forespørselen din, følger du trinnene nedenfor.
vagrant ssh inn i boksen din
Skriv inn: 'sudo vim /etc/mysql/my.cnf'
Bla til bunnen av filen og skriv A
for å gå inn i innsettingsmodus
Kopier og lim inn
[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
Skriv esc
for å avslutte inndatamodus
Skriv :wq
for å lagre og lukke vim.
Skriv sudo service mysql restart
for å starte MySQL på nytt.