Dejad de usar FLUSH PRIVILEGES

Cada vez que alguien escribe un tutorial o solución a un problema relacionado con la creación de una nueva cuentas de usuario o la provisión de diferentes privilegios veo la sugerencia de utilizar FLUSH PRIVILEGES. Por ejemplo, el primer post en /r/mysql en el momento de escribir estas líneas, “MySQL:The user specified as a definer does not exist (error 1449)-Solutions” es culpable múltiples veces de esto mismo (Actualización: el usuario ha tachado estas líneas, tras la publicación de este artículo).

No es mi intención denunciar ese artículo, pero lo cierto es que he visto cometer ese error muchas, muchas veces. Incluso si os dirigís a la página del manual de referencia sobre el comando GRANT veréis al final -de un usuario ajeno a MySQL- usar GRANT seguido de un FLUSH PRIVILEGES.

¿Por qué debería importarme? ¿Es FLUSH PRIVILEGES un problema? ¿Por qué lo hace todo el mundo? La razón de que ese comando exista es porque —para mejorar el rendimiento— MySQL mantiene una copia en memoria de las tablas GRANT (de permisos de usuario). De esta manera, no necesita leerlas de disco en cada conexión, en cada cambio de la base de datos por defecto, y en cada consulta enviada al servidor. El comando mencionado anteriormente fuerza la recarga de esta caché, leyéndola directamente de disco (o de la caché del sistema de archivos), tal y como el manual de referencia de MySQL indica claramente (teniendo incluso su propia sección When Privilege Changes Take Effect “Cuándo tienen efecto los cambios de privilegios”). Sin embargo, su ejecución es totalmente innecesaria en la mayoría de los casos prácticos porque:

If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE, SET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

que significa, más o menos:

Si modificas las tablas de privilegios de manera inderecta, usando sentencias de gestión de cuentas tales como GRANT, REVOKE, SET PASSWORD o RENAME USER el servidor tiene en cuenta estos cambios y carga las tablas en memoria de nuevo de manera inmediata.

La única razón en la cual es necesario realizar la operación de recarga manualmente es cuando:

you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE (se modifican las tablas de privilegios directamente usando sentencias como INSERT, UPDATE o DELETE)

Para la mayoría de operaciones, como crear un usuario, cambiar sus privilegios o contraseña, es preferible usar las operaciones de alto nivel. No sólo son más fáciles de usar, también son en general compatibles con un mayor número de versiones de MySQL y además te evitarán cometer errores (por supuesto, es necesario acordarse de configurar el modo sql “NO_AUTO_CREATE_USER“). Incluso normalmente funcionarán sin problemas en un entorno hostil para MyISAM como es un clúster de Galera. Ciertamente, hay razones por las que puede ser necesario editar las tablas manualmente- como administradores, es posible que deseemos manipular de manera especial los privilegios o importar las tablas mysql.* de algún otro lugar, por lo que en estos casos ejecutar FLUSH PRIVILEGES es obligatorio. Tened en cuenta que, como dice la página del manual, en la mayoría de los casos (por ejemplo, privilegios globales) cambiar los permisos de un usuario sólo afectará a nuevas conexiones y nunca a consultas que se estén ejecutando ya que los privilegios se comprueban al inicio del procesado de la consulta -léase el manual para los detalles.

Así, pues, ¿por qué mi cruzada contra el abuso de FLUSH PRIVILEGES? Después de todo, en el peor de los casos, ¡se recargarán los mismos privilegios de nuevo! No es una cuestión de rendimiento. Aunque, en casos extremos bien podría ser un problema. Echad un vistazo al siguiente script, que ejecuta 10 000 sentencias CREATE USER (esto sólo se puede hacer en un único hilo ya que las tablas grant todavía están en formato MyISAM, incluso en 5.7.6):

def execute_test(port, drop, flush, thread):
   db = mysql.connector.connect(host="localhost", port=port, user="msandbox", passwd="msandbox", database="test")
   for i in range(0, TIMES):
      cursor = db.cursor()
      if (drop):
         sql = "DROP USER 'test-" + `thread` + '-' + `i` + "'@'localhost'"
      else:
         sql = "CREATE USER 'test-" + `thread` + '-' + `i` + "'@'localhost' IDENTIFIED BY '" + DEFAULT_PASSWORD + "'"
      cursor.execute(sql)
      cursor.close()
      db.commit()
      if (flush):
         cursor = db.cursor()
         flush_sql = "FLUSH PRIVILEGES"
         cursor.execute(flush_sql)
         cursor.close()
         db.commit()
   db.close()

