Ho aggiornato il mio sistema e ho installato MySql 5.7.9 con php per un'applicazione web su cui sto lavorando. Ho una query che viene creata dinamicamente, e quando viene eseguita nelle vecchie versioni di MySql funziona bene. Dall'aggiornamento alla 5.7 ottengo questo errore:
L'espressione #1 dell'elenco SELECT non è nella clausola GROUP BY e contiene colonna non aggregata 'support_desk.mod_users_groups.group_id' che è non dipende funzionalmente dalle colonne nella clausola GROUP BY; questo è incompatibile con sql_mode=only_full_group_by
Notate la pagina del manuale per Mysql 5.7 sull'argomento Server SQL Modes.
Questa è la query che mi sta dando problemi:
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
Ho fatto un po' di ricerche su Google sul problema, ma non capisco only_full_group_by
abbastanza per capire cosa devo fare per risolvere la query. Posso semplicemente disattivare l'opzione only_full_group_by
o c'è qualcos'altro che devo fare?
Fammi sapere se hai bisogno di altre informazioni.
Potete provare a disabilitare l'impostazione only_full_group_by
eseguendo quanto segue:
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 non accetta NO_AUTO_CREATE_USER
quindi deve essere rimosso.
Aggiungerei semplicemente group_id
al GROUP BY
.
Quando si Seleziona
una colonna che non fa parte del GROUP BY
ci potrebbero essere più valori per quella colonna all'interno dei gruppi, ma ci sarà spazio solo per un singolo valore nei risultati. Quindi, il database di solito ha bisogno di sapere esattamente come rendere quei valori multipli in un unico valore. Comunemente, questo viene fatto con una funzione aggregata come COUNT()
, SUM()
, MAX()
ecc. Dico di solito perché la maggior parte degli altri sistemi di database popolari insistono su questo. Tuttavia, in MySQL prima della versione 5.7 il comportamento di default è stato più indulgente perché non si lamenterà e poi sceglierà arbitrariamente qualsiasi valore! Ha anche una funzione ANY_VALUE()
che potrebbe essere usata come un'altra soluzione a questa questione se avete davvero bisogno dello stesso comportamento di prima. Questa flessibilità ha un costo perché non è deterministica, quindi non la raccomanderei a meno che non abbiate una buona ragione per averne bisogno. MySQL sta ora attivando l'impostazione only_full_group_by
per impostazione predefinita per buone ragioni, quindi è meglio abituarsi ad essa e rendere le vostre query conformi.
Allora perché la mia semplice risposta qui sopra? Ho fatto un paio di supposizioni:
il group_id
è unico. Sembra ragionevole, è un 'ID' dopo tutto.
anche il nome_gruppo
è unico. Questo potrebbe non essere un presupposto così ragionevole. Se questo non è il caso e hai dei nomi_gruppo
duplicati e poi segui il mio consiglio di aggiungere group_id
al GROUP BY
, potresti scoprire che ora ottieni più risultati di prima perché i gruppi con lo stesso nome avranno ora righe separate nei risultati. Per me, questo sarebbe meglio che avere questi gruppi duplicati nascosti perché il database ha tranquillamente selezionato un valore arbitrariamente!
È anche buona pratica qualificare tutte le colonne con il loro nome di tabella o alias quando c'è più di una tabella coinvolta...
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
Se non vuoi apportare alcuna modifica alla tua query attuale, segui i seguenti passi -
vagrant ssh nella tua casella
Digitare: sudo vim /etc/mysql/my.cnf
.
Scorrete fino al fondo del file e digitate A
per entrare in modalità di inserimento
Copia e incolla
[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
Digitare esc
per uscire dalla modalità di input
Digitare :wq
per salvare e chiudere vim.
Digitare sudo service mysql restart
per riavviare MySQL.