(mysql.info) stored-procedure-logging
Info Catalog
(mysql.info) stored-procedure-replication-faq
(mysql.info) stored-procedures
17.4 Binary Logging of Stored Routines and Triggers
===================================================
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 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
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 backup-recovery.
This section describes the development of binary logging in MySQL 5.0
with respect to stored routines (procedures and functions) and
triggers. The discussion first summarizes the changes that have taken
place in the logging implementation, and then states the current
conditions that the implementation places on the use of stored
routines. Finally, implementation details are given that provide
information about when and why various changes were made. These
details show how several aspects of the current logging behavior were
implemented in response to shortcomings identified in earlier versions.
In general, the issues described here result from the fact that binary
logging occurs at the SQL statement level. A future MySQL release is
expected to implement row-level binary logging, which specifies the
changes to make to individual rows as a result of executing SQL
statements.
Unless noted otherwise, the remarks here assume that you have enabled
binary logging by starting the server with the -log-bin option. (See
binary-log.) If the binary log is not enabled, replication is
not possible, nor is the binary log available for data recovery.
The development of stored routine logging in MySQL 5.0 can be
summarized as follows:
* Before MySQL 5.0.6: In the initial implementation of stored
routine logging, statements that create stored routines and `CALL'
statements are not logged. These omissions can cause problems for
replication and data recovery.
* MySQL 5.0.6: Statements that create stored routines and `CALL'
statements are logged. Stored function invocations are logged when
they occur in statements that update data (because those
statements are logged). However, function invocations are not
logged when they occur in statements such as `SELECT' that do not
change data, even if a data change occurs within a function
itself; this can cause problems. Under some circumstances,
functions and procedures can have different effects if executed at
different times or on different (master and slave) machines, and
thus can be unsafe for data recovery or replication. To handle
this, measures are implemented to allow identification of safe
routines and to prevent creation of unsafe routines except by
users with sufficient privileges.
* MySQL 5.0.12: For stored functions, when a function invocation
that changes data occurs within a non-logged statement such as
`SELECT', the server logs a `DO FUNC_NAME()' statement that
invokes the function so that the function gets executed during
data recovery or replication to slave servers. For stored
procedures, the server does not log `CALL' statements. Instead, it
logs individual statements within a procedure that are executed as
a result of a `CALL'. This eliminates problems that may occur when
a procedure would follow a different execution path on a slave
than on the master.
* MySQL 5.0.16: The procedure logging changes made in 5.0.12 allow
the conditions on unsafe routines to be relaxed for stored
procedures. Consequently, the user interface for controlling these
conditions is revised to apply only to functions. Procedure
creators are no longer bound by them.
* MySQL 5.0.17: Logging of stored functions as `DO FUNC_NAME()'
statements (per the changes made in 5.0.12) are logged as `SELECT
FUNC_NAME()' statements instead for better control over error
checking.
As a consequence of the preceding changes, the following conditions
currently apply to stored function creation when binary logging is
enabled. These conditions do not apply to stored procedure creation.
* 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.
* 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. 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, so you must specify
`DETERMINISTIC' explicitly to declare that a function is
deterministic.
Use of the `NOW()' function (or its synonyms) or `RAND()'
does not necessarily make a function non-deterministic. For
`NOW()', the binary log includes the timestamp and replicates
correctly. `RAND()' also replicates correctly as long as it
is invoked only once within a function. (You can consider the
function execution timestamp and random number seed as
implicit inputs that are identical on the master and slave.)
* 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,
`DETERMINISTIC' or one of `NO SQL' and `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)
Assessment of the nature of a function is based on the `honesty'
of the creator: MySQL does not check that a function declared
`DETERMINISTIC' contains no statements that produce
non-deterministic results.
* 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 option when starting the server.
If binary logging is not enabled,
`log_bin_trust_function_creators' does not apply and `SUPER' is
not required for routine creation.
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 non-deterministic (and does not replicate).
You should be careful about using such functions in triggers.
Triggers can update tables (as of MySQL 5.0.10), so error messages
similar to those for stored functions occur with `CREATE TRIGGER' if
you do not have the `SUPER' privilege and
`log_bin_trust_function_creators' is 0.
The rest of this section provides details on the development of stored
routine logging. Some of these details give additional background on
the rationale for the current logging-related conditions on stored
routine use.
*Routine logging before MySQL 5.0.6:* Statements that create and use
stored routines are not written to the binary log, but statements
invoked within stored routines are logged. Suppose that you issue the
following statements:
CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;
For this example, only the `INSERT' statement appears in the binary
log. The `CREATE PROCEDURE' and `CALL' statements do not appear. The
absence of routine-related statements in the binary log means that
stored routines are not replicated correctly. It also means that for a
data recovery operation, re-executing events in the binary log does not
recover stored routines.
*Routine logging changes in MySQL 5.0.6:* To address the absence of
logging for stored routine creation and `CALL' statements (and the
consequent replication and data recovery concerns), the characteristics
of binary logging for stored routines were changed as described here.
(Some of the items in the following list point out issues that are
dealt with in later versions.)
* The server writes `CREATE PROCEDURE', `CREATE FUNCTION', `ALTER
PROCEDURE', `ALTER FUNCTION', `DROP PROCEDURE', and `DROP
FUNCTION' statements to the binary log. Also, the server logs
`CALL' statements, not the statements executed within procedures.
Suppose that you issue the following statements:
CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
CALL mysp;
For this example, the `CREATE PROCEDURE' and `CALL' statements
appear in the binary log, but the `INSERT' statement does not
appear. This corrects the problem that occurred before MySQL 5.0.6
such that only the `INSERT' was logged.
* Logging `CALL' statements has a security implication for
replication, which arises from two factors:
* It is possible for a procedure 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 routine, the user can write a
routine containing a dangerous statement that will execute only on
the slave where the statement is processed by the SQL 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 procedure
`unsafe_sp()' as follows:
mysql> delimiter //
mysql> CREATE PROCEDURE unsafe_sp ()
-> BEGIN
-> IF @@server_id=2 THEN DROP DATABASE accounting; END IF;
-> END;
-> //
mysql> delimiter ;
mysql> CALL unsafe_sp();
The `CREATE PROCEDURE' and `CALL' 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 `DROP DATABASE'
statement that drops the `accounting' database. Thus, the `CALL'
statement has different effects on the master and slave and is not
replication-safe.
The preceding example uses a stored procedure, but similar
problems can occur for stored functions that are invoked within
statements that are written to the binary log: Function invocation
has different effects on the master and slave.
To guard against this danger for servers that have binary logging
enabled, MySQL 5.0.6 introduces the requirement that stored
procedure and function creators must have the `SUPER' privilege,
in addition to the usual `CREATE ROUTINE' privilege that is
required. Similarly, to use `ALTER PROCEDURE' or `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_routine_creators variable)
If you do not want to require routine 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_routine_creators' system variable to
1. You can also set this variable by using the
-log-bin-trust-routine-creators option when starting the server.
If binary logging is not enabled, `log_bin_trust_routine_creators'
does not apply and `SUPER' is not required for routine creation.
* If a routine that performs updates is non-deterministic, 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
routine is refused unless you declare the routine to be
deterministic or to not modify data. Two sets of routine
characteristics apply here:
* The `DETERMINISTIC' and `NOT DETERMINISTIC' characteristics
indicate whether a routine always produces the same result
for given inputs. The default is `NOT DETERMINISTIC' if
neither characteristic is given, so you must specify
`DETERMINISTIC' explicitly to declare that a routine is
deterministic.
* The `CONTAINS SQL', `NO SQL', `READS SQL DATA', and `MODIFIES
SQL DATA' characteristics provide information about whether
the routine reads or writes data. Either `NO SQL' or `READS
SQL DATA' indicates that a routine 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 PROCEDURE' or `CREATE FUNCTION'
statement to be accepted, `DETERMINISTIC' or one of `NO SQL' and
`READS SQL DATA' must be specified explicitly. Otherwise an error
occurs:
ERROR 1418 (HY000): This routine 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_routine_creators
variable)
If you set `log_bin_trust_routine_creators' to 1, the requirement
that routines be deterministic or not modify data is dropped.
* A `CALL' statement is written to the binary log if the routine
returns no error, but not otherwise. When a routine that modifies
data fails, you get this warning:
ERROR 1417 (HY000): A routine failed and has neither NO SQL nor
READS SQL DATA in its declaration and binary logging is enabled; if
non-transactional tables were updated, the binary log will miss their
changes
This logging behavior has the potential to cause problems. If a
routine partly modifies a non-transactional table (such as a
`MyISAM' table) and returns an error, the binary log will not
reflect these changes. To protect against this, you should use
transactional tables in the routine and modify the tables within
transactions.
If you use the `IGNORE' keyword with `INSERT', `DELETE', or
`UPDATE' to ignore errors within a routine, a partial update might
occur but no error will result. Such statements are logged and
they replicate normally.
* Although statements normally are not written to the binary log if
they are rolled back, `CALL' statements are logged even when they
occur within a rolled-back transaction. This can result in a `CALL'
being rolled back on the master but executed on slaves.
* If a stored function is invoked within a statement such as
`SELECT' that does not modify data, execution of the function is
not written to the binary log, even if the function itself
modifies data. This logging behavior has the potential to cause
problems. Suppose that a function `myfunc()' is defined as follows:
CREATE FUNCTION myfunc () RETURNS INT DETERMINISTIC
BEGIN
INSERT INTO t (i) VALUES(1);
RETURN 0;
END;
Given that definition, the following statement is not written to
the binary log because it is a `SELECT'. Nevertheless, it
modifies the table `t' because `myfunc()' modifies `t':
SELECT myfunc();
A workaround for this problem is to invoke functions that do
updates only within statements that do updates (and which
therefore are written to the binary log). Note that although the
`DO' statement sometimes is executed for the side effect of
evaluating an expression, `DO' is not a workaround here because it
is not written to the binary log.
* On slave servers, -replicate-*-table rules do not apply to `CALL'
statements or to statements within stored routines. These
statements are always replicated. If such statements contain
references to tables that do not exist on the slave, they could
have undesirable effects when executed on the slave.
*Routine logging changes in MySQL 5.0.12:* The changes in 5.0.12
address several problems that were present in earlier versions:
* Stored function invocations in non-logged statements such as
`SELECT' were not being logged, even when a function itself
changed data.
* Stored procedure logging at the `CALL' level could cause different
effects on a master and slave if a procedure took different
execution paths on the two machines.
* `CALL' statements were logged even when they occurred within a
rolled-back transaction.
To deal with these issues, MySQL 5.0.12 implements the following
changes to function and procedure logging:
* A stored function invocation is logged as a `DO' statement if the
function changes data and occurs within a statement that would not
otherwise be logged. This corrects the problem of non-replication
of data changes that result from use of stored functions in
non-logged 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 `DO 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:
CREATE FUNCTION f1(a INT) RETURNS INT
BEGIN
IF (a < 3) THEN
INSERT INTO t2 VALUES (a);
END IF;
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 `DO' statement for each of them. That is, MySQL
writes the following statements to the binary log:
DO f1(1);
DO f1(2);
The server also logs a `DO' statement for a stored function
invocation when the function invokes a stored procedure that
causes an error. In this case, the server writes the `DO'
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.
Note: See later in this section for changes made in MySQL 5.0.19:
These logged `DO FUNC_NAME()' statements are logged as `SELECT
FUNC_NAME()' statements instead.
* 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 eliminates the
problems that could result from a procedure having different
execution paths on different machines. For example, the `DROP
DATABASE' problem shown earlier for the `unsafe_sp()' procedure
does not occur and the routine is no longer replication-unsafe
because it has the same effect on master and slave servers.
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
non-procedure 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()' allows 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.
* 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. Previously, `CALL' statements were logged even if
they occurred within a rolled-back transaction. As of MySQL
5.0.12, 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 non-transactional tables are logged because
rollback does not cancel them.
* Updates to a mix of transactional and non-transactional
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 still should be exercised in
the use of stored functions that invoke a procedure, even if the
procedure is otherwise safe in itself.
* Because procedure logging occurs at the statement level rather
than at the `CALL' level, interpretation of the -replicate-*-table
options is revised to apply only to stored functions. They no
longer apply to stored procedures, except those procedures that
are invoked from within functions.
*Routine logging changes in MySQL 5.0.16:* In 5.0.12, a change was
introduced to log stored procedure calls at the statement level rather
than at the `CALL' level. This change eliminates the requirement that
procedures be identified as safe. The requirement now exists only for
stored functions, because they still appear in the binary log as
function invocations rather than as the statements executed within the
function. To reflect the lifting of the restriction on stored
procedures, the `log_bin_trust_routine_creators' system variable is
renamed to `log_bin_trust_function_creators' and the
-log-bin-trust-routine-creators server option is renamed to
-log-bin-trust-function-creators. (For backward compatibility, the old
names are recognized but result in a warning.) Error messages that now
apply only to functions and not to routines in general are re-worded.
*Routine logging changes in MySQL 5.0.19:* In 5.0.12, a change was
introduced to log a stored function invocation as `DO FUNC_NAME()' if
the invocation changes data and occurs within a non-logged statement,
or if the function invokes a stored procedure that produces an error. In
5.0.19, these invocations are logged as `SELECT FUNC_NAME()' instead.
The change to `SELECT' was made because use of `DO' was found to yield
insufficient control over error code checking.
Info Catalog
(mysql.info) stored-procedure-replication-faq
(mysql.info) stored-procedures
automatically generated byinfo2html