Note: This page is obsolete. The information is old, outdated, or otherwise currently incorrect. We are keeping the page for historical reasons only. Do not rely on the information in this article.

We merge TokuDB from Tokutek git repositories on GutHub:

• tokudb-engine

• ft-index

Just merge normally at release points (use tag names) and don't forget to update storage/tokudb/CMakeLists.txt, setting TOKUDB_VERSION correctly.

_{This page is licensed: CC BY-SA / Gnu FDL}

About

Generally speaking, writing plugins for MariaDB is very similar to writing plugins for MySQL.

Authentication Plugins

See .

Note: This page is obsolete. The information is old, outdated, or otherwise currently incorrect. We are keeping the page for historical reasons only. Do not rely on the information in this article.

Background

Percona used to maintain XtraDB as a patch series against the InnoDB plugin. This affected how we started merging XtraDB in.

Now Percona maintains a normal source repository on launchpad (lp:percona-server). But we continue to merge the old way to preserve the history of our changes.

This article is obsolete. We don't use bzr anymore. This howto needs to be rewritten to explain how to create a merge tree in git.

Merge tree in the context of this HOWTO is a tree created specifically to simplify merges of third-party packages into MariaDB. WIth a merge tree there's a clear separation between upstream changes and our changes and in most cases bzr can do the merges automatically.

Here's how I created a merge tree for pcre:

• prerequisites: we already have pcre in the MariaDB tree, together with our changes (otherwise one can trivially create a bzr repository out of source pcre tarball).

• create an empty repository:

mkdir pcre
cd pcre
bzr init

• download pcre source tarball of the same version that we have in the tree — pcre-8.34.tar.bz2

• unpack it in the same place where the files are in the source tree:

• Add files to the repository with the same file-ids as in the MariaDB tree!

• All done. Commit and push

• Now null-merge that into your MariaDB tree. Note, that for the initial merge you need to specify the revision range 0..1

• Remove pcre files that shouldn't be in MariaDB tree, revert all changes that came from pcre (remember — it's a null-merge, pcre-8.34 is already in MariaDB tree), rename files in place as needed, resolve conflicts:

• Verify that the tree is unchanged and commit:

• Congratulations, your new merge tree is ready!

Now see .

_{This page is licensed: CC BY-SA / Gnu FDL}

Information Schema plugins can support and statements.

SHOW

SHOW statements support is enabled automatically. A plugin only needs to specify column names for the SHOW statement in the old_name member of the field declaration structure. Columns with the old_name set to 0 will be hidden from the SHOW

Note: This page is obsolete. The information is old, outdated, or otherwise currently incorrect. We are keeping the page for historical reasons only. Do not rely on the information in this article.

Merging from MySQL into MariaDB

“Password validation” means ensuring that user passwords meet certain minimal security requirements. A dedicated plugin API allows the creation of password validation plugins that will check user passwords as they are set (in and statements) and either allow or reject them.

SQL-Level Extensions

MariaDB comes with three password validation plugins — the simple_password_check plugin, the cracklib_password_check plugin and the password_reuse_check plugin. They are not enabled by default; use (or ) statement to install them.

When at least one password plugin is loaded, all new passwords will be validated and password-changing statements will fail if the password will not pass validation checks. Several password validation plugin can be loaded at the same time — in this case a password must pass all validation checks by all plugins.

Password-Changing Statements

One can use various SQL statements to change a user password:

With Plain Text Password

These statements are subject to password validation. If at least one password validation plugin is loaded, plain-text passwords specified in these statements will be validated.

With Password Hash

These statements can not possibly use password validation — there is nothing to validate, the original plain-text password is not available. MariaDB introduces a strict password validation mode — controlled by a global server variable. If the strict password validation is enabled and at least one password validation plugin is loaded then these “unvalidatable” passwords will be rejected. Otherwise they will be accepted. By default a strict password validation is enabled (but note that it has no effect if no password validation plugin is loaded).

Examples

Failed password validation:

Strict password validation:

Plugin API

Password validation plugin API is very simple. A plugin must implement only one method — validate_password(). This method takes two arguments — user name and the plain-text password. And it returns 0 when the password has passed the validation and 1 otherwise,

See also mysql/plugin_password_validation.h and password validation plugins in plugin/simple_password_check/ and plugins/cracklib_password_check/.

_{This page is licensed: CC BY-SA / Gnu FDL}

If you have a merge tree, you merge into MariaDB as follows:

1. MariaDB merge trees are in the mergetrees repository. Add it as a new remote:

