Scheduled tasks in MySQL

Couple of days ago, during the training session one of the trainee(experienced in other DBMS) was really curious to know on how to setup & use Scheduled tasks in MySQL. I was also equally interested to spend some time on "Event Scheduler" as I was also getting a chance to show that how MySQL is equipped with the rich features but unfortunately the discussion ended before taking off. Most of the ppl felt its just a matter of one line adding to the crontab  & job is done. So most of users were not aware of the fact that MySQL has added a feature which could do what the Unix crontab  (also known as a “cron job”) or the Windows Task Scheduler can.

MySQL Events are tasks that run according to a schedule due to this can also be referred as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab  (also known as a “cron job”) or the Windows Task Scheduler.

(From Manual)

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server.

Version Introduced           5.1.6
Command Line Format     --event-scheduler[=value]
Config File Format           event-scheduler
Option Sets Variable        Yes, event_scheduler
Variable Name                 event_scheduler
Variable Scope                 Global
Dynamic Variable             Yes
Permitted Values   
            Type                    enumeration
            Default                OFF
            Valid Values         ON, OFF, DISABLED

From the mysql prompt you can enable/disable  using any one of the following statements

mysql>SET GLOBAL event_scheduler = ON;
mysql>SET @@global.event_scheduler = ON;
mysql>SET GLOBAL event_scheduler = 1;
mysql>SET @@global.event_scheduler = 1;

Also, In the server configuration file (my.cnf, or my.ini on Windows systems), include the line where it will be read by the server (for example, in a [mysqld] section): 

event_scheduler=ON|OFF|DISABLED


So now we know that what needs to be done at the server level in order to use the feature. Okay the platform is ready for event_scheduler and can demonstrate you with a tiny example.
 
 Just ensuring that event_scheduler is enabled or not

show global variables like 'event_scheduler';
event_scheduler    ON

 The result of the SQL command ensures me that "event_scheduler" is enabled now and I can use the feature(but this settings should be made permanent by adding it to config file ).

Syntax for CREATE EVENT

CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;

In which - the "event_name" must be a valid identifier of up to 64 characters (e.g me_ushastry, me_trainer etc ushastry1978 etc), with delimiters allowed (`Something To Do`), possibly qualified (database1.event1). Events are database objects, so they are stored within a database and event names must be unique within that database. When checking for uniqueness, MySQL uses case-insensitive comparisons.

The "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.

The possibilities are:

• AT timestamp [+ interval integer_value time_keyword ]
• EVERY interval
• EVERY interval STARTS timestamp
• EVERY interval ENDS timestamp
• EVERY interval STARTS timestamp ENDS timestamp

"AT timestamp" means "Do this once at the specified time". The timestamp must contain both date and time (that is, it must be a DATETIME or TIMESTAMP value) and must be in the future -- you cannot make an event which is supposed to have already occurred. To specify an exact time, you can also add an interval to the timestamp (using + INTERVAL, a positive integer and one of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND); this makes sense only when you're using the CURRENT_TIMESTAMP function. Here are two examples:

Scenario is like we have a table named `tbl_users` and we have to delete the records from the table every  5 minutes and we want to automate this process so that no one has to sit and run the piece of SQL code manually.



 # Checking "event_scheduler"

create database mysqlscheduler;
use mysqlscheduler;

CREATE TABLE IF NOT EXISTS `tbl_users`
(                     
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `name` varchar(100) DEFAULT NULL,              
    `email` varchar(100) DEFAULT NULL,              
    `promo_emails` enum('YES','NO') DEFAULT 'NO',
    PRIMARY KEY (`id`)                             
) ENGINE=MyISAM;




# Add couple of records for testing


INSERT INTO mysqlscheduler.tbl_users VALUES('','Umesh','ushastry@gmail.com','NO'),('','Umesh','ushastry_duplicate@gmail.com','YES'),('','Umesh','ushastry_dummy@gmail.com','NO');

SELECT * FROM mysqlscheduler.tbl_users;

1    Umesh    ushastry@gmail.com    NO
2    Umesh    ushastry_duplicate@gmail.com    YES
3    Umesh    ushastry_dummy@gmail.com    NO
\N    \N    \N    NO



 Now time to create EVENT which would take care of deleting the records


CREATE EVENT drop_dummy_user
ON SCHEDULE EVERY 15 MINUTE
STARTS TIMESTAMP '2009-01-01 00:00:00'
ENDS TIMESTAMP '2009-12-31 23:59:00'
DO DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES';

C:\ushastry\MySQL_Com\mysql-5.1.34-win32\bin>mysql -uroot -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
                  db: mysqlscheduler
                name: drop_dummy_user
                body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
             definer: root@localhost
          execute_at: NULL
      interval_value: 15
      interval_field: MINUTE
             created: 2009-09-02 14:51:45
            modified: 2009-09-02 14:51:45
       last_executed: NULL
              starts: 2009-01-01 00:00:00
                ends: 2009-12-31 23:59:00
              status: ENABLED
       on_completion: DROP
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)

mysql>

Above command output can be read as

• the db column shows the name of the database that contains the event
• the name column holds the name of the event; recall that it must be unique within the database
• the definer column shows whose privileges will be checked when the event is executed; if the user shown no longer has the proper privileges to execute the event's SQL statement, an error will be returned
• the execute_at column shows when the event should be executed; this is always NULL unless the ON SCHEDULE clause uses the AT timestamp option
• the interval_value column shows the numeric value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the interval_field columns shows the datetime value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the created column shows the timestamp when the event was created
• the modified column shows the timestamp when the event was last modified; this will be the same as the created column if ALTER EVENT has never changed the event
• the last_executed column shows the timestamp of the last time the event was executed; in this example, it is NULL because the newly-created event hasn't been executed yet
• the starts column shows the timestamp when the event will start executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the ends column shows the timestamp when the event will stop executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the status column shows whether the event is currently enabled or disabled; in this example, it shows ENABLED, the default value
• the on_completion column shows whether the event will be preserved or dropped when it is finished; in this example, it shows that the event will be preserved, as specified in the CREATE EVENT statement
• the comment column shows the comment included in the CREATE EVENT statement

After 15 min or so you can check and confirm that the records has been deleted 


SELECT * FROM mysqlscheduler.tbl_users;

1    Umesh    ushastry@gmail.com    NO
3    Umesh    ushastry_dummy@gmail.com    NO
\N    \N    \N    NO


Also, can be verified from the event meta data that when it was executed last time



mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
                  db: mysqlscheduler
                name: drop_dummy_user
                body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
             definer: root@localhost
          execute_at: NULL
      interval_value: 15
      interval_field: MINUTE
             created: 2009-09-02 14:51:45
            modified: 2009-09-02 14:51:45
       last_executed: 2009-09-02 14:00:00
              starts: 2009-01-01 00:00:00
                ends: 2009-12-31 23:59:00
              status: ENABLED
       on_completion: DROP
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)
 


More details can be found here - http://dev.mysql.com/doc/refman/5.1/en/events.html