Chapter 18. Stored Programs and Views

Table of Contents

18.1. Defining Stored Programs
18.2. Using Stored Routines (Procedures and Functions)
18.2.1. Stored Routine Syntax
18.2.2. Stored Routines and MySQL Privileges
18.2.3. Stored Routine Metadata
18.2.4. Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
18.3. Using Triggers
18.3.1. Trigger Syntax
18.3.2. Trigger Metadata
18.4. Using Views
18.4.1. View Syntax
18.4.2. View Processing Algorithms
18.4.3. Updatable and Insertable Views
18.4.4. View Metadata
18.5. Binary Logging of Stored Programs

This chapter discusses stored programs and views, which are database objects defined in terms of SQL code that is stored on the server for later invocation.

Stored programs include these objects:

Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

This chapter describes how to use each type of stored program and views. Additional information about SQL syntax for statements related to these objects is available in the following locations:

18.1. Defining Stored Programs

Each stored program contains a body that consists of an SQL statement. This statement may be a compound statement made up of several statements separated by semicolon (;) characters. For example, the following stored procedure has a body made up of a BEGIN ... END block that contains a SET statement and a REPEAT loop that itself contains another SET statement:

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END

If you use the mysql client program to define a stored program that contains the semicolon characters within its definition, a problem arises. By default, mysql itself recognizes semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. The following example shows how to do this for the dorepeat() procedure just shown. The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This allows the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

mysql> delimiter //

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

You can redefine the delimiter to a string other than //, and the delimiter can consist of a single character or multiple characters. You should avoid the use of the backslash (“\”) character because that is the escape character for MySQL.

The following is an example of a function that takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

18.2. Using Stored Routines (Procedures and Functions)

Stored routines (procedures and functions) are supported in MySQL 5.0. A stored routine is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored routine instead.

Stored routines require the proc table in the mysql database. This table is created during the MySQL 5.0 installation procedure. If you are upgrading to MySQL 5.0 from an earlier version, be sure to update your grant tables to make sure that the proc table exists. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.

MySQL Enterprise For expert advice on using stored procedures and functions subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

Stored routines can be particularly useful in certain situations:

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

Stored routines can provide improved performance because less information needs to be sent between the server and the client. The tradeoff is that this does increase the load on the database server because more of the work is done on the server side and less is done on the client (application) side. Consider this if many client machines (such as Web servers) are serviced by only one or a few database servers.

Stored routines also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally (for example, by using classes). Using these client application language features is beneficial for the programmer even outside the scope of database use.

MySQL follows the SQL:2003 syntax for stored routines, which is also used by IBM's DB2.

The MySQL implementation of stored routines is still in progress. All syntax described here is supported and any limitations and extensions are documented where appropriate.

Additional resources

18.2.1. Stored Routine Syntax

A stored routine is either a procedure or a function. Stored routines are created with the CREATE PROCEDURE and CREATE FUNCTION statements (see Section 12.1.9, “CREATE PROCEDURE and CREATE FUNCTION Syntax”). A procedure is invoked using a CALL statement (see Section 12.2.1, “CALL Syntax”), and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function's name), and can return a scalar value. The body of a stored routine can use compound statements (see Section 12.8, “MySQL Compound-Statement Syntax”).

Stored routines can be dropped with the DROP PROCEDURE and DROP FUNCTION statements (see Section 12.1.16, “DROP PROCEDURE and DROP FUNCTION Syntax”), and altered with the ALTER PROCEDURE and ALTER FUNCTION statements (see Section 12.1.3, “ALTER PROCEDURE Syntax”).

As of MySQL 5.0.1, a stored procedure or function is associated with a particular database. This has several implications:

  • When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). USE statements within stored routines are disallowed.

  • You can qualify routine names with the database name. This can be used to refer to a routine that is not in the current database. For example, to invoke a stored procedure p or function f that is associated with the test database, you can say CALL test.p() or test.f().

  • When a database is dropped, all stored routines associated with it are dropped as well.

(In MySQL 5.0.0, stored routines are global and not associated with a database. They inherit the default database from the caller. If a USE db_name is executed within the routine, the original default database is restored upon routine exit.)

Stored functions cannot be recursive.