1. Check out the branch you want to update and merge, for example:

1. delete everything in the branch

2. download the latest released source tarball, unpack it, copy files into the repository:

• for InnoDB-5.6: use the content of the storage/innobase/ of the latest MySQL 5.6 source release tarball.

• for performance schema 5.6: use storage/perfschema, include/mysql/psi, mysql-test/suite/perfschema, and mysql-test/suite/perfschema_stress from the latest MySQL 5.6 source release tarball.

1. Now git add ., git commit (use the tarball version as a comment), git push

2. merge this branch into MariaDB

3. Sometimes after a merge, some changes may be needed:

• for performance schema 5.6: update storage/perfschema/ha_perfschema.cc, plugin version under maria_declare_plugin.

• for InnoDB-5.6: update storage/innobase/include/univ.i, setting INNODB_VERSION_MAJOR, INNODB_VERSION_MINOR, INNODB_VERSION_BUGFIX to whatever MySQL version you were merging from.

_{This page is licensed: CC BY-SA / Gnu FDL}

When creating a connection, a THD object is created for that connection. This contains all connection information and also caches to speed up queries and avoid frequent malloc() calls.

When creating a new connection, the following malloc() calls are done for the THD:

The following information is the state in MariaDB 10.6.1 when compiled without debugging.

Local Thread Memory

This is part of select memory_used from information_schema.processlist.

Objects Stored in THD->memroot During Connect

State at First Call to mysql_execute_command

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB, starting with version 5.5.21 supports non-blocking operations in the client-library. This allows an application to start a query or other operation against the database, and then continue to do other work (in the same thread) while the request is sent over the network, the query is processed in the server, and the result travels back. As parts of the result become ready, the application can — at its leisure — call back into the library to continue processing, repeating this until the operation is completed.

Non-blocking operation is implemented entirely within the client library. This means no special server support is necessary and non-blocking operation works with any version of the MariaDB or MySQL server, the same as the normal blocking API. It also means that it is not possible to have two queries running at the same time on the same connection (this is a protocol limitation). But a single thread can have any number of non-blocking queries running at the same time, each using its own MYSQL connection object.

Non-blocking operation is useful when an application needs to run a number of independent queries in parallel at the same time, to speed up operation compared to running them sequentially one after the other. This could be multiple queries against a single server (to better utilize multiple CPU cores and/or a high-capacity I/O system on the server), or it could be queries against multiple servers (e.g. SHOW STATUS against all running servers for monitoring, or a map/reduce-like operation against a big sharded database).

Non-blocking operation is also very useful in applications that are already written in a non-blocking style, for example using a framework like libevent, or, for example, a GUI-application using an event loop. Using the non-blocking client library allows the integrations of database queries into such applications, without the risk of long-running queries "hanging" the user interface or stalling the event loop, and without having to manually spawn separate threads to run the queries and re-synchronize with the threads to get the results back.

In this context, "blocking" means the situation where communication on the network socket to the server has to wait while processing the query. Waiting can be necessary because the server has not yet had time to process the query, or because the data needs to travel over the network from the server, or even because the first part of a large request needs to be sent out on the network before local socket buffers can accept the last part. Whenever such a wait is necessary, control returns to the application. The application will then runselect() or poll() (or something similar) to detect when any wait condition is satisfied, and then call back into the library to continue processing.

An example program is available in the MariaDB source tree:

It uses libevent to run a set of queries in parallel from within a single thread / event loop. This is a good example of how to integrate non-blocking query processing into an event-based framework.

The non-blocking API in the client library is entirely optional. The new library is completely ABI- and source-compatible with existing applications. Also, applications not using non-blocking operations are not affected, nor is there any significant performance penalty for having support for non-blocking operations in the library for applications which do not use them.

The library internally uses co-routines, and requires a co-routine implementation to work. Native implementations are included for i386, amd64, and (since Connector/C version 3.3.12) aarch64 architectures. For other architectures, a fallback to ucontext is automatically used if available. An alternate fallback boost::context can also be used instead of ucontext by building with -DWITH_BOOST_CONTEXT=ON (boost::context is not used by default). If no co-routine implementation is available the non-blocking operations are disabled and will not work.

_{This page is licensed: CC BY-SA / Gnu FDL}

The embedded MariaDB server, libmysqld has the identical interface as the libmysqclient.

The normal usage of the embedded server is to use the normal mysql.h include file in your application and link with libmysqld instead of libmysqlclient.

The intention is that one should be able to move from a server/client version of MariaDB to a single server version of MariaDB by just changing which library you link with.

