| List: | Commits | « Previous MessageNext Message » | |
| From: | jon | Date: | February 13 2006 8:14pm |
| Subject: | svn commit - mysqldoc@docsrva: r1296 - in trunk: refman-5.1 refman-common | ||
| View as plain text | |||
Author: jstephens Date: 2006-02-13 20:14:48 +0100 (Mon, 13 Feb 2006) New Revision: 1296 Log: trunk/refman-5.1/events.xml - Event Scheduler chapter (added) trunk/refman-common/titles.en.ent - Added new chapter/section titles trunk/refman-5.1/information-schema.xml - Updated I_S.EVENTS table description trunk/refman-5.1/manual.xml - XInclude: new chapter file trunk/refman-5.1/Makefile - ran make depend Added: trunk/refman-5.1/events.xml Modified: trunk/refman-5.1/Makefile trunk/refman-5.1/information-schema.xml trunk/refman-5.1/manual.xml trunk/refman-common/titles.en.ent Modified: trunk/refman-5.1/Makefile =================================================================== --- trunk/refman-5.1/Makefile 2006-02-13 16:46:05 UTC (rev 1295) +++ trunk/refman-5.1/Makefile 2006-02-13 19:14:48 UTC (rev 1296) @@ -44,7 +44,7 @@ MANUAL_SRCS_EXTRA = versions.ent ../refman-common/fixedchars.ent ../refman-common/titles.en.ent -MANUAL_SRCS = $(MANUAL_SRCS_EXTRA) manual.xml preface.xml introduction.xml ../refman-common/manual-conventions.en.xml ../refman-common/what-is-mysql-ab.en.xml ../refman-common/what-is.en.xml ../refman-common/maxdb.en.xml ../refman-common/information-sources.xml ../refman-common/bug-reports.xml installing.xml tutorial.xml using-mysql-programs.xml database-administration.xml replication.xml optimization.xml client-utility-programs.xml language-structure.xml reservedwords.xml ../refman-common/reserved-new-5.1.xml charset.xml data-types.xml functions.xml sql-syntax.xml storage-engines.xml innodb.xml images/blackhole-1.png custom-engine.xml images/custom-engine-overview.png ndbcluster.xml images/cluster-components-1.png images/replicas-groups-1-1.png images/replicas-groups-1-2.png images/multi-comp-1.png partitioning.xml spatial-extensions.xml stored-procedures.xml triggers.xml views.xml information-schema.xml precision-math.xml apis.xml connectors.xml connector-odbc.xml images/! myarchitecture.png images/mydsn-icon.png images/mydsn.png images/mydsn-setup.png images/mydsn-example.png images/mydsn-test-success.png images/mydsn-test-fail.png images/mydsn-options.png images/mydsn-icon.png images/mydsn.png images/mydsn-trace.png images/myaccess.png images/myaccess-odbc.png images/mydsn-trace.png images/mydll-properties.png images/mydsn-options.png images/myflowchart.png connector-net.xml ../refman-common/news-connector-net.xml connector-j.xml ../refman-common/news-connector-j.xml connector-mxj.xml extending-mysql.xml problems.xml error-handling.xml errmsgs-server.xml errmsgs-client.xml ../refman-common/credits.xml news.xml ../refman-common/news-5.1.xml ../refman-common/news-myodbc.xml porting.xml ../refman-common/environment-variables.xml ../refman-common/regexp.xml limits.xml restrictions.xml ../refman-common/gpl-license.xml ../refman-common/mysql-floss-license-exception.xml +MANUAL_SRCS = $(MANUAL_SRCS_EXTRA) manual.xml preface.xml introduction.xml ../refman-common/manual-conventions.en.xml ../refman-common/what-is-mysql-ab.en.xml ../refman-common/what-is.en.xml ../refman-common/maxdb.en.xml ../refman-common/information-sources.xml ../refman-common/bug-reports.xml installing.xml tutorial.xml using-mysql-programs.xml database-administration.xml replication.xml optimization.xml client-utility-programs.xml language-structure.xml reservedwords.xml ../refman-common/reserved-new-5.1.xml charset.xml data-types.xml functions.xml sql-syntax.xml storage-engines.xml innodb.xml images/blackhole-1.png custom-engine.xml images/custom-engine-overview.png ndbcluster.xml images/cluster-components-1.png images/replicas-groups-1-1.png images/replicas-groups-1-2.png images/multi-comp-1.png images/cluster-replication-overview.png images/cluster-replication-binlog-injector.png partitioning.xml spatial-extensions.xml stored-procedures.xml triggers.xml events.xml view! s.xml information-schema.xml precision-math.xml apis.xml connectors.xml connector-odbc.xml images/myarchitecture.png images/mydsn-icon.png images/mydsn.png images/mydsn-setup.png images/mydsn-example.png images/mydsn-test-success.png images/mydsn-test-fail.png images/mydsn-options.png images/mydsn-icon.png images/mydsn.png images/mydsn-trace.png images/myaccess.png images/myaccess-odbc.png images/mydsn-trace.png images/mydll-properties.png images/mydsn-options.png images/myflowchart.png connector-net.xml ../refman-common/news-connector-net.xml connector-j.xml ../refman-common/news-connector-j.xml connector-mxj.xml extending-mysql.xml problems.xml error-handling.xml errmsgs-server.xml errmsgs-client.xml ../refman-common/credits.xml news.xml ../refman-common/news-5.1.xml ../refman-common/news-myodbc.xml porting.xml ../refman-common/environment-variables.xml ../refman-common/regexp.xml limits.xml restrictions.xml ../refman-common/gpl-license.xml ../refman-common/mysql-floss-li! cense-exception.xml manual-prepped.xml: $(MANUAL_SRCS) manual-manprepped.xml: $(MANUAL_SRCS) Added: trunk/refman-5.1/events.xml =================================================================== --- trunk/refman-5.1/events.xml 2006-02-13 16:46:05 UTC (rev 1295) +++ trunk/refman-5.1/events.xml 2006-02-13 19:14:48 UTC (rev 1296) @@ -0,0 +1,1567 @@ +<?xml version="1.0" encoding="UTF-8"?> +<!DOCTYPE chapter PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN" +"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd" +[ + <!ENTITY % fixedchars.entities SYSTEM "../refman-common/fixedchars.ent"> + %fixedchars.entities; + <!ENTITY % title.entities SYSTEM "../refman-common/titles.en.ent"> + %title.entities; + <!ENTITY % versions.entities SYSTEM "versions.ent"> + %versions.entities; +]> +<chapter id="events"> + + <title>&title-events;</title> + + <remark role="note"> + Author: Jon Stephens. Based on WL#1034 and materials supplied by + Trudy Pelzer, Andrey Hristov, and Sergei Golubchik. Also + incorporates valuable feedback from Stefan Hinz and Paul DuBois. + </remark> + + <remark role="todo"> + A large portion of this material could easily migrate to other + chapters of the Manual. What should stay here? + </remark> + + <para> + This chapter describes the <firstterm>MySQL Event + Scheduler</firstterm>, for which support was added in MySQL 5.1.6., + and is divided into the following sections: + </para> + + <itemizedlist> + + <listitem> + <para> + <xref linkend="events-overview"/> provides an introduction to + and conceptual overview of MySQL Events. + </para> + </listitem> + + <listitem> + <para> + <xref linkend="events-syntax"/> discusses the SQL commands + introduced in MySQL 5.1.6 for creating, altering, and dropping + MySQL Events. + </para> + </listitem> + + <listitem> + <para> + <xref linkend="events-metadata"/> shows how to obtain + information about events and how this information is stored by + the MySQL Server. + </para> + </listitem> + + <listitem> + <para> + <xref linkend="events-privileges"/> discusses the privileges + required to work with events and the ramifications that events + have with regard to privileges when executing. + </para> + </listitem> + + <listitem> + <para> + <xref linkend="events-limitations-restrictions"/> describes the + restrictions and limitations of MySQL's Event Scheduler + implementation. + </para> + </listitem> + + </itemizedlist> + + <para> + <emphasis role="bold">Additional Resources</emphasis>: + </para> + + <itemizedlist> + + <listitem> + <para> + You may find the + <ulink url="http://forums.mysql.com/list.php?119">MySQL Event + Scheduler User Forum</ulink> of use when working with events. + Here you can discuss the MySQL Event Scheduler with other MySQL + users and the MySQL developers. + </para> + </listitem> + + </itemizedlist> + + <section id="events-overview"> + + <title>&title-events-overview;</title> + + <para> + MySQL Events are tasks that run according to a schedule. + Therefore, we sometimes refer to them as + <emphasis>scheduled</emphasis> events. When you create an event, + you are creating a named routine consisting of one or more SQL + statements, which is to be executed at one or more regular + intervals, beginning and ending at a specific date and time. + Conceptually, this is similar to the idea of the Unix + <literal>crontab</literal> (also known as a <quote>cron + job</quote>) or the Windows Task Scheduler. + </para> + + <para> + Scheduled tasks of this type are also sometimes known as + <quote>temporal triggers</quote>, implying that these are routines + that are triggered by the passage of time. While this is + essentially correct, we prefer to use the term + <emphasis>events</emphasis> in order to avoid confusion with + triggers of the type discussed in <xref linkend="triggers"/>. + Events should more specifically not be confused with + <quote>temporary triggers</quote>. Whereas a trigger is a routine + that is executed in response to a specific type of event that + occurs on a given table, a (scheduled) event is a routine that is + executed in response to the passage of a specified time interval. + </para> + + <para> + While there is no provision in the SQL Standard for event + scheduling, there are precedents in other database systems, and + you may notice some similarities between these implementations and + that found in the MySQL Server. + </para> + + <para> + MySQL Events have the following major features and properties: + </para> + + <itemizedlist> + + <listitem> + <para> + An event is uniquely identified by: its name; the schema to + which it is assigned; and the user who created it (definer). + </para> + </listitem> + + <listitem> + <para> + An event performs a specific action according to a schedule. + This action consists of an SQL statement, which can be a + compound statement in a <literal>BEGIN ... END</literal> block + if desired (see <xref linkend="begin-end"/>). An event's + timing can be either <firstterm>transient</firstterm> or + <firstterm>recurrent</firstterm>. A transient event executes + one time only. A recurrent event repeats its action at a + regular interval, and the schedule for a recurring event can + be assigned a specific start day and time, end day and time, + both, or neither. (By default, a recurring event's schedule + begins as soon as it is created, and continues indefinitely, + until it is disabled or dropped.) + </para> + </listitem> + + <listitem> + <para> + Users can create, modify, and drop scheduled events using SQL + statements intended for these purposes. Syntactically invalid + event creation and modification statements fail with an + appropriate error message. Note that a user may include + statements in an event's action which require privileges that + the user does not actually have. The event creation or + modification statement succeeds but the event's action fails. + See <xref linkend="events-privileges"/> for details. + </para> + </listitem> + + <listitem> + <para> + Many of the properties of an event can be set or modified + using SQL statements. These properties include the event's + name, timing, persistence (that is, whether it is preserved + following the expiration of its schedule), status (enabled or + disabled), action to be performed, and the schema to which it + is assigned. See <xref linkend="events-alter"/>. + </para> + + <para> + The definer of an event cannot be changed; it is always the + user who created the event. An event can be modified only by + the event's definer, or by a user having privileges on the + <literal>mysql.event</literal> table (see + <xref linkend="events-privileges"/>.) + </para> + </listitem> + + <listitem> + <para> + An event's action statement may include most SQL statements + permitted within stored routines. + </para> + </listitem> + + </itemizedlist> + + <para> + MySQL 5.1.6 introduces a global variable + <literal>event_scheduler</literal> which determines whether the + Event scheduler is enabled for the server. This variable defaults + to <literal>OFF</literal> or <literal>0</literal>, meaning that + event scheduling is not available: + </para> + +<programlisting> +mysql> <userinput>SHOW GLOBAL VARIABLES LIKE 'event%'</userinput>; ++-----------------+-------+ +| Variable_name | Value | ++-----------------+-------+ +| event_scheduler | OFF | ++-----------------+-------+ +1 row in set (0.01 sec) + +mysql> <userinput>SELECT @@event_scheduler;</userinput> ++-------------------+ +| @@event_scheduler | ++-------------------+ +| 0 | ++-------------------+ +1 row in set (0.00 sec) +</programlisting> + + <para> + In order to enable event scheduling, you must first issue one of + these statements: + </para> + +<programlisting> +SET GLOBAL event_scheduler = ON; +</programlisting> + + <para> + or + </para> + +<programlisting> +SET GLOBAL event_scheduler = 1; +</programlisting> + + <para> + or + </para> + +<programlisting> +SET @@global.event_scheduler = ON; +</programlisting> + + <para> + or + </para> + +<programlisting> +SET @@global.event_scheduler = 1; +</programlisting> + + <para> + <emphasis role="bold">Note</emphasis>: You can issue + event-manipulation statements when + <literal>event_scheduler</literal> is set to + <literal>OFF</literal> or <literal>0</literal>. No warnings or + errors are generated in such cases (so long as the statements are + themselves valid). However, scheduled events cannot execute until + this variable is set to <literal>ON</literal> or + <literal>1</literal>. (Once this has been done, all events whose + scheduling conditions are met become active.) + </para> + + <para> + <emphasis role="bold">Note</emphasis>: Since + <literal>event_scheduler</literal> is a global variable, you must + have the <literal>SUPER</literal> privilege to set its value. + </para> + + <para> + You can also enable event scheduling by starting + <command>mysqld</command> with + <option>--event_scheduler=1</option> or more simply + <option>--event_scheduler</option>. (<literal>1</literal> is the + default value in this case.) + </para> + + <para> + For SQL statements used to create, alter, and drop events, see + <xref linkend="events-syntax"/>. + </para> + + <para> + MySQL 5.1.6 and later provides an <literal>EVENTS</literal> table + in the <literal>INFORMATION_SCHEMA</literal> database. This table + can be queried to find out about the events which exist on the + server. See <xref linkend="events-metadata"/>. + </para> + + <para> + For information regarding event scheduling and the MySQL privilege + system, see <xref linkend="events-privileges"/>. + </para> + + </section> + + <section id="events-syntax"> + + <title>&title-events-syntax;</title> + + <para> + MySQL 5.1.6 and later provides several SQL statements for working + with scheduled events: + </para> + + <itemizedlist> + + <listitem> + <para> + New events are defined using the <literal>CREATE + EVENT</literal> statement. See + <xref linkend="events-create"/>. + </para> + </listitem> + + <listitem> + <para> + The definition of an existing event can be changed by means of + the <literal>ALTER EVENT</literal> statement. See + <xref linkend="events-alter"/>. + </para> + </listitem> + + <listitem> + <para> + When a scheduled event is no longer wanted or needed, it can + be deleted from the server by its definer using the + <literal>DROP EVENT</literal> statement. See + <xref linkend="events-drop"/>. (Note that whether an event + persists past the end of its schedule also depends on its + <literal>ON COMPLETION</literal> clause, if it has one. See + <xref linkend="events-create"/>.) + </para> + + <para> + An event can be deleted by a user other than its definer, but + in this case, the user performing the the deletion must have + the necessary privileges on the <literal>mysql.event</literal> + table. See <xref linkend="events-privileges"/>. + </para> + </listitem> + + </itemizedlist> + + <section id="events-create"> + + <title>&title-events-create;</title> + +<programlisting> +CREATE EVENT [IF NOT EXISTS] <replaceable>event_name</replaceable> + ON SCHEDULE <replaceable>schedule</replaceable> + [ON COMPLETION [NOT] PRESERVE] + [ENABLED | DISABLE] + [COMMENT '<replaceable>comment</replaceable>'] + DO <replaceable>sql_statement</replaceable>; + +<replaceable>schedule</replaceable>: + AT <replaceable>timestamp</replaceable> [+ INTERVAL <replaceable>interval</replaceable>] + | EVERY <replaceable>interval</replaceable> [STARTS <replaceable>timestamp</replaceable>] [ENDS <replaceable>timestamp</replaceable>] + +<replaceable>interval</replaceable>: + <replaceable>quantity</replaceable> {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | + WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | + DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} +</programlisting> + + <para> + This statement creates and schedules a new event. The minimum + requirements for a valid <literal>CREATE EVENT</literal> + statement are as follows: + </para> + + <itemizedlist> + + <listitem> + <para> + The keywords <literal>CREATE EVENT</literal> plus an event + name, which uniquely identifies the event from among those + created by the current user in the current schema. + </para> + </listitem> + + <listitem> + <para> + An <literal>ON SCHEDULE</literal> clause, which determines + when and how often the event executes. + </para> + </listitem> + + <listitem> + <para> + A <literal>DO</literal> clause, which contains the SQL + statement to be executed by an event. + </para> + </listitem> + + </itemizedlist> + + <para> + This is an example of a minimal <literal>CREATE EVENT</literal> + statement: + </para> + +<programlisting> +CREATE EVENT myevent + ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR + DO + UPDATE myschema.mytable SET mycol = mycol + 1; +</programlisting> + + <para> + The previous statement creates an event named + <literal>myevent</literal>. This event executes once — one + hour following its creation — by running an SQL statement + that increments the value of the + <literal>myschema.mytable</literal> table's + <literal>mycol</literal> column by 1. + </para> + + <para> + The <replaceable>event_name</replaceable> 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 <xref linkend="legal-names"/>. + </para> + + <para> + If no schema is indicated as part of + <replaceable>event_name</replaceable>, then the default + (current) schema is assumed. The definer is always the current + MySQL user. <emphasis>Note that is it possible for two different + users to create different events having the same name on the + same database schema</emphasis>. + </para> + + <para> + <emphasis role="bold">Note</emphasis>: MySQL uses + case-insensitive comparisons when checking for the uniqueness of + event names. This means that, for example, you cannot have two + events named <literal>myevent</literal> and + <literal>MyEvent</literal> created by the same MySQL user in the + same database schema. + </para> + + <para> + <literal>IF NOT EXISTS</literal> functions in the much the same + fashion with <literal>CREATE EVENT</literal> as it does when + used with a <literal>CREATE TABLE</literal> statement; if an + event named <replaceable>event_name</replaceable> already exists + in the same schema, no action is taken, and no error results. + (However, a warning is generated.) + </para> + + <para> + The <literal>ON SCHEDULE</literal> clause determines when, how + often, and for how long the + <replaceable>sql_statement</replaceable> defined for the event + executes. This clause takes one of two forms: + </para> + + <itemizedlist> + + <listitem> + <para> + <literal>AT <replaceable>timestamp</replaceable></literal> + is used for a transient event. It specifies that the event + executes one time only at the date and time, given as the + <replaceable>timestamp</replaceable>, 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 <literal>DATETIME</literal> or + <literal>TIMESTAMP</literal> type for this purpose. The + <replaceable>timestamp</replaceable> 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: + </para> + +<programlisting> +mysql> <userinput>SELECT NOW();</userinput> ++---------------------+ +| NOW() | ++---------------------+ +| 2006-02-10 23:59:01 | ++---------------------+ +1 row in set (0.04 sec) + +mysql> <userinput>CREATE EVENT e_totals</userinput> + -> <userinput>ON SCHEDULE AT '2006-02-10 23:59:00'</userinput> + -> <userinput>DO INSERT INTO test.totals VALUES (NOW());</userinput> +<errortext>ERROR 1522 (HY000): Activation (AT) time is in the past</errortext> +</programlisting> + + <para> + Note that <literal>CREATE EVENT</literal> statements which + are invalid — for whatever reason — fail with an + error. + </para> + + <para> + You may use <literal>CURRENT_TIMESTAMP</literal> to specify + the current date and time. In such a case, the event acts as + soon as it is created. + </para> + + <para> + 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 <quote>three weeks from + now</quote> — you can use the optional clause + <literal>+ INTERVAL + <replaceable>interval</replaceable></literal>. The + <replaceable>interval</replaceable> portion consists of two + parts, a quantity and a unit of time, and follows the same + syntax rules that govern intervals used in the + <literal>DATE_ADD()</literal> function (see + <xref linkend="date-and-time-functions"/>. Note that the + units keywords are also the same, except that you cannot use + any units involving microseconds when defining an event. + </para> + + <remark role="note"> + The following does not produce a syntax error, so if it is + wrong, then it should do so. + </remark> + + <para> + You can also combine intervals. For example, <literal>AT + CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 + DAY</literal> is equivalent to <quote>three weeks and two + days from now</quote>. Each portion of such a clause must + begin with <literal>+ INTERVAL</literal>. + </para> + </listitem> + + <listitem> + <para> + For actions which are to be repeated at a regular interval, + you can use an <literal>EVERY</literal> clause. The EVERY + keyword is followed by an + <replaceable>interval</replaceable> as described in the + previous dicussion of the <literal>AT</literal> keyword. + (Note that <literal>+ INTERVAL</literal> is + <emphasis>not</emphasis> used with + <literal>EVERY</literal>.) For example, <literal>EVERY 6 + WEEK</literal> means <quote>every six weeks</quote>. + </para> + + <para> + It is not possible to combine <literal>+ INTERVAL</literal> + clauses in a single <literal>EVERY</literal> clause; + however, you can use the same complex time units allowed in + a <literal>+ INTERVAL</literal>. For example, <quote>every + two minutes and ten seconds</quote> can be expressed as + <literal>EVERY '2:10' MINUTE_SECOND</literal>. + </para> + + <para> + An <literal>EVERY</literal> clause may also contain an + optional <literal>STARTS</literal> clause. + <literal>STARTS</literal> is followed by a + <replaceable>timestamp</replaceable> value which indicates + when the action should begin repeating, and may also use + <literal>+ INTERVAL + <replaceable>interval</replaceable></literal> in order to + specify an amount of time <quote>from now</quote>. For + example, <literal>EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 + WEEK</literal> means <quote>every three months, beginning + one week from now</quote>. Similarly, you can express + <quote>every two weeks, beginning six hours and fifteen + minutes from now</quote> as <literal>EVERY 2 WEEK STARTS + CURRENT_TIMESTAMP + '6:15' HOUR_MINUTE</literal>. Not + specifying <literal>STARTS</literal> is the same as using + <literal>STARTS CURRENT_TIMESTAMP</literal> — that is, + the action specified for the event begins repeating + immediately upon creation of the event. + </para> + + <para> + An <literal>EVERY</literal> clause may also contain an + optional <literal>ENDS</literal> clause. The + <literal>ENDS</literal> keyword is followed by a + <replaceable>timestamp</replaceable> value which tells MySQL + when the event should stop repeating. You may also use + <literal>+ INTERVAL + <replaceable>interval</replaceable></literal> with + <literal>ENDS</literal>; for instance, <literal>EVERY 12 + HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS + CURRENT_TIMESTAMP + INTERVAL 4 WEEK</literal> is equivalent + to <quote>every twelve hours, beginning thirty minutes from + now, and ending four weeks from now</quote>. Not using + <literal>ENDS</literal> means that the event continues + executing indefinitely. + </para> + + <para> + <literal>ENDS</literal> supports the same syntax for complex + time units as <literal>STARTS</literal> does. + </para> + + <para> + Note that you may use <literal>STARTS</literal>, + <literal>ENDS</literal>, both, or neither in an + <literal>EVERY</literal> clause. + </para> + </listitem> + + </itemizedlist> + + <para> + Normally, once an event has expired, it is immediately dropped. + You can override this behaviour by specifying <literal>ON + COMPLETION PRESERVE</literal>. Using <literal>ON COMPLETION NOT + PRESERVE</literal> merely makes the default non-persistent + behaviour explicit. + </para> + + <para> + You can create an event but keep it from being active using the + <literal>DISABLE</literal> keyword. Alternatively, you may use + <literal>ENABLED</literal> to make explicit the default status, + which is active. This is most useful in conjunction with + <literal>ALTER EVENT</literal> (see + <xref linkend="events-alter"/>). + </para> + + <para> + You may supply a comment for an event using a + <literal>COMMENT</literal> clause. + <replaceable>comment</replaceable> 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. + </para> + + <para> + The <literal>DO</literal> 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 + <xref linkend="routine-restrictions"/>.) For example, the + following event <literal>e_hourly</literal> deletes all rows + from the <literal>sessions</literal> table once per hour, where + this table is part of the <literal>site_activity</literal> + schema: + </para> + +<programlisting> +CREATE EVENT e_hourly + EVERY 1 HOUR + COMMENT 'Clears out sessions table each hour.' + DO + DELETE FROM site_activity.sessions; +</programlisting> + + <para> + <emphasis role="bold">Note</emphasis>: The + <literal>SHOW</literal> statement and <literal>SELECT</literal> + 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 <literal>SELECT INTO</literal> and + <literal>INSERT ... SELECT</literal> that store a result. (See + the next example in this section for an instance of the latter.) + </para> + + <para> + <emphasis role="bold">Note</emphasis>: Any reference to a table + in the <literal>DO</literal> clause must be qualified with the + name of the schema in which the table occurs. + </para> + + <para> + As with stored routines, you can use multiple statements in the + <literal>DO</literal> clause by bracketing them with the + <literal>BEGIN</literal> and <literal>END</literal> keywords, as + shown here: + </para> + +<programlisting> +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 ; +</programlisting> + + <para> + Note the use of the <literal>DELIMITER</literal> statement to + change the statement delimiter, as with stored routines. See + <xref linkend="create-procedure"/>. + </para> + + <para> + 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: + </para> + +<programlisting> +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 ; +</programlisting> + + <para> + There is no way to pass parameters directly to or from events; + however, it is possible to invoke a stored routine with + parameters: + </para> + +<programlisting> +CREATE EVENT e_call_myproc + ON SCHEDULE AT CURRENT_TIMESTAMP + 1 DAY + DO CALL myproc(5, 27); +</programlisting> + + <para> + In addition, if the event's definer has the + <literal>SUPER</literal> 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. + </para> + + <para> + 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 <xref linkend="stored-procedure-syntax"/>. Note that you can + create an event as part of a stored routine, but an event cannot + be created by another event. + </para> + + </section> + + <section id="events-alter"> + + <title>&title-events-alter;</title> + +<programlisting> +ALTER EVENT <replaceable>event_name</replaceable> + [ON SCHEDULE <replaceable>schedule</replaceable>] + [RENAME TO <replaceable>new_event_name</replaceable>] + [ON COMPLETION [NOT] PRESERVE] + [COMMENT '<replaceable>comment</replaceable>'] + [ENABLED | DISABLE] + [DO <replaceable>sql_statement</replaceable>] +</programlisting> + + <para> + The <literal>ALTER EVENT</literal> statement is used to change + one or more of the characteristics of an existing event without + the need to drop and recreate it. The syntax for each of the + <literal>ON SCHEDULE</literal>, <literal>ON + COMPLETION</literal>, <literal>COMMENT</literal>, + <literal>ENABLE</literal> / <literal>DISABLE</literal>, and + <literal>DO</literal> clauses is exactly the same as when used + with CREATE EVENT. (See <xref linkend="events-create"/>.) + </para> + + <para> + <literal>ALTER EVENT</literal> works only with an existing + event: + </para> + +<programlisting> +mysql> <userinput>ALTER EVENT no_such_event </userinput> + > <userinput>ON SCHEDULE EVERY '2:3' DAY_HOUR;</userinput> +<errortext>ERROR 1517 (HY000): Unknown event 'no_such_event'</errortext> +</programlisting> + + <para> + In each of the following examples, assume that the event named + <literal>myevent</literal> is defined as shown here: + </para> + +<programlisting> +CREATE EVENT myevent + ON SCHEDULE EVERY 6 HOUR + COMMENT 'A sample comment.' + DO + UPDATE myschema.mytable SET mycol = mycol + 1; +</programlisting> + + <para> + The following statement changes the schedule for + <literal>myevent</literal> from once every six hours starting + immediately to once every twelve hours, starting four hours from + the time the statement is run: + </para> + +<programlisting> +ALTER EVENT myevent + ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + 4 HOUR; +</programlisting> + + <para> + To disable <literal>myevent</literal>, use this <literal>ALTER + EVENT</literal> statement: + </para> + +<programlisting> +ALTER EVENT myevent + DISABLE; +</programlisting> + + <para> + It is possible to change multiple characteristics of an event in + a single statement. This example changes the SQL statement + executed by <literal>myevent</literal> to one that deletes all + records from <literal>mytable</literal>; it also changes the + schedule for the event such that it executes once, one day after + this <literal>ALTER EVENT</literal> statement is run. + </para> + +<programlisting> +ALTER TABLE myevent + ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY + DO + TRUNCATE TABLE myschema.mytable; +</programlisting> + + <para> + To rename an event, use the <literal>ALTER EVENT</literal> + statement's <literal>RENAME TO</literal> clause, as shown here: + </para> + +<programlisting> +ALTER EVENT myevent + RENAME TO yourevent; +</programlisting> + + <para> + The previous statement renames the event + <literal>myevent</literal> to <literal>yourevent</literal>. + (Note that there is no <literal>RENAME EVENT</literal> + statement.) + </para> + + <para> + You can also move an event to a different schema using + <literal>ALTER EVENT ... RENAME TO ...</literal> and + <literal><replaceable>schema_name.table_name</replaceable></literal> + notation, as shown here: + </para> + +<programlisting> +ALTER EVENT oldschema.myevent + RENAME TO newschema.myevent; +</programlisting> + + <para> + It is necessary to include only those options in an + <literal>ALTER EVENT</literal> statement which correspond to + characteristics that you actually wish to change; options which + are omitted retain their existing values. This includes any + default values for <literal>CREATE EVENT</literal> such as + <literal>ENABLED</literal>. + </para> + + </section> + + <section id="events-drop"> + + <title>&title-events-drop;</title> + +<programlisting> +DROP EVENT [IF EXISTS] <replaceable>event_name</replaceable> +</programlisting> + + <para> + This statement drops the event named + <replaceable>event_name</replaceable>. The event immediately + ceases being active, and is deleted completely from the server. + </para> + + <para> + If the event does not exist, the error <errortext>ERROR 1517 + (HY000): Unknown event + '<replaceable>event_name</replaceable>'</errortext> results. You + can override this and cause the statement to fail silently by + using <literal>IF EXISTS</literal>. + </para> + + </section> + + </section> + + <section id="events-metadata"> + + <title>&title-events-metadata;</title> + + <section id="events-information-schema"> + + <title>&title-events-information-schema;</title> + + <para> + Information about events is stored in the + <literal>EVENTS</literal> table of the + <literal>INFORMATION_SCHEMA</literal> database, which is + discussed in <xref linkend="events-table"/>. + </para> + + </section> + + <section id="events-show"> + + <title>&title-events-show;</title> + + <remark role="todo"> + [js] Move to SHOW Syntax section eventually... + </remark> + +<programlisting> +SHOW [FULL] EVENTS [FROM <replaceable>schema_name</replaceable>] [LIKE <replaceable>pattern</replaceable>] +</programlisting> + + <para> + In its simplest form, <literal>SHOW EVENTS</literal> lists all + of the events in the current schema for which the current user + is the definer: + </para> + +<programlisting> +mysql> <userinput>SELECT CURRENT_USER(), SCHEMA();</userinput> ++----------------+----------+ +| CURRENT_USER() | SCHEMA() | ++----------------+----------+ +| jon@ghidora | myschema | ++----------------+----------+ +1 row in set (0.00 sec) + +mysql> <userinput>SHOW EVENTS\G</userinput> +*************************** 1. row *************************** + Db: myschema + Name: e_daily + Definer: jon@ghidora + Type: RECURRING + Execute at: NULL +Interval value: 10 +Interval field: INTERVAL_SECOND + Starts: 0000-00-00 00:00:00 + Ends: 0000-00-00 00:00:00 + Status: ENABLED +1 row in set (0.01 sec) +</programlisting> + + <para> + The columns in the output of <literal>SHOW EVENTS</literal> + — which are similar to, but not identical to the columns + in the <literal>INFORMATION_SCHEMA.EVENTS</literal> table + — are shown here: + </para> + + <itemizedlist> + + <listitem> + <para> + <literal>Db</literal>: The schema (database) on which the + event is defined. + </para> + </listitem> + + <listitem> + <para> + <literal>Name</literal>: The name of the event. + </para> + </listitem> + + <listitem> + <para> + <literal>Definer</literal>: The user account + (<literal><replaceable>username</replaceable>@<replaceable>hostname</replaceable></literal>) + which created the event. + </para> + </listitem> + + <listitem> + <para> + <literal>Type</literal>: One of the two values <literal>ONE + TIME</literal> (transient) or <literal>RECURRING</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>Execute At</literal>: The date and time when a + transient event is set to execute. Shown as a + <literal>DATETIME</literal> value. + </para> + + <para> + For a recurring event, the value of this column is always + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>Interval Value</literal>: For a recurring event, + the number of intervals to wait between event executions. + </para> + + <para> + For a transient event, the value of this column is always + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>Interval Field</literal>: The time units used for + the interval which a recurring event waits before repeating. + </para> + + <para> + For a transient event, the value of this column is always + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>Starts</literal>: The start date and time for a + recurring event. This is displayed as a + <literal>DATETIME</literal> value, and defaults to + <literal>'0000-00-00 00:00:00'</literal> if no start date + and time is defined for the event. + </para> + + <para> + For a transient event, the value of this column is always + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>Ends</literal>: The end date and time for a + recurring event. This is displayed as a + <literal>DATETIME</literal> value, and defaults to + <literal>'0000-00-00 00:00:00'</literal> if no end date and + time is defined for the event. + </para> + + <para> + For a transient event, the value of this column is always + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>Status</literal>: The event status. One of + <literal>ENABLED</literal> or <literal>DISABLE</literal>. + </para> + </listitem> + + </itemizedlist> + + <para> + Note that the action statement is not shown in the output of + <literal>SHOW EVENTS</literal>. + </para> + + <para> + To see events for a different schema, you can use the + <literal>FROM</literal> clause. For example, if the + <literal>test</literal> schema had been selected in the + preceding example, the user could view his events on + <literal>myschema</literal> using the following statement: + </para> + +<programlisting> +SHOW EVENTS FROM myschema; +</programlisting> + + <para> + You can filter the list returned by this statement on the event + name using <literal>LIKE</literal> plus a pattern. + </para> + + <para> + Normally, only the events for which the current user is the + definer are shown. A user with the <literal>PROCESS</literal> + privilege can view events defined by all users on a given schema + using <literal>SHOW FULL EVENTS</literal>. + </para> + + <para> + A record of events executed on the server can be read from the + MySQL Server's error log (see + <xref + linkend="events-privileges"/> for a sample). + </para> + + </section> + + <section id="events-show-create"> + + <title>&title-events-show-create;</title> + +<programlisting> +SHOW CREATE EVENT <replaceable>event_name</replaceable> +</programlisting> + + <para> + This statement displays the <literal>CREATE TABLE</literal> + statement needed to re-create a given event. For example (using + the same event <literal>e_daily</literal> defined in + <xref + linkend="events-show"/>): + </para> + +<programlisting> +mysql> SHOW CREATE EVENT test.e_daily\G + +*************************** 1. row *************************** + Event: e_daily +Create Event: CREATE EVENT e_daily + ON SCHEDULE EVERY 1 DAY + STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR + ENABLED + COMMENT 'Saves total number of sessions and + clears the table once per day.' + DO + BEGIN + INSERT INTO site_activity.totals (when, total) + SELECT CURRENT_TIMESTAMP, COUNT(*) + FROM site_activity.sessions; + DELETE FROM site_activity.sessions; + END +</programlisting> + + <para> + Note that the output reflects the current status of the event + (<literal>ENABLED</literal>) rather than the status with which + it was created. + </para> + + </section> + + </section> + + <section id="events-privileges"> + + <title>&title-events-privileges;</title> + + <para> + To enable or disable the execution of scheduled events, it is + necessary to set the value of the global + <literal>event_scheduler</literal> variable. This requires the + <literal>SUPER</literal> privilege. + </para> + + <para> + MySQL 5.1.6 introduces a privilege governing the creation, + modification, and deletion of events, the <literal>EVENT</literal> + privilege. This privilege can be bestowed using + <literal>GRANT</literal>. For example, this + <literal>GRANT</literal> statement confers the + <literal>EVENT</literal> privilege for the schema named + <literal>myschema</literal> on the user + <literal>jon@ghidora</literal>: + </para> + +<programlisting> +GRANT EVENT ON myschema.* TO jon@ghidora; +</programlisting> + + <para> + (We assume that this user account already exists, and that we wish + for it to remain unchanged otherwise.) + </para> + + <para> + To grant this same user the <literal>EVENT</literal> privilege on + all schemas would require the following statement: + </para> + +<programlisting> +GRANT EVENT ON *.* TO jon@ghidora; +</programlisting> + + <para> + Note that the <literal>EVENT</literal> privilege has schema-level + scope. Therefore, trying to grant it on a single table results in + an error as shown: + </para> + +<programlisting> +mysql> <userinput>GRANT EVENT ON myschema.mytable TO jon@ghidora;</userinput> +<errortext>ERROR 1144 (42000): Illegal GRANT/REVOKE command; please +consult the manual to see which privileges can be used</errortext> +</programlisting> + + <para> + It is important to understand that an event is executed with the + privileges of its definer, and that it cannot perform any actions + for which its definer does not have the requisite privileges. For + example, suppose that <literal>jon@ghidora</literal> has the + <literal>EVENT</literal> privilege for + <literal>myschema</literal>. Suppose also that this user has the + <literal>SELECT</literal> privilege for + <literal>myschema</literal>, but no other privileges for this + schema. It is possible for <literal>jon@ghidora</literal> to + create a new event such as this one: + </para> + +<programlisting> +CREATE EVENT e_store_ts + ON SCHEDULE EVERY 10 SECOND + DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP()); +</programlisting> + + <para> + The user waits for a minute or so, and then performs a + <literal>SELECT * FROM mytable;</literal> query, expecting to see + several new rows in the table. Instead, he finds that the table is + empty. Since he does not have the <literal>INSERT</literal> + privilege for the table in question, the event has no effect. + </para> + + <para> + If you inspect the MySQL error log + (<filename><replaceable>hostname</replaceable>.err</filename>), + you can see that the event is executing, but the action it is + attempting to perform fails, as indicated by + <literal>RetCode=0</literal>: + </para> + +<programlisting> +060209 22:39:44 [Note] EVEX EXECUTING event newdb.e [EXPR:10] +060209 22:39:44 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0 +060209 22:39:54 [Note] EVEX EXECUTING event newdb.e [EXPR:10] +060209 22:39:54 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0 +060209 22:40:04 [Note] EVEX EXECUTING event newdb.e [EXPR:10] +060209 22:40:04 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0 +</programlisting> + + <para> + Since this user very likely does not have access to the error log, + he can verify whether the event's action statement is valid by + running it himself: + </para> + +<programlisting> +mysql> <userinput>INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());</userinput> +<errortext>ERROR 1142 (42000): INSERT command denied to user +'jon'@'ghidora' for table 'mytable'</errortext> +</programlisting> + + <para> + Inspection of the <literal>INFORMATION_SCHEMA.EVENTS</literal> + table shows that <literal>e_store_ts</literal> exists and is + enabled, but its <literal>LAST_EXECUTED</literal> column is + <literal>NULL</literal>: + </para> + +<programlisting> +mysql> <userinput>SELECT * FROM EVENTS</userinput> + > <userinput>WHERE EVENT_NAME='e_store_ts'</userinput> + > <userinput>AND EVENT_SCHEMA='myschema'\G</userinput> +*************************** 1. row *************************** + EVENT_CATALOG: NULL + EVENT_SCHEMA: myschema + EVENT_NAME: e_store_ts + DEFINER: jon@ghidora + EVENT_BODY: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP()) + EVENT_TYPE: RECURRING + EXECUTE_AT: NULL +INTERVAL_VALUE: 5 +INTERVAL_FIELD: INTERVAL_SECOND + SQL_MODE: NULL + STARTS: 0000-00-00 00:00:00 + ENDS: 0000-00-00 00:00:00 + STATUS: ENABLED + ON_COMPLETION: NOT PRESERVE + CREATED: 2006-02-09 22:36:06 + LAST_ALTERED: 2006-02-09 22:36:06 + LAST_EXECUTED: NULL + EVENT_COMMENT: +1 row in set (0.00 sec) +</programlisting> + + <para> + To rescind the <literal>EVENT</literal> privilege, use the + <literal>REVOKE</literal> statement. In this example, the + <literal>EVENT</literal> privilege on the schema + <literal>myschema</literal> is removed from the + <literal>jon@ghidora</literal> user account: + </para> + +<programlisting> +REVOKE EVENT ON myschema.* FROM jon@ghidora; +</programlisting> + + <para> + <emphasis role="bold">Important</emphasis>: Revoking the + <literal>EVENT</literal> privilege from a user account does + <emphasis>not</emphasis> delete or disable any events that may + have been created by that account. + </para> + + <para> + For example, suppose that that user <literal>jon@ghidora</literal> + has been granted the <literal>EVENT</literal> and + <literal>INSERT</literal> privileges on the + <literal>myschema</literal> schema. This user then creates the + following event: + </para> + +<programlisting> +CREATE EVENT e_insert + ON SCHEDULE EVERY 7 SECOND + SO INSERT INTO myschema.mytable; +</programlisting> + + <para> + After this event has been created, <literal>root</literal> revokes + the <literal>EVENT</literal> privilege for + <literal>jon@ghidora</literal>. However, + <literal>e_insert</literal> continues to execute, inserting a new + row into <literal>mytable</literal> each seven seconds. + </para> + + <para> + Event definitions are stored in the <literal>mysql.event</literal> + table, which was added in MySQL 5.1.6. To drop an event created by + another user account, the MySQL <literal>root</literal> user (or + another user with the necessary privileges) can delete rows from + this table. For example, to remove the event + <literal>e_insert</literal> shown previously, + <literal>root</literal> can use the following statement: + </para> + +<programlisting> +DELETE FROM mysql.event + WHERE db = 'myschema' + AND definer = 'jon@ghidora' + AND name = 'e_insert'; +</programlisting> + + <para> + It is very important to match the event name, schema name, and + user account when deleting rows from the + <literal>mysql.event</literal> table. This is because: + </para> + + <itemizedlist> + + <listitem> + <para> + The same user can create different events of the same name in + different schemas. + </para> + </listitem> + + <listitem> + <para> + Different users can create different events having the same + name in the same schema. + </para> + </listitem> + + </itemizedlist> + + <para> + Users' <literal>EVENT</literal> privileges are stored in the + <literal>Event_priv</literal> columns of the + <literal>mysql.user</literal> and <literal>mysql.db</literal> + tables. In both cases, this column holds one of the values + '<literal>Y</literal>' or '<literal>N</literal>'. + '<literal>N</literal>' is the default. + <literal>mysql.user.Event_priv</literal> is set to + '<literal>Y</literal>' for a given user only if that user has the + global <literal>EVENT</literal> privilege (that is, if the + privilege was bestowed using <literal>GRANT EVENT ON + *.*</literal>). For a schema-level <literal>EVENT</literal> + privilege, <literal>GRANT</literal> creates a row in + <literal>mysql.db</literal> and sets that row's + <literal>Db</literal> column to the name of the schema, the + <literal>User</literal> column to the name of the user, and the + <literal>Event_priv</literal> column to '<literal>Y</literal>'. + There should never be any need to manipulate these tables + directly, since the <literal>GRANT EVENT</literal> and + <literal>REVOKE EVENT</literal> statement perform the required + operations on them. + </para> + + <remark role="todo"> + [js] Move to status variables section. + </remark> + + <para> + MySQL 5.1.6 introduces five status variables providing counts of + event-related operations (but <emphasis>not</emphasis> of + statements executed by events — see + <xref linkend="events-limitations-restrictions"/>). These are: + </para> + + <itemizedlist> + + <listitem> + <para> + <literal>Com_create_event</literal>: The number of + <literal>CREATE EVENT</literal> statements executed since the + last server restart. + </para> + </listitem> + + <listitem> + <para> + <literal>Com_alter_event</literal>: The number of + <literal>ALTER EVENT</literal> statements executed since the + last server restart. + </para> + </listitem> + + <listitem> + <para> + <literal>Com_drop_event</literal>: The number of <literal>DROP + EVENT</literal> statements executed since the last server + restart. + </para> + </listitem> + + <listitem> + <para> + <literal>Com_show_create_event</literal>: The number of + <literal>SHOW CREATE EVENT</literal> statements executed since + the last server restart. + </para> + </listitem> + + <listitem> + <para> + <literal>Com_show_events</literal>: The number of + <literal>SHOW EVENTS</literal> statements executed since the + last server restart. + </para> + </listitem> + + </itemizedlist> + + <para> + You can view current values for all of these at one time by + running the statement <literal>SHOW STATUS LIKE + '%event%';</literal>. + </para> + + <remark role="todo"> + Find out what we are going to do regarding a SHOW EVENT STATUS + statement (if anything). + </remark> + + </section> + + <section id="events-limitations-restrictions"> + + <title>&title-events-limitations-restrictions;</title> + + <para> + This section lists restrictions and limitations applying to event + scheduling in MySQL. + </para> + + <para> + In MySQL 5.1.6, any table referenced in an event's action + statement must be fully qualified with the name of the schema in + which it occurs (that is, as + <literal><replaceable>schema_name</replaceable>.<replaceable>table_name</replaceable></literal>). + </para> + + <para> + An event may not be created, altered, or dropped by a trigger, + stored routine, or another event. This is by design. However, an + event may create, alter, or drop triggers and stored routines. + </para> + + <para> + The resolution of event schedules is measured in seconds. There is + no way to cause events scheduled to occur at the same second to + execute in a given order. + </para> + + <para> + Execution of event statements have no affect on the server's + statement counts such as <literal>Com_select</literal> and + <literal>Com_insert</literal> that are displayed by <literal>SHOW + STATUS</literal>. + </para> + + <para> + In MySQL 5.1.6, you may not view another user's events in the + <literal>INFORMATION_SCHEMA.EVENTS</literal> table. In other + words, any query made against this table is treated as though it + contains the condition <literal>DEFINER = CURRENT_USER()</literal> + in the <literal>WHERE</literal> clause. Beginning with MySQL + 5.1.7, this restriction is lifted for users with the + <literal>PROCESS</literal> privilege. + </para> + + <para> + Events cannot be created with a start time that is in the past. + </para> + + <para> + In MySQL 5.1.6, <literal>INFORMATION_SCHEMA.EVENTS</literal> shows + <literal>NULL</literal> in the <literal>SQL_MODE</literal> column. + In MySQL 5.1.7, the <literal>SQL_MODE</literal> displayed is that + in effect when the event was created. + </para> + + <para> + In MySQL 5.1.6, the only way to drop or alter an event created by + a user who is not the definer of that event is by manipulation of + the <literal>mysql.event</literal> system table by the MySQL + <literal>root</literal> user or by another user with privileges on + this table. Beginning with MySQL 5.1.7, <literal>DROP + USER</literal> drops all events for which that user was the + definer; also beginning with MySQL 5.1.7 <literal>DROP + SCHEMA</literal> drops all events associated with the dropped + schema. + </para> + + </section> + +</chapter> Modified: trunk/refman-5.1/information-schema.xml =================================================================== --- trunk/refman-5.1/information-schema.xml 2006-02-13 16:46:05 UTC (rev 1295) +++ trunk/refman-5.1/information-schema.xml 2006-02-13 19:14:48 UTC (rev 1296) @@ -3655,22 +3655,22 @@ <row> <entry><literal>EVENT_CATALOG</literal></entry> <entry/> - <entry/> + <entry><literal>NULL</literal></entry> </row> <row> <entry><literal>EVENT_SCHEMA</literal></entry> + <entry><literal>Db</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>EVENT_NAME</literal></entry> + <entry><literal>Name</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>DEFINER</literal></entry> + <entry><literal>Definer</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>EVENT_BODY</literal></entry> @@ -3679,23 +3679,23 @@ </row> <row> <entry><literal>EVENT_TYPE</literal></entry> + <entry><literal>Type</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>EXECUTE_AT</literal></entry> + <entry><literal>Execute at</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>INTERVAL_VALUE</literal></entry> + <entry><literal>Interval value</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>INTERVAL_FIELD</literal></entry> + <entry><literal>Interval field</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>SQL_MODE</literal></entry> @@ -3704,18 +3704,18 @@ </row> <row> <entry><literal>STARTS</literal></entry> + <entry><literal>Starts</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>ENDS</literal></entry> + <entry><literal>Ends</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>STATUS</literal></entry> + <entry><literal>Status</literal></entry> <entry/> - <entry/> </row> <row> <entry><literal>ON_COMPLETION</literal></entry> @@ -3754,16 +3754,272 @@ <listitem> <para> - The <literal>EVENTS</literal> table was added in MySQL 5.1.6. + <literal>EVENT_CATALOG</literal>: The value of this column is + always <literal>NULL</literal>. </para> </listitem> + <listitem> + <para> + <literal>EVENT_SCHEMA</literal>: The name of the schema + (database) to which this event belongs. + </para> + </listitem> + + <listitem> + <para> + <literal>EVENT_NAME</literal>: The name of the event. + </para> + </listitem> + + <listitem> + <para> + <literal>DEFINER</literal>: The user who created the event. + Always displayed in + <literal><replaceable>username</replaceable>@<replaceable>hostname</replaceable></literal> + format. + </para> + </listitem> + + <listitem> + <para> + <literal>EVENT_BODY</literal>: The text of the SQL statement + making up the event's <literal>DO</literal> clause; in other + words, the statement executed by this event. + </para> + </listitem> + + <listitem> + <para> + <literal>EVENT_TYPE</literal>: One of the two values + <literal>ONE TIME</literal> or <literal>RECURRING</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>EXECUTE_AT</literal>: For a transient event, this is + the <literal>DATETIME</literal> value specified in the + <literal>AT</literal> clause of the <literal>CREATE + EVENT</literal> statement used to create the event, or of the + last <literal>ALTER EVENT</literal> statement that modified + the event. The value shown in this column reflects the + addition or subtraction of any INTERVAL value included in the + event's <literal>AT</literal> clause. For example, if an event + is created using <literal>ON SCHEDULE AT CURRENT_TIMESTAMP + + '1:6' DAY_HOUR</literal>, and the event was created at + 2006-02-09 14:05:30, then the value shown in this column would + be <literal>'2006-02-10 20:05:30'</literal>. + </para> + + <para> + If the event's timing is determined by an + <literal>EVERY</literal> clause instead of an + <literal>AT</literal> clause (that is, if the event is + recurring), then the value of this column is + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>INTERVAL_VALUE</literal>: For recurring events, this + column contains the numeric portion of the event's + <literal>EVERY</literal> clause. + </para> + + <para> + For a transient event (that is, an event whose timing is + determined by an <literal>AT</literal> clause), this column's + value is <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>INTERVAL_FIELD</literal>: For recurring events, this + column contains the units portion of the + <literal>EVERY</literal> clause governing the timing of the + event, prefixed with '<literal>INTERVAL_</literal>'. Thus, + this column contains a value such as + '<literal>INTERVAL_YEAR</literal>', + '<literal>INTERVAL_QUARTER</literal>', + '<literal>INTERVAL_DAY</literal>', and so on. + </para> + + <para> + For a transient event (that is, an event whose timing is + determined by an <literal>AT</literal> clause), this column's + value is <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>SQL_MODE</literal>: The SQL mode in effect at the + time the event was created or altered. + </para> + </listitem> + + <listitem> + <para> + <literal>STARTS</literal>: For a recurring event whose + definition includes a <literal>STARTS</literal> clause, this + column contains the corresponding <literal>DATETIME</literal> + value. As with the <literal>EXECUTE_AT</literal> column, this + value resolves any expressions used. + </para> + + <para> + If there is no <literal>STARTS</literal> clause affecting the + timing of the event, then this column contains + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>ENDS</literal>: For a recurring event whose + definition includes a <literal>ENDS</literal> clause, this + column contains the corresponding <literal>DATETIME</literal> + value. As with the <literal>EXECUTE_AT</literal> column (see + previous example), this value resolves any expressions used. + </para> + + <para> + If there is no <literal>ENDS</literal> clause affecting the + timing of the event, then this column contains + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>STATUS</literal>: One of the two values + <literal>ENABLED</literal> or <literal>DISABLE</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>ON_COMPLETION</literal>: One of the two values + <literal>PRESERVE</literal> or <literal>NOT + PRESERVE</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>CREATED</literal>: The date and time when the event + was created. This is a <literal>DATETIME</literal> value. + </para> + </listitem> + + <listitem> + <para> + <literal>LAST_ALTERED</literal>: The date and time when the + event was last modified. This is a <literal>DATETIME</literal> + value. If the event has not been modified since its creation, + ten this column holds the same value as the + <literal>CREATED</literal> column. + </para> + </listitem> + + <listitem> + <para> + <literal>LAST_EXECUTED</literal>: The date and time when the + event last executed. A <literal>DATETIME</literal> value. If + the event has never executed, then this column's value is + <literal>NULL</literal>. + </para> + </listitem> + + <listitem> + <para> + <literal>EVENT_COMMENT</literal>: The text of a comment, if + the event has one. If there is no comment, then the value of + this column is an empty string. + </para> + </listitem> + </itemizedlist> - <remark role="todo"> - Add xref to SHOW EVENTS when that is documented. - </remark> + <para> + <emphasis role="bold">Example</emphasis>: Suppose the user + <literal>jon@ghidora</literal> creates an event named + <literal>e_daily</literal>, then modifies it a few minutes later + using an <literal>ALTER EVENT</literal> statement, as shown here: + </para> +<programlisting> +DELIMITER | + +CREATE EVENT e_daily + ON SCHEDULE EVERY 1 DAY + STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR + DISABLE + COMMENT 'Saves total number of sessions and + clears the table once per 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 ; + +ALTER EVENT e_daily + ENABLED; +</programlisting> + + <para> + (Note that comments can span multiple lines.) + </para> + + <para> + This user can then run the following <literal>SELECT</literal> + statement, and obtain the output shown: + </para> + +<programlisting> +mysql> <userinput>SELECT * FROM INFORMATION_SCHEMA.EVENTS</userinput> + > <userinput>WHERE EVENT_NAME = 'e_daily'</userinput> + > <userinput>AND EVENT_SCHEMA = 'myschema'\G</userinput> + +*************************** 1. row *************************** + EVENT_CATALOG: NULL + EVENT_SCHEMA: myschema + EVENT_NAME: e_daily + DEFINER: jon@ghidora + EVENT_BODY: BEGIN + INSERT INTO site_activity.totals (when, total) + SELECT CURRENT_TIMESTAMP, COUNT(*) + FROM site_activity.sessions; + DELETE FROM site_activity.sessions; + END + EVENT_TYPE: RECURRING + EXECUTE_AT: NULL + INTERVAL_VALUE: 1 + INTERVAL_FIELD: INTERVAL_DAY + SQL_MODE: NULL + STARTS: 2006-02-10 20:41:23 + ENDS: NULL + STATUS: ENABLED + ON_COMPLETION: DROP + CREATED: 2006-02-09 14:35:35 + LAST_ALTERED: 2006-02-09 14:41:23 + LAST_EXECUTED: NULL + EVENT_COMMENT: Saves total number of sessions and + clears the table once per day. +1 row in set (0.50 sec) +</programlisting> + + <para> + See also <xref linkend="events-show"/>. + </para> + </section> <section id="files-table"> @@ -3776,8 +4032,8 @@ </indexterm> <para> - The <literal>FILES</literal> table provides information about - the files in which MySQL NDB tables are stored. + The <literal>FILES</literal> table provides information about the + files in which MySQL NDB tables are stored. </para> <informaltable> Modified: trunk/refman-5.1/manual.xml =================================================================== --- trunk/refman-5.1/manual.xml 2006-02-13 16:46:05 UTC (rev 1295) +++ trunk/refman-5.1/manual.xml 2006-02-13 19:14:48 UTC (rev 1296) @@ -79,6 +79,8 @@ <xi:include href="triggers.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/> + <xi:include href="events.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/> + <xi:include href="views.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/> <xi:include href="information-schema.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/> Modified: trunk/refman-common/titles.en.ent =================================================================== --- trunk/refman-common/titles.en.ent 2006-02-13 16:46:05 UTC (rev 1295) +++ trunk/refman-common/titles.en.ent 2006-02-13 19:14:48 UTC (rev 1296) @@ -357,6 +357,18 @@ <!ENTITY title-errors-in-4-0 "Issues in MySQL 4.0 Fixed in a Later Version"> <!ENTITY title-errors-in-4-1 "Issues in MySQL 4.1 Fixed in a Later Version"> <!ENTITY title-estimating-performance "Estimating Query Performance"> +<!ENTITY title-events "MySQL Event Scheduler"> +<!ENTITY title-events-alter "<literal>ALTER EVENT</literal> Syntax"> +<!ENTITY title-events-create "<literal>CREATE EVENT</literal> Syntax"> +<!ENTITY title-events-drop "<literal>DROP EVENT</literal> Syntax"> +<!ENTITY title-events-information-schema "Where MySQL Stores Event Metadata"> +<!ENTITY title-events-limitations-restrictions "Event Scheduler Limitations and Restrictions"> +<!ENTITY title-events-metadata "Event Metadata"> +<!ENTITY title-events-overview "Event Scheduler Overview"> +<!ENTITY title-events-privileges "The Event Scheduler and MySQL Privileges"> +<!ENTITY title-events-show "<literal>SHOW EVENTS</literal>"> +<!ENTITY title-events-show-create "<literal>SHOW CREATE EVENT</literal>"> +<!ENTITY title-events-syntax "Event Scheduler Syntax"> <!ENTITY title-events-table "The <literal>INFORMATION_SCHEMA EVENTS</literal> Table"> <!ENTITY title-example-auto-increment "Using <literal>AUTO_INCREMENT</literal>"> <!ENTITY title-example-foreign-keys "Using Foreign Keys">
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r1296 - in trunk: refman-5.1 refman-common | jon | 13 Feb |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 1.164 sec. using MySQL 5.1.14-beta-log