MySQL 5.1 Plugin Development

Chapter 1. Compiling and Using MySQL Plugins

As you progress through this book you will see several examples of how to use MySQL plugins. This chapter is designed to help you compile and install the UDFs (User Defined Functions) and MySQL plugins that will be created in the following chapters. Do not miss it; you will need this knowledge in every single chapter later on.

UDF libraries

MySQL comes with a small utility called mysql_config, which aids the supply of some of the required options to your compiler. In most cases you need:

shell$ mysql_config --cflags

This will print something such as the following:

-I/opt/mysql-5.1/include/mysql -g -Wreturn-type -Wtrigraphs -W -Wformat -Wsign-compare -Wunused-function -Wunused-value -Wunused-parameter -m64 -DUNIV_LINUX

Both MySQL plugins and UDFs need to be compiled as shared libraries. How this is done depends on the platform.

Linux

Under Linux, UDFs should be compiled as follows:

gcc -o udf_library.so udf_library.c `mysql_config --cflags` -shared -fPIC

The mysql_config in backticks will apply the results for the command as switches to gcc, so the include directories as well as other required build options are automatically inserted. The -shared option tells the compiler that we are creating a shared library and ‑fPIC enables Position Independent Code, which is required for dynamic linking of this shared library.

Mac OS X

Compiling on Mac OS X is very much like compiling on Linux, but the way shared libraries are defined is slightly different:

gcc -o udf_library.so udf_library.c `mysql_config --cflags` -bundle

A bundle is the Mac OS X equivalent of a shared library. If the UDF needs to call functions in the server binary (for example, if it uses the DBUG debugging facility) the command line will need to be:

gcc -o udf_library.so udf_library.c `mysql_config --cflags` -bundle -Wl,-undefined -Wl,dynamic_lookup

Windows

Setting up for compiling UDFs in Windows is generally more involved than in other operating systems.

As everywhere, we need to have the required libraries and include files installed. To do this we run the MySQL installer. If you already have MySQL installed, you can use this tool to modify your installation. The following screenshot shows that we have selected Custom to do this, but a complete install will also give the required files:

Now we need to select Developer Components and then C Include Files / Lib Files to have them included in the installation. Once this is done the installer should look similar to this:

Also, you need to have Microsoft Visual Studio installed. There are free express editions available from the Microsoft website, which we can use.

In Visual Studio we need to create a new empty project to put our source code into and set up the build environment:

Then we need to add a source file to this project. We can either create a new .cpp file or add an existing one to a project:

Now we need to modify the project properties to set up everything required to compile the UDF. To start with, inside the General configuration section, we need to set the Configuration Type to a .dll file (a Windows dynamic link library):

Then in the C/C++ section we need to add the MySQL include path to Additional Include Directories:

Finally, we need to create a definitions file that lists the functions from this library which we wish to export for MySQL to use. It may look as follows:

EXPORTS
udf_hello_world
udf_hello_world_init
udf_hello_world_deinit

This is then added to the Linker configuration in the Input section under Module Definition File. This gives a hand-typed dialog, so we need to type in the full path to the definitions file we just created:

We can then compile our UDF and, if successful, we will have a brand new .dll file:

Installing a UDF

Now that we have our UDF, we need to install it in the MySQL server. For security reasons MySQL will only load plugins and UDFs from the location defined in the plugin_dir system variable. This variable can only be set during the startup of the MySQL server. By default it is in the lib/mysql/plugin subdirectory inside the directory where MySQL is installed. So we need to put our UDF library there.

We can then tell MySQL to load the library using:

CREATE FUNCTION my_udf_function RETURNS STRING SONAME 'my_udf_function.so'

More details on how to use this syntax and how to solve UDF loading errors are in the UDF chapter of this book.

UDF libraries

MySQL comes with a small utility called mysql_config, which aids the supply of some of the required options to your compiler. In most cases you need:

shell$ mysql_config --cflags

This will print something such as the following:

-I/opt/mysql-5.1/include/mysql -g -Wreturn-type -Wtrigraphs -W -Wformat -Wsign-compare -Wunused-function -Wunused-value -Wunused-parameter -m64 -DUNIV_LINUX

Both MySQL plugins and UDFs need to be compiled as shared libraries. How this is done depends on the platform.

Linux

Under Linux, UDFs should be compiled as follows:

gcc -o udf_library.so udf_library.c `mysql_config --cflags` -shared -fPIC

Mac OS X

Compiling on Mac OS X is very much like compiling on Linux, but the way shared libraries are defined is slightly different:

gcc -o udf_library.so udf_library.c `mysql_config --cflags` -bundle

