J'ai mis à jour mon système et j'ai installé MySql 5.7.9 avec php pour une application web sur laquelle je travaille. J'ai une requête qui est créée dynamiquement, et lorsqu'elle est exécutée dans les anciennes versions de MySql, elle fonctionne bien. Depuis la mise à jour vers la version 5.7, je reçois cette erreur :
L'expression #1 de la liste SELECT n'est pas dans la clause GROUP BY et contient la colonne non agrégée 'support_desk.mod_users_groups.group_id' ; qui est pas fonctionnellement dépendante des colonnes de la clause GROUP BY ; ceci est incompatible avec sql_mode=only_full_group_by
Voir la page du manuel de Mysql 5.7 sur le sujet des [Server SQL Modes][1].
Voici la requête qui me pose problème :
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
J'ai fait quelques recherches sur Google, mais je ne comprends pas suffisamment l'option only_full_group_by
pour savoir ce que je dois faire pour corriger la requête. Puis-je simplement désactiver l'option only_full_group_by
, ou dois-je faire autre chose ?
Faites-moi savoir si vous avez besoin de plus d'informations.
Vous pouvez essayer de désactiver le paramètre only_full_group_by
en exécutant ce qui suit :
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 n'accepte pas NO_AUTO_CREATE_USER
, il faut donc le supprimer.
J'ajouterais simplement group_id
au GROUP BY
.
Lorsque vous sélectionnez une colonne qui ne fait pas partie du groupe BY, il peut y avoir plusieurs valeurs pour cette colonne dans les groupes, mais il n'y aura de la place que pour une seule valeur dans les résultats. Il faut donc habituellement indiquer à la base de données comment transformer ces valeurs multiples en une seule valeur. Généralement, cela est fait avec une fonction d'agrégation comme COUNT()
, SUM()
, MAX()
etc... Je dis souvent parce que la plupart des autres systèmes de bases de données populaires insistent sur ce point. Cependant, dans MySQL avant la version 5.7, le comportement par défaut est plus indulgent car il ne se plaindra pas et choisira ensuite arbitrairement n'importe quelle valeur ! Il dispose également d'une fonction ANY_VALUE()
qui pourrait être utilisée comme une autre solution à cette question si vous aviez vraiment besoin du même comportement qu'auparavant. Cette flexibilité a un coût car elle n'est pas déterministe, donc je ne la recommande pas à moins que vous ayez une très bonne raison d'en avoir besoin. MySQL active maintenant le paramètre only_full_group_by
par défaut pour de bonnes raisons, donc il est préférable de s'y habituer et de faire en sorte que vos requêtes s'y conforment.
Alors pourquoi ma réponse simple ci-dessus ? J'ai fait quelques hypothèses :
le group_id
est unique. Cela semble raisonnable, c'est un 'ID' ; après tout.
le group_name
est également unique. Ce n'est peut-être pas une hypothèse si raisonnable. Si ce n'est pas le cas et que vous avez des group_names
en double et que vous suivez mon conseil d'ajouter group_id
au GROUP BY
, vous pouvez constater que vous obtenez maintenant plus de résultats qu'avant parce que les groupes avec le même nom auront maintenant des lignes séparées dans les résultats. Pour moi, c'est mieux que d'avoir ces groupes dupliqués cachés parce que la base de données a tranquillement sélectionné une valeur arbitrairement !
C’est aussi une bonne pratique de qualifier toutes les colonnes avec leur nom de table ou alias quand il y a plus d’une table impliquée...
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
Si vous ne souhaitez pas modifier votre requête actuelle, suivez les étapes ci-dessous.
vagrant ssh dans votre boîte
Tapez : sudo vim /etc/mysql/my.cnf
Faites défiler jusqu'au bas du fichier et tapez A
pour entrer en mode insertion
Copiez et collez
[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
Tapez esc
pour quitter le mode de saisie.
Tapez :wq
pour enregistrer et fermer vim.
Tapez sudo service mysql restart
pour redémarrer MySQL.