Stored Programs and Views - MariaDB - Databases - Software - Computers


Stored Programs and Views
Prev Next

Stored Programs and Views

Table of Contents

Defining Stored Programs
Using Stored Routines (Procedures and Functions)
Stored Routine Syntax
Stored Routines and MariaDB Privileges
Stored Routine Metadata
Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()
Using Triggers
Trigger Syntax
Trigger Metadata
Using the Event Scheduler
Event Scheduler Overview
Event Scheduler Configuration
Event Syntax
Event Metadata
Event Scheduler Status
The Event Scheduler and MariaDB Privileges
Using Views
View Syntax
View Processing Algorithms
Updatable and Insertable Views
View Metadata
Access Control for Stored Programs and Views
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 execution.

Stored programs include these objects:

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

This chapter describes how to use stored programs and views. The following sections provide additional information about SQL syntax for statements related to these objects:

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

Using Stored Routines (Procedures and Functions)

Stored Routine Syntax
Stored Routines and MariaDB Privileges
Stored Routine Metadata
Stored Procedures, Functions, Triggers, and LAST_INSERT_ID()

Stored routines (procedures and functions) are supported in MariaDB 5.6. 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 MariaDB database. This table is created during the MariaDB 5.6 installation procedure. If you are upgrading to MariaDB 5.6 from an earlier version, be sure to update your grant tables to make sure that the proc table exists. See , "mysql_upgrade - Check Tables for MariaDB Upgrade".

Stored routines can be particularly useful in certain situations:

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 enable you to have libraries of functions in the database server. This is a feature shared by modern application languages that enable 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. All syntax described here is supported and any limitations and extensions are documented where appropriate.

Additional Resources

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 , "CREATE PROCEDURE and CREATE FUNCTION Syntax"). A procedure is invoked using a CALL statement (see , "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 , "MySQL Compound-Statement Syntax").

Stored routines can be dropped with the DROP PROCEDURE and DROP FUNCTION statements (see , "DROP PROCEDURE and DROP FUNCTION Syntax"), and altered with the ALTER PROCEDURE and ALTER FUNCTION statements (see , "ALTER PROCEDURE Syntax").

A stored procedure or function is associated with a particular database. This has several implications:

Stored functions cannot be recursive.

Recursion in stored procedures is permitted 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 , "Server System Variables", for more information.

MySQL supports a very useful extension that enables 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 MariaDB client library that supports multiple result sets. This means the client must use a client library from a version of MariaDB 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 , "mysql-real-connect()", and , "C API Support for Multiple Statement Execution".

Stored Routines and MariaDB Privileges

The MariaDB grant system takes stored routines into account as follows:

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.

Stored Routine Metadata

Metadata about stored routines can be obtained as follows:

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 , "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:

Using Triggers

Trigger Syntax
Trigger Metadata

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 views, nor by changes to tables made by APIs that do not transmit SQL statements to the MariaDB Server. This means that triggers are not activated by changes in INFORMATION_SCHEMA or performance_schema tables, because these tables are actually views.

To use triggers if you have upgraded to MariaDB 5.6 from an older release that did not support triggers, you should upgrade your grant tables so that they contain the trigger-related privileges. See , "mysql_upgrade - Check Tables for MariaDB Upgrade".

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

Additional Resources

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 , "CREATE TRIGGER Syntax", and , "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:

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 + 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 MariaDB 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 permitted 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:

MySQL handles errors during trigger execution as follows:

Trigger Metadata

Metadata about triggers can be obtained as follows:

Using the Event Scheduler

Event Scheduler Overview
Event Scheduler Configuration
Event Syntax
Event Metadata
Event Scheduler Status
The Event Scheduler and MariaDB Privileges

The MySQL Event Scheduler manages the scheduling and execution of events: Tasks that run according to schedule. The following discussion covers the Event Scheduler and is divided into the following sections:

Stored routines require the event table in the MariaDB database. This table is created during the MariaDB 5.6 installation procedure. If you are upgrading to MariaDB 5.6 from an earlier version, be sure to update your grant tables to make sure that the event table exists. See , "mysql_upgrade - Check Tables for MariaDB Upgrade".

Additional Resources

Event Scheduler Overview

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a "cron job") or the Windows Task Scheduler.

