17.4. Binary Logging of Stored Routines and Triggers

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:

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.

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:

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:

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:

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.

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.

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;

DO f1(1);
DO f1(2);