| 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: | |
| 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
[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.
