Diferencia entre revisiones de «Mysql Eventos»

De MediaWiki
Ir a la navegación Ir a la búsqueda
(Página creada con «=Introducción= * Los eventos son tareas que se ejecutan en base a un horario definido previamente. * Antes de la versión 5.7, los eventos eran 'simulados' haciendo uso...»)
 
(No se muestran 20 ediciones intermedias del mismo usuario)
Línea 18: Línea 18:
  
 
:<u>Nota:</u> Recordar que ya vimos en esta wiki como [http://wiki.cifprodolfoucha.es/index.php?title=Mysql_Instalaci%C3%B3n#Variables_del_sistema cambiar el valor de una variable global].
 
:<u>Nota:</u> Recordar que ya vimos en esta wiki como [http://wiki.cifprodolfoucha.es/index.php?title=Mysql_Instalaci%C3%B3n#Variables_del_sistema cambiar el valor de una variable global].
 +
  
  
 
<br />
 
<br />
 +
 
=Creación de eventos=
 
=Creación de eventos=
  
 +
* La orden SQL para crear eventos es [https://dev.mysql.com/doc/refman/5.7/en/create-event.html CREATE EVENT].
 +
* Se necesita el [https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_event privilegio EVENT] sobre la base de datos.
 +
 +
 +
* Entre las diferentes opciones tenemos:
 +
:* ON SCHEDULE: Indicamos el momento en que se va a ejecutar el evento y su frecuencia (una vez, una vez cada cierto tiempo, entre dos intervalos de tiempo,....)
 +
:* DEFINER: Al igual que en el caso de las vistas y procedimientos, indicamos el usuario con el que se va a ejecutar el evento y por lo tanto se ejecutará con los permisos que tenga asociados dicho usuario.
 +
:* event_body: Es el contenido del evento.
 +
:* ON COMPLETION PRESERVE indica que el evento, cuando se termina de ejecutar (y si ya no va a ejecutarse posteriormente) no se elimine (que es el comportamiento por defecto y se indica con la opción ON COMPLETION NOT PRESERVE).
 +
:* DISABLE indica que el evento está creado pero no se va a ejecutar. Para activarlo (normalmente con la orden SQL ALTER EVENT) pondremos ENABLE. La opción DISABLE ON SLAVE se utiliza en entornos de replicación con Mysql instalado en varios equipos. Lo que indicaría esta opción es que el evento sólo debe ejecutarse en el MASTER y no debe replicarse a los SLAVES.
 +
 +
 +
* Al igual que los procedimientos, podemos tener varias órdenes en el cuerpo del evento.
 +
: En ese caso deberemos poner después del DO '''BEGIN'''  Código del evento  '''END'''
 +
: Deberemos establecer un delimitador diferente antes del CREATE para separar el fin del CREATE con el fin de cada instrucción SQL que conforma el evento (al igual que pasaba en los procedimientos).
 +
 +
 +
* Una función que nos puede ser útil
 +
:* [https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_current-timestamp CURRENT_TIMESTAMP] que nos devuelve el día y hora actual del sistema y que se suele utilizar para establecer los intervalos de ejecución del evento de la forma: EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK (es un ejemplo)
 +
:* [https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html Funciones para el manejo de tiempos y fechas].
 +
 +
 +
 +
* Veamos un ejemplo sobre la base de datos employees (recordar cambiar el valor de la variable del sistema event_scheduler).
 +
: Vamos a crear un evento que se ejecute el día uno de cada mes y que obtenga los nombres de los empleados que hayan sido contratados durante el mes anterior.
 +
: Dichos nombres de guardarán en una tabla de nombre empleados_mes <u>creada previamente</u>.
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
delimiter \\
 +
 +
CREATE EVENT empleados_del_mes
 +
ON SCHEDULE
 +
EVERY 1 MONTH
 +
COMMENT 'Guarda los empleados contratados en el mes anterior'
 +
DO
 +
BEGIN
 +
 +
            INSERT INTO empleados_mes (nombre)
 +
            SELECT concat(first_name,' ',last_name)
 +
            FROM employees.employees
 +
    WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);
 +
END\\
 +
   
 +
delimiter ;
 +
</syntaxhighlight>
 +
 +
: Veremos después consultando los datos del evento, que éste no comenzará hasta el mes que viene.
 +
 +
 +
<br />
 +
 +
=Modificación de eventos=
 +
 +
* La orden SQL que modifica un evento es [https://dev.mysql.com/doc/refman/5.7/en/alter-event.html ALTER EVENT].
 +
* Se necesita el [https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_event privilegio EVENT] sobre la base de datos.
 +
 +
 +
* Veamos un ejemplo.
 +
: Sobre el evento creado en el paso anterior, haz una modificación para que comience en el mes actual:
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
ALTER EVENT empleados_del_mes
 +
ON SCHEDULE
 +
EVERY 1 MONTH STARTS NOW();
 +
</syntaxhighlight>
 +
 +
 +
 +
<br />
 +
 +
=Borrado de eventos=
 +
 +
* La orden SQL que modifica un evento es [https://dev.mysql.com/doc/refman/5.7/en/drop-event.html DROP EVENT].
 +
* Se necesita el [https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_event privilegio EVENT] sobre la base de datos.
 +
 +
 +
* Si intentamos borrar un evento que no existe sin utilizar la opción DROP EVENT '''IF EXISTS''' el gestor Mysql lanzará una excepción con el mensaje: ERROR 1517 (HY000): Unknown event 'event_name'
 +
 +
* Veamos un ejemplo:
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
DROP EVENT [IF EXISTS] nombre_del_evento;
 +
</syntaxhighlight>
 +
 +
 +
 +
<br />
 +
 +
=Consulta de eventos=
 +
 +
* La orden SQL que permite obtener información de un evento es [https://dev.mysql.com/doc/refman/5.7/en/show-events.html SHOW EVENTS].
 +
: Se necesita el [https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_event privilegio EVENT] sobre la base de datos.
 +
 +
: Para ver los eventos asociados a una base de datos específica podemos hacer uso de la palabra FROM de la forma: SHOW EVENTS '''FROM''' nombre_bd;
 +
: También podemos 'buscar' como ya hicimos anteriormente:
 +
:* Si queremos buscar por los eventos por su nombre podemos hacer uso de la cláusula '''LIKE''' que busca por patrón (símbolo comodín el %) nombres de eventos.
 +
:* Si queremos buscar dentro de los resultados aquellas filas que cumplan alguna condición asociada al valor de alguna columna debemos hacer uso la cláusula '''WHERE'''. Por ejemplo: SHOW EVENTS WHERE Definer like 'root%'
 +
 +
: Veamos un ejemplo:
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
SHOW EVENTS;
 +
</syntaxhighlight>
 +
: La orden anterior muestra información sobre quien creó el evento, cuando comienza a ejecutarse y su intervalo de ejecución, cuando finaliza, si está habilitado y sobre qué base de datos actúa.
 +
 +
[[Imagen:Mysql_eventos_1.jpg|400px|center]]
 +
 +
 +
 +
* Si queremos obtener el código del evento, debemos de ejecutar la orden SQL [https://dev.mysql.com/doc/refman/5.7/en/show-create-event.html SHOW CREATE EVENT].
 +
: También podemos consultar directamente el diccionario de datos, concretamente la [https://dev.mysql.com/doc/refman/5.7/en/events-table.html tabla EVENTS] de la base de datos INFORMATION_SCHEMA.
 +
 +
: Veamos un ejemplo:
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
SHOW CREATE EVENT empleados_del_mes;
 +
</syntaxhighlight>
 +
 +
 +
[[Imagen:Mysql_eventos_2.jpg|400px|center]]
 +
 +
 +
 +
<br />
 +
* '''IMPORTANTE:''' Para obtener información sobre cómo fue la ejecución de un evento debemos consultar al fichero de log (por defecto es /var/log/mysql/error.log):
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
cat /var/log/mysql/error.log | grep Event
 +
</syntaxhighlight>
 +
 +
 +
 +
[[Imagen:Mysql_eventos_3.jpg|600px|center]]
 +
 +
 +
<br />
 +
 +
=Ejercicios propuestos=
 +
 +
*<u>Nota:</u> Para crear una tabla podemos hacer uso de la orden SQL: CREATE TABLE nombre_table SELECT col1,col2,... FROM tabla
 +
:: Logicamente si la tabla existe dará un error, por lo que deberíamos borrarla previamente en caso de que exista: DROP TABLE IF EXISTS nombre_tabla;
 +
:: Si quisiéramos añadir filas una vez creada, en vez de hacer el drop en caso de que exista, tendríamos que hacer un insert multifila:
 +
::* Comprobamos si existe: SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'BASE_DE_DATOS') AND (TABLE_NAME = 'TABLA_A_BUSCAR');
 +
::* Insert multifila: INSERT INTO tabla_destino (col_tabla_destino) SELECT col FROM tabla_origen;
 +
 +
 +
 +
* Queremos registrar, cada 1 minuto en el día de hoy y durante unos 10 minutos desde el momento de creación del evento, qué usuarios están accediendo a la base de datos employees. Estos usuarios (sus nombres y equipos) deberán quedar registrados en una tabla de nombre 'historico_usuarios_hora' en la que se guardará el nombre del usuario así como el día-hora-minuto en el que se produjo el registro de actividad.
 +
: Crea dos usuarios y conéctate a la base de de datos. Después haz que uno de ellos se desconecte la base de datos.
 +
 +
: Modifica el evento y haz que el evento no desaparezca cuando termine el intervalo de ejecución.
 +
 +
: Modifica el evento para que se vuelva a ejecutar en el minuto siguiente al actual y comprueba que registra nuevamente a uno de los usuarios anteriores (que se vuelva a conectar)
 +
 +
 +
* Haz que el día 30 de Abril del 2018 a las 23:15 se guarde una copia de los datos de la tabla departments en una tabla creada por ti previamente de nombre dept_copia_temp
 +
 +
 +
 +
* Crea una table de nombre empleados_numero_mensual que guarde el número de empleados que tiene la empresa. Haz que se ejecute una vez 4 días después de que se cree el evento.
 +
: Ejecuta la orden que muestre el momento en que se va a ejecutar.
 +
: Modifica el evento para que se ejecute desde el día de hoy hasta dentro de un año y que lo haga cada mes.
 +
: Ejecuta la orden que muestre el momento en que se va a ejecutar.
 +
 +
 +
 +
* Haz que el día 1 de Enero de cada año se actualicen los salarios de los manager de cada departamento un 10% (trabajan mucho :) que estén trabajando actualmente en la empresa). Esta actualización se debe producir durante 5 años a partir del actual.
 +
: Cambia la hora del sistema para comprobar que se ejecuta el evento.
 +
 +
 +
 +
* Muestra todos los eventos creados de la base de datos employees.
 +
: Muestra todos los eventos creados de la base de datos employees y que fueran creados por el usuario root.
 +
 +
 +
 +
* Borra uno de los eventos, asegurándote de que exista.
  
  
 +
* Crea un evento que se ejecute cada día a las 22:00 y que pase a un archivo .csv el contenido de la tabla departments, con el nombre departments_datos.csv
 +
: Para hacerlo puedes hacer uso de una tabla con motor CSV o bien haciendo uso de la orden [https://dev.mysql.com/doc/refman/8.0/en/select-into.html SQL SELECT INTO]
 +
: En el enlace anterior podemos ver un ejemplo de uso:
 +
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 +
  SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
 +
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
 +
  LINES TERMINATED BY '\n'
 +
  FROM test_table;
 +
</syntaxhighlight>
 +
: <u>Nota:</u> En la versión actual el único directorio por defecto al que se puede escribir con la orden anterior es '''/var/lib/mysql-files/'''
 +
:: Si se quiere escribir en otro lugar del disco habría que modificar la [https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_secure_file_priv variable del sistema secure_file_priv].
  
  

Revisión del 20:05 11 feb 2019

Introducción

  • Los eventos son tareas que se ejecutan en base a un horario definido previamente.
  • Antes de la versión 5.7, los eventos eran 'simulados' haciendo uso de la función cron del sistema operativo LINUX o con el programador de tareas de Windows.
  • Un evento se identifica por su nombre. Cada evento está asociado a un esquema (base de datos) y se ejecuta en un intervalo de tiempo determinado.
Un evento ejecuta un conjunto de instrucciones SQL formando un bloque dentro de un BEGIN - END.


  • Los eventos se pueden programar para que se ejecuten una única vez o periódicamente.
  • Si vale OFF: El thread que se encarga de ejecutar los eventos está desactivado y por lo tanto no se ejecutará ningún evento. La tarea 'event_sheduler' no se muestra con la orden SHOW PROCESSLIST. Es el valor por defecto. En este estado, para activar el planificador de eventos debemos darle el valor ON a esta variable.
  • Si vale ON: El 'Event Scheduler' (planificador de eventos) es iniciado. En este caso aparecerá como un proceso más al ejecutar la orden SQL: SHOW PROCESSLIST. Para desactivarlo tendremos que cambiar su valor a OFF.
  • Si vale DISABLED: En este caso el planificador de eventos no podría ser activado dinámicamente cambiando el valor de la variable global a ON.
Nota: Recordar que ya vimos en esta wiki como cambiar el valor de una variable global.



Creación de eventos


  • Entre las diferentes opciones tenemos:
  • ON SCHEDULE: Indicamos el momento en que se va a ejecutar el evento y su frecuencia (una vez, una vez cada cierto tiempo, entre dos intervalos de tiempo,....)
  • DEFINER: Al igual que en el caso de las vistas y procedimientos, indicamos el usuario con el que se va a ejecutar el evento y por lo tanto se ejecutará con los permisos que tenga asociados dicho usuario.
  • event_body: Es el contenido del evento.
  • ON COMPLETION PRESERVE indica que el evento, cuando se termina de ejecutar (y si ya no va a ejecutarse posteriormente) no se elimine (que es el comportamiento por defecto y se indica con la opción ON COMPLETION NOT PRESERVE).
  • DISABLE indica que el evento está creado pero no se va a ejecutar. Para activarlo (normalmente con la orden SQL ALTER EVENT) pondremos ENABLE. La opción DISABLE ON SLAVE se utiliza en entornos de replicación con Mysql instalado en varios equipos. Lo que indicaría esta opción es que el evento sólo debe ejecutarse en el MASTER y no debe replicarse a los SLAVES.


  • Al igual que los procedimientos, podemos tener varias órdenes en el cuerpo del evento.
En ese caso deberemos poner después del DO BEGIN Código del evento END
Deberemos establecer un delimitador diferente antes del CREATE para separar el fin del CREATE con el fin de cada instrucción SQL que conforma el evento (al igual que pasaba en los procedimientos).


  • Una función que nos puede ser útil
  • CURRENT_TIMESTAMP que nos devuelve el día y hora actual del sistema y que se suele utilizar para establecer los intervalos de ejecución del evento de la forma: EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK (es un ejemplo)
  • Funciones para el manejo de tiempos y fechas.


  • Veamos un ejemplo sobre la base de datos employees (recordar cambiar el valor de la variable del sistema event_scheduler).
Vamos a crear un evento que se ejecute el día uno de cada mes y que obtenga los nombres de los empleados que hayan sido contratados durante el mes anterior.
Dichos nombres de guardarán en una tabla de nombre empleados_mes creada previamente.
 1 delimiter \\
 2 
 3 CREATE EVENT empleados_del_mes
 4 	ON SCHEDULE 
 5 		EVERY 1 MONTH
 6 	COMMENT 'Guarda los empleados contratados en el mes anterior'
 7 	DO
 8 	BEGIN
 9 
10             INSERT INTO empleados_mes (nombre)
11             SELECT concat(first_name,' ',last_name) 
12             FROM employees.employees
13 	    WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);
14 	END\\
15     
16 delimiter ;
Veremos después consultando los datos del evento, que éste no comenzará hasta el mes que viene.



Modificación de eventos


  • Veamos un ejemplo.
Sobre el evento creado en el paso anterior, haz una modificación para que comience en el mes actual:
1 ALTER EVENT empleados_del_mes
2 ON SCHEDULE 
3 	EVERY 1 MONTH STARTS NOW();



Borrado de eventos


  • Si intentamos borrar un evento que no existe sin utilizar la opción DROP EVENT IF EXISTS el gestor Mysql lanzará una excepción con el mensaje: ERROR 1517 (HY000): Unknown event 'event_name'
  • Veamos un ejemplo:
1 DROP EVENT [IF EXISTS] nombre_del_evento;



Consulta de eventos

  • La orden SQL que permite obtener información de un evento es SHOW EVENTS.
Se necesita el privilegio EVENT sobre la base de datos.
Para ver los eventos asociados a una base de datos específica podemos hacer uso de la palabra FROM de la forma: SHOW EVENTS FROM nombre_bd;
También podemos 'buscar' como ya hicimos anteriormente:
  • Si queremos buscar por los eventos por su nombre podemos hacer uso de la cláusula LIKE que busca por patrón (símbolo comodín el %) nombres de eventos.
  • Si queremos buscar dentro de los resultados aquellas filas que cumplan alguna condición asociada al valor de alguna columna debemos hacer uso la cláusula WHERE. Por ejemplo: SHOW EVENTS WHERE Definer like 'root%'
Veamos un ejemplo:
1 SHOW EVENTS;
La orden anterior muestra información sobre quien creó el evento, cuando comienza a ejecutarse y su intervalo de ejecución, cuando finaliza, si está habilitado y sobre qué base de datos actúa.
Mysql eventos 1.jpg


  • Si queremos obtener el código del evento, debemos de ejecutar la orden SQL SHOW CREATE EVENT.
También podemos consultar directamente el diccionario de datos, concretamente la tabla EVENTS de la base de datos INFORMATION_SCHEMA.
Veamos un ejemplo:
1 SHOW CREATE EVENT empleados_del_mes;


Mysql eventos 2.jpg



  • IMPORTANTE: Para obtener información sobre cómo fue la ejecución de un evento debemos consultar al fichero de log (por defecto es /var/log/mysql/error.log):
1 cat /var/log/mysql/error.log | grep Event


Mysql eventos 3.jpg



Ejercicios propuestos

  • Nota: Para crear una tabla podemos hacer uso de la orden SQL: CREATE TABLE nombre_table SELECT col1,col2,... FROM tabla
Logicamente si la tabla existe dará un error, por lo que deberíamos borrarla previamente en caso de que exista: DROP TABLE IF EXISTS nombre_tabla;
Si quisiéramos añadir filas una vez creada, en vez de hacer el drop en caso de que exista, tendríamos que hacer un insert multifila:
  • Comprobamos si existe: SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'BASE_DE_DATOS') AND (TABLE_NAME = 'TABLA_A_BUSCAR');
  • Insert multifila: INSERT INTO tabla_destino (col_tabla_destino) SELECT col FROM tabla_origen;


  • Queremos registrar, cada 1 minuto en el día de hoy y durante unos 10 minutos desde el momento de creación del evento, qué usuarios están accediendo a la base de datos employees. Estos usuarios (sus nombres y equipos) deberán quedar registrados en una tabla de nombre 'historico_usuarios_hora' en la que se guardará el nombre del usuario así como el día-hora-minuto en el que se produjo el registro de actividad.
Crea dos usuarios y conéctate a la base de de datos. Después haz que uno de ellos se desconecte la base de datos.
Modifica el evento y haz que el evento no desaparezca cuando termine el intervalo de ejecución.
Modifica el evento para que se vuelva a ejecutar en el minuto siguiente al actual y comprueba que registra nuevamente a uno de los usuarios anteriores (que se vuelva a conectar)


  • Haz que el día 30 de Abril del 2018 a las 23:15 se guarde una copia de los datos de la tabla departments en una tabla creada por ti previamente de nombre dept_copia_temp


  • Crea una table de nombre empleados_numero_mensual que guarde el número de empleados que tiene la empresa. Haz que se ejecute una vez 4 días después de que se cree el evento.
Ejecuta la orden que muestre el momento en que se va a ejecutar.
Modifica el evento para que se ejecute desde el día de hoy hasta dentro de un año y que lo haga cada mes.
Ejecuta la orden que muestre el momento en que se va a ejecutar.


  • Haz que el día 1 de Enero de cada año se actualicen los salarios de los manager de cada departamento un 10% (trabajan mucho :) que estén trabajando actualmente en la empresa). Esta actualización se debe producir durante 5 años a partir del actual.
Cambia la hora del sistema para comprobar que se ejecuta el evento.


  • Muestra todos los eventos creados de la base de datos employees.
Muestra todos los eventos creados de la base de datos employees y que fueran creados por el usuario root.


  • Borra uno de los eventos, asegurándote de que exista.


  • Crea un evento que se ejecute cada día a las 22:00 y que pase a un archivo .csv el contenido de la tabla departments, con el nombre departments_datos.csv
Para hacerlo puedes hacer uso de una tabla con motor CSV o bien haciendo uso de la orden SQL SELECT INTO
En el enlace anterior podemos ver un ejemplo de uso:
1   SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
2   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
3   LINES TERMINATED BY '\n'
4   FROM test_table;
Nota: En la versión actual el único directorio por defecto al que se puede escribir con la orden anterior es /var/lib/mysql-files/
Si se quiere escribir en otro lugar del disco habría que modificar la variable del sistema secure_file_priv.




-- Ángel D. Fernández González -- (2017).