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:

23.1 Defining 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:

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.

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:

23.2 Using Stored Routines (Procedures and Functions)

MySQL supports stored routines (procedures and functions). 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 installation procedure. If you are upgrading to MySQL
5.7 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.

A stored procedure or function is associated with a particular
database. This has several implications:

When the routine is invoked, an implicit USE
db_name is performed (and
undone when the routine terminates).
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.

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 27.8.7.54, “mysql_real_connect()”, and
Section 27.8.16, “C API Multiple Statement Execution Support”.

23.2.2 Stored Routines and MySQL Privileges

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.

23.2.4 Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()

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.

23.3 Using Triggers

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 a statement inserts, updates,
or deletes rows in the associated table. These row operations are
trigger events. For example, rows can be inserted by
INSERT or LOAD
DATA statements, and an insert trigger activates for each
inserted row. A trigger can be set to activate either before or
after the trigger event. For example, you can have a trigger
activate before each row that is inserted into a table or after each
row that is updated.

Important

MySQL triggers activate only for changes made to tables by SQL
statements. This includes changes to base tables that underlie
updatable views. Triggers do not activate for changes to tables
made by APIs that do not transmit SQL statements to the MySQL
Server. This means that triggers are not activated by updates made
using the NDB API.

Triggers are not activated by changes in
INFORMATION_SCHEMA or
performance_schema tables. Those tables are
actually views and triggers are not permitted on views.

The following sections describe the syntax for creating and dropping
triggers, show some examples of how to use them, and indicate how to
obtain trigger metadata.

23.3.1 Trigger Syntax and Examples

Here is a simple example that associates a trigger with a table,
to activate for INSERT operations.
The trigger acts as an accumulator, summing the values inserted
into one of the columns of the table.

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 action time, the
triggering event, and what to do when the trigger activates:

The keyword BEFORE indicates the trigger
action time. In this case, the trigger activates before each
row inserted into the table. The other permitted keyword here
is AFTER.

The keyword INSERT indicates the trigger
event; that is, the type of operation that activates the
trigger. In the example, INSERT
operations cause trigger activation. You can also create
triggers for DELETE and
UPDATE operations.

The statement following FOR EACH ROW
defines the trigger body; that is, the statement to execute
each time the trigger activates, which occurs once for each
row affected by the triggering event. In the example, the
trigger body is a simple
SET
that accumulates into a user variable 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:

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;

If you drop a table, any triggers for the table are also dropped.

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.

As of MySQL 5.7.2, it is possible to define multiple triggers for
a given table that have the same trigger event and action time.
For example, you can have two BEFORE UPDATE
triggers for a table. By default, triggers that have the same
trigger event and action time activate in the order they were
created. To affect trigger order, specify a clause after
FOR EACH ROW that indicates
FOLLOWS or PRECEDES and the
name of an existing trigger that also has the same trigger event
and action time. With FOLLOWS, the new trigger
activates after the existing trigger. With
PRECEDES, the new trigger activates before the
existing trigger.

For example, the following trigger definition defines another
BEFORE INSERT trigger for the
account table:

This trigger, ins_transaction, is similar to
ins_sum but accumulates deposits and
withdrawals separately. It has a PRECEDES
clause that causes it to activate before
ins_sum; without that clause, it would activate
after ins_sum because it is created after
ins_sum.

Before MySQL 5.7.2, there cannot be multiple triggers for a given
table that have the same trigger event and action time. For
example, you cannot have two BEFORE UPDATE
triggers for a table. To work around this, you can 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.)

Within the trigger body, the OLD and
NEW keywords enable you to access columns in
the rows affected by a trigger. OLD and
NEW are MySQL extensions to triggers; they are
not case sensitive.

In an INSERT trigger, only
NEW.col_name can be
used; there is no old row. In a DELETE trigger,
only OLD.col_name
can be used; there is no new row. In an UPDATE
trigger, you can use
OLD.col_name to
refer to the columns of a row before it is updated and
NEW.col_name to
refer to the columns of the row after it is updated.

