Error al cargar ficha de especie

[viktorKhan]

Falla al cargar la ficha de especie (Especie->lista de aves->ver ficha de la especie) de queda en "Por favor, espere...".

En el log aparece el siguiente error:

2020-02-13 17:35:13 Error: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'anuario_bbdd.Cita.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Request URL: /especie/generar_mapa/especie_id:95
Stack Trace:
#0 /var/www/anuario/lib/Cake/Model/Datasource/DboSource.php(460): PDOStatement->execute(Array)
#1 /var/www/anuario/lib/Cake/Model/Datasource/DboSource.php(426): DboSource->_execute('SELECT COUNT(C...', Array) #2 /var/www/anuario/lib/Cake/Model/Datasource/DboSource.php(670): DboSource->execute('SELECT COUNT(C...', Array, Array)
#3 /var/www/anuario/lib/Cake/Model/Datasource/DboSource.php(1081): DboSource->fetchAll('SELECT COUNT(`C...', false)
#4 /var/www/anuario/lib/Cake/Model/Model.php(2688): DboSource->read(Object(Cita), Array)
#5 /var/www/anuario/app/Model/Cita.php(912): Model->find('all', Array)
#6 /var/www/anuario/app/Controller/EspecieController.php(393): Cita->obtenerTotalCitasPorCuadriculaUtm('95')
#7 [internal function]: EspecieController->generar_mapa()
#8 /var/www/anuario/lib/Cake/Controller/Controller.php(486): ReflectionMethod->invokeArgs(Object(EspecieController), Array)
#9 /var/www/anuario/lib/Cake/Routing/Dispatcher.php(187): Controller->invokeAction(Object(CakeRequest))
#10 /var/www/anuario/lib/Cake/Routing/Dispatcher.php(162): Dispatcher->_invoke(Object(EspecieController), Object(CakeRequest), Object(CakeResponse))
#11 /var/www/anuario/app/webroot/index.php(109): Dispatcher->dispatch(Object(CakeRequest), Object(CakeResponse))
#12 {main}

[viktorKhan]

Parece un problem al actualizar la versión de MySQL -> https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql

[viktorKhan]

Finalmente aplicando la solución publicada en este post se corrige el problema. Para ello hay que hacer lo siguiente:

1. Hacer backup de la variable de entorno de MySQL sql_mode copiando y guardando el resultado de la siguiente query:

SELECT @@sql_mode;

2. Lanzar la siguiente query:

SET sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

No es necesario reiniciar el servicio de MySQL para ver los resultados del cambio.

[viktorKhan]

Parece que en ocasiones lo descrito en el post anterior no funciona bien, en ese caso, probar con lo descrito en este otro post -> https://www.lawebdelprogramador.com/foros/MySQL/1651514-error-1055.html

[viktorKhan]

El error se sigue produciendo ya que el fix propuesto parece que no es persistente. Es necesario buscar una solución a través de la modificación del código.

[viktorKhan]

El error se produce al usar un find de tipo "all" (https://book.cakephp.org/2/en/models/retrieving-your-data.html#find-all). Al usar este tipo de find, por defecto, siempre incluye la clave primaria entre los campos devueltos, por lo tanto, siempre nos obliga a incluir este campo en el group by, cuando realmente no lo necesitamos.

public function obtenerTotalCitasPorCuadriculaUtm($especie_id) {

        $citas = $this -> find(
            'all',
            array(
                'conditions'=>array('Cita.especie_id'=>$especie_id),
                'fields'=>array('COUNT(Cita.id) as total', 'Lugar.cuadricula_utm_id as cuadriculaUtm'),
                'group'=>array('cuadriculaUtm')
            )
        );

        return $citas;
    }

Se ha cambiado el tipo a "list" (https://book.cakephp.org/2/en/models/retrieving-your-data.html#find-list), esto hace que sólo se incluyan los campos especificados en "fields". El primer campo especificado hará de key del array devuelto.

public function obtenerTotalCitasPorCuadriculaUtm($especie_id) {

        $citas = $this -> find(
            'list',
            array(
                'conditions'=>array('Cita.especie_id'=>$especie_id),
                'fields'=>array('Lugar.cuadricula_utm_id as cuadriculaUtm', 'COUNT(Cita.id) as total'),
                'group'=>array('cuadriculaUtm')
            )
        );

        return $citas;
    }

Después de este cambio, se ha detectado otro problema en el uso de alias en queries de tipo findList, como se explica en este post -> https://stackoverflow.com/questions/6711927/cakephp-group-and-count-items-returned-in-list

Finalmente se ha optado por crear un nuevo modelo llamado CitaCount con el campo virtual

var $virtualFields = array(
		'citas_count' => 'COUNT(CitaCount.id)'
	);

y el método se ha dejado así:

public function obtenerTotalCitasPorCuadriculaUtm($especie_id) {

        $citas = $this -> find(
            'list',
            array(
				'joins' => array(
					array(
						'table' => 'lugar',
						'alias' => 'Lugar',
						'type' => 'INNER',
						'conditions' => array(
							'Lugar.id = CitaCount.lugar_id'
						)
					)
				),
                'conditions'=>array('CitaCount.especie_id'=>$especie_id),
                'fields'=>array('Lugar.cuadricula_utm_id', 'citas_count'),
                'group'=>array('Lugar.cuadricula_utm_id')
            )
        );

        return $citas;
    }

Este cambio se ha incluido como parte de la resolución de este ticket #66