Las mediciones de tiempos de ambas ejecuciones son las siguientes:

$ time ./test_flush.py
Not flushing
Executing the command 10000 times

real    0m15.508s
user    0m0.827s
sys     0m0.323s

$ ./test_flush.py -d
Not flushing
Dropping users
Executing the command 10000 times

$ time ./test_flush.py -f
Flushing after each create
Executing the command 10000 times

real    2m7.041s
user    0m2.482s
sys     0m0.771s

$ ./test_flush.py -d
Not flushing
Dropping users
Executing the command 10000 times

Podemos ver que usar FLUSH PRIVILEGES es 8 veces más lento que no utilizarlo. De nuevo, quiero enfatizar que el rendimiento no es el mayor de los problemas en esta situación, ya que la mayoría de las personas lo ejecutarían tan sólo una vez al final de cada bloque de comandos, por lo que no supondría una gran sobrecarga. Incluso si hay una mayor cantidad de operaciones de lectura de disco, debemos asumir que todo round trip a la base de datos, y todo commit requiere ciertos recursos extra, por lo que esto mismo se puede extrapolar a cualquier comando. Además, la concurrencia no suele ser un problema en la creación de cuentas de usuario, ya que la tabla mysql.user no es normalmente (o no debería) se muy dinámica.

El mayor problema contra el abuso de FLUSH PRIVILEGES es que la gente lo ejecuta sin comprender porqué lo hacen, y lo que realmente hace este comando. Cada vez que una persona tiene un problema con el sistema de privilegios de MySQL, el primer consejo dado es ejecutar dicha sentencia “por si acaso”. Si no os lo creéis, echad un vistazo rápido a respuestas en dba.stackexchange como esta, esta y esta (que he seleccionado entre las muchas existentes), cuyo usuario original no estaba alterando manualmente las tablas mysql.*. La problemática está en que la mayor parte de las veces el comando no hace absolutamente nada, y el problema subyace en la pobre comprensión del sistema de permisos de MySQL. Como dice el dicho en inglés- cuando tienes un martillo, todo problema parece un clavo. Los usuarios leen que esa es la manera adecuada de resolver problemas relacionados con permisos y pasan el “conocimiento” a otros usuarios, creando básicamente el equivalente a una mito urbano en el mundo MySQL.

Así pues, la siguiente vez que encuentres un problema como un usuario que no puede entrar en la base de datos, o aplicarle privilegios, hay muchas otras fuentes de confusión, tales como: el uso de old_passwords, usar un método de autenticación distinto de las contraseñas nativas, no disponer de los permisos adecuados o de la propiedad WITH GRANT OPTION para transmitirlos, el servidor no detectando el usuario con el mismo nombre y host que en el que realmente está, el uso de skip-name-resolve lo qual hace que se ignores las entradas dns, no esperar a una nueva conexión para que los cambios surtan efecto, … y muchas otras dificultades que van unidas a la autenticación y autorización. El sistema de privilegios de MySQL no es precisamente obvio ni perfecto(¡Hola, otorgar permisos para bases de datos que no existen?), pero invertir 5 minutos en leer el detallado manual sobre grants puede ahorrar muchos quebraderos de cabeza en el futuro. TL;TR RTFM

Para aquellos que ya saben cuándo usar o no usar FLUSH PRIVILEGES, por favor, la siguiente vez que veáis a alguien abusarlo, educar al usuario sobre mejores prácticas para que la gente no siga fundamentándose en la magia y mitos urbanos para resolver problemas; id a reddit/stackoverflow/vuestra red social favorita/etc. y votad positivamente las buenas prácticas/comentad sobre las malas. Hoy podría ser FLUSH PRIVILEGES, mañana puede que sea “añadir OPTIMIZE TABLE en un trabajo del cron cada 5 minutos para tus tablas InnoDB” (y sí, esto último lo he encontrado en producción ahí fuera).