Scheduled tasks of this type are also sometimes known as "temporal triggers", implying that these are objects that are triggered by the passage of time. While this is essentially correct, we prefer to use the term events to avoid confusion with triggers of the type discussed in , "Using Triggers". Events should more specifically not be confused with "temporary triggers". Whereas a trigger is a database object whose statements are executed in response to a specific type of event that occurs on a given table, a (scheduled) event is an object whose statements are executed in response to the passage of a specified time interval.

While there is no provision in the SQL Standard for event scheduling, there are precedents in other database systems, and you may notice some similarities between these implementations and that found in the MariaDB Server.

MySQL Events have the following major features and properties:

Event Scheduler Configuration

Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread. When running, the event scheduler thread and its current state can be seen by users having the PROCESS privilege in the output of SHOW PROCESSLIST, as shown in the discussion that follows.

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. It has one of these 3 values, which affect event scheduling as described here:

If the Event Scheduler status has not been set to DISABLED, event_scheduler can be toggled between ON and OFF (using SET). It is also possible to use 0 for OFF, and 1 for ON when setting this variable. Thus, any of the following 4 statements can be used in the mysql client to turn on the Event Scheduler: 

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

Similarly, any of these 4 statements can be used to turn off the Event Scheduler: 

SET GLOBAL event_scheduler = OFF;
SET @@global.event_scheduler = OFF;
SET GLOBAL event_scheduler = 0;
SET @@global.event_scheduler = 0;

Although ON and OFF have numeric equivalents, the value displayed for event_scheduler by SELECT or SHOW VARIABLES is always one of OFF, ON, or DISABLED. DISABLED has no numeric equivalent. For this reason, ON and OFF are usually preferred over 1 and 0 when setting this variable.

Note that attempting to set event_scheduler without specifying it as a global variable causes an error: 

mysql< SET @@event_scheduler = OFF;
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOBAL variable and should be set with SET GLOBAL
Important

It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime.

To disable the event scheduler, use one of the following two methods:

To enable the Event Scheduler, restart the server without the --event-scheduler=DISABLED command-line option, or after removing or commenting out the line containing event-scheduler=DISABLED in the server configuration file, as appropriate. Alternatively, you can use ON (or 1) or OFF (or 0) in place of the DISABLED value when starting the server.Note

You can issue event-manipulation statements when event_scheduler is set to DISABLED. No warnings or errors are generated in such cases (provided that the statements are themselves valid). However, scheduled events cannot execute until this variable is set to ON (or 1). Once this has been done, the event scheduler thread executes all events whose scheduling conditions are satisfied.

