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. (Binary log events differ from scheduled event stored objects.) The binary log has two important purposes:
For replication, the binary log is used on source replication servers as a record of the statements to be sent to replica servers. The source sends the events contained in its binary log to its replicas, which execute those events to make the same data changes that were made on the source. See *note 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 *note recovery-from-backups::.
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 source and replica.
Replicated statements executed on a replica are processed by the replica SQL thread, which has full privileges. It is possible for a procedure to follow different execution paths on source and replica servers, so a user can write a routine containing a dangerous statement that executes only on the replica 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 source and replica, 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.
Unless noted otherwise, the remarks here assume that binary logging is enabled on the server (see *note binary-log::.) If the binary log is not enabled, replication is not possible, nor is the binary log available for data recovery. In MySQL 5.7, binary logging is not enabled by default, and you enable it using the '--log-bin' option.
In general, the issues described here result when binary logging occurs at the SQL statement level (statement-based binary logging). 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 *note 'CALL': 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 replica side, only the row changes are seen, not the stored program invocation.
Mixed format binary logging ('binlog_format=MIXED') uses statement-based binary logging, except for cases where only row-based binary logging is guaranteed to lead to proper results. With mixed format, when a stored function, stored procedure, trigger, event, or prepared statement contains anything that is not safe for statement-based binary logging, the entire statement is marked as unsafe and logged in row format. The statements used to create and drop procedures, functions, triggers, and events are always safe, and are logged in statement format. For more information about row-based, mixed, and statement-based logging, and how safe and unsafe statements are determined, see *note replication-formats::.
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.
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 *note create-procedure::.)
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 *note 'CREATE FUNCTION': 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.
When you attempt to execute a stored function, if 'binlog_format=STATEMENT' is set, the 'DETERMINISTIC' keyword must be specified in the function definition. If this is not the case, an error is generated and the function does not run, unless 'log_bin_trust_function_creators=1' is specified to override this check (see below). For recursive function calls, the 'DETERMINISTIC' keyword is required on the outermost call only. If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the 'DETERMINISTIC' keyword.
Because MySQL does not check if a function really is deterministic at creation time, the invocation of a stored function with the 'DETERMINISTIC' keyword might carry out an action that is unsafe for statement-based logging, or invoke a function or procedure containing unsafe statements. If this occurs when 'binlog_format=STATEMENT' is set, a warning message is issued. If row-based or mixed binary logging is in use, no warning is issued, and the statement is replicated in row-based format.
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 at server startup.
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 *note replication-features::.
Triggers are similar to stored functions, so the preceding remarks regarding functions also apply to triggers with the following exception: *note 'CREATE TRIGGER': 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 *note 'CREATE TRIGGER': create-trigger. if you do not have the required privileges. On the replica side, the replica 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 note 'CREATE EVENT': create-event, note 'CREATE PROCEDURE': create-procedure, note 'CREATE FUNCTION': create-function, note 'ALTER EVENT': alter-event, note 'ALTER PROCEDURE': alter-procedure, note 'ALTER FUNCTION': alter-function, note 'DROP EVENT': drop-event, note 'DROP PROCEDURE': drop-procedure, and *note 'DROP FUNCTION': drop-function. statements to the binary log.
A stored function invocation is logged as a note 'SELECT': 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, note 'SELECT': select. statements are not written to the binary log, but a *note 'SELECT': 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 source server:
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 note 'SELECT': select. statement executes, the function 'f1()' is invoked three times. Two of those invocations insert a row, and MySQL logs a note 'SELECT': 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 note 'SELECT': 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 note 'SELECT': select. statement to the log along with the expected error code. On the replica, 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 source and replica servers.
* Statements executed on a replica are processed by the replica
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 executes only on the replica where it is processed by a thread that has full privileges. For example, if the source and replica servers have server ID values of 1 and 2, respectively, a user on the source 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 note 'CREATE FUNCTION': create-function. and note 'INSERT': insert. statements are written to the binary log, so the replica executes them. Because the replica SQL thread has full privileges, it executes the dangerous statement. Thus, the function invocation has different effects on the source and replica 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 *note 'ALTER FUNCTION': alter-function, you must have the 'SUPER' privilege in addition to the 'ALTER ROUTINE' privilege. Without the 'SUPER' privilege, an error occurs:
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 makes a replica different from the source.
* Restored data is different from the original data.To deal with these problems, MySQL enforces the following requirement: On a source 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 *note 'CREATE FUNCTION': 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 note 'CALL': call. level. That is, the server does not log the note 'CALL': call. statement, it logs those statements within the procedure that actually execute. As a result, the same changes that occur on the source server are observed on replicas. 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 replica with the same effect as the original
statement that was executed on the source within a stored
procedure.
The use of 'NAME_CONST()' can result in a problem for *note
'CREATE TABLE ... SELECT': create-table. 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 source and
replica 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 is rewritten as follows:
CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
To ensure that the source and replica 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 *note
'SET': set-variable. statement to the binary log to make sure
that the variable exists on the replica with the same value as
on the source. For example, if a statement refers to a
variable '@my_var', that statement is preceded in the binary
log by the following statement, where VALUE is the value of
'@my_var' on the source:
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 *note 'BEGIN': commit, *note
'COMMIT': commit, and *note 'ROLLBACK': commit. 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, *note
'BEGIN': commit. and *note 'COMMIT': 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 *note 'BEGIN': commit.
and *note 'ROLLBACK': commit. so that replicas make the
same changes and rollbacks as on the source.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 *note 'DO': 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.
File: manual.info.tmp, Node: stored-program-restrictions, Next: view-restrictions, Prev: stored-programs-logging, Up: stored-objects