Error en MYSQL Incorrect key file for table /tmp/#sql_XXXX_X.MYI

Hoy junto con @rafah3ndez, nos hemos encontrado que un aplicativo con un problema en base de datos, mostrando el siguiente mensaje de error Incorrect key file for table /tmp/#sql_XXXX_X.MYI en el fichero de log e impidiendo su conexión. Este error es mostrado por MYSQL cuando trata de crear una tabla temporal y no dispone de suficiente espacio para crearla, os voy a ir explicando los pasos que he ir realizando para solucionarlo.

Lo primero de todo es tener encuenta que MYSQL utilizar una variable en la que almacena la ruta de los ficheros remporales, dicha variable es tmpdir, y se configura en el fichero my.conf, si dicha variable no es configurada por defecto usa la carpeta temporal del sistema operativo /tmp.

Para comprobar que valor tiene configurada la variable tmpdir podemos hacerlo enetrando en nuestra instancia de MYSQL y ejecutando lo siguiente:

mysql> show variables;

Como habéis podido observar, aparecen muchos parámetros de  configuración pero vamos a centrarnos en tmpdir.

En la imagen observais las siguientes variables:

tmpdir -> Nos muestra la ruta donde MYSQL va a generar los ficheros temporales

tmp_table_size -> Nos muestra el tamaño máximo para crear tablas temporales.

Ahora que tenemos localizado el directorio temporal, en este caso /tmp , necesitamos saber cuanto ocupa:

df -h
/dev/mapper/grp-temporal 4,0G   65M  3,7G   2% /tmp

Si observais en el /tmp hay 3.5gb libres, debería de ser suficientes para que MYSQL crease sus ficheros temporales, pero parece ser que no:

[root@dbc01 tmp]# du -sh *
4,0K    hsperfdata_oracle
16K     lost+found
12K     mysql
3,6G    #sql_7a25_0.MYD
4,0K    #sql_7a25_0.MYI

Justo en el momento de ejecutar el aplicativo, comienza a generar una consulta que va creando un fichero temporal bastante grande y cuando ya no puede crecer más, el aplicativo se para mostrando el error y el fichero desaparece.

Lo ideal sería revisar esa consulta para ver si realmente debe generar tanto espacio temporal, a veces esto no es posible por lo que tenemos dos alternativas:

- Ampliar el espacio temporal (si no es posible)

- Cambiar la ruta del espacio temporal, para realizar esto tenemos que parar nuestra instancia de base de datos y añadir en el fichero my.conf una ruta alternativa, con el inconveniente de que tendríamos cortes de servicio.

En nuestro caso he podido ampliar el espacio temporal, ya que lo tenia por LVM y tenia unos 10 GB libres, con 2GB más ya no ha vuelto a mostrar el error. Este problema te muestra la importancia de conocer los aplicativos que se van a poner en producción y asi tener una configuración optima de la base de datos.

---