Starting the MariaDB server with the --skip-grant-tables option causes event_scheduler to be set to DISABLED, overriding any other value set either on the command line or in the my.cnf or my.ini file (Bug #26807).

For SQL statements used to create, alter, and drop events, see , "Event Syntax".

MySQL 5.6 provides an EVENTS table in the INFORMATION_SCHEMA database. This table can be queried to obtain information about scheduled events which have been defined on the server. See , "Event Metadata", and , "The INFORMATION_SCHEMA EVENTS Table", for more information.

For information regarding event scheduling and the MariaDB privilege system, see , "The Event Scheduler and MariaDB Privileges".

Event Syntax

MySQL 5.6 provides several SQL statements for working with scheduled events:

Event Metadata

Metadata about events can be obtained as follows:

Event Scheduler Time Representation

Each session in MariaDB has a session time zone (STZ). This is the session time_zone value that is initialized from the server's global time_zone value when the session begins but may be changed during the session.

The session time zone that is current when a CREATE EVENT or ALTER EVENT statement executes is used to interpret times specified in the event definition. This becomes the event time zone (ETZ); that is, the time zone that is used for event scheduling and is in effect within the event as it executes.

For representation of event information in the mysql.event table, the execute_at, starts, and ends times are converted to UTC and stored along with the event time zone. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. The last_executed time is also stored in UTC.

If you select information from mysql.event, the times just mentioned are retrieved as UTC values. These times can also be obtained by selecting from the INFORMATION_SCHEMA.EVENTS table or from SHOW EVENTS, but they are reported as ETZ values. Other times available from these sources indicate when an event was created or last altered; these are displayed as STZ values. The following table summarizes representation of event times.

Value mysql.event INFORMATION_SCHEMA.EVENTS SHOW EVENTS
Execute at UTC ETZ ETZ
Starts UTC ETZ ETZ
Ends UTC ETZ ETZ
Last executed UTC ETZ n/a
Created STZ STZ n/a
Last altered STZ STZ n/a

Event Scheduler Status

The Event Scheduler writes information about event execution that terminates with an error or warning to the MariaDB Server's error log. See , "The Event Scheduler and MariaDB Privileges" for an example.

To obtain information about the state of the Event Scheduler for debugging and troubleshooting purposes, run mysqladmin debug (see , "mysqladmin - Client for Administering a MariaDB Server"); after running this command, the server's error log contains output relating to the Event Scheduler, similar to what is shown here: 

Events status:
LLA = Last Locked At LUA = Last Unlocked At WOC = Waiting On Condition DL = Data Locked Event scheduler status:
State : INITIALIZED Thread id : 0
LLA : init_scheduler:313
LUA : init_scheduler:318
WOC : NO Workers : 0
Executed : 0
Data locked: NO Event queue status:
Element count : 1
Data locked : NO Attempting lock : NO LLA : init_queue:148
LUA : init_queue:168
WOC : NO Next activation : 0000-00-00 00:00:00

In statements that occur as part of events executed by the Event Scheduler, diagnostics messages (not only errors, but also warnings) are written to the error log, and, on Windows, to the application event log. For frequently executed events, it is possible for this to result in many logged messages. For example, for SELECT ... INTO var_list statements, if the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). For either condition, you can avoid having the warnings be logged by declaring a condition handler; see , "DECLARE ... HANDLER Syntax". For statements that may retrieve multiple rows, another strategy is to use LIMIT 1 to limit the result set to a single row.

The Event Scheduler and MariaDB Privileges

To enable or disable the execution of scheduled events, it is necessary to set the value of the global event_scheduler system variable. This requires the SUPER privilege.

The EVENT privilege governs the creation, modification, and deletion of events. This privilege can be bestowed using GRANT. For example, this GRANT statement confers the EVENT privilege for the schema named myschema on the user jon@ghidora: 

GRANT EVENT ON myschema.* TO jon@ghidora;

(We assume that this user account already exists, and that we wish for it to remain unchanged otherwise.)

To grant this same user the EVENT privilege on all schemas, use the following statement: 

GRANT EVENT ON *.* TO jon@ghidora;

The EVENT privilege has global or schema-level scope. Therefore, trying to grant it on a single table results in an error as shown: 

mysql> GRANT EVENT ON myschema.mytable TO jon@ghidora;
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the manual to see which privileges can be used

It is important to understand that an event is executed with the privileges of its definer, and that it cannot perform any actions for which its definer does not have the requisite privileges. For example, suppose that jon@ghidora has the EVENT privilege for myschema. Suppose also that this user has the SELECT privilege for myschema, but no other privileges for this schema. It is possible for jon@ghidora to create a new event such as this one: 

CREATE EVENT e_store_ts
 ON SCHEDULE
 EVERY 10 SECOND
 DO
 INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());

The user waits for a minute or so, and then performs a SELECT * FROM mytable; query, expecting to see several new rows in the table. Instead, the table is empty. Since the user does not have the INSERT privilege for the table in question, the event has no effect.

If you inspect the MariaDB error log (hostname.err), you can see that the event is executing, but the action it is attempting to perform fails, as indicated by RetCode=0: 

060209 22:39:44 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:44 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:39:54 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:54 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:40:04 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:40:04 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0

Since this user very likely does not have access to the error log, it is possible to verify whether the event's action statement is valid by executing it directly: 

