A Look Inside a MySQL Daemon Plugin

10 min read

A look inside a Daemon plugin

Unlike UDFs, MySQL plugins store all of the metadata in the plugins shared library. So when installing a plugin you only need to specify the name of the plugin and its shared library filename. This eliminates much of the user error while installing. With UDFs it is very easy to choose the wrong return type or forget the AGGREGATE keyword, but with plugins this is not possible.

Why write a Daemon plugin

Just like UDFs and other MySQL plugin types the Daemon plugin can be used to add extra functionality to MySQL with the same advantages and disadvantages.

Daemon plugins are ideal for writing code that needs to reside in the server but does not need to communicate with it—such as a heartbeat plugin or monitoring plugins—because the simple Daemon plugin API does not provide any means for a server and a plugin to communicate with each other.

Installing and using Daemon plugins

Installing plugins is relatively easy because all of the information about a plugin is stored inside it. To install a plugin we can use the INSTALL PLUGIN statement as follows:

mysql> INSTALL PLUGIN my_plugin SONAME 'my_plugin.so';

Likewise, to remove a plugin we use:

mysql> UNINSTALL PLUGIN my_plugin;

When a plugin is installed it is initialized instantly and this means that the code we write will start automatically when our plugin is installed.

Upon installing a plugin it is added to the mysql.plugin table so MySQL knows it is installed and can load it again on startup. In other words, similar to UDFs, all installed plugins are loaded automatically when a server is started.

A plugin is de-initialized when either it is uninstalled or the MySQL server is being shut down. It is worth noting at this time that if the MySQL server crashes for any reason the de-initialization of the plugin will not happen.

If a plugin is installed, we can prevent it from being loaded and executed at startup with the –disable-plugin-my-plugin or –plugin-my-plugin=OFF commands. If we do not do that MySQL will try to load it because the default behavior is –plugin-my-plugin=ON. If the plugin fails to load, MySQL will note that fact in the error log and will continue without this plugin. If we want to be sure that a plugin is absolutely loaded in the server, and that the server will not simply ignore a plugin failure, we can use –plugin-my-plugin=FORCE. In this mode the server will exit if our plugin fails to load.

As we can see below, the mysql.plugin table simply contains the plugin name and the filename for the shared library containing the plugin:

mysql> SELECT * FROM mysql.plugin;
| name | dl |
| my_plugin | my_plugin.so |
1 row in set (0.01 sec)

MySQL has a SHOW command to give us information about installed plugins. This is very useful to see if a plugin is actually running. If there was a problem during initialization then the status for the plugin will be marked as DISABLED. A sample output for SHOW PLUGINS can be seen below:

*************************** 11. row ***************************
Name: my_plugin
Status: ACTIVE
Library: my_plugin.so
License: GPL
11 rows in set (0.00 sec)

Information Schema also includes a table for use with plugins, and it contains more detail than SHOW PLUGINS. It shows version information supplied by the plugin as well as the plugin description:

mysql> SELECT * FROM information_schema.plugins WHERE PLUGIN_NAME='my_

*************************** 1. row ***************************
PLUGIN_NAME: my_plugin
PLUGIN_LIBRARY: my_plugin.so
PLUGIN_AUTHOR: Andrew Hutchings
PLUGIN_DESCRIPTION: Daemon example, shows a declaration
1 row in set (0.00 sec)

Technically, loading of plugins is very similar to loading of UDFs. Problems that can arise, ways of solving them, and error messages are similar to those of UDFs.

The role of a version

As we have seen, there are three two-component version numbers in the INFORMATION_SCHEMA.PLUGINS table. One of them, PLUGIN_VERSION, is purely informational. It is a number that a plugin author can specify arbitrarily, and MySQL itself does not do anything with it. The other two are very important though. They are used to protect the API, to make sure that if a plugin is loaded it uses the same API version that the server provides. This is one of the main differences to UDFs. UDF API is not versioned. Hence, it was not developed and still has only those features that it did in 3.21.24. Extending UDF API is risky; any change and old UDFs may start crashing the server.

Plugin API, on the other hand, is safe. It is protected by a version, and this version is part of every plugin library, the API version that the plugin was compiled against. When a plugin is loaded the server verifies that the version is supported by the server and refuses to load a plugin otherwise. That is, the server can guarantee that all loaded plugins are fully compatible with the server, and no crash can happen because of API mismatch.

The API is protected with two version numbers, as it contains two parts—one is common to all plugin types. It is version 1.0, as can be seen in the PLUGIN_LIBRARY_ VERSION column above. The other one is specific to each plugin type. For Daemon plugins this version is 50147.0, as seen in the PLUGIN_TYPE_VERSION column, and it is derived from the MySQL server version (which was 5.1.47 in my examples).

Defining Daemon plugins

The most basic of Daemon plugins needs no code at all; only a declaration is required. A plugin declaration is an instance of a st_mysql_plugin structure:

