The binary log contains information about SQL statements that modify database contents. This information is stored in the form of “events” that describe the modifications. The binary log has two important purposes:
For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 16.2, “Replication Implementation”.
Certain data recovery operations require use of the binary log. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.3.2, “Using Backups for Recovery”.
However, there are certain binary logging issues that apply with respect to stored programs (stored procedures and functions, and triggers):
Logging occurs at the statement level. In some cases, it is possible that a statement will affect different sets of rows on a master and a slave.
Replicated statements executed on a slave are processed by the slave SQL thread, which has full privileges. It is possible for a procedure to follow different execution paths on master and slave servers, so a user can write a routine containing a dangerous statement that will execute only on the slave where it is processed by a thread that has full privileges.
If a stored program that modifies data is nondeterministic, it is not repeatable. This can result in different data on a master and slave, or cause restored data to differ from the original data.
This section describes how MySQL 5.0 handles binary logging for stored programs. The discussion first states the current conditions that the implementation places on the use of stored programs, and what you can do to avoid problems. Then it summarizes the changes that have taken place in the logging implementation. 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 of MySQL.
In general, the issues described here occur due to the fact that binary logging occurs at the SQL statement level. MySQL 5.1 implements row-level binary logging, which solves or alleviates these issues because the log contains changes made 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
Section 5.4.3, “The Binary Log”.) If the binary log is not enabled,
replication is not possible, nor is the binary log available for
The current conditions on the use of stored functions in MySQL 5.0 can be summarized as follows. These conditions do not apply to stored procedures and they do not apply unless binary logging is enabled.
To create or alter a stored function, you must have the
SUPERprivilege, in addition to the
ALTER ROUTINEprivilege that is normally required. (Depending on the
DEFINERvalue in the function definition,
SUPERmight be required regardless of whether binary logging is enabled. See Section 13.1.9, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.)
When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
By default, for a
CREATE FUNCTIONstatement to be accepted, at least one of
NO SQL, or
READS SQL DATAmust 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
DETERMINISTICis free of statements that produce nondeterministic results.
To relax the preceding conditions on function creation (that you must have the
SUPERprivilege and that a function must be declared deterministic or to not modify data), set the global
log_bin_trust_function_creatorssystem variable to 1. By default, this variable has a value of 0, but you can change it like this:
SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
--log-bin-trust-function-creators=1option when starting the server.
If binary logging is not enabled,
log_bin_trust_function_creatorsdoes not apply.
SUPERis not required for function creation unless, as described previously, the
DEFINERvalue in the function definition requires it.
For information about built-in functions that may be unsafe for replication (and thus cause stored functions that use them to be unsafe as well), see Section 16.4.1, “Replication Features and Issues”.
Triggers are similar to stored functions, so the preceding remarks
regarding functions also apply to triggers with the following
CREATE TRIGGER does not
have an optional
so triggers are assumed to be always deterministic. However, this
assumption might in some cases be invalid. For example, the
UUID() function is nondeterministic
(and does not replicate). You should be careful about using such
functions in triggers.
Triggers can update tables, so error messages similar to those for
stored functions occur with
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 details on the development of stored routine logging. 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.
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
CALLstatements are not logged. These omissions can cause problems for replication and data recovery.
MySQL 5.0.6: Statements that create stored routines and
CALLstatements 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
SELECTthat 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 enable 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 nonlogged statement such as
SELECT, the server logs a
DOstatement 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
CALLstatements. 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 enable 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
DOstatements (per the changes made in 5.0.12) are logged as
SELECTstatements instead for better control over error checking.
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
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
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
The server writes
DROP PROCEDURE, and
DROP FUNCTIONstatements to the binary log. Also, the server logs
CALLstatements, 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
CALLstatements appear in the binary log, but the
INSERTstatement does not appear. This corrects the problem that occurred before MySQL 5.0.6 such that only the
CALLstatements has a security implication for replication, which arises from two factors:
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.
The implication is that although a user must have the
CREATE ROUTINEprivilege to create a routine, the 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. 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
CREATE PROCEDURE unsafe_sp ()->
IF @@server_id=2 THEN DROP DATABASE accounting; END IF;->
CALLstatements 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 DATABASEstatement that drops the
accountingdatabase. Thus, the
CALLstatement 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
SUPERprivilege, in addition to the usual
CREATE ROUTINEprivilege that is required. Similarly, to use
ALTER FUNCTION, you must have the
SUPERprivilege in addition to the
ALTER ROUTINEprivilege. Without the
SUPERprivilege, 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
SUPERprivilege (for example, if all users with the
CREATE ROUTINEprivilege on your system are experienced application developers), set the global
log_bin_trust_routine_creatorssystem variable to 1. You can also set this variable by using the
--log-bin-trust-routine-creators=1option when starting the server. If binary logging is not enabled,
log_bin_trust_routine_creatorsdoes not apply.
SUPERis not required for routine creation unless, as described previously, the
DEFINERvalue in the routine definition requires it.
If a routine 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 routine is refused unless you declare the routine to be deterministic or to not modify data. Two sets of routine characteristics apply here:
NOT DETERMINISTICcharacteristics indicate whether a routine always produces the same result for given inputs. The default is
NOT DETERMINISTICif neither characteristic is given. To declare that a routine is deterministic, you must specify
READS SQL DATA, and
MODIFIES SQL DATAcharacteristics provide information about whether the routine reads or writes data. Either
READS SQL DATAindicates that a routine does not change data, but you must specify one of these explicitly because the default is
CONTAINS SQLif no characteristic is given.
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_creatorsto 1, the requirement that routines be deterministic or not modify data is dropped.
CALLstatement 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 nontransactional table (such as a
MyISAMtable) 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
UPDATEto 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,
CALLstatements are logged even when they occur within a rolled-back transaction. This can result in a
CALLbeing rolled back on the master but executed on slaves.
If a stored function is invoked within a statement such as
SELECTthat 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
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
DOstatement sometimes is executed for the side effect of evaluating an expression,
DOis not a workaround here because it is not written to the binary log.
On slave servers,
--replicate-*-tablerules do not apply to
CALLstatements 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 nonlogged statements such as
SELECTwere not being logged, even when a function itself changed data.
Stored procedure logging at the
CALLlevel could cause different effects on a master and slave if a procedure took different execution paths on the two machines.
CALLstatements 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 procedure and function logging:
A stored function invocation is logged as a
DOstatement if the function changes data and occurs within a statement that would not otherwise be logged. This corrects the problem of nonreplication of data changes that result from use of stored functions in nonlogged statements. For example,
SELECTstatements are not written to the binary log, but a
SELECTmight invoke a stored function that makes changes. To handle this, a
DOstatement 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; RETURN 0; END; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2),(3); SELECT f1(a) FROM t1;
SELECTstatement executes, the function
f1()is invoked three times. Two of those invocations insert a row, and MySQL logs a
DOstatement 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
DOstatement for a stored function invocation when the function invokes a stored procedure that causes an error. In this case, the server writes the
DOstatement 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
DOstatements are logged as
Stored procedure calls are logged at the statement level rather than at the
CALLlevel. That is, the server does not log the
CALLstatement, 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 DATABASEproblem 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 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:
var_nameis the local variable name, and
var_valueis 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:
SELECT NAME_CONST('myname', 14);+--------+ | myname | +--------+ | 14 | +--------+
NAME_CONST()enables a logged standalone statement to be executed on a slave with the same effect as the original statement that was executed on the master within a stored procedure.
The use of
NAME_CONST()can result in a problem for
CREATE TABLE ... SELECTstatements 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
myvarhas 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
SETstatement 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
valueis the value of
@my_varon the master:
SET @my_var =
Procedure calls can occur within a committed or rolled-back transaction. Previously,
CALLstatements 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
ROLLBACKstatements 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,
COMMITstatements 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.
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
DOstatement (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
CALLlevel, interpretation of the
--replicate-*-tableoptions 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
server option is renamed to
(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
if the invocation
changes data and occurs within a nonlogged statement, or if the
function invokes a stored procedure that produces an error. In
5.0.19, these invocations are logged as
SELECT was made because
DO was found to yield
insufficient control over error code checking.