Error relacionado con only_full_group_by al ejecutar una consulta en MySql

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 que actualicé a 5.7 recibo este error:

La expresión # 1 de la lista SELECT no está en la cláusula GROUP BY y contiene la columna no agregada 'support_desk.mod_users_groups.group_id' que no depende funcionalmente de las columnas de la cláusula GROUP BY; esto es incompatible con sql_mode = only_full_group_by

Tenga en cuenta la página del manual para Mysql 5.7 sobre el tema de los 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

Hice algunas búsquedas en Google sobre el problema, pero no entiendo lo only_full_group_bysuficiente como para averiguar qué debo hacer para solucionar la consulta. ¿Puedo simplemente desactivar la only_full_group_byopción o hay algo más que deba hacer?

Déjeme saber si usted necesita más información.

Solo agregaría group_idal GROUP BY.

Cuando se SELECTingresa una columna que no es parte de, GROUP BYpodría haber múltiples valores para esa columna dentro de los grupos, pero solo habrá espacio para un solo valor en los resultados. Por lo tanto, la base de datos generalmente necesita saber exactamente cómo convertir esos valores múltiples en un valor. Comúnmente, esto se hace con una función de agregado como COUNT(), SUM(), MAX()etc ... Digo generalmente porque la mayoría de otros sistemas de bases de datos populares insisten en esto. Sin embargo, en MySQL antes de la versión 5.7, el comportamiento predeterminado ha sido más indulgente porque no se queja y luego elige arbitrariamente cualquier valor . También tiene una ANY_VALUE()función que podría usarse como otra solución a esta pregunta si realmente necesitara el mismo comportamiento que antes. Esta flexibilidad tiene un costo porque no es determinista, por lo que no la recomendaría a menos que tenga una muy buena razón para necesitarla. MySQL ahora activa la only_full_group_byconfiguración de forma predeterminada por buenas razones, por lo que es mejor acostumbrarse y hacer que sus consultas la cumplan.

Entonces, ¿por qué mi simple respuesta anterior? Hice un par de suposiciones:

1) el group_ides único. Parece razonable, después de todo es una 'identificación'.

2) el group_nametambién es único. Esta puede no ser una suposición tan razonable. Si este no es el caso y tiene algunos duplicados group_namesy luego sigue mi consejo para agregar group_idal GROUP BY, es posible que ahora obtenga más resultados que antes porque los grupos con el mismo nombre ahora tendrán filas separadas en los resultados. Para mí, esto sería mejor que tener estos grupos duplicados ocultos porque la base de datos ha seleccionado discretamente 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

Puede intentar deshabilitar la only_full_group_byconfiguración 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_USERpor lo que debe eliminarse.

puede desactivar el mensaje de advertencia como se explica en las otras respuestas o puede comprender lo que está sucediendo y solucionarlo.

A partir de MySQL 5.7.5, el modo SQL predeterminado incluye ONLY_FULL_GROUP_BY, lo que significa que cuando está agrupando filas y luego seleccionando algo de esos grupos, debe decir explícitamente de qué fila debe hacerse esa selección.

Mysql necesita saber qué fila del grupo está buscando, lo que le brinda dos opciones

• También puede agregar la columna que desea a la declaración de grupo, group by rect.color, rect.valueque puede ser lo que desea en algunos casos, de lo contrario, devolvería resultados duplicados con el mismo color que es posible que no desee
• también puede usar funciones agregadas de mysql para indicar qué fila está buscando dentro de los grupos como AVG() MIN() MAX() lista completa
• Y, finalmente, puede usarlo ANY_VALUE()si está seguro de que todos los resultados dentro del grupo son iguales. Doc

Si no desea realizar ningún cambio en su consulta actual, siga los pasos a continuación:

1. ssh vagabundo en tu caja
2. Tipo: sudo vim /etc/mysql/my.cnf
3. Desplácese hasta la parte inferior del archivo y escriba Apara ingresar al modo de inserción

