A Look Inside a MySQL Daemon Plugin

Exclusive offer: get 50% off this eBook here
MySQL 5.1 Plugin Development

MySQL 5.1 Plugin Development — Save 50%

Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins

$26.99    $13.50
by Andrew Hutchings | August 2010 | MySQL Open Source Web Development

In this article, by Andrew Hutchings, co-author of MySQL 5.1 Plugin Development, we will be introduced to the MySQL Plugin API. It talks about the most simple plugin type—Daemon plugins. It starts with the basic structure of a plugin—what a plugin declaration should look like, what plugin types are, and so on. Then it describes features common to all plugin types—initialization and de-initialization callbacks, status variables, and configuration system variables.

We will specifically cover:

  • A look inside a Daemon plugin
  • Why write a Daemon plugin
  • Installing and using Daemon plugins
  • The role of a version
  • Defining Daemon plugins

(For more resources on MySQL, see here.)

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:

mysql> SHOW PLUGINS\G
....
*************************** 11. row ***************************
Name: my_plugin
Status: ACTIVE
Type: DAEMON
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_
plugin'\G

*************************** 1. row ***************************
PLUGIN_NAME: my_plugin
PLUGIN_VERSION: 1.0
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: DAEMON
PLUGIN_TYPE_VERSION: 50147.0
PLUGIN_LIBRARY: my_plugin.so
PLUGIN_LIBRARY_VERSION: 1.0
PLUGIN_AUTHOR: Andrew Hutchings
PLUGIN_DESCRIPTION: Daemon example, shows a declaration
PLUGIN_LICENSE: GPL
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 =
{ MYSQL_DAEMON_INTERFACE_VERSION };

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:

mysql_declare_plugin(my_plugin)
{
MYSQL_DAEMON_PLUGIN,
&my_plugin_info,
"my_plugin",

"Andrew Hutchings (Andrew.Hutchings@Sun.COM)",
"Daemon example, shows a declaration",
PLUGIN_LICENSE_GPL,
my_plugin_init,
my_plugin_deinit,
0x0100,
NULL,
NULL,
NULL
},
{
MYSQL_DAEMON_PLUGIN,
&my_plugin2_info,
"my_plugin2",

"Sergei Golubchik (serg@mariadb.org)",
"Another Daemon example, shows a declaration",
PLUGIN_LICENSE_GPL,
my_plugin2_init,
NULL,
0xC0FFEE,
status,
vars,
NULL
}
mysql_declare_plugin_end;

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.

MySQL 5.1 Plugin Development Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins
Published: August 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

Read more about this book

(For more resources on MySQL, see here.)

Status variables

Status variables can be used to give feedback to the user and any application that reads status variables. To create them we need to define a zero-terminated array of structures of the type st_mysql_show_var. This structure is defined in plugin.h as:

struct st_mysql_show_var {
const char *name;
char *value;
enum enum_mysql_show_type type;
};

In this structure name is the name of the status variable that will be seen when executing SHOW STATUS. The value member is a pointer to the memory that will contain the data for this status variable. It will be casted to the appropriate pointer type and dereferenced as needed. What it will be casted to depends on the type member, according to the following table:

The last two types are special. Elements of these types do not define rows in SHOW STATUS, they define other elements of the st_mysql_show_var structure.

SHOW_ARRAY specifies that value is not really a value, but a pointer to another zero-terminated array of status variables. The corresponding name of the SHOW_ARRAY element will be used as a prefix added to all the names of status variables in the referenced array. The variables beginning with Com_ in the normal SHOW STATUS output is an example of this in action.

The SHOW_FUNC function is more interesting. The value is interpreted as a pointer to a function that generates the st_mysql_show_var structure. This function should have the following prototype:

int my_status_var(MYSQL_THD thd,
struct st_mysql_show_var *var, char *buff)

The first argument is a pointer to the thread object of the current connection, var points to a st_mysql_show_var structure that we need to fill in, and buff is a preallocated 1024-byte buffer. The function needs to set var->type and var->value (the var->name member will be ignored by MySQL) to form a valid status variable structure. And if needed, var->type can be set to SHOW_ARRAY or even to SHOW_FUNC again, and MySQL will handle this situation correctly. A buffer buff is provided as convenience storage; the function may store the value there and point var->value to it. The return value of this function is ignored.

So, putting this together we can have something similar to the following to make our plugin add new status variables to a server:


struct st_mysql_show_var my_status_vars[]=
{
{"data_size", (char *)&data_size, SHOW_LONG},
{"avg_text_size", (char *)&avg_text_size, SHOW_LONGLONG},
{0,0,0}
};