Recursion in stored procedures is allowed but disabled by default. To enable recursion, set the max_sp_recursion_depth server system variable to a value greater than zero. Stored procedure recursion increases the demand on thread stack space. If you increase the value of max_sp_recursion_depth, it may be necessary to increase thread stack size by increasing the value of thread_stack at server startup. See Section 5.1.3, “Server System Variables”, for more information.

MySQL supports the very useful extension that allows the use of regular SELECT statements (that is, without using cursors or local variables) inside a stored procedure. The result set of such a query is simply sent directly to the client. Multiple SELECT statements generate multiple result sets, so the client must use a MySQL client library that supports multiple result sets. This means the client must use a client library from a version of MySQL at least as recent as 4.1. The client should also specify the CLIENT_MULTI_RESULTS option when it connects. For C programs, this can be done with the mysql_real_connect() C API function. See Section 20.9.3.52, “mysql_real_connect(), and Section 20.9.12, “C API Support for Multiple Statement Execution”.

MySQL Enterprise MySQL Enterprise subscribers will find numerous articles about stored routines in the MySQL Enterprise Knowledge Base. Access to this collection of articles is one of the advantages of subscribing to MySQL Enterprise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.

18.2.2. Stored Routines and MySQL Privileges

Beginning with MySQL 5.0.3, the grant system takes stored routines into account as follows:

  • The CREATE ROUTINE privilege is needed to create stored routines.

  • The ALTER ROUTINE privilege is needed to alter or drop stored routines. This privilege is granted automatically to the creator of a routine if necessary, and dropped when the routine creator drops the routine.

  • The EXECUTE privilege is required to execute stored routines. However, this privilege is granted automatically to the creator of a routine if necessary (and dropped when the creator drops the routine). Also, the default SQL SECURITY characteristic for a routine is DEFINER, which enables users who have access to the database with which the routine is associated to execute the routine.

  • If the automatic_sp_privileges system variable is 0, the EXECUTE and ALTER ROUTINE privileges are not automatically granted and dropped.

The server manipulates the mysql.proc table in response to statements that create, alter, or drop stored routines. It is not supported that the server will notice manual manipulation of this table.

18.2.3. Stored Routine Metadata

Metadata about stored routines can be obtained as follows:

18.2.4. Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()

Within the body of a stored routine (procedure or function) or a trigger, the value of LAST_INSERT_ID() changes the same way as for statements executed outside the body of these kinds of objects (see Section 11.10.3, “Information Functions”). The effect of a stored routine or trigger upon the value of LAST_INSERT_ID() that is seen by following statements depends on the kind of routine:

  • If a stored procedure executes statements that change the value of LAST_INSERT_ID(), the changed value is seen by statements that follow the procedure call.

  • For stored functions and triggers that change the value, the value is restored when the function or trigger ends, so following statements do not see a changed value.

18.3. Using Triggers

Support for triggers is included beginning with MySQL 5.0.2. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Some uses for triggers are to perform checks of values to be inserted into a table or to perform calculations on values involved in an update.

A trigger is defined to activate when an INSERT, DELETE, or UPDATE statement executes for the associated table. A trigger can be set to activate either before or after the triggering statement. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated.

Important

MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server; in particular, they are not activated by updates made using the NDB API.

To use triggers if you have upgraded to MySQL 5.0 from an older release that did not support triggers, you should upgrade your grant tables so that they contain the trigger-related privileges. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.

The following discussion describes the syntax for creating and dropping triggers, and shows some examples of how to use them.

Additional resources

18.3.1. Trigger Syntax

To create a trigger or drop a trigger, use the CREATE TRIGGER or DROP TRIGGER statement. The syntax for these statements is described in Section 12.1.11, “CREATE TRIGGER Syntax”, and Section 12.1.18, “DROP TRIGGER Syntax”.

Here is a simple example that associates a trigger with a table for INSERT statements. The trigger acts as an accumulator, summing the values inserted into one of the columns of the table.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

The CREATE TRIGGER statement creates a trigger named ins_sum that is associated with the account table. It also includes clauses that specify the trigger activation time, the triggering event, and what to do with the trigger activates:

  • The keyword BEFORE indicates the trigger action time. In this case, the trigger should activate before each row inserted into the table. The other allowable keyword here is AFTER.

  • The keyword INSERT indicates the event that activates the trigger. In the example, INSERT statements cause trigger activation. You can also create triggers for DELETE and UPDATE statements.

  • The statement following FOR EACH ROW defines the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering statement In the example, the triggered statement is a simple SET that accumulates the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.

