Chapter 24 Extending MySQL

The pluggable storage engine architecture used by MySQL Server enables storage engines to be written as plugins and loaded into and unloaded from a running server. For a description of this architecture, see Section 15.2, “Overview of MySQL Storage Engine Architecture”.

For information on how to use the plugin API to write storage engines, see MySQL Internals: Writing a Custom Storage Engine.

MySQL has a built-in parser that it uses by default for full-text operations (parsing text to be indexed, or parsing a query string to determine the terms to be used for a search). For full-text processing, “parsing” means extracting words from text or a query string based on rules that define which character sequences make up a word and where word boundaries lie.

When parsing for indexing purposes, the parser passes each word to the server, which adds it to a full-text index. When parsing a query string, the parser passes each word to the server, which accumulates the words for use in a search.

The parsing properties of the built-in full-text parser are described in Section 12.9, “Full-Text Search Functions”. These properties include rules for determining how to extract words from text. The parser is influenced by certain system variables such as ft_min_word_len and ft_max_word_len that cause words shorter or longer to be excluded, and by the stopword list that identifies common words to be ignored.

The plugin API enables you to provide a full-text parser of your own so that you have control over the basic duties of a parser. A parser plugin can operate in either of two roles:

It is also possible for a parser plugin to operate in both roles. That is, it could extract text from noncleartext input (the front end role), and also parse the text into words (thus replacing the built-in parser).

A full-text plugin is associated with full-text indexes on a per-index basis. That is, when you install a parser plugin initially, that does not cause it to be used for any full-text operations. It simply becomes available. For example, a full-text parser plugin becomes available to be named in a WITH PARSER clause when creating individual FULLTEXT indexes. To create such an index at table-creation time, do this:

CREATE TABLE t
(
  doc CHAR(255),
  FULLTEXT INDEX (doc) WITH PARSER my_parser
) ENGINE=MyISAM;

Or you can add the index after the table has been created:

ALTER TABLE t ADD FULLTEXT INDEX (doc) WITH PARSER my_parser;

The only SQL change for associating the parser with the index is the WITH PARSER clause. Searches are specified as before, with no changes needed for queries.

When you associate a parser plugin with a FULLTEXT index, the plugin is required for using the index. If the parser plugin is dropped, any index associated with it becomes unusable. Any attempt to use a table for which a plugin is not available results in an error, although DROP TABLE is still possible.

For more information about full-text plugins, see Section 24.2.4.4, “Writing Full-Text Parser Plugins”. MySQL 5.5 only supports full-text plugins with MyISAM.

A daemon plugin is a simple type of plugin used for code that should be run by the server but that does not communicate with it. MySQL distributions include an example daemon plugin that writes periodic heartbeat messages to a file.

For more information about daemon plugins, see Section 24.2.4.5, “Writing Daemon Plugins”.

INFORMATION_SCHEMA plugins enable the creation of tables containing server metadata that are exposed to users through the INFORMATION_SCHEMA database. For example, InnoDB uses INFORMATION_SCHEMA plugins to provide tables that contain information about current transactions and locks.

For more information about INFORMATION_SCHEMA plugins, see Section 24.2.4.6, “Writing INFORMATION_SCHEMA Plugins”.

MySQL replication is asynchronous by default. With semisynchronous replication, a commit performed on the master side blocks before returning to the session that performed the transaction until at least one slave acknowledges that it has received and logged the events for the transaction. Semisynchronous replication is implemented through complementary master and client plugins. See Section 17.3.8, “Semisynchronous Replication”.

For more information about semisynchronous replication plugins, see Section 24.2.4.7, “Writing Semisynchronous Replication Plugins”.

As of MySQL 5.5.3, the server provides a pluggable audit interface that enables information about server operations to be reported to interested parties. Currently, audit notification occurs for these operations (although the interface is general and the server could be modified to report others):

Audit plugins may register with the audit interface to receive notification about server operations. When an auditable event occurs within the server, the server determines whether notification is needed. For each registered audit plugin, the server checks the event against those event classes in which the plugin is interested and passes the event to the plugin if there is a match.

This interface enables audit plugins to receive notifications only about operations in event classes they consider significant and to ignore others. The interface provides for categorization of operations into event classes and further division into event subclasses within each class.

When an audit plugin is notified of an auditable event, it receives a pointer to the current THD structure and a pointer to a structure that contains information about the event. The plugin can examine the event and perform whatever auditing actions are appropriate. For example, the plugin can see what statement produced a result set or was logged, the number of rows in a result, who the current user was for an operation, or the error code for failed operations.

For more information about audit plugins, see Section 24.2.4.8, “Writing Audit Plugins”.

MySQL 5.5.7 and up supports pluggable authentication. Authentication plugins exist on both the server and client sides. Plugins on the server side implement authentication methods for use by clients when they connect to the server. A plugin on the client side communicates with a server-side plugin to provide the authentication information that it requires. A client-side plugin may interact with the user, performing tasks such as soliciting a password or other authentication credentials to be sent to the server. See Section 6.3.6, “Pluggable Authentication”.

Pluggable authentication also enables proxy user capability, in which one user takes the identity of another user. A server-side authentication plugin can return to the server the name of the user whose identity the connecting user should have. See Section 6.3.8, “Proxy Users”.

For more information about authentication plugins, see Section 24.2.4.9, “Writing Authentication Plugins”.

The following procedure provides an overview of the steps needed to create a plugin library. The next sections provide additional details on setting plugin data structures and writing specific types of plugins.

A plugin library file includes descriptor information to indicate what plugins it contains.

If the plugin library contains any server plugins, it must include the following descriptor information:

The plugin library also contains the interface functions that are referenced by the general and type-specific descriptors for each plugin in the library.

If the plugin library contains a client plugin, it must include a descriptor for the plugin. The descriptor begins with a fixed set of members common to all client plugins, followed by any members specific to the plugin type. To provide the descriptor framework, invoke two macros from the client_plugin.h header file:

mysql_declare_client_plugin(plugin_type)
   ... members common to all client plugins ...
   ... type-specific extra members ...
mysql_end_client_plugin;

The plugin library also contains any interface functions referenced by the client descriptor.

The mysql_declare_plugin() and mysql_declare_client_plugin() macros differ somewhat in how they can be invoked, which has implications for the contents of plugin libraries. The following guidelines summarize the rules:

When a client program looks for a client plugin that is in a plugin library and not built into libmysqlclient, it looks for a file with a basename that is the same as the plugin name. For example, if a program needs to use a client authentication plugin named auth_xxx on a system that uses .so as the library suffix, it looks in the file named auth_xxx.so. (On OS X, the program looks first for auth_xxx.dylib, then for auth_xxx.so.) For this reason, if a plugin library contains a client plugin, the library must have the same basename as that plugin.

The same is not true for a library that contains server plugins. The --plugin-load option and the INSTALL PLUGIN statement provide the library file name explicitly, so there need be no explicit relationship between the library name and the name of any server plugins it contains.

mysql_declare_plugin(name)
 ... one or more server plugin descriptors here ...
mysql_declare_plugin_end;

struct st_mysql_plugin
{
  int type;             /* the plugin type (a MYSQL_XXX_PLUGIN value)   */
  void *info;           /* pointer to type-specific plugin descriptor   */
  const char *name;     /* plugin name                                  */
  const char *author;   /* plugin author (for I_S.PLUGINS)              */
  const char *descr;    /* general descriptive text (for I_S.PLUGINS)   */
  int license;          /* the plugin license (PLUGIN_LICENSE_XXX)      */
  int (*init)(void *);  /* the function to invoke when plugin is loaded */
  int (*deinit)(void *);/* the function to invoke when plugin is unloaded */
  unsigned int version; /* plugin version (for I_S.PLUGINS)             */
  struct st_mysql_show_var *status_vars;
  struct st_mysql_sys_var **system_vars;
  void * __reserved1;   /* reserved for dependency checking             */
  unsigned long flags;  /* flags for plugin */
};

mysql_declare_plugin(ftexample)
{
  MYSQL_FTPARSER_PLUGIN,      /* type                            */
  &simple_parser_descriptor,  /* descriptor                      */
  "simple_parser",            /* name                            */
  "Oracle Corporation",       /* author                          */
  "Simple Full-Text Parser",  /* description                     */
  PLUGIN_LICENSE_GPL,         /* plugin license                  */
  simple_parser_plugin_init,  /* init function (when loaded)     */
  simple_parser_plugin_deinit,/* deinit function (when unloaded) */
  0x0001,                     /* version                         */
  simple_status,              /* status variables                */
  simple_system_variables,    /* system variables                */
  NULL,
  0
}
mysql_declare_plugin_end;

