How to schedule an event using MySQL event scheduler

This article shows how to create a scheduled event in MySQL database to execute some database/table related tasks. This method is an alternative to creating a cron job/Task Scheduler that executed a task.

What do you need

You will require the followings:

  • A user that has event privilege. To grant event privilege, use the following command
mysql> GRANT EVENT ON schema.* TO user@host;
  • The user also has the relevant CRUD privileges i.e. allowed to INSERT, DELETE, etc

For the purpose of this tutorial, I also have

  • A database called testdb
  • A table in the database called fruit
  • Some data in the table fruit.

 

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

mysql> USE testdb;
Database changed

mysql> CREATE TABLE testdb.`fruit` (
-> `id` INT(11) NOT NULL AUTO_INCREMENT,
-> `description` VARCHAR(50) NULL DEFAULT NULL,
-> `quantity` INT(11) NULL DEFAULT NULL,
-> `date_created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
-> `date_modified` TIMESTAMP NULL,
-> PRIMARY KEY (`id`)
-> )
-> ENGINE=InnoDB
-> AUTO_INCREMENT=4
-> ;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO testdb.fruit (description, quantity) VALUES ('apple', 10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testdb.fruit (description, quantity) VALUES ('orange', 5);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO testdb.fruit (description, quantity) VALUES ('banana', 0);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER 'testuser'@’%’ IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Steps

  • Check whether the event_scheduler process is running. Look for user event_scheduler row in the resulting query.
mysql> SHOW PROCESSLIST;
+----+------+-----------------+--------+---------+------+----------+------------------+
| Id | User | Host            | db     | Command | Time | State    | Info             |
+----+------+-----------------+--------+---------+------+----------+------------------+
| 26 | root | localhost:59421 | testdb | Sleep   |  117 |          | NULL             |
| 27 | root | localhost:59800 | testdb | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+--------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

As shown above, there is no event_scheduler process is listed. Start the event scheduler

  • Start the event_scheduler process, as below command
mysql> SET GLOBAL event_scheduler = ON;
Query OK, 0 rows affected (0.00 sec)
  • Check again to confirm its started.
mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
| Id | User            | Host            | db     | Command | Time | State                       | Info             |
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
| 26 | root            | localhost:59421 | testdb | Sleep   |  277 |                             | NULL             |
| 27 | root            | localhost:59800 | testdb | Query   |    0 | starting                    | SHOW PROCESSLIST |
| 28 | event_scheduler | localhost       | NULL   | Daemon  |    2 | Waiting for next activation | NULL             |
+----+-----------------+-----------------+--------+---------+------+-----------------------------+------------------+
3 rows in set (0.00 sec)

  • Create an event. In this example I create an event to add the number of apple by 1 every 15 seconds
mysql> CREATE EVENT add_1_apple
-> ON SCHEDULE
-> EVERY 15 SECOND
-> COMMENT 'Add an apple every 15 seconds'
-> DO
->   UPDATE testdb.fruit
->   SET quantity = quantity + 1,
->   DATE_MODIFIED = NOW()
->   WHERE description = 'apple';
Query OK, 0 rows affected (0.00 sec)
  • * change to MINUTE or HOUR if you want to run every 15 minutes or hours
  • Check the result. Notice the additional quantity and date modified has changed for apple
mysql> SELECT * FROM FRUIT;
+----+-------------+----------+---------------------+---------------------+
| id | description | quantity | date_created        | date_modified       |
+----+-------------+----------+---------------------+---------------------+
|  4 | apple       |       12 | 2017-11-14 17:23:01 | 2017-11-14 17:46:06 |
|  5 | orange      |        5 | 2017-11-14 17:23:01 | NULL                |
|  6 | banana      |        0 | 2017-11-14 17:23:03 | NULL                |
+----+-------------+----------+---------------------+---------------------+
3 rows in set (0.00 sec)
  • Example daily at 1 AM
    CREATE EVENT add_100_orange
    ON SCHEDULE
    EVERY 1 DAY
    STARTS (TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR)
    COMMENT ‘Add 100 oranges every day at 1AM’
    DO
    BEGIN
    ..
    ..
    END

    Run only at specific hours

    CREATE EVENT my_event
    ON SCHEDULE EVERY HOUR
    DO
    IF CURRENT_TIME BETWEEN '08:00:00' AND '18:00:00' THEN
    -- do stuff
    END IF

    To show lists of events that have been created

mysql> SHOW EVENTS FROM testdb;
+--------+-------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| Db     | Name        | Definer        | Time zone | Type      | Execute at | Interval value | Interval field | Starts              | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------+-------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
| testdb | add_1_apple | root@localhost | SYSTEM    | RECURRING | NULL       | 15             | SECOND         | 2017-11-14 17:45:36 | NULL | ENABLED |          0 | cp850                | cp850_general_ci     | latin1_swedish_ci  |
+--------+-------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+---------+------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
  • To delete an existing event
  • DROP EVENT [IF EXISTS] event_name;
mysql> DROP EVENT IF EXISTS add_1_apple;
  • You can also look at information_schema.events for events definition. For example.
mysql> SELECT event_schema, event_definition, status, starts, last_executed FROM INFORMATION_SCHEMA.EVENTS;
+--------------+-----------------------------------------------------------------------------------------------------+---------+---------------------+---------------------+
| event_schema | event_definition                                                                                    | status  | starts              | last_executed       |
+--------------+-----------------------------------------------------------------------------------------------------+---------+---------------------+---------------------+
| otherdb | INSERT INTO otherdb.grade(grade_code, grade_name) values('TT01', 'Event 1')                         | ENABLED | 2017-11-10 01:00:00 | 2017-11-13 06:41:20 |

| otherdb | insert into otherdb.grade(grade_code, grade_name) values('A','B')                                   | ENABLED | 2017-11-09 11:31:50 | 2017-11-14 18:20:20 |

| testdb       | UPDATE testdb.fruit

SET quantity = quantity + 1,

DATE_MODIFIED = NOW()

WHERE description = 'apple' | ENABLED | 2017-11-14 17:45:36 | 2017-11-14 18:20:21 |

+--------------+-----------------------------------------------------------------------------------------------------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)

That’s it!

References