gcc -o udf_library.so udf_library.c `mysql_config --cflags` -bundle -Wl,-undefined -Wl,dynamic_lookup

Windows

Setting up for compiling UDFs in Windows is generally more involved than in other operating systems.

Now we need to select Developer Components and then C Include Files / Lib Files to have them included in the installation. Once this is done the installer should look similar to this:

Also, you need to have Microsoft Visual Studio installed. There are free express editions available from the Microsoft website, which we can use.

In Visual Studio we need to create a new empty project to put our source code into and set up the build environment:

Then we need to add a source file to this project. We can either create a new .cpp file or add an existing one to a project:

Then in the C/C++ section we need to add the MySQL include path to Additional Include Directories:

Finally, we need to create a definitions file that lists the functions from this library which we wish to export for MySQL to use. It may look as follows:

EXPORTS
udf_hello_world
udf_hello_world_init
udf_hello_world_deinit

We can then compile our UDF and, if successful, we will have a brand new .dll file:

Installing a UDF

We can then tell MySQL to load the library using:

CREATE FUNCTION my_udf_function RETURNS STRING SONAME 'my_udf_function.so'

More details on how to use this syntax and how to solve UDF loading errors are in the UDF chapter of this book.

Plugin libraries

Building and installing plugin libraries is very much like building and installing UDFs. The include and library paths are the same but some further build options are needed. This is slightly complicated by the fact that some plugin types (namely Information Schema and Storage Engine plugins) require the MySQL source to be downloaded for the version of the MySQL server you have installed. This is so that the plugin can have access to data and functions that are only "half-public" and are not declared in the installed C header files.

Linux

When compiling on Linux and using just the normal plugin API we can compile in the same way as with UDFs:

gcc -omy_plugin.so my_plugin.c `mysql_config --cflags` -shared -fPIC -DMYSQL_DYNAMIC_PLUGIN

Notice that the main difference here is -DMYSQL_DYNAMIC_PLUGIN. This sets up the necessary environment for the plugin at compile time.

For plugins that require access to the MySQL server source, compiling is slightly different (suppose, the MySQL source tree is in /Sources/mysql‑5.1.35):

gcc omy_plugin.so my_plugin.cc `mysql_config cflags` —I/Sources/mysql 5.1.35/include/ I/Sources/mysql 5.1.35/regex —I/Sources/mysql 5.1.35/sql shared fPIC fno exceptions —fno rtti DMYSQL_DYNAMIC_PLUGIN

Typically, such a plugin will be in C++, not C. It is compiled exactly the same way the main server is—without exceptions or runtime type identification. Technically, it could use exceptions, but then it may need to use g++ instead of gcc as a C++ compiler. Either way, it needs extra include paths that point to the include/, regex/, and sql/ directories of the MySQL source tree.

Mac OS X

Just as in the UDF case, compiling plugins on Mac OS X is almost the same as on Linux. You can use the same command line and only replace ‑shared ‑fPIC with ‑bundle or bundle ‑Wl, ‑undefined ‑Wl,dynamic_lookup as explained before.

Windows

In Windows we can compile MySQL plugins that do not require the inclusion of the MySQL source code (everything except Information Schema and Storage Engine plugins) using a process very similar to compiling UDFs.

First, we need to create an empty project file to contain the source and build environment:

We can then add or create a .cpp file containing the source for our plugin:

This project needs to be a .dll, not an executable one. We can set this in the project's Property Pages dialog:

We now need to set up the C/C++ include paths so that the MySQL include path is in them:

This final step is different to compiling the UDFs. We need to add a C/C++ preprocessor definition so that the include files set up everything we need for a MySQL plugin. To do this we simply add MYSQL_DYNAMIC_PLUGIN to the definitions list:

Installing a plugin

Just as with UDFs, our MySQL plugin needs to be in plugin_dir before it can be added to MySQL. Once it is located there the syntax is very simple. All of the details about how to use the plugin are in the plugin itself. So we simply need:

INSTALL PLUGIN my_plugin SONAME 'my_plugin.so'

Automatic builds, packaging

Specifying all compiler options manually, as we did in a previous section, gets more complicated as the number of files grows. When a plugin consists of more than a couple of files, an appropriate Makefile becomes almost a requirement. And it is absolutely unavoidable if we want to distribute our great plugin, as we cannot expect our users to copy and paste complex command lines from a README file. We want the process of configuring and building a plugin to be as simple as possible. But first we need to decide whether a plugin should be built from inside the MySQL source tree or standalone.

UDFs and standalone plugins

