Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




19.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:

  • For replication, 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 6.2, “Replication Implementation Overview”.

  • 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, “Using Backups for Recovery”.

This section describes how MySQL 5.1 handles binary logging for stored routines (procedures and functions) and triggers. It also states the current conditions that the implementation places on the use of stored routines, and then provides additional information about the reasons for these conditions.

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.11.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.1 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 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.

  • 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 function 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, 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 following discussion provides additional detail about the logging implementation and its implications.


  • 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 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 SELECT 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:

      IF (a < 3) THEN 
        INSERT INTO t2 VALUES (a);
      END IF;
    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.

    Note: Before MySQL 5.1.7, you will see these SELECT func_name() statements logged as DO func_name(). The change to SELECT was made because use of DO was found to yield insufficient control over error code checking.

  • 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 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 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 statment. 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 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 non-deterministic, 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, so you must specify DETERMINISTIC explicitly to declare that a function is deterministic.

      Use of the NOW() function (or its synonyms) or RAND() does not necessarily make a function non-deterministic. For NOW(), the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is invoked only once within a function. (You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)

    • 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, 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

    If you set log_bin_trust_function_creators to 1, the requirement that functions be deterministic or not modify data is dropped.

    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.

  • 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 non-procedure 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.

    • 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. Previously, CALL statements 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 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 non-transactional tables are logged because rollback does not cancel them.

      • Updates to a mix of transactional and non-transactional 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.

  Published under the terms of the GNU General Public License Design by Interspire