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 6.3.2, “Using Backups for Recovery”.
However, there are certain binary logging issues that apply with respect to stored programs (stored procedures and functions, triggers, and events), if 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.5 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 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.1.2, “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.2.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 current conditions on the use of stored functions in MySQL 5.5 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.
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:
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.
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:
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
You can also set this variable by using the
--log-bin-trust-function-creators=1
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 function creation.
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
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 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 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.
The server writes CREATE EVENT
,
CREATE PROCEDURE
,
CREATE FUNCTION
,
ALTER EVENT
,
ALTER PROCEDURE
,
ALTER FUNCTION
,
DROP EVENT
,
DROP PROCEDURE
, and
DROP 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 a
SELECT
might invoke a stored
function that makes changes. To handle this, a SELECT
statement is
written to the binary log when the given function makes a
change. Suppose that the following statements are executed on
the master:
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 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:
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
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 you do not want to require function 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_function_creators
system variable to 1. You can also set this variable by using
the
--log-bin-trust-function-creators=1
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 function creation.
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)
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 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:
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.
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.
User Comments
Add your own comment.