5.5 MySQL Server Plugins

MySQL supports an plugin API that enables creation of server plugins. Plugins can be loaded at server startup, or loaded and unloaded at runtime without restarting the server. The plugins supported by this interface include, but are not limited to, storage engines, 'INFORMATION_SCHEMA' tables, full-text parser plugins, partitioning support, and server extensions.

MySQL distributions include several plugins that implement server extensions:

The following sections describe how to install and uninstall plugins, and how to determine at runtime which plugins are installed and obtain information about them. For information about writing plugins, see The MySQL Plugin API (https://dev.mysql.com/doc/extending-mysql/5.7/en/plugin-api.html).

 File: manual.info.tmp, Node: plugin-loading, Next: obtaining-plugin-information, Prev: server-plugins, Up: server-plugins

5.5.1 Installing and Uninstalling Plugins

Server plugins must be loaded into the server before they can be used. MySQL supports plugin loading at server startup and runtime. It is also possible to control the activation state of loaded plugins at startup, and to unload them at runtime.

While a plugin is loaded, information about it is available as described in *note obtaining-plugin-information::.

Installing Plugins

Before a server plugin can be used, it must be installed using one of the following methods. In the descriptions, PLUGIN_NAME stands for a plugin name such as 'innodb', 'csv', or 'validate_password'.

Built-in Plugins

A built-in plugin is known by the server automatically. By default, the server enables the plugin at startup. Some built-in plugins permit this to be changed with the '--PLUGIN_NAME[=ACTIVATION_STATE]' option.

Plugins Registered in the mysql.plugin System Table

The 'mysql.plugin' system table serves as a registry of plugins (other than built-in plugins, which need not be registered). During the normal startup sequence, the server loads plugins registered in the table. By default, for a plugin loaded from the 'mysql.plugin' table, the server also enables the plugin. This can be changed with the '--PLUGIN_NAME[=ACTIVATION_STATE]' option.

If the server is started with the '--skip-grant-tables' option, plugins registered in the 'mysql.plugin' table are not loaded and are unavailable.

Plugins Named with Command-Line Options

A plugin located in a plugin library file can be loaded at server startup with the '--plugin-load', '--plugin-load-add', or '--early-plugin-load' option. Normally, for a plugin loaded at startup, the server also enables the plugin. This can be changed with the '--PLUGIN_NAME[=ACTIVATION_STATE]' option.

The '--plugin-load' and '--plugin-load-add' options load plugins after built-in plugins and storage engines have initialized during the server startup sequence. The '--early-plugin-load' option is used to load plugins that must be available prior to initialization of built-in plugins and storage engines.

The value of each plugin-loading option is a semicolon-separated list of PLUGIN_LIBRARY and NAME'='PLUGIN_LIBRARY values. Each PLUGIN_LIBRARY is the name of a library file that contains plugin code, and each NAME is the name of a plugin to load. If a plugin library is named without any preceding plugin name, the server loads all plugins in the library. With a preceding plugin name, the server loads only the named plugin from the libary. The server looks for plugin library files in the directory named by the 'plugin_dir' system variable.

Plugin-loading options do not register any plugin in the 'mysql.plugin' table. For subsequent restarts, the server loads the plugin again only if '--plugin-load', '--plugin-load-add', or '--early-plugin-load' is given again. That is, the option produces a one-time plugin-installation operation that persists for a single server invocation.

'--plugin-load', '--plugin-load-add', and '--early-plugin-load' enable plugins to be loaded even when '--skip-grant-tables' is given (which causes the server to ignore the 'mysql.plugin' table). '--plugin-load', '--plugin-load-add', and '--early-plugin-load' also enable plugins to be loaded at startup that cannot be loaded at runtime.

The '--plugin-load-add' option complements the '--plugin-load' option:

For example, these options:

 --plugin-load=x --plugin-load-add=y

are equivalent to these options:

 --plugin-load-add=x --plugin-load-add=y

and are also equivalent to this option:

 --plugin-load="x;y"

But these options:

 --plugin-load-add=y --plugin-load=x

are equivalent to this option:

 --plugin-load=x

Plugins Installed with the INSTALL PLUGIN Statement

A plugin located in a plugin library file can be loaded at runtime with the note 'INSTALL PLUGIN': install-plugin. statement. The statement also registers the plugin in the 'mysql.plugin' table to cause the server to load it on subsequent restarts. For this reason, note 'INSTALL PLUGIN': install-plugin. requires the 'INSERT' privilege for the 'mysql.plugin' table.

The plugin library file base name depends on your platform. Common suffixes are '.so' for Unix and Unix-like systems, '.dll' for Windows.

Example: The '--plugin-load-add' option installs a plugin at server startup. To install a plugin named 'myplugin' from a plugin library file named 'somepluglib.so', use these lines in a 'my.cnf' file:

 [mysqld]
 plugin-load-add=myplugin=somepluglib.so

In this case, the plugin is not registered in 'mysql.plugin'. Restarting the server without the '--plugin-load-add' option causes the plugin not to be loaded at startup.

Alternatively, the *note 'INSTALL PLUGIN': install-plugin. statement causes the server to load the plugin code from the library file at runtime:

 INSTALL PLUGIN myplugin SONAME 'somepluglib.so';

*note 'INSTALL PLUGIN': install-plugin. also causes 'permanent' plugin registration: The plugin is listed in the 'mysql.plugin' table to ensure that the server loads it on subsequent restarts.

Many plugins can be loaded either at server startup or at runtime. However, if a plugin is designed such that it must be loaded and initialized during server startup, attempts to load it at runtime using *note 'INSTALL PLUGIN': install-plugin. produce an error:

 mysql> INSTALL PLUGIN myplugin SONAME 'somepluglib.so';
 ERROR 1721 (HY000): Plugin 'myplugin' is marked as not dynamically
 installable. You have to stop the server to install it.

In this case, you must use '--plugin-load', '--plugin-load-add', or '--early-plugin-load'.

If a plugin is named both using a '--plugin-load', '--plugin-load-add', or '--early-plugin-load' option and (as a result of an earlier *note 'INSTALL PLUGIN': install-plugin. statement) in the 'mysql.plugin' table, the server starts but writes these messages to the error log:

 [ERROR] Function 'PLUGIN_NAME' already exists
 [Warning] Couldn't load plugin named 'PLUGIN_NAME'
 with soname 'PLUGIN_OBJECT_FILE'.

Controlling Plugin Activation State

If the server knows about a plugin when it starts (for example, because the plugin is named using a '--plugin-load-add' option or is registered in the 'mysql.plugin' table), the server loads and enables the plugin by default. It is possible to control activation state for such a plugin using a '--PLUGIN_NAME[=ACTIVATION_STATE]' startup option, where PLUGIN_NAME is the name of the plugin to affect, such as 'innodb', 'csv', or 'validate_password'. As with other options, dashes and underscores are interchangeable in option names. Also, activation state values are not case-sensitive. For example, '--my_plugin=ON' and '--my-plugin=on' are equivalent.

Plugin activation states are visible in the 'LOAD_OPTION' column of the Information Schema *note 'PLUGINS': information-schema-plugins-table. table.

Suppose that 'CSV', 'BLACKHOLE', and 'ARCHIVE' are built-in pluggable storage engines and that you want the server to load them at startup, subject to these conditions: The server is permitted to run if 'CSV' initialization fails, must require that 'BLACKHOLE' initialization succeeds, and should disable 'ARCHIVE'. To accomplish that, use these lines in an option file:

 [mysqld]
 csv=ON
 blackhole=FORCE
 archive=OFF

The '--enable-PLUGIN_NAME' option format is a synonym for '--PLUGIN_NAME=ON'. The '--disable-PLUGIN_NAME' and '--skip-PLUGIN_NAME' option formats are synonyms for '--PLUGIN_NAME=OFF'.

If a plugin is disabled, either explicitly with 'OFF' or implicitly because it was enabled with 'ON' but fails to initialize, aspects of server operation that require the plugin change. For example, if the plugin implements a storage engine, existing tables for the storage engine become inaccessible, and attempts to create new tables for the storage engine result in tables that use the default storage engine unless the 'NO_ENGINE_SUBSTITUTION' SQL mode is enabled to cause an error to occur instead.

Disabling a plugin may require adjustment to other options. For example, if you start the server using '--skip-innodb' to disable note 'InnoDB': innodb-storage-engine, other 'innodb_XXX' options likely need to be omitted at startup. In addition, because note 'InnoDB': innodb-storage-engine. is the default storage engine, it cannot start unless you specify another available storage engine with '--default_storage_engine'. You must also set '--default_tmp_storage_engine'.

Uninstalling Plugins

At runtime, the note 'UNINSTALL PLUGIN': uninstall-plugin. statement disables and uninstalls a plugin known to the server. The statement unloads the plugin and removes it from the 'mysql.plugin' system table, if it is registered there. For this reason, note 'UNINSTALL PLUGIN': uninstall-plugin. statement requires the 'DELETE' privilege for the 'mysql.plugin' table. With the plugin no longer registered in the table, the server does not load the plugin during subsequent restarts.

note 'UNINSTALL PLUGIN': uninstall-plugin. can unload a plugin regardless of whether it was loaded at runtime with note 'INSTALL PLUGIN': install-plugin. or at startup with a plugin-loading option, subject to these conditions:

To uninstall a plugin that currently is loaded at server startup with a plugin-loading option, use this procedure.

  1. Remove from the 'my.cnf' file any options related to the plugin.

  2. Restart the server.

  3. Plugins normally are installed using either a plugin-loading option at startup or with note 'INSTALL PLUGIN': install-plugin. at runtime, but not both. However, removing options for a plugin from the 'my.cnf' file may not be sufficient to uninstall it if at some point note 'INSTALL PLUGIN': install-plugin. has also been used. If the plugin still appears in the output from note 'INFORMATION_SCHEMA.PLUGINS': information-schema-plugins-table. or note 'SHOW PLUGINS': show-plugins, use *note 'UNINSTALL PLUGIN': uninstall-plugin. to remove it from the 'mysql.plugin' table. Then restart the server again.

 File: manual.info.tmp, Node: obtaining-plugin-information, Next: thread-pool, Prev: plugin-loading, Up: server-plugins

5.5.2 Obtaining Server Plugin Information

There are several ways to determine which plugins are installed in the server:

 File: manual.info.tmp, Node: thread-pool, Next: rewriter-query-rewrite-plugin, Prev: obtaining-plugin-information, Up: server-plugins

5.5.3 MySQL Enterprise Thread Pool

Note:

MySQL Enterprise Thread Pool is an extension included in MySQL Enterprise Edition, a commercial product. To learn more about commercial products, https://www.mysql.com/products/.

MySQL Enterprise Edition includes MySQL Enterprise Thread Pool, implemented using a server plugin. The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The thread pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections.

The thread pool addresses several problems of the model that uses one thread per connection:

Additional Resources

*note faqs-thread-pool::

 File: manual.info.tmp, Node: thread-pool-elements, Next: thread-pool-installation, Prev: thread-pool, Up: thread-pool

5.5.3.1 Thread Pool Elements ............................

MySQL Enterprise Thread Pool comprises these elements:

 File: manual.info.tmp, Node: thread-pool-installation, Next: thread-pool-operation, Prev: thread-pool-elements, Up: thread-pool

5.5.3.2 Thread Pool Installation ................................

This section describes how to install MySQL Enterprise Thread Pool. For general information about installing plugins, see *note plugin-loading::.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the 'plugin_dir' system variable). If necessary, configure the plugin directory location by setting the value of 'plugin_dir' at server startup.

