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

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.

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

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:

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.

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.

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.

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:

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'

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.

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.

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])

$ ./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

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

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