17.4. Binary Logging of Stored Routines and Triggers

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:

This section describes the development of binary logging in MySQL 5.0 with respect to stored routines (procedures and functions) and triggers. The discussion first summarizes the changes that have taken place in the logging implementation, and then states the current conditions that the implementation places on the use of stored routines. 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.

In general, the issues described here result from the fact that binary logging occurs at the SQL statement level. A future MySQL release is expected to implement row-level binary logging, which specifies the changes to make 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.12.3, “The Binary Log”.) If the binary log is not enabled, replication is not possible, nor is the binary log available for data recovery.

The development of stored routine logging in MySQL 5.0 can be summarized as follows:

As a consequence of the preceding changes, the following conditions currently apply to stored function creation when binary logging is enabled. These conditions do not apply to stored procedure creation.

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 non-deterministic (and does not replicate). You should be careful about using such functions in triggers.

Triggers can update tables (as of MySQL 5.0.10), so error messages similar to those for stored functions occur with CREATE TRIGGER if you do not have the SUPER privilege and log_bin_trust_function_creators is 0.

The rest of this section provides details on the development of stored routine logging. Some of these details give additional background on the rationale for the current logging-related conditions on stored routine use.

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:

CALL mysp();

For this example, only the INSERT 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 CALL 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 versions.)

Routine logging changes in MySQL 5.0.12: The changes in 5.0.12 address several problems that were present in earlier versions:

To deal with these issues, MySQL 5.0.12 implements the following changes to function and procedure logging:

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 log_bin_trust_function_creators and the --log-bin-trust-routine-creators server option is renamed to --log-bin-trust-function-creators. (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 DO func_name() if the invocation changes data and occurs within a non-logged statement, or if the function invokes a stored procedure that produces an error. In 5.0.19, these invocations are logged as SELECT func_name() instead. The change to SELECT was made because use of DO was found to yield insufficient control over error code checking.