4. Copiar y pegar

   [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
   

5. Escriba escpara salir del modo de entrada

6. Escriba :wqpara guardar y cerrar vim.
7. Escriba sudo service mysql restartpara reiniciar MySQL.

Use ANY_VALUE()para referirse a la columna no agregada.

SELECT name,           address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works

De los documentos de MySQL 5.7 :

Puede lograr el mismo efecto sin deshabilitar ONLY_FULL_GROUP_BY usando ANY_VALUE()para referirse a la columna no agregada.

...

Esta consulta puede no ser válida con ONLY_FULL_GROUP_BYhabilitada porque la columna de dirección no agregada en la lista de selección no se nombra en la GROUP BYcláusula:

SELECT name, address, MAX(age) FROM t GROUP BY name;

...

Si sabe que, para un conjunto de datos dado, cada valor de nombre de hecho determina de forma única el valor de la dirección, la dirección depende funcionalmente del nombre. Para decirle a MySQL que acepte la consulta, puede usar la ANY_VALUE()función:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Intentaré explicarte de qué se trata este error.
A partir de MySQL 5.7.5, la opción ONLY_FULL_GROUP_BYestá habilitada de forma predeterminada.
Por lo tanto, de acuerdo con el estándar SQL92 y anteriores:

no permite consultas para las cuales la lista de selección, la condición HAVING o la lista ORDER BY se refieren a columnas no agregadas que no están nombradas en la cláusula GROUP BY ni son funcionalmente dependientes de (determinadas únicamente por) columnas GROUP BY

( leer más en documentos )

Así por ejemplo:

SELECT * FROM `users` GROUP BY `name`;

Recibirá un mensaje de error después de ejecutar la consulta anterior.

# 1055 - La expresión # 1 de la lista SELECT no está en la cláusula GROUP BY y contiene la columna no agregada 'testsite.user.id' que no depende funcionalmente de las columnas de la cláusula GROUP BY; esto es incompatible con sql_mode = only_full_group_by

¿Por qué?
Debido a que MySQL no entiende exactamente qué valores de los registros agrupados se deben recuperar, y este es el punto.

Es decir, digamos que tiene estos registros en su userstabla:

Y ejecutará la consulta no válida mostrada arriba.
Y obtendrá el error que se muestra arriba, porque hay 3 registros con nombre John, y está bien, pero todos tienen emailvalores de campo diferentes .
Entonces, MySQL simplemente no comprende cuál de ellos devolver en el registro agrupado resultante.

Puede solucionar este problema, simplemente cambiando su consulta de esta manera:

SELECT `name` FROM `users` GROUP BY `name`

Además, es posible que desee agregar más campos a la sección SELECT, pero no puede hacerlo, si no están agregados, pero hay una muleta que podría usar (pero no se recomienda):

SELECT ANY_VALUE(`id`), ANY_VALUE(`email`), `name` FROM `users` GROUP BY `name`

Ahora, puede preguntar, ¿por qué ANY_VALUEno se recomienda su uso?
Debido a que MySQL no sabe exactamente qué valor de registros agrupados recuperar, y al usar esta función, le pide que obtenga cualquiera de ellos (en este caso, se obtuvo el correo electrónico del primer registro con nombre = John).
Exactamente, no puedo pensar en por qué querrías que existiera este comportamiento.

Por favor, si no me entiendes, lee más sobre cómo funciona la agrupación en MySQL, es muy simple.

Y al final, aquí hay una consulta más simple pero válida.
Si desea consultar el recuento total de usuarios según las edades disponibles, es posible que desee escribir esta consulta

SELECT `age`, COUNT(`age`) FROM `users` GROUP BY `age`;

Lo cual es totalmente válido, según las reglas de MySQL.
Y así.

Es importante comprender cuál es exactamente el problema y solo entonces escribir la solución.

Estoy usando Laravel 5.3, mysql 5.7.12, en laravel homestead (0.5.0, creo)

Incluso después de configurar explícitamente la edición /etc/mysql/my.cnfpara reflejar:

[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

Seguía recibiendo el error.

Tuve que cambiar config/database.phpde truea false:

    'mysql' => [
        'strict' => false, //behave like 5.6
        //'strict' => true //behave like 5.7
    ], 

Otras lecturas:

https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel -5-2

Si está utilizando wamp 3.0.6 o cualquier versión superior que no sea la 2.5 estable, podría enfrentar este problema, en primer lugar, el problema es con sql. tienes que nombrar los campos en consecuencia. pero hay otra forma de resolverlo. haga clic en el icono verde de wamp. mysql-> configuración de mysql-> sql_mode-> ninguno. o desde la consola puedes cambiar los valores predeterminados.

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';

Vaya a mysql o phpmyadmin y seleccione la base de datos, luego simplemente ejecute esta consulta y funcionará. esta trabajando bien para mi.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Adición de líneas (mencionar más abajo) en el archivo: /etc/mysql/my.cnf

[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

Funciona bien para mi. Versión del servidor: 5.7.18-0ubuntu0.16.04.1 - (Ubuntu)

Para Mac:

1.Copie el my-default.cnf predeterminado en /etc/my.cnf

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

2.Cambie sql_mode en my.cnf usando su editor favorito y configúrelo en este

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. Reinicie el servidor MySQL.

mysql.server restart

Esto es lo que me ayudó a comprender todo el problema:

1. https://stackoverflow.com/a/20074634/1066234
2. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

Y a continuación, otro ejemplo de una consulta problemática.

Problemático:

SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
                        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
                        AND cookieid = #

Resuelto agregando esto al final:

  GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress

Nota: Vea el mensaje de error en PHP, le indica dónde radica el problema.

Ejemplo:

Error de consulta de MySQL 1140: en una consulta agregada sin GROUP BY, la expresión # 4 de la lista SELECT contiene la columna no agregada 'db.gameplay.timestamp'; esto es incompatible con sql_mode = only_full_group_by - Consulta: SELECT COUNT (*) como intentos, SUM (transcurrido) como transcurridototal, ID de usuario, marca de tiempo, questionid, answerid, SUM (correcto) como correcto, transcurrido, dirección IP DESDE el juego DONDE marca de tiempo> = FECHA_SUB (AHORA (), INTERVALO 1 DÍA) Y ID de usuario = 1

En este caso, faltaba la expresión # 4 en GROUP BY.

Para localhost / wampserver 3 podemos establecer sql-mode = user_mode para eliminar este error:

click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode

luego reinicia wamp o apache

Puede agregar un unique indexa group_id; si está seguro de que group_ides único.

Puede resolver su caso sin modificar la consulta .

Una respuesta tardía, pero aún no se ha mencionado en las respuestas. Tal vez debería completar las respuestas ya completas disponibles. Al menos resolvió mi caso cuando tuve que dividir una tabla con demasiados campos.

Si tiene este error con Symfony usando el generador de consultas de doctrine , y si este error es causado por un orderBy :

Preste atención a selectla columna que desea groupByy utilice en addGroupBylugar de groupBy:

$query = $this->createQueryBuilder('smth')->addGroupBy('smth.mycolumn');

Funciona en Symfony3 -

Disculpas por no usar tu SQL exacto

Usé esta consulta para superar la advertencia de Mysql.

SELECT count(*) AS cnt, `regions_id`
FROM regionables 
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1

nota la clave para mi ser

count(*) AS cnt

Tuve que editar el siguiente archivo en mi Ubuntu 18.04:

/etc/mysql/mysql.conf.d/mysqld.cnf

con

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

y

sudo service mysql restart

La respuesta de consenso anterior es buena, pero si tiene problemas para ejecutar consultas dentro de los procedimientos almacenados después de arreglar su archivo my.cnf, intente cargar sus SP nuevamente.

Sospecho que MySQL debe haber compilado los SP con el valor predeterminado only_full_group_by establecido originalmente. Por lo tanto, incluso cuando cambié my.cnf y reinicié mysqld, no tuvo ningún efecto en los SP, y siguieron fallando con "La lista SELECT no está en la cláusula GROUP BY y contiene una columna no agregada ... que no depende funcionalmente de las columnas en GROUP BY cláusula; esto es incompatible con sql_mode = only_full_group_by ".

La recarga de los SP debe haber provocado que se recompilen ahora con only_full_group_by desactivado. Después de eso, parecen funcionar como se esperaba.