mysql> INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP());
ERROR 1142 (42000): INSERT command denied to user
'jon'@'ghidora' for table 'mytable'

Inspection of the INFORMATION_SCHEMA.EVENTS table shows that e_store_ts exists and is enabled, but its LAST_EXECUTED column is NULL: 

mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS
 > WHERE EVENT_NAME='e_store_ts'
 > AND EVENT_SCHEMA='myschema'\G
*************************** 1. row ***************************
 EVENT_CATALOG: NULL
 EVENT_SCHEMA: myschema
 EVENT_NAME: e_store_ts
 DEFINER: jon@ghidora
 EVENT_BODY: SQL EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
 EVENT_TYPE: RECURRING
 EXECUTE_AT: NULL
 INTERVAL_VALUE: 5
 INTERVAL_FIELD: SECOND
 SQL_MODE: NULL
 STARTS: 0000-00-00 00:00:00
 ENDS: 0000-00-00 00:00:00
 STATUS: ENABLED
 ON_COMPLETION: NOT PRESERVE
 CREATED: 2006-02-09 22:36:06
 LAST_ALTERED: 2006-02-09 22:36:06
 LAST_EXECUTED: NULL
 EVENT_COMMENT:
1 row in set (0.00 sec)

To rescind the EVENT privilege, use the REVOKE statement. In this example, the EVENT privilege on the schema myschema is removed from the jon@ghidora user account: 

REVOKE EVENT ON myschema.* FROM jon@ghidora;
Important

Revoking the EVENT privilege from a user does not delete or disable any events that may have been created by that user.

An event is not migrated or dropped as a result of renaming or dropping the user who created it.

Suppose that the user jon@ghidora has been granted the EVENT and INSERT privileges on the myschema schema. This user then creates the following event: 

CREATE EVENT e_insert
 ON SCHEDULE
 EVERY 7 SECOND
 DO
 INSERT INTO myschema.mytable;

After this event has been created, root revokes the EVENT privilege for jon@ghidora. However, e_insert continues to execute, inserting a new row into mytable each seven seconds. The same would be true if root had issued either of these statements:

You can verify that this is true by examining the mysql.event table (discussed later in this section) or the INFORMATION_SCHEMA.EVENTS table (see , "The INFORMATION_SCHEMA EVENTS Table") before and after issuing a DROP USER or RENAME USER statement.

Event definitions are stored in the mysql.event table. To drop an event created by another user account, the MariaDB root user (or another user with the necessary privileges) can delete rows from this table. For example, to remove the event e_insert shown previously, root can use the following statement: 

DELETE FROM mysql.event
 WHERE db = 'myschema'
 AND definer = 'jon@ghidora'
 AND name = 'e_insert';

It is very important to match the event name, database schema name, and user account when deleting rows from the mysql.event table. This is because the same user can create different events of the same name in different schemas.

Users' EVENT privileges are stored in the Event_priv columns of the mysql.user and mysql.db tables. In both cases, this column holds one of the values 'Y' or 'N'. 'N' is the default. mysql.user.Event_priv is set to 'Y' for a given user only if that user has the global EVENT privilege (that is, if the privilege was bestowed using GRANT EVENT ON *.*). For a schema-level EVENT privilege, GRANT creates a row in mysql.db and sets that row's Db column to the name of the schema, the User column to the name of the user, and the Event_priv column to 'Y'. There should never be any need to manipulate these tables directly, since the GRANT EVENT and REVOKE EVENT statements perform the required operations on them.

Five status variables provide counts of event-related operations (but not of statements executed by events; see "Restrictions on Stored Programs"). These are:

You can view current values for all of these at one time by running the statement SHOW STATUS LIKE '%event%';.

Using Views

View Syntax
View Processing Algorithms
Updatable and Insertable Views
View Metadata

Views (including updatable views) are available in MariaDB Server 5.6. Views are stored queries that when invoked produce a result set. A view acts as a virtual table.

To use views if you have upgraded to MariaDB 5.6 from an older release that did not support views, you should upgrade your grant tables so that they contain the view-related privileges. See , "mysql_upgrade - Check Tables for MariaDB Upgrade".

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