This means that the embedded C client API only changes when the normal C API changes, usually only between major releases.

The only major change required in your application if you are going to use the embedded server is that you have to call the following functions from your application:

MariaDB supports progress reporting for some long running commands.

What is Progress Reporting?

Progress reporting means that:

• There is a Progress column in which shows the total progress (0-100%)

The MariaDB non-blocking client API is modelled after the normal blocking library calls. This makes it easy to learn and remember. It makes it easier to translate code from using the blocking API to using the non-blocking API (or vice versa). And it also makes it simple to mix blocking and non-blocking calls in the same code path.

For every library call that may block on socket I/O, such as 'int mysql_real_query(MYSQL, query, query_length)', two additional non-blocking calls are introduced:

To do non-blocking operation, an application first callsmysql_real_query_start() instead of mysql_real_query(), passing the same parameters.

If mysql_real_query_start() returns zero, then the operation completed without blocking, and 'status' is set to the value that would normally be returned from mysql_real_query().

Else, the return value from mysql_real_query_start() is a bitmask of events that the library is waiting on. This can be MYSQL_WAIT_READ,MYSQL_WAIT_WRITE, or MYSQL_WAIT_EXCEPT, corresponding to the similar flags for select() or poll(); and it can include MYSQL_WAIT_TIMEOUT when waiting for a timeout to occur (e.g. a connection timeout).

In this case, the application continues other processing and eventually checks for the appropriate condition(s) to occur on the socket (or for timeout). When this occurs, the application can resume the operation by callingmysql_real_query_cont(), passing in 'wait_status' a bitmask of the events which actually occurred.

Just like mysql_real_query_start(), mysql_real_query_cont() returns zero when done, or a bitmask of events it needs to wait on. Thus the application continues to repeatedly call mysql_real_query_cont(), intermixed with other processing of its choice; until zero is returned, after which the result of the operation is stored in 'status'.

Some calls, like mysql_option(), do not do any socket I/O, and so can never block. For these, there are no separate _start() or _cont() calls. See the "" page for a full list of what functions can and can not block.

The checking for events on the socket / timeout can be done with select() or poll() or a similar mechanism. Though often it will be done using a higher-level framework (such as libevent), which supplies facilities for registering and acting on such conditions.

The descriptor of the socket on which to check for events can be obtained by calling mysql_get_socket(). The duration of any timeout can be obtained from mysql_get_timeout_value().

Here is a trivial (but full) example of running a query with the non-blocking API. The example is found in the MariaDB source tree asclient/async_example.c. (A larger, more realistic example using libevent is found as tests/async_queries.c in the source):

Setting MYSQL_OPT_NONBLOCK

Before using any non-blocking operation, it is necessary to enable it first by setting the MYSQL_OPT_NONBLOCK option:

This call can be made at any time — typically it will be done at the start, before mysql_real_connect(), but it can be done at any time to start using non-blocking operations.

If a non-blocking operation is attempted without setting theMYSQL_OPT_NONBLOCK option, the program will typically crash with a NULL pointer exception.

The argument for MYSQL_OPT_NONBLOCK is the size of the stack used to save the state of a non-blocking operation while it is waiting for I/O and the application is doing other processing. Normally, applications will not have to change this, and it can be passed as zero to use the default value.

Mixing blocking and non-blocking operation

It is possible to freely mix blocking and non-blocking calls on the sameMYSQL connection.

Thus, an application can do a normal blocking mysql_real_connect() and subsequently do a non-blocking mysql_real_query_start(). Or vice versa, do a non-blocking mysql_real_connect_start(), and later do a blockingmysql_real_query() on the resulting connection.

Mixing can be useful to allow code to use the simpler blocking API in parts of the program where waiting is not a problem. For example establishing the connection(s) at program startup, or doing small quick queries between large, long-running ones.

The only restriction is that any previous non-blocking operation must have finished before starting a new blocking (or non-blocking) operation, see the next section: "Terminating a non-blocking operation early" below.

Terminating a non-blocking operation early

When a non-blocking operation is started with mysql_real_query_start() or another _start() function, it must be allowed to finish before starting a new operation. Thus, the application must continue calling mysql_real_query_cont() until zero is returned, indicating that the operation is completed. It is not allowed to leave one operation "hanging" in the middle of processing and then start a new one on top of it.

It is, however, permissible to terminate the connection completely withmysql_close() in the middle of processing a non-blocking call. A new connection must then be initiated with mysql_real_connect before new queries can be run, either with a new MYSQL object or re-using the old one.