The plugin library file base name is 'thread_pool'. The file name suffix differs per platform (for example, '.so' for Unix and Unix-like systems, '.dll' for Windows).

To enable thread pool capability, load the plugins to be used by starting the server with the '--plugin-load-add' option. For example, if you name only the plugin library file, the server loads all plugins that it contains (that is, the thread pool plugin and all the 'INFORMATION_SCHEMA' tables). To do this, put these lines in the server 'my.cnf' file, adjusting the '.so' suffix for your platform as necessary:

 [mysqld]
 plugin-load-add=thread_pool.so

That is equivalent to loading all thread pool plugins by naming them individually:

 [mysqld]
 plugin-load-add=thread_pool=thread_pool.so
 plugin-load-add=tp_thread_state=thread_pool.so
 plugin-load-add=tp_thread_group_state=thread_pool.so
 plugin-load-add=tp_thread_group_stats=thread_pool.so

If desired, you can load individual plugins from the library file. To load the thread pool plugin but not the 'INFORMATION_SCHEMA' tables, use an option like this:

 [mysqld]
 plugin-load-add=thread_pool=thread_pool.so

To load the thread pool plugin and only the *note 'TP_THREAD_STATE': information-schema-tp-thread-state-table. 'INFORMATION_SCHEMA' table, use options like this:

 [mysqld]
 plugin-load-add=thread_pool=thread_pool.so
 plugin-load-add=tp_thread_state=thread_pool.so

Note:

If you do not load all the 'INFORMATION_SCHEMA' tables, some or all MySQL Enterprise Monitor thread pool graphs are empty.

To verify plugin installation, examine the Information Schema note 'PLUGINS': information-schema-plugins-table. table or use the note 'SHOW PLUGINS': show-plugins. statement (see *note obtaining-plugin-information::). For example:

 mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
        FROM INFORMATION_SCHEMA.PLUGINS
        WHERE PLUGIN_NAME LIKE 'thread%' OR PLUGIN_NAME LIKE 'tp%';
 +-----------------------+---------------+
 | PLUGIN_NAME           | PLUGIN_STATUS |
 +-----------------------+---------------+
 | thread_pool           | ACTIVE        |
 | TP_THREAD_STATE       | ACTIVE        |
 | TP_THREAD_GROUP_STATE | ACTIVE        |
 | TP_THREAD_GROUP_STATS | ACTIVE        |
 +-----------------------+---------------+

If the server loads the thread pool plugin successfully, it sets the 'thread_handling' system variable to 'loaded-dynamically'.

If a plugin fails to initialize, check the server error log for diagnostic messages.

 File: manual.info.tmp, Node: thread-pool-operation, Next: thread-pool-tuning, Prev: thread-pool-installation, Up: thread-pool

5.5.3.3 Thread Pool Operation .............................

The thread pool consists of a number of thread groups, each of which manages a set of client connections. As connections are established, the thread pool assigns them to thread groups in round-robin fashion.

The thread pool exposes system variables that may be used to configure its operation:

To configure the number of thread groups, use the 'thread_pool_size' system variable. The default number of groups is 16. For guidelines on setting this variable, see *note thread-pool-tuning::.

The maximum number of threads per group is 4096 (or 4095 on some systems where one thread is used internally).

The thread pool separates connections and threads, so there is no fixed relationship between connections and the threads that execute statements received from those connections. This differs from the default thread-handling model that associates one thread with one connection such that a given thread executes all statements from its connection.

The thread pool tries to ensure a maximum of one thread executing in each group at any time, but sometimes permits more threads to execute temporarily for best performance:

Here are examples of conditions under which a thread group might have multiple threads started to execute statements:

