Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

21.2.1. CREATE EVENT Syntax

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

schedule:
    AT timestamp [+ INTERVAL interval]
  | EVERY interval [STARTS timestamp] [ENDS timestamp]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

This statement creates and schedules a new event. The minimum requirements for a valid CREATE EVENT statement are as follows:

  • The keywords CREATE EVENT plus an event name, which uniquely identifies the event from among those created by the current user in the current schema.

  • An ON SCHEDULE clause, which determines when and how often the event executes.

  • A DO clause, which contains the SQL statement to be executed by an event.

This is an example of a minimal CREATE EVENT statement:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;

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.

The event_name must be a valid MySQL identifier with a maximum length of 64 characters. It may be delimited using back ticks, and may be qualified with the name of a database schema. An event is associated with both a MySQL user (the definer) and a schema, and its name must be unique among all events created by that user within that schema. In general, the rules governing event names are the same as those for names of stored routines. See Section 9.2, “Database, Table, Index, Column, and Alias Names”.

If no schema is indicated as part of event_name, then the default (current) schema is assumed. The definer is always the current MySQL user. It is possible for two different users to create different events having the same name on the same database schema. For example, the users [email protected] and [email protected] may each create an event named myevent on the database schema named myschema. These are entirely different events.

Note: MySQL uses case-insensitive comparisons when checking for the uniqueness of event names. This means that, for example, you cannot have two events named myevent and MyEvent created by the same MySQL user in the same database schema.

IF NOT EXISTS functions in the much the same fashion with CREATE EVENT as it does when used with a CREATE TABLE statement; 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.)

The ON SCHEDULE clause determines when, how often, and for how long the sql_statement defined for the event repeats. This clause takes one of two forms:

  • AT timestamp is used for a transient event. It specifies that the event executes one time only at the date and time, given as the timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value. You may use a value which is of either the DATETIME or TIMESTAMP type for this purpose. The timestamp must also be in the future — you cannot schedule an event to take place in the past. Trying to do so fails with an error, as shown here:

    mysql> SELECT NOW();
    +---------------------+
    | NOW()               |
    +---------------------+
    | 2006-02-10 23:59:01 |
    +---------------------+
    1 row in set (0.04 sec)
    
    mysql> CREATE EVENT e_totals
        ->     ON SCHEDULE AT '2006-02-10 23:59:00'
        ->     DO INSERT INTO test.totals VALUES (NOW());
    ERROR 1522 (HY000): Activation (AT) time is in the past
    

    CREATE EVENT statements which are themselves invalid — for whatever reason — fail with an error.

    You may use CURRENT_TIMESTAMP to specify the current date and time. In such a case, the event acts as soon as it is created.

    In order to create an event which occurs at some point in the future relative to the current date and time — such as that expressed by the phrase “three weeks from now” — you can use the optional clause + INTERVAL interval. The interval portion consists of two parts, a quantity and a unit of time, and follows the same syntax rules that govern intervals used in the DATE_ADD() function (see Section 12.5, “Date and Time Functions”. The units keywords are also the same, except that you cannot use any units involving microseconds when defining an event.

    You can also combine intervals. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY is equivalent to “three weeks and two days from now”. Each portion of such a clause must begin with + INTERVAL.

  • For actions which are to be repeated at a regular interval, you can use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous dicussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means “every six weeks”.

    It is not possible to combine + INTERVAL clauses in a single EVERY clause; however, you can use the same complex time units allowed in a + INTERVAL. For example, “every two minutes and ten seconds” can be expressed as EVERY '2:10' MINUTE_SECOND.

    An EVERY clause may also contain an optional STARTS clause. STARTS is followed by a timestamp value which indicates when the action should begin repeating, and may also use + INTERVAL interval in order to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 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 + '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.

    An EVERY clause may also contain an optional ENDS clause. The ENDS keyword is followed by a timestamp value which tells MySQL when the event should stop repeating. You may also use + INTERVAL interval with ENDS; for instance, EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK is equivalent to “every twelve hours, beginning thirty minutes from now, and ending four weeks from now”. Not using ENDS means that the event continues executing indefinitely.

    ENDS supports the same syntax for complex time units as STARTS does.

    You may use STARTS, ENDS, both, or neither in an EVERY clause.

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default non-persistent behavior explicit.

You can create an event but keep it from being active using the DISABLE keyword. Alternatively, you may use ENABLE to make explicit the default status, which is active. This is most useful in conjunction with ALTER EVENT (see Section 21.2.2, “ALTER EVENT Syntax”).

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.

The DO clause specifies an action carried by the event, and consists of an SQL statement. Nearly any valid MySQL statement which can be used in a stored routine can also be used as the action statement for a scheduled event. (See Section I.1, “Restrictions on Stored Routines and Triggers”.) For example, the following event e_hourly deletes all rows from the sessions table once per hour, where this table is part of the site_activity schema:

CREATE EVENT e_hourly
    EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;

Note: The SHOW statement and SELECT statements that merely return a result set have no effect when used in an event; the output from these is not sent to the MySQL Monitor, nor is it stored anywhere. However, you can use statements such as SELECT INTO and INSERT ... SELECT that store a result. (See the next example in this section for an instance of the latter.)

Note: Any reference to a table in the DO clause must be qualified with the name of the schema in which the table occurs.

As with stored routines, you can use multiple statements in the DO clause by bracketing them with the BEGIN and END keywords, as shown here:

DELIMITER |

CREATE EVENT e_daily
    EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day.'
    DO
      BEGIN
        INSERT INTO site_activity.totals (when, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*) 
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |

DELIMITER ;

Note the use of the DELIMITER statement to change the statement delimiter, as with stored routines. See Section 19.2.1, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.

More complex compound statements, such as those used in stored routines, are possible in an event. This example uses local variables, an error handler, and a flow control construct:

DELIMITER |

CREATE EVENT e
    ON SCHEDULE EVERY 5 SECOND
    DO
      BEGIN
        DECLARE v INTEGER;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;

        SET v = 0;

        WHILE v < 5 DO
          INSERT INTO t1 VALUES (0);
          UPDATE t2 SET s1 = s1 + 1;
          SET v = v + 1;
        END WHILE;
    END |

DELIMITER ;

There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters:

CREATE EVENT e_call_myproc
    ON SCHEDULE AT CURRENT_TIMESTAMP + 1 DAY
    DO CALL myproc(5, 27); 

In addition, if the event's definer has the SUPER privilege, that event may read and write global variables. As granting this privilege entails a potential for abuse, extreme care must be taken in doing so.

Generally, any statements which are valid in stored routines may be used for action statements executed by events. For more information about statements allowable within stored routines, see Section 19.2, “Stored Procedure Syntax”. You can create an event as part of a stored routine, but an event cannot be created by another event.


 
 
  Published under the terms of the GNU General Public License Design by Interspire