#ifndef MYSQL_DYNAMIC_PLUGIN
#define __MYSQL_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS) \
MYSQL_PLUGIN_EXPORT int VERSION= MYSQL_PLUGIN_INTERFACE_VERSION; \
MYSQL_PLUGIN_EXPORT int PSIZE= sizeof(struct st_mysql_plugin); \
MYSQL_PLUGIN_EXPORT struct st_mysql_plugin DECLS[]= {
#else
#define __MYSQL_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS) \
MYSQL_PLUGIN_EXPORT int _mysql_plugin_interface_version_= MYSQL_PLUGIN_INTERFACE_VERSION; \
MYSQL_PLUGIN_EXPORT int _mysql_sizeof_struct_st_plugin_= sizeof(struct st_mysql_plugin); \
MYSQL_PLUGIN_EXPORT struct st_mysql_plugin _mysql_plugin_declarations_[]= {
#endif

#define mysql_declare_plugin(NAME) \
__MYSQL_DECLARE_PLUGIN(NAME, \
                 builtin_ ## NAME ## _plugin_interface_version, \
                 builtin_ ## NAME ## _sizeof_struct_st_plugin, \
                 builtin_ ## NAME ## _plugin)

#define mysql_declare_plugin_end ,{0,0,0,0,0,0,0,0,0,0,0,0,0}}

int _mysql_plugin_interface_version_= MYSQL_PLUGIN_INTERFACE_VERSION;
int _mysql_sizeof_struct_st_plugin_= sizeof(struct st_mysql_plugin);
struct st_mysql_plugin _mysql_plugin_declarations_[]= {
{
  MYSQL_FTPARSER_PLUGIN,      /* type                            */
  &simple_parser_descriptor,  /* descriptor                      */
  "simple_parser",            /* name                            */
  "Oracle Corporation",       /* author                          */
  "Simple Full-Text Parser",  /* description                     */
  PLUGIN_LICENSE_GPL,         /* plugin license                  */
  simple_parser_plugin_init,  /* init function (when loaded)     */
  simple_parser_plugin_deinit,/* deinit function (when unloaded) */
  0x0001,                     /* version                         */
  simple_status,              /* status variables                */
  simple_system_variables,    /* system variables                */
  NULL,
  0
}
  ,{0,0,0,0,0,0,0,0,0,0,0,0}}
};

mysql_declare_plugin(ftexample)
{
  MYSQL_FTPARSER_PLUGIN,      /* type                            */
  &simple_parser_descriptor,  /* descriptor                      */
  "simple_parser",            /* name                            */
  "Oracle Corporation",       /* author                          */
  "Simple Full-Text Parser",  /* description                     */
  PLUGIN_LICENSE_GPL,         /* plugin license                  */
  simple_parser_plugin_init,  /* init function (when loaded)     */
  simple_parser_plugin_deinit,/* deinit function (when unloaded) */
  0x0001,                     /* version                         */
  simple_status,              /* status variables                */
  simple_system_variables,    /* system variables                */
  NULL,
  0
}
mysql_declare_plugin_end;

const char *simple_parser_name = "simple_parser";
const char *simple_parser_author = "Oracle Corporation";
const char *simple_parser_description = "Simple Full-Text Parser";

mysql_declare_plugin(ftexample)
{
  MYSQL_FTPARSER_PLUGIN,      /* type                            */
  &simple_parser_descriptor,  /* descriptor                      */
  simple_parser_name,         /* name                            */
  simple_parser_author,       /* author                          */
  simple_parser_description,  /* description                     */
  PLUGIN_LICENSE_GPL,         /* plugin license                  */
  simple_parser_plugin_init,  /* init function (when loaded)     */
  simple_parser_plugin_deinit,/* deinit function (when unloaded) */
  0x0001,                     /* version                         */
  simple_status,              /* status variables                */
  simple_system_variables,    /* system variables                */
  NULL,
  0
}
mysql_declare_plugin_end;

typedef struct
{
  const char *name;
  const char *author;
  const char *description;
} plugin_info;

plugin_info parser_info = {
  "simple_parser",
  "Oracle Corporation",
  "Simple Full-Text Parser"
};

mysql_declare_plugin(ftexample)
{
  MYSQL_FTPARSER_PLUGIN,      /* type                            */
  &simple_parser_descriptor,  /* descriptor                      */
  parser_info.name,           /* name                            */
  parser_info.author,         /* author                          */
  parser_info.description,    /* description                     */
  PLUGIN_LICENSE_GPL,         /* plugin license                  */
  simple_parser_plugin_init,  /* init function (when loaded)     */
  simple_parser_plugin_deinit,/* deinit function (when unloaded) */
  0x0001,                     /* version                         */
  simple_status,              /* status variables                */
  simple_system_variables,    /* system variables                */
  NULL,
  0
}
mysql_declare_plugin_end;

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

#define SHOW_VAR_FUNC_BUFF_SIZE 1024

typedef int (*mysql_show_var_func) (void *thd,
                                    struct st_mysql_show_var *out,
                                    char *buf);

struct st_mysql_sys_var {
 int flags;
 const char *name, *comment;
 int (*check)(THD*, struct st_mysql_sys_var *, void*, st_mysql_value*);
 void (*update)(THD*, struct st_mysql_sys_var *, void*, const void*);
};

static int my_foo;
static MYSQL_SYSVAR_INT(foo_var, my_foo, 
                        PLUGIN_VAR_RQCMDARG, "foo comment", 
                        NULL, NULL, 0, 0, INT_MAX, 0);
 ...
   SYSVAR(foo_var)= value;
   value= SYSVAR(foo_var);
   my_foo= value; 
   value= my_foo;

static MYSQL_THDVAR_BOOL(some_flag, 
                         PLUGIN_VAR_NOCMDARG, "flag comment",
                         NULL, NULL, FALSE);
 ...
   if (THDVAR(thd, some_flag))
   {
     do_something();
     THDVAR(thd, some_flag)= FALSE;
   }

static struct st_mysql_sys_var *my_plugin_vars[]= {
  MYSQL_SYSVAR(foo_var),
  MYSQL_SYSVAR(some_flag),
  NULL
};
mysql_declare_plugin(fooplug)
{
  MYSQL_..._PLUGIN,
  &plugin_data,
  "fooplug",
  "foo author",
  "This does foo!",
  PLUGIN_LICENSE_GPL,
  foo_init,
  foo_fini,
  0x0001,
  NULL,
  my_plugin_vars,
  NULL,
  0
}
mysql_declare_plugin_end;

struct st_mysql_client_plugin
{
  int type;
  unsigned int interface_version;
  const char *name;
  const char *author;
  const char *desc;
  unsigned int version[3];
  const char *license;
  void *mysql_api;
  int (*init)(char *, size_t, int, va_list);
  int (*deinit)();
  int (*options)(const char *option, const void *);
};

mysql_declare_client_plugin(plugin_type)
   ... members common to all client plugins ...
   ... type-specific extra members ...
mysql_end_client_plugin;

mysql_declare_client_plugin(AUTHENTICATION)
  "my_auth_plugin",
  "Author Name",
  "My Client Authentication Plugin",
  {1,0,0},
  "GPL",
  NULL,
  my_auth_init,
  my_auth_deinit,
  my_auth_options,
  my_auth_main
mysql_end_client_plugin;

char *plugin_dir = "path_to_plugin_dir";
char *default_auth = "plugin_name";

/* ... process command-line options ... */

mysql_options(&mysql, MYSQL_PLUGIN_DIR, plugin_dir);
mysql_options(&mysql, MYSQL_DEFAULT_AUTH, default_auth);

After your plugin is written, you must compile it and install it. The procedure for compiling shared objects varies from system to system. If you build your library using CMake, it should be able to generate the correct compilation commands for your system. If the library is named somepluglib, you should end up with a shared object file that has a name something like somepluglib.so. (The file name might have a different suffix on your system.)

To use CMake, you'll need to set up the configuration files to enable the plugin to be compiled and installed. Use the plugin examples under the plugin directory of a MySQL source distribution as a guide.

Create CMakeLists.txt, which should look something like this:

MYSQL_ADD_PLUGIN(somepluglib somepluglib.c
  MODULE_ONLY MODULE_OUTPUT_NAME "somepluglib")

When CMake generates the Makefile, it should take care of passing to the compilation command the -DMYSQL_DYNAMIC_PLUGIN flag, and passing to the linker the -lmysqlservices flag, which is needed to link in any functions from services provided through the plugin services interface. See Section 24.2.5, “MySQL Services for Plugins”.

Run CMake, then run make:

shell> cmake .
shell> make

If you need to specify configuration options to CMake, see Section 2.9.4, “MySQL Source-Configuration Options”, for a list. For example, you might want to specify CMAKE_INSTALL_PREFIX to indicate the MySQL base directory under which the plugin should be installed. You can see what value to use for this option with SHOW VARIABLES:

mysql> SHOW VARIABLES LIKE 'basedir';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| base          | /usr/local/mysql |
+---------------+------------------+

The location of the plugin directory where you should install the library is given by the plugin_dir system variable. For example:

mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| plugin_dir    | /usr/local/mysql/lib/mysql/plugin |
+---------------+-----------------------------------+

To install the plugin library, use make:

shell> make install

Verify that make install installed the plugin library in the proper directory. After installing it, make sure that the library permissions permit it to be executed by the server.

MySQL 5.5 only supports full-text parser plugins with MyISAM. For introductory information about full-text parser plugins, see Section 24.2.3.2, “Full-Text Parser Plugins”.

A full-text parser server plugin can be used to replace or modify the built-in full-text parser. This section describes how to write a full-text parser plugin named simple_parser. This plugin performs parsing based on simpler rules than those used by the MySQL built-in full-text parser: Words are nonempty runs of whitespace characters.

The instructions use the source code in the plugin/fulltext directory of MySQL source distributions, so change location into that directory. The following procedure describes how the plugin library is created:

A daemon plugin is a simple type of plugin used for code that should be run by the server but that does not communicate with it. This section describes how to write a daemon server plugin, using the example plugin found in the plugin/daemon_example directory of MySQL source distributions. That directory contains the daemon_example.cc source file for a daemon plugin named daemon_example that writes a heartbeat string at regular intervals to a file named mysql-heartbeat.log in the data directory.

To write a daemon plugin, include the following header file in the plugin source file. Other MySQL or general header files might also be needed, depending on the plugin capabilities and requirements.

#include <mysql/plugin.h>

plugin.h defines the MYSQL_DAEMON_PLUGIN server plugin type and the data structures needed to declare the plugin.

The daemon_example.cc file sets up the library descriptor as follows. The library descriptor includes a single general server plugin descriptor.

mysql_declare_plugin(daemon_example)
{
  MYSQL_DAEMON_PLUGIN,
  &daemon_example_plugin,
  "daemon_example",
  "Brian Aker",
  "Daemon example, creates a heartbeat beat file in mysql-heartbeat.log",
  PLUGIN_LICENSE_GPL,
  daemon_example_plugin_init, /* Plugin Init */
  daemon_example_plugin_deinit, /* Plugin Deinit */
  0x0100 /* 1.0 */,
  NULL,                       /* status variables                */
  NULL,                       /* system variables                */
  NULL,                       /* config options                  */
  0,                          /* flags                           */
}
mysql_declare_plugin_end;

The name member (daemon_example) indicates the name to use for references to the plugin in statements such as INSTALL PLUGIN or UNINSTALL PLUGIN. This is also the name displayed by SHOW PLUGINS or INFORMATION_SCHEMA.PLUGINS.

The second member of the plugin descriptor, daemon_example_plugin, points to the type-specific daemon plugin descriptor. This structure consists only of the type-specific API version number:

struct st_mysql_daemon daemon_example_plugin=
{ MYSQL_DAEMON_INTERFACE_VERSION  };

The type-specific structure has no interface functions. There is no communication between the server and the plugin, except that the server calls the initialization and deinitialization functions from the general plugin descriptor to start and stop the plugin:

To compile and install a plugin library object file, use the instructions in Section 24.2.4.3, “Compiling and Installing Plugin Libraries”. To use the library file, it must be installed in the plugin directory (the directory named by the plugin_dir system variable). For the daemon_example plugin, it is compiled and installed when you build MySQL from source. It is also included in binary distributions. The build process produces a shared object library with a name of libdaemon_example.so (the suffix might differ depending on your platform).

To use the plugin, register it with the server. For example, to register the plugin at runtime, use this statement (change the suffix as necessary):

mysql> INSTALL PLUGIN daemon_example SONAME 'libdaemon_example.so';

For additional information about plugin loading, see Section 5.1.8.1, “Installing and Uninstalling Plugins”.

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement.

While the plugin is loaded, it writes a heartbeat string at regular intervals to a file named mysql-heartbeat.log in the data directory. This file grows without limit, so after you have satistifed yourself that the plugin operates correctly, unload it:

mysql> UNINSTALL PLUGIN daemon_example;

This section describes how to write an INFORMATION_SCHEMA table server plugin. For example code that implements such plugins, see the sql/sql_show.cc file of a MySQL source distribution. You can also look at the example plugins found in the InnoDB source. See the handler/i_s.cc and handler/ha_innodb.cc files within the InnoDB source tree (in the storage/innobase directory).

To write an INFORMATION_SCHEMA table plugin, include the following header files in the plugin source file. Other MySQL or general header files might also be needed, depending on the plugin capabilities and requirements.

#include <sql_class.h>
#include <table.h>

These header files are located in the sql directory of MySQL source distributions. They contain C++ structures, so the source file for an INFORMATION_SCHEMA plugin must be compiled as C++ (not C) code.

The source file for the example plugin developed here is named simple_i_s_table.cc. It creates a simple INFORMATION_SCHEMA table named SIMPLE_I_S_TABLE that has two columns named NAME and VALUE. The general descriptor for a plugin library that implements the table looks like this:

mysql_declare_plugin(simple_i_s_library)
{
  MYSQL_INFORMATION_SCHEMA_PLUGIN,
  &simple_table_info,                /* type-specific descriptor */
  "SIMPLE_I_S_TABLE",                /* table name */
  "Author Name",                     /* author */
  "Simple INFORMATION_SCHEMA table", /* description */
  PLUGIN_LICENSE_GPL,                /* license type */
  simple_table_init,                 /* init function */
  NULL,
  0x0100,                            /* version = 1.0 */
  NULL,                              /* no status variables */
  NULL,                              /* no system variables */
  NULL,                              /* no reserved information */
  0                                  /* no flags */
}
mysql_declare_plugin_end;

The name member (SIMPLE_I_S_TABLE) indicates the name to use for references to the plugin in statements such as INSTALL PLUGIN or UNINSTALL PLUGIN. This is also the name displayed by SHOW PLUGINS or INFORMATION_SCHEMA.PLUGINS.

The simple_table_info member of the general descriptor points to the type-specific descriptor, which consists only of the type-specific API version number:

static struct st_mysql_information_schema simple_table_info =
{ MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION };

The general descriptor points to the initialization and deinitialization functions:

The initialization function should return 0 for success, 1 if an error occurs. The function receives a generic pointer, which it should interpret as a pointer to the table structure:

static int table_init(void *ptr)
{
  ST_SCHEMA_TABLE *schema_table= (ST_SCHEMA_TABLE*)ptr;

  schema_table->fields_info= simple_table_fields;
  schema_table->fill_table= simple_fill_table;
  return 0;
}

The function should set these two members of the table structure:

The array pointed to by fields_info should contain one element per column of the INFORMATION_SCHEMA plus a terminating element. The following simple_table_fields array for the example plugin indicates that SIMPLE_I_S_TABLE has two columns. NAME is string-valued with a length of 10 and VALUE is integer-valued with a display width of 20. The last structure marks the end of the array.

static ST_FIELD_INFO simple_table_fields[]=
{
  {"NAME", 10, MYSQL_TYPE_STRING, 0, 0 0, 0},
  {"VALUE", 6, MYSQL_TYPE_LONG, 0, MY_I_S_UNSIGNED, 0, 0},
  {0, 0, MYSQL_TYPE_NULL, 0, 0, 0, 0}
};

For more information about the column information structure, see the definition of ST_FIELD_INFO in the table.h header file. The permissible MYSQL_TYPE_xxx type values are those used in the C API; see Section 23.8.5, “C API Data Structures”.

The fill_table member should be set to a function that populates the table and returns 0 for success, 1 if an error occurs. For the example plugin, the simple_fill_table() function looks like this:

static int simple_fill_table(THD *thd, TABLE_LIST *tables, COND *cond)
{
  TABLE *table= tables->table;

  table->field[0]->store("Name 1", 6, system_charset_info);
  table->field[1]->store(1);
  if (schema_table_store_record(thd, table))
    return 1;
  table->field[0]->store("Name 2", 6, system_charset_info);
  table->field[1]->store(2);
  if (schema_table_store_record(thd, table))
    return 1;
  return 0;
}

For each row of the INFORMATION_SCHEMA table, this function initializes each column, then calls schema_table_store_record() to install the row. The store() method arguments depend on the type of value to be stored. For column 0 (NAME, a string), store() takes a pointer to a string, its length, and information about the character set of the string:

store(const char *to, uint length, CHARSET_INFO *cs);

For column 1 (VALUE, an integer), store() takes the value and a flag indicating whether it is unsigned:

store(longlong nr, bool unsigned_value);

For other examples of how to populate INFORMATION_SCHEMA tables, search for instances of schema_table_store_record() in sql_show.cc.

To compile and install a plugin library object file, see the instructions in Section 24.2.4.3, “Compiling and Installing Plugin Libraries”. To use the library file, it must be installed in the plugin directory (the directory named by the plugin_dir system variable).

To test the plugin, install it:

mysql> INSTALL PLUGIN SIMPLE_I_S_TABLE SONAME 'simple_i_s_table.so';

Verify that the table is present:

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
    -> WHERE TABLE_NAME = 'SIMPLE_I_S_TABLE';
+------------------+
| TABLE_NAME       |
+------------------+
| SIMPLE_I_S_TABLE |
+------------------+

Try to select from it:

mysql> SELECT * FROM INFORMATION_SCHEMA.SIMPLE_I_S_TABLE;
+--------+-------+
| NAME   | VALUE |
+--------+-------+
| Name 1 |     1 |
| Name 2 |     2 |
+--------+-------+

Uninstall it:

mysql> UNINSTALL PLUGIN SIMPLE_I_S_TABLE;

This section describes how to write semisynchronous replication server plugins, using the example plugins found in the plugin/semisync directory of MySQL source distributions. That directory contains the source files for master and slave plugins named rpl_semi_sync_master and rpl_semi_sync_slave. The information here covers only how to set up the plugin framework. For details about how the plugins implement replication functions, see the source.

To write a semisynchronous replication plugin, include the following header file in the plugin source file. Other MySQL or general header files might also be needed, depending on the plugin capabilities and requirements.

#include <mysql/plugin.h>

plugin.h defines the MYSQL_REPLICATION_PLUGIN server plugin type and the data structures needed to declare the plugin.

For the master side, semisync_master_plugin.cc contains this general descriptor for a plugin named rpl_semi_sync_master:

mysql_declare_plugin(semi_sync_master)
{
  MYSQL_REPLICATION_PLUGIN,
  &semi_sync_master_plugin,
  "rpl_semi_sync_master",
  "He Zhenxing",
  "Semi-synchronous replication master",
  PLUGIN_LICENSE_GPL,
  semi_sync_master_plugin_init, /* Plugin Init */
  semi_sync_master_plugin_deinit, /* Plugin Deinit */
  0x0100 /* 1.0 */,
  semi_sync_master_status_vars, /* status variables */
  semi_sync_master_system_vars, /* system variables */
  NULL,                         /* config options */
  0,                            /* flags */
}
mysql_declare_plugin_end;

For the slave side, semisync_slave_plugin.cc contains this general descriptor for a plugin named rpl_semi_sync_slave:

mysql_declare_plugin(semi_sync_slave)
{
  MYSQL_REPLICATION_PLUGIN,
  &semi_sync_slave_plugin,
  "rpl_semi_sync_slave",
  "He Zhenxing",
  "Semi-synchronous replication slave",
  PLUGIN_LICENSE_GPL,
  semi_sync_slave_plugin_init, /* Plugin Init */
  semi_sync_slave_plugin_deinit, /* Plugin Deinit */
  0x0100 /* 1.0 */,
  semi_sync_slave_status_vars,  /* status variables */
  semi_sync_slave_system_vars,  /* system variables */
  NULL,                         /* config options */
  0,                            /* flags */
}
mysql_declare_plugin_end;

For both the master and slave plugins, the general descriptor has pointers to the type-specific descriptor, the initialization and deinitialization functions, and to the status and system variables implemented by the plugin. For information about variable setup, see Section 24.2.4.2.2, “Server Plugin Status and System Variables”. The following remarks discuss the type-specific descriptor and the initialization and deinitialization functions for the master plugin but apply similarly to the slave plugin.

The semi_sync_master_plugin member of the master general descriptor points to the type-specific descriptor, which consists only of the type-specific API version number:

struct Mysql_replication semi_sync_master_plugin= {
  MYSQL_REPLICATION_INTERFACE_VERSION
};

The initialization and deinitialization function declarations look like this:

static int semi_sync_master_plugin_init(void *p);
static int semi_sync_master_plugin_deinit(void *p);

The initialization function uses the pointer to register transaction and binary logging “observers” with the server. After successful initialization, the server takes care of invoking the observers at the appropriate times. (For details on the observers, see the source files.) The deinitialization function cleans up by deregistering the observers. Each function returns 0 for success or 1 if an error occurs.

To compile and install a plugin library object file, use the instructions in Section 24.2.4.3, “Compiling and Installing Plugin Libraries”. To use the library files, they must be installed in the plugin directory (the directory named by the plugin_dir system variable). For the rpl_semi_sync_master and rpl_semi_sync_slave plugins, they are compiled and installed when you build MySQL from source. They are also included in binary distributions. The build process produces shared object libraries with names of semisync_master.so and semisync_slave.so (the suffix might differ depending on your platform).

This section describes how to write an audit server plugin, using the example plugin found in the plugin/audit_null directory of MySQL source distributions. The audit_null.c source file in that directory implements a simple example audit plugin named NULL_AUDIT.

Within the server, the pluggable audit interface is implemented in the sql_audit.h and sql_audit.cc files in the sql directory of MySQL source distributions. Additionally, several places in the server are modified to call the audit interface when an auditable event occurs, so that registered audit plugins can be notified about the event if necessary. To see where such calls occur, look for invocations of functions with names of the form mysql_audit_xxx(). Audit notification occurs for server operations such as these:

To write an audit plugin, include the following header file in the plugin source file. Other MySQL or general header files might also be needed, depending on the plugin capabilities and requirements.

#include <mysql/plugin_audit.h>

plugin_audit.h includes plugin.h, so you need not include the latter file explicitly. plugin.h defines the MYSQL_AUDIT_PLUGIN server plugin type and the data structures needed to declare the plugin. plugin_audit.h defines data structures specific to audit plugins.

An audit plugin, like any MySQL server plugin, has a general plugin descriptor (see Section 24.2.4.2.1, “Server Plugin Library and Plugin Descriptors”). In audit_null.c, the general descriptor looks like this:

mysql_declare_plugin(audit_null)
{
  MYSQL_AUDIT_PLUGIN,         /* type                            */
  &audit_null_descriptor,     /* descriptor                      */
  "NULL_AUDIT",               /* name                            */
  "Oracle Corp",              /* author                          */
  "Simple NULL Audit",        /* description                     */
  PLUGIN_LICENSE_GPL,
  audit_null_plugin_init,     /* init function (when loaded)     */
  audit_null_plugin_deinit,   /* deinit function (when unloaded) */
  0x0002,                     /* version                         */
  simple_status,              /* status variables                */
  NULL,                       /* system variables                */
  NULL,
  0,
}
mysql_declare_plugin_end;

The name member (NULL_AUDIT) indicates the name to use for references to the plugin in statements such as INSTALL PLUGIN or UNINSTALL PLUGIN. This is also the name displayed by INFORMATION_SCHEMA.PLUGINS or SHOW PLUGINS.

The general descriptor also refers to simple_status, a structure that exposes several status variables to the SHOW STATUS statement:

static struct st_mysql_show_var simple_status[]=
{
  { "Audit_null_called", (char *) &number_of_calls, SHOW_INT },
  { "Audit_null_general_log", (char *) &number_of_calls_general_log,
    SHOW_INT },
  { "Audit_null_general_error", (char *) &number_of_calls_general_error,
    SHOW_INT },
  { "Audit_null_general_result", (char *) &number_of_calls_general_result,
    SHOW_INT },
  { 0, 0, 0}
};

The audit_null_plugin_init initialization function sets the status variables to zero when the plugin is loaded. The audit_null_plugin_deinit function performs cleanup with the plugin is unloaded. During operation, the plugin increments the first status variable for each notification it receives. It also increments the others according to the event class and subclass. In effect, the first variable is the aggregate of the counts for the event subclasses.

The audit_null_descriptor value in the general descriptor points to the type-specific descriptor. For audit plugins, this descriptor has the following structure:

struct st_mysql_audit
{
  int interface_version;
  void (*release_thd)(MYSQL_THD);
  void (*event_notify)(MYSQL_THD, unsigned int, const void *);
  unsigned long class_mask[MYSQL_AUDIT_CLASS_MASK_SIZE];
};

The type-specific descriptor has these members:

The server uses the event_notify and release_thd functions together. They are called within the context of a specific thread, and a thread might perform an activity that produces several event notifications. The first time the server calls event_notify for a thread, it creates a binding of the plugin to the thread. The plugin cannot be uninstalled while this binding exists. When no more events for the thread will occur, the server informs the plugin of this by calling the release_thd function, and then destroys the binding. For example, when a client issues a statement, the thread processing the statement might notify audit plugins about the result set produced by the statement and about the statement being logged. After these notifications occur, the server releases the plugin before putting the thread to sleep until the client issues another statement.

This design enables the plugin to allocate resources needed for a given thread in the first call to the event_notify function and release them in the release_thd function:

event_notify function:
  if memory is needed to service the thread
    allocate memory
  ... rest of notification processing ...

release_thd function:
  if memory was allocated
    release memory
  ... rest of release processing ...

That is more efficient than allocating and releasing memory repeatedly in the notification function.

For the NULL_AUDIT example audit plugin, the type-specific descriptor looks like this:

static struct st_mysql_audit audit_null_descriptor=
{
  MYSQL_AUDIT_INTERFACE_VERSION,                    /* interface version    */
  NULL,                                             /* release_thd function */
  audit_null_notify,                                /* notify function      */
  { (unsigned long) MYSQL_AUDIT_GENERAL_CLASSMASK } /* class mask           */
};

The server calls audit_null_notify to pass audit event information to the plugin. There is no release_thd function.

The event class mask indicates an interest in all events of the “general” class. plugin_audit.h defines its symbol, MYSQL_AUDIT_GENERAL_CLASS, and a mask with a bit for this class:

#define MYSQL_AUDIT_GENERAL_CLASS 0
#define MYSQL_AUDIT_GENERAL_CLASSMASK (1 << MYSQL_AUDIT_GENERAL_CLASS)

plugin_audit.h also has defines for a “connection” event class, although the NULL_AUDIT plugin does nothing with such events:

#define MYSQL_AUDIT_CONNECTION_CLASS 1
#define MYSQL_AUDIT_CONNECTION_CLASSMASK (1 << MYSQL_AUDIT_CONNECTION_CLASS)

A plugin could be written to receive both general and connection events by setting its type-specific descriptor class mask like this:

  { (unsigned long) MYSQL_AUDIT_GENERAL_CLASSMASK |
                 MYSQL_AUDIT_CONNECTION_CLASSMASK } /* class mask        */

In the type-specific descriptor, the second and third parameters of the event_notify function prototype represent the event class and a generic pointer to an event structure:

void (*event_notify)(MYSQL_THD, unsigned int, const void *);

Events in different classes may have different structures, so the notification function should use the event class value to determine how to interpret the pointer to the event structure.

If the server calls the notification function with an event class of MYSQL_AUDIT_GENERAL_CLASS, it passes the event structure as a pointer to a mysql_event_general structure:

struct mysql_event_general
{
  unsigned int event_subclass;
  int general_error_code;
  unsigned long general_thread_id;
  const char *general_user;
  unsigned int general_user_length;
  const char *general_command;
  unsigned int general_command_length;
  const char *general_query;
  unsigned int general_query_length;
  struct charset_info_st *general_charset;
  unsigned long long general_time;
  unsigned long long general_rows;
};

Audit plugins can interpret mysql_event_general members as follows:

The general_host, general_sql_command, general_external_user, and general_ip members are new in MySQL 5.5.34. These are MYSQL_LEX_STRING structures that pair a string and its length. For example, if event_general is a pointer to a general event, you can access the members of the general_host value as follows:

event_general->general_host.length
event_general->general_host.str

The NULL_AUDIT plugin notification function is quite simple. It increments a global event counter, verifies that the event is of the “general” class, then looks at the event subclass to determine which subclass counter to increment:

static void audit_null_notify(MYSQL_THD thd __attribute__((unused)),
                              unsigned int event_class,
                              const void *event)
{
  number_of_calls++;
  if (event_class == MYSQL_AUDIT_GENERAL_CLASS)
  {
    const struct mysql_event_general *event_general=
      (const struct mysql_event_general *) event;
    switch (event_general->event_subclass)
    {
    case MYSQL_AUDIT_GENERAL_LOG:
      number_of_calls_general_log++;
      break;
    case MYSQL_AUDIT_GENERAL_ERROR:
      number_of_calls_general_error++;
      break;
    case MYSQL_AUDIT_GENERAL_RESULT:
      number_of_calls_general_result++;
      break;
    default:
      break;
    }
  }
}

To compile and install a plugin library object file, use the instructions in Section 24.2.4.3, “Compiling and Installing Plugin Libraries”. To use the library file, it must be installed in the plugin directory (the directory named by the plugin_dir system variable). For the AUDIT_NULL plugin, it is compiled and installed when you build MySQL from source. It is also included in binary distributions. The build process produces a shared object library with a name of adt_null.so (the suffix might differ depending on your platform).

To register the plugin at runtime, use this statement (change the suffix as necessary):

mysql> INSTALL PLUGIN NULL_AUDIT SONAME 'adt_null.so';

For additional information about plugin loading, see Section 5.1.8.1, “Installing and Uninstalling Plugins”.

To verify plugin installation, examine the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement.

While the audit plugin is installed, it exposes status variables that indicate the events for which the plugin has been called:

mysql> SHOW STATUS LIKE 'Audit_null%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Audit_null_called         | 1385  |
| Audit_null_general_error  | 1     |
| Audit_null_general_log    | 741   |
| Audit_null_general_result | 643   |
+---------------------------+-------+

Audit_null_called counts all events, and the other variables count instances of event subclasses. For example, the preceding SHOW STATUS statement causes the server to send a result to the client and to write a message to the general query log if that log is enabled. Thus, a client that issues the statement repeatedly causes Audit_null_called and Audit_null_general_result to be incremented each time, and Audit_null_general_log to be incremented if the log is enabled.

To disable the plugin after testing it, use this statement to unload it:

mysql> UNINSTALL PLUGIN NULL_AUDIT;

MySQL supports pluggable authentication, in which plugins are invoked to authenticate client connections. Authentication plugins enable the use of authentication methods other than the built-in method of passwords stored in the mysql.user table. For example, plugins can be written to access external authentication methods. Also, authentication plugins can support the proxy user capability, such that the connecting user is a proxy for another user and is treated, for purposes of access control, as having the privileges of a different user. For more information, see Section 6.3.6, “Pluggable Authentication”, and Section 6.3.8, “Proxy Users”.

An authentication plugin can be written for the server side or the client side. Server-side plugins use the same plugin API that is used for the other server plugin types such as full-text parser or audit plugins (although with a different type-specific descriptor). Client-side plugins use the client plugin API.

Several header files contain information relevant to authentication plugins:

To write an authentication plugin, include the following header files in the plugin source file. Other MySQL or general header files might also be needed, depending on the plugin capabilities and requirements.

plugin_auth.h includes plugin.h and plugin_auth_common.h, so you need not include the latter files explicitly.

This section describes how to write a pair of simple server and client authentication plugins that work together.

The server-side and client-side plugins developed here both are named auth_simple. As described in Section 24.2.4.2, “Plugin Data Structures”, the plugin library file must have the same basename as the client plugin, so the source file name is auth_simple.c and produces a library named auth_simple.so (assuming that your system uses .so as the suffix for library files).

In MySQL source distributions, authentication plugin source is located in the plugin/auth directory and can be examined as a guide to writing other authentication plugins. Also, to see how the built-in authentication plugins are implemented, see sql/sql_acl.cc for plugins that are built in to the MySQL server and sql-common/client.c for plugins that are built in to the libmysqlclient client library. (For the built-in client plugins, note that the auth_plugin_t structures used there differ from the structures used with the usual client plugin declaration macros. In particular, the first two members are provided explicitly, not by declaration macros.)

mysql_declare_plugin(auth_simple)
{
  MYSQL_AUTHENTICATION_PLUGIN,
  &auth_simple_handler,                 /* type-specific descriptor */
  "auth_simple",                        /* plugin name */
  "Author Name",                        /* author */
  "Any-password authentication plugin", /* description */
  PLUGIN_LICENSE_GPL,                   /* license type */
  NULL,                                 /* no init function */
  NULL,                                 /* no deinit function */
  0x0100,                               /* version = 1.0 */
  NULL,                                 /* no status variables */
  NULL,                                 /* no system variables */
  NULL,                                 /* no reserved information */
  0                                     /* no flags */
}
mysql_declare_plugin_end;

struct st_mysql_auth
{
  int interface_version;
  const char *client_auth_plugin;
  int (*authenticate_user)(MYSQL_PLUGIN_VIO *vio, MYSQL_SERVER_AUTH_INFO *info);
};

static struct st_mysql_auth auth_simple_handler =
{
  MYSQL_AUTHENTICATION_INTERFACE_VERSION,
  "auth_simple",           /* required client-side plugin name */
  auth_simple_server       /* server-side plugin main function */
};

typedef struct st_mysql_server_auth_info
{
  char *user_name;
  unsigned int user_name_length;
  const char *auth_string;
  unsigned long auth_string_length;
  char authenticated_as[MYSQL_USERNAME_LENGTH+1]; 
  char external_user[512];
  int  password_used;
  const char *host_or_ip;
  unsigned int host_or_ip_length;
} MYSQL_SERVER_AUTH_INFO;

static int auth_simple_server (MYSQL_PLUGIN_VIO *vio,
                               MYSQL_SERVER_AUTH_INFO *info)
{
  unsigned char *pkt;
  int pkt_len;

  /* read the password as null-terminated string, fail on error */
  if ((pkt_len= vio->read_packet(vio, &pkt)) < 0)
    return CR_ERROR;

  /* fail on empty password */
  if (!pkt_len || *pkt == '\0')
  {
    info->password_used= PASSWORD_USED_NO;
    return CR_ERROR;
  }

  /* accept any nonempty password */
  info->password_used= PASSWORD_USED_YES;

  return CR_OK;
}

mysql_declare_client_plugin(AUTHENTICATION)
  "auth_simple",                        /* plugin name */
  "Author Name",                        /* author */
  "Any-password authentication plugin", /* description */
  {1,0,0},                              /* version = 1.0.0 */
  "GPL",                                /* license type */
  NULL,                                 /* for internal use */
  NULL,                                 /* no init function */
  NULL,                                 /* no deinit function */
  NULL,                                 /* no option-handling function */
  auth_simple_client                    /* main function */
mysql_end_client_plugin;

static int auth_simple_client (MYSQL_PLUGIN_VIO *vio, MYSQL *mysql)
{
  int res;

  /* send password as null-terminated string in clear text */
  res= vio->write_packet(vio, (const unsigned char *) mysql->passwd, 
                         strlen(mysql->passwd) + 1);

  return res ? CR_ERROR : CR_OK;
}

mysql> CREATE USER 'x'@'localhost'
    -> IDENTIFIED WITH auth_simple;

shell> mysql --user=x --skip-password
ERROR 1045 (28000): Access denied for user 'x'@'localhost' (using password: NO)

shell> mysql --user=x --password=abc
mysql>

char *plugin_dir = "path_to_plugin_dir";
char *default_auth = "plugin_name";

/* ... process command-line options ... */

mysql_options(&mysql, MYSQL_PLUGIN_DIR, plugin_dir);
mysql_options(&mysql, MYSQL_DEFAULT_AUTH, default_auth);

CREATE USER ''@'%.example.com'
  IDENTIFIED WITH my_plugin AS 'extuser1=mysqlusera, extuser2=mysqluserb'
CREATE USER ''@'%.example.org'
  IDENTIFIED WITH my_plugin AS 'extuser1=mysqluserc, extuser2=mysqluserd'

CREATE USER 'plugin_user1'@'localhost'
  IDENTIFIED WITH auth_simple_proxy;
CREATE USER 'plugin_user2'@'localhost'
  IDENTIFIED WITH auth_simple_proxy AS 'proxied_user';

CREATE USER 'proxied_user'@'localhost'
  IDENTIFIED BY 'proxied_user_pass';
GRANT PROXY
  ON 'proxied_user'@'localhost'
  TO 'plugin_user2'@'localhost';

static int auth_simple_proxy_server (MYSQL_PLUGIN_VIO *vio,
                                     MYSQL_SERVER_AUTH_INFO *info)
{
  unsigned char *pkt;
  int pkt_len;

  /* read the password as null-terminated string, fail on error */
  if ((pkt_len= vio->read_packet(vio, &pkt)) < 0)
    return CR_ERROR;

  /* fail on empty password */
  if (!pkt_len || *pkt == '\0')
  {
    info->password_used= PASSWORD_USED_NO;
    return CR_ERROR;
  }

  /* accept any nonempty password */
  info->password_used= PASSWORD_USED_YES;

  /* if authentication string is nonempty, use as proxied user name */
  /* and use client name as external_user value */
  if (info->auth_string_length > 0)
  {
    strcpy (info->authenticated_as, info->auth_string);
    strcpy (info->external_user, info->user_name);
  }

  return CR_OK;
}

shell> mysql --user=plugin_user1 --password=x

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user, @@external_user\G
*************************** 1. row ***************************
         USER(): plugin_user1@localhost
 CURRENT_USER(): plugin_user1@localhost
   @@proxy_user: NULL
@@external_user: NULL

shell> mysql --user=plugin_user2 --password=x

mysql> SELECT USER(), CURRENT_USER(), @@proxy_user, @@external_user\G
*************************** 1. row ***************************
         USER(): plugin_user2@localhost
 CURRENT_USER(): proxied_user@localhost
   @@proxy_user: 'plugin_user2'@'localhost'
@@external_user: 'plugin_user2'@'localhost'

This section describes the different functions that you need to define when you create a simple UDF. Section 24.3.2, “Adding a New User-Defined Function”, describes the order in which MySQL calls these functions.

The main xxx() function should be declared as shown in this section. Note that the return type and parameters differ, depending on whether you declare the SQL function XXX() to return STRING, INTEGER, or REAL in the CREATE FUNCTION statement:

For STRING functions:

char *xxx(UDF_INIT *initid, UDF_ARGS *args,
          char *result, unsigned long *length,
          char *is_null, char *error);

For INTEGER functions:

long long xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

For REAL functions:

double xxx(UDF_INIT *initid, UDF_ARGS *args,
              char *is_null, char *error);

DECIMAL functions return string values and should be declared the same way as STRING functions. ROW functions are not implemented.

The initialization and deinitialization functions are declared like this:

my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);

void xxx_deinit(UDF_INIT *initid);

The initid parameter is passed to all three functions. It points to a UDF_INIT structure that is used to communicate information between functions. The UDF_INIT structure members follow. The initialization function should fill in any members that it wishes to change. (To use the default for a member, leave it unchanged.)

This section describes the different functions that you need to define when you create an aggregate UDF. Section 24.3.2, “Adding a New User-Defined Function”, describes the order in which MySQL calls these functions.

The xxx() function for an aggregate UDF should be declared the same way as for a nonaggregate UDF. See Section 24.3.2.1, “UDF Calling Sequences for Simple Functions”.

For an aggregate UDF, MySQL calls the xxx() function after all rows in the group have been processed. You should normally never access its UDF_ARGS argument here but instead return a value based on your internal summary variables.

Return value handling in xxx() should be done the same way as for a nonaggregate UDF. See Section 24.3.2.4, “UDF Return Values and Error Handling”.

The xxx_reset() and xxx_add() functions handle their UDF_ARGS argument the same way as functions for nonaggregate UDFs. See Section 24.3.2.3, “UDF Argument Processing”.

The pointer arguments to is_null and error are the same for all calls to xxx_reset(), xxx_clear(), xxx_add() and xxx(). You can use this to remember that you got an error or whether the xxx() function should return NULL. You should not store a string into *error! error points to a single-byte variable, not to a string buffer.

*is_null is reset for each group (before calling xxx_clear()). *error is never reset.

If *is_null or *error are set when xxx() returns, MySQL returns NULL as the result for the group function.

The args parameter points to a UDF_ARGS structure that has the members listed here:

The initialization function should return 0 if no error occurred and 1 otherwise. If an error occurs, xxx_init() should store a null-terminated error message in the message parameter. The message is returned to the client. The message buffer is MYSQL_ERRMSG_SIZE characters long, but you should try to keep the message to less than 80 characters so that it fits the width of a standard terminal screen.

The return value of the main function xxx() is the function value, for long long and double functions. A string function should return a pointer to the result and set *length to the length (in bytes) of the return value. For example:

memcpy(result, "result string", 13);
*length = 13;

MySQL passes a buffer to the xxx() function using the result parameter. This buffer is sufficiently long to hold 255 characters, which can be multibyte characters. The xxx() function can store the result in this buffer if it fits, in which case the return value should be a pointer to the buffer. If the function stores the result in a different buffer, it should return a pointer to that buffer.

If your string function does not use the supplied buffer (for example, if it needs to return a string longer than 255 characters), you must allocate the space for your own buffer with malloc() in your xxx_init() function or your xxx() function and free it in your xxx_deinit() function. You can store the allocated memory in the ptr slot in the UDF_INIT structure for reuse by future xxx() calls. See Section 24.3.2.1, “UDF Calling Sequences for Simple Functions”.

To indicate a return value of NULL in the main function, set *is_null to 1:

*is_null = 1;

To indicate an error return in the main function, set *error to 1:

*error = 1;

If xxx() sets *error to 1 for any row, the function value is NULL for the current row and for any subsequent rows processed by the statement in which XXX() was invoked. (xxx() is not even called for subsequent rows.)

Files implementing UDFs must be compiled and installed on the host where the server runs. This process is described below for the example UDF file sql/udf_example.c that is included in MySQL source distributions.

If a UDF will be referred to in statements that will be replicated to slave servers, you must ensure that every slave also has the function available. Otherwise, replication will fail on the slaves when they attempt to invoke the function.

The immediately following instructions are for Unix. Instructions for Windows are given later in this section.

The udf_example.c file contains the following functions:

A dynamically loadable file should be compiled as a sharable object file, using a command something like this:

shell> gcc -shared -o udf_example.so udf_example.c

If you are using gcc with CMake (which is how MySQL is configured), you should be able to create udf_example.so with a simpler command:

shell> make udf_example

After you compile a shared object containing UDFs, you must install it and tell MySQL about it. Compiling a shared object from udf_example.c using gcc directly produces a file named udf_example.so. Copy the shared object to the server's plugin directory and name it udf_example.so. This directory is given by the value of the plugin_dir system variable.

On some systems, the ldconfig program that configures the dynamic linker does not recognize a shared object unless its name begins with lib. In this case you should rename a file such as udf_example.so to libudf_example.so.

On Windows, you can compile user-defined functions by using the following procedure:

After the shared object file has been installed, notify mysqld about the new functions with the following statements. If object files have a suffix different from .so on your system, substitute the correct suffix throughout (for example, .dll on Windows).

mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';
mysql> CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';
mysql> CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.so';
mysql> CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE FUNCTION reverse_lookup
    ->        RETURNS STRING SONAME 'udf_example.so';
mysql> CREATE AGGREGATE FUNCTION avgcost
    ->        RETURNS REAL SONAME 'udf_example.so';

To delete functions, use DROP FUNCTION:

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
mysql> DROP FUNCTION sequence;
mysql> DROP FUNCTION lookup;
mysql> DROP FUNCTION reverse_lookup;
mysql> DROP FUNCTION avgcost;

The CREATE FUNCTION and DROP FUNCTION statements update the func system table in the mysql database. The function's name, type and shared library name are saved in the table. You must have the INSERT or DELETE privilege for the mysql database to create or drop functions, respectively.

You should not use CREATE FUNCTION to add a function that has previously been created. If you need to reinstall a function, you should remove it with DROP FUNCTION and then reinstall it with CREATE FUNCTION. You would need to do this, for example, if you recompile a new version of your function, so that mysqld gets the new version. Otherwise, the server continues to use the old version.

An active function is one that has been loaded with CREATE FUNCTION and not removed with DROP FUNCTION. All active functions are reloaded each time the server starts, unless you start mysqld with the --skip-grant-tables option. In this case, UDF initialization is skipped and UDFs are unavailable.

MySQL takes several measures to prevent misuse of user-defined functions.

UDF object files cannot be placed in arbitrary directories. They must be located in the server's plugin directory. This directory is given by the value of the plugin_dir system variable.

To use CREATE FUNCTION or DROP FUNCTION, you must have the INSERT or DELETE privilege, respectively, for the mysql database. This is necessary because those statements add and delete rows from the mysql.func table.

UDFs should have at least one symbol defined in addition to the xxx symbol that corresponds to the main xxx() function. These auxiliary symbols correspond to the xxx_init(), xxx_deinit(), xxx_reset(), xxx_clear(), and xxx_add() functions. mysqld also supports an --allow-suspicious-udfs option that controls whether UDFs that have only an xxx symbol can be loaded. By default, the option is off, to prevent attempts at loading functions from shared object files other than those containing legitimate UDFs. If you have older UDFs that contain only the xxx symbol and that cannot be recompiled to include an auxiliary symbol, it may be necessary to specify the --allow-suspicious-udfs option. Otherwise, you should avoid enabling this capability.

If you have some very specific problem, you can always try to debug MySQL. To do this you must configure MySQL with the -DWITH_DEBUG=1 option. You can check whether MySQL was compiled with debugging by doing: mysqld --help. If the --debug flag is listed with the options then you have debugging enabled. mysqladmin ver also lists the mysqld version as mysql ... --debug in this case.

If mysqld stops crashing when you configure it with the -DWITH_DEBUG=1 CMake option, you probably have found a compiler bug or a timing bug within MySQL. In this case, you can try to add -g using the CMAKE_C_FLAGS and CMAKE_CXX_FLAGS CMake options and not use -DWITH_DEBUG=1. If mysqld dies, you can at least attach to it with gdb or use gdb on the core file to find out what happened.

When you configure MySQL for debugging you automatically enable a lot of extra safety check functions that monitor the health of mysqld. If they find something “unexpected,” an entry is written to stderr, which mysqld_safe directs to the error log! This also means that if you are having some unexpected problems with MySQL and are using a source distribution, the first thing you should do is to configure MySQL for debugging! (The second thing is to send mail to a MySQL mailing list and ask for help. See Section 1.5.1, “MySQL Mailing Lists”. If you believe that you have found a bug, please use the instructions at Section 1.6, “How to Report Bugs or Problems”.

In the Windows MySQL distribution, mysqld.exe is by default compiled with support for trace files.

If the mysqld server doesn't start or if you can cause it to crash quickly, you can try to create a trace file to find the problem.

To do this, you must have a mysqld that has been compiled with debugging support. You can check this by executing mysqld -V. If the version number ends with -debug, it is compiled with support for trace files. (On Windows, the debugging server is named mysqld-debug rather than mysqld as of MySQL 4.1.)

Start the mysqld server with a trace log in /tmp/mysqld.trace on Unix or \mysqld.trace on Windows:

shell> mysqld --debug

On Windows, you should also use the --standalone flag to not start mysqld as a service. In a console window, use this command:

C:\> mysqld-debug --debug --standalone

After this, you can use the mysql.exe command-line tool in a second console window to reproduce the problem. You can stop the mysqld server with mysqladmin shutdown.

The trace file can become very large! To generate a smaller trace file, you can use debugging options something like this:

mysqld --debug=d,info,error,query,general,where:O,/tmp/mysqld.trace

This only prints information with the most interesting tags to the trace file.

If you make a bug report about this, please only send the lines from the trace file to the appropriate mailing list where something seems to go wrong! If you can't locate the wrong place, you can open a bug report and upload the trace file to the report, so that a MySQL developer can take a look at it. For instructions, see Section 1.6, “How to Report Bugs or Problems”.

The trace file is made with the DBUG package by Fred Fish. See Section 24.4.3, “The DBUG Package”.

Program Database (PDB) files (with file name extension pdb) are included in ZIP Archive distributions (but not MSI distributions) of MySQL. These files provide information for debugging your MySQL installation in the event of a problem.

The PDB file contains more detailed information about mysqld and other tools that enables more detailed trace and dump files to be created. You can use these with WinDbg or Visual Studio to debug mysqld.

For more information on PDB files, see Microsoft Knowledge Base Article 121366. For more information on the debugging options available, see Debugging Tools for Windows.

On most systems you can also start mysqld from gdb to get more information if mysqld crashes.

With some older gdb versions on Linux you must use run --one-thread if you want to be able to debug mysqld threads. In this case, you can only have one thread active at a time. It is best to upgrade to gdb 5.1 because thread debugging works much better with this version!

NPTL threads (the new thread library on Linux) may cause problems while running mysqld under gdb. Some symptoms are:

In this case, you should set the following environment variable in the shell before starting gdb:

LD_ASSUME_KERNEL=2.4.1
export LD_ASSUME_KERNEL

When running mysqld under gdb, you should disable the stack trace with --skip-stack-trace to be able to catch segfaults within gdb.

In MySQL 4.0.14 and above you should use the --gdb option to mysqld. This installs an interrupt handler for SIGINT (needed to stop mysqld with ^C to set breakpoints) and disable stack tracing and core file handling.

It is very hard to debug MySQL under gdb if you do a lot of new connections the whole time as gdb doesn't free the memory for old threads. You can avoid this problem by starting mysqld with thread_cache_size set to a value equal to max_connections + 1. In most cases just using --thread_cache_size=5' helps a lot!

If you want to get a core dump on Linux if mysqld dies with a SIGSEGV signal, you can start mysqld with the --core-file option. This core file can be used to make a backtrace that may help you find out why mysqld died:

shell> gdb mysqld core
gdb>   backtrace full
gdb>   quit

See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”.

If you are using gdb 4.17.x or above on Linux, you should install a .gdb file, with the following information, in your current directory:

set print sevenbit off
handle SIGUSR1 nostop noprint
handle SIGUSR2 nostop noprint
handle SIGWAITING nostop noprint
handle SIGLWP nostop noprint
handle SIGPIPE nostop
handle SIGALRM nostop
handle SIGHUP nostop
handle SIGTERM nostop noprint

If you have problems debugging threads with gdb, you should download gdb 5.x and try this instead. The new gdb version has very improved thread handling!

Here is an example how to debug mysqld:

shell> gdb /usr/local/libexec/mysqld
gdb> run
...
backtrace full # Do this when mysqld crashes

Include the preceding output in a bug report, which you can file using the instructions in Section 1.6, “How to Report Bugs or Problems”.

If mysqld hangs, you can try to use some system tools like strace or /usr/proc/bin/pstack to examine where mysqld has hung.

strace /tmp/log libexec/mysqld

If you are using the Perl DBI interface, you can turn on debugging information by using the trace method or by setting the DBI_TRACE environment variable.

On some operating systems, the error log contains a stack trace if mysqld dies unexpectedly. You can use this to find out where (and maybe why) mysqld died. See Section 5.2.2, “The Error Log”. To get a stack trace, you must not compile mysqld with the -fomit-frame-pointer option to gcc. See Section 24.4.1.1, “Compiling MySQL for Debugging”.

A stack trace in the error log looks something like this:

mysqld got signal 11;
Attempting backtrace. You can use the following information
to find out where mysqld died. If you see no messages after
this, something went terribly wrong...

stack_bottom = 0x41fd0110 thread_stack 0x40000
mysqld(my_print_stacktrace+0x32)[0x9da402]
mysqld(handle_segfault+0x28a)[0x6648e9]
/lib/libpthread.so.0[0x7f1a5af000f0]
/lib/libc.so.6(strcmp+0x2)[0x7f1a5a10f0f2]
mysqld(_Z21check_change_passwordP3THDPKcS2_Pcj+0x7c)[0x7412cb]
mysqld(_ZN16set_var_password5checkEP3THD+0xd0)[0x688354]
mysqld(_Z17sql_set_variablesP3THDP4ListI12set_var_baseE+0x68)[0x688494]
mysqld(_Z21mysql_execute_commandP3THD+0x41a0)[0x67a170]
mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x282)[0x67f0ad]
mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xbb7[0x67fdf8]
mysqld(_Z10do_commandP3THD+0x24d)[0x6811b6]
mysqld(handle_one_connection+0x11c)[0x66e05e]

If resolution of function names for the trace fails, the trace contains less information:

mysqld got signal 11;
Attempting backtrace. You can use the following information
to find out where mysqld died. If you see no messages after
this, something went terribly wrong...

stack_bottom = 0x41fd0110 thread_stack 0x40000
[0x9da402]
[0x6648e9]
[0x7f1a5af000f0]
[0x7f1a5a10f0f2]
[0x7412cb]
[0x688354]
[0x688494]
[0x67a170]
[0x67f0ad]
[0x67fdf8]
[0x6811b6]
[0x66e05e]

In the latter case, you can use the resolve_stack_dump utility to determine where mysqld died by using the following procedure:

Newer versions of glibc stack trace functions also print the address as relative to the object. On glibc-based systems (Linux), the trace for a crash within a plugin looks something like:

plugin/auth/auth_test_plugin.so(+0x9a6)[0x7ff4d11c29a6]

To translate the relative address (+0x9a6) into a file name and line number, use this command:

shell> addr2line -fie auth_test_plugin.so 0x9a6
auth_test_plugin
mysql-trunk/plugin/auth/test_plugin.c:65

The addr2line utility is part of the binutils package on Linux.

On Solaris, the procedure is similar. The Solaris printstack() already prints relative addresses:

plugin/auth/auth_test_plugin.so:0x1510

To translate, use this command:

shell> gaddr2line -fie auth_test_plugin.so 0x1510
mysql-trunk/plugin/auth/test_plugin.c:88

Windows already prints the address, function name and line:

000007FEF07E10A4 auth_test_plugin.dll!auth_test_plugin()[test_plugin.c:72]

Note that before starting mysqld with the general query log enabled, you should check all your tables with myisamchk. See Chapter 5, MySQL Server Administration.

If mysqld dies or hangs, you should start mysqld with the general query log enabled. See Section 5.2.3, “The General Query Log”. When mysqld dies again, you can examine the end of the log file for the query that killed mysqld.

If you use the default general query log file, the log is stored in the database directory as host_name.log In most cases it is the last query in the log file that killed mysqld, but if possible you should verify this by restarting mysqld and executing the found query from the mysql command-line tools. If this works, you should also test all complicated queries that didn't complete.

You can also try the command EXPLAIN on all SELECT statements that takes a long time to ensure that mysqld is using indexes properly. See Section 13.8.2, “EXPLAIN Syntax”.

You can find the queries that take a long time to execute by starting mysqld with the slow query log enabled. See Section 5.2.5, “The Slow Query Log”.

If you find the text mysqld restarted in the error log file (normally named hostname.err) you probably have found a query that causes mysqld to fail. If this happens, you should check all your tables with myisamchk (see Chapter 5, MySQL Server Administration), and test the queries in the MySQL log files to see whether one fails. If you find such a query, try first upgrading to the newest MySQL version. If this doesn't help and you can't find anything in the mysql mail archive, you should report the bug to a MySQL mailing list. The mailing lists are described at http://lists.mysql.com/, which also has links to online list archives.

If you have started mysqld with --myisam-recover-options, MySQL automatically checks and tries to repair MyISAM tables if they are marked as 'not closed properly' or 'crashed'. If this happens, MySQL writes an entry in the hostname.err file 'Warning: Checking table ...' which is followed by Warning: Repairing table if the table needs to be repaired. If you get a lot of these errors, without mysqld having died unexpectedly just before, then something is wrong and needs to be investigated further. See Section 5.1.3, “Server Command Options”.

As of MySQL 5.5.3, when the server detects MyISAM table corruption, it writes additional information to the error log, such as the name and line number of the source file, and the list of threads accessing the table. Example: Got an error from thread_id=1, mi_dynrec.c:368. This is useful information to include in bug reports.

It is not a good sign if mysqld did die unexpectedly, but in this case, you should not investigate the Checking table... messages, but instead try to find out why mysqld died.

If you get corrupted tables or if mysqld always fails after some update commands, you can test whether this bug is reproducible by doing the following:

You can also use the script mysql_find_rows to just execute some of the update statements if you want to narrow down the problem.