19.3. Registro binario de procedimientos almacenados y disparadores

MySQL 5.0

19.3. Registro binario de procedimientos almacenados y disparadores

Esta sección describe cómo MySQL trata procedimientos almacenados (procedimientos o funciones) con respecto a logueo binario. La sección también se aplica a disparadores.

El log binario contiene información sobre comandos SQL que modifican contenidos de base de datos. Esta información se almacena en la forma de “eventos” que describen las modificaciones.

El log binario tiene dos propósitos importantes:

El logueo de procedimientos almacenados difiere antes y después de MySQL 5.0.6. Antes de MySQL 5.0.6, los comandos que crean y usan procedimientos almacenados no se escriben en el log binario, pero los comandos invocados desde procedimientos almacenados se loguean. Suponga que ejecuta los siguientes comandos:

CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;

Para este ejemplo, sólo el comando aparecerá en el log binario. Los comandos y no aparecerán. La ausencia de comandos relacionados con rutinas en el log binario significa que procedimientos almacenados no se replican correctamente. También significa que para operaciones de recuperación de datos, re-ejectuar eventos en el log binario no recupera procedimientos almacenados.

Para tratar estos temas de replicación y recuperación de datos, se cambió el logueo de procedimientos almacenados en MySQL 5.0.6. Sin embargo, este cambio provoca nuevos temas, que se presentan en la siguiente discusión.

A no ser que se diga lo contrario, estas notas asumen que no ha activado el logueo binario arrancando el servidor con la opción . (Si el log binario no se activa, la replicación no es posible, ni está disponible el log binario para replicación de datos.) Consulte Sección 5.10.3, “El registro binario (Binary Log)”.