UDFs and certain plugin types (for example, full-text parser plugins, some Daemon plugins, or newer plugin types added after MySQL 5.1) do not require MySQL sources for building; the API for them is complete and self-sufficient. These plugins can be distributed and built independently from MySQL. Writing a Makefile or configure.ac for such a plugin does not differ from writing them for any other project—we only need to set the installation path correctly. When using automake and libtool, a simple Makefile.am can look like this:

plugindir= $(libdir)/mysql/plugin
plugin_LTLIBRARIES= my_plugin.la
my_plugin_la_SOURCES= my_plugin.c
my_plugin_la_LDFLAGS= -module -rpath $(plugindir)
my_plugin_la_CFLAGS= -DMYSQL_DYNAMIC_PLUGIN

This file sets the installation directory to be mysql/plugin/ inside the library path, which is usually /usr/lib. However, strictly speaking, the user has to use the same library path that his MySQL installation uses. It specifies the build target to be my_plugin.la—it is a libtool control file, a text file with information about my_plugin.so. The latter will be built automatically. It tells the libtool that we are building a library for dynamic loading with dlopen() (the -module option does that) and where it will be installed. The last line adds ‑DMYSQL_DYNAMIC_PLUGIN to the compiler command line. There is no need to specify ‑fPIC, ‑shared, or ‑bundle; libtool will use them automatically, depending on the platform we are building on. It knows a large number of operating systems, compilers, linkers, and their corresponding command-line switches for building dynamically loaded modules.

In addition to Makefile.am, a complete project will need a configure.ac file, AUTHORS, NEWS, ChangeLog, and README files. The last four files are required by automake, but they can be empty. The configure.ac file is used by autoconf to generate a configure script, which, in turn, will generate Makefile. A minimal configure.ac could be as simple as:

AC_INIT(my_plugin, 0.1)
AM_INIT_AUTOMAKE
AC_PROG_LIBTOOL
AC_CONFIG_FILES([Makefile])
AC_OUTPUT

It sets the name and version of our software package, initializes automake and libtool, and specifies that the result of the configure script should be a Makefile.

Plugins that are built from the MySQL source tree

If we need to have access to the MySQL source tree for our plugin, we can at least do it with style. Plugins that are built from the MySQL source tree can be integrated seamlessly into the MySQL build system. Additionally, we will get support for Microsoft Windows builds and the ability to link the plugin statically into the server, so that it becomes a part of the mysqld binary. Unlike standalone plugins, we will only need three auxiliary files here.

On UNIX-like systems, MySQL 5.1 is built using autotools and make. A plug.in file will be the source file for autoconf, and Makefile.am for automake. To build MySQL on Windows one needs CMake, and thus our plugin should come with a CMakeLists.txt file. All of these three files can use the full power of autotools or CMake, if necessary, but for a minimal working plugin they only need to contain a few simple lines.

plug.in

The plug.in file describes the plugin to the MySQL configure script. A plugin is detected automatically by autoconf as long as its plug.in file can be found in a directory located in the plugin/ or storage/ subdirectory in the MySQL source tree (in other words, it should be either plugin/*/plug.in or storage/*/plug.in). A plug.in file can use all autoconf and m4 macros as usual. Additionally, MySQL defines a few macros specifically for using in the plug.in files. They all are documented in the config/ac-macros/plugin.m4 file in the MySQL source tree. The most important of them are described as follows:

MYSQL_PLUGIN([name],[long name], [description], [group,group...])
This is usually the first line in any plug.in file. This macro is mandatory. It declares a new plugin. The name will be used in the configure options such as ‑‑with‑plugin‑foo and ‑‑without‑plugin‑foo. The long name and the description will be printed in the ./configure ‑‑help output. "Groups" are preset configuration names that one can specify in the ‑‑with‑plugin=group option. Any group name can be used, but max, max‑no‑ndb, and default are commonly used. Most plugins add themselves to the max and max‑no‑ndb groups.
MYSQL_PLUGIN_STATIC([name],[libmyplugin.a])
This macro declares that a plugin name supports static builds, that is, it can be built as a static library and linked statically into the server binary. It specifies the name of this static library, which can be later referred to in Makefile.am as @plugin_myplugin_static_target@. It will be expanded to libmyplugin.a if a static build is selected, otherwise it will be empty.
MYSQL_PLUGIN_DYNAMIC([name],[myplugin.la])
Similarly, this macro declares that a plugin can be built as a shared library and loaded into the server dynamically. It introduces a Makefile.am substitution @plugin_myplugin_dynamic_target@, which is myplugin.la if this shared library needs to be built, and empty otherwise.
MYSQL_PLUGIN_ACTIONS([name],[ ACTION-IF-SELECTED ])
The ACTION‑IF‑SELECTED code will be executed only if this plugin is selected by configure either for static or dynamic builds. Here we can check for system headers, libraries, and functions that are used by the plugin. Normal AC_ macros can be used here freely.

