Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Applications: MySQL > tech-resources > articlesmysql-events.html (Request Expert MySQL Support)
Skip navigation links

About the Author

Peter Gulutzan

Peter Gulutzan is a Software Architect at MySQL AB, and author of SQL Performance Tuning. He's also another Canadian member of the MySQL Documentation Team.

We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

Learn about new MySQL releases, technical articles, events and more.

MySQL 5.1 New Features: MySQL Events



By Peter Gulutzan

Copyright © 2006 by MySQL AB
“MySQL 5.1 New Features” Series

CONTENTS

Andrey Hristov added "Events" for MySQL version 5.1 when he was working on his master's thesis for the University of Applied Sciences in Stuttgart. We liked the feature a lot, and now Andrey works for MySQL. An inspiring story, eh? And now MySQL 5.1 is getting stabler, it's "beta" instead of "alpha". So we can try some real work with this Events feature.

Do you need help?X

Make an Event and Make it go

I took the latest "MySQL 5.1 - Beta release" from the MySQL downloads page and installed with Windows XP. Then I started the server, then I started a client (as root), and said:

mysql> use test;
Database changed
mysql> create table test.t (s1 timestamp);
Query OK, 0 rows affected (0.11 sec)

14mysql> create event e on schedule every 1 second do
       insert into test.t values (current_timestamp);
Query OK, 1 row affected (0.00 sec)

mysql> set global event_scheduler = 1;
Query OK, 0 rows affected (0.00 sec)

/* 3-second delay */

mysql> select * from test.t;
+---------------------+
| s1                  |
+---------------------+
| 2006-04-05 15:44:26 |
| 2006-04-05 15:44:27 |
| 2006-04-05 15:44:28 |
+---------------------+

The above is the "hello world" of MySQL events. It illustrates that a row is being added into t every 1 second, which is what I wanted.

CREATE EVENT syntax

CREATE EVENT
 [IF NOT EXISTS]                /* Note 1 */
 event_name                     /* Note 2 */
 ON SCHEDULE schedule           /* Note 3 */
 [ON COMPLETION [NOT] PRESERVE] /* Note 4 */
 [ENABLE | DISABLE]             /* Note 5 */
 [COMMENT 'comment']            /* Note 6 */
 DO sql_statement               /* Note 7 */

Note 1: [IF NOT EXISTS]

You'll find this optional clause in other CREATE statements too, for example "CREATE TABLE IF NOT EXISTS t ...". The clause always means: if the object is already there then leave it alone, if it's not there then do this creation.

Note 2: event_name