To use the trigger, set the accumulator variable to zero, execute an INSERT statement, and then see what value the variable has afterward:

mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48               |
+-----------------------+

In this case, the value of @sum after the INSERT statement has executed is 14.98 + 1937.50 - 100, or 1852.48.

To destroy the trigger, use a DROP TRIGGER statement. You must specify the schema name if the trigger is not in the default schema:

mysql> DROP TRIGGER test.ins_sum;

Triggers for a table are also dropped if you drop the table.

Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

In addition to the requirement that trigger names be unique for a schema, there are other limitations on the types of triggers you can create. In particular, you cannot have two triggers for a table that have the same activation time and activation event. For example, you cannot define two BEFORE INSERT triggers or two AFTER UPDATE triggers for a table. This should rarely be a significant limitation, because it is possible to define a trigger that executes multiple statements by using the BEGIN ... END compound statement construct after FOR EACH ROW. (An example appears later in this section.)

The OLD and NEW keywords enable you to access columns in the rows affected by a trigger. (OLD and NEW are not case sensitive.) In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. A column named with NEW can be referred to if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or that are used to update a row.

In a BEFORE trigger, the NEW value for an AUTO_INCREMENT column is 0, not the automatically generated sequence number that will be generated when the new record actually is inserted.

OLD and NEW are MySQL extensions to triggers.

By using the BEGIN ... END construct, you can define a trigger that executes multiple statements. Within the BEGIN block, you also can use other syntax that is allowed within stored routines such as conditionals and loops. However, just as for stored routines, if you use the mysql program to define a trigger that executes multiple statements, it is necessary to redefine the mysql statement delimiter so that you can use the ; statement delimiter within the trigger definition. The following example illustrates these points. It defines an UPDATE trigger that checks the new value to be used for updating each row, and modifies the value to be within the range from 0 to 100. This must be a BEFORE trigger because the value needs to be checked before it is used to update the row:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

It can be easier to define a stored procedure separately and then invoke it from the trigger using a simple CALL statement. This is also advantageous if you want to invoke the same routine from within several triggers.

There are some limitations on what can appear in statements that a trigger executes when activated:

  • The trigger cannot use the CALL statement to invoke stored procedures that return data to the client or that use dynamic SQL. (Stored procedures are allowed to return data to the trigger through OUT or INOUT parameters.)

  • The trigger cannot use statements that explicitly or implicitly begin or end a transaction such as START TRANSACTION, COMMIT, or ROLLBACK.

  • Prior to MySQL 5.0.10, triggers cannot contain direct references to tables by name.

MySQL handles errors during trigger execution as follows:

  • If a BEFORE trigger fails, the operation on the corresponding row is not performed.

  • A BEFORE trigger is activated by the attempt to insert or modify the row, regardless of whether the attempt subsequently succeeds.

  • An AFTER trigger is executed only if the BEFORE trigger (if any) and the row operation both execute successfully.

  • An error during either a BEFORE or AFTER trigger results in failure of the entire statement that caused trigger invocation.

  • For transactional tables, failure of a statement should cause rollback of all changes performed by the statement. Failure of a trigger causes the statement to fail, so trigger failure also causes rollback. For nontransactional tables, such rollback cannot be done, so although the statement fails, any changes performed prior to the point of the error remain in effect.

18.3.2. Trigger Metadata

Metadata about triggers can be obtained as follows:

18.4. Using Views

Views (including updatable views) are available in MySQL Server 5.0. Views are stored queries that when invoked produce a result set. A view acts as a virtual table. Views are available in binary releases from 5.0.1 and up.

To use views if you have upgraded to MySQL 5.0.1 from an older release, you should upgrade your grant tables so that they contain the view-related privileges. See Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”.

The following discussion describes the syntax for creating and dropping views, and shows some examples of how to use them.

Additional resources

18.4.1. View Syntax

The CREATE VIEW statement creates a new view (see Section 12.1.12, “CREATE VIEW Syntax”). To alter the definition of a view or drop a view, use ALTER VIEW (see Section 12.1.5, “ALTER VIEW Syntax”), or DROP VIEW (see Section 12.1.19, “DROP VIEW Syntax”).

