My (SQL) WorkLog: September 2009

Wednesday, September 02, 2009

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