Names can be up to 64 characters long, and can be `delimited` with backticks (or "delimited" with double quotes if you're using ANSI mode).

Names must be unique within a schema; a schema (database) shouldn't have two events with the same name no matter what the MySQL manual says at the moment.

Opinion: avoid naming conventions, just let event_name be descriptive. The name `E1` is not descriptive, and the name `MONTH_END` is only descriptive if the periodicity is essential, that is, if it's impossible that you'd do this operation daily.

Examples:

MONTH_END
`Background Check`

Note 3: ON SCHEDULE schedule

There are two kinds of schedule:

Do you need more help?X

AT timestamp
/* "one-time schedule" */

EVERY number-of-time-units time-unit [STARTS timestamp] [ENDS timestamp]
/* "recurring schedule" */

In either kind of schedule, the timestamp can be any expression whose data type is TIMESTAMP or DATETIME, provided it's in the future (greater than CURRENT_TIMESTAMP) and provided it's before the last year of the Unix epoch (less than or equal to '2037-12-31 23:59:59').

Examples:

TIMESTAMP '2020-01-01 00:00:00' - INTERVAL '3' DAY
CURRENT_TIMESTAMP
timestampadd(YEAR,1,'2020-01-01 00:00:00')
20070707121212
'2007-01-01 23:59:59'

In a recurring schedule, the number-of-time-units is any integer expression except NULL and the time-unit is a keyword: YEAR, MONTH, DAY, HOUR, MINUTE, or SECOND.

Opinion: other time-units are legal (QUARTER, WEEK, YEAR_MONTH, DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, MINUTE_SECOND) but they are non-standard, unwieldy, and unnecessary.

Actually a one-time schedule is just a special case of a recurring schedule that stops immediately. These two statements differ technically but will generally accomplish the same thing:

CREATE EVENT Transient
ON SCHEDULE AT NOW()
DO SET @a=@a;

CREATE EVENT Recurring
ON SCHEDULE EVERY 1 YEAR STARTS NOW() ENDS NOW()
DO SET @a=@a;

Note 4: [ON COMPLETION [NOT] PRESERVE]

Here COMPLETION means "when the event can't happen any more" because the one-time event has occurred once or because the recurring event has passed the ENDS point. The only effect of saying PRESERVE is that the event won't be dropped automatically.

Can we help you?X

Opinion: usually you don't need this clause.

Note 5: [ENABLE | DISABLE]

The default is that an event is "enabled", that is, it will try to do its job. You might want to say DISABLE when you create a recurring event for the first time, and then turn it on or off whenever you need it, using "ALTER EVENT ... ENABLE" or "ALTER EVENT ... DISABLE".

Note 6: [COMMENT 'comment']

The comment will appear in the metadata, that is, when you're looking at information_schema tables you'll see COMMENT columns. The maximum comment length is 64 characters.

The following is the wrong way to add a comment:

CREATE EVENT Wrong ON SCHEDULE EVERY 1 DAY
DO /* Daily report for lower management */ CALL report157();

The trouble is, inline comments are stripped by the MySQL parser, so you won't see them again. So the following is the right way to add a comment:

CREATE EVENT Right ON SCHEDULE EVERY 1 DAY
COMMENT 'Daily report for lower management'
DO CALL report157();

Opinion: Of course it's best if an event is self-explanatory, because it has a descriptive name and because the event's statement is not obscure code. But that's not true when we have numbered stored procedures.

Note 7: DO sql_statement

Whenever an event goes into action, it executes the SQL statement in this clause.

The SQL statement can be compound, for example BEGIN DROP TABLE test.t1; DROP TABLE test.t2; END If you're using compound statements with mysql client, remember to use DELIMITER.

Can't find what you're looking for?X

There are a few restrictions about what can be in the SQL statement. Generally we think of the statements as 'like functions or triggers' so if you can't do something with functions/triggers then you might not be able to do it with events either.

Specifically you can't say:

   LOCK TABLES
   UNLOCK TABLES
   CREATE EVENT (bug#18896)
   ALTER EVENT (bug#18896)
   LOAD DATA
CREATE EVENT is not preparable. CREATE EVENT is legal inside CREATE PROCEDURE.
CREATE EVENT should not be used with the embedded MySQL server library.

CREATE EVENT examples

CREATE EVENT MONITOR_PROCESSLIST
ON SCHEDULE EVERY '1' SECOND
DO INSERT INTO db1.process_counter
   SELECT CURRENT_TIMESTAMP,COUNT(*)
   FROM INFORMATION_SCHEMA.PROCESSLIST;

Don't know where to look next?X

- This event will check every second "how many jobs are running" and will record the count in a table, along with the time that the count occurred. The process_counter will fill up, with 24 * 60 * 60 = 86,400 new rows every day, possibly.

CREATE EVENT DROP_TEST_T
AT TIMESTAMP(CURRENT_DATE,'23:59:59')
DO DROP TABLE test.t;

- This event will happen once, at one minute to midnight, tonight. It will drop table t in database test.

SET GLOBAL event_scheduler = 1

The "event scheduler" is a process (a separate thread on most operating systems) which looks at each existing event and makes the decision “is it time to make this event happen?" Thus:

For (each event that has been created)
  If (event is not DISABLE)
  And (time is before ENDS time)
  And (time is after STARTS time)
  And (number-of-time-units has passed since last execution, if recurring)
  And (we're not already executing it)
  Then:
    Create a new thread
    Pass the event's sql_statement to the new thread
    (The thread will be destroyed automatically when done)

Because each event has its own thread, parallel operation is the rule. When you schedule multiple events for the same moment, they'll happen at the same moment -- more or less. If it's a second late or a second early, MySQL doesn't worry.

The statement "SET GLOBAL event_scheduler = 1" or "SET GLOBAL event_scheduler = ON" will start the event scheduler. Setting the same variable to 0 or OFF will stop the event scheduler. When the event scheduler stops, no new events will run, but currently-running events will go to completion.

As with all SET GLOBAL statements, you'll need the SUPER privilege.

Confused? Frustrated?X

You can also say mysqld ... --event-scheduler=1 when starting the server.

ALTER EVENT

ALTER EVENT
 event_name
 ON SCHEDULE schedule
 [RENAME TO new_event_name]
 [ON COMPLETION [NOT] PRESERVE]
 [ENABLE | DISABLE]
 [COMMENT 'comment']
 DO sql_statement
 ;

The wonderful thing about ALTER EVENT is that it has the same clauses as CREATE EVENT, so it's easy to learn.

You can change any attribute of an event, or multiple attributes at once. The most common use is simply
ALTER EVENT event_name DISABLE
or
ALTER EVENT event_name ENABLE

The only new clause is RENAME. There's no “RENAME EVENT” statement, so if you want to change an event's name, you have to say
ALTER EVENT event_name_1 RENAME TO event_name_2;

The new event name may be in a different database.

DROP EVENT

DROP EVENT
[IF EXISTS]
event_name
;

DROP EVENT works the same way that other "DROP object" statements work -- the event disappears. However, if an event is already running, you won't stop it by dropping it -- execution continues till the event's SQL statement has completed.

DROP USER and DROP DATABASE drop events automatically.

The EVENT privilege

GRANT EVENT ON *.* TO user;
or
GRANT EVENT ON database_name.* TO user;

REVOKE EVENT ON *.* FROM user;
or
REVOKE EVENT ON database_name.* FROM user;

Whether you're creating, altering, or dropping, you'll need the EVENT privilege. Since events are fairly powerful, there might be some shops where only one or two people can create events. But in other places, anyone will be able to make their own.

The EVENT privilege isn't a guarantee that execution will happen. Appropriate privileges are necessary at execution time too. For example, if JOE makes an event which inserts into table t, then MySQL checks -- each time it's executing the event -- that JOE has INSERT privilege for table t.

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365X

There is a new column Event_priv in mysql.user and in mysql.db.

Context

Maybe user JOE starts his day by saying

mysql --user=JOE
SET SQL_MODE='ANSI';
USE database1;

That sets some "context" information: current_user='JOE', sql_mode='ANSI', database()='database1'. If JOE now creates an event, then the user and mode are passed on to the event -- the event will operate "as if it's JOE". In effect this is like routines which execute with DEFINER privileges, although we don't support a DEFINER= clause for events yet. And the sql_mode will still be ANSI. But other context information will not be passed on. So ...

Suppose that user JOE does this:

CREATE EVENT e ON SCHEDULE EVERY 1 SECOND DO
INSERT INTO t VALUES (CURRENT_USER());

Where is table t? Joe thinks "it's surely on database database1", but that's unreliable, the event can fail. So what Joe should say is:

CREATE EVENT e ON SCHEDULE EVERY 1 SECOND DO
INSERT INTO database1.t VALUES (CURRENT_USER());

Now there's no problem. The event will insert into the right table. Incidentally, the value it will insert is 'JOE' because the event is operating as if it's JOE.

Metadata

After you have created an event, there are five ways to see it: by selecting from mysql.event, by SHOW EVENTS, by SHOW FULL EVENTS, by selecting from information_schema.events, or by SHOW CREATE EVENT. Here is an example:

mysql> CREATE EVENT
    -> Event_For_Metadata
    -> ON SCHEDULE
    ->   EVERY 1 DAY
    ->   STARTS CURRENT_TIMESTAMP
    ->   ENDS CURRENT_TIMESTAMP + INTERVAL '5' DAY
    -> ON COMPLETION NOT PRESERVE
    -> DISABLE
    -> COMMENT 'Specimen Event Only'
    -> DO
    ->   DROP TABLE test.t;
Query OK, 1 row affected (0.00 sec)




mysql> show events\G
*************************** 1. row ***************************
            Db: db1
          Name: peter's_event
       Definer: root@localhost
          Type: RECURRING
    Execute at: NULL
Interval value: 1
Interval field: SECOND
        Starts: 2006-04-06 18:09:50
          Ends: NULL

Do you need help?X

        Status: ENABLED
1 row in set (0.00 sec)

mysql> SELECT * FROM mysql.event where name = 'Event_For_Metadata'\G
*************************** 1. row ***************************
            db: db1
          name: Event_For_Metadata
          body: DROP TABLE test.t
       definer: root@localhost
    execute_at: NULL
interval_value: 1
interval_field: DAY
       created: 2006-04-06 11:17:31
      modified: 2006-04-06 11:17:31
 last_executed: NULL
        starts: 2006-04-06 17:17:31
          ends: 2006-04-11 17:17:31
        status: DISABLED
 on_completion: DROP
      sql_mode:
       comment: Specimen Event Only
1 row in set (0.04 sec)

mysql> SELECT * FROM information_schema.events where event_name = 'Event_For_Metadata'\G
*************************** 1. row ***************************
 EVENT_CATALOG: NULL
  EVENT_SCHEMA: db1
    EVENT_NAME: Event_For_Metadata
       DEFINER: root@localhost
    EVENT_BODY: DROP TABLE test.t
    EVENT_TYPE: RECURRING
    EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
      SQL_MODE:
        STARTS: 2006-04-06 17:17:31
          ENDS: 2006-04-11 17:17:31
        STATUS: DISABLED
 ON_COMPLETION: NOT PRESERVE
       CREATED: 2006-04-06 11:17:31
  LAST_ALTERED: 2006-04-06 11:17:31
 LAST_EXECUTED: NULL
 EVENT_COMMENT: Specimen Event Only
1 row in set (0.01 sec)




mysql> show create event Event_For_Metadata\G
*************************** 1. row ***************************
       Event: Event_For_Metadata
    sql_mode:
Create Event: CREATE EVENT `Event_For_Metadata` ON SCHEDULE EVERY 1 DAY ON
COMPLETION NOT PRESERVE DISABLE COMMENT 'Specimen Event Only' DO DROP TABLE test.t
1 row in set (0.00 sec)

The mysql.event and information_schema.events tables have the same information, but the privilege requirements are different. If you want to select from mysql.event, you need SELECT privilege on mysql.event. If you want to select from information_schema.events, you don't need any privilege (anybody can select from any information_schema table), but you'll see NULLs for objects that you have no privileges on.

Do you need more help?X

In fact the question of privileges for metadata isn't finally settled at time of writing, as the following quotations show:

“In MySQL 5.1.6, you may not view another user's events in the INFORMATION_SCHEMA.EVENTS table. In other words, any query made against this table is treated as though it contains the condition DEFINER = CURRENT_USER() in the WHERE clause.”
-- The MySQL Reference Manual

"The privilege needed for seeing a row in information_schema.events is: I must be the definer. This is not consistent with SHOW EVENTS (which also requires that I have the event privilege), and it's too restrictive -- what if the user gets dropped?"
-- Bug#16992

Opinion: selecting from INFORMATION_SCHEMA is a more "standard" way to look at metadata. (Events are not SQL-standard objects but the INFORMATION_SCHEMA structure is.)

How do I know if an event happened?

If an event encounters an error, or if it doesn't get scheduled for execution at the expected time, the failure is hard to see. Events don't have a 'console' of their own, and even if they did, maybe nobody's watching.

So here are some ways to check up on your events:

1. (This is only available with some non-Windows operating systems and is a temporary "debug phase" feature which we'll eventually remove.) If you started the MySQL server with 'mysqld', look to see whether mysqld has displayed anything. Here is a typical mysqld display of an event execution:

060406 12:02:59 [Note] SCHEDULER: Started thread 4162
060406 12:02:59 [Note] SCHEDULER: Executing event db1.e_count of root@localhost 

Can we help you?X

[EXPR:1]
060406 12:02:59 [Warning] SCHEDULER: [`root`@`localhost`][`db1`.`e_count`] 
Data truncated for column 's1' at row -1515870811]
060406 12:02:59 [Note] SCHEDULER: Executed event db1.e_count of root@localhost 
[EXPR:1]. RetCode=0

And here is a typical mysqld display that occurs just after one says 'SET GLOBAL event_scheduler = OFF':

Can't find what you're looking for?X
060406 12:03:01 [Note] SCHEDULER: Emptying the queue.
060406 12:03:01 [Note] SCHEDULER: Stopped.

2. (This is not working right at time of writing, but I expect great things of it.) Start the MySQL server with --log=log_file_name. Any statements by events will appear along with statements by real users.

3. SHOW PROCESSLIST. This will show that the 'event_scheduler' job is running, and when there's a thread for the event itself, that will appear too.

4. SELECT LAST_EXECUTED FROM INFORMATION_SCHEMA.EVENTS. If LAST_EXECUTED is NULL, the event was never executed. (Unfortunately this doesn't work either, at the moment, due to bug#17493.)

5. Make the event's SQL statement into a begin/end block which has an SQLEXCEPTION handler. The handler can log all failures in a table that's made for the purpose. Here's an example of what I mean:

mysql> delimiter //
mysql> create table event_failures (event_name varchar(64) character set utf8, 
	   event_timestamp timestamp);
    	-> //
Query OK, 0 rows affected (0.08 sec)

mysql> create event event_which_logs_failure on schedule every 1 second do begin 
declare exit handler for sqlexception insert into db1.event_failures values 
('event_which_logs_failure',current_timestamp); insert into no_such_table values (0); end//
Query OK, 1 row affected (0.00 sec)

6. Use an error log.

Tricky Schedules

Here are tips for tricky schedules:

  • "every Tuesday at 3pm starting next Tuesday"
  • "on the last day of each month"
  • "whenever user JOE is connected"
CREATE EVENT Every_Tuesday_At_3pm
ON SCHEDULE
EVERY 7 DAY
STARTS
 CASE WHEN DAYOFWEEK(CURRENT_DATE) > 3
      OR (DAYOFWEEK(CURRENT_DATE) = 3 AND HOUR(CURRENT_TIMESTAMP) >= 15)
 THEN
  TIMESTAMP(ADDDATE(CURRENT_DATE,(7+3)-DAYOFWEEK(CURRENT_DATE)),'15:00:00')
 ELSE
  TIMESTAMP(ADDDATE(CURRENT_DATE,(3)-DAYOFWEEK(CURRENT_DATE)),'15:00:00') END
 DO ...//



CREATE EVENT End_Of_Month
 ON SCHEDULE EVERY 1 DAY
 DO
  IF MONTH(CURRENT_DATE) <> MONTH(CURRENT_DATE+INTERVAL '1' DAY) THEN
    ...
  END IF;

CREATE EVENT if_joe_is_on
ON SCHEDULE EVERY 1 SECOND
DO
  IF (SELECT COUNT(*)
      FROM information_schema.processlist
      WHERE user='joe') > 0
  THEN ...; END IF//

A particularly tricky schedule is “EVERY n MONTH”. Suppose today is August 31, and you say “CREATE EVENT ... EVERY 1 MONTH ...”. What is August 31 plus 1 month? Answer: September 30. What is September 30 plus 1 month? Answer: October 30. But what's August 31 plus 2 months? Answer: October 31. So which did you really want, October 30 or October 31? See Bug#16397.

What happens if ...

Events aren't hard to use, but there are some unusual situations that might leave you wondering: was that supposed to happen? Here are unusual things, and what to expect.

Don't know where to look next?X

What happens if a recurring event is 'missed'?

Consider a recurring event with a schedule EVERY 1 MINUTE, which is next scheduled for execution at 13:00. But somebody says "SET GLOBAL event_scheduler = OFF" at 12:59 and doesn't turn it back on till 13:01. Or the server goes down for maintenance. Or some real user is running an amazingly piggish query so the event scheduler is squeezed out for a few minutes. In that case: the event is missed forever. The event scheduler doesn't say at 13:01 "gee I missed the 13:00 so I'll do it now". It's like missing a bus -- you take the next.

What happens if two events are scheduled simultaneously?

It's easy to say "CREATE EVENT `Conflict_1` ON SCHEDULE AT '2006-12-13 14:15:16' ..." and then say "CREATE EVENT `Conflict_2` ON SCHEDULE AT `2006-12-13 14:15:16' ...". We know that the event scheduler will try to run both events simultaneously, in parallel on different threads. But which will start first? We don't know. Don't hope that `Conflict_1` will get to the top of the queue first. There is no way to prioritize.

What happens with daylight saving time?

Generally we hope that clocks will go forward in a regular way. But in the fall the clocks fall back one hour, and in the spring they spring forward one hour. This could cause chaos if you schedule events for between 1am and 3am, so avoid those times completely. At the time I'm writing this, MySQL stores the times as UTC, Universal Co-ordinated Time, which has no switching to daylight saving. But that's causing more troubles than it solves, and it's an acknowledged bug (bug#16420).

What if the event is already running?

Suppose that event X is scheduled every 1 second, but X is slow, sometimes it takes more than one second to execute. The event scheduler will detect such cases. It will skip X and try again a second later. I have found that, even on a system with a fairly light load, even if X does little more than insert into a table, a few 'misses' of 1-second events can occur each day.

Confused? Frustrated?X

What if an event is dropped while it's running?

The nastiest (or goofiest) way to ensure this is to say "CREATE EVENT X ON SCHEDULE EVERY 1 SECOND DO DROP EVENT X". The drop won't cause the event to halt immediately, it will continue, but it won't be executed again. On the other hand, if you try to drop events by saying "delete from mysql.events", you'll succeed but the events will continue and will happen again. The event scheduler might not release them from its queue.

What if I kill the event scheduler?

With SHOW PROCESSLIST, or SELECT ... FROM INFORMATION_SCHEMA.PROCESSLIST, you can see the 'event_scheduler' job or you can see the threads of events that are actually running. If you're privileged, you can say KILL those jobs. We definitely think it's a bad idea to kill the event scheduler while it's running, but usually the effect is fairly benign. Once it is killed, the event scheduler won't restart.

That's just "what happens now". I don't mind talking about undocumented behaviour, but you should check that my tests are still true.

Other DBMSs

Events are not part of the SQL:2003 standard. When migrating from another DBMS, you will definitely have to make changes.

The Sybase Adaptive Server IQ Reference Manual has an example of how they do actions based on times:

CREATE EVENT IncrementalBackup
SCHEDULE
START TIME '1:00AM' EVERY 24 HOURS
HANDLER
    BEGIN
        BACKUP DATABASE INCREMENTAL
        TO 'backups/daily.incr'
    END

source »

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365X

The syntax of SQLBase's CREATE EVENT is not similar:

CREATE EVENT rec_stcom
 RAISE AT 12:00:00 AM EVERY 1 DAYS
 (EXECUTE RECOMPILE ( SYSADM , % , _ ,0,0,0));

source »

There are other DBMSs which create "event" objects, for example DB2 has CREATE EVENT MONITOR, Ingres has CREATE DBEVENT, and SQL Server 2005 has CREATE EVENT NOTIFICATION. But events for these DBMSs are activated by state changes, for example when an object is dropped. MySQL events, by contrast, are strictly for schedules with time-of-day and/or intervals.

Example: Replacement for UPDATE DELAYED

MySQL has a statement "INSERT DELAYED", which does an insert on a different thread. We won't need to add a statement "UPDATE DELAYED" though, because events operate on different threads. So we can say this:

CREATE EVENT Update_Delayed

Do you need help?X

ON SCHEDULE AT CURRENT_TIMESTAMP
DO UPDATE database1.table1 SET column1=value1;

This is not ideal. In fact the implementor of the Events feature doesn't recommend it, as there is plenty much overhead storing the event in the database, and getting the event scheduler to put it in the queue.

Example: Materialized Views

Imagine a big table. It takes a long time to do a grouped search on it. Many users could use the grouped-search results. They don't care if the results are obsolete by a few minutes.

That's a scenario for "materialized views", which are hard-to-make views that can be refreshed periodically or on demand. MySQL doesn't support materialized views directly. However, you can do periodic refreshing with events.

Do you need more help?X
CREATE TEMPORARY TABLE Mat_View
AS SELECT Category,COUNT(*) FROM Main_Table
GROUP BY Category;

DELIMITER //

CREATE EVENT Mat_View_Refresher
ON SCHEDULE EVERY 10 MINUTE
DO
BEGIN
  DELETE FROM Mat_View;
  INSERT INTO Mat_View
  SELECT Category,COUNT(*) FROM Main_Table
  GROUP BY Category;
END//

Unfortunately we can't use LOCK TABLE and UNLOCK TABLE within an event, so there might be short-lived periods when users of Mat_View will see “Table Not Found” error messages. But they just have to retry.

I'm not the first person who came up with this idea, I'll give credit in the "Further Reading" section at the end of this book.

Example: Overnight

Each night at midnight, let's destroy the tables in the test database. Since the 'destroyer' is potentially useful outside events, we'll make it as a stored procedure that we can call manually if we wish.

mysql> CREATE PROCEDURE destroyer ()
    -> BEGIN
    ->   DECLARE v VARCHAR(64) CHARACTER SET UTF8;
    ->   DECLARE c CURSOR FOR
    ->     SELECT table_name
    ->     FROM information_schema.tables
    ->     WHERE table_schema = 'test';
    ->   DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
    ->   OPEN c;
    ->   WHILE TRUE DO
    ->     FETCH c INTO v;
    ->     SET @v = CONCAT('DROP TABLE test.',v);
    ->     PREPARE stmt1 FROM @v;
    ->     EXECUTE stmt1;
    ->   END WHILE;
    -> END//
Query OK, 0 rows affected (0.07 sec)


mysql> CREATE EVENT event_calling_destroyer
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS TIMESTAMP(CURRENT_DATE+1,'00:00:01')
    -> DO CALL database1.destroyer()//
Query OK, 1 row affected (0.06 sec)

Future

This is a reasonably complete implementation of an "event scheduler". Eventually we'll have more than just schedules, we hope. For example, perhaps there could be triggers of events when disk space runs low, or when a user connects.

But let's not get ahead of ourselves. For the near future, we just want to make sure that events are utterly reliable by the time we release MySQL Version 5.1 "ga" (general availability). And this is where you, dear reader, will no doubt be able to help us. Please try out events. If you have trouble, you might find that there's already a bug report – check bugs.mysql.com and search in the "Server -- Event Scheduler" category. (At the time I write this – April 2006 -- there are 18 bugs, which is way down from 39 bugs in January.) If you're seeing a bug that's not on our list, well, you know the drill. Tell us! Or, what's more likely, if you find no problems -- tell your friends!

Further Reading
Contact Us  Legal Notices  Order Services Online 
Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library