We would then use my_status_vars in the plugin declaration.

System variables

If status variables can be seen as output variables, then system variables can be seen as input variables. MySQL plugins can have variables that are visible in SHOW VARIABLES so that users can modify the settings of a plugin. They can also be set at the command line when starting mysqld if the options are set to do this. To create system variables we use an array of macros as follows:

struct st_mysql_sys_var* my_sysvars[]= {
MYSQL_SYSVAR(my_var),
MYSQL_SYSVAR(my_other_var),
NULL
};

It does not create any system variables by itself, it only creates a list of variables to put into a plugin declaration. The MYSQL_SYSVAR() macro is expanded into a pointer to a system variable structure. Such a structure needs to be created first. In other words, our system variables my_var and my_other_var need to be declared before we can put them in an array. We do this by using other macros depending on the type of variable we require. Macros to create global variables are summarized in the following table:

To create session, or thread-local, variables we need to use a different set of macros. They start with MYSQL_THDVAR instead of MYSQL_SYSVAR, and they do not take the varname parameter:

The parameters of all these macros are as follows:

The opt parameter helps you define extra options for the variable. Multiple options can be used by using an or ('|') to join them together. A description of these options can be seen as follows:

For ENUM and SET variables we need to create a TYPELIB structure that provides a list of allowed values. A TYPELIB structure is defined in include/typelib.h as follows:

typedef struct st_typelib {
unsigned int count;
const char *name;
const char **type_names;
unsigned int *type_lengths;
} TYPELIB;

However, we only need to set count correctly and put the list of values into type_names as an array of strings. Other TYPELIB members are used in different places in MySQL, but not for system variables.

Now, let's look at the examples:

static char turbo;
static MYSQL_SYSVAR_BOOL(turbo_mode, turbo,
PLUGIN_VAR_READONLY | PLUGIN_VAR_NOCMDARG,
"Enabled <<turbo>> mode", NULL, NULL, 0);

Suppose, our plugin (called "ourplugin") supports a turbo mode, and when enabled everything works much faster. Unfortunately, it cannot activate this turbo mode at runtime, and once a plugin is started, the mode cannot be changed. The variable declaration above does just that. First, it declares a static C variable turbo. If we need to check if the turbo mode is active, we simply write if (turbo). Then we create a global MySQL system variable that is bound to our turbo variable. The variable is called turbo_mode, and being a variable of ourplugin, its full name will be ourplugin_turbo_mode. For example, to see its value with a SELECT statement, a user will need to type:


mysql> SELECT @@global.ourplugin_turbo_mode;

We have declared this variable with the PLUGIN_VAR_READONLY flag to prevent runtime modifications of it with the SET statement. Our plugin simply cannot enable turbo mode at runtime. But it can be activated at startup with a command-line option:

shell$ mysqld_safe --enable-ourplugin-turbo-mode

It can also be activated from a configuration file. This variable has no custom check or update functions, and by default (see the last parameter in the declaration), the turbo mode is disabled.


static unsigned long long log_size;
static MYSQL_SYSVAR_ULONGLONG(log_size, log_size,
PLUGIN_VAR_RQCMDARG, "Upper limit for a log file size",
NULL, NULL, 102400, 1024, 1099511627776, 1024);

The second example for our hypothetical plugin introduces a variable that limits the size of log files. It is a global updatable system variable, one can modify its value with

mysql> SET GLOBAL ourplugin_log_size=20480;

or on the command line

shell$ mysqld_safe --ourplugin-log-size=20480

Because we have specified PLUGIN_VAR_RQCMDARG, the corresponding command-line option must be used with an argument. It will be an error to write ––ourplugin-log-size and not provide a number. In the declaration, we have specified that a value of our variable must between 1024 and 1099511627776 (in other words, 1KB-1TB), and must be always divisible by 1024. If a user tries to set it to an incorrect value, MySQL will adjust it automatically.

static const char *mode_names[] = {
"NORMAL", "TURBO", "SUPER", "HYPER", "MEGA"
};
static TYPELIB modes = { 5, NULL, mode_names, NULL };
static MYSQL_THDVAR_ENUM(mode, PLUGIN_VAR_NOCMDOPT,
"one of NORMAL, TURBO, SUPER, HYPER, MEGA",
NULL, NULL, 0, &modes);

This is a very advanced turbo mode from the first example. It can be in one of the five modes, can be changed at runtime, and even per thread. Different clients may enable different modes; the mode of one connection does not affect others.