Las características de logueo binario para comandos de procedimientos almacenados se describen en la siguiente lista. Algunos de los iconos indican problemas que debería conocer, pero en algunos casos, hay inicializaciones de servidor que puede modificar o soluciones que puede usar.

  • Los comandos , , , y se escriben en el log binario, como lo son , , y .

    Sin embargo, hay implicaciones de seguridad para replicación: para crear una rutina, un usuario debe tener el permiso , pero un usuario que tenga este permiso puede escribir una rutina para realizar cualquier acción en un servidor esclavo ya que el flujo SQL en el esclavo corre con todos los permisos. Por ejemplo, si el maestro y el esclavo tienen valores de ID del servidor de 1 y 2 respectivamente, un usuario en el maestro puede crear e invocar procedimientos como sigue:

    mysql> delimiter //
    mysql> CREATE PROCEDURE mysp ()
        -> BEGIN
        ->   IF @@server_id=2 THEN DROP DATABASE accounting; END IF;
        -> END;
        -> //
    mysql> delimiter ;
    mysql> CALL mysp();
    

    Los comandos y se escriben en el log binario, así que los ejecutará el esclavo. Ya que el flujo SQL del esclavo tiene todos los permisos, borra la base de datos .

    Para evitar este peligro en servidores con logueo binario activado, MySQL 5.0.6 introduce el requerimiento que los creadores de procedimientos almacenados y funciones deben tener el permiso , además del permiso requerido. Similarmente, para usar o , debe tener el permiso además del permiso . Sin el permiso ocurre un error:

    ERROR 1419 (HY000): You do not have the SUPER privilege and
    binary logging is enabled (you *might* want to use the less safe
    log_bin_trust_routine_creators variable)
    

    Puede no querer forzar el requerimiento en los creadores de rutinas que deben tener el permiso . Por ejemplo, todos los usuarios con el permiso en su sistema pueden ser desarrolladores de aplicaciones con experiencia. Para desactivar el requerimiento de , cambie la variable de sistema global a 1. Por defecto, esta variable vale 0, pero puede cambiarla así:

    mysql> SET GLOBAL log_bin_trust_routine_creators = 1;
    

    Puede activar esta variable usando la opción al arrancar el servidor.

    Si el logueo binario no está activado, no se aplica y no se ncesita el permiso para creación de rutinas.

  • Una rutina no-determinista que realiza actualizaciones no es repetible, que puede tener dos efectos no deseables:

    • El esclavo será distinto al maestro.

    • Los datos restaurados serán distintos a los originales.

    Para tratar estos problemas, MySQL fuerza los siguientes requerimientos: En un servidor maestro, la creación y alteración de una rutina se rehúsa a no ser que la rutina se declare como determinista o que no modifique datos. Esto significa que cuando crea una rutina, debe declarar que es determinista o que no cambia datos. Dos conjuntos de características de rutinas se aplica aquí:

    • y indican si una rutina siempre produce el mismo resultado para entradas dadas. Por defecto es si no se da ninguna característica, así que debe especificar explícitamente para declarar que la rutina es determinista.

      El uso de las funciones (o sus sinónimos) o no hacen una rutina no-determinista necesariamente. Para , el log binario incluye la fecha y hora y replica correctamente. también replica correctamente mientras se invoque sólo una vez dentro de una rutina. (Puede considerar la fecha y hora de ejecución de la rutina y la semilla de números aleatorios como entradas implícitas que son idénticas en el maestro y el esclavo.)

    • , , , y proporciona información acerca de si la rutina lee o escribe datos. Tanto o indican que una rutina no cambia datos, pero debe especificar uno de estos explícitamente ya que por defecto es si ninguna de estas características se da.

    Por defecto, para que un comando o sea aceptado, o y deben especificarse explícitamente. De otro modo ocurre un error:

    ERROR 1418 (HY000): This routine has none of DETERMINISTIC, NO SQL,
    or READS SQL DATA in its declaration and binary logging is enabled
    (you *might* want to use the less safe log_bin_trust_routine_creators
    variable)
    

    Si asigna a 1, el requerimiento que la rutina sea determinista o que no modifique datos se elimina.

    Tenga en cuenta que la naturaleza de una rutina se basa en la “honestidad” del creador: MySQL no comprueba que una rutina declarada no contenga comandos que produzcan productos no deterministas.

  • Un comando se escribe en el log binario si la rutina no retorna error, pero no en otro caso. Cuando una rutina que modifica datos falla, obtiene esta advertencia:

    ERROR 1417 (HY000): A routine failed and has neither NO SQL nor
    READS SQL DATA in its declaration and binary logging is enabled; if
    non-transactional tables were updated, the binary log will miss their
    changes
    

    Este logueo del comportamiento tiene un potencial para causar problemas. Si una rutina modifica parcialmente una tabla no transaccional (tal como una tabla ) y retorna un error, el log binario no refleja estos cambios. Para protegerse de esto, debe usar trablas transaccionales en la rutina y modificar las tablas dentro de transacciones.

    Si usa la palabra clave con , , o para ignorar errores dentro de una rutina, una actualización parcial puede ocurrir sin producir error. Tales comandos se loguean y se replican normalmente.

  • Si una función almacenada se invoca dentro de un comando tal como que no modifica datos, la ejecución de la función no se escribe en el log binario, incluso si la función misma modifica datos. Este comportamiento de logueo tiene potencial para causar problemas. Suponga que una función se define así:

    CREATE FUNCTION myfunc () RETURNS INT
    BEGIN
      INSERT INTO t (i) VALUES(1);
      RETURN 0;
    END;
    

    Dada esta definición, el comando siguiente modifica la tabla porque modifica , pero el comando no se escribe en el log binario porque es un :

    SELECT myfunc();
    

    Una solución de este problema es invocar funciones que actualizan dentro de comandos que hacen actualizaciones. Tenga en cuenta que aunque el comando a veces se ejecuta como efecto colateral de evaluar una expresión, no es una solución aquí porque no está escrito en el log binario.

  • Los comandos ejecutados dentro de una rutina no se escriben en el log binario. Suponga que ejectua los siguientes comandos:

    CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
    CALL mysp;
    

    Para este ejemplo, los comandos y aparecerán en el log binario. El comando no aparecerá. Esto arregla el problema que ocurre antes de MySQL 5.0.6 en que los comandos y no se loguearon y se logueó.

  • En servidores esclavos, la siguiente limitación se aplica cuando se determina qué eventos del maestro se replican: reglas no se aplican a comandos o a comandos dentro de rutinas: Las reglas en estos casos siempre retornan “replica!

Los disparadores son similares a los procedimientos almacenados, así que las notas precedentes también se aplican a disparadores con las siguientes excepciones: no tiene una característica opcional, así que los disparadores se asumen como deterministas. Sin embargo, esta hipótesis puede ser inválida en algunos casos . Por ejemplo, la función no es determinista (y no replica). Debe ser cuidadoso acerca de usar tales funciones y disparadores.

Los disparadores actualmente no actualizan tablas, pero lo harán en el futuro. Por esta razón, los mensajes de error similares a los de los procedimientos almacenados ocurren con si no tiene el permiso y es 0.

Los temas tratados en esta sección son resultado del hecho que el logueo binario se hace a nivel de comandos SQL. MySQL 5.1 introducirá logueo binario a nivel de registro, lo que ocurre en un nivel más granular que especifica qué cambios hacer a registros individuales como resultado de ejecutar comandos SQL.