The thread pool is designed to be scalable across an increasing number of connections. It is also designed to avoid deadlocks that can arise from limiting the number of actively executing statements. It is important that threads that do not report back to the thread pool do not prevent other statements from executing and thus cause the thread pool to become deadlocked. Examples of such statements follow:

In each case, to prevent deadlock, the statement is moved to the stalled category when it does not complete quickly, so that the thread group can permit another statement to begin executing. With this design, when a thread executes or becomes blocked for an extended time, the thread pool moves the thread to the stalled category and for the rest of the statement's execution, it does not prevent other statements from executing.

The maximum number of threads that can occur is the sum of 'max_connections' and 'thread_pool_size'. This can happen in a situation where all connections are in execution mode and an extra thread is created per group to listen for more statements. This is not necessarily a state that happens often, but it is theoretically possible.

 File: manual.info.tmp, Node: thread-pool-tuning, Prev: thread-pool-operation, Up: thread-pool

5.5.3.4 Thread Pool Tuning ..........................

This section provides guidelines on setting thread pool system variables for best performance, measured using a metric such as transactions per second.

'thread_pool_size' is the most important parameter controlling thread pool performance. It can be set only at server startup. Our experience in testing the thread pool indicates the following:

Another system variable, 'thread_pool_stall_limit', is important for handling of blocked and long-running statements. If all calls that block the MySQL Server are reported to the thread pool, it would always know when execution threads are blocked. However, this may not always be true. For example, blocks could occur in code that has not been instrumented with thread pool callbacks. For such cases, the thread pool must be able to identify threads that appear to be blocked. This is done by means of a timeout that can be tuned using the 'thread_pool_stall_limit' system variable, the value of which is measured in 10ms units. This parameter ensures that the server does not become completely blocked. The value of 'thread_pool_stall_limit' has an upper limit of 6 seconds to prevent the risk of a deadlocked server.

'thread_pool_stall_limit' also enables the thread pool to handle long-running statements. If a long-running statement was permitted to block a thread group, all other connections assigned to the group would be blocked and unable to start execution until the long-running statement completed. In the worst case, this could take hours or even days.

The value of 'thread_pool_stall_limit' should be chosen such that statements that execute longer than its value are considered stalled. Stalled statements generate a lot of extra overhead since they involve extra context switches and in some cases even extra thread creations. On the other hand, setting the 'thread_pool_stall_limit' parameter too high means that long-running statements block a number of short-running statements for longer than necessary. Short wait values permit threads to start more quickly. Short values are also better for avoiding deadlock situations. Long wait values are useful for workloads that include long-running statements, to avoid starting too many new statements while the current ones execute.

Suppose a server executes a workload where 99.9% of the statements complete within 100ms even when the server is loaded, and the remaining statements take between 100ms and 2 hours fairly evenly spread. In this case, it would make sense to set 'thread_pool_stall_limit' to 10 (10 x 10ms = 100ms). The default value of 6 (60ms) is suitable for servers that primarily execute very simple statements.

The 'thread_pool_stall_limit' parameter can be changed at runtime to enable you to strike a balance appropriate for the server work load. Assuming that the 'TP_THREAD_GROUP_STATS' table is enabled, you can use the following query to determine the fraction of executed statements that stalled:

 SELECT SUM(STALLED_QUERIES_EXECUTED) / SUM(QUERIES_EXECUTED)
 FROM INFORMATION_SCHEMA.TP_THREAD_GROUP_STATS;

This number should be as low as possible. To decrease the likelihood of statements stalling, increase the value of 'thread_pool_stall_limit'.

When a statement arrives, what is the maximum time it can be delayed before it actually starts executing? Suppose that the following conditions apply:

In the worst case, the 10 high-priority statements represent 10 transactions that continue executing for a long time. Thus, in the worst case, no statements are moved to the high-priority queue because it already contains statements awaiting execution. After 10 seconds, the new statement is eligible to be moved to the high-priority queue. However, before it can be moved, all the statements before it must be moved as well. This could take another 2 seconds because a maximum of 100 statements per second are moved to the high-priority queue. Now when the statement reaches the high-priority queue, there could potentially be many long-running statements ahead of it. In the worst case, every one of those becomes stalled and it takes 1 second for each statement before the next statement is retrieved from the high-priority queue. Thus, in this scenario, it takes 222 seconds before the new statement starts executing.

This example shows a worst case for an application. How to handle it depends on the application. If the application has high requirements for the response time, it should most likely throttle users at a higher level itself. Otherwise, it can use the thread pool configuration parameters to set some kind of a maximum waiting time.

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin, Next: version-tokens, Prev: thread-pool, Up: server-plugins

5.5.4 The Rewriter Query Rewrite Plugin

