When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.

When security is paramount. Banks, for example, use stored procedures and functions for all common operations. This provides a consistent and secure environment, and routines can ensure that each operation is properly logged. In such a setup, applications and users would have no access to the database tables directly, but can only execute specific stored routines.

You may find the Stored Procedures User Forum of use when working with stored procedures and functions.

For answers to some commonly asked questions regarding stored routines in MySQL, see Section A.4, “MySQL 5.0 FAQ — Stored Procedures and Functions”.

There are some restrictions on the use of stored routines. See Section D.1, “Restrictions on Stored Routines and Triggers”.

Binary logging for stored routines takes place as described in Section 18.5, “Binary Logging of Stored Programs”.

You may find the Triggers User Forum of use when working with views.

For answers to some commonly asked questions regarding triggers in MySQL, see Section A.5, “MySQL 5.0 FAQ — Triggers”.

There are some restrictions on the use of triggers; see Section D.1, “Restrictions on Stored Routines and Triggers”.

Binary logging for triggers takes place as described in Section 18.5, “Binary Logging of Stored Programs”.

You may find the Views User Forum of use when working with views.

For answers to some commonly asked questions regarding views in MySQL, see Section A.6, “MySQL 5.0 FAQ — Views”.

There are some restrictions on the use of views; see Section D.4, “Restrictions on Views”.

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.4, “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”.

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.

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.

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 routine 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.3.1, “Replication Features and Issues”.

Before MySQL 5.0.6: In the initial implementation of stored routine logging, statements that create stored routines and CALL statements are not logged. These omissions can cause problems for replication and data recovery.

MySQL 5.0.6: Statements that create stored routines and CALL statements are logged. Stored function invocations are logged when they occur in statements that update data (because those statements are logged). However, function invocations are not logged when they occur in statements such as SELECT that do not change data, even if a data change occurs within a function itself; this can cause problems. Under some circumstances, functions and procedures can have different effects if executed at different times or on different (master and slave) machines, and thus can be unsafe for data recovery or replication. To handle this, measures are implemented to allow identification of safe routines and to prevent creation of unsafe routines except by users with sufficient privileges.

MySQL 5.0.12: For stored functions, when a function invocation that changes data occurs within a nonlogged statement such as SELECT, the server logs a DO func_name() statement that invokes the function so that the function gets executed during data recovery or replication to slave servers. For stored procedures, the server does not log CALL statements. Instead, it logs individual statements within a procedure that are executed as a result of a CALL. This eliminates problems that may occur when a procedure would follow a different execution path on a slave than on the master.

MySQL 5.0.16: The procedure logging changes made in 5.0.12 allow the conditions on unsafe routines to be relaxed for stored procedures. Consequently, the user interface for controlling these conditions is revised to apply only to functions. Procedure creators are no longer bound by them.

MySQL 5.0.17: Logging of stored functions as DO func_name() statements (per the changes made in 5.0.12) are logged as SELECT func_name() statements instead for better control over error checking.

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 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 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=1 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 nondeterministic, 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, at least one of DETERMINISTIC, NO SQL, or 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 nontransactional 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 nonlogged 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 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 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;
  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 DO statement for each of them. That is, MySQL writes the following statements to the binary log:

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

The server also logs a DO statement for a stored function invocation when the function invokes a stored procedure that causes an error. In this case, the server writes the DO 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: See later in this section for changes made in MySQL 5.0.19: These logged DO func_name() statements are logged as SELECT func_name() statements instead.

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 eliminates the problems that could result from a procedure having different execution paths on different machines. For example, the DROP DATABASE problem shown earlier for the unsafe_sp() procedure does not occur and the routine is no longer replication-unsafe because it has the same effect on master and slave servers.

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 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 still should be exercised in the use of stored functions that invoke a procedure, even if the procedure is otherwise safe in itself.

Because procedure logging occurs at the statement level rather than at the CALL level, interpretation of the --replicate-*-table options is revised to apply only to stored functions. They no longer apply to stored procedures, except those procedures that are invoked from within functions.