The above means that we need to have a session variable for the mode, not a global one. And because it takes one value out of a fixed list of values, MYSQL_THDVAR_ENUM is a good match here. As explained above, we need to create a TYPELIB with the list of allowed values, and use it when declaring our system variable. Just for the sake of this example we make it PLUGIN_VAR_NOCMDOPT and MySQL will not create a ––ourplugin-mode command-line option for this variable. As a result, this variable can only be changed in SQL, with the SET statement. However, usually it is better and certainly more user friendly to provide a corresponding command-line option for every system variable, and a system variable for every command-line option. Now, this is a session variable. How can we access its value from our plugin? Unlike a global system variable, it cannot store its value in a normal C or C++ variable, because such a variable cannot have different values in different threads. This is why MySQL provides a special macro to access a value of a session system variable. We cannot write simply if (mode == 0) as before, but we use if(THDVAR(thd,mode) == 0) instead. It can be assigned too; THDVAR(thd,mode)=3 will work as expected. The type of the THDVAR(thd,mode) will be unsigned long, as listed in the table previously.

There are two parameters of the MYSQL_SYSVAR_* and MYSQL_THDVAR_* macros that we have not touched yet. The check() and update() function callbacks are called when the system variable is altered. The check() function is used to add custom checks to a variable to make sure the setting is acceptable. The update() function can be used to perform additional actions when a variable is updated, for example, when a variable memory_buffer_size is updated one may want to resize the corresponding memory buffer. There are two functions instead of one, check_and_update(), because a SET statement can modify many variables at once. In this case, MySQL first calls the check() function for all these variables, and then their update() functions. In other words, MySQL tries to maintain an "all or nothing" behavior. An update() function, if provided, should try not to fail as all the preconditions for it to succeed should have been verified in a check() function. And a check() function, in turn, should avoid having any side effects, because they will persist even if the update is canceled. These functions have the following prototypes:

int check(MYSQL_THD thd, struct st_mysql_sys_var *var,
void *save, struct st_mysql_value *value);

Here thd is the thread object for the connection that is changing the variable, var is the structure of the system variable, save is a pointer to where the data should be saved, and value is the value passed to the function. A minimal check function should get the result of the expression behind the value in the appropriate type and store it in a *save. If a check function is not provided at all, MySQL will do it automatically.

void update(MYSQL_THD thd, struct st_mysql_sys_var *var,
void *var_ptr, const void *save);

Here we, again, have thd and var pointers. A save pointer gives a new value of the updated variable, and var_ptr is a pointer to the variable to be updated. A minimal update function needs to update the *val_ptr with the value of *save. Again, if no update function is provided, MySQL will do it automatically.

Summary

In the above article we have covered:

  • A look inside a Daemon plugin
  • Why write a Daemon plugin
  • Installing and using Daemon plugins
  • The role of a version
  • Defining Daemon plugins

Further resources on this subject:


MySQL 5.1 Plugin Development Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins
Published: August 2010
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

About the Author :


Andrew Hutchings

Andrew Hutchings has been working in the IT industry almost all his life. He started his development career programming embedded microcontrollers in assembly language for environmental monitoring systems. He then went on to systems administrator for a hosting company, in which he got his first taste for modifying the MySQL source code.

Andrew created his own business in development and DBA work, which led him to be employed by Dennis Publishing, a major UK magazine publisher. In this role as Technical Architect he designed and implemented hosting setups, acting as senior developer and DBA. This was to be his first taste of MySQL clusters and led to his development of ndb_watch, a cluster monitoring daemon.

After gaining Zend Certified Engineer and Certified MySQL Database Administrator qualifications he gained a position at Sun Microsystems as a MySQL Support Engineer. This involves working daily with the MySQL source code and customer API code to help diagnose and fix issues. He is now a specialist in MySQL Cluster and C APIs.

In his spare time Andrew is a community contributor to MySQL with feature patches and bug fixes in both 5.1 and 6.0. He has also contributed to the Drizzle project, a fork of MySQL aimed at having a clean micro-kernel core, committing a significant amount of code to the project.

Books From Packt


MySQL Admin Cookbook
MySQL Admin Cookbook

Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

High Availability MySQL Cookbook
High Availability MySQL Cookbook

MySQL for Python: Database Access Made Easy
MySQL for Python: Database Access Made Easy

Creating your MySQL Database: Practical Design Tips and Techniques
Creating your MySQL Database: Practical Design Tips and Techniques

Alfresco 3 Web Content Management
Alfresco 3 Web Content Management

WordPress Top Plugins
WordPress Top Plugins

TYPO3 4.3 Multimedia Cookbook
TYPO3 4.3 Multimedia Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
v
N
2
K
a
Z
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software