MySQL supports query rewrite plugins that can examine and possibly modify SQL statements received by the server before the server executes them. See Query Rewrite Plugins (https://dev.mysql.com/doc/extending-mysql/5.7/en/plugin-types.html#query-rewrite-plugin-type).

MySQL distributions include a postparse query rewrite plugin named 'Rewriter' and scripts for installing the plugin and its associated elements. These elements work together to provide *note 'SELECT': select. rewriting capability:

The following sections describe how to install and use the 'Rewriter' plugin, and provide reference information for its associated elements.

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin-installation, Next: rewriter-query-rewrite-plugin-usage, Prev: rewriter-query-rewrite-plugin, Up: rewriter-query-rewrite-plugin

5.5.4.1 Installing or Uninstalling the Rewriter Query Rewrite Plugin ....................................................................

Note:

If installed, the 'Rewriter' plugin involves some overhead even when disabled. To avoid this overhead, do not install the plugin unless you plan to use it.

To install or uninstall the 'Rewriter' query rewrite plugin, choose the appropriate script located in the 'share' directory of your MySQL installation:

Run the chosen script as follows:

 $> mysql -u root -p < install_rewriter.sql
 Enter password: (ENTER ROOT PASSWORD HERE)

The example here uses the 'install_rewriter.sql' installation script. Substitute 'uninstall_rewriter.sql' if you are uninstalling the plugin.

Running an installation script should install and enable the plugin. To verify that, connect to the server and execute this statement:

 mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
 +------------------+-------+
 | Variable_name    | Value |
 +------------------+-------+
 | rewriter_enabled | ON    |
 +------------------+-------+

For usage instructions, see note rewriter-query-rewrite-plugin-usage::. For reference information, see note rewriter-query-rewrite-plugin-reference::.

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin-usage, Next: rewriter-query-rewrite-plugin-reference, Prev: rewriter-query-rewrite-plugin-installation, Up: rewriter-query-rewrite-plugin

5.5.4.2 Using the Rewriter Query Rewrite Plugin ...............................................

To enable or disable the plugin, enable or disable the 'rewriter_enabled' system variable. By default, the 'Rewriter' plugin is enabled when you install it (see *note rewriter-query-rewrite-plugin-installation::). To set the initial plugin state explicitly, you can set the variable at server startup. For example, to enable the plugin in an option file, use these lines:

 [mysqld]
 rewriter_enabled=ON

It is also possible to enable or disable the plugin at runtime:

 SET GLOBAL rewriter_enabled = ON;
 SET GLOBAL rewriter_enabled = OFF;

Assuming that the 'Rewriter' plugin is enabled, it examines and possibly modifies each *note 'SELECT': select. statement received by the server. The plugin determines whether to rewrite statements based on its in-memory cache of rewriting rules, which are loaded from the 'rewrite_rules' table in the 'query_rewrite' database.

Adding Rewrite Rules

To add rules for the 'Rewriter' plugin, add rows to the 'rewrite_rules' table, then invoke the 'flush_rewrite_rules()' stored procedure to load the rules from the table into the plugin. The following example creates a simple rule to match statements that select a single literal value:

 INSERT INTO query_rewrite.rewrite_rules (pattern, replacement)
 VALUES('SELECT ?', 'SELECT ? + 1');

The resulting table contents look like this:

 mysql> SELECT * FROM query_rewrite.rewrite_rules\G
 *************************** 1. row ***************************
                 id: 1
            pattern: SELECT ?
   pattern_database: NULL
        replacement: SELECT ? + 1
            enabled: YES
            message: NULL
     pattern_digest: NULL
 normalized_pattern: NULL

The rule specifies a pattern template indicating which *note 'SELECT': select. statements to match, and a replacement template indicating how to rewrite matching statements. However, adding the rule to the 'rewrite_rules' table is not sufficient to cause the 'Rewriter' plugin to use the rule. You must invoke 'flush_rewrite_rules()' to load the table contents into the plugin in-memory cache:

 mysql> CALL query_rewrite.flush_rewrite_rules();

Tip:

If your rewrite rules seem not to be working properly, make sure that you have reloaded the rules table by calling 'flush_rewrite_rules()'.

When the plugin reads each rule from the rules table, it computes a normalized (statement digest) form from the pattern and a digest hash value, and uses them to update the 'normalized_pattern' and 'pattern_digest' columns:

 mysql> SELECT * FROM query_rewrite.rewrite_rules\G
 *************************** 1. row ***************************
                 id: 1
            pattern: SELECT ?
   pattern_database: NULL
        replacement: SELECT ? + 1
            enabled: YES
            message: NULL
     pattern_digest: 46b876e64cd5c41009d91c754921f1d4
 normalized_pattern: select ?

For information about statement digesting, normalized statements, and digest hash values, see *note performance-schema-statement-digests::.

If a rule cannot be loaded due to some error, calling 'flush_rewrite_rules()' produces an error:

 mysql> CALL query_rewrite.flush_rewrite_rules();
 ERROR 1644 (45000): Loading of some rule(s) failed.

When this occurs, the plugin writes an error message to the 'message' column of the rule row to communicate the problem. Check the 'rewrite_rules' table for rows with non-'NULL' 'message' column values to see what problems exist.

Patterns use the same syntax as prepared statements (see note prepare::). Within a pattern template, '?' characters act as parameter markers that match data values. The '?' characters should not be enclosed within quotation marks. Parameter markers can be used only where data values should appear, and they cannot be used for SQL keywords, identifiers, functions, and so on. The plugin parses a statement to identify the literal values (as defined in note literals::), so you can put a parameter marker in place of any literal value.

Like the pattern, the replacement can contain '?' characters. For a statement that matches a pattern template, the plugin rewrites it, replacing '?' parameter markers in the replacement using data values matched by the corresponding markers in the pattern. The result is a complete statement string. The plugin asks the server to parse it, and returns the result to the server as the representation of the rewritten statement.

After adding and loading the rule, check whether rewriting occurs according to whether statements match the rule pattern:

 mysql> SELECT PI();
 +----------+
 | PI()     |
 +----------+
 | 3.141593 |
 +----------+
 1 row in set (0.01 sec)

 mysql> SELECT 10;
 +--------+
 | 10 + 1 |
 +--------+
 |     11 |
 +--------+
 1 row in set, 1 warning (0.00 sec)

No rewriting occurs for the first note 'SELECT': select. statement, but does for the second. The second statement illustrates that when the 'Rewriter' plugin rewrites a statement, it produces a warning message. To view the message, use note 'SHOW WARNINGS': show-warnings.:

 mysql> SHOW WARNINGS\G
 *************************** 1. row ***************************
   Level: Note
    Code: 1105
 Message: Query 'SELECT 10' rewritten to 'SELECT 10 + 1' by a query rewrite plugin

To enable or disable an existing rule, modify its 'enabled' column and reload the table into the plugin. To disable rule 1:

 UPDATE query_rewrite.rewrite_rules SET enabled = 'NO' WHERE id = 1;
 CALL query_rewrite.flush_rewrite_rules();

This enables you to deactivate a rule without removing it from the table.

To re-enable rule 1:

 UPDATE query_rewrite.rewrite_rules SET enabled = 'YES' WHERE id = 1;
 CALL query_rewrite.flush_rewrite_rules();

The 'rewrite_rules' table contains a 'pattern_database' column that 'Rewriter' uses for matching table names that are not qualified with a database name:

Suppose that a table named 'appdb.users' has a column named 'id' and that applications are expected to select rows from the table using a query of one of these forms, where the second can be used when 'appdb' is the default database:

 SELECT * FROM users WHERE appdb.id = ID_VALUE;
 SELECT * FROM users WHERE id = ID_VALUE;

Suppose also that the 'id' column is renamed to 'user_id' (perhaps the table must be modified to add another type of ID and it is necessary to indicate more specifically what type of ID the 'id' column represents).

The change means that applications must refer to 'user_id' rather than 'id' in the 'WHERE' clause, but old applications that cannot be updated no longer work properly. The 'Rewriter' plugin can solve this problem by matching and rewriting problematic statements. To match the statement 'SELECT * FROM appdb.users WHERE id = VALUE' and rewrite it as 'SELECT * FROM appdb.users WHERE user_id = VALUE', you can insert a row representing a replacement rule into the rewrite rules table. If you also want to match this 'SELECT' using the unqualified table name, it is also necessary to add an explicit rule. Using '?' as a value placeholder, the two *note 'INSERT': insert. statements needed look like this:

 INSERT INTO query_rewrite.rewrite_rules
     (pattern, replacement) VALUES(
     'SELECT * FROM appdb.users WHERE id = ?',
     'SELECT * FROM appdb.users WHERE user_id = ?'
     );
 INSERT INTO query_rewrite.rewrite_rules
     (pattern, replacement, pattern_database) VALUES(
     'SELECT * FROM users WHERE id = ?',
     'SELECT * FROM users WHERE user_id = ?',
     'appdb'
     );

After adding the two new rules, execute the following statement to cause them to take effect:

 CALL query_rewrite.flush_rewrite_rules();

'Rewriter' uses the first rule to match statements that use the qualified table name, and the second to match statements that use the unqualified name. The second rule works only when 'appdb' is the default database.

How Statement Matching Works

The 'Rewriter' plugin uses statement digests and digest hash values to match incoming statements against rewrite rules in stages. The 'max_digest_length' system variable determines the size of the buffer used for computing statement digests. Larger values enable computation of digests that distinguish longer statements. Smaller values use less memory but increase the likelihood of longer statements colliding with the same digest value.

The plugin matches each statement to the rewrite rules as follows:

  1. Compute the statement digest hash value and compare it to the rule digest hash values. This is subject to false positives, but serves as a quick rejection test.

  2. If the statement digest hash value matches any pattern digest hash values, match the normalized (statement digest) form of the statement to the normalized form of the matching rule patterns.

  3. If the normalized statement matches a rule, compare the literal values in the statement and the pattern. A '?' character in the pattern matches any literal value in the statement. If the statement prepares a *note 'SELECT': select. statement, '?' in the pattern also matches '?' in the statement. Otherwise, corresponding literals must be the same.

If multiple rules match a statement, it is nondeterministic which one the plugin uses to rewrite the statement.

If a pattern contains more markers than the replacement, the plugin discards excess data values. If a pattern contains fewer markers than the replacement, it is an error. The plugin notices this when the rules table is loaded, writes an error message to the 'message' column of the rule row to communicate the problem, and sets the 'Rewriter_reload_error' status variable to 'ON'.

Rewriting Prepared Statements

Prepared statements are rewritten at parse time (that is, when they are prepared), not when they are executed later.

Prepared statements differ from nonprepared statements in that they may contain '?' characters as parameter markers. To match a '?' in a prepared statement, a 'Rewriter' pattern must contain '?' in the same location. Suppose that a rewrite rule has this pattern:

 SELECT ?, 3

The following table shows several prepared *note 'SELECT': select. statements and whether the rule pattern matches them.

Prepared Statement Whether Pattern Matches Statement

'PREPARE s AS 'SELECT 3, 3'' Yes

'PREPARE s AS 'SELECT ?, 3'' Yes

'PREPARE s AS 'SELECT 3, ?'' No

'PREPARE s AS 'SELECT ?, ?'' No

Rewriter Plugin Operational Information

The 'Rewriter' plugin makes information available about its operation by means of several status variables:

 mysql> SHOW GLOBAL STATUS LIKE 'Rewriter%';
 +-----------------------------------+-------+
 | Variable_name                     | Value |
 +-----------------------------------+-------+
 | Rewriter_number_loaded_rules      | 1     |
 | Rewriter_number_reloads           | 5     |
 | Rewriter_number_rewritten_queries | 1     |
 | Rewriter_reload_error             | ON    |
 +-----------------------------------+-------+

For descriptions of these variables, see *note rewriter-query-rewrite-plugin-status-variables::.

When you load the rules table by calling the 'flush_rewrite_rules()' stored procedure, if an error occurs for some rule, the 'CALL' statement produces an error, and the plugin sets the 'Rewriter_reload_error' status variable to 'ON':

 mysql> CALL query_rewrite.flush_rewrite_rules();
 ERROR 1644 (45000): Loading of some rule(s) failed.

 mysql> SHOW GLOBAL STATUS LIKE 'Rewriter_reload_error';
 +-----------------------+-------+
 | Variable_name         | Value |
 +-----------------------+-------+
 | Rewriter_reload_error | ON    |
 +-----------------------+-------+

In this case, check the 'rewrite_rules' table for rows with non-'NULL' 'message' column values to see what problems exist.

Rewriter Plugin Use of Character Sets

When the 'rewrite_rules' table is loaded into the 'Rewriter' plugin, the plugin interprets statements using the current global value of the 'character_set_client' system variable. If the global 'character_set_client' value is changed subsequently, the rules table must be reloaded.

A client must have a session 'character_set_client' value identical to what the global value was when the rules table was loaded or rule matching does not work for that client.

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin-reference, Prev: rewriter-query-rewrite-plugin-usage, Up: rewriter-query-rewrite-plugin

5.5.4.3 Rewriter Query Rewrite Plugin Reference ...............................................

The following discussion serves as a reference to these elements associated with the 'Rewriter' query rewrite plugin:

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin-table, Next: rewriter-query-rewrite-plugin-routines, Prev: rewriter-query-rewrite-plugin-reference, Up: rewriter-query-rewrite-plugin-reference

5.5.4.4 Rewriter Query Rewrite Plugin Rules Table .................................................

The 'rewrite_rules' table in the 'query_rewrite' database provides persistent storage for the rules that the 'Rewriter' plugin uses to decide whether to rewrite statements.

Users communicate with the plugin by modifying the set of rules stored in this table. The plugin communicates information to users by setting the table's 'message' column.

Note:

The rules table is loaded into the plugin by the 'flush_rewrite_rules' stored procedure. Unless that procedure has been called following the most recent table modification, the table contents do not necessarily correspond to the set of rules the plugin is using.

The 'rewrite_rules' table has these columns:

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin-routines, Next: rewriter-query-rewrite-plugin-system-variables, Prev: rewriter-query-rewrite-plugin-table, Up: rewriter-query-rewrite-plugin-reference

5.5.4.5 Rewriter Query Rewrite Plugin Procedures and Functions ..............................................................

'Rewriter' plugin operation uses a stored procedure that loads the rules table into its in-memory cache, and a helper loadable function. Under normal operation, users invoke only the stored procedure. The function is intended to be invoked by the stored procedure, not directly by users.

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin-system-variables, Next: rewriter-query-rewrite-plugin-status-variables, Prev: rewriter-query-rewrite-plugin-routines, Up: rewriter-query-rewrite-plugin-reference

5.5.4.6 Rewriter Query Rewrite Plugin System Variables ......................................................

The 'Rewriter' query rewrite plugin supports the following system variables. These variables are available only if the plugin is installed (see *note rewriter-query-rewrite-plugin-installation::).

 File: manual.info.tmp, Node: rewriter-query-rewrite-plugin-status-variables, Prev: rewriter-query-rewrite-plugin-system-variables, Up: rewriter-query-rewrite-plugin-reference

5.5.4.7 Rewriter Query Rewrite Plugin Status Variables ......................................................

The 'Rewriter' query rewrite plugin supports the following status variables. These variables are available only if the plugin is installed (see *note rewriter-query-rewrite-plugin-installation::).

 File: manual.info.tmp, Node: version-tokens, Next: plugin-services, Prev: rewriter-query-rewrite-plugin, Up: server-plugins

5.5.5 Version Tokens

MySQL includes Version Tokens, a feature that enables creation of and synchronization around server tokens that applications can use to prevent accessing incorrect or out-of-date data.

The Version Tokens interface has these characteristics:

The following sections describe the elements of Version Tokens, discuss how to install and use it, and provide reference information for its elements.

 File: manual.info.tmp, Node: version-tokens-elements, Next: version-tokens-installation, Prev: version-tokens, Up: version-tokens

5.5.5.1 Version Tokens Elements ...............................

Version Tokens is based on a plugin library that implements these elements:

 File: manual.info.tmp, Node: version-tokens-installation, Next: version-tokens-usage, Prev: version-tokens-elements, Up: version-tokens

5.5.5.2 Installing or Uninstalling Version Tokens .................................................

Note:

If installed, Version Tokens involves some overhead. To avoid this overhead, do not install it unless you plan to use it.

This section describes how to install or uninstall Version Tokens, which is implemented in a plugin library file containing a plugin and loadable functions. For general information about installing or uninstalling plugins and loadable functions, see note plugin-loading::, and note function-loading::.

To be usable by the server, the plugin library file must be located in the MySQL plugin directory (the directory named by the 'plugin_dir' system variable). If necessary, configure the plugin directory location by setting the value of 'plugin_dir' at server startup.

The plugin library file base name is 'version_tokens'. The file name suffix differs per platform (for example, '.so' for Unix and Unix-like systems, '.dll' for Windows).

To install the Version Tokens plugin and functions, use the note 'INSTALL PLUGIN': install-plugin. and note 'CREATE FUNCTION': create-function. statements, adjusting the '.so' suffix for your platform as necessary:

 INSTALL PLUGIN version_tokens SONAME 'version_token.so';
 CREATE FUNCTION version_tokens_set RETURNS STRING
   SONAME 'version_token.so';
 CREATE FUNCTION version_tokens_show RETURNS STRING
   SONAME 'version_token.so';
 CREATE FUNCTION version_tokens_edit RETURNS STRING
   SONAME 'version_token.so';
 CREATE FUNCTION version_tokens_delete RETURNS STRING
   SONAME 'version_token.so';
 CREATE FUNCTION version_tokens_lock_shared RETURNS INT
   SONAME 'version_token.so';
 CREATE FUNCTION version_tokens_lock_exclusive RETURNS INT
   SONAME 'version_token.so';
 CREATE FUNCTION version_tokens_unlock RETURNS INT
   SONAME 'version_token.so';

You must install the functions to manage the server's version token list, but you must also install the plugin because the functions do not work correctly without it.

If the plugin and functions are used on a replication source server, install them on all replica servers as well to avoid replication problems.

Once installed as just described, the plugin and functions remain installed until uninstalled. To remove them, use the note 'UNINSTALL PLUGIN': uninstall-plugin. and note 'DROP FUNCTION': drop-function. statements:

 UNINSTALL PLUGIN version_tokens;
 DROP FUNCTION version_tokens_set;
 DROP FUNCTION version_tokens_show;
 DROP FUNCTION version_tokens_edit;
 DROP FUNCTION version_tokens_delete;
 DROP FUNCTION version_tokens_lock_shared;
 DROP FUNCTION version_tokens_lock_exclusive;
 DROP FUNCTION version_tokens_unlock;

 File: manual.info.tmp, Node: version-tokens-usage, Next: version-tokens-reference, Prev: version-tokens-installation, Up: version-tokens

5.5.5.3 Using Version Tokens ............................

Before using Version Tokens, install it according to the instructions provided at *note version-tokens-installation::.

A scenario in which Version Tokens can be useful is a system that accesses a collection of MySQL servers but needs to manage them for load balancing purposes by monitoring them and adjusting server assignments according to load changes. Such a system comprises these elements:

Version Tokens permit server access to be managed according to assignment without requiring clients to repeatedly query the servers about their assignments:

The client-side logic for detecting version token errors and selecting a new server can be implemented different ways:

The following example illustrates the preceding discussion in more concrete form.

When Version Tokens initializes on a given server, the server's version token list is empty. Token list maintenance is performed by calling functions. The 'SUPER' privilege is required to call any of the Version Token functions, so token list modification is expected to be done by a management or administrative application that has that privilege.

Suppose that a management application communicates with a set of servers that are queried by clients to access employee and product databases (named 'emp' and 'prod', respectively). All servers are permitted to process data retrieval statements, but only some of them are permitted to make database updates. To handle this on a database-specific basis, the management application establishes a list of version tokens on each server. In the token list for a given server, token names represent database names and token values are 'read' or 'write' depending on whether the database must be used in read-only fashion or whether it can take reads and writes.

Client applications register a list of version tokens they require the server to match by setting a system variable. Variable setting occurs on a client-specific basis, so different clients can register different requirements. By default, the client token list is empty, which matches any server token list. When a client sets its token list to a nonempty value, matching may succeed or fail, depending on the server version token list.

To define the version token list for a server, the management application calls the 'version_tokens_set()' function. (There are also functions for modifying and displaying the token list, described later.) For example, the application might send these statements to a group of three servers:

Server 1:

 mysql> SELECT version_tokens_set('emp=read;prod=read');
 +------------------------------------------+
 | version_tokens_set('emp=read;prod=read') |
 +------------------------------------------+
 | 2 version tokens set.                    |
 +------------------------------------------+

Server 2:

 mysql> SELECT version_tokens_set('emp=write;prod=read');
 +-------------------------------------------+
 | version_tokens_set('emp=write;prod=read') |
 +-------------------------------------------+
 | 2 version tokens set.                     |
 +-------------------------------------------+

Server 3:

 mysql> SELECT version_tokens_set('emp=read;prod=write');
 +-------------------------------------------+
 | version_tokens_set('emp=read;prod=write') |
 +-------------------------------------------+
 | 2 version tokens set.                     |
 +-------------------------------------------+

The token list in each case is specified as a semicolon-separated list of 'NAME=VALUE' pairs. The resulting token list values result in these server assingments:

In addition to assigning each server a version token list, the management application also maintains a cache that reflects the server assignments.

Before communicating with the servers, a client application contacts the management application and retrieves information about server assignments. Then the client selects a server based on those assignments. Suppose that a client wants to perform both reads and writes on the 'emp' database. Based on the preceding assignments, only server 2 qualifies. The client connects to server 2 and registers its server requirements there by setting its 'version_tokens_session' system variable:

 mysql> SET @@SESSION.version_tokens_session = 'emp=write';

For subsequent statements sent by the client to server 2, the server compares its own version token list to the client list to check whether they match. If so, statements execute normally:

 mysql> UPDATE emp.employee SET salary = salary * 1.1 WHERE id = 4981;
 Query OK, 1 row affected (0.07 sec)
 Rows matched: 1  Changed: 1  Warnings: 0

 mysql> SELECT last_name, first_name FROM emp.employee WHERE id = 4981;
 +-----------+------------+
 | last_name | first_name |
 +-----------+------------+
 | Smith     | Abe        |
 +-----------+------------+
 1 row in set (0.01 sec)

Discrepancies between the server and client version token lists can occur two ways:

As long as the assignment of server 2 does not change, the client continues to use it for reads and writes. But suppose that the management application wants to change server assignments so that writes for the 'emp' database must be sent to server 1 instead of server 2. To do this, it uses 'version_tokens_edit()' to modify the 'emp' token value on the two servers (and updates its cache of server assignments):

Server 1:

 mysql> SELECT version_tokens_edit('emp=write');
 +----------------------------------+
 | version_tokens_edit('emp=write') |
 +----------------------------------+
 | 1 version tokens updated.        |
 +----------------------------------+

Server 2:

 mysql> SELECT version_tokens_edit('emp=read');
 +---------------------------------+
 | version_tokens_edit('emp=read') |
 +---------------------------------+
 | 1 version tokens updated.       |
 +---------------------------------+

'version_tokens_edit()' modifies the named tokens in the server token list and leaves other tokens unchanged.

The next time the client sends a statement to server 2, its own token list no longer matches the server token list and an error occurs:

 mysql> UPDATE emp.employee SET salary = salary * 1.1 WHERE id = 4982;
 ERROR 3136 (42000): Version token mismatch for emp. Correct value read

In this case, the client should contact the management application to obtain updated information about server assignments, select a new server, and send the failed statement to the new server.

Note:

Each client must cooperate with Version Tokens by sending only statements in accordance with the token list that it registers with a given server. For example, if a client registers a token list of ''emp=read'', there is nothing in Version Tokens to prevent the client from sending updates for the 'emp' database. The client itself must refrain from doing so.

For each statement received from a client, the server implicitly uses locking, as follows:

The server uses shared locks so that comparisons for multiple sessions can occur without blocking, while preventing changes to the tokens for any session that attempts to acquire an exclusive lock before it manipulates tokens of the same names in the server token list.

The preceding example uses only a few of the functions included in the Version Tokens plugin library, but there are others. One set of functions permits the server's list of version tokens to be manipulated and inspected. Another set of functions permits version tokens to be locked and unlocked.

These functions permit the server's list of version tokens to be created, changed, removed, and inspected:

Each of those functions, if successful, returns a binary string indicating what action occurred. The following example establishes the server token list, modifies it by adding a new token, deletes some tokens, and displays the resulting token list:

 mysql> SELECT version_tokens_set('tok1=a;tok2=b');
 +-------------------------------------+
 | version_tokens_set('tok1=a;tok2=b') |
 +-------------------------------------+
 | 2 version tokens set.               |
 +-------------------------------------+
 mysql> SELECT version_tokens_edit('tok3=c');
 +-------------------------------+
 | version_tokens_edit('tok3=c') |
 +-------------------------------+
 | 1 version tokens updated.     |
 +-------------------------------+
 mysql> SELECT version_tokens_delete('tok2;tok1');
 +------------------------------------+
 | version_tokens_delete('tok2;tok1') |
 +------------------------------------+
 | 2 version tokens deleted.          |
 +------------------------------------+
 mysql> SELECT version_tokens_show();
 +-----------------------+
 | version_tokens_show() |
 +-----------------------+
 | tok3=c;               |
 +-----------------------+

Warnings occur if a token list is malformed:

 mysql> SELECT version_tokens_set('tok1=a; =c');
 +----------------------------------+
 | version_tokens_set('tok1=a; =c') |
 +----------------------------------+
 | 1 version tokens set.            |
 +----------------------------------+
 1 row in set, 1 warning (0.00 sec)

 mysql> SHOW WARNINGS\G
 *************************** 1. row ***************************
   Level: Warning
    Code: 42000
 Message: Invalid version token pair encountered. The list provided
          is only partially updated.
 1 row in set (0.00 sec)

As mentioned previously, version tokens are defined using a semicolon-separated list of 'NAME=VALUE' pairs. Consider this invocation of 'version_tokens_set()':

 mysql> SELECT version_tokens_set('tok1=b;;; tok2= a = b ; tok1 = 1\'2 3"4')
 +---------------------------------------------------------------+
 | version_tokens_set('tok1=b;;; tok2= a = b ; tok1 = 1\'2 3"4') |
 +---------------------------------------------------------------+
 | 3 version tokens set.                                         |
 +---------------------------------------------------------------+

Version Tokens interprets the argument as follows:

Given those rules, the preceding 'version_tokens_set()' call results in a token list with two tokens: 'tok1' has the value '1'2 3"4', and 'tok2' has the value 'a = b'. To verify this, call 'version_tokens_show()':

 mysql> SELECT version_tokens_show();
 +--------------------------+
 | version_tokens_show()    |
 +--------------------------+
 | tok2=a = b;tok1=1'2 3"4; |
 +--------------------------+

If the token list contains two tokens, why did 'version_tokens_set()' return the value '3 version tokens set'? That occurred because the original token list contained two definitions for 'tok1', and the second definition replaced the first.

The Version Tokens token-manipulation functions place these constraints on token names and values:

Version Tokens also includes a set of functions enabling tokens to be locked and unlocked:

Each locking function returns nonzero for success. Otherwise, an error occurs:

 mysql> SELECT version_tokens_lock_shared('lock1', 'lock2', 0);
 +-------------------------------------------------+
 | version_tokens_lock_shared('lock1', 'lock2', 0) |
 +-------------------------------------------------+
 |                                               1 |
 +-------------------------------------------------+

 mysql> SELECT version_tokens_lock_shared(NULL, 0);
 ERROR 3131 (42000): Incorrect locking service lock name '(null)'.

Locking using Version Tokens locking functions is advisory; applications must agree to cooperate.

It is possible to lock nonexisting token names. This does not create the tokens.

Note:

Version Tokens locking functions are based on the locking service described at note locking-service::, and thus have the same semantics for shared and exclusive locks. (Version Tokens uses the locking service routines built into the server, not the locking service function interface, so those functions need not be installed to use Version Tokens.) Locks acquired by Version Tokens use a locking service namespace of 'version_token_locks'. Locking service locks can be monitored using the Performance Schema, so this is also true for Version Tokens locks. For details, see note locking-service-monitoring::.

For the Version Tokens locking functions, token name arguments are used exactly as specified. Surrounding whitespace is not ignored and '=' and ';' characters are permitted. This is because Version Tokens simply passes the token names to be locked as is to the locking service.

 File: manual.info.tmp, Node: version-tokens-reference, Prev: version-tokens-usage, Up: version-tokens

5.5.5.4 Version Tokens Reference ................................

The following discussion serves as a reference to these Version Tokens elements:

Version Tokens Functions

The Version Tokens plugin library includes several functions. One set of functions permits the server's list of version tokens to be manipulated and inspected. Another set of functions permits version tokens to be locked and unlocked. The 'SUPER' privilege is required to invoke any Version Tokens function.

The following functions permit the server's list of version tokens to be created, changed, removed, and inspected. Interpretation of NAME_LIST and TOKEN_LIST arguments (including whitespace handling) occurs as described in *note version-tokens-usage::, which provides details about the syntax for specifying tokens, as well as additional examples.

The following functions permit version tokens to be locked and unlocked:

The locking functions share these characteristics:

Version Tokens System Variables

Version Tokens supports the following system variables. These variables are unavailable unless the Version Tokens plugin is installed (see *note version-tokens-installation::).

System variables:

 File: manual.info.tmp, Node: plugin-services, Prev: version-tokens, Up: server-plugins

5.5.6 MySQL Plugin Services

MySQL server plugins have access to server 'plugin services.' The plugin services interface complements the plugin API by exposing server functionality that plugins can call. For developer information about writing plugin services, see MySQL Services for Plugins (https://dev.mysql.com/doc/extending-mysql/5.7/en/services-for-plugins.html). The following sections describe plugin services available at the SQL and C-language levels.

 File: manual.info.tmp, Node: locking-service, Next: keyring-service, Prev: plugin-services, Up: plugin-services

5.5.6.1 The Locking Service ...........................

MySQL distributions provide a locking interface that is accessible at two levels:

For general information about plugin services, see *note plugin-services::. For general information about loadable functions, see Adding a Loadable Function (https://dev.mysql.com/doc/extending-mysql/5.7/en/adding-loadable-function.html).

The locking interface has these characteristics:

The interface provided by the locking service is distinct from that provided by 'GET_LOCK()' and related SQL functions (see *note locking-functions::). For example, 'GET_LOCK()' does not implement namespaces and provides only exclusive locks, not distinct read and write locks.

 File: manual.info.tmp, Node: locking-service-c-interface, Next: locking-service-interface, Prev: locking-service, Up: locking-service

5.5.6.2 The Locking Service C Interface .......................................

This section describes how to use the locking service C language interface. To use the function interface instead, see note locking-service-interface:: For general characteristics of the locking service interface, see note locking-service::. For general information about plugin services, see *note plugin-services::.

Source files that use the locking service should include this header file:

 #include <mysql/service_locking.h>

To acquire one or more locks, call this function:

 int mysql_acquire_locking_service_locks(MYSQL_THD opaque_thd,
                                         const char* lock_namespace,
                                         const char**lock_names,
                                         size_t lock_num,
                                         enum enum_locking_service_lock_type lock_type,
                                         unsigned long lock_timeout);

The arguments have these meanings:

To release locks acquired for a given namespace, call this function:

 int mysql_release_locking_service_locks(MYSQL_THD opaque_thd,
                                         const char* lock_namespace);

The arguments have these meanings:

Locks acquired or waited for by the locking service can be monitored at the SQL level using the Performance Schema. For details, see *note locking-service-monitoring::.

 File: manual.info.tmp, Node: locking-service-interface, Prev: locking-service-c-interface, Up: locking-service

5.5.6.3 The Locking Service Function Interface ..............................................

This section describes how to use the locking service interface provided by its loadable functions. To use the C language interface instead, see note locking-service-c-interface:: For general characteristics of the locking service interface, see note locking-service::. For general information about loadable functions, see Adding a Loadable Function (https://dev.mysql.com/doc/extending-mysql/5.7/en/adding-loadable-function.html).

Installing or Uninstalling the Locking Service Function Interface

The locking service routines described in note locking-service-c-interface:: need not be installed because they are built into the server. The same is not true of the loadable functions that map onto calls to the service routines: The functions must be installed before use. This section describes how to do that. For general information about loadable function installation, see note function-loading::.

The locking service functions are implemented in a plugin library file located in the directory named by the 'plugin_dir' system variable. The file base name is 'locking_service'. The file name suffix differs per platform (for example, '.so' for Unix and Unix-like systems, '.dll' for Windows).

To install the locking service functions, use the *note 'CREATE FUNCTION': create-function. statement, adjusting the '.so' suffix for your platform as necessary:

 CREATE FUNCTION service_get_read_locks RETURNS INT
   SONAME 'locking_service.so';
 CREATE FUNCTION service_get_write_locks RETURNS INT
   SONAME 'locking_service.so';
 CREATE FUNCTION service_release_locks RETURNS INT
   SONAME 'locking_service.so';

If the functions are used on a replication source server, install them on all replica servers as well to avoid replication problems.

Once installed, the functions remain installed until uninstalled. To remove them, use the *note 'DROP FUNCTION': drop-function. statement:

 DROP FUNCTION service_get_read_locks;
 DROP FUNCTION service_get_write_locks;
 DROP FUNCTION service_release_locks;

Using the Locking Service Function Interface

Before using the locking service functions, install them according to the instructions provided at *note locking-service-function-installation::.

To acquire one or more read locks, call this function:

 mysql> SELECT service_get_read_locks('mynamespace', 'rlock1', 'rlock2', 10);
 +---------------------------------------------------------------+
 | service_get_read_locks('mynamespace', 'rlock1', 'rlock2', 10) |
 +---------------------------------------------------------------+
 |                                                             1 |
 +---------------------------------------------------------------+

The first argument is the lock namespace. The final argument is an integer timeout indicating how many seconds to wait to acquire the locks before giving up. The arguments in between are the lock names.

For the example just shown, the function acquires locks with lock identifiers '(mynamespace, rlock1)' and '(mynamespace, rlock2)'.

To acquire write locks rather than read locks, call this function:

 mysql> SELECT service_get_write_locks('mynamespace', 'wlock1', 'wlock2', 10);
 +----------------------------------------------------------------+
 | service_get_write_locks('mynamespace', 'wlock1', 'wlock2', 10) |
 +----------------------------------------------------------------+
 |                                                              1 |
 +----------------------------------------------------------------+

In this case, the lock identifiers are '(mynamespace, wlock1)' and '(mynamespace, wlock2)'.

To release all locks for a namespace, use this function:

 mysql> SELECT service_release_locks('mynamespace');
 +--------------------------------------+
 | service_release_locks('mynamespace') |
 +--------------------------------------+
 |                                    1 |
 +--------------------------------------+

Each locking function returns nonzero for success. If the function fails, an error occurs. For example, the following error occurs because lock names cannot be empty:

 mysql> SELECT service_get_read_locks('mynamespace', '', 10);
 ERROR 3131 (42000): Incorrect locking service lock name ''.

A session can acquire multiple locks for the same lock identifier. As long as a different session does not have a write lock for an identifier, the session can acquire any number of read or write locks. Each lock request for the identifier acquires a new lock. The following statements acquire three write locks with the same identifier, then three read locks for the same identifier:

 SELECT service_get_write_locks('ns', 'lock1', 'lock1', 'lock1', 0);
 SELECT service_get_read_locks('ns', 'lock1', 'lock1', 'lock1', 0);

If you examine the Performance Schema 'metadata_locks' table at this point, you find that the session holds six distinct locks with the same '(ns, lock1)' identifier. (For details, see *note locking-service-monitoring::.)

Because the session holds at least one write lock on '(ns, lock1)', no other session can acquire a lock for it, either read or write. If the session held only read locks for the identifier, other sessions could acquire read locks for it, but not write locks.

Locks for a single lock-acquisition call are acquired atomically, but atomicity does not hold across calls. Thus, for a statement such as the following, where 'service_get_write_locks()' is called once per row of the result set, atomicity holds for each individual call, but not for the statement as a whole:

 SELECT service_get_write_locks('ns', 'lock1', 'lock2', 0) FROM t1 WHERE ... ;

Caution:

Because the locking service returns a separate lock for each successful request for a given lock identifier, it is possible for a single statement to acquire a large number of locks. For example:

 INSERT INTO ... SELECT service_get_write_locks('ns', t1.col_name, 0) FROM t1;

These types of statements may have certain adverse effects. For example, if the statement fails part way through and rolls back, locks acquired up to the point of failure still exist. If the intent is for there to be a correspondence between rows inserted and locks acquired, that intent is not satisfied. Also, if it is important that locks are granted in a certain order, be aware that result set order may differ depending on which execution plan the optimizer chooses. For these reasons, it may be best to limit applications to a single lock-acquisition call per statement.

Locking Service Monitoring

The locking service is implemented using the MySQL Server metadata locks framework, so you monitor locking service locks acquired or waited for by examining the Performance Schema 'metadata_locks' table.

First, enable the metadata lock instrument:

 mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
     -> WHERE NAME = 'wait/lock/metadata/sql/mdl';

Then acquire some locks and check the contents of the 'metadata_locks' table:

 mysql> SELECT service_get_write_locks('mynamespace', 'lock1', 0);
 +----------------------------------------------------+
 | service_get_write_locks('mynamespace', 'lock1', 0) |
 +----------------------------------------------------+
 |                                                  1 |
 +----------------------------------------------------+
 mysql> SELECT service_get_read_locks('mynamespace', 'lock2', 0);
 +---------------------------------------------------+
 | service_get_read_locks('mynamespace', 'lock2', 0) |
 +---------------------------------------------------+
 |                                                 1 |
 +---------------------------------------------------+
 mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS
     -> FROM performance_schema.metadata_locks
     -> WHERE OBJECT_TYPE = 'LOCKING SERVICE'\G
 *************************** 1. row ***************************
   OBJECT_TYPE: LOCKING SERVICE
 OBJECT_SCHEMA: mynamespace
   OBJECT_NAME: lock1
     LOCK_TYPE: EXCLUSIVE
   LOCK_STATUS: GRANTED
 *************************** 2. row ***************************
   OBJECT_TYPE: LOCKING SERVICE
 OBJECT_SCHEMA: mynamespace
   OBJECT_NAME: lock2
     LOCK_TYPE: SHARED
   LOCK_STATUS: GRANTED

Locking service locks have an 'OBJECT_TYPE' value of 'LOCKING SERVICE'. This is distinct from, for example, locks acquired with the 'GET_LOCK()' function, which have an 'OBJECT_TYPE' of 'USER LEVEL LOCK'.

The lock namespace, name, and mode appear in the 'OBJECT_SCHEMA', 'OBJECT_NAME', and 'LOCK_TYPE' columns. Read and write locks have 'LOCK_TYPE' values of 'SHARED' and 'EXCLUSIVE', respectively.

The 'LOCK_STATUS' value is 'GRANTED' for an acquired lock, 'PENDING' for a lock that is being waited for. You see 'PENDING' if one session holds a write lock and another session is attempting to acquire a lock having the same identifier.

Locking Service Interface Function Reference

The SQL interface to the locking service implements the loadable functions described in this section. For usage examples, see *note locking-service-function-usage::.

The functions share these characteristics:

These locking service functions are available:

 File: manual.info.tmp, Node: keyring-service, Prev: locking-service, Up: plugin-services

5.5.6.4 The Keyring Service ...........................

MySQL Server supports a keyring service that enables internal server components and plugins to securely store sensitive information for later retrieval. MySQL distributions provide a keyring interface that is accessible at two levels:

This section describes how to use the keyring service functions to store, retrieve, and remove keys in the MySQL keyring keystore. For information about the SQL interface that uses functions, note keyring-functions-general-purpose::. For general keyring information, see note keyring::.

The keyring service uses whatever underlying keyring plugin is enabled, if any. If no keyring plugin is enabled, keyring service calls fail.

A 'record' in the keystore consists of data (the key itself) and a unique identifier through which the key is accessed. The identifier has two parts:

The keyring service functions have these characteristics in common:

These keyring service functions are available:

 File: manual.info.tmp, Node: server-loadable-functions, Next: multiple-servers, Prev: server-plugins, Up: server-administration