Table of Contents
This chapter discusses stored programs and views, which are database objects defined in terms of SQL code that is stored on the server for later execution.
Stored programs include these objects:
Stored routines, that is, stored procedures and functions. A
stored procedure is invoked using the
CALL
statement. A procedure does
not have a return value but can modify its parameters for later
inspection by the caller. It can also generate result sets to be
returned to the client program. A stored function is used much
like a built-in function. you invoke it in an expression and it
returns a value during expression evaluation.
Triggers. A trigger is a named database object that is associated with a table and that is activated when a particular event occurs for the table, such as an insert or update.
Events. An event is a task that the server runs according to schedule.
Views are stored queries that when referenced produce a result set. A view acts as a virtual table.
This chapter describes how to use stored programs and views. The following sections provide additional information about SQL syntax for statements related to these objects:
For each object type, there are CREATE
,
ALTER
, and DROP
statements
that control which objects exist and how they are defined. See
Section 13.1, “Data Definition Statements”.
The CALL
statement is used to
invoke stored procedures. See Section 13.2.1, “CALL
Syntax”.
Stored program definitions include a body that may use compound statements, loops, conditionals, and declared variables. See Section 13.6, “MySQL Compound-Statement Syntax”.
As of MySQL 5.6.6, metadata changes to objects referred to by stored programs are detected and cause automatic reparsing of the affected statements when the program is next executed. For more information, see Section 8.9.4, “Caching of Prepared Statements and Stored Programs”.
Each stored program contains a body that consists of an SQL
statement. This statement may be a compound statement made up of
several statements separated by semicolon (;
)
characters. For example, the following stored procedure has a body
made up of a BEGIN ...
END
block that contains a
SET
statement and a REPEAT
loop that
itself contains another
SET
statement:
CREATE PROCEDURE dorepeat(p1 INT) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT; END;
If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.
To redefine the mysql delimiter, use the
delimiter
command. The following example shows
how to do this for the dorepeat()
procedure
just shown. The delimiter is changed to //
to
enable the entire definition to be passed to the server as a
single statement, and then restored to ;
before
invoking the procedure. This enables the ;
delimiter used in the procedure body to be passed through to the
server rather than being interpreted by mysql
itself.
mysql>delimiter //
mysql>CREATE PROCEDURE dorepeat(p1 INT)
->BEGIN
->SET @x = 0;
->REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
->END
->//
Query OK, 0 rows affected (0.00 sec) mysql>delimiter ;
mysql>CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec) mysql>SELECT @x;
+------+ | @x | +------+ | 1001 | +------+ 1 row in set (0.00 sec)
You can redefine the delimiter to a string other than
//
, and the delimiter can consist of a single
character or multiple characters. You should avoid the use of the
backslash (“\
”) character because
that is the escape character for MySQL.
The following is an example of a function that takes a parameter,
performs an operation using an SQL function, and returns the
result. In this case, it is unnecessary to use
delimiter
because the function definition
contains no internal ;
statement delimiters:
mysql>CREATE FUNCTION hello (s CHAR(20))
mysql>RETURNS CHAR(50) DETERMINISTIC
->RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec) mysql>SELECT hello('world');
+----------------+ | hello('world') | +----------------+ | Hello, world! | +----------------+ 1 row in set (0.00 sec)
Stored routines (procedures and functions) are supported in MySQL 5.6. A stored routine is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored routine instead.
Stored routines require the proc
table in the
mysql
database. This table is created during the
MySQL 5.6 installation procedure. If you are upgrading
to MySQL 5.6 from an earlier version, be sure to update
your grant tables to make sure that the proc
table exists. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.
Stored routines can be particularly useful in certain situations:
When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.
Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.
Stored routines also enable you to have libraries of functions in the database server. This is a feature shared by modern application languages that enable such design internally (for example, by using classes). Using these client application language features is beneficial for the programmer even outside the scope of database use.
MySQL follows the SQL:2003 syntax for stored routines, which is also used by IBM's DB2. All syntax described here is supported and any limitations and extensions are documented where appropriate.
You may find the Stored Procedures User Forum of use when working with stored procedures and functions.
For answers to some commonly asked questions regarding stored routines in MySQL, see Section B.4, “MySQL 5.6 FAQ: Stored Procedures and Functions”.
There are some restrictions on the use of stored routines. See Section E.1, “Restrictions on Stored Programs”.
Binary logging for stored routines takes place as described in Section 18.7, “Binary Logging of Stored Programs”.
A stored routine is either a procedure or a function. Stored
routines are created with the CREATE
PROCEDURE
and CREATE
FUNCTION
statements (see
Section 13.1.12, “CREATE PROCEDURE
and
CREATE FUNCTION
Syntax”). A procedure is invoked using
a CALL
statement (see
Section 13.2.1, “CALL
Syntax”), and can only pass back values using
output variables. A function can be called from inside a statement
just like any other function (that is, by invoking the function's
name), and can return a scalar value. The body of a stored routine
can use compound statements (see
Section 13.6, “MySQL Compound-Statement Syntax”).
Stored routines can be dropped with the DROP
PROCEDURE
and DROP
FUNCTION
statements (see
Section 13.1.21, “DROP PROCEDURE
and
DROP FUNCTION
Syntax”), and altered with the
ALTER PROCEDURE
and
ALTER FUNCTION
statements (see
Section 13.1.4, “ALTER PROCEDURE
Syntax”).
A stored procedure or function is associated with a particular database. This has several implications:
When the routine is invoked, an implicit USE
is performed (and
undone when the routine terminates).
db_name
USE
statements within stored
routines are not permitted.
You can qualify routine names with the database name. This can
be used to refer to a routine that is not in the current
database. For example, to invoke a stored procedure
p
or function f
that is
associated with the test
database, you can
say CALL test.p()
or
test.f()
.
When a database is dropped, all stored routines associated with it are dropped as well.
Stored functions cannot be recursive.
Recursion in stored procedures is permitted but disabled by
default. To enable recursion, set the
max_sp_recursion_depth
server
system variable to a value greater than zero. Stored procedure
recursion increases the demand on thread stack space. If you
increase the value of
max_sp_recursion_depth
, it may be
necessary to increase thread stack size by increasing the value of
thread_stack
at server startup.
See Section 5.1.4, “Server System Variables”, for more
information.
MySQL supports a very useful extension that enables the use of
regular SELECT
statements (that is,
without using cursors or local variables) inside a stored
procedure. The result set of such a query is simply sent directly
to the client. Multiple SELECT
statements generate multiple result sets, so the client must use a
MySQL client library that supports multiple result sets. This
means the client must use a client library from a version of MySQL
at least as recent as 4.1. The client should also specify the
CLIENT_MULTI_RESULTS
option when it connects.
For C programs, this can be done with the
mysql_real_connect()
C API
function. See Section 21.9.3.53, “mysql_real_connect()
”, and
Section 21.9.13, “C API Support for Multiple Statement Execution”.
The MySQL grant system takes stored routines into account as follows:
The CREATE ROUTINE
privilege is
needed to create stored routines.
The ALTER ROUTINE
privilege is
needed to alter or drop stored routines. This privilege is
granted automatically to the creator of a routine if
necessary, and dropped from the creator when the routine is
dropped.
The EXECUTE
privilege is
required to execute stored routines. However, this privilege
is granted automatically to the creator of a routine if
necessary (and dropped from the creator when the routine is
dropped). Also, the default SQL SECURITY
characteristic for a routine is DEFINER
,
which enables users who have access to the database with which
the routine is associated to execute the routine.
If the
automatic_sp_privileges
system variable is 0, the
EXECUTE
and
ALTER ROUTINE
privileges are
not automatically granted to and dropped from the routine
creator.
The creator of a routine is the account used to execute the
CREATE
statement for it. This might not be
the same as the account named as the
DEFINER
in the routine definition.
The server manipulates the mysql.proc
table in
response to statements that create, alter, or drop stored
routines. It is not supported that the server will notice manual
manipulation of this table.
Metadata about stored routines can be obtained as follows:
Query the ROUTINES
table of the
INFORMATION_SCHEMA
database. See
Section 19.19, “The INFORMATION_SCHEMA ROUTINES
Table”.
Use the SHOW CREATE PROCEDURE
and SHOW CREATE FUNCTION
statements to see routine definitions. See
Section 13.7.5.11, “SHOW CREATE PROCEDURE
Syntax”.
Use the SHOW PROCEDURE STATUS
and SHOW FUNCTION STATUS
statements to see routine characteristics. See
Section 13.7.5.29, “SHOW PROCEDURE STATUS
Syntax”.
Within the body of a stored routine (procedure or function) or a
trigger, the value of
LAST_INSERT_ID()
changes the same
way as for statements executed outside the body of these kinds of
objects (see Section 12.14, “Information Functions”). The effect
of a stored routine or trigger upon the value of
LAST_INSERT_ID()
that is seen by
following statements depends on the kind of routine:
If a stored procedure executes statements that change the
value of LAST_INSERT_ID()
, the
changed value is seen by statements that follow the procedure
call.
For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements do not see a changed value.
A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.
A trigger is defined to activate when an
INSERT
,
DELETE
, or
UPDATE
statement executes for the
associated table. A trigger can be set to activate either before or
after the triggering statement. For example, you can have a trigger
activate before each row that is inserted into a table or after each
row that is updated.
MySQL triggers are activated by SQL statements
only. They are not activated by changes in
views, nor by changes to tables made by APIs that do not transmit
SQL statements to the MySQL Server. This means that triggers are
not activated by changes in INFORMATION_SCHEMA
or performance_schema
tables, because these
tables are actually views.
To use triggers if you have upgraded to MySQL 5.6 from an older release that did not support triggers, you should upgrade your grant tables so that they contain the trigger-related privileges. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.
The following discussion describes the syntax for creating and dropping triggers, and shows some examples of how to use them.
You may find the Triggers User Forum of use when working with views.
For answers to some commonly asked questions regarding triggers in MySQL, see Section B.5, “MySQL 5.6 FAQ: Triggers”.
There are some restrictions on the use of triggers; see Section E.1, “Restrictions on Stored Programs”.
Binary logging for triggers takes place as described in Section 18.7, “Binary Logging of Stored Programs”.
To create a trigger or drop a trigger, use the
CREATE TRIGGER
or
DROP TRIGGER
statement. The syntax
for these statements is described in
Section 13.1.15, “CREATE TRIGGER
Syntax”, and
Section 13.1.24, “DROP TRIGGER
Syntax”.
Here is a simple example that associates a trigger with a table
for INSERT
statements. The trigger
acts as an accumulator, summing the values inserted into one of
the columns of the table.
mysql>CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec) mysql>CREATE TRIGGER ins_sum BEFORE INSERT ON account
->FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)
The CREATE TRIGGER
statement
creates a trigger named ins_sum
that is
associated with the account
table. It also
includes clauses that specify the trigger activation time, the
triggering event, and what to do with the trigger activates:
The keyword BEFORE
indicates the trigger
action time. In this case, the trigger should activate before
each row inserted into the table. The other permissible
keyword here is AFTER
.
The keyword INSERT
indicates
the event that activates the trigger. In the example,
INSERT
statements cause trigger
activation. You can also create triggers for
DELETE
and
UPDATE
statements.
The statement following FOR EACH ROW
defines the statement to execute each time the trigger
activates, which occurs once for each row affected by the
triggering statement. In the example, the triggered statement
is a simple
SET
that accumulates the values inserted into the
amount
column. The statement refers to the
column as NEW.amount
which means “the
value of the amount
column to be inserted
into the new row.”
To use the trigger, set the accumulator variable to zero, execute
an INSERT
statement, and then see
what value the variable has afterward:
mysql>SET @sum = 0;
mysql>INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql>SELECT @sum AS 'Total amount inserted';
+-----------------------+ | Total amount inserted | +-----------------------+ | 1852.48 | +-----------------------+
In this case, the value of @sum
after the
INSERT
statement has executed is
14.98 + 1937.50 - 100
, or
1852.48
.
To destroy the trigger, use a DROP
TRIGGER
statement. You must specify the schema name if
the trigger is not in the default schema:
mysql> DROP TRIGGER test.ins_sum;
Triggers for a table are also dropped if you drop the table.
Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.
In addition to the requirement that trigger names be unique for a
schema, there are other limitations on the types of triggers you
can create. In particular, you cannot have two triggers for a
table that have the same activation time and activation event. For
example, you cannot define two BEFORE INSERT
triggers or two AFTER UPDATE
triggers for a
table. This should rarely be a significant limitation, because it
is possible to define a trigger that executes multiple statements
by using the BEGIN ...
END
compound statement construct after FOR EACH
ROW
. (An example appears later in this section.)
The OLD
and NEW
keywords
enable you to access columns in the rows affected by a trigger.
(OLD
and NEW
are not case
sensitive.) In an INSERT
trigger, only
NEW.
can be
used; there is no old row. In a col_name
DELETE
trigger,
only OLD.
can be used; there is no new row. In an col_name
UPDATE
trigger, you can use
OLD.
to
refer to the columns of a row before it is updated and
col_name
NEW.
to
refer to the columns of the row after it is updated.
col_name
A column named with OLD
is read only. You can
refer to it (if you have the SELECT
privilege), but not modify it. A column named with
NEW
can be referred to if you have the
SELECT
privilege for it. In a
BEFORE
trigger, you can also change its value
with SET NEW.
if you have the
col_name
=
value
UPDATE
privilege for it. This means
you can use a trigger to modify the values to be inserted into a
new row or that are used to update a row.
In a BEFORE
trigger, the NEW
value for an AUTO_INCREMENT
column is 0, not
the automatically generated sequence number that will be generated
when the new record actually is inserted.
OLD
and NEW
are MySQL
extensions to triggers.
By using the BEGIN ...
END
construct, you can define a trigger that executes
multiple statements. Within the BEGIN
block,
you also can use other syntax that is permitted within stored
routines such as conditionals and loops. However, just as for
stored routines, if you use the mysql program
to define a trigger that executes multiple statements, it is
necessary to redefine the mysql statement
delimiter so that you can use the ;
statement
delimiter within the trigger definition. The following example
illustrates these points. It defines an UPDATE
trigger that checks the new value to be used for updating each
row, and modifies the value to be within the range from 0 to 100.
This must be a BEFORE
trigger because the value
needs to be checked before it is used to update the row:
mysql>delimiter //
mysql>CREATE TRIGGER upd_check BEFORE UPDATE ON account
->FOR EACH ROW
->BEGIN
->IF NEW.amount < 0 THEN
->SET NEW.amount = 0;
->ELSEIF NEW.amount > 100 THEN
->SET NEW.amount = 100;
->END IF;
->END;//
mysql>delimiter ;
It can be easier to define a stored procedure separately and then
invoke it from the trigger using a simple
CALL
statement. This is also
advantageous if you want to invoke the same routine from within
several triggers.
There are some limitations on what can appear in statements that a trigger executes when activated:
The trigger cannot use the CALL
statement to invoke stored procedures that return data to the
client or that use dynamic SQL. (Stored procedures are
permitted to return data to the trigger through
OUT
or INOUT
parameters.)
The trigger cannot use statements that explicitly or
implicitly begin or end a transaction such as
START
TRANSACTION
, COMMIT
,
or ROLLBACK
.
MySQL handles errors during trigger execution as follows:
If a BEFORE
trigger fails, the operation on
the corresponding row is not performed.
A BEFORE
trigger is activated by the
attempt to insert or modify the row,
regardless of whether the attempt subsequently succeeds.
An AFTER
trigger is executed only if the
BEFORE
trigger (if any) and the row
operation both execute successfully.
An error during either a BEFORE
or
AFTER
trigger results in failure of the
entire statement that caused trigger invocation.
For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.
Metadata about triggers can be obtained as follows:
Query the TRIGGERS
table of the
INFORMATION_SCHEMA
database. See
Section 19.27, “The INFORMATION_SCHEMA TRIGGERS
Table”.
Use the SHOW TRIGGERS
statement. See Section 13.7.5.39, “SHOW TRIGGERS
Syntax”.
The MySQL Event Scheduler manages the scheduling and execution of events: Tasks that run according to schedule. The following discussion covers the Event Scheduler and is divided into the following sections:
Section 18.4.1, “Event Scheduler Overview”, provides an introduction to and conceptual overview of MySQL Events.
Section 18.4.3, “Event Syntax”, discusses the SQL statements for creating, altering, and dropping MySQL Events.
Section 18.4.4, “Event Metadata”, shows how to obtain information about events and how this information is stored by the MySQL Server.
Section 18.4.6, “The Event Scheduler and MySQL Privileges”, discusses the privileges required to work with events and the ramifications that events have with regard to privileges when executing.
Stored routines require the event
table in the
mysql
database. This table is created during the
MySQL 5.6 installation procedure. If you are upgrading
to MySQL 5.6 from an earlier version, be sure to update
your grant tables to make sure that the event
table exists. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.
You may find the MySQL Event Scheduler User Forum of use when working with scheduled events.
There are some restrictions on the use of events; see Section E.1, “Restrictions on Stored Programs”.
Binary logging for events takes place as described in Section 18.7, “Binary Logging of Stored Programs”.
MySQL Events are tasks that run according to a schedule.
Therefore, we sometimes refer to them as
scheduled events. When you create an event,
you are creating a named database object containing one or more
SQL statements 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 crontab
(also known as a “cron job”) or the Windows Task
Scheduler.
Scheduled tasks of this type are also sometimes known as “temporal triggers”, implying that these are objects that are triggered by the passage of time. While this is essentially correct, we prefer to use the term events to avoid confusion with triggers of the type discussed in Section 18.3, “Using Triggers”. Events should more specifically not be confused with “temporary triggers”. Whereas a trigger is a database object whose statements are executed in response to a specific type of event that occurs on a given table, a (scheduled) event is an object whose statements are executed in response to the passage of a specified time interval.
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.
MySQL Events have the following major features and properties:
In MySQL 5.6, an event is uniquely identified by its name and the schema to which it is assigned.
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
BEGIN ...
END
block if desired (see
Section 13.6, “MySQL Compound-Statement Syntax”). An event's
timing can be either one-time or
recurrent. A one-time 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.)
If a repeating event does not terminate within its scheduling
interval, the result may be multiple instances of the event
executing simultaneously. If this is undesirable, you should
institute a mechanism to prevent simultaneous instances. For
example, you could use the
GET_LOCK()
function, or row or
table locking.
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. 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 Section 18.4.6, “The Event Scheduler and MySQL Privileges” for details.
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 Section 13.1.2, “ALTER EVENT
Syntax”.
The default definer of an event is the user who created the
event, unless the event has been altered, in which case the
definer is the user who issued the last
ALTER EVENT
statement affecting
that event. An event can be modified by any user having the
EVENT
privilege on the database
for which the event is defined. See
Section 18.4.6, “The Event Scheduler and MySQL Privileges”.
An event's action statement may include most SQL statements permitted within stored routines. For restrictions, see Section E.1, “Restrictions on Stored Programs”.
Events are executed by a special event scheduler
thread; when we refer to the Event Scheduler, we
actually refer to this thread. When running, the event scheduler
thread and its current state can be seen by users having the
PROCESS
privilege in the output of
SHOW PROCESSLIST
, as shown in the
discussion that follows.
The global event_scheduler
system
variable determines whether the Event Scheduler is enabled and
running on the server. It has one of these 3 values, which affect
event scheduling as described here:
OFF
: The Event Scheduler is stopped. The
event scheduler thread does not run, is not shown in the
output of SHOW PROCESSLIST
, and
no scheduled events are executed. OFF
is
the default value for
event_scheduler
.
When the Event Scheduler is stopped
(event_scheduler
is
OFF
), it can be started by setting the
value of event_scheduler
to
ON
. (See next item.)
ON
: The Event Scheduler is started; the
event scheduler thread runs and executes all scheduled events.
When the Event Scheduler is ON
, the event
scheduler thread is listed in the output of
SHOW PROCESSLIST
as a daemon
process, and its state is represented as shown here:
mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
*************************** 2. row ***************************
Id: 2
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 3
State: Waiting for next activation
Info: NULL
2 rows in set (0.00 sec)
Event scheduling can be stopped by setting the value of
event_scheduler
to
OFF
.
DISABLED
: This value renders the Event
Scheduler nonoperational. When the Event Scheduler is
DISABLED
, the event scheduler thread does
not run (and so does not appear in the output of
SHOW PROCESSLIST
). In addition,
the Event Scheduler state cannot be changed at runtime.
If the Event Scheduler status has not been set to
DISABLED
,
event_scheduler
can be toggled
between ON
and OFF
(using
SET
). It
is also possible to use 0
for
OFF
, and 1
for
ON
when setting this variable. Thus, any of the
following 4 statements can be used in the mysql
client to turn on the Event Scheduler:
SET GLOBAL event_scheduler = ON; SET @@global.event_scheduler = ON; SET GLOBAL event_scheduler = 1; SET @@global.event_scheduler = 1;
Similarly, any of these 4 statements can be used to turn off the Event Scheduler:
SET GLOBAL event_scheduler = OFF; SET @@global.event_scheduler = OFF; SET GLOBAL event_scheduler = 0; SET @@global.event_scheduler = 0;
Although ON
and OFF
have
numeric equivalents, the value displayed for
event_scheduler
by
SELECT
or SHOW
VARIABLES
is always one of OFF
,
ON
, or DISABLED
.
DISABLED
has no numeric
equivalent. For this reason, ON
and
OFF
are usually preferred over
1
and 0
when setting this
variable.
Note that attempting to set
event_scheduler
without
specifying it as a global variable causes an error:
mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL
variable and should be set with SET GLOBAL
It is possible to set the Event Scheduler to
DISABLED
only at server startup. If
event_scheduler
is
ON
or OFF
, you cannot set
it to DISABLED
at runtime. Also, if the Event
Scheduler is set to DISABLED
at startup, you
cannot change the value of
event_scheduler
at runtime.
To disable the event scheduler, use one of the following two methods:
As a command-line option when starting the server:
--event-scheduler=DISABLED
In the server configuration file (my.cnf
,
or my.ini
on Windows systems), include
the line where it will be read by the server (for example, in
a [mysqld]
section):
event_scheduler=DISABLED
To enable the Event Scheduler, restart the server without the
--event-scheduler=DISABLED
command-line option, or after removing or commenting out the line
containing event-scheduler=DISABLED
in the server configuration file, as appropriate. Alternatively,
you can use ON
(or 1
) or
OFF
(or 0
) in place of the
DISABLED
value when starting the server.
You can issue event-manipulation statements when
event_scheduler
is set to
DISABLED
. No warnings or errors are generated
in such cases (provided that the statements are themselves
valid). However, scheduled events cannot execute until this
variable is set to ON
(or
1
). Once this has been done, the event
scheduler thread executes all events whose scheduling conditions
are satisfied.
Starting the MySQL server with the
--skip-grant-tables
option causes
event_scheduler
to be set to
DISABLED
, overriding any other value set either
on the command line or in the my.cnf
or
my.ini
file (Bug #26807).
For SQL statements used to create, alter, and drop events, see Section 18.4.3, “Event Syntax”.
MySQL 5.6 provides an
EVENTS
table in the
INFORMATION_SCHEMA
database. This table can be
queried to obtain information about scheduled events which have
been defined on the server. See Section 18.4.4, “Event Metadata”,
and Section 19.7, “The INFORMATION_SCHEMA EVENTS
Table”, for more information.
For information regarding event scheduling and the MySQL privilege system, see Section 18.4.6, “The Event Scheduler and MySQL Privileges”.
MySQL 5.6 provides several SQL statements for working with scheduled events:
New events are defined using the CREATE
EVENT
statement. See Section 13.1.9, “CREATE EVENT
Syntax”.
The definition of an existing event can be changed by means of
the ALTER EVENT
statement. See
Section 13.1.2, “ALTER EVENT
Syntax”.
When a scheduled event is no longer wanted or needed, it can
be deleted from the server by its definer using the
DROP EVENT
statement. See
Section 13.1.18, “DROP EVENT
Syntax”. Whether an event persists past
the end of its schedule also depends on its ON
COMPLETION
clause, if it has one. See
Section 13.1.9, “CREATE EVENT
Syntax”.
An event can be dropped by any user having the
EVENT
privilege for the
database on which the event is defined. See
Section 18.4.6, “The Event Scheduler and MySQL Privileges”.
Metadata about events can be obtained as follows:
Query the event
table of the
mysql
database.
Query the EVENTS
table of the
INFORMATION_SCHEMA
database. See
Section 19.7, “The INFORMATION_SCHEMA EVENTS
Table”.
Use the SHOW CREATE EVENT
statement. See Section 13.7.5.9, “SHOW CREATE EVENT
Syntax”.
Use the SHOW EVENTS
statement.
See Section 13.7.5.19, “SHOW EVENTS
Syntax”.
Event Scheduler Time Representation
Each session in MySQL has a session time zone (STZ). This is the
session time_zone
value that is
initialized from the server's global
time_zone
value when the session
begins but may be changed during the session.
The session time zone that is current when a
CREATE EVENT
or
ALTER EVENT
statement executes is
used to interpret times specified in the event definition. This
becomes the event time zone (ETZ); that is, the time zone that is
used for event scheduling and is in effect within the event as it
executes.
For representation of event information in the
mysql.event
table, the
execute_at
, starts
, and
ends
times are converted to UTC and stored
along with the event time zone. This enables event execution to
proceed as defined regardless of any subsequent changes to the
server time zone or daylight saving time effects. The
last_executed
time is also stored in UTC.
If you select information from mysql.event
, the
times just mentioned are retrieved as UTC values. These times can
also be obtained by selecting from the
INFORMATION_SCHEMA.EVENTS
table or
from SHOW EVENTS
, but they are
reported as ETZ values. Other times available from these sources
indicate when an event was created or last altered; these are
displayed as STZ values. The following table summarizes
representation of event times.
Value | mysql.event | INFORMATION_SCHEMA.EVENTS | SHOW EVENTS |
---|---|---|---|
Execute at | UTC | ETZ | ETZ |
Starts | UTC | ETZ | ETZ |
Ends | UTC | ETZ | ETZ |
Last executed | UTC | ETZ | n/a |
Created | STZ | STZ | n/a |
Last altered | STZ | STZ | n/a |
The Event Scheduler writes information about event execution that terminates with an error or warning to the MySQL Server's error log. See Section 18.4.6, “The Event Scheduler and MySQL Privileges” for an example.
To obtain information about the state of the Event Scheduler for debugging and troubleshooting purposes, run mysqladmin debug (see Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”); after running this command, the server's error log contains output relating to the Event Scheduler, similar to what is shown here:
Events status: LLA = Last Locked At LUA = Last Unlocked At WOC = Waiting On Condition DL = Data Locked Event scheduler status: State : INITIALIZED Thread id : 0 LLA : init_scheduler:313 LUA : init_scheduler:318 WOC : NO Workers : 0 Executed : 0 Data locked: NO Event queue status: Element count : 1 Data locked : NO Attempting lock : NO LLA : init_queue:148 LUA : init_queue:168 WOC : NO Next activation : 0000-00-00 00:00:00
In statements that occur as part of events executed by the Event
Scheduler, diagnostics messages (not only errors, but also
warnings) are written to the error log, and, on Windows, to the
application event log. For frequently executed events, it is
possible for this to result in many logged messages. For example,
for SELECT ... INTO
statements, if the
query returns no rows, a warning with error code 1329 occurs
(var_list
No data
), and the variable values remain
unchanged. If the query returns multiple rows, error 1172 occurs
(Result consisted of more than one row
). For
either condition, you can avoid having the warnings be logged by
declaring a condition handler; see
Section 13.6.7.2, “DECLARE ...
HANDLER
Syntax”. For statements that may
retrieve multiple rows, another strategy is to use LIMIT
1
to limit the result set to a single row.
To enable or disable the execution of scheduled events, it is
necessary to set the value of the global
event_scheduler
system variable.
This requires the SUPER
privilege.
The EVENT
privilege governs the
creation, modification, and deletion of events. This privilege can
be bestowed using GRANT
. For
example, this GRANT
statement
confers the EVENT
privilege for the
schema named myschema
on the user
jon@ghidora
:
GRANT EVENT ON myschema.* TO jon@ghidora;
(We assume that this user account already exists, and that we wish for it to remain unchanged otherwise.)
To grant this same user the EVENT
privilege on all schemas, use the following statement:
GRANT EVENT ON *.* TO jon@ghidora;
The EVENT
privilege has global or
schema-level scope. Therefore, trying to grant it on a single
table results in an error as shown:
mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please
consult the manual to see which privileges can be used
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 jon@ghidora
has the
EVENT
privilege for
myschema
. Suppose also that this user has the
SELECT
privilege for
myschema
, but no other privileges for this
schema. It is possible for jon@ghidora
to
create a new event such as this one:
CREATE EVENT e_store_ts ON SCHEDULE EVERY 10 SECOND DO INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
The user waits for a minute or so, and then performs a
SELECT * FROM mytable;
query, expecting to see
several new rows in the table. Instead, the table is empty. Since
the user does not have the INSERT
privilege for the table in question, the event has no effect.
If you inspect the MySQL error log
(
),
you can see that the event is executing, but the action it is
attempting to perform fails, as indicated by
hostname
.errRetCode=0
:
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
Since this user very likely does not have access to the error log, it is possible to verify whether the event's action statement is valid by executing it directly:
mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
ERROR 1142 (42000): INSERT command denied to user
'jon'@'ghidora' for table 'mytable'
Inspection of the
INFORMATION_SCHEMA.EVENTS
table shows
that e_store_ts
exists and is enabled, but its
LAST_EXECUTED
column is
NULL
:
mysql>SELECT * FROM INFORMATION_SCHEMA.EVENTS
>WHERE EVENT_NAME='e_store_ts'
>AND EVENT_SCHEMA='myschema'\G
*************************** 1. row *************************** EVENT_CATALOG: NULL EVENT_SCHEMA: myschema EVENT_NAME: e_store_ts DEFINER: jon@ghidora EVENT_BODY: SQL EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP()) EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 5 INTERVAL_FIELD: 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)
To rescind the EVENT
privilege, use
the REVOKE
statement. In this
example, the EVENT
privilege on the
schema myschema
is removed from the
jon@ghidora
user account:
REVOKE EVENT ON myschema.* FROM jon@ghidora;
Revoking the EVENT
privilege from
a user does not delete or disable any events that may have been
created by that user.
An event is not migrated or dropped as a result of renaming or dropping the user who created it.
Suppose that the user jon@ghidora
has been
granted the EVENT
and
INSERT
privileges on the
myschema
schema. This user then creates the
following event:
CREATE EVENT e_insert ON SCHEDULE EVERY 7 SECOND DO INSERT INTO myschema.mytable;
After this event has been created, root
revokes
the EVENT
privilege for
jon@ghidora
. However,
e_insert
continues to execute, inserting a new
row into mytable
each seven seconds. The same
would be true if root
had issued either of
these statements:
DROP USER jon@ghidora;
RENAME USER jon@ghidora TO
someotherguy@ghidora;
You can verify that this is true by examining the
mysql.event
table (discussed later in this
section) or the
INFORMATION_SCHEMA.EVENTS
table (see
Section 19.7, “The INFORMATION_SCHEMA EVENTS
Table”) before and after issuing a
DROP USER
or
RENAME USER
statement.
Event definitions are stored in the mysql.event
table. To drop an event created by another user account, the MySQL
root
user (or another user with the necessary
privileges) can delete rows from this table. For example, to
remove the event e_insert
shown previously,
root
can use the following statement:
DELETE FROM mysql.event WHERE db = 'myschema' AND definer = 'jon@ghidora' AND name = 'e_insert';
It is very important to match the event name, database schema
name, and user account when deleting rows from the
mysql.event
table. This is because the same
user can create different events of the same name in different
schemas.
Users' EVENT
privileges are stored
in the Event_priv
columns of the
mysql.user
and mysql.db
tables. In both cases, this column holds one of the values
'Y
' or 'N
'.
'N
' is the default.
mysql.user.Event_priv
is set to
'Y
' for a given user only if that user has the
global EVENT
privilege (that is, if
the privilege was bestowed using GRANT EVENT ON
*.*
). For a schema-level
EVENT
privilege,
GRANT
creates a row in
mysql.db
and sets that row's
Db
column to the name of the schema, the
User
column to the name of the user, and the
Event_priv
column to 'Y
'.
There should never be any need to manipulate these tables
directly, since the GRANT
EVENT
and REVOKE EVENT
statements
perform the required operations on them.
Five status variables provide counts of event-related operations (but not of statements executed by events; see Section E.1, “Restrictions on Stored Programs”). These are:
Com_create_event
: The number of
CREATE EVENT
statements
executed since the last server restart.
Com_alter_event
: The number of
ALTER EVENT
statements executed
since the last server restart.
Com_drop_event
: The number of
DROP EVENT
statements executed
since the last server restart.
Com_show_create_event
: The number of
SHOW CREATE EVENT
statements
executed since the last server restart.
Com_show_events
: The number of
SHOW EVENTS
statements executed
since the last server restart.
You can view current values for all of these at one time by
running the statement SHOW STATUS LIKE
'%event%';
.
Views (including updatable views) are available in MySQL Server 5.6. Views are stored queries that when invoked produce a result set. A view acts as a virtual table.
To use views if you have upgraded to MySQL 5.6 from an older release that did not support views, you should upgrade your grant tables so that they contain the view-related privileges. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.
The following discussion describes the syntax for creating and dropping views, and shows some examples of how to use them.
You may find the Views User Forum of use when working with views.
For answers to some commonly asked questions regarding views in MySQL, see Section B.6, “MySQL 5.6 FAQ: Views”.
There are some restrictions on the use of views; see Section E.5, “Restrictions on Views”.
The CREATE VIEW
statement creates a
new view (see Section 13.1.16, “CREATE VIEW
Syntax”). To alter the
definition of a view or drop a view, use
ALTER VIEW
(see
Section 13.1.7, “ALTER VIEW
Syntax”), or DROP
VIEW
(see Section 13.1.25, “DROP VIEW
Syntax”).
A view can be created from many kinds of
SELECT
statements. It can refer to
base tables or other views. It can use joins,
UNION
, and subqueries. The
SELECT
need not even refer to any
tables. The following example defines a view that selects two
columns from another table, as well as an expression calculated
from those columns:
mysql>CREATE TABLE t (qty INT, price INT);
mysql>INSERT INTO t VALUES(3, 50), (5, 60);
mysql>CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql>SELECT * FROM v;
+------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | | 5 | 60 | 300 | +------+-------+-------+ mysql>SELECT * FROM v WHERE qty = 5;
+------+-------+-------+ | qty | price | value | +------+-------+-------+ | 5 | 60 | 300 | +------+-------+-------+
The optional ALGORITHM
clause for
CREATE VIEW
or
ALTER VIEW
is a MySQL extension to
standard SQL. It affects how MySQL processes the view.
ALGORITHM
takes three values:
MERGE
, TEMPTABLE
, or
UNDEFINED
. The default algorithm is
UNDEFINED
if no ALGORITHM
clause is present.
For MERGE
, the text of a statement that refers
to the view and the view definition are merged such that parts of
the view definition replace corresponding parts of the statement.
For TEMPTABLE
, the results from the view are
retrieved into a temporary table, which then is used to execute
the statement.
For UNDEFINED
, MySQL chooses which algorithm to
use. It prefers MERGE
over
TEMPTABLE
if possible, because
MERGE
is usually more efficient and because a
view cannot be updatable if a temporary table is used.
A reason to choose TEMPTABLE
explicitly is that
locks can be released on underlying tables after the temporary
table has been created and before it is used to finish processing
the statement. This might result in quicker lock release than the
MERGE
algorithm so that other clients that use
the view are not blocked as long.
A view algorithm can be UNDEFINED
for three
reasons:
No ALGORITHM
clause is present in the
CREATE VIEW
statement.
The CREATE VIEW
statement has
an explicit ALGORITHM = UNDEFINED
clause.
ALGORITHM = MERGE
is specified for a view
that can be processed only with a temporary table. In this
case, MySQL generates a warning and sets the algorithm to
UNDEFINED
.
As mentioned earlier, MERGE
is handled by
merging corresponding parts of a view definition into the
statement that refers to the view. The following examples briefly
illustrate how the MERGE
algorithm works. The
examples assume that there is a view v_merge
that has this definition:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100;
Example 1: Suppose that we issue this statement:
SELECT * FROM v_merge;
MySQL handles the statement as follows:
v_merge
becomes t
*
becomes vc1, vc2
,
which corresponds to c1, c2
The view WHERE
clause is added
The resulting statement to be executed becomes:
SELECT c1, c2 FROM t WHERE c3 > 100;
Example 2: Suppose that we issue this statement:
SELECT * FROM v_merge WHERE vc1 < 100;
This statement is handled similarly to the previous one, except
that vc1 < 100
becomes c1 <
100
and the view WHERE
clause is
added to the statement WHERE
clause using an
AND
connective (and parentheses are
added to make sure the parts of the clause are executed with
correct precedence). The resulting statement to be executed
becomes:
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
Effectively, the statement to be executed has a
WHERE
clause of this form:
WHERE (select WHERE) AND (view WHERE)
If the MERGE
algorithm cannot be used, a
temporary table must be used instead. MERGE
cannot be used if the view contains any of the following
constructs:
Some views are updatable. That is, you can use them in statements
such as UPDATE
,
DELETE
, or
INSERT
to update the contents of
the underlying table. For a view to be updatable, there must be a
one-to-one relationship between the rows in the view and the rows
in the underlying table. There are also certain other constructs
that make a view nonupdatable. To be more specific, a view is not
updatable if it contains any of the following:
Aggregate functions (SUM()
,
MIN()
,
MAX()
,
COUNT()
, and so forth)
DISTINCT
GROUP BY
HAVING
Subquery in the select list
Certain joins (see additional join discussion later in this section)
Nonupdatable view in the FROM
clause
A subquery in the WHERE
clause that refers
to a table in the FROM
clause
Refers only to literal values (in this case, there is no underlying table to update)
Uses ALGORITHM = TEMPTABLE
(use of a
temporary table always makes a view nonupdatable)
Multiple references to any column of a base table.
With respect to insertability (being updatable with
INSERT
statements), an updatable
view is insertable if it also satisfies these additional
requirements for the view columns:
There must be no duplicate view column names.
The view must contain all columns in the base table that do not have a default value.
The view columns must be simple column references and not derived columns. A derived column is one that is not a simple column reference but is derived from an expression. These are examples of derived columns:
3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery
)
A view that has a mix of simple column references and derived columns is not insertable, but it can be updatable if you update only those columns that are not derived. Consider this view:
CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
This view is not insertable because col2
is
derived from an expression. But it is updatable if the update does
not try to update col2
. This update is
permissible:
UPDATE v SET col1 = 0;
This update is not permissible because it attempts to update a derived column:
UPDATE v SET col2 = 0;
It is sometimes possible for a multiple-table view to be
updatable, assuming that it can be processed with the
MERGE
algorithm. For this to work, the view
must use an inner join (not an outer join or a
UNION
). Also, only a single table
in the view definition can be updated, so the
SET
clause must name only columns from one of
the tables in the view. Views that use
UNION ALL
are not
permitted even though they might be theoretically updatable,
because the implementation uses temporary tables to process them.
For a multiple-table updatable view,
INSERT
can work if it inserts into
a single table. DELETE
is not
supported.
INSERT DELAYED
is not supported for
views.
If a table contains an AUTO_INCREMENT
column,
inserting into an insertable view on the table that does not
include the AUTO_INCREMENT
column does not
change the value of
LAST_INSERT_ID()
, because the side
effects of inserting default values into columns not part of the
view should not be visible.
The WITH CHECK OPTION
clause can be given for
an updatable view to prevent inserts or updates to rows except
those for which the WHERE
clause in the
select_statement
is true.
In a WITH CHECK OPTION
clause for an updatable
view, the LOCAL
and CASCADED
keywords determine the scope of check testing when the view is
defined in terms of another view. The LOCAL
keyword restricts the CHECK OPTION
only to the
view being defined. CASCADED
causes the checks
for underlying views to be evaluated as well. When neither keyword
is given, the default is CASCADED
. Consider the
definitions for the following table and set of views:
mysql>CREATE TABLE t1 (a INT);
mysql>CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
->WITH CHECK OPTION;
mysql>CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
->WITH LOCAL CHECK OPTION;
mysql>CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
->WITH CASCADED CHECK OPTION;
Here the v2
and v3
views are
defined in terms of another view, v1
.
v2
has a LOCAL
check option,
so inserts are tested only against the v2
check. v3
has a CASCADED
check option, so inserts are tested not only against its own
check, but against those of underlying views. The following
statements illustrate these differences:
mysql>INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec) mysql>INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'
MySQL sets a flag, called the view updatability flag, at
CREATE VIEW
time. The flag is set
to YES
(true) if
UPDATE
and
DELETE
(and similar operations) are
legal for the view. Otherwise, the flag is set to
NO
(false). The IS_UPDATABLE
column in the
INFORMATION_SCHEMA.VIEWS
table
displays the status of this flag. It means that the server always
knows whether a view is updatable. If the view is not updatable,
statements such UPDATE
,
DELETE
, and
INSERT
are illegal and will be
rejected. (Note that even if a view is updatable, it might not be
possible to insert into it, as described elsewhere in this
section.)
The updatability of views may be affected by the value of the
updatable_views_with_limit
system
variable. See Section 5.1.4, “Server System Variables”.
Metadata about views can be obtained as follows:
Query the VIEWS
table of the
INFORMATION_SCHEMA
database. See
Section 19.29, “The INFORMATION_SCHEMA VIEWS
Table”.
Use the SHOW CREATE VIEW
statement. See Section 13.7.5.14, “SHOW CREATE VIEW
Syntax”.
Stored programs and views are defined prior to use and, when
referenced, execute within a security context that determines
their privileges. These privileges are controlled by their
DEFINER
attribute, and, if there is one, their
SQL SECURITY
characteristic.
All stored programs (procedures, functions, triggers, and events)
and views can have a DEFINER
attribute that
names a MySQL account. If the DEFINER
attribute
is omitted from a stored program or view definition, the default
account is the user who creates the object.
In addition, stored routines (procedures and functions) and views
can have a SQL SECURITY
characteristic with a
value of DEFINER
or INVOKER
to specify whether the object executes in definer or invoker
context. If the SQL SECURITY
characteristic is
omitted, the default is definer context.
Triggers and events have no SQL SECURITY
characteristic and always execute in definer context. The server
invokes these objects automatically as necessary, so there is no
invoking user.
Definer and invoker security contexts differ as follows:
A stored program or view that executes in definer security
context executes with the privileges of the account named by
its DEFINER
attribute. These privileges may
be entirely different from those of the invoking user. The
invoker must have appropriate privileges to reference the
object (for example, EXECUTE
to
call a stored procedure or
SELECT
to select from a view),
but when the object executes, the invoker's privileges are
ignored and only the DEFINER
account
privileges matter. If this account has few privileges, the
object is correspondingly limited in the operations it can
perform. If the DEFINER
account is highly
privileged (such as a root
account), the
object can perform powerful operations no matter who
invokes it.
A stored routine or view that executes in invoker security
context can perform only operations for which the invoker has
privileges. The DEFINER
attribute can be
specified but has no effect for objects that execute in
invoker context.
Consider the following stored procedure:
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1() SQL SECURITY DEFINER BEGIN UPDATE t1 SET counter = counter + 1; END;
Any user who has the EXECUTE
privilege for p1
can invoke it with a
CALL
statement. However, when
p1
executes, it does so in
DEFINER
security context and thus executes with
the privileges of 'admin'@'localhost'
, the
account named in the DEFINER
attribute. This
account must have the EXECUTE
privilege for p1
as well as the
UPDATE
privilege for the table
t1
. Otherwise, the procedure fails.
Now consider this stored procedure, which is identical to
p1
except that its SQL
SECURITY
characteristic is INVOKER
:
CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2() SQL SECURITY INVOKER BEGIN UPDATE t1 SET counter = counter + 1; END;
p2
, unlike p1
, executes in
INVOKER
security context. The
DEFINER
attribute is irrelevant and
p2
executes with the privileges of the invoking
user. p2
fails if the invoker lacks the
EXECUTE
privilege for
p2
or the UPDATE
privilege for the table t1
.
MySQL uses the following rules to control which accounts a user
can specify in an object DEFINER
attribute:
You can specify a DEFINER
value other than
your own account only if you have the
SUPER
privilege.
If you do not have the SUPER
privilege, the only legal user value is your own account,
either specified literally or by using
CURRENT_USER
. You cannot set
the definer to some other account.
To minimize the risk potential for stored program and view creation and use, follow these guidelines:
For a stored routine or view, use SQL SECURITY
INVOKER
in the object definition when possible so
that it can be used only by users with permissions appropriate
for the operations performed by the object.
If you create definer-context stored programs or views while
using an account that has the
SUPER
privilege, specify an
explicit DEFINER
attribute that names an
account possessing only the privileges required for the
operations performed by the object. Specify a highly
privileged DEFINER
account only when
absolutely necessary.
Administrators can prevent users from specifying highly
privileged DEFINER
accounts by not granting
them the SUPER
privilege.
Definer-context objects should be written keeping in mind that they may be able to access data for which the invoking user has no privileges. In some cases, you can prevent reference to these objects by not granting unauthorized users particular privileges:
However, no such control exists for triggers because users do
not reference them directly. A trigger always executes in
DEFINER
context and is activated by access
to the table with which it is associated, even ordinary table
accesses by users with no special privileges. If the
DEFINER
account is highly privileged, the
trigger can perform sensitive or dangerous operations. This
remains true if the SUPER
and
TRIGGER
privileges needed to
create the trigger are revoked from the account of the user
who created it. Administrators should be especially careful
about granting users that combination of privileges.
The binary log contains information about SQL statements that modify database contents. This information is stored in the form of “events” that describe the modifications. The binary log has two important purposes:
For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 16.2, “Replication Implementation”.
Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.3.2, “Using Backups for Recovery”.
However, there are certain binary logging issues that apply with respect to stored programs (stored procedures and functions, triggers, and events), if logging occurs at the statement level:
In some cases, it is possible that a statement will affect different sets of rows on a master and a slave.
Replicated statements executed on a slave are processed by the slave SQL thread, which has full privileges. It is possible for a procedure to follow different execution paths on master and slave servers, so a user can write a routine containing a dangerous statement that will execute only on the slave where it is processed by a thread that has full privileges.
If a stored program that modifies data is nondeterministic, it is not repeatable. This can result in different data on a master and slave, or cause restored data to differ from the original data.
This section describes how MySQL 5.6 handles binary logging for stored programs. It states the current conditions that the implementation places on the use of stored programs, and what you can do to avoid problems. It also provides additional information about the reasons for these conditions.
In general, the issues described here result when binary logging
occurs at the SQL statement level. If you use row-based binary
logging, the log contains changes made to individual rows as a
result of executing SQL statements. When routines or triggers
execute, row changes are logged, not the statements that make the
changes. For stored procedures, this means that the
CALL
statement is not logged. For
stored functions, row changes made within the function are logged,
not the function invocation. For triggers, row changes made by the
trigger are logged. On the slave side, only the row changes are
seen, not the stored program invocation. For general information
about row-based logging, see
Section 16.1.2, “Replication Formats”.
Unless noted otherwise, the remarks here assume that you have
enabled binary logging by starting the server with the
--log-bin
option. (See
Section 5.2.4, “The Binary Log”.) If the binary log is not enabled,
replication is not possible, nor is the binary log available for
data recovery.
The current conditions on the use of stored functions in MySQL 5.6 can be summarized as follows. These conditions do not apply to stored procedures or Event Scheduler events and they do not apply unless binary logging is enabled.
To create or alter a stored function, you must have the
SUPER
privilege, in addition to
the CREATE ROUTINE
or
ALTER ROUTINE
privilege that is
normally required. (Depending on the
DEFINER
value in the function definition,
SUPER
might be required
regardless of whether binary logging is enabled. See
Section 13.1.12, “CREATE PROCEDURE
and
CREATE FUNCTION
Syntax”.)
When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
By default, for a CREATE
FUNCTION
statement to be accepted, at least one of
DETERMINISTIC
, NO SQL
,
or READS SQL DATA
must be specified
explicitly. Otherwise an error occurs:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
This function is deterministic (and does not modify data), so it is safe:
CREATE FUNCTION f1(i INT) RETURNS INT DETERMINISTIC READS SQL DATA BEGIN RETURN i; END;
This function uses UUID()
,
which is not deterministic, so the function also is not
deterministic and is not safe:
CREATE FUNCTION f2() RETURNS CHAR(36) CHARACTER SET utf8 BEGIN RETURN UUID(); END;
This function modifies data, so it may not be safe:
CREATE FUNCTION f3(p_id INT) RETURNS INT BEGIN UPDATE t SET modtime = NOW() WHERE id = p_id; RETURN ROW_COUNT(); END;
Assessment of the nature of a function is based on the
“honesty” of the creator: MySQL does not check
that a function declared DETERMINISTIC
is
free of statements that produce nondeterministic results.
Although it is possible to create a deterministic stored
function without specifying DETERMINISTIC
,
you cannot execute this function using statement-based binary
logging. To execute such a function, you must use row-based or
mixed binary logging. Alternatively, if you explicitly specify
DETERMINISTIC
in the function definition,
you can use any kind of logging, including statement-based
logging.
To relax the preceding conditions on function creation (that
you must have the SUPER
privilege and that a function must be declared deterministic
or to not modify data), set the global
log_bin_trust_function_creators
system variable to 1. By default, this variable has a value of
0, but you can change it like this:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
--log-bin-trust-function-creators=1
option when starting the server.
If binary logging is not enabled,
log_bin_trust_function_creators
does not apply. SUPER
is not
required for function creation unless, as described
previously, the DEFINER
value in the
function definition requires it.
For information about built-in functions that may be unsafe for replication (and thus cause stored functions that use them to be unsafe as well), see Section 16.4.1, “Replication Features and Issues”.
Triggers are similar to stored functions, so the preceding remarks
regarding functions also apply to triggers with the following
exception: CREATE TRIGGER
does not
have an optional DETERMINISTIC
characteristic,
so triggers are assumed to be always deterministic. However, this
assumption might in some cases be invalid. For example, the
UUID()
function is nondeterministic
(and does not replicate). You should be careful about using such
functions in triggers.
Triggers can update tables, so error messages similar to those for
stored functions occur with CREATE
TRIGGER
if you do not have the required privileges. On
the slave side, the slave uses the trigger
DEFINER
attribute to determine which user is
considered to be the creator of the trigger.
The rest of this section provides additional detail about the
logging implementation and its implications. You need not read it
unless you are interested in the background on the rationale for
the current logging-related conditions on stored routine use. This
discussion applies only for statement-based logging, and not for
row-based logging, with the exception of the first item:
CREATE
and DROP
statements
are logged as statements regardless of the logging mode.
The server writes CREATE EVENT
,
CREATE PROCEDURE
,
CREATE FUNCTION
,
ALTER EVENT
,
ALTER PROCEDURE
,
ALTER FUNCTION
,
DROP EVENT
,
DROP PROCEDURE
, and
DROP FUNCTION
statements to the
binary log.
A stored function invocation is logged as a
SELECT
statement if the
function changes data and occurs within a statement that would
not otherwise be logged. This prevents nonreplication of data
changes that result from use of stored functions in nonlogged
statements. For example, SELECT
statements are not written to the binary log, but a
SELECT
might invoke a stored
function that makes changes. To handle this, a SELECT
statement is
written to the binary log when the given function makes a
change. Suppose that the following statements are executed on
the master:
func_name
()
CREATE FUNCTION f1(a INT) RETURNS INT BEGIN IF (a < 3) THEN INSERT INTO t2 VALUES (a); END IF; RETURN 0; END; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); SELECT f1(a) FROM t1;
When the SELECT
statement
executes, the function f1()
is invoked
three times. Two of those invocations insert a row, and MySQL
logs a SELECT
statement for
each of them. That is, MySQL writes the following statements
to the binary log:
SELECT f1(1); SELECT f1(2);
The server also logs a SELECT
statement for a stored function invocation when the function
invokes a stored procedure that causes an error. In this case,
the server writes the SELECT
statement to the log along with the expected error code. On
the slave, if the same error occurs, that is the expected
result and replication continues. Otherwise, replication
stops.
Logging stored function invocations rather than the statements executed by a function has a security implication for replication, which arises from two factors:
It is possible for a function to follow different execution paths on master and slave servers.
Statements executed on a slave are processed by the slave SQL thread which has full privileges.
The implication is that although a user must have the
CREATE ROUTINE
privilege to
create a function, the user can write a function containing a
dangerous statement that will execute only on the slave where
it is processed by a thread that has full privileges. For
example, if the master and slave servers have server ID values
of 1 and 2, respectively, a user on the master server could
create and invoke an unsafe function
unsafe_func()
as follows:
mysql>delimiter //
mysql>CREATE FUNCTION unsafe_func () RETURNS INT
->BEGIN
->IF @@server_id=2 THEN
->dangerous_statement
; END IF;RETURN 1;
->END;
->//
mysql>delimiter ;
mysql>INSERT INTO t VALUES(unsafe_func());
The CREATE FUNCTION
and
INSERT
statements are written
to the binary log, so the slave will execute them. Because the
slave SQL thread has full privileges, it will execute the
dangerous statement. Thus, the function invocation has
different effects on the master and slave and is not
replication-safe.
To guard against this danger for servers that have binary
logging enabled, stored function creators must have the
SUPER
privilege, in addition to
the usual CREATE ROUTINE
privilege that is required. Similarly, to use
ALTER FUNCTION
, you must have
the SUPER
privilege in addition
to the ALTER ROUTINE
privilege.
Without the SUPER
privilege, an
error will occur:
ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
If you do not want to require function creators to have the
SUPER
privilege (for example,
if all users with the CREATE
ROUTINE
privilege on your system are experienced
application developers), set the global
log_bin_trust_function_creators
system variable to 1. You can also set this variable by using
the
--log-bin-trust-function-creators=1
option when starting the server. If binary logging is not
enabled,
log_bin_trust_function_creators
does not apply. SUPER
is not
required for function creation unless, as described
previously, the DEFINER
value in the
function definition requires it.
If a function that performs updates is nondeterministic, it is not repeatable. This can have two undesirable effects:
It will make a slave different from the master.
Restored data will be different from the original data.
To deal with these problems, MySQL enforces the following requirement: On a master server, creation and alteration of a function is refused unless you declare the function to be deterministic or to not modify data. Two sets of function characteristics apply here:
The DETERMINISTIC
and NOT
DETERMINISTIC
characteristics indicate whether a
function always produces the same result for given inputs.
The default is NOT DETERMINISTIC
if
neither characteristic is given. To declare that a
function is deterministic, you must specify
DETERMINISTIC
explicitly.
The CONTAINS SQL
, NO
SQL
, READS SQL DATA
, and
MODIFIES SQL DATA
characteristics
provide information about whether the function reads or
writes data. Either NO SQL
or
READS SQL DATA
indicates that a
function does not change data, but you must specify one of
these explicitly because the default is CONTAINS
SQL
if no characteristic is given.
By default, for a CREATE
FUNCTION
statement to be accepted, at least one of
DETERMINISTIC
, NO SQL
,
or READS SQL DATA
must be specified
explicitly. Otherwise an error occurs:
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
If you set
log_bin_trust_function_creators
to 1, the requirement that functions be deterministic or not
modify data is dropped.
Stored procedure calls are logged at the statement level
rather than at the CALL
level.
That is, the server does not log the
CALL
statement, it logs those
statements within the procedure that actually execute. As a
result, the same changes that occur on the master will be
observed on slave servers. This prevents problems that could
result from a procedure having different execution paths on
different machines.
In general, statements executed within a stored procedure are written to the binary log using the same rules that would apply were the statements to be executed in standalone fashion. Some special care is taken when logging procedure statements because statement execution within procedures is not quite the same as in nonprocedure context:
A statement to be logged might contain references to local procedure variables. These variables do not exist outside of stored procedure context, so a statement that refers to such a variable cannot be logged literally. Instead, each reference to a local variable is replaced by this construct for logging purposes:
NAME_CONST(var_name
,var_value
)
var_name
is the local variable
name, and var_value
is a
constant indicating the value that the variable has at the
time the statement is logged.
NAME_CONST()
has a value of
var_value
, and a
“name” of
var_name
. Thus, if you invoke
this function directly, you get a result like this:
mysql> SELECT NAME_CONST('myname', 14);
+--------+
| myname |
+--------+
| 14 |
+--------+
NAME_CONST()
enables a
logged standalone statement to be executed on a slave with
the same effect as the original statement that was
executed on the master within a stored procedure.
The use of NAME_CONST()
can
result in a problem for
CREATE TABLE
... SELECT
statements when the source column
expressions refer to local variables. Converting these
references to NAME_CONST()
expressions can result in column names that are different
on the master and slave servers, or names that are too
long to be legal column identifiers. A workaround is to
supply aliases for columns that refer to local variables.
Consider this statement when myvar
has
a value of 1:
CREATE TABLE t1 SELECT myvar;
That will be rewritten as follows:
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
To ensure that the master and slave tables have the same column names, write the statement like this:
CREATE TABLE t1 SELECT myvar AS myvar;
The rewritten statement becomes:
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
A statement to be logged might contain references to
user-defined variables. To handle this, MySQL writes a
SET
statement to the binary log to make sure that the variable
exists on the slave with the same value as on the master.
For example, if a statement refers to a variable
@my_var
, that statement will be
preceded in the binary log by the following statement,
where value
is the value of
@my_var
on the master:
SET @my_var = value
;
Procedure calls can occur within a committed or
rolled-back transaction. Transactional context is
accounted for so that the transactional aspects of
procedure execution are replicated correctly. That is, the
server logs those statements within the procedure that
actually execute and modify data, and also logs
BEGIN
,
COMMIT
, and
ROLLBACK
statements as necessary. For example, if a procedure
updates only transactional tables and is executed within a
transaction that is rolled back, those updates are not
logged. If the procedure occurs within a committed
transaction,
BEGIN
and COMMIT
statements are
logged with the updates. For a procedure that executes
within a rolled-back transaction, its statements are
logged using the same rules that would apply if the
statements were executed in standalone fashion:
Updates to transactional tables are not logged.
Updates to nontransactional tables are logged because rollback does not cancel them.
Updates to a mix of transactional and nontransactional
tables are logged surrounded by
BEGIN
and
ROLLBACK
so that slaves will make the same changes and
rollbacks as on the master.
A stored procedure call is not written to
the binary log at the statement level if the procedure is
invoked from within a stored function. In that case, the only
thing logged is the statement that invokes the function (if it
occurs within a statement that is logged) or a
DO
statement (if it occurs
within a statement that is not logged). For this reason, care
should be exercised in the use of stored functions that invoke
a procedure, even if the procedure is otherwise safe in
itself.