Additional Resources

View Syntax

The CREATE VIEW statement creates a new view (see , "CREATE VIEW Syntax"). To alter the definition of a view or drop a view, use ALTER VIEW (see , "ALTER VIEW Syntax"), or DROP VIEW (see , "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 |
+------+-------+-------+

View Processing Algorithms

The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MariaDB extension to standard SQL. It affects how MariaDB 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, MariaDB 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:

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:

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:

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:

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:

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

UPDATE v SET col1 = 0;

This update is not permissible 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 not permitted 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 , "Server System Variables".

View Metadata

Metadata about views can be obtained as follows:

Access Control for Stored Programs and Views

Stored programs and views are defined prior to use and, when referenced, execute within a security context that determines their privileges. These privileges are controlled by their DEFINER attribute, and, if there is one, their SQL SECURITY characteristic.

All stored programs (procedures, functions, triggers, and events) and views can have a DEFINER attribute that names a MariaDB account. If the DEFINER attribute is omitted from a stored program or view definition, the default account is the user who creates the object.

In addition, stored routines (procedures and functions) and views can have a SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If the SQL SECURITY characteristic is omitted, the default is definer context.

Triggers and events have no SQL SECURITY characteristic and always execute in definer context. The server invokes these objects automatically as necessary, so there is no invoking user.

Definer and invoker security contexts differ as follows:

Consider the following stored procedure: 

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p1()
SQL SECURITY DEFINER BEGIN
 UPDATE t1 SET counter = counter + 1;
END;

Any user who has the EXECUTE privilege for p1 can invoke it with a CALL statement. However, when p1 executes, it does so in DEFINER security context and thus executes with the privileges of 'admin'@'localhost', the account named in the DEFINER attribute. This account must have the EXECUTE privilege for p1 as well as the UPDATE privilege for the table t1. Otherwise, the procedure fails.

Now consider this stored procedure, which is identical to p1 except that its SQL SECURITY characteristic is INVOKER: 

CREATE DEFINER = 'admin'@'localhost' PROCEDURE p2()
SQL SECURITY INVOKER BEGIN
 UPDATE t1 SET counter = counter + 1;
END;

p2, unlike p1, executes in INVOKER security context. The DEFINER attribute is irrelevant and p2 executes with the privileges of the invoking user. p2 fails if the invoker lacks the EXECUTE privilege for p2 or the UPDATE privilege for the table t1.

MySQL uses the following rules to control which accounts a user can specify in an object DEFINER attribute:

To minimize the risk potential for stored program and view creation and use, follow these guidelines:

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:

However, there are certain binary logging issues that apply with respect to stored programs (stored procedures and functions, triggers, and events), if logging occurs at the statement level:

This section describes how MariaDB 5.6 handles binary logging for stored programs. It states the current conditions that the implementation places on the use of stored programs, and what you can do to avoid problems. It also provides additional information about the reasons for these conditions.

In general, the issues described here result when binary logging occurs at the SQL statement level. If you use row-based binary logging, the log contains changes made to individual rows as a result of executing SQL statements. When routines or triggers execute, row changes are logged, not the statements that make the changes. For stored procedures, this means that the CALL statement is not logged. For stored functions, row changes made within the function are logged, not the function invocation. For triggers, row changes made by the trigger are logged. On the slave side, only the row changes are seen, not the stored program invocation. For general information about row-based logging, see , "Replication Formats".

Unless noted otherwise, the remarks here assume that you have enabled binary logging by starting the server with the --log-bin option. (See , "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 MariaDB 5.6 can be summarized as follows. These conditions do not apply to stored procedures or Event Scheduler events and they do not apply unless binary logging is enabled.

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 additional detail about the logging implementation and its implications. You need not read it unless you are interested in the background on the rationale for the current logging-related conditions on stored routine use. This discussion applies only for statement-based logging, and not for row-based logging, with the exception of the first item: CREATE and DROP statements are logged as statements regardless of the logging mode.

Copyright 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices
Prev Next
Partitioning Home Chapter 18. INFORMATION_SCHEMA Tables