In the future, we may implement an abort facility to force an on-going operation to terminate as quickly as possible (but it will still be necessary to call mysql_real_query_cont() one last time after abort, allowing it to clean up the operation and return immediately with an appropriate error code).

Restrictions

DNS

When mysql_real_connect_start() is passed a hostname (as opposed to a local unix socket or an IP address, it may need to look up the hostname in DNS, depending on local host configuration (e.g. if the name is not in/etc/hosts or cached). Such DNS lookups do not happen in a non-blocking way. This means that mysql_real_connect_start() will not return control to the application while waiting for the DNS response. Thus the application may "hang" for some time if DNS is slow or non-functional.

If this is a problem, the application can pass an IP address tomysql_real_connect_start() instead of a hostname, which avoids the problem. The IP address can be obtained by the application with whatever non-blocking DNS loopup operation is available to it from the operating system or event framework used. Alternatively, a simple solution may be to just add the hostname to the local host lookup file (/etc/hosts on Posix/Unix/Linux machines).

Windows Named Pipes and Shared Memory connections

There is no support in the non-blocking API for connections using Windows named pipes or shared memory

Named pipes and shared memory can still be used, using either the blocking or the non-blocking API. However, operations that need to wait on I/O on the named pipe will not return control to the application; instead they will "hang" waiting for the operation to complete, just like the normal blocking API calls.

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB's requires the use of a . These plugins are responsible both for the management of encryption keys and for the actual encryption and decryption of data.

MariaDB supports the use of . Each encryption key uses a 32-bit integer as a key identifier. If the specific plugin supports , then encryption keys can also be rotated, which creates a new version of the encryption key.

See and for more information.

Encryption Plugin API

The Encryption plugin API was created to allow a plugin to:

The mariadb-test program runs a test case against a MariaDB or MySQL server and optionally compares the output with a result file. This program reads input written in a special test language. Typically, you invokemariadb-test via rather than invoking it directly.

mariadb-test_embedded is similar but is built with support for the libmariadbd embedded server.

Features of mariadb-test:

• Can send SQL statements to the server for execution

Here is a list of all functions in the non-blocking client API and their parameters. Apart from operating in a non-blocking way, they all work exactly the same as their blocking counterparts, so their exact semantics can be obtained from the documentation of the normal client API.

The API also contains the following three functions which are used to get the socket fd and timeout values when waiting for events to occur:

Return the descriptor of the socket used for the connection.

This should only be called when a _start() or _cont() function returns a value with the MYSQL_WAIT_TIMEOUT flag set. In this case, it returns the value, in seconds, after which a timeout has occured and the application should call the appropriate _cont() function passingMYSQL_WAIT_TIMEOUT as the event that occured.

This is used to handle connection and read timeouts.

This function is available starting from and .

Like mysql_get_timeout_value(), this should only be called when a _start() or _cont() function returns a value with the MYSQL_WAIT_TIMEOUT flag set. In this case, it returns the value, in millisecondsseconds, after which a timeout has occured and the application should call the appropriate _cont() function passing MYSQL_WAIT_TIMEOUT as the event that occured.

The difference to mysql_get_timeout_value() is that this provides millisecond resolution for timeouts, rather than just whole seconds. In , internal timeouts can now be in milliseconds, while in 5.5 and below it was only whole seconds.

This milliseconds version is provided also in (from 5.5.28 onwards) to make it easier for applications to work with either library version. However, in 5.5 it always returns a multiple of 1000 milliseconds.

is a list of all functions from the normal API which can be used safely in a non-blocking program, since they never need to block.

mysql_real_connect_start() initiates a non-blocking connection request to a server.

When mysql_real_connect_start() or mysql_real_connect_cont() returns zero, a copy of the passed 'mysql' argument is stored in *ret.

Initiate fetch of another row from a SELECT query.

If the MYSQL_RES was obtained from mysql_use_result(), then this function allows stream processing, where initial rows are returned to the application while the server is still sending subsequent rows. When no more data is available on the socket, mysql_fetch_row_start() ormysql_fetch_row_cont() will return MYSQL_WAIT_READ (or possiblyMYSQL_WAIT_WRITE if using TLS and TLS re-negotiation is needed; alsoMYSQL_WAIT_TIMEOUT may be set if read timeout is enabled). When data becomes available, more rows can be fetched with mysql_fetch_row_cont().

If the MYSQL_RES was obtained from mysql_store_result() /mysql_store_result_start() / mysql_store_result_cont(), then this function cannot block — mysql_fetch_row_start() will always return 0 (and if desired, plain mysql_fetch_row() may be used instead with equivalent effect).

This function can need to wait if not all rows were fetched before it was called (then it needs to consume any pending rows sent from the server so they do not interfere with any subsequent queries sent).

If all rows were already fetched, then this function will not need to wait.mysql_free_result_start() will return zero (or if so desired, plainmysql_free_result() may be used instead).

Note that mysql_free_result() returns no value, so there is no extra 'ret' parameter for mysql_free_result_start() ormysql_free_result_cont().

mysql_close() sends a COM_QUIT request to the server, though it does not wait for any reply.

Thus teoretically it can block (if the socket buffer is full), though in practise it is probably unlikely to occur frequently.

The non-blocking version of mysql_close() is provided for completeness; for many applications using the normal mysql_close() is probably sufficient (and may be simpler).

Note that mysql_close() returns no value, so there is no extra 'ret' parameter for mysql_close_start() or mysql_close_cont().

Client API functions which never block

The following client API functions never need to do I/O and thus can never block. Therefore, they can be used as normal in programs using non-blocking operations; no need to call any special _start() variant. (Even if a_start() variant was available, it would always return zero, so no_cont() call would ever be needed).

• mysql_eof()``

_{This page is licensed: CC BY-SA / Gnu FDL}

Implementation Specification for Stored Procedures

How Parsing and Execution of Queries Work

In order to execute a query, the function sql_parse.cc:mysql_parse() is called, which in turn calls the parser (yyparse()) with an updated Lex structure as the result. mysql_parse() then calls mysql_execute_command() which dispatches on the command code (in Lex) to the corresponding code for executing that particular query.

There are three structures involved in the execution of a query which are of interest to the implementation:

• Lex (mentioned above) is the "compiled" query, that is the output from the parser and what is then interpreted to do the actual work. It constains an enum value (sql_command) which is the query type, and all the data collected by the parser needed for the execution (table names, fields, values, etc).

• THD is the "run-time" state of a connection, containing all that is needed for a particular client connection, and, among other things, the Lex structure currently being executed.

• Item_*: During parsing, all data is translated into "items", objects of the subclasses of "Item", such as Item_int, Item_real,

How to Fit Stored Procedures into this Scheme

Overview of the Classes and Files for Stored Procedures

(More detailed APIs at the end of this page)

class sp_head (sp_head.{cc,h})

This contains, among other things, an array of "instructions" and the method for executing the procedure.

class sp_pcontext (sp_pcontext.{cc,h}

This is the parse context for the procedure. It's primarily used during parsing to keep track of local parameters, variables and labels, but it's also used at time to find the parameters mode (IN, OUT or INOUT) and type when setting up the runtime context.

class sp_instr (sp_head.{cc,h})

This is the base class for "instructions", that is, what is generated by the parser. It turns out that we only need a minimum of 5 different sub classes:

• sp_instr_stmt Execute a statement. This is the "call-out" any normal SQL statement, like a , etc. It contains the Lex structure for the statement in question.

• sp_instr_set Set the value of a local variable (or parameter)

• sp_instr_jump An unconditional jump.

• sp_instr_jump_if_not Jump if condition is not true. It turns out that the negative test is most convenient when generating the code for the flow control constructs.

class sp_rcontext (sp_rcontext.h)

This is the runtime context in the THD structure. It contains an array of items, the parameters and local variables for the currently executing stored procedure. This means that variable value lookup is in runtime is constant time, a simple index operation.

class Item_splocal (Item.{cc,h})

This is a subclass of Item. Its sole purpose is to hide the fact that the real Item is actually in the current frame (runtime context). It contains the frame offset and defers all methods to the real Item in the frame. This is what the parser generates for local variables.

Utility Functions (sp.{cc,h})

This contains functions for creating, dropping and finding a stored procedure in the (or the internal cache).

Parsing CREATE PROCEDURE

When parsing a the parser first initializes thesphead and spcont (runtime context) fields in the Lex. The sql_command code for the result of parsing a isSQLCOM_CREATE_PROCEDURE.

The parsing of the parameter list and body is relatively straightforward:

• Parameters: name, type and mode (IN/OUT/INOUT) is pushed to spcont

• Declared local variables: Same as parameters (mode is then IN)

• Local Variable references: If an identifier is found in spcont, an Item_splocal is created with the variable's frame index, otherwise an Item_field or Item_ref

A Simple Example

Parsing the procedure:

would generate the following structures:

Note that the contents of the spcont is changing during the parsing, at all times reflecting the state of the would-be runtime frame. The m_instr is an array of instructions:

Here, '3', 'x>0', etc, represent the Items or Lex for the respective expressions or statements.

Parsing CREATE FUNCTION

is essentially the same thing as for a PROCEDURE, with the addition that a FUNCTION has a return type and a RETURN statement, but no OUT or INOUT parameters.

The main difference during parsing is that we store the result type in the sp_head. However, there are big differences when it comes to invoking a FUNCTION. (See below.)

Storing, Caching, Dropping

As seen above, the entired definition string, including the "CREATE PROCEDURE" (or "FUNCTION") is kept. The procedure definition string is stored in the table mysql.proc with the name and type as the key, the type being one of the enum ("procedure","function").

A PROCEDURE is just stored in the . A FUNCTION has an additional requirement. They will be called in expressions with the same syntax as UDFs, so UDFs and stored FUNCTIONs share the namespace. Thus, we must make sure that we do not have UDFs and FUNCTIONs with the same name (even if they are stored in different places).

This means that we can reparse the procedure as many time as we want. The first time, the resulting Lex is used to store the procedure in the database (using the function sp.c:sp_create_procedure()).

The simplest way would be to just leave it at that, and re-read the procedure from the database each time it is called. (And in fact, that's the way the earliest implementation will work.) However, this is not very efficient, and we can do better. The full implementation should work like this:

1. Upon creation time, parse and store the procedure. Note that we still need to parse it to catch syntax errors, but we can't check if called procedures exists for instance.

2. Upon first CALL, read from the database, parse it, and cache the resulting Lex in memory. This time we can do more error checking.

3. Upon subsequent CALLs, use the cached Lex.

Note that this implies that the Lex structure with its sphead must be reentrant, that is, reusable and shareable between different threads and calls. The runtime state for a procedure is kept in the sp_rcontext in THD.

The mechanisms of storing, finding, and dropping procedures are encapsulated in the files sp.{cc,h}.

CALLing a Procedure

A is parsed just like any statement. The resulting Lex has the sql_command SQLCOM_CALL, the procedure's name and the parameters are pushed to the Lex' value_list.

sql_parse.cc:mysql_execute_command() then uses sp.cc:sp_find() to get the sp_head for the procedure (which may have been read from the database or fetched from the in-memory cache) and calls the sp_head's method execute(). Note: It's important that substatements called by the procedure do not do send_ok(). Fortunately, there is a flag in THD->net to disable this during CALLs. If a substatement fails, it will however send an error back to the client, so the CALL mechanism must return immediately and without sending an error.

The sp_head::execute() method works as follows:

1. Keep a pointer to the old runtime context in THD (if any)

2. Create a new runtime context. The information about the required size is in sp_head's parse time context.

3. Push each parameter (from the CALL's Lex->value_list) to the new context. If it's an OUT or INOUT parameter, the parameter's offset in the caller's frame is set in the new context as well.

4. For each instruction, call its execute() method. The result is a pointer to the next instruction to execute (or NULL) if an error occurred.

USE database

Before executing the instruction we also keeps the current default database (if any). If this was changed during execution (i.e. a statement has been executed), we restore the current database to the original.

This is the most useful way to handle USE in procedures. If we didn't, the caller would find himself in a different database after calling a function, which can be confusing. Restoring the database also gives full freedom to the procedure writer:

• It's possible to write "general" procedures that are independent of the actual database name.

• It's possible to write procedures that work on a particular database by calling USE, without having to use fully qualified table names everywhere (which doesn't help if you want to call other, "general", procedures anyway).

Evaluating Items

There are three occasions where we need to evaluate an expression:

• When SETing a variable

• When CALLing a procedure

• When testing an expression for a branch (in IF, WHILE, etc)

The semantics in stored procedures is "call-by-value", so we have to evaluate any "func" Items at the point of the CALL or SET, otherwise we would get a kind of "lazy" evaluation with unexpected results with respect to OUT parameters for instance. For this the support function, sp_head.cc:eval_func_item() is needed.

Calling a FUNCTION

Functions don't have an explicit call keyword like procedures. Instead, they appear in expressions with the conventional syntax "fun(arg, ...)". The problem is that we already have (UDFs) which are called the same way. A UDF is detected by the lexical analyzer (not the parser!), in the find_keyword() function, and returns a UDF_*_FUNC or UDA_*_SUM token with the udf_func object as the yylval.

So, stored functions must be handled in a similar way, and as a consequence, UDFs and functions must not have the same name.

Detecting and Parsing a FUNCTION Invocation

The existence of UDFs are checked during the lexical analysis (in sql_lex.cc:find_keyword()). This has the drawback that they must exist before they are referred to, which was ok before SPs existed, but then it becomes a problem. The first implementation of SP FUNCTIONs will work the same way, but this should be fixed a.s.a.p. (This will required some reworking of the way UDFs are handled, which is why it's not done from the start.) For the time being, a FUNCTION is detected the same way, and returns the token SP_FUNC. During the parsing we only check for the existence of the function, we don't parse it, since wa can't call the parser recursively.

When encountering a SP_FUNC with parameters in the expression parser, an instance of the new Item_func_sp class is created. Unlike UDFs, we don't have different classes for different return types, since we at this point don't know the type.

Collecting FUNCTIONs to invoke

A FUNCTION differs from a PROCEDURE in one important aspect: Whereas a PROCEDURE is CALLed as statement by itself, a FUNCTION is invoked "on-the-fly" during the execution of another statement. This makes things a lot more complicated compared to CALL:

• We can't read and parse the FUNCTION from the at the point of invocation; the server requires that all tables used are opened and locked at the beginning of the query execution. One "obvious" solution would be to simply push "mysql.proc" to the list of tables used by the query, but this implies a "join" with this table if the query is a select, so it doesn't work (and we can't exclude this table easily; since a privileged used might in fact want to search the proc table). Another solution would of course be to allow the opening and closing of the during a query execution, but this it not possible at the present.

So, the solution is to collect the names of the referred FUNCTIONs during parsing in the lex. Then, before doing anything else in mysql_execute_command(), read all functions from the database an keep them in the THD, where the functionsp_find_function() can find them during the execution. Note: Even with an in-memory cache, we must still make sure that the functions are indeed read and cached at this point. The code that read and cache functions from the database must also be invoked recursively for each read FUNCTION to make sure we have all the functions we need.

Parsing DROP PROCEDURE/FUNCTION

The procedure name is pushed to Lex->value_list. The sql_command code for the result of parsing a isSQLCOM_DROP_PROCEDURE/SQLCOM_DROP_FUNCTION.

Dropping is done by simply getting the procedure with the sp_find() function and calling sp_drop() (both in sp.{cc,h}).

/ also supports the non-standard "IF EXISTS", analogous to other statements in MariaDB.

Condition and Handlers

Condition names are lexical entities and are kept in the parser context just like variables. But, condition are just "aliases" for SQLSTATE strings, or mysqld error codes (which is a non-standard extension in MySQL), and are only used during parsing.

Handlers comes in three types, CONTINUE, EXIT and UNDO. The latter is like an EXIT handler with an implicit rollback, and is currently not implemented. The EXIT handler jumps to the end of its BEGIN-END block when finished. The CONTINUE handler returns to the statement following that which invoked the handler.

The handlers in effect at any point is part of each thread's runtime state, so we need to push and pop handlers in the sp_rcontext during execution. We use special instructions for this:

• sp_instr_hpush_jump Push a handler. The instruction contains the necessary information, like which conditions we handle and the location of the handler. The jump takes us to the location after the handler code.

• sp_instr_hpop Pop the handlers of the current frame (which we are just leaving).

It might seems strange to jump past the handlers like that, but there's no extra cost in doing this, and for technical reasons it's easiest for the parser to generate the handler instructions when they occur in the source.

When an error occurs, one of the error routines is called and an error message is normally sent back to the client immediately. Catching a condition must be done in these error routines (there are quite a few) to prevent them from doing this. We do this by calling a method in the THD's sp_rcontext (if there is one). If a handler is found, this is recorded in the context and the routine returns without sending the error message. The execution loop (sp_head::execute()) checks for this after each statement and invokes the handler that has been found. If several errors or warnings occurs during one statement, only the first is caught, the rest are ignored.

Invoking and returning from a handler is trivial in the EXIT case. We simply jump to it, and it will have an sp_instr_jump as its last instruction.

Calling and returning from a CONTINUE handler poses some special problems. Since we need to return to the point after its invocation, we push the return location on a stack in the sp_rcontext (this is done by the execution loop). The handler then ends with a special instruction, sp_instr_hreturn, which returns to this location.

CONTINUE handlers have one additional problem: They are parsed at the lexical level where they occur, so variable offsets will assume that it's actually called at that level. However, a handler might be invoked from a sub-block where additional local variables have been declared, which will then share the location of any local variables in the handler itself. So, when calling a CONTINUE handler, we need to save any local variables above the handler's frame offset, and restore them upon return. (This is not a problem for EXIT handlers, since they will leave the block anyway.) This is taken care of by the execution loop and the sp_instr_hreturn instruction.

Examples

EXIT handler:

CONTINUE handler:

Cursors

For stored procedures to be really useful, you want to have cursors. MySQL doesn't yet have "real" cursor support (with API and ODBC support, allowing updating, arbitrary scrolling, etc), but a simple asensitive, non-scrolling, read-only cursor can be implemented in SPs using the class Protocol_cursor. This class intecepts the creation and sending of results sets and instead stores it in-memory, as MYSQL_FIELDS and MYSQL_ROWS (as in the client API).

To support this, we need the usual name binding support in sp_pcontext (similar to variables and conditions) to keep track on declared cursor names, and a corresponding run-time mechanism in sp_rcontext. Cursors are lexically scoped like everything with a body or BEGIN/END block, so they are pushed and poped as usual (see conditions and variables above). The basic operations on a cursor are OPEN, FETCH and CLOSE, which will each have a corresponding instruction. In addition, we need instructions to push a new cursor (this will encapsulate the LEX of the SELECT statement of the cursor), and a pop instruction:

• sp_instr_cpush Push a cursor to the sp_rcontext. This instruction contains the LEX for the select statement

• sp_instr_cpop Pop a number of cursors from the sp_rcontext.

• sp_instr_copen Open a cursor: This will execute the select and get the result set in a sepeate memroot.

• sp_instr_cfetch Fetch the next row from the in-memory result set. The instruction contains a list of the variables (frame offsets) to set.

A cursor is a separate class, sp_cursor (defined in sp_rcontex.h) which encapsulates the basic operations used by the above instructions. This class contains the LEX, Protocol_cursor object, and its memroot, as well as the cursor's current state. Compiling and executing is fairly straight-forward. sp_instr_copen is a subclass of sp_instr_stmt and uses its mechanism to execute a substatement.

Example

The SP cache

There are two ways to cache SPs:

1. one global cache, share by all threads/connections,

2. one cache per thread.

There are pros and cons with both methods:

• Pros: Save memory, each SP only read from table once,

• Cons: Needs locking (= serialization at access), requires thread-safe data structures,

• Pros: Fast, no locking required (almost), limited thread-safe requirement,

• Cons: Uses more memory, each SP read from table once per thread.

Unfortunately, we cannot use alternative 1 for the time being, as most of the data structures to be cached (lex and items) are not reentrant and thread-safe. (Things are modified at execution, we have THD pointers stored everywhere, etc.) This leaves us with alternative 2, one cache per thread; or actually two, since we keep FUNCTIONs and PROCEDUREs in separate caches. This is not that terrible; the only case when it will perform significantly worse than a global cache is when we have an application where new threads are connecting, calling a procedure, and disconnecting, over and over again.

The cache implementation itself is simple and straightforward, a hashtable wrapped in a class and a C API (see APIs below).

There is however one issue with multiple caches: dropping and altering procedures. Normally, this should be a very rare event in a running system; it's typically something you do during development and testing, so it's not unthinkable that we would simply ignore the issue and let any threads running with a cached version of an SP keep doing so until its disconnected. But assuming we want to keep the caches consistent with respect to drop and alter, it can be done:

1. A global counter is needed, initialized to 0 at start.

2. At each DROP or ALTER, increase the counter by one.

3. Each cache has its own copy of the counter, copied at the last read.

4. When looking up a name in the cache, first check if the global counter is larger than the local copy. If so, clear the cache and return "not found", and update the local counter; otherwise, lookup as usual.

This minimizes the cost to a single brief lock for the access of an integer when operating normally. Only in the event of an actual drop or alter, is the cache cleared. This may seem to be drastic, but since we assume that this is a rare event, it's not a problem. It would of course be possible to have a much more fine-grained solution, keeping track of each SP, but the overhead of doing so is not worth the effort.

Class and Function APIs

This is an outline of the key types. Some types and other details in the actual files have been omitted for readability.

The parser context: sp_pcontext.h

Run-time context (call frame): sp_rcontext.h:

The procedure: sp_head.h:

Instructions

The base class

SET instruction

Unconditional jump

Conditional jump

Return a function value

Push a handler and jump

Pops handlers

Return from a CONTINUE handler

Push a CURSOR

Pop CURSORs

Open a CURSOR

Close a CURSOR

Fetch a row with CURSOR

Utility functions: sp.h

The cache: sp_cache.h

The mysql.proc schema

This is the used in :

_{This page is licensed: CC BY-SA / Gnu FDL}