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 Section 19.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 9.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 source and replica.
Replicated statements executed on a replica are processed by the replica's applier thread. Unless you implement replication privilege checks, which are available from MySQL 8.0.18 (see Section 19.3.3, “Replication Privilege Checks”), the applier thread has full privileges. In this situation, it is possible for a procedure to follow different execution paths on source and replica servers, so a user could write a routine containing a dangerous statement that executes only on the replica.
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
Section 7.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. From MySQL 8.0, binary logging is enabled by
default, and is only disabled if you specify the
--skip-log-bin
or
--disable-log-bin
option at startup.
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
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
Section 19.2.1, “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
SET_USER_ID
privilege (or the deprecatedSUPER
privilege), in addition to theCREATE ROUTINE
orALTER ROUTINE
privilege that is normally required. (Depending on theDEFINER
value in the function definition,SET_USER_ID
orSUPER
might be required regardless of whether binary logging is enabled. See Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”.)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 ofDETERMINISTIC
,NO SQL
, orREADS 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 utf8mb4 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, theDETERMINISTIC
keyword must be specified in the function definition. If this is not the case, an error is generated and the function does not run, unlesslog_bin_trust_function_creators=1
is specified to override this check (see below). For recursive function calls, theDETERMINISTIC
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 theDETERMINISTIC
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 whenbinlog_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 globallog_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, theDEFINER
value in the function definition requires it.For information about built-in functions that may be unsafe for replication (and thus cause stored functions that use them to be unsafe as well), see Section 19.5.1, “Replication Features and Issues”.
Triggers are similar to stored functions, so the preceding remarks
regarding functions also apply to triggers with the following
exception: CREATE TRIGGER
does not
have an optional DETERMINISTIC
characteristic,
so triggers are assumed to be always deterministic. However, this
assumption might 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 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
CREATE EVENT
,CREATE PROCEDURE
,CREATE FUNCTION
,ALTER EVENT
,ALTER PROCEDURE
,ALTER FUNCTION
,DROP EVENT
,DROP PROCEDURE
, andDROP FUNCTION
statements to the binary log.A stored function invocation is logged as a
SELECT
statement if the function changes data and occurs within a statement that would not otherwise be logged. This prevents nonreplication of data changes that result from use of stored functions in nonlogged statements. For example,SELECT
statements are not written to the binary log, but aSELECT
might invoke a stored function that makes changes. To handle this, aSELECT
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:func_name
()CREATE FUNCTION f1(a INT) RETURNS INT BEGIN IF (a < 3) THEN INSERT INTO t2 VALUES (a); END IF; RETURN 0; END; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); SELECT f1(a) FROM t1;
When the
SELECT
statement executes, the functionf1()
is invoked three times. Two of those invocations insert a row, and MySQL logs aSELECT
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 theSELECT
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's applier thread. Unless you implement replication privilege checks, which are available from MySQL 8.0.18 (see Section 19.3.3, “Replication Privilege Checks”), the applier thread 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 functionunsafe_func()
as follows:mysql> delimiter // mysql> CREATE FUNCTION unsafe_func () RETURNS INT -> BEGIN -> IF @@server_id=2 THEN dangerous_statement; END IF; -> RETURN 1; -> END; -> // mysql> delimiter ; mysql> INSERT INTO t VALUES(unsafe_func());
The
CREATE FUNCTION
andINSERT
statements are written to the binary log, so the replica executes them. Because the replica's applier 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 usualCREATE ROUTINE
privilege that is required. Similarly, to useALTER FUNCTION
, you must have theSUPER
privilege in addition to theALTER ROUTINE
privilege. Without theSUPER
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 you do not want to require function creators to have the
SUPER
privilege (for example, if all users with theCREATE ROUTINE
privilege on your system are experienced application developers), set the globallog_bin_trust_function_creators
system variable to 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, theDEFINER
value in the function definition requires it.The use of replication privilege checks where available (from MySQL 8.0.18) is recommended whatever choice you make about privileges for function creators. Replication privilege checks can be set up to ensure that only expected and relevant operations are authorized for the replication channel. For instructions to do this, see Section 19.3.3, “Replication Privilege Checks”.
If a function that performs updates is nondeterministic, it is not repeatable. This can have two undesirable effects:
It causes a replica to differ from the source.
Restored data does not match 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
andNOT DETERMINISTIC
characteristics indicate whether a function always produces the same result for given inputs. The default isNOT DETERMINISTIC
if neither characteristic is given. To declare that a function is deterministic, you must specifyDETERMINISTIC
explicitly.The
CONTAINS SQL
,NO SQL
,READS SQL DATA
, andMODIFIES SQL DATA
characteristics provide information about whether the function reads or writes data. EitherNO SQL
orREADS SQL DATA
indicates that a function does not change data, but you must specify one of these explicitly because the default isCONTAINS SQL
if no characteristic is given.
By default, for a
CREATE FUNCTION
statement to be accepted, at least one ofDETERMINISTIC
,NO SQL
, orREADS SQL DATA
must be specified explicitly. Otherwise an error occurs:ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
If you set
log_bin_trust_function_creators
to 1, the requirement that functions be deterministic or not modify data is dropped.Stored procedure calls are logged at the statement level rather than at the
CALL
level. That is, the server does not log theCALL
statement, it logs those statements within the procedure that actually execute. As a result, the same changes that occur on the source server also occur 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, andvar_value
is a constant indicating the value that the variable has at the time the statement is logged.NAME_CONST()
has a value ofvar_value
, and a “name” ofvar_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 forCREATE TABLE ... SELECT
statements when the source column expressions refer to local variables. Converting these references toNAME_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 whenmyvar
has a value of 1:CREATE TABLE t1 SELECT myvar;
This 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
SET
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, wherevalue
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
BEGIN
,COMMIT
, andROLLBACK
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
andCOMMIT
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
andROLLBACK
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
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.