An example of a plug.in file can look like

MYSQL_PLUGIN(my_plugin,[My Plugin Example],
[An example of My Plugin], [max,max-no-ndb])
MYSQL_PLUGIN_STATIC(my_plugin,[libmy_plugin.a])
MYSQL_PLUGIN_DYNAMIC(my_plugin,[my_plugin.la])

With such a file in place, say in plugin/my_plugin/plug.in, all we need to do is to run autoreconf ‑f to recreate the configure script. After that, there is no distinction between our plugin and official MySQL plugins:

$ ./configure --help
`configure' configures this package to adapt to many kinds of systems.
...
--with-plugins=PLUGIN[[[,PLUGIN..]]]
Plugins to include in mysqld. (default is: none)
Must be a configuration name or a comma separated
list of plugins.
Available configurations are: none max max-no-ndb
all.
Available plugins are: partition daemon_example
ftexample archive blackhole csv example federated
heap ibmdb2i innobase innodb_plugin myisam
myisammrg my_plugin ndbcluster.
...
=== My Plugin Example ===
Plugin Name: my_plugin
Description: An example of My Plugin
Supports build: static and dynamic
Configurations: max, max-no-ndb

A new plugin is mentioned in the "available plugins" list and described in detail at the end of the configure ‑‑help output.

Makefile.am

As in the case of standalone plugins, we need a Makefile.am file. It will be converted by automake and the configure script to a Makefile, and it defines how the plugin, static or shared library, should be built. This file is more complex than for standalone plugins because it needs to cover both static and dynamic builds. Of course, when a plugin supports only one way of linking, only static or only dynamic, Makefile.am gets much simpler. Let's analyze it line by line:

pkgplugindir = $(pkglibdir)/plugin
INCLUDES = -I$(top_srcdir)/include -I$(top_builddir)/include \
-I$(top_srcdir)/sql
pkgplugin_LTLIBRARIES = @plugin_my_plugin_shared_target@
my_plugin_la_LDFLAGS = -module -rpath $(pkgplugindir)
my_plugin_la_CXXFLAGS= -DMYSQL_DYNAMIC_PLUGIN
my_plugin_la_SOURCES = my_plugin.c
noinst_LIBRARIES = @plugin_my_plugin_static_target@
libmy_plugin_a_SOURCES= my_plugin.c
EXTRA_LTLIBRARIES = my_plugin.la
EXTRA_LIBRARIES = libmy_plugin.a
EXTRA_DIST = CMakeLists.txt plug.in

The file starts with defining pkgplugindir—a place where a plugin will be installed.

Then we set the search path for the #include directives; it needs to contain at least the include/ directory where most of the headers are, and often the sql/ directory too, especially when we need to access internal server structures.

Now we can specify automake build rules for the targets. A shared target is a libtool library (LTLIBRARIES) that should be installed in pkgplugindir (because we used the pkgplugin_ prefix). And we specify the source files, compiler and linker flags that are needed to build the my_plugin.la library. If a user decides not to build a dynamic version of our plugin, @plugin_my_plugin_shared_target@ will be empty and no libtool library will be built.

Similarly, we specify rules for the static target. It is a library (LIBRARIES), and it should not be installed (noinst_). Indeed, as it will be linked into the server statically, becoming a part of the server binary, there is no need to install it separately. In this case, we do not need any special compiler or linker flags, we only specify the sources.

Because a user may decide to build either a static or a shared library, the name of the build target is not known before the configure script is run. However, automake needs to know all possible targets in advance, and we list them in the EXTRA_ variables.

We end the file by listing all remaining files that are part of the plugin source distribution, but are not mentioned in other rules. The automake needs to know about them, otherwise the make dist command will work incorrectly.

CMakeLists.txt

In MySQL 5.1, of all plugin types, only Storage Engine plugins can be integrated into the MySQL build system on Windows. One does this by providing an appropriate CMakeLists.txt file. All of the CMake power is available there, but a minimal CMakeLists.txt file is as simple as this:

INCLUDE("${PROJECT_SOURCE_DIR}/storage/mysql_storage_engine.cmake")
SET(my_plugin_SOURCES my_plugin.c)
MYSQL_STORAGE_ENGINE(my_plugin)

We only specify the name of the storage engine, my_plugin, and the source files, and include the file that does all of the heavy job.

MySQL 5.1 Plugin Development: Extend MySQL to suit your needs with this unique guide into the world of MySQL plugins

What do you get with eBook?

Product Details