He actualizado mi sistema y he instalado MySql 5.7.9 con php para una aplicación web en la que estoy trabajando. Tengo una consulta que se crea dinámicamente, y cuando se ejecuta en versiones anteriores de MySql funciona bien. Desde la actualización a la 5.7 me sale este error:
La expresión #1 de la lista SELECT no está en la cláusula GROUP BY y contiene columna no agregada 'support_desk.mod_users_groups.group_id' que es no depende funcionalmente de las columnas de la cláusula GROUP BY; esto es incompatible con sql_mode=only_full_group_by
Observe la página del Manual de Mysql 5.7 sobre el tema de Modos SQL del servidor.
Esta es la consulta que me está dando problemas:
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
He buscado un poco en Google, pero no entiendo lo suficiente la opción "only_full_group_by" como para saber qué tengo que hacer para arreglar la consulta. ¿Puedo simplemente desactivar la opción only_full_group_by
, o hay algo más que deba hacer?
Hazme saber si necesitas más información.
Puede intentar desactivar la configuración only_full_group_by
ejecutando lo siguiente:
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 no acepta NO_AUTO_CREATE_USER
por lo que debe ser eliminado.
Yo sólo añadiría group_id
al GROUP BY
.
Al SELECCIONAR
una columna que no es parte del GROUP BY
podría haber múltiples valores para esa columna dentro de los grupos, pero sólo habrá espacio para un solo valor en los resultados. Por lo tanto, la base de datos normalmente necesita saber exactamente cómo convertir esos valores múltiples en un solo valor. Comúnmente, esto se hace con una función agregada como COUNT()
, SUM()
, MAX()
etc... Digo normalmente porque la mayoría de los sistemas de bases de datos populares insisten en esto. ¡Sin embargo, en MySQL antes de la versión 5.7 el comportamiento por defecto ha sido más indulgente porque no se quejará y luego elegirá arbitrariamente cualquier valor! También tiene una función ANY_VALUE()
que podría usarse como otra solución a esta cuestión si realmente necesita el mismo comportamiento que antes. Esta flexibilidad tiene un coste, ya que no es determinista, por lo que no la recomendaría a menos que tenga una muy buena razón para necesitarla. MySQL está activando ahora la configuración only_full_group_by
por defecto por buenas razones, así que es mejor acostumbrarse a ella y hacer que sus consultas la cumplan.
Entonces, ¿por qué mi sencilla respuesta anterior? He hecho un par de suposiciones:
el group_id
es único. Parece razonable, es un 'ID' después de todo.
el nombre_del_grupo
también es único. Esto puede no ser una suposición razonable. Si este no es el caso y usted tiene algunos duplicados group_names
y luego seguir mi consejo de añadir group_id
a la GROUP BY
, usted puede encontrar que ahora se obtiene más resultados que antes, porque los grupos con el mismo nombre ahora tendrá filas separadas en los resultados. Para mí, esto sería mejor que tener estos grupos duplicados ocultos porque la base de datos ha seleccionado tranquilamente un valor de forma arbitraria.
También es una buena práctica calificar todas las columnas con su nombre de tabla o alias cuando hay más de una tabla involucrada...
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 no quiere hacer ningún cambio en su consulta actual, siga los siguientes pasos -
vagrant ssh en su caja
Escriba: sudo vim /etc/mysql/my.cnf
Desplázate hasta el final del archivo y escribe A
para entrar en modo de inserción
Copie y pegue
[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
Escriba esc
para salir del modo de entrada
Escriba :wq
para guardar y cerrar vim.
Escribe sudo service mysql restart
para reiniciar MySQL.