A column named with OLD is read only. You can
refer to it (if you have the SELECT
privilege), but not modify it. You can refer to a column named
with NEW if you have the
SELECT privilege for it. In a
BEFORE trigger, you can also change its value
with SET NEW.col_name =
value if you have the
UPDATE privilege for it. This means
you can use a trigger to modify the values to be inserted into a
new row or used to update a row. (Such a SET
statement has no effect in an AFTER trigger
because the row change will have already occurred.)

In a BEFORE trigger, the NEW
value for an AUTO_INCREMENT column is 0, not
the sequence number that is generated automatically when the new
row actually is inserted.

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
must be checked before it is used to update the row:

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 execute the same code from within
several triggers.

There are 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.)

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 any
BEFORE triggers and the row operation
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.

Triggers can contain direct references to tables by name, such as
the trigger named testref shown in this
example:

23.4 Using the Event Scheduler

The MySQL Event Scheduler
manages the scheduling and execution of events, that is, tasks that
run according to a schedule. The following discussion covers the
Event Scheduler and is divided into the following sections:

Stored routines require the event table in the
mysql database. This table is created during the
MySQL 5.7 installation procedure. If you are upgrading
to MySQL 5.7 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”.

23.4.1 Event Scheduler Overview

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 23.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, 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, “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 23.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 23.4.6, “The Event Scheduler and MySQL Privileges”.

23.4.2 Event Scheduler Configuration

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.

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:

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

Important

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.

Note

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).

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.

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
var_list statements, if the
query returns no rows, a warning with error code 1329 occurs
(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.

23.4.6 The Event Scheduler and MySQL Privileges

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
(hostname.err),
you can see that the event is executing, but the action it is
attempting to perform fails:

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;

Important

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:

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.

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:

23.5.2 View Processing Algorithms

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.

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 specify 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.

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:

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:

23.5.3 Updatable and Insertable Views

Some views are updatable and references to them can be used to
specify tables to be updated in data change statements. That is,
you can use them in statements such as
UPDATE,
DELETE, or
INSERT to update the contents of
the underlying table. Derived tables can also be specified in
multiple-table UPDATE and
DELETE statements, but can only be
used for reading data to specify rows to be updated or deleted.
Generally, the view references must be updatable, meaning that
they may be merged and not materialized. Composite views have more
complex rules.

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:

Before MySQL 5.7.11, subqueries in the select list fail for
INSERT, but are okay for
UPDATE,
DELETE. As of MySQL 5.7.11,
that is still true for nondependent subqueries. For dependent
subqueries in the select list, no data change statements are
permitted.

Certain joins (see additional join discussion later in this
section)

Reference to nonupdatable view in the FROM
clause

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)

Multiple references to any column of a base table (fails for
INSERT, okay for
UPDATE,
DELETE)

A generated column in a view is considered updatable because it is
possible to assign to it. However, if such a column is updated
explicitly, the only permitted value is
DEFAULT. For information about generated
columns, see Section 13.1.18.8, “CREATE TABLE and Generated Columns”.

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.

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. They must
not be expressions, such as these:

3.14159
col1 + 3
UPPER(col2)
col3 / col4
(subquery)

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.

If a view is not updatable, statements such
UPDATE,
DELETE, and
INSERT are illegal and are
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 IS_UPDATABLE flag may be unreliable if a
view depends on one or more other views, and one of these
underlying views is updated. Regardless of the
IS_UPDATABLE value, the server keeps track of
the updatability of a view and correctly rejects data change
operations to views that are not updatable. If the
IS_UPDATABLE value for a view has become
inaccurate to due to changes to underlying views, the value can be
updated by deleting and recreating the view.

INSERT: The insert table of an
INSERT statement may be a view
reference that is merged. If the view is a join view, all
components of the view must be updatable (not materialized).
For a multiple-table updatable view,
INSERT can work if it inserts
into a single table.

This statement is invalid because one component of the join
view is nonupdatable:

INSERT INTO vjoin (c) VALUES (1);

This statement is valid; the view contains no materialized
components:

INSERT INTO vup (c) VALUES (1);

UPDATE: The table or tables to
be updated in an UPDATE
statement may be view references that are merged. If a view is
a join view, at least one component of the view must be
updatable (this differs from
INSERT).

In a multiple-table UPDATE
statement, the updated table references of the statement must
be base tables or updatable view references. Nonupdated table
references may be materialized views or derived tables.

This statement is valid; column c is from
the updatable part of the join view:

