optimizar - mysql remove html tags

Error relacionado con only_full_group_by al ejecutar una consulta en MySql

Question

He actualizado mi sistema e 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 5.7 me sale 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 en 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

Busqué en Google sobre el tema, pero no entiendo only_full_group_by suficiente como para descubrir qué necesito hacer para solucionar la consulta. ¿Puedo desactivar la opción only_full_group_by o hay algo más que deba hacer?

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

Answer 1

Adición de líneas (mencione a continuación) 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

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

Answer 2

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

tenga en cuenta la clave para mí ser

count(*) AS cnt

Answer 3

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

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

Y en el siguiente 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 dice dónde está el problema.

Ejemplo:

Error de consulta MySQL 1140: en la 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 elapsedtotal, userid, timestamp, questionid, answerid, SUM (correcto) como correcto, transcurrido, ipaddress DESDE el juego DONDE marca de tiempo> = DATE_SUB (AHORA (), INTERVALO 1 DÍA) Y userid = 1

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

Answer 4

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.cnf para 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.php de true a 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

Answer 5

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

no permite consultas para las que la lista de selección, la condición HAVING o la lista ORDER BY hacen referencia a columnas no agregadas que no se nombran en la cláusula GROUP BY ni dependen funcionalmente de (columnas determinadas por 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 en la cláusula GROUP BY; esto es incompatible con sql_mode = only_full_group_by

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

IE digamos que tiene estos registros en su tabla de users :

Y ejecutará la consulta no válida que se muestra arriba.
Y obtendrá el error que se muestra arriba, porque hay 3 registros con el nombre John , y es bueno, pero todos tienen valores de campo de email diferentes.
Entonces, MySQL simplemente no entiende 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 SELECCIONAR, pero no puede hacerlo, si no están agregados, pero hay una muleta que podría usar (pero no muy recomendable):

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

Ahora, puede preguntar, ¿por qué no se recomienda usar ANY_VALUE ?
Debido a que MySQL no sabe exactamente qué valor de los registros agrupados recuperar, y al usar esta función, le pide que busque alguno de ellos (en este caso, se obtuvo el correo electrónico del primer registro con nombre = John).
Exactamente, no puedo tener ninguna idea de por qué querrías que este comportamiento exista.

Por favor, si no me comprende, lea 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 de acuerdo con las edades disponibles, puede anotar esta consulta

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

Lo cual es totalmente válido, de acuerdo con las reglas de MySQL.
Y así.

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

Answer 6

Para Mac:

1. Copie my-default.cnf predeterminado a /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

Answer 7

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

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

luego reinicie wamp o apache

Answer 8

Puede agregar un unique index a group_id ; si está seguro de que group_id es ú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.

Answer 9

Puede intentar deshabilitar 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 eliminarse.

Answer 10

Si está utilizando wamp 3.0.6 o cualquier versión superior que no sea la versión estable 2.5, puede 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-> mysql settings-> sql_mode-> none. o desde la consola puede 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'';

Answer 11

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 A para 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 esc para salir del modo de entrada

6. Escriba :wq para guardar y cerrar vim.
7. Escriba sudo service mysql restart para reiniciar MySQL.

Answer 12

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

Preste atención para select la columna que desea groupBy , y use addGroupBy lugar de groupBy :

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

Funciona en Symfony3 -

Answer 13

Simplemente agregaría group_id al GROUP BY .

Al SELECT una columna que no es parte de GROUP BY , podría haber múltiples valores para esa columna dentro de los grupos, pero solo habrá espacio para un único valor en los resultados. Por lo tanto, la base de datos generalmente necesita que se le diga exactamente cómo convertir esos valores múltiples en un valor. Comúnmente, esto se hace con una función agregada como COUNT() , SUM() , MAX() etc ... Digo generalmente porque la mayoría de los otros sistemas de bases de datos populares insisten en esto. Sin embargo, en MySQL anterior a la versión 5.7, el comportamiento predeterminado 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 pregunta si realmente necesita 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 está activando la configuración only_full_group_by de manera predeterminada por buenas razones, por lo que es mejor acostumbrarse y hacer que sus consultas cumplan con ella.

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

1) el group_id es único. Parece razonable, es una ''ID'' después de todo.

2) el group_name también es único. Esto puede no ser una suposición tan razonable. Si este no es el caso y tiene algunos group_names duplicados y luego sigue mi consejo para agregar group_id al 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 arbitrariamente.

También es una buena práctica calificar todas las columnas con el nombre o alias de su tabla 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

Answer 14

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 agrupa filas y luego selecciona 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 al grupo de declaración de group by rect.color, rect.value que puede ser lo que desea en algunos casos, de lo contrario devolvería resultados duplicados con el mismo color que puede no desear
• 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 usar ANY_VALUE() si está seguro de que todos los resultados dentro del grupo son los mismos. doc

Answer 15

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

Desde documentos MySQL 5.7 :

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

...

Esta consulta puede ser inválida con ONLY_FULL_GROUP_BY habilitado porque la columna de dirección no ONLY_FULL_GROUP_BY en la lista de selección no se nombra en la cláusula GROUP BY :

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 manera ú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 función ANY_VALUE() :

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