Bug #16429 Events: I have to say ON SCHEDULE
Submitted: 12 Jan 2006 4:27 Modified: 22 Feb 2006 19:20
Reporter: Peter Gulutzan Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.1.6-alpha-debug OS:Linux (SUSE 10.0)
Assigned to: Andrey Hristov CPU Architecture:Any

[12 Jan 2006 4:27] Peter Gulutzan
Description:
I have to say "CREATE EVENT event_name ON SCHEDULE ...".

I want to say merely:
"CREATE EVENT event_name SCHEDULE ...".

How to repeat:
mysql> create event e_41 schedule every 1 second do set @a=5;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'schedule every 1 second do set @a=5' at line 1
[12 Jan 2006 6:33] Aleksey Kishkin
I think severity must be s4 - feature request, mustnt it?
[13 Feb 2006 0:02] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[14 Feb 2006 19:30] Brian Aker
Follow what other databases do.
[15 Feb 2006 13:22] Andrey Hristov
An event definition includes two distinct pieces. The trigger condition can be
an occurrence, such as a disk filling up beyond a defined threshold. A schedule
is a set of times, each of which acts as a trigger condition. When a trigger
condition is satisfied, the event handler executes. The event handler includes
one or more actions specified inside a compound statement (BEGIN... END).
If no trigger condition or schedule specification is supplied, only an explicit
TRIGGER EVENT statement can trigger the event. During development, you
may wish to develop and test event handlers using TRIGGER EVENT, and add
the schedule or WHERE clause once testing is complete.
Event errors are logged to the database server console.
When event handlers are triggered, the server makes context information, such
as the connection ID that caused the event to be triggered, available to the event
handler using the EVENT_PARAMETER function.
CREATE EVENT clause The event name is an identifier. An event has a
creator, which is the user creating the event, and the event handler executes
with the permissions of that creator. This is the same as stored procedure
execution. You cannot create events owned by other users.
You can list event names by querying the system table SYSEVENT. For
example:
SELECT event_id, event_name FROM SYS.SYSEVENT
TYPE clause The event-type is one of the listed set of system-defined
event types. The event types are case insensitive. To specify the conditions
under which this event-type triggers the event, use the WHERE clause.

--DiskSpace event types If the database contains an event handler for
  one of the DiskSpace types, the database server checks the available space
  on each device associated with the relevant file every 30 seconds.
  In the event the database has more than one dbspace, on separate drives,
  DBDiskSpace checks each drive and acts depending on the lowest
  available space.
  The LogDiskSpace event type checks the location of the transaction log
  and any mirrored transaction log, and reports based on the least available
  space.
  The disk space event types require Windows NT 4.0 or later and are not
  available on UNIX platforms.

--Globalautoincrement event type This event fires when the
  GLOBAL AUTOINCREMENT default value for a table is within one
  percent of the end of its range. A typical action for the handler could be to
  request a new value for the GLOBAL_DATABASE_ID option.
  You can use the EVENT_CONDITION function with RemainingValues as
  an argument for this event type.

--ServerIdle event type If the database contains an event handler for
  the ServerIdle type, the server checks for server activity every 30 seconds.

WHERE clause The trigger condition determines the condition under
which an event is fired. For example, to take an action when the disk containing
the transaction log becomes more than 80% full, use the following triggering
condition:
...
WHERE event_condition( `LogDiskSpacePercentFree` ) < 20
...
The argument to the EVENT_CONDITION function must be valid for the
event type.
You can use multiple AND conditions to make up the WHERE clause, but you
cannot use OR conditions or other conditions.
For information on valid arguments, see \u201cEVENT_CONDITION function
[System]\u201d on page 305.
SCHEDULE clause This clause specifies when scheduled actions are to
take place. The sequence of times acts as a set of triggering conditions for the
associated actions defined in the event handler.
You can create more than one schedule for a given event and its associated
handler. This permits complex schedules to be implemented. While it is
compulsory to provide a schedule-name when there is more than one schedule,
it is optional if you provide only a single schedule.
You can list schedule names by querying the system table SYSSCHEDULE.
For example:
SELECT event_id, sched_name FROM SYS.SYSSCHEDULE
Each event has a unique event id. Use the event_id columns of SYSEVENT
and SYSSCHEDULE to match the event to the associated schedule.

When a non-recurring scheduled event has passed, its schedule is deleted, but
the event handler is not deleted.
Scheduled event times are calculated when the schedules are created, and again
when the event handler completes execution. The next event time is computed
by inspecting the schedule or schedules for the event, and finding the next
schedule time that is in the future. If an event handler is instructed to run every
hour between 9:00 and 5:00, and it takes 65 minutes to execute, it runs at 9:00,
11:00, 1:00, 3:00, and 5:00. If you want execution to overlap, you must create
more than one event.
The subclauses of a schedule definition are as follows:
-- START TIME The first scheduled time for each day on which the event
   is scheduled. If a START DATE is specified, the START TIME refers to
   that date. If no START DATE is specified, the START TIME is on the
   current day (unless the time has passed) and each subsequent day.
-- BETWEEN ... AND A range of times during the day outside of which
   no scheduled times occur. If a START DATE is specified, the scheduled
   times do not occur until that date.
-- EVERY An interval between successive scheduled events. Scheduled
   events occur only after the START TIME for the day, or in the range
   specified by BETWEEN ... AND.

-- ON A list of days on which the scheduled events occur. The default is
   every day. These can be specified as days of the week or days of the month.
   Days of the week are Monday, Tuesday, and so on. The abbreviated forms
   of the day, such as Mon, Tue, and so on, may also be used.
   Days of the month are integers from 0 to 31. A value of 0 represents the
   last day of any month.

-- START DATE The date on which scheduled events are to start
   occurring. The default is the current date.

Each time a scheduled event handler is completed, the next scheduled time and
date is calculated.
1 If the EVERY clause is used, find whether the next scheduled time falls on
the current day, and is before the end of the BETWEEN ... AND range. If
so, that is the next scheduled time.
2 If the next scheduled time does not fall on the current day, find the next
date on which the event is to be executed.

3 Find the START TIME for that date, or the beginning of the BETWEEN
... AND range.

ENABLE | DISABLE : By default, event handlers are enabled. When
  DISABLE is specified, the event handler does not execute even when the
  scheduled time or triggering condition occurs. A TRIGGER EVENT statement
  does not cause a disabled event handler to be executed.

AT clause: If you wish to execute events at remote or consolidated
  databases in a SQL Remote setup, you can use this clause to restrict the
  databases at which the event is handled. By default, all databases execute the
  event.

HANDLER clause: Each event has one handler. Like the body of a stored
  procedure, the handler is a compound statement. There are some differences,
  though: you can use an EXCEPTION clause within the compound statement to
  handle errors, but not the ON EXCEPTION RESUME clause provided within
  stored procedures.