Incompatibilidades MySQL tras actualizar Ubuntu

Durante el fin de semana actualizamos un servidor Ubuntu 14.04 a 16.04. Este servidor contiene una base de datos MySQL, donde se conecta una aplicación de gestión instalada en el resto de equipos de la LAN.

Comprobamos que tras la actualización se puede acceder a la aplicación, pero no funcionan las búsquedas (no dan error, pero nunca devuelven resultado), por lo que empezamos a buscar la raiz del problema:

Comprobamos que la MySQL ha pasado de la versión 5.6 a la 5.7. Intentamos volver a la versión anterior, pero ya no se encuentra en los repositorios de Ubuntu. Aunque en internet hay algunos pasos a seguir, no nos funcionan, por lo que debemos intentar hacer funcionar nuestro programa con la última versión.

Activamos el log de MySQL para registrar todas las consultas de la aplicación en el servidor:

En el fichero /etc/mysql/mysql.conf.d/mysqld.cnf descomentamos las líneas:

#general_log_file = /var/log/mysql/mysql.log
#general_log = 1

Ahora ejecutamos en nuestra aplicación la búsqueda que da error y buscamos en el log de mysql la consulta que se ejecuta, que en nuestro caso es esta:

SELECT numero FROM expediente WHERE numero=1000 AND fechacierre= CAST("0000-00-00" AS DATE)

La copiamos en phpmyadmin y vemos que tampoco devuelve ningún valor, a menos que eliminemos la condición de fecha de cierre, o que indiquemos una fecha válida, por lo que el fallo parece estar en la búsqueda de fechas vacías.

Un comentario en el foro de ubuntu nos da una pista:

What error are you getting? I suspect that it is due to the ONLY_FULL_GROUP_BY flag which is "on" by default in MySQL 5.7. Set it to off and you may be fine.

La nueva versión de MySQL activa por defecto unos modos que pueden crear incompatibilidades con nuestra aplicación.

Consultamos la documentación, que nos indica que podemos comprobar los modos activos actualmente desde el cliente de MySQL con

SELECT @@GLOBAL.sql_mode;

que nos devuelve lo siguiente:

ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_AUTO_CREATE_USER
NO_ENGINE_SUBSTITUTION

También podemos consultar los SQL MODES desde phpmyadmin: en la pantalla principal, en los botones de arriba, pulsamos el que pone variables y buscamos sql mode. Podemos modificarlas desde ahí pulsando editar.

Parece que son estas opciones las que impiden las consultas sobre la base de datos usando fechas vacías, como estabamos haciendo.

Comprobamos en otra instalación similar con Ubuntu 14.04 que no hay ningún modo SQL activo, por lo que los eliminamos con este comando:

SET GLOBAL sql_mode = '';

Puede que en lugar de vacío nos interese dejar NO_ENGINE_SUBSTITUTION

Tras esto el programa vuelve a funcionar correctamente, aunque al reiniciar la MySQL se vuelven a activar estos SQL modes.

Para que este cambio quede guardado tras reiniciar el servidor, debemos añadir, en el fichero de configuración de MySQL (/etc/mysql/my.cnf o /etc/mysql/mariadb.conf.d/50-server.cnf según proceda) esta línea:
[mysqld]

sql_mode = ""

Más info: sitepoint