MySql Event Scheduler

What is an event? 

Event is 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.

In Other word,

Events are tasks that run according to a schedule. It also called scheduled events.

It is similar to the idea of the Unix crontab (a.k.a. a cron job) or the Windows Task Scheduler.

• An event is uniquely identified by its name and the schema to which it is assigned.

• An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in BEGIN .. END;

• An event's timing can be either one-time or recurrent.

Support: ~

     Events have been supported in MySQL since version 5.1.*

Possible Values:~

1. OFF (default Value).
2. ON
3. DISABLE

• OFF : The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.
• ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events.
• DISABLED: This value renders the Event Scheduler non-operational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.

Important:~

It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime.

Turn ON-OFF the event_scheduler:~

If the Event Scheduler status has not been set to DISABLED, event_scheduler can be toggled between 1/ON and 0/OFF (using SET).

Any of the following 4 statements can be used to turn on the Event Scheduler: (By default its value is OFF)

1. SET GLOBAL event_scheduler = ON;
2. SET @@global.event_scheduler = ON;
3. SET GLOBAL event_scheduler = 1;
4. SET @@global.event_scheduler = 1;

Similarly, any of these 4 statements can be used to turn off the Event Scheduler:

1. SET GLOBAL event_scheduler = OFF;
2. SET @@global.event_scheduler = OFF;
3. SET GLOBAL event_scheduler = 0;
4. SET @@global.event_scheduler = 0;

Also have to add:

event_scheduler=ON To the /etc/my.conf in the [mysqld] section.

Check status of the event_scheduler:~

SELECT @@event_scheduler;

SELECT @@GLOBAL.event_scheduler;

mysql> select @@GLOBAL.event_scheduler ;

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

| @@GLOBAL.event_scheduler |

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

| OFF                      |

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

1 row in set (0.00 sec)

When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process, and its state is represented as shown here:

mysql> SHOW PROCESSLIST;

Row Key	Data for First Row	Data for Second row
Id:	1	2
User:	root	event_scheduler
Host:	localhost:49168	localhost
db:	sam	NULL
Command:	Query	Daemon
Time:	0	25
State:	NULL	Waiting for next activation
Info:	SHOW PROCESSLIST	NULL

2 rows in set (0.00 sec)

Syntax:~

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}

About Syntax:~

• The DEFINER clause was added in MySQL 5.1.17.

• The DEFINER clause specifies the MySQL account to be used when checking access privileges at event execution time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE EVENT statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.
• If you specify the DEFINER clause, these rules determine the valid DEFINER user values.
• If you do not have the SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account.
• If you have the SUPER privilege, you can specify any syntactically valid account name. If the account does not actually exist, a warning is generated.
• Although it is possible to create an event with a nonexistent DEFINER account, an error occurs at event execution time if the account does not exist.
• IF NOT EXISTS has the same meaning for CREATE EVENT as for CREATE TABLE: If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.)
• The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms:
• AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a date time value. You may use a value of either the DATETIME or TIMESTAMP type for this purpose. If the date is in the past, a warning occurs.

NOTE:~

• The event will not run unless the Event Scheduler is enabled. 

The minimum requirements for a valid CREATE EVENT statement are as follows:

1. The keywords CREATE EVENT plus an event name, which uniquely identifies the event within a database schema. (Prior to MySQL 5.1.12, the event name needed to be unique only among events created by the same user within a schema.)
2. An ON SCHEDULE clause, which determines when and how often the event executes.
3. A DO clause, which contains the SQL statement to be executed by an event.

Example1:~

CREATE EVENT myevent

    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

    DO

      UPDATE myschema.mytable SET mycol = mycol + 1;

Explanation.

The previous statement creates an event named myevent. This event executes once one hour following its creation by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1.

Example2:~

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

| NOW()               |

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

| 2012-10-11 22:26:39 |

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

1 row in set (0.03 sec)

mysql> CREATE EVENT past_event

    ->     ON SCHEDULE AT '2012-10-11 22:26:00'

    ->     DO INSERT INTO sam.user VALUES (NOW());

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;

Level	Code	Message
Note	1588	Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation.

1 row in set (0.00 sec)

To create an event, that occurs at some point in the future relative to the current date-time, use the optional clause [+ INTERVAL interval].

The interval portion consists of two parts, a quantity and a unit of V. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK

You can also combine intervals. For example,

 AT CURRENT_TIMESTAMP

+ INTERVAL 3 WEEK

+ INTERVAL 2 DAY

Each portion of such a clause must begin with + INTERVAL.

To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.)

For example, EVERY 6 WEEK

An EVERY clause may contain an optional STARTS clause. STARTS are followed by a timestamp value that indicates when the action should begin repeating, and may also use + INTERVAL interval to specify an amount of time. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means every three months, beginning one week from now. Similarly, you can express every two weeks, beginning six hours and fifteen minutes from now as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE.

Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP that is, the action specified for the event begins repeating immediately upon creation of the event.

Example3:~

mysql> CREATE EVENT insert_event ON SCHEDULE

EVERY 1 MINUTE

DO INSERT INTO User SELECT NOW();

Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE EVENT INSERT_EVENT\G

    Event : insert_event

 sql_mode:STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,

           NO_ENGINE_SUBSTITUTION

time_zone:SYSTEM

Create Event:CREATE DEFINER=`root`@`localhost` EVENT `

             insert_event` ON SCHEDULE EVERY 1 MINUTE 

             STARTS '2012-10-12 22:06:21' ON COMPLETION

             NOT PRESERVE ENABLE DO INSERT INTO User SELECT NOW()

character_set_client:cp850

collation_connection:cp850_general_ci

Database Collation:utf8_general_ci

1 row in set (0.00 sec)

Notes :~

• The event_name must be a valid MySQL identifier with a maximum length of 64 characters.
• Event names are not case sensitive
• An event is associated with a schema. If no schema is indicated as part of event_name, the default (current) schema is assumed.
• An event is immediately dropped once its schedule has expired (ON COMPLETION NOT PRESERVE). Set ON COMPLETION PRESERVE to prevent that behavior. Using ON COMPLETION NOT PRESERVE merely makes the default non persistent behavior explicit.
• You may use STARTS, ENDS, both, or neither in an EVERY clause.
• If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.
• You may supply a comment for an event using a COMMENT clause. Comment may be any string of up to 64 characters that you wish to use for describing the event. The comment text, being a string literal, must be surrounded by quotation marks.
• A CREATE EVENT statement that contains an ALTER EVENT statement in its DO clause appears to succeed; however, when the server attempts to execute the resulting scheduled event, the execution fails with an error.

To disable my_event, use this ALTER EVENT statement:

• ALTER EVENT my_event DISABLE; 

The schedule can be assigned various settings, e.g.

Syntax	Explanation	Example
AT ‘YYYY-MM-DD HH:MM.SS’	Run once on a specific date/time	AT ’2011-06-01 02:00.00’
AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]	Run once after a specific period has elapsed	AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]	Run at specific intervals forever	EVERY 1 DAY
EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date ENDS date	Run at specific intervals during a specific period	EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ENDS ’2012-01-01 00:00.00′

References:~

1. http://dev.mysql.com/doc/refman/5.1/en/events-overview.html
2. http://dev.mysql.com/doc/refman/5.1/en/events-configuration.html