UPDATE vjoin SET c=c+1;

This statement is invalid; column x is from
the nonupdatable part:

UPDATE vjoin SET x=x+1;

This statement is valid; the updated table reference of the
multiple-table UPDATE is an
updatable view (vup):

UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;

This statement is invalid; it tries to update a materialized
derived table:

UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;

DELETE: The table or tables to
be deleted from in a DELETE
statement must be merged views. Join views are not allowed
(this differs from INSERT and
UPDATE).

This statement is invalid because the view is a join view:

DELETE vjoin WHERE ...;

This statement is valid because the view is a merged
(updatable) view:

DELETE vup WHERE ...;

This statement is valid because it deletes from a merged
(updatable) view:

DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...;

Additional discussion and examples follow.

Earlier discussion in this section pointed out that a view is not
insertable if not all columns are simple column references (for
example, if it contains columns that are expressions or composite
expressions). Although such a view is not insertable, it can be
updatable if you update only columns that are not expressions.
Consider this view:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

This view is not insertable because col2 is 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 an
expression column:

UPDATE v SET col2 = 0;

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.

23.5.4 The View WITH CHECK OPTION Clause

The WITH CHECK OPTION clause can be given for
an updatable view to prevent inserts to rows for which the
WHERE clause in the
select_statement is not true. It also
prevents updates to rows for which the WHERE
clause is true but the update would cause it to be not true (in
other words, it prevents visible rows from being updated to
nonvisible rows).

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. When neither keyword is given,
the default is CASCADED.

Before MySQL 5.7.6, WITH CHECK OPTION testing
works like this:

With LOCAL, the view
WHERE clause is checked, but no underlying
views are checked.

With CASCADED, the view
WHERE clause is checked, then checking
recurses to underlying views, adds WITH CASCADED
CHECK OPTION to them (for purposes of the check;
their definitions remain unchanged), and applies the same
rules.

With no check option, the view WHERE clause
is not checked, and no underlying views are checked.

As of MySQL 5.7.6, WITH CHECK OPTION testing is
standard-compliant (with changed semantics from previously for
LOCAL and no check clause):

With LOCAL, the view
WHERE clause is checked, then checking
recurses to underlying views and applies the same rules.

With CASCADED, the view
WHERE clause is checked, then checking
recurses to underlying views, adds WITH CASCADED
CHECK OPTION to them (for purposes of the check;
their definitions remain unchanged), and applies the same
rules.

With no check option, the view WHERE clause
is not checked, then checking recurses to underlying views,
and applies the same rules.

Consider the definitions for the following table and set of views:

CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
WITH CHECK OPTION;
CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
WITH LOCAL CHECK OPTION;
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. Before
MySQL 5.7.6, because v2 has a
LOCAL check option, inserts are tested only
against the v2 check. v3 has
a CASCADED check option, so inserts are tested
not only against the v3 check, but against
those of underlying views. The following statements illustrate
these differences:

As of MySQL 5.7.6, the semantics for LOCAL
differ from previously: Inserts for v2 are
checked against its LOCAL check option, then
(unlike before 5.7.6), the check recurses to v1
and the rules are applied again. The rules for
v1 cause a check failure. The check for
v3 fails as before:

23.6 Access Control for Stored Programs and Views

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 an 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.

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:

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:

A stored procedure or function cannot be referenced by a
user who does not have the
EXECUTE privilege for it.

A view cannot be referenced by a user who does not have
the appropriate privilege for it
(SELECT to select from it,
INSERT to insert into it,
and so forth).

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.

23.7 Binary Logging of Stored Programs

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, if logging occurs at the statement level, there are
certain binary logging issues with respect to stored programs
(stored procedures and functions, triggers, and events):

In some cases, a statement might affect different sets of rows
on master and 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 master
and slave, or cause restored data to differ from the original
data.

This section describes how MySQL 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
logging 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.2.1, “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.4.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 conditions on the use of stored functions in MySQL 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.

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:

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:

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.

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 be invalid in some cases. For example, the
UUID() function is nondeterministic
(and does not replicate). 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.

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
func_name() statement is
written to the binary log when the given function makes a
change. Suppose that the following statements are executed on
the master:

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:

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 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)

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:

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.