[+/-]
Storage of Stored Programs is implemented using either tables in
the database (in the mysql schema), or physical
files.
The table mysql.proc contains one record per
Stored Procedure or Stored Function. Note that this table design
is a mix of relational and non relational (blob) content:
Attributes that are part of the interface of a stored procedure or function (like its name, return type, etc), or that are global to the object (implementation language, deterministic properties, security properties, sql mode, etc) are stored with a dedicated column in the
mysql.proctable.The body of a stored procedure or function, which consists of the original code expressed in SQL, including user comments if any, is stored as-is preserving the original indentation in blob column 'body'.
This design choice allows the various attributes to be representedin a format that is easy to work with (relational model), while allowing a lot of flexibility for the content of the body.
A minor exception to this is the storage of the parameters of a
stored procedure or function (which are part of its interface)
inside the blob column param_list (instead of
using a child proc_param table).
Table mysql.procs_priv describes privileges
granted for a given Stored Procedure or Stored Function in
mysql.proc.
The code used to encapsulate database access is:
db_create_routine()db_load_routine()db_drop_routine()mysql_routine_grant()grant_load()grant_reload()
Trigger definitions are stored in plain text files in the directory that contains the schema objects.
The file <schema>/<trigger>.TRN
is the TRIGGERNAME file. It represents the
fact that the object named “trigger” is a table
trigger, and points to the table the trigger is attached to.
Every trigger has a dedicated *.TRN file.
This design decision is used to facilitate operating system
filesystem services to enforce the SQL standard requirement that
all triggers in a given schema must be unique.
The file <schema>/<table>.TRG
is the TRIGGERS file. It represents all the
table triggers attached to a given table, so this file can
contain triggers for multiple events
(BEFORE/AFTER,
INSERT/UPDATE/DELETE).
Currently it is impossible to have more than one trigger per
table for a given trigger action time and type, hence this file
may contain at most six trigger definitions.
The code used to encapsulate file access is:
Table_triggers_list::create_trigger()Table_triggers_list::drop_trigger()Table_triggers_list::check_n_load()Table_triggers_list::drop_all_triggers()Table_triggers_list::change_table_name()See the C++ class
Table_triggers_listin general.
Using files for triggers is due to historical reasons, and
follows the same design as *.frm files for
table metadata. This approach has several drawbacks:
Each file has yet another text file format, which is necessary to print and parse back correctly. Custom code has to be implemented, which is consuming in terms of resources, and introduces technical risk or code duplication.
Tables are replicated, values in columns are checked for data validity, integrity constraints can be defined ... where none of the above is available with a file based implementation.
With tables, the default locking, transaction and isolation mechanism used by the server in general can be leveraged, but the same is not available with files.
Cluster support for any new metadata operation that operates on files will require a custom solution. E.g. to propagate CREATE TABLE statement across MySQL Cluster mysqld nodes we use a so-called “.frm shipping” technique. There is no similar solution implemented for triggers at this point, and thus a trigger created in one mysqld node does not automatically become visible on other nodes. Potentially, if data is stored in tables, cluster support may be added as simply as by issuing
ALTER TABLE mysql.triggers ENGINE=NDB;
Various drawbacks of filesystem based solution are provided in this chapter only for a sake of example. Other advantages and disadvantages of two approaches may be found in relevant worklog entries and design documents.
The current implementation of the storage layer for table triggers is considered private to the server, and might change without warnings in future releases.
Events storage is very similar to Stored Procedure and Stored
Function storage, and shares the same design. Since more
attributes are needed to represent an event, a different table
is used: mysql.event.
The code used to encapsulate the database access is:
Event_db_repository::create_event()Event_db_repository::update_event()Event_db_repository::drop_event()See the C++ class
Event_db_repositoryin general.
Some critical attributes, such as SQL_MODE,
are explicitly part of the storage format.
Other attributes, that also impact significantly the behavior in general of Stored Programs, can be implicitly derived from other properties of the storage layer. In particular:
The
USE <database>in effect for a stored program is theschemathe stored object belongs to.The statement
DECLARE v CHAR(10)does not intrinsically convey any notion of character set or collation. The character set and collation of this local variable, in a stored program, derives from the character set and collation of theschemathe stored object belongs to.
