Storage of Stored Programs is implemented using either tables in
the database (in the
mysql schema), or physical
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
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
mysql.procs_priv describes privileges
granted for a given Stored Procedure or Stored Function in
The code used to encapsulate database access is:
Trigger definitions are stored in plain text files in the directory that contains the schema objects.
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
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.
TRIGGERS file. It represents all the
table triggers attached to a given table, so this file can
contain triggers for multiple events
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:
See the C++ class
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
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
The code used to encapsulate the database access is:
See the C++ class
Some critical attributes, such as
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:
USE <database> in effect for a
stored program is the
schema the stored
object belongs to.
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 the
stored object belongs to.