A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns:

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50), (5, 60);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
|    5 |    60 |   300 |
+------+-------+-------+
mysql> SELECT * FROM v WHERE qty = 5;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    5 |    60 |   300 |
+------+-------+-------+

18.4.2. View Processing Algorithms

The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if no ALGORITHM clause is present.

For MERGE, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.

For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.

For UNDEFINED, MySQL chooses which algorithm to use. It prefers MERGE over TEMPTABLE if possible, because MERGE is usually more efficient and because a view cannot be updatable if a temporary table is used.

A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.

A view algorithm can be UNDEFINED for three reasons:

  • No ALGORITHM clause is present in the CREATE VIEW statement.

  • The CREATE VIEW statement has an explicit ALGORITHM = UNDEFINED clause.

  • ALGORITHM = MERGE is specified for a view that can be processed only with a temporary table. In this case, MySQL generates a warning and sets the algorithm to UNDEFINED.

As mentioned earlier, MERGE is handled by merging corresponding parts of a view definition into the statement that refers to the view. The following examples briefly illustrate how the MERGE algorithm works. The examples assume that there is a view v_merge that has this definition:

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;

Example 1: Suppose that we issue this statement:

SELECT * FROM v_merge;

MySQL handles the statement as follows:

  • v_merge becomes t

  • * becomes vc1, vc2, which corresponds to c1, c2

  • The view WHERE clause is added

The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE c3 > 100;

Example 2: Suppose that we issue this statement:

SELECT * FROM v_merge WHERE vc1 < 100;

This statement is handled similarly to the previous one, except that vc1 < 100 becomes c1 < 100 and the view WHERE clause is added to the statement WHERE clause using an AND connective (and parentheses are added to make sure the parts of the clause are executed with correct precedence). The resulting statement to be executed becomes:

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);

Effectively, the statement to be executed has a WHERE clause of this form:

WHERE (select WHERE) AND (view WHERE)

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

  • Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

  • DISTINCT

  • GROUP BY

  • HAVING

  • LIMIT

  • UNION or UNION ALL

  • Subquery in the select list

  • Refers only to literal values (in this case, there is no underlying table)

18.4.3. Updatable and Insertable Views

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. To be more specific, a view is not updatable if it contains any of the following:

  • Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

  • DISTINCT

  • GROUP BY

  • HAVING

  • UNION or UNION ALL

  • Subquery in the select list

  • Certain joins (see additional join discussion later in this section)

  • Nonupdatable view in the FROM clause

  • A subquery in the WHERE clause that refers to a table in the FROM clause

  • Refers only to literal values (in this case, there is no underlying table to update)

  • Uses ALGORITHM = TEMPTABLE (use of a temporary table always makes a view nonupdatable)

  • Multiple references to any column of a base table.

With respect to insertability (being updatable with INSERT statements), an updatable view is insertable if it also satisfies these additional requirements for the view columns:

  • There must be no duplicate view column names.

  • The view must contain all columns in the base table that do not have a default value.

  • The view columns must be simple column references and not derived columns. A derived column is one that is not a simple column reference but is derived from an expression. These are examples of derived columns:

    3.14159
    col1 + 3
    UPPER(col2)
    col3 / col4
    (subquery)
    

A view that has a mix of simple column references and derived columns is not insertable, but it can be updatable if you update only those columns that are not derived. Consider this view:

CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;

This view is not insertable because col2 is derived from an expression. But it is updatable if the update does not try to update col2. This update is allowable:

UPDATE v SET col1 = 0;

This update is not allowable because it attempts to update a derived column:

UPDATE v SET col2 = 0;

It is sometimes possible for a multiple-table view to be updatable, assuming that it can be processed with the MERGE algorithm. For this to work, the view must use an inner join (not an outer join or a UNION). Also, only a single table in the view definition can be updated, so the SET clause must name only columns from one of the tables in the view. Views that use UNION ALL are disallowed even though they might be theoretically updatable, because the implementation uses temporary tables to process them.

For a multiple-table updatable view, INSERT can work if it inserts into a single table. DELETE is not supported.

INSERT DELAYED is not supported for views.

