Jobs en MySQL

Esta vez vamos a ver la forma de realizar una tarea programada dentro de MySQL (salvando las distancias, un job de Oracle).

Primero tenemos que asegurarnos de que tenemos el scheduler arrancado esto lo vemos con un:

Source   
mysql> SHOW processlist;
+----+------+-----------------+------------+---------+------+-------+------------------+
| Id | User | Host            | db         | Command | Time | State | Info             |
+----+------+-----------------+------------+---------+------+-------+------------------+
|  6 | root | localhost:49987 | assets_pru | Sleep   |  299 |       | NULL             |
|  8 | root | localhost       | assets_pru | Query   |    0 | NULL  | SHOW processlist |
+----+------+-----------------+------------+---------+------+-------+------------------+
2 rows IN SET (0.00 sec)

No está arrancado, para esto tenemos que modificar un parámetro de my.cnf en la seccion mysqld:

Source   
event-scheduler=ON

Reiniciamos mysql y podemos verificar que:

Source   
mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row IN SET (0.00 sec)
mysql> SHOW processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  1 | event_scheduler | localhost | NULL | Daemon  |   15 | Waiting ON empty queue | NULL             |
|  3 | root            | localhost | NULL | Query   |    0 | NULL                   | SHOW processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows IN SET (0.00 sec)

Para hacer pruebas vamos a usar la tabla:

Source   
CREATE TABLE test.prueba (
fecha_hora datetime
) ENGINE=InnoDB;

Ahora creamos el evento, siguiendo la sintaxis:

Source   
CREATE
[DEFINER = { user | CURRENT_USER }]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'comment']
DO event_body;
schedule:
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Para nuestro caso:

Source   
CREATE event evento_prueba ON schedule every 1 minute do INSERT INTO prueba VALUES(now());

Y 4 minutos más tarde:

Source   
mysql> SELECT * FROM prueba;
+---------------------+
| fecha_hora          |
+---------------------+
| 2013-05-08 21:30:28 |
| 2013-05-08 21:31:28 |
| 2013-05-08 21:32:28 |
| 2013-05-08 21:33:28 |
+---------------------+
4 rows IN SET (0.00 sec)

Ahora podemos ver el evento con:

Source   
mysql> SHOW events\G
*************************** 1. row ***************************
Db: test
Name: evento_prueba
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval FIELD: MINUTE
Starts: 2013-05-08 21:27:51
Ends: NULL
STATUS: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
DATABASE Collation: latin1_swedish_ci
1 row IN SET (0.00 sec)

Un evento podemos desactivarlo y activarlo a conveniencia:

Source   
mysql> ALTER event evento_prueba disable;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW events\G
*************************** 1. row ***************************
Db: test
Name: evento_prueba
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval FIELD: MINUTE
Starts: 2013-05-08 21:30:28
Ends: NULL
STATUS: DISABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
DATABASE Collation: latin1_swedish_ci
1 row IN SET (0.00 sec)
mysql> ALTER event evento_prueba enable;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW events\G
*************************** 1. row ***************************
Db: test
Name: evento_prueba
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval FIELD: MINUTE
Starts: 2013-05-08 21:30:28
Ends: NULL
STATUS: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
DATABASE Collation: latin1_swedish_ci
1 row IN SET (0.00 sec)

Modificar la programación:

Source   
mysql> ALTER event evento_prueba ON schedule every 30 second;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM prueba;
+---------------------+
| fecha_hora          |
+---------------------+
| 2013-05-08 21:30:28 |
| 2013-05-08 21:31:28 |
| 2013-05-08 21:32:28 |
| 2013-05-08 21:33:28 |
| 2013-05-08 21:34:28 |
| 2013-05-08 21:35:28 |
| 2013-05-08 21:36:28 |
| 2013-05-08 21:55:28 |
| 2013-05-08 21:56:28 |
| 2013-05-08 21:56:44 |
| 2013-05-08 21:57:14 |
| 2013-05-08 21:57:44 |
+---------------------+
12 rows IN SET (0.00 sec)

Desactivamos el evento y lo sustituimos para llamar a un procedimiento almacenado(que hace lo mismo):

Source   
mysql> ALTER event evento_prueba disable;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter //
mysql> CREATE PROCEDURE inserta()
-> BEGIN
->    INSERT INTO prueba VALUES(now());
-> END
-> //
Query OK, 0 rows affected (0.07 sec)
mysql> delimiter ;
mysql> DROP event evento_prueba;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE event evento_prueba ON schedule every 1 minute do call inserta();
Query OK, 0 rows affected (0.00 sec)

También podemos obtener la sentencia de creación del evento con:

Source   
mysql> SHOW CREATE event evento_prueba\G
*************************** 1. row ***************************
Event: evento_prueba
sql_mode:
time_zone: SYSTEM
CREATE Event: CREATE DEFINER=`root`@`localhost` EVENT `evento_prueba` ON SCHEDULE EVERY 1 MINUTE STARTS '2013-05-08 22:08:43' ON COMPLETION NOT PRESERVE ENABLE DO call inserta()
character_set_client: utf8
collation_connection: utf8_general_ci
DATABASE Collation: latin1_swedish_ci
1 row IN SET (0.00 sec)

Espero que os sea de utilidad.

2 thoughts on “Jobs en MySQL

  1. Excelente artículo, todo lo que se necesita para empezar con el event-scheduler en MySQL bien resumido y explicado.

    Muchas gracias por tomarte el tiempo de compartirlo, espero que para los demas, sea de tan buen provecho como lo ha sido para mi.

Deja un comentario