MySQL jobs, scheduling tasks

This time we will see how to perform a scheduled task in MySQL (relatively speaking a job Oracle).

First we have to make sure we have the scheduler started with this we see:

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)

It is not started, for this we have to change a parameter of the mysqld section in my.cnf:

Source   
event-scheduler=ON

We restart mysql and can verify that:

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)

To test we will use the table:

Source   
CREATE TABLE test.test_table (
date_time datetime
) ENGINE=InnoDB;

Now we create the event, following syntax:

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}

In our case:

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

And 4 minutes later:

Source   
mysql> SELECT * FROM test_table;
+---------------------+
| date_time          |
+---------------------+
| 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)

Now we can see the event:

Source   
mysql> SHOW events\G
*************************** 1. row ***************************
Db: test
Name: event_test
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)

An event can disable and enable in convenience:

Source   
mysql> ALTER event event_test disable;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW events\G
*************************** 1. row ***************************
Db: test
Name: event_test
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 event_test enable;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW events\G
*************************** 1. row ***************************
Db: test
Name: event_test
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)

Modify the schedule:

Source   
mysql> ALTER event event_test ON schedule every 30 second;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test_table;
+---------------------+
| date_time          |
+---------------------+
| 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)

We deactivate the event and replace it to call a stored procedure (which does the same):

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

We can also obtain event creation statement with:

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

I hope you find it useful.

Leave a Reply