struct st_mysql_plugin
int type;
void *info;
const char *name;
const char *author;
const char *descr;
int license;
int (*init)(void *);
int (*deinit)(void *);
unsigned int version;
struct st_mysql_show_var *status_vars;
struct st_mysql_sys_var **system_vars;
void *__reserved1

The type defines what type of plugin this will be, which in turn defines what it can do. In MySQL 5.1, type can be set to one of the following enumerated values:

Here we are talking about Daemon plugins so this should be set to MYSQL_DAEMON_PLUGIN.

The info member is a pointer to the descriptor of the plugin and its members. It contains the information specific to this particular plugin type (while the st_mysql_plugin structure itself contains the information applicable to any plugin, independently of its type). It always starts with an API version number and for Daemon plugins this is all it contains. For other plugin types it may also contain plugin methods that the server will call, but Daemon plugins are not designed to communicate with the server, and their descriptor structure contains nothing besides the version:

struct st_mysql_daemon my_daemon_plugin =

Next we have the name member, which specifies the name of the plugin as it will be used by MySQL. This is the name that needs to be used in the INSTALL PLUGIN statement, the name that will be seen in SHOW PLUGINS and SHOW ENGINES, the name that all plugin configuration variables and command-line options will start from. That is, the name of the plugin should be a valid SQL identifier and should be good for the command line too. A safe choice would be a name that consists of only Latin letters, digits, and an underscore. Plugin names are not case-sensitive.

The author member is a string containing details about the author; it can contain anything we wish. It must be in UTF-8 and can be arbitrarily long, but MySQL will only show the first 64 characters of it.

The final string member is descr, which should contain a description of the plugin. Again we are free to put whatever we like here, but we would normally put a short line stating what the plugin does. Again, it is supposed to be UTF-8, but it can be as long as you want.

In the next member, each plugin specifies its license. This does not strictly do anything as such, but should help with accidental distribution of a plugin under the wrong license. There are currently three possible values for the license member:

Then we come to the init and deinit members, which are pointers to the plugin initialization and de-initialization functions. The initialization function is called when the plugin is loaded during INSTALL PLUGIN or server startup. The de-initialization function is called when a plugin is unloaded, which, again, can happen for two reasons, UNINSTALL PLUGIN or server shutdown. In a Daemon plugin the initialization function is often used to fork a thread to run the main function of the plugin. Both the initialization and the de-initialization functions should return 0 on success or 1 on failure.

The version member should be used for the current version of our plugin. A two-component version is encoded as a hexadecimal number, where the lower 8 bits store the second component (minor version) and all others bits store the first component (major version). For example, if the version is set to 0x205, MySQL will show it as “2.5”, and if the version is set to 0x123FF, MySQL will show it as “291.255”. Unfortunately, there is no way to store in this member a more complex version such as “1.10.14b-RC2”.

MySQL has many status variables that can be seen with the SHOW STATUS statement, and there are different third-party tools that analyze this data, how the status variables change over time, draw graphs, and so on. A plugin can benefit from that and make its status and various statistics and performance values visible as MySQL status variables. A pointer to the list of the plugin status variables is stored in the status_vars member.

Similarly, there is a SHOW VARIABLES statement. It lists all MySQL system variables, variables that are used to alter the behavior of the server. They can have serverwide or session-only effect, some of them can be set on the command line or in the configuration file. They can be modifiable run-time or read-only. This is all available to plugins too. A plugin can add new system variables to the server, global or session, with or without command-line option support, modifiable or read-only. As we would expect, a pointer to the array of these variables goes into the system_vars member.

Finally there is one __reserved1 member, which is unused in MySQL 5.1 and should be set to NULL.

MySQL provides two macros that help to declare plugins. A plugin declaration starts from the mysql_declare_plugin() macro. It takes one argument, a unique identifier for this plugin library, it will be used automatically as needed to avoid name clashes when plugins are linked statically into the server. This identifier must be the same one that was used as a plugin name in the plug.in file. We can put many plugins in one library, but they all need to be declared in one place, after the mysql_declare_plugin() macro, separated by commas. We end the list of plugin declarations with a mysql_declare_plugin_end macro.

A complete example of the plugin declarations can be seen as follows:


"Andrew Hutchings (Andrew.Hutchings@Sun.COM)",
"Daemon example, shows a declaration",

"Sergei Golubchik (serg@mariadb.org)",
"Another Daemon example, shows a declaration",

This declares two plugins. We can see that the first one:

  • is a Daemon plugin
  • has an info structure called my_plugin_info
  • is called my_plugin and was written by me (Andrew Hutchings)
  • is described as an example plugin
  • is GPL licensed
  • has initialization and de-initialization functions
  • is of version 1.0
  • has no system or status variables

The second plugin can be interpreted similarly. It is also a Daemon plugin of version 49407.238 with initialization function, without de-initialization function, with both status and system variables.


Please enter your comment!
Please enter your name here