27.7 Stored Program Binary Logging (original) (raw)

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:

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):

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 (seeSection 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 theCALL 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, seeSection 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.

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.

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.

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, theUUID() 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 triggerDEFINER 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.

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 replica, if the same error occurs, that is the expected result and replication continues. Otherwise, replication stops.

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 theSUPER privilege, in addition to the usual CREATE ROUTINE privilege that is required. Similarly, to useALTER 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 you do not want to require function creators to have theSUPER privilege (for example, if all users with the CREATE 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, the DEFINER value in the function definition requires it.

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 setlog_bin_trust_function_creators to 1, the requirement that functions be deterministic or not modify data is dropped.