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:
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:
event-scheduler=ON
Reiniciamos mysql y podemos verificar que:
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:
CREATE TABLE test.prueba (
fecha_hora datetime
) ENGINE=InnoDB;
Ahora creamos el evento, siguiendo la sintaxis:
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:
CREATE event evento_prueba ON schedule every 1 minute do INSERT INTO prueba VALUES(now());
Y 4 minutos más tarde:
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:
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:
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:
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):
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:
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.
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.
muchismas gracias, excelente!!!
Muchisimas gracias por tu aporte, muy completo.