Diferencia entre revisiones de «Mysql Eventos»

De MediaWiki
Ir a la navegación Ir a la búsqueda
(No se muestran 9 ediciones intermedias del mismo usuario)
Línea 25: Línea 25:
 
=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 EVENTE].
+
* 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.
 
* Se necesita el [https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_event privilegio EVENT] sobre la base de datos.
  
Línea 50: Línea 50:
 
* Veamos un ejemplo sobre la base de datos employees (recordar cambiar el valor de la variable del sistema event_scheduler).
 
* 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.
 
: 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.
+
: Dichos nombres de guardarán en una tabla de nombre empleados_mes <u>creada previamente</u>.
 
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 
::<syntaxhighlight lang="java" line enclose="div" highlight="" >
 
delimiter \\
 
delimiter \\
Línea 60: Línea 60:
 
DO
 
DO
 
BEGIN
 
BEGIN
DECLARE nombre varchar(30);
+
 
   
+
            INSERT INTO empleados_mes (nombre)
SELECT concat(first_name,' ',last_name) as nombre
+
            SELECT concat(first_name,' ',last_name)  
        INTO nombre
+
            FROM employees.employees
        FROM employees.employees
+
    WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);
WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH);
 
       
 
        INSERT INTO empleados_mes (nombre)
 
        VALUES (nombre);
 
       
 
   
 
 
END\\
 
END\\
 
      
 
      
Línea 146: Línea 140:
  
 
[[Imagen:Mysql_eventos_2.jpg|400px|center]]
 
[[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 />
 
<br />
Línea 151: Línea 158:
 
=Ejercicios propuestos=
 
=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.
  
* Queremos registrar, cada 1 minuto el día de hoy y durante una 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.
+
: Modifica el evento y haz que el evento no desaparezca cuando termine el intervalo de ejecución.
: Haz que el evento no desaparezca cuando termine el intervalo de ejecución.
 
: Crea dos usuarios y conéctate a la base de de datos. Después desconéctate de la base de datos.
 
  
 
: 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)
 
: 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)
Línea 172: Línea 187:
 
* 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.
 
* 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.
 
: Cambia la hora del sistema para comprobar que se ejecuta el evento.
 
  
  
Línea 181: Línea 195:
  
  
 +
* Borra uno de los eventos, asegurándote de que exista.
  
* 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).