If a table contains an AUTO_INCREMENT column, inserting into an insertable view on the table that does not include the AUTO_INCREMENT column does not change the value of LAST_INSERT_ID(), because the side effects of inserting default values into columns not part of the view should not be visible.

The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true.

In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED. Consider the definitions for the following table and set of views:

mysql> CREATE TABLE t1 (a INT);
mysql> CREATE VIEW v1 AS SELECT * FROM t1 WHERE a < 2
    -> WITH CHECK OPTION;
mysql> CREATE VIEW v2 AS SELECT * FROM v1 WHERE a > 0
    -> WITH LOCAL CHECK OPTION;
mysql> CREATE VIEW v3 AS SELECT * FROM v1 WHERE a > 0
    -> WITH CASCADED CHECK OPTION;

Here the v2 and v3 views are defined in terms of another view, v1. v2 has a LOCAL check option, so inserts are tested only against the v2 check. v3 has a CASCADED check option, so inserts are tested not only against its own check, but against those of underlying views. The following statements illustrate these differences:

mysql> INSERT INTO v2 VALUES (2);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO v3 VALUES (2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v3'

MySQL sets a flag, called the view updatability flag, at CREATE VIEW time. The flag is set to YES (true) if UPDATE and DELETE (and similar operations) are legal for the view. Otherwise, the flag is set to NO (false). The IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table displays the status of this flag. It means that the server always knows whether a view is updatable. If the view is not updatable, statements such UPDATE, DELETE, and INSERT are illegal and will be rejected. (Note that even if a view is updatable, it might not be possible to insert into it, as described elsewhere in this section.)

The updatability of views may be affected by the value of the updatable_views_with_limit system variable. See Section 5.1.3, “Server System Variables”.

18.4.4. View Metadata

Metadata about views can be obtained as follows:

18.5. Binary Logging of Stored Programs

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

However, there are certain binary logging issues that apply with respect to stored programs (stored procedures and functions, and triggers):

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

This section describes how MySQL 5.0 handles binary logging for stored programs. The discussion first states the current conditions that the implementation places on the use of stored programs, and what you can do to avoid problems. Then it summarizes the changes that have taken place in the logging implementation. 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 of MySQL.

In general, the issues described here occur due to the fact that binary logging occurs at the SQL statement level. MySQL 5.1 implements row-level binary logging, which solves or alleviates these issues because the log contains changes made 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.2.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 current conditions on the use of stored functions in MySQL 5.0 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.

    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”.

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 nondeterministic (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 required privileges. On the slave side, the slave uses the trigger DEFINER attribute to determine which user is considered to be the creator of the trigger.

The rest of this section provides details on the development of stored routine logging. 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.

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

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

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:

CREATE PROCEDURE mysp INSERT INTO t VALUES(1);
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.)

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

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

    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:

    • 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 routine is refused unless you declare the routine to be deterministic or to not modify data. Two sets of routine characteristics apply here:

    • The DETERMINISTIC and NOT DETERMINISTIC characteristics indicate whether a routine always produces the same result for given inputs. The default is NOT DETERMINISTIC if neither characteristic is given. To declare that a routine is deterministic, you must specify DETERMINISTIC explicitly.

    • The CONTAINS SQL, NO SQL, READS SQL DATA, and MODIFIES SQL DATA characteristics provide information about whether the routine reads or writes data. Either NO SQL or READS SQL DATA indicates that a routine 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 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.

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

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

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

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

      The use of NAME_CONST() can result in a problem for CREATE TABLE ... SELECT statements when the source column expressions refer to local variables. Converting these references to NAME_CONST() expressions can result in column names that are different on the master and slave servers, or names that are too long to be legal column identifiers. A workaround is to supply aliases for columns that refer to local variables. Consider this statement when myvar has a value of 1:

      CREATE TABLE t1 SELECT myvar;
      

      That will be rewritten as follows:

      CREATE TABLE t1 SELECT NAME_CONST(myvar, 1);
      

      To ensure that the master and slave tables have the same column names, write the statement like this:

      CREATE TABLE t1 SELECT myvar AS myvar;
      

      The rewritten statement becomes:

      CREATE TABLE t1 SELECT NAME_CONST(myvar, 1) AS myvar;
      
    • 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 nontransactional tables are logged because rollback does not cancel them.

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

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 nonlogged 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.