Hosting guebs

Chapter 22. Extending MySQL

Table of Contents

22.1. MySQL Internals
22.1.1. MySQL Threads
22.1.2. The MySQL Test Suite
22.2. The MySQL Plugin API
22.2.1. Plugin API Characteristics
22.2.2. Plugin API Components
22.2.3. Types of Plugins
22.2.4. Writing Plugins
22.3. Adding New Functions to MySQL
22.3.1. Features of the User-Defined Function Interface
22.3.2. Adding a New User-Defined Function
22.3.3. Adding a New Native Function
22.4. Debugging and Porting MySQL
22.4.1. Debugging a MySQL Server
22.4.2. Debugging a MySQL Client
22.4.3. The DBUG Package

22.1. MySQL Internals

This chapter describes a lot of things that you need to know when working on the MySQL code. To track or contribute to MySQL development, follow the instructions in Section 2.11.3, “Installing MySQL from a Development Source Tree”. If you are interested in MySQL internals, you should also subscribe to our internals mailing list. This list has relatively low traffic. For details on how to subscribe, please see Section 1.6.1, “MySQL Mailing Lists”. Many MySQL developers at Oracle Corporation are on the internals list and we help other people who are working on the MySQL code. Feel free to use this list both to ask questions about the code and to send patches that you would like to contribute to the MySQL project!

22.1.1. MySQL Threads

The MySQL server creates the following threads:

  • Connection manager threads handle client connection requests on the network interfaces that the server listens to. On all platforms, one manager thread handles TCP/IP connection requests. On Unix, this manager thread also handles Unix socket file connection requests. On Windows, a manager thread handles shared-memory connection requests, and another handles named-pipe connection requests. The server does not create threads to handle interfaces that it does not listen to. For example, a Windows server that does not have support for named-pipe connections enabled does not create a thread to handle them.

  • Connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.

    For information about tuning the parameters that control thread resources, see Section 8.9.3, “How MySQL Uses Threads for Client Connections”.

  • On a master replication server, connections from slave servers are handled like client connections: There is one thread per connected slave.

  • On a slave replication server, an I/O thread is started to connect to the master server and read updates from it. An SQL thread is started to apply updates read from the master. These two threads run independently and can be started and stopped independently.

  • A signal thread handles all signals. This thread also normally handles alarms and calls process_alarm() to force timeouts on connections that have been idle too long.

  • If InnoDB is used, there will be 4 additional threads by default. Those are file I/O threads, controlled by the innodb_file_io_threads parameter. See Section 14.6.2, “InnoDB Startup Options and System Variables”.

  • If mysqld is compiled with -DUSE_ALARM_THREAD, a dedicated thread that handles alarms is created. This is only used on some systems where there are problems with sigwait() or if you want to use the thr_alarm() code in your application without a dedicated signal handling thread.

  • If the server is started with the --flush_time=val option, a dedicated thread is created to flush all tables every val seconds.

  • Each table for which INSERT DELAYED statements are issued gets its own thread. See Section 13.2.5.2, “INSERT DELAYED Syntax”.

  • If the event scheduler is active, there is one thread for the scheduler, and a thread for each event currently running. See Section 19.4.1, “Event Scheduler Overview”.

mysqladmin processlist only shows the connection, INSERT DELAYED, replication, and event threads.

22.1.2. The MySQL Test Suite

The test system that is included in Unix source and binary distributions makes it possible for users and developers to perform regression tests on the MySQL code. These tests can be run on Unix.

You can also write your own test cases. For information about the MySQL Test Framework, including system requirements, see the manual available at http://dev.mysql.com/doc/mysqltest/2.0/en/.

The current set of test cases doesn't test everything in MySQL, but it should catch most obvious bugs in the SQL processing code, operating system or library issues, and is quite thorough in testing replication. Our goal is to have the tests cover 100% of the code. We welcome contributions to our test suite. You may especially want to contribute tests that examine the functionality critical to your system because this ensures that all future MySQL releases work well with your applications.

The test system consists of a test language interpreter (mysqltest), a Perl script to run all tests (mysql-test-run.pl), the actual test cases written in a special test language, and their expected results. To run the test suite on your system after a build, type make test from the source root directory, or change location to the mysql-test directory and type ./mysql-test-run.pl. If you have installed a binary distribution, change location to the mysql-test directory under the installation root directory (for example, /usr/local/mysql/mysql-test), and run ./mysql-test-run.pl. All tests should succeed. If any do not, feel free to try to find out why and report the problem if it indicates a bug in MySQL. See Section 1.7, “How to Report Bugs or Problems”.

If one test fails, you should run mysql-test-run.pl with the --force option to check whether any other tests fail.

If you have a copy of mysqld running on the machine where you want to run the test suite, you do not have to stop it, as long as it is not using ports 9306 or 9307. If either of those ports is taken, you should set the MTR_BUILD_THREAD environment variable to an appropriate value, and the test suite will use a different set of ports for master, slave, NDB, and Instance Manager). For example:

shell> export MTR_BUILD_THREAD=31
shell> ./mysql-test-run.pl [options] [test_name]

In the mysql-test directory, you can run an individual test case with ./mysql-test-run.pl test_name.

If you have a question about the test suite, or have a test case to contribute, send an email message to the MySQL internals mailing list. See Section 1.6.1, “MySQL Mailing Lists”.

22.2. The MySQL Plugin API

MySQL 5.1 and up supports a plugin API that enables creation of server components. Plugins can be loaded at server startup, or loaded and unloaded at runtime without restarting the server. The API is generic and does not specify what plugins can do. The components supported by this interface include, but are not limited to, storage engines, full-text parser plugins, and server extensions.

For example, full-text parser plugins can be used to replace or augment the built-in full-text parser. A plugin can parse text into words using rules that differ from those used by the built-in parser. This can be useful if you need to parse text with characteristics different from those expected by the built-in parser.

The plugin interface is more general than the older user-defined function (UDF) interface.

The plugin interface uses the plugin table in the mysql database to record information about plugins that have been installed permanently with the INSTALL PLUGIN statement. This table is created as part of the MySQL installation process. (If you are upgrading from a version of MySQL older than 5.1, you should run the mysql_upgrade command to create this table. See Section 4.4.8, “mysql_upgrade — Check and Upgrade MySQL Tables”.) Plugins can also be installed for a single server invocation with the --plugin-load option. Plugins installed this way are not recorded in the plugin table. See Section 5.1.8.1, “Installing and Uninstalling Plugins”.

Additional Resources

The book MySQL 5.1 Plugin Development by Sergei Golubchik and Andrew Hutchings provides a wealth of detail about the plugin API. Despite the fact that the book's title refers to MySQL Server 5.1, most of the information in it applies to later versions as well.

22.2.1. Plugin API Characteristics

The server plugin API has these characteristics:

  • All plugins have several things in common.

    Each plugin has a name that it can be referred to in SQL statements, as well as other metadata such as an author and a description that provide other information. This information can be examined in the INFORMATION_SCHEMA.PLUGINS table or using the SHOW PLUGINS statement.

  • The plugin framework is extendable to accommodate different kinds of plugins.

    Although some aspects of the plugin API are common to all types of plugins, the API also permits type-specific interface elements so that different types of plugins can be created. A plugin with one purpose can have an interface most appropriate to its own requirements and not the requirements of some other plugin type.

    Interfaces for several types of plugins exist, such as storage engines, full-text parser, and INFORMATION_SCHEMA tables. Others can be added.

  • Plugins can expose information to users.

    A plugin can implement system and status variables that are available through the SHOW VARIABLES and SHOW STATUS statements.

  • The plugin API includes versioning information.

    The version information included in the plugin API enables a plugin library and each plugin that it contains to be self-identifying with respect to the API version that was used to build the library. If the API changes over time, the version numbers will change, but a server can examine a given plugin library's version information to determine whether it supports the plugins in the library.

    There are two types of version numbers. The first is the version for the general plugin framework itself. Each plugin library includes this kind of version number. The second type of version applies to individual plugins. Each specific type of plugin has a version for its interface, so each plugin in a library has a type-specific version number. For example, a library containing a full-text parser plugin has a general plugin API version number, and the plugin has a version number specific to the full-text plugin interface.

  • The plugin API implements security restrictions.

    A plugin library must be installed in a specific dedicated directory for which the location is controlled by the server and cannot be changed at runtime. Also, the library must contain specific symbols that identify it as a plugin library. The server will not load something as a plugin if it was not built as a plugin.

In some respects, the server plugin API is similar to the older user-defined function (UDF) API that it supersedes, but the plugin API has several advantages over the older interface. For example, UDFs had no versioning information. Also, the newer plugin interface eliminates the security issues of the older UDF interface. The older interface for writing nonplugin UDFs permitted libraries to be loaded from any directory searched by the system's dynamic linker, and the symbols that identified the UDF library were relatively nonspecific.

22.2.2. Plugin API Components

The server plugin implementation comprises several components.

SQL statements:

  • INSTALL PLUGIN registers a plugin in the mysql.plugin table and loads the plugin code.

  • UNINSTALL PLUGIN unregisters a plugin from the mysql.plugin table and unloads the plugin code.

  • The WITH PARSER clause for full-text index creation associates a full-text parser plugin with a given FULLTEXT index.

  • SHOW PLUGINS displays information about server plugins.

Command-line options and system variables:

  • The --plugin-load option enables plugins to be loaded at server startup time.

  • The plugin_dir system variable indicates the location of the directory where all plugins must be installed. The value of this variable can be specified at server startup with a --plugin_dir=path option. mysql_config --plugindir displays the default plugin directory path name.

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

Plugin-related tables:

Source files (the locations indicate where the files are found in MySQL source distributions):

  • In the include/mysql directory, plugin.h exposes the public plugin API. This file should be examined by anyone who wants to write a plugin library. plugin_xxx.h files provide additional information that pertains to specific types of plugins.

  • In the sql directory, sql_plugin.h and sql_plugin.cc comprise the internal plugin implementation. These files need not be consulted by plugin developers. They may be of interest for those who want to know more about how the server handles plugins.

22.2.3. Types of Plugins

The plugin API enables creation of plugins that implement several capabilities:

  • Storage engines

  • Full-text parsers

  • Daemons

  • INFORMATION_SCHEMA tables

The following sections provide an overview of these plugin types.

22.2.3.1. Storage Engine Plugins

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 14.4, “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.

22.2.3.2. Full-Text Parser Plugins

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:

  • The plugin can replace the built-in parser. In this role, the plugin reads the input to be parsed, splits it up into words, and passes the words to the server (either for indexing or for word accumulation).

    One reason to use a parser this way is that you need to use different rules from those of the built-in parser for determining how to split up input into words. For example, the built-in parser considers the text case-sensitive to consist of two words case and sensitive, whereas an application might need to treat the text as a single word.

  • The plugin can act in conjunction with the built-in parser by serving as a front end for it. In this role, the plugin extracts text from the input and passes the text to the parser, which splits up the text into words using its normal parsing rules. In particular, this parsing will be affected by the ft_xxx system variables and the stopword list.

    One reason to use a parser this way is that you need to index content such as PDF documents, XML documents, or .doc files. The built-in parser is not intended for those types of input but a plugin can pull out the text from these input sources and pass it to the built-in parser.

It is also possible for a parser plugin to operate in both roles. That is, it could extract text from nonplaintext 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 it 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 22.2.4.4, “Writing Full-Text Parser Plugins”.

22.2.3.3. Daemon Plugins

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 22.2.4.5, “Writing Daemon Plugins”.

22.2.3.4. INFORMATION_SCHEMA 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 22.2.4.6, “Writing INFORMATION_SCHEMA Plugins”.

22.2.4. Writing Plugins

To create a plugin library, you must provide the required descriptor information that indicates what plugins the library file contains, and write the interface functions for each plugin.

Every server plugin must have a general descriptor that provides information to the plugin API, and a type-specific descriptor that provides information about the plugin interface for a given type of plugin. The structure of the general descriptor is the same for all plugin types. The structure of the type-specific descriptor varies among plugin types and is determined by the requirements of what the plugin needs to do. The server plugin interface also enables plugins to expose status and system variables. These variables become visible through the SHOW STATUS and SHOW VARIABLES statements and the corresponding INFORMATION_SCHEMA tables.

You can write plugins in C or C++ (or another language that can use C calling conventions). Plugins are loaded and unloaded dynamically, so your operating system must support dynamic loading and you must have compiled mysqld dynamically (not statically).

A server plugin contains code that becomes part of the running server, so when you write the plugin, you are bound by any and all constraints that otherwise apply to writing server code. For example, you may have problems if you attempt to use functions from the libstdc++ library. These constraints may change in future versions of the server, so it is possible that server upgrades will require revisions to plugins originally written for older servers. For information about these constraints, see Section 2.11.4, “MySQL Source-Configuration Options”, and Section 2.11.5, “Dealing with Problems Compiling MySQL”.

22.2.4.1. Overview of Plugin Writing

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.

  1. In the plugin source file, include the header files that the plugin library needs. The plugin.h file is required, and the library might require other files as well. For example:

    #include <stdlib.h>
    #include <ctype.h>
    #include <mysql/plugin.h>
  2. Set up the descriptor information for the plugin library file. This descriptor must contain the general plugin descriptor for each server plugin in the file. For more information, see Section 22.2.4.2.1, “Library and Plugin Descriptors”. In addition, set up the type-specific descriptor for each server plugin in the library. Each plugin's general descriptor points to its type-specific descriptor.

  3. Write the plugin interface functions for each plugin. Each plugin's general plugin descriptor points to the initialization and deinitialization functions that the server should invoke when it loads and unloads the plugin. The plugin's type-specific description may also point to interface functions.

  4. Set up the status and system variables, if there are any.

  5. Compile the plugin library as a shared library and install it in the plugin directory. For more information, see Section 22.2.4.3, “Compiling and Installing Plugin Libraries”.

  6. Register the plugin with the server. For more information, see Section 5.1.8.1, “Installing and Uninstalling Plugins”.

  7. Test the plugin to verify that it works properly.

22.2.4.2. Plugin Data Structures

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

The plugin library must include the following descriptor information:

  • A library descriptor indicates the general server plugin API version number used by the library and contains a general plugin descriptor for each server plugin in the library. To provide the framework for this descriptor, invoke two macros from the plugin.h header file:

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

    The macros expand to provide a declaration for the API version automatically. You must provide the plugin descriptors.

  • Within the library descriptor, each general server plugin is described by a st_mysql_plugin structure. This plugin descriptor structure contains information that is common to every type of server plugin: A value that indicates the plugin type; the plugin name, author, description, and license type; pointers to the initialization and deinitialization functions that the server invokes when it loads and unloads the plugin, and pointers to any status or system variables the plugin implements.

  • Each general server plugin descriptor within the library descriptor also contains a pointer to a type-specific plugin descriptor. The structure of the type-specific descriptors varies from one plugin type to another because each type of plugin can have its own API. A type-specific plugin descriptor contains a type-specific API version number and pointers to the functions that are needed to implement that plugin type. For example, a full-text parser plugin has initialization and deinitialization functions, and a main parsing function. The server invokes these functions when it uses the plugin to parse text.

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

22.2.4.2.1. Library and Plugin Descriptors

Every plugin library must include a library descriptor that contains the general plugin descriptor for each server plugin in the file. This section discusses how to write the library and general descriptors for server plugins.

The library descriptor must define two symbols:

  • _mysql_plugin_interface_version_ specifies the version number of the general plugin framework. This is given by the MYSQL_PLUGIN_INTERFACE_VERSION symbol, which is defined in the plugin.h file.

  • _mysql_plugin_declarations_ defines an array of plugin declarations, terminated by a declaration with all members set to 0. Each declaration is an instance of the st_mysql_plugin structure (also defined in plugin.h). There must be one of these for each server plugin in the library.

If the server does not find those two symbols in a library, it does not accept it as a legal plugin library and rejects it with an error. This prevents use of a library for plugin purposes unless it was built specifically as a plugin library.

The conventional way to define the two required symbols is by using the mysql_declare_plugin() and mysql_declare_plugin_end macros from the plugin.h file:

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

Each server plugin must have a general descriptor that provides information to the server plugin API. The general descriptor has the same structure for all plugin types. The st_mysql_plugin structure in the plugin.h file defines this descriptor:

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             */
};

The st_mysql_plugin descriptor structure members are used as follows. char * members should be specified as null-terminated strings.

  • type: The plugin type. This must be one of the plugin-type values from plugin.h:

    /*
      The allowable types of plugins
    */
    #define MYSQL_UDF_PLUGIN             0  /* User-defined function        */
    #define MYSQL_STORAGE_ENGINE_PLUGIN  1  /* Storage Engine               */
    #define MYSQL_FTPARSER_PLUGIN        2  /* Full-text parser plugin      */
    #define MYSQL_DAEMON_PLUGIN          3  /* The daemon/raw plugin type */
    #define MYSQL_INFORMATION_SCHEMA_PLUGIN  4  /* The I_S plugin type */
    ...

    For example, for a full-text parser plugin, the type value is MYSQL_FTPARSER_PLUGIN.

  • info: A pointer to the type-specific descriptor for the plugin. This descriptor's structure depends on the particular type of plugin, unlike that of the general plugin descriptor structure. For version-control purposes, the first member of the type-specific descriptor for every plugin type is expected to be the interface version for the type. This enables the server to check the type-specific version for every plugin no matter its type. Following the version number, the descriptor includes any other members needed, such as callback functions and other information needed by the server to invoke the plugin properly. Later sections on writing particular types of server plugins describe the structure of their type-specific descriptors.

  • name: A string that gives the plugin name. This is the name that will be listed in the mysql.plugin table and by which you refer to the plugin in SQL statements such as INSTALL PLUGIN and UNINSTALL PLUGIN, or with the --plugin-load option. The name is also visible in the INFORMATION_SCHEMA.PLUGINS table or the output from SHOW PLUGINS.

    The plugin name should not begin with the name of any server option. If it does, the server will fail to initialize it. For example, the server has a --socket option, so you should not use a plugin name such as socket, socket_plugin, and so forth.

  • author: A string naming the plugin author. This can be whatever you like.

  • desc: A string that provides a general description of the plugin. This can be whatever you like.

  • license: The plugin license type. The value can be one of PLUGIN_LICENSE_PROPRIETARY, PLUGIN_LICENSE_GPL, or PLUGIN_LICENSE_BSD.

  • init: A once-only initialization function, or NULL if there is no such function. The server executes this function when it loads the plugin, which happens for INSTALL PLUGIN or, for plugins listed in the mysql.plugin table, at server startup. The function takes one argument that points to the internal structure used to identify the plugin. It returns zero for success and nonzero for failure.

  • deinit: A once-only deinitialization function, or NULL if there is no such function. The server executes this function when it unloads the plugin, which happens for UNINSTALL PLUGIN or, for plugins listed in the mysql.plugin table, at server shutdown. The function takes one argument that points to the internal structure used to identify the plugin It returns zero for success and nonzero for failure.

  • version: The plugin version number. When the plugin is installed, this value can be retrieved from the INFORMATION_SCHEMA.PLUGINS table. The value includes major and minor numbers. If you write the value as a hex constant, the format is 0xMMNN, where MM and NN are the major and minor numbers, respectively. For example, 0x0302 represents version 3.2.

  • status_vars: A pointer to a structure for status variables associated with the plugin, or NULL if there are no such variables. When the plugin is installed, these variables are displayed in the output of the SHOW STATUS statement.

    The status_vars member, if not NULL, points to an array of st_mysql_show_var structures that describe status variables. See Section 22.2.4.2.2, “Plugin Status and System Variables”.

  • system_vars: A pointer to a structure for system variables associated with the plugin, or NULL if there are no such variables. These options and system variables can be used to help initialize variables within the plugin.

    The system_vars member, if not NULL, points to an array of st_mysql_sys_var structures that describe system variables. See Section 22.2.4.2.2, “Plugin Status and System Variables”.

  • __reserved1: A placeholder for the future. Currently, it should be set to NULL.

The server invokes the init and deinit functions in the general plugin descriptor only when loading and unloading the plugin. They have nothing to do with use of the plugin such as happens when an SQL statement causes the plugin to be invoked.

For example, the descriptor information for a library that contains a single full-text parser plugin named simple_parser looks like this:

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
}
mysql_declare_plugin_end;

For a full-text parser plugin, the type must be MYSQL_FTPARSER_PLUGIN. This is the value that identifies the plugin as being legal for use in a WITH PARSER clause when creating a FULLTEXT index. (No other plugin type is legal for this clause.)

plugin.h defines the mysql_declare_plugin() and mysql_declare_plugin_end macros like this:

#ifndef MYSQL_DYNAMIC_PLUGIN
#define __MYSQL_DECLARE_PLUGIN(NAME, VERSION, PSIZE, DECLS)            \
int VERSION= MYSQL_PLUGIN_INTERFACE_VERSION;                           \
int PSIZE= sizeof(struct st_mysql_plugin);                             \
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}}
Note

Those declarations define the _mysql_plugin_interface_version_ symbol only if the MYSQL_DYNAMIC_PLUGIN symbol is defined. This means that -DMYSQL_DYNAMIC_PLUGIN must be provided as part of the compilation command to build the plugin as a shared library.

When the macros are used as just shown, they expand to the following code, which defines both of the required symbols (_mysql_plugin_interface_version_ and _mysql_plugin_declarations_):

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}}
};

The preceding example declares a single plugin in the general descriptor, but it is possible to declare multiple plugins. List the declarations one after the other between mysql_declare_plugin() and mysql_declare_plugin_end, separated by commas.

MySQL server plugins can be written in C or C++ (or another language that can use C calling conventions). If you write a C++ plugin, one C++ feature that you should not use is nonconstant variables to initialize global structures. Members of structures such as the st_mysql_plugin structure should be initialized only with constant variables. The simple_parser descriptor shown earlier is permissible in a C++ plugin because it satisfies that requirement:

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
}
mysql_declare_plugin_end;

Here is another valid way to write the general descriptor. It uses constant variables to indicate the plugin name, author, and description:

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
}
mysql_declare_plugin_end;

However, the following general descriptor is invalid. It uses structure members to indicate the plugin name, author, and description, but structures are not considered constant initializers in C++:

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
}
mysql_declare_plugin_end;
22.2.4.2.2. Plugin Status and System Variables

The server plugin interface enables plugins to expose status and system variables using the status_vars and system_vars members of the general plugin descriptor.

The status_vars member of the general plugin descriptor, if not 0, points to an array of st_mysql_show_var structures, each of which describes one status variable, followed by a structure with all members set to 0. The st_mysql_show_var structure has this definition:

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

When the plugin is installed, the plugin name and the name value are joined with an underscore to form the name displayed by SHOW STATUS.

The following table shows the permissible status variable type values and what the corresponding variable should be.

Table 22.1. Server Plugin Status Variable Types

Variable TypeMeaning
SHOW_BOOLPointer to a boolean variable
SHOW_INTPointer to an integer variable
SHOW_LONGPointer to a long integer variable
SHOW_LONGLONGPointer to a longlong integer variable
SHOW_CHARA string
SHOW_CHAR_PTRPointer to a string
SHOW_ARRAYPointer to another st_mysql_show_var array
SHOW_FUNCPointer to a function
SHOW_DOUBLEPointer to a double

For the SHOW_FUNC type, the function is called and fills in its out parameter, which then provides information about the variable to be displayed. The function has this signature:

#define SHOW_VAR_FUNC_BUFF_SIZE 1024

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

The system_vars member, if not 0, points to an array of st_mysql_sys_var structures, each of which describes one system variable (which can also be set from the command-line or configuration file), followed by a structure with all members set to 0. The st_mysql_sys_var structure is defined as follows:

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*);
};

Additional fields are append as required depending upon the flags.

For convenience, a number of macros are defined that make creating new system variables within a plugin much simpler.

Throughout the macros, the following fields are available:

  • name: An unquoted identifier for the system variable.

  • varname: The identifier for the static variable. Where not available, it is the same as the name field.

  • opt: Additional use flags for the system variable. The following table shows the permissible flags.

    Table 22.2. Server Plugin System Variable Flags

    Flag ValueDescription
    PLUGIN_VAR_READONLYThe system variable is read only
    PLUGIN_VAR_NOSYSVARThe system variable is not user visible at runtime
    PLUGIN_VAR_NOCMDOPTThe system variable is not configurable from the command line
    PLUGIN_VAR_NOCMDARGNo argument is required at the command line (typically used for boolean variables)
    PLUGIN_VAR_RQCMDARGAn argument is required at the command line (this is the default)
    PLUGIN_VAR_OPCMDARGAn argument is optional at the command line
    PLUGIN_VAR_MEMALLOCUsed for string variables; indicates that memory is to be allocated for storage of the string

  • comment: A descriptive comment to be displayed in the server help message. NULL if this variable is to be hidden.

  • check: The check function, NULL for default.

  • update: The update function, NULL for default.

  • default: The variable default value.

  • minimum: The variable minimum value.

  • maximum: The variable maximum value.

  • blocksize: The variable block size. When the value is set, it is rounded to the nearest multiple of blocksize.

A system variable may be accessed either by using the static variable directly or by using the SYSVAR()accessor macro. The SYSVAR() macro is provided for completeness. Usually it should be used only when the code cannot directly access the underlying variable.

For example:

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;

Session variables may be accessed only through the THDVAR() accessor macro. For example:

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;
   }

All global and session system variables must be published to mysqld before use. This is done by constructing a NULL-terminated array of the variables and linking to it in the plugin public interface. For example:

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
}
mysql_declare_plugin_end;

The following convenience macros enable you to declare different types of system variables:

  • Boolean system variables of type my_bool, which is a 1-byte boolean. (0 = FALSE, 1 = TRUE)

    MYSQL_THDVAR_BOOL(name, opt, comment, check, update, default)
    MYSQL_SYSVAR_BOOL(name, varname, opt, comment, check, update, default)
  • String system variables of type char*, which is a pointer to a null-terminated string.

    MYSQL_THDVAR_STR(name, opt, comment, check, update, default)
    MYSQL_SYSVAR_STR(name, varname, opt, comment, check, update, default)
  • Integer system variables, of which there are several varieties.

    • An int system variable, which is typically a 4-byte signed word.

      MYSQL_THDVAR_INT(name, opt, comment, check, update, default, min, max, blk)
      MYSQL_SYSVAR_INT(name, varname, opt, comment, check, update, default,
                     minimum, maximum, blocksize)
    • An unsigned int system variable, which is typically a 4-byte unsigned word.

      MYSQL_THDVAR_UINT(name, opt, comment, check, update, default, min, max, blk)
      MYSQL_SYSVAR_UINT(name, varname, opt, comment, check, update, default,
                      minimum, maximum, blocksize)
    • A long system variable, which is typically either a 4- or 8-byte signed word.

      MYSQL_THDVAR_LONG(name, opt, comment, check, update, default, min, max, blk)
      MYSQL_SYSVAR_LONG(name, varname, opt, comment, check, update, default,
                      minimum, maximum, blocksize)
    • An unsigned long system variable, which is typically either a 4- or 8-byte unsigned word.

      MYSQL_THDVAR_ULONG(name, opt, comment, check, update, default, min, max, blk)
      MYSQL_SYSVAR_ULONG(name, varname, opt, comment, check, update, default,
                       minimum, maximum, blocksize)
    • A long long system variable, which is typically an 8-byte signed word.

      MYSQL_THDVAR_LONGLONG(name, opt, comment, check, update,
                          default, minimum, maximum, blocksize)
      MYSQL_SYSVAR_LONGLONG(name, varname, opt, comment, check, update, 
                          default, minimum, maximum, blocksize)
    • An unsigned long long system variable, which is typically an 8-byte unsigned word.

      MYSQL_THDVAR_ULONGLONG(name, opt, comment, check, update, 
                           default, minimum, maximum, blocksize)
      MYSQL_SYSVAR_ULONGLONG(name, varname, opt, comment, check, update,
                           default, minimum, maximum, blocksize)
    • An unsigned long system variable, which is typically either a 4- or 8-byte unsigned word. The range of possible values is an ordinal of the number of elements in the typelib, starting from 0.

      MYSQL_THDVAR_ENUM(name, opt, comment, check, update, default, typelib)
      MYSQL_SYSVAR_ENUM(name, varname, opt, comment, check, update,
                      default, typelib)
    • An unsigned long long system variable, which is typically an 8-byte unsigned word. Each bit represents an element in the typelib.

      MYSQL_THDVAR_SET(name, opt, comment, check, update, default, typelib)
      MYSQL_SYSVAR_SET(name, varname, opt, comment, check, update,
      default, typelib)

Internally, all mutable and plugin system variables are stored in a HASH structure.

Display of the server command-line help text is handled by compiling a DYNAMIC_ARRAY of all variables relevant to command-line options, sorting them, and then iterating through them to display each option.

When a command-line option has been handled, it is then removed from the argv by the handle_option() function (my_getopt.c); in effect, it is consumed.

The server processes command-line options during the plugin installation process, immediately after the plugin has been successfully loaded but before the plugin initialization function has been called

Plugins loaded at runtime do not benefit from any configuration options and must have usable defaults. Once they are installed, they are loaded at mysqld initialization time and configuration options can be set at the command line or within my.cnf.

Plugins should consider the thd parameter to be read only.

22.2.4.3. Compiling and Installing Plugin Libraries

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 the GNU autotools, libtool 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 extension on your system.)

To use the autotools, you'll need to make a few changes to the configuration files at this point to enable the plugin to be compiled and installed. Assume that your MySQL distribution is installed at a base directory of /usr/local/mysql and that its header files are located in the include directory under the base directory.

Edit Makefile.am, which should look something like this:

#Makefile.am example for a plugin

pkgplugindir=$(libdir)/mysql/plugin
INCLUDES= -I$(top_builddir)/include -I$(top_srcdir)/include
#noinst_LTLIBRARIES= somepluglib.la
pkgplugin_LTLIBRARIES= somepluglib.la
somepluglib_la_SOURCES= plugin_example.c
somepluglib_la_LDFLAGS= -module -rpath $(pkgplugindir)
somepluglib_la_CFLAGS= -DMYSQL_DYNAMIC_PLUGIN
Note

As mentioned in Section 22.2.4.2.1, “Library and Plugin Descriptors”, be sure to specify -DMYSQL_DYNAMIC_PLUGIN as part of the compilation command when you build the plugin. The somepluglib_la_CFLAGS line takes care of this.

Adjust the INCLUDES line to specify the path name to the installed MySQL header files. Edit it to look like this:

INCLUDES= -I/usr/local/mysql/include

Make sure that the noinst_LTLIBRARIES line is commented out or remove it. Make sure that the pkglib_LTLIBRARIES line is not commented out; it enables the make install command.

Set up the files needed for the configure command, invoke it, and run make:

shell> autoreconf --force --install --symlink
shell> ./configure --prefix=/usr/local/mysql
shell> make

The --prefix option to configure indicates 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.

22.2.4.4. Writing 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:

  1. To write a full-text parser plugin, include the following header file in the plugin source file. Other MySQL or general header files might also be needed.

    #include <mysql/plugin.h>

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

  2. Set up the library descriptor for the plugin library file.

    This descriptor contains the general plugin descriptor for the server plugin. For a full-text parser plugin, the type must be MYSQL_FTPARSER_PLUGIN. This is the value that identifies the plugin as being legal for use in a WITH PARSER clause when creating a FULLTEXT index. (No other plugin type is legal for this clause.)

    For example, the library descriptor for a library that contains a single full-text parser plugin named simple_parser looks like this:

    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
    }
    mysql_declare_plugin_end;

    The name member (simple_parser) 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.

    For more information, see Section 22.2.4.2.1, “Library and Plugin Descriptors”.

  3. Set up the type-specific plugin descriptor.

    Each general plugin descriptor in the library descriptor points to a type-specific descriptor. For a full-text parser plugin, the type-specific descriptor is an instance of the st_mysql_ftparser structure in the plugin.h file:

    struct st_mysql_ftparser
    {
      int interface_version;
      int (*parse)(MYSQL_FTPARSER_PARAM *param);
      int (*init)(MYSQL_FTPARSER_PARAM *param);
      int (*deinit)(MYSQL_FTPARSER_PARAM *param);
    };

    As shown by the structure definition, the descriptor has an interface version number and contains pointers to three functions. The version is specified using a symbol of the form MYSQL_xxx_INTERFACE_VERSION (such as (MYSQL_FTPARSER_INTERFACE_VERSION for full-text parser plugins) The init and deinit members should point to a function or be set to 0 if the function is not needed. The parse member must point to the function that performs the parsing.

    In the simple_parser declaration, that descriptor is indicated by &simple_parser_descriptor. The descriptor specifies the version number for the full-text plugin interface (as given by MYSQL_FTPARSER_INTERFACE_VERSION), and the plugin's parsing, initialization, and deinitialization functions:

    static struct st_mysql_ftparser simple_parser_descriptor=
    {
      MYSQL_FTPARSER_INTERFACE_VERSION, /* interface version      */
      simple_parser_parse,              /* parsing function       */
      simple_parser_init,               /* parser init function   */
      simple_parser_deinit              /* parser deinit function */
    };

    A full-text parser plugin is used in two different contexts, indexing and searching. In both contexts, the server calls the initialization and deinitialization functions at the beginning and end of processing each SQL statement that causes the plugin to be invoked. However, during statement processing, the server calls the main parsing function in context-specific fashion:

    • For indexing, the server calls the parser for each column value to be indexed.

    • For searching, the server calls the parser to parse the search string. The parser might also be called for rows processed by the statement. In natural language mode, there is no need for the server to call the parser. For boolean mode phrase searches or natural language searches with query expansion, the parser is used to parse column values for information that is not in the index. Also, if a boolean mode search is done for a column that has no FULLTEXT index, the built-in parser will be called. (Plugins are associated with specific indexes. If there is no index, no plugin is used.)

    The plugin declaration in the general plugin descriptor has init and deinit members that point initialization and deinitialization functions, and so does the type-specific plugin descriptor to which it points. However, these pairs of functions have different purposes and are invoked for different reasons:

    • For the plugin declaration in the general plugin descriptor, the initialization and deinitialization functions are invoked when the plugin is loaded and unloaded.

    • For the type-specific plugin descriptor, the initialization and deinitialization functions are invoked per SQL statement for which the plugin is used.

    Each interface function named in the plugin descriptor should return zero for success or nonzero for failure, and each of them receives an argument that points to a MYSQL_FTPARSER_PARAM structure containing the parsing context. The structure has this definition:

    typedef struct st_mysql_ftparser_param
    {
      int (*mysql_parse)(struct st_mysql_ftparser_param *,
                         char *doc, int doc_len);
      int (*mysql_add_word)(struct st_mysql_ftparser_param *,
                            char *word, int word_len,
                            MYSQL_FTPARSER_BOOLEAN_INFO *boolean_info);
      void *ftparser_state;
      void *mysql_ftparam;
      struct charset_info_st *cs;
      char *doc;
      int length;
      int flags;
      enum enum_ftparser_mode mode;
    } MYSQL_FTPARSER_PARAM;
    Note

    The definition shown is current as of MySQL 5.1.12. It is incompatible with versions of MySQL 5.1 older than 5.1.12.

    The structure members are used as follows:

    • mysql_parse: A pointer to a callback function that invokes the server's built-in parser. Use this callback when the plugin acts as a front end to the built-in parser. That is, when the plugin parsing function is called, it should process the input to extract the text and pass the text to the mysql_parse callback.

      The first parameter for this callback function should be the param value itself:

      param->mysql_parse(param, ...);

      A front end plugin can extract text and pass it all at once to the built-in parser, or it can extract and pass text to the built-in parser a piece at a time. However, in this case, the built-in parser treats the pieces of text as though there are implicit word breaks between them.

    • mysql_add_word: A pointer to a callback function that adds a word to a full-text index or to the list of search terms. Use this callback when the parser plugin replaces the built-in parser. That is, when the plugin parsing function is called, it should parse the input into words and invoke the mysql_add_word callback for each word.

      The first parameter for this callback function should be the param value itself:

      param->mysql_add_word(param, ...);
    • ftparser_state: This is a generic pointer. The plugin can set it to point to information to be used internally for its own purposes.

    • mysql_ftparam: This is set by the server. It is passed as the first argument to the mysql_parse or mysql_add_word callback.

    • cs: A pointer to information about the character set of the text, or 0 if no information is available.

    • doc: A pointer to the text to be parsed.

    • length: The length of the text to be parsed, in bytes.

    • flags: Parser flags. This is zero if there are no special flags. Currently, the only nonzero flag is MYSQL_FTFLAGS_NEED_COPY, which means that mysql_add_word() must save a copy of the word (that is, it cannot use a pointer to the word because the word is in a buffer that will be overwritten.) This member was added in MySQL 5.1.12.

      This flag might be set or reset by MySQL before calling the parser plugin, by the parser plugin itself, or by the mysql_parse() function.

    • mode: The parsing mode. This value will be one of the following constants:

      • MYSQL_FTPARSER_SIMPLE_MODE: Parse in fast and simple mode, which is used for indexing and for natural language queries. The parser should pass to the server only those words that should be indexed. If the parser uses length limits or a stopword list to determine which words to ignore, it should not pass such words to the server.

      • MYSQL_FTPARSER_WITH_STOPWORDS: Parse in stopword mode. This is used in boolean searches for phrase matching. The parser should pass all words to the server, even stopwords or words that are outside any normal length limits.

      • MYSQL_FTPARSER_FULL_BOOLEAN_INFO: Parse in boolean mode. This is used for parsing boolean query strings. The parser should recognize not only words but also boolean-mode operators and pass them to the server as tokens using the mysql_add_word callback. To tell the server what kind of token is being passed, the plugin needs to fill in a MYSQL_FTPARSER_BOOLEAN_INFO structure and pass a pointer to it.

    If the parser is called in boolean mode, the param->mode value will be MYSQL_FTPARSER_FULL_BOOLEAN_INFO. The MYSQL_FTPARSER_BOOLEAN_INFO structure that the parser uses for passing token information to the server looks like this:

    typedef struct st_mysql_ftparser_boolean_info
    {
      enum enum_ft_token_type type;
      int yesno;
      int weight_adjust;
      bool wasign;
      bool trunc;
      /* These are parser state and must be removed. */
      byte prev;
      byte *quot;
    } MYSQL_FTPARSER_BOOLEAN_INFO;

    The parser should fill in the structure members as follows:

    • type: The token type. The following table shows the permissible types.

      Table 22.3. Full-Text Parser Token Types

      Token ValueMeaning
      FT_TOKEN_EOFEnd of data
      FT_TOKEN_WORDA regular word
      FT_TOKEN_LEFT_PARENThe beginning of a group or subexpression
      FT_TOKEN_RIGHT_PARENThe end of a group or subexpression
      FT_TOKEN_STOPWORDA stopword

    • yesno: Whether the word must be present for a match to occur. 0 means that the word is optional but increases the match relevance if it is present. Values larger than 0 mean that the word must be present. Values smaller than 0 mean that the word must not be present.

    • weight_adjust: A weighting factor that determines how much a match for the word counts. It can be used to increase or decrease the word's importance in relevance calculations. A value of zero indicates no weight adjustment. Values greater than or less than zero mean higher or lower weight, respectively. The examples at Section 12.9.2, “Boolean Full-Text Searches”, that use the < and > operators illustrate how weighting works.

    • wasign: The sign of the weighting factor. A negative value acts like the ~ boolean-search operator, which causes the word's contribution to the relevance to be negative.

    • trunc: Whether matching should be done as if the boolean-mode * truncation operator had been given.

    Plugins should not use the prev and quot members of the MYSQL_FTPARSER_BOOLEAN_INFO structure.

  4. Set up the plugin interface functions.

    The general plugin descriptor in the library descriptor names the initialization and deinitialization functions that the server should invoke when it loads and unloads the plugin. For simple_parser, these functions do nothing but return zero to indicate that they succeeded:

    static int simple_parser_plugin_init(void *arg __attribute__((unused)))
    {
      return(0);
    }
    
    static int simple_parser_plugin_deinit(void *arg __attribute__((unused)))
    {
      return(0);
    }

    Because those functions do not actually do anything, you could omit them and specify 0 for each of them in the plugin declaration.

    The type-specific plugin descriptor for simple_parser names the initialization, deinitialization, and parsing functions that the server invokes when the plugin is used. For simple_parser, the initialization and deinitialization functions do nothing:

    static int simple_parser_init(MYSQL_FTPARSER_PARAM *param
                                  __attribute__((unused)))
    {
      return(0);
    }
    
    static int simple_parser_deinit(MYSQL_FTPARSER_PARAM *param
                                    __attribute__((unused)))
    {
      return(0);
    }

    Here too, because those functions do nothing, you could omit them and specify 0 for each of them in the plugin descriptor.

    The main parsing function, simple_parser_parse(), acts as a replacement for the built-in full-text parser, so it needs to split text into words and pass each word to the server. The parsing function's first argument is a pointer to a structure that contains the parsing context. This structure has a doc member that points to the text to be parsed, and a length member that indicates how long the text is. The simple parsing done by the plugin considers nonempty runs of whitespace characters to be words, so it identifies words like this:

    static int simple_parser_parse(MYSQL_FTPARSER_PARAM *param)
    {
      char *end, *start, *docend= param->doc + param->length;
    
      for (end= start= param->doc;; end++)
      {
        if (end == docend)
        {
          if (end > start)
            add_word(param, start, end - start);
          break;
        }
        else if (isspace(*end))
        {
          if (end > start)
            add_word(param, start, end - start);
          start= end + 1;
        }
      }
      return(0);
    }

    As the parser finds each word, it invokes a function add_word() to pass the word to the server. add_word() is a helper function only; it is not part of the plugin interface. The parser passes the parsing context pointer to add_word(), as well as a pointer to the word and a length value:

    static void add_word(MYSQL_FTPARSER_PARAM *param, char *word, size_t len)
    {
      MYSQL_FTPARSER_BOOLEAN_INFO bool_info=
        { FT_TOKEN_WORD, 0, 0, 0, 0, ' ', 0 };
    
      param->mysql_add_word(param, word, len, &bool_info);
    }

    For boolean-mode parsing, add_word() fills in the members of the bool_info structure as described earlier in the discussion of the st_mysql_ftparser_boolean_info structure.

  5. Set up the status variables. For the simple_parser plugin, the following status variable array sets up one status variable with a value that is static text, and another with a value that is stored in a long integer variable:

    long number_of_calls= 0;
    
    struct st_mysql_show_var simple_status[]=
    {
      {"static", (char *)"just a static text", SHOW_CHAR},
      {"called", (char *)&number_of_calls,     SHOW_LONG},
      {0,0,0}
    };

    When the plugin is installed, the plugin name and the name value are joined with an underscore to form the name displayed by SHOW STATUS. For the array just shown, the resulting status variable names are simple_parser_static and simple_parser_called. This convention means that you can easily display the variables for a plugin using its name:

    mysql> SHOW STATUS LIKE 'simple_parser%';
    +----------------------+--------------------+
    | Variable_name        | Value              |
    +----------------------+--------------------+
    | simple_parser_static | just a static text |
    | simple_parser_called | 0                  |
    +----------------------+--------------------+
    
  6. To compile and install a plugin library object file, use the instructions in Section 22.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 simple_parser 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 mypluglib.so (the extension might be different depending on your platform).

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

    mysql> INSTALL PLUGIN simple_parser SONAME 'mypluglib.so';
    

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

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

  9. Test the plugin to verify that it works properly.

    Create a table that contains a string column and associate the parser plugin with a FULLTEXT index on the column:

    mysql> CREATE TABLE t (c VARCHAR(255),
        ->   FULLTEXT (c) WITH PARSER simple_parser
        -> ) ENGINE=MyISAM;
    Query OK, 0 rows affected (0.01 sec)
    

    Insert some text into the table and try some searches. These should verify that the parser plugin treats all nonwhitespace characters as word characters:

    mysql> INSERT INTO t VALUES
        ->   ('latin1_general_cs is a case-sensitive collation'),
        ->   ('I\'d like a case of oranges'),
        ->   ('this is sensitive information'),
        ->   ('another row'),
        ->   ('yet another row');
    Query OK, 5 rows affected (0.02 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    
    mysql> SELECT c FROM t;
    +-------------------------------------------------+
    | c                                               |
    +-------------------------------------------------+
    | latin1_general_cs is a case-sensitive collation |
    | I'd like a case of oranges                      |
    | this is sensitive information                   |
    | another row                                     |
    | yet another row                                 |
    +-------------------------------------------------+
    5 rows in set (0.00 sec)
    
    mysql> SELECT MATCH(c) AGAINST('case') FROM t;
    +--------------------------+
    | MATCH(c) AGAINST('case') |
    +--------------------------+
    |                        0 |
    |          1.2968142032623 |
    |                        0 |
    |                        0 |
    |                        0 |
    +--------------------------+
    5 rows in set (0.00 sec)
    
    mysql> SELECT MATCH(c) AGAINST('sensitive') FROM t;
    +-------------------------------+
    | MATCH(c) AGAINST('sensitive') |
    +-------------------------------+
    |                             0 |
    |                             0 |
    |               1.3253291845322 |
    |                             0 |
    |                             0 |
    +-------------------------------+
    5 rows in set (0.01 sec)
    
    mysql> SELECT MATCH(c) AGAINST('case-sensitive') FROM t;
    +------------------------------------+
    | MATCH(c) AGAINST('case-sensitive') |
    +------------------------------------+
    |                    1.3109166622162 |
    |                                  0 |
    |                                  0 |
    |                                  0 |
    |                                  0 |
    +------------------------------------+
    5 rows in set (0.01 sec)
    
    mysql> SELECT MATCH(c) AGAINST('I\'d') FROM t;
    +--------------------------+
    | MATCH(c) AGAINST('I\'d') |
    +--------------------------+
    |                        0 |
    |          1.2968142032623 |
    |                        0 |
    |                        0 |
    |                        0 |
    +--------------------------+
    5 rows in set (0.01 sec)
    

    Note how neither case nor insensitive match case-insensitive the way that they would for the built-in parser.

22.2.4.5. Writing Daemon Plugins

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.

#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                  */
}
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:

  • daemon_example_plugin_init() opens the heartbeat file and spawns a thread that wakes up periodically and writes the next message to the file.

  • daemon_example_plugin_deinit() closes the file and performs other cleanup.

To compile and install a plugin library object file, use the instructions in Section 22.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 extension might be different 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 extension 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;

22.2.4.6. Writing INFORMATION_SCHEMA Plugins

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

#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 */
}
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 provides information about the table structure and a function that populates the table.

  • The deinitialization function performs any required cleanup. If no cleanup is needed, this descriptor member can be NULL (as in the example shown).

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:

  • fields_info: An array of ST_FIELD_INFO structures that contain information about each column.

  • fill_table: A function that populates the table.

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 21.8.1, “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 22.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;

22.3. Adding New Functions to MySQL

There are three ways to add new functions to MySQL:

Each method of creating compiled functions has advantages and disadvantages:

  • If you write user-defined functions, you must install object files in addition to the server itself. If you compile your function into the server, you don't need to do that.

  • Native functions require you to modify a source distribution. UDFs do not. You can add UDFs to a binary MySQL distribution. No access to MySQL source is necessary.

  • If you upgrade your MySQL distribution, you can continue to use your previously installed UDFs, unless you upgrade to a newer version for which the UDF interface changes. For native functions, you must repeat your modifications each time you upgrade.

Whichever method you use to add new functions, they can be invoked in SQL statements just like native functions such as ABS() or SOUNDEX().

See Section 9.2.4, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

The following sections describe features of the UDF interface, provide instructions for writing UDFs, discuss security precautions that MySQL takes to prevent UDF misuse, and describe how to add native MySQL functions.

For example source code that illustrates how to write UDFs, take a look at the sql/udf_example.c file that is provided in MySQL source distributions.

22.3.1. Features of the User-Defined Function Interface

The MySQL interface for user-defined functions provides the following features and capabilities:

  • Functions can return string, integer, or real values and can accept arguments of those same types.

  • You can define simple functions that operate on a single row at a time, or aggregate functions that operate on groups of rows.

  • Information is provided to functions that enables them to check the number, types, and names of the arguments passed to them.

  • You can tell MySQL to coerce arguments to a given type before passing them to a function.

  • You can indicate that a function returns NULL or that an error occurred.

22.3.2. Adding a New User-Defined Function

For the UDF mechanism to work, functions must be written in C or C++ and your operating system must support dynamic loading. MySQL source distributions include a file sql/udf_example.c that defines 5 new functions. Consult this file to see how UDF calling conventions work. The include/mysql_com.h header file defines UDF-related symbols and data structures. (You need not include this header file directly because it is included by mysql.h.)

A UDF contains code that becomes part of the running server, so when you write a UDF, you are bound by any and all constraints that otherwise apply to writing server code. For example, you may have problems if you attempt to use functions from the libstdc++ library. These constraints may change in future versions of the server, so it is possible that server upgrades will require revisions to UDFs that were originally written for older servers. For information about these constraints, see Section 2.11.4, “MySQL Source-Configuration Options”, and Section 2.11.5, “Dealing with Problems Compiling MySQL”.

To be able to use UDFs, you need to link mysqld dynamically. Don't configure MySQL using --with-mysqld-ldflags=-all-static. If you want to use a UDF that needs to access symbols from mysqld (for example, the metaphone function in sql/udf_example.c that uses default_charset_info), you must link the program with -rdynamic (see man dlopen). If you plan to use UDFs, the rule of thumb is to configure MySQL with --with-mysqld-ldflags=-rdynamic unless you have a very good reason not to.

For each function that you want to use in SQL statements, you should define corresponding C (or C++) functions. In the following discussion, the name xxx is used for an example function name. To distinguish between SQL and C/C++ usage, XXX() (uppercase) indicates an SQL function call, and xxx() (lowercase) indicates a C/C++ function call.

Note

When using C++ you can encapsulate your C functions within:

extern "C" { ... }

This will ensure that your C++ function names remain readable in the completed UDF.

The C/C++ functions that you write to implement the interface for XXX() are:

  • xxx() (required)

    The main function. This is where the function result is computed. The correspondence between the SQL function data type and the return type of your C/C++ function is shown here.

    SQL TypeC/C++ Type
    STRINGchar *
    INTEGERlong long
    REALdouble

    It is also possible to declare a DECIMAL function, but currently the value is returned as a string, so you should write the UDF as though it were a STRING function. ROW functions are not implemented.

  • xxx_init() (optional)

    The initialization function for xxx(). It can be used for the following purposes:

    • To check the number of arguments to XXX().

    • To check that the arguments are of a required type or, alternatively, to tell MySQL to coerce arguments to the types you want when the main function is called.

    • To allocate any memory required by the main function.

    • To specify the maximum length of the result.

    • To specify (for REAL functions) the maximum number of decimal places in the result.

    • To specify whether the result can be NULL.

  • xxx_deinit() (optional)

    The deinitialization function for xxx(). It should deallocate any memory allocated by the initialization function.

When an SQL statement invokes XXX(), MySQL calls the initialization function xxx_init() to let it perform any required setup, such as argument checking or memory allocation. If xxx_init() returns an error, MySQL aborts the SQL statement with an error message and does not call the main or deinitialization functions. Otherwise, MySQL calls the main function xxx() once for each row. After all rows have been processed, MySQL calls the deinitialization function xxx_deinit() so that it can perform any required cleanup.

For aggregate functions that work like SUM(), you must also provide the following functions:

  • xxx_clear()

    Reset the current aggregate value but do not insert the argument as the initial aggregate value for a new group.

  • xxx_add()

    Add the argument to the current aggregate value.

MySQL handles aggregate UDFs as follows:

  1. Call xxx_init() to let the aggregate function allocate any memory it needs for storing results.

  2. Sort the table according to the GROUP BY expression.

  3. Call xxx_clear() for the first row in each new group.

  4. Call xxx_add() for each row that belongs in the same group.

  5. Call xxx() to get the result for the aggregate when the group changes or after the last row has been processed.

  6. Repeat steps 3 to 5 until all rows has been processed

  7. Call xxx_deinit() to let the UDF free any memory it has allocated.

All functions must be thread-safe. This includes not just the main function, but the initialization and deinitialization functions as well, and also the additional functions required by aggregate functions. A consequence of this requirement is that you are not permitted to allocate any global or static variables that change! If you need memory, you should allocate it in xxx_init() and free it in xxx_deinit().

22.3.2.1. UDF Calling Sequences for Simple Functions

This section describes the different functions that you need to define when you create a simple UDF. Section 22.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.)

  • my_bool maybe_null

    xxx_init() should set maybe_null to 1 if xxx() can return NULL. The default value is 1 if any of the arguments are declared maybe_null.

  • unsigned int decimals

    The number of decimal digits to the right of the decimal point. The default value is the maximum number of decimal digits in the arguments passed to the main function. For example, if the function is passed 1.34, 1.345, and 1.3, the default would be 3, because 1.345 has 3 decimal digits.

    For arguments that have no fixed number of decimals, the decimals value is set to 31, which is 1 more than the maximum number of decimals permitted for the DECIMAL, FLOAT, and DOUBLE data types.

    A decimals value of 31 is used for arguments in cases such as a FLOAT or DOUBLE column declared without an explicit number of decimals (for example, FLOAT rather than FLOAT(10,3)) and for floating-point constants such as 1345E-3. It is also used for string and other nonnumber arguments that might be converted within the function to numeric form.

    The value to which the decimals member is initialized is only a default. It can be changed within the function to reflect the actual calculation performed. The default is determined such that the largest number of decimals of the arguments is used. If the number of decimals is 31 for even one of the arguments, that is the value used for decimals.

  • unsigned int max_length

    The maximum length of the result. The default max_length value differs depending on the result type of the function. For string functions, the default is the length of the longest argument. For integer functions, the default is 21 digits. For real functions, the default is 13 plus the number of decimal digits indicated by initid->decimals. (For numeric functions, the length includes any sign or decimal point characters.)

    If you want to return a blob value, you can set max_length to 65KB or 16MB. This memory is not allocated, but the value is used to decide which data type to use if there is a need to temporarily store the data.

  • char *ptr

    A pointer that the function can use for its own purposes. For example, functions can use initid->ptr to communicate allocated memory among themselves. xxx_init() should allocate the memory and assign it to this pointer:

    initid->ptr = allocated_memory;

    In xxx() and xxx_deinit(), refer to initid->ptr to use or deallocate the memory.

  • my_bool const_item

    xxx_init() should set const_item to 1 if xxx() always returns the same value and to 0 otherwise.

22.3.2.2. UDF Calling Sequences for Aggregate Functions

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

  • xxx_reset()

    This function is called when MySQL finds the first row in a new group. It should reset any internal summary variables and then use the given UDF_ARGS argument as the first value in your internal summary value for the group. Declare xxx_reset() as follows:

    void xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
                   char *is_null, char *error);

    xxx_reset() is not needed or used in MySQL 5.1, in which the UDF interface uses xxx_clear() instead. However, you can define both xxx_reset() and xxx_clear() if you want to have your UDF work with older versions of the server. (If you do include both functions, the xxx_reset() function in many cases can be implemented internally by calling xxx_clear() to reset all variables, and then calling xxx_add() to add the UDF_ARGS argument as the first value in the group.)

  • xxx_clear()

    This function is called when MySQL needs to reset the summary results. It is called at the beginning for each new group but can also be called to reset the values for a query where there were no matching rows. Declare xxx_clear() as follows:

    void xxx_clear(UDF_INIT *initid, char *is_null, char *error);

    is_null is set to point to CHAR(0) before calling xxx_clear().

    If something went wrong, you can store a value in the variable to which the error argument points. error points to a single-byte variable, not to a string buffer.

    xxx_clear() is required by MySQL 5.1.

  • xxx_add()

    This function is called for all rows that belong to the same group. You should use it to add the value in the UDF_ARGS argument to your internal summary variable.

    void xxx_add(UDF_INIT *initid, UDF_ARGS *args,
    char *is_null, char *error);

The xxx() function for an aggregate UDF should be declared the same way as for a nonaggregate UDF. See Section 22.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 22.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 22.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.

22.3.2.3. UDF Argument Processing

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

  • unsigned int arg_count

    The number of arguments. Check this value in the initialization function if you require your function to be called with a particular number of arguments. For example:

    if (args->arg_count != 2)
    {
        strcpy(message,"XXX() requires two arguments");
        return 1;
    }

    For other UDF_ARGS member values that are arrays, array references are zero-based. That is, refer to array members using index values from 0 to args->arg_count – 1.

  • enum Item_result *arg_type

    A pointer to an array containing the types for each argument. The possible type values are STRING_RESULT, INT_RESULT, REAL_RESULT, and DECIMAL_RESULT.

    To make sure that arguments are of a given type and return an error if they are not, check the arg_type array in the initialization function. For example:

    if (args->arg_type[0] != STRING_RESULT ||
        args->arg_type[1] != INT_RESULT)
    {
        strcpy(message,"XXX() requires a string and an integer");
        return 1;
    }

    Arguments of type DECIMAL_RESULT are passed as strings, so you should handle them the same way as STRING_RESULT values.

    As an alternative to requiring your function's arguments to be of particular types, you can use the initialization function to set the arg_type elements to the types you want. This causes MySQL to coerce arguments to those types for each call to xxx(). For example, to specify that the first two arguments should be coerced to string and integer, respectively, do this in xxx_init():

    args->arg_type[0] = STRING_RESULT;
    args->arg_type[1] = INT_RESULT;

    Exact-value decimal arguments such as 1.3 or DECIMAL column values are passed with a type of DECIMAL_RESULT. However, the values are passed as strings. If you want to receive a number, use the initialization function to specify that the argument should be coerced to a REAL_RESULT value:

    args->arg_type[2] = REAL_RESULT;
  • char **args

    args->args communicates information to the initialization function about the general nature of the arguments passed to your function. For a constant argument i, args->args[i] points to the argument value. (See later for instructions on how to access the value properly.) For a nonconstant argument, args->args[i] is 0. A constant argument is an expression that uses only constants, such as 3 or 4*7-2 or SIN(3.14). A nonconstant argument is an expression that refers to values that may change from row to row, such as column names or functions that are called with nonconstant arguments.

    For each invocation of the main function, args->args contains the actual arguments that are passed for the row currently being processed.

    If argument i represents NULL, args->args[i] is a null pointer (0). If the argument is not NULL, functions can refer to it as follows:

    • An argument of type STRING_RESULT is given as a string pointer plus a length, to enable handling of binary data or data of arbitrary length. The string contents are available as args->args[i] and the string length is args->lengths[i]. Do not assume that the string is null-terminated.

    • For an argument of type INT_RESULT, you must cast args->args[i] to a long long value:

      long long int_val;
      int_val = *((long long*) args->args[i]);
    • For an argument of type REAL_RESULT, you must cast args->args[i] to a double value:

      double    real_val;
      real_val = *((double*) args->args[i]);
    • For an argument of type DECIMAL_RESULT, the value is passed as a string and should be handled like a STRING_RESULT value.

    • ROW_RESULT arguments are not implemented.

  • unsigned long *lengths

    For the initialization function, the lengths array indicates the maximum string length for each argument. You should not change these. For each invocation of the main function, lengths contains the actual lengths of any string arguments that are passed for the row currently being processed. For arguments of types INT_RESULT or REAL_RESULT, lengths still contains the maximum length of the argument (as for the initialization function).

  • char *maybe_null

    For the initialization function, the maybe_null array indicates for each argument whether the argument value might be null (0 if no, 1 if yes).

  • char **attributes

    args->attributes communicates information about the names of the UDF arguments. For argument i, the attribute name is available as a string in args->attributes[i] and the attribute length is args->attribute_lengths[i]. Do not assume that the string is null-terminated.

    By default, the name of a UDF argument is the text of the expression used to specify the argument. For UDFs, an argument may also have an optional [AS] alias_name clause, in which case the argument name is alias_name. The attributes value for each argument thus depends on whether an alias was given.

    Suppose that a UDF my_udf() is invoked as follows:

    SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);

    In this case, the attributes and attribute_lengths arrays will have these values:

    args->attributes[0] = "expr1"
    args->attribute_lengths[0] = 5
    
    args->attributes[1] = "alias1"
    args->attribute_lengths[1] = 6
    
    args->attributes[2] = "alias2"
    args->attribute_lengths[2] = 6
  • unsigned long *attribute_lengths

    The attribute_lengths array indicates the length of each argument name.

22.3.2.4. UDF Return Values and Error Handling

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 multi-byte 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 22.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.)

22.3.2.5. Compiling and Installing User-Defined Functions

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:

  • metaphon() returns a metaphon string of the string argument. This is something like a soundex string, but it is more tuned for English.

  • myfunc_double() returns the sum of the ASCII values of the characters in its arguments, divided by the sum of the length of its arguments.

  • myfunc_int() returns the sum of the length of its arguments.

  • sequence([const int]) returns a sequence starting from the given number or 1 if no number has been given.

  • lookup() returns the IP address for a host name.

  • reverse_lookup() returns the host name for an IP address. The function may be called either with a single string argument of the form 'xxx.xxx.xxx.xxx' or with four numbers.

  • avgcost() returns an average cost. This is an aggregate function.

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 configure and libtool (which is how MySQL is configured), you should be able to create udf_example.so with a simpler command:

shell> make udf_example.la

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. Compiling the shared object using make produces a file named something like udf_example.so.0.0.0 in the .libs directory (the exact name may vary from platform to platform). 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.

Note

This is a change in MySQL 5.1. For earlier versions of MySQL, the shared object can be located in any directory that is searched by your system's dynamic linker.

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:

  1. Obtain the development source for MySQL 5.1. See Section 2.1.3, “How to Get MySQL”.

  2. Obtain the CMake build utility, if necessary, from http://www.cmake.org. (Version 2.6 or later is required).

  3. In the source tree, look in the sql directory. There are files named udf_example.def udf_example.c there. Copy both files from this directory to your working directory.

  4. Create a CMake makefile (CMakeLists.txt) with these contents:

    PROJECT(udf_example)
    
    # Path for MySQL include directory
    INCLUDE_DIRECTORIES("c:/mysql/include")
    
    ADD_DEFINITIONS("-DHAVE_DLOPEN")
    ADD_LIBRARY(udf_example MODULE udf_example.c udf_example.def)
    TARGET_LINK_LIBRARIES(udf_example wsock32)
  5. Create the VC project and solution files:

    cmake -G "<Generator>"

    Invoking cmake --help shows you a list of valid Generators.

  6. Create udf_example.dll:

    devenv udf_example.sln /build Release

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.

22.3.2.6. User-Defined Function Security Precautions

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

You must have the INSERT privilege to be able to use CREATE FUNCTION and the DELETE privilege to be able to use DROP FUNCTION. This is necessary because these 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.

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.

Note

This is a change in MySQL 5.1. For earlier versions of MySQL, the shared object can be located in any directory that is searched by your system's dynamic linker.

22.3.3. Adding a New Native Function

To add a new native MySQL function, use the procedure described here, which requires that you use a source distribution. You cannot add native functions to a binary distribution because it is necessary to modify MySQL source code and compile MySQL from the modified source. If you migrate to another version of MySQL (for example, when a new version is released), you must repeat the procedure with the new version.

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

To add a new native function, follow these steps to modify source files in the sql directory. For MySQL 5.1, the first two steps apply only as of 5.1.13. For older versions, see the instructions in the corresponding section of the MySQL 5.0 manual.

  1. Create a subclass for the function in item_create.cc:

    • If the function takes a fixed number of arguments, create a subclass of Create_func_arg0, Create_func_arg1, Create_func_arg2, or Create_func_arg3, respectively, depending on whether the function takes zero, one, two, or three arguments. For examples, see the Create_func_uuid, Create_func_abs, Create_func_pow, and Create_func_lpad classes.

    • If the function takes a variable number of arguments, create a subclass of Create_native_func. For an example, see Create_func_concat.

  2. To provide a name by which the function can be referred to in SQL statements, register the name in item_create.cc by adding a line to this array:

    static Native_func_registry func_array[]

    You can register several names for the same function. For example, see the lines for "LCASE" and "LOWER", which are aliases for Create_func_lcase.

  3. In item_func.h, declare a class inheriting from Item_num_func or Item_str_func, depending on whether your function returns a number or a string.

  4. In item_func.cc, add one of the following declarations, depending on whether you are defining a numeric or string function:

    double   Item_func_newname::val()
    longlong Item_func_newname::val_int()
    String  *Item_func_newname::Str(String *str)

    If you inherit your object from any of the standard items (like Item_num_func), you probably only have to define one of these functions and let the parent object take care of the other functions. For example, the Item_str_func class defines a val() function that executes atof() on the value returned by ::str().

  5. If the function is nondeterministic, include the following statement in the item constructor to indicate that function results should not be cached:

    current_thd->lex->safe_to_cache_query=0;

    A function is nondeterministic if, given fixed values for its arguments, it can return different results for different invocations.

  6. You should probably also define the following object function:

    void Item_func_newname::fix_length_and_dec()

    This function should at least calculate max_length based on the given arguments. max_length is the maximum number of characters the function may return. This function should also set maybe_null = 0 if the main function can't return a NULL value. The function can check whether any of the function arguments can return NULL by checking the arguments' maybe_null variable. Look at Item_func_mod::fix_length_and_dec for a typical example of how to do this.

All functions must be thread-safe. In other words, do not use any global or static variables in the functions without protecting them with mutexes.

If you want to return NULL from ::val(), ::val_int(), or ::str(), you should set null_value to 1 and return 0.

For ::str() object functions, there are additional considerations to be aware of:

  • The String *str argument provides a string buffer that may be used to hold the result. (For more information about the String type, take a look at the sql_string.h file.)

  • The ::str() function should return the string that holds the result, or (char*) 0 if the result is NULL.

  • All current string functions try to avoid allocating any memory unless absolutely necessary!

22.4. Debugging and Porting MySQL

This appendix helps you port MySQL to other operating systems. Do check the list of currently supported operating systems first. See Section 2.1, “General Installation Guidance”. If you have created a new port of MySQL, please let us know so that we can list it here and on our Web site (http://www.mysql.com/), recommending it to other users.

Note

If you create a new port of MySQL, you are free to copy and distribute it under the GPL license, but it does not make you a copyright holder of MySQL.

A working POSIX thread library is needed for the server.

Both the server and the client need a working C++ compiler. We use gcc on many platforms. Other compilers that are known to work are Sun Studio, HP-UX aCC, IBM AIX xlC_r), Intel ecc/icc. With previous versions on the respective platforms, we also used Irix cc and Compaq cxx.

Important

If you are trying to build MySQL 5.1 with icc on the IA64 platform, and need support for MySQL Cluster, you should first ensure that you are using icc version 9.1.043 or later. (For details, see Bug #21875.)

To compile only the client, use ./configure --without-server.

If you want or need to change any Makefile or the configure script, you also need GNU Automake and Autoconf. See Section 2.11.3, “Installing MySQL from a Development Source Tree”.

All steps needed to remake everything from the most basic files.

/bin/rm */.deps/*.P
/bin/rm -f config.cache
aclocal
autoheader
aclocal
automake
autoconf
./configure --with-debug=full --prefix='your installation directory'

# The makefiles generated above need GNU make 3.75 or newer.
# (called gmake below)
gmake clean all install init-db

If you run into problems with a new port, you may have to do some debugging of MySQL! See Section 22.4.1, “Debugging a MySQL Server”.

Note

Before you start debugging mysqld, first get the test programs mysys/thr_alarm and mysys/thr_lock to work. This ensures that your thread installation has even a remote chance to work!

22.4.1. Debugging a MySQL Server

If you are using some functionality that is very new in MySQL, you can try to run mysqld with the --skip-new (which disables all new, potentially unsafe functionality). See Section C.5.4.2, “What to Do If MySQL Keeps Crashing”.

Binary distributions of MySQL server from Oracle include a specific debug binary, mysqld-debug. This is a build including the debugging information and is built in the same way as the debug build format described in Section 22.4.1.1, “Compiling MySQL for Debugging”. You should use this build when debugging the MySQL server.

If mysqld doesn't want to start, you should verify that you don't have any my.cnf files that interfere with your setup! You can check your my.cnf arguments with mysqld --print-defaults and avoid using them by starting with mysqld --no-defaults ....

If mysqld starts to eat up CPU or memory or if it hangs, you can use mysqladmin processlist status to find out if someone is executing a query that takes a long time. It may be a good idea to run mysqladmin -i10 processlist status in some window if you are experiencing performance problems or problems when new clients can't connect.

The command mysqladmin debug dumps some information about locks in use, used memory and query usage to the MySQL log file. This may help solve some problems. This command also provides some useful information even if you haven't compiled MySQL for debugging!

If the problem is that some tables are getting slower and slower you should try to optimize the table with OPTIMIZE TABLE or myisamchk. See Chapter 5, MySQL Server Administration. You should also check the slow queries with EXPLAIN.

You should also read the OS-specific section in this manual for problems that may be unique to your environment. See Section 2.1, “General Installation Guidance”.

22.4.1.1. Compiling MySQL for Debugging

If you have some very specific problem, you can always try to debug MySQL. To do this you must configure MySQL with the --with-debug or the --with-debug=full 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 you are using gcc, the recommended configure line is:

CC=gcc CFLAGS="-O2" CXX=gcc CXXFLAGS="-O2 -felide-constructors \
   -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql \
   --with-debug --with-extra-charsets=complex

This avoids problems with the libstdc++ library and with C++ exceptions (many compilers have problems with C++ exceptions in threaded code) and compile a MySQL version with support for all character sets.

If you suspect a memory overrun error, you can configure MySQL with --with-debug=full, which installs a memory allocation (SAFEMALLOC) checker. However, running with SAFEMALLOC is quite slow, so if you get performance problems you should start mysqld with the --skip-safemalloc option. This disables the memory overrun checks for each call to malloc() and free().

If mysqld stops crashing when you compile it with --with-debug, you probably have found a compiler bug or a timing bug within MySQL. In this case, you can try to add -g to the CFLAGS and CXXFLAGS variables above and not use --with-debug. 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.6.1, “MySQL Mailing Lists”. If you believe that you have found a bug, please use the instructions at Section 1.7, “How to Report Bugs or Problems”.

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

22.4.1.2. Creating 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 C:\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.7, “How to Report Bugs or Problems”.

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

22.4.1.3. Using pdb to create a Windows crashdump

Starting with MySQL 5.1.12 the Program Database files (extension pdb) are included in the Noinstall distribution 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 Dr Watson, WinDbg and 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.

Dr Watson is installed with all Windows distributions, but if you have installed Windows development tools, Dr Watson may have been replaced with WinDbg, the debugger included with Visual Studio, or the debugging tools provided with Borland or Delphi.

To generate a crash file using Dr Watson, follow these steps:

  1. Start Dr Watson by running drwtsn32.exe interactively using the -i option:

    C:\> drwtsn32 -i
    
  2. Set the Log File Path to the directory where you want to store trace files.

  3. Make sure Dump All Thread Contexts and Append To Existing Log File.

  4. Uncheck Dump Symbol Table, Visual Notification, Sound Notification and Create Crash Dump File.

  5. Set the Number of Instructions to a suitable value to capture enough calls in the stacktrace. A value of at 25 should be enough.

Note that the file generated can become very large.

22.4.1.4. Debugging mysqld under gdb

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:

  • mysqld hangs during startup (before it writes ready for connections).

  • mysqld crashes during a pthread_mutex_lock() or pthread_mutex_unlock() call.

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 C.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.7, “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.

22.4.1.5. Using a Stack Trace

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 22.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:

  1. Copy the numbers from the stack trace to a file, for example mysqld.stack. The numbers should not include the surrounding square brackets:

    0x9da402
    0x6648e9
    0x7f1a5af000f0
    0x7f1a5a10f0f2
    0x7412cb
    0x688354
    0x688494
    0x67a170
    0x67f0ad
    0x67fdf8
    0x6811b6
    0x66e05e
  2. Make a symbol file for the mysqld server:

    shell> nm -n libexec/mysqld > /tmp/mysqld.sym
    

    If mysqld is not linked statically, use the following command instead:

    shell> nm -D -n libexec/mysqld > /tmp/mysqld.sym
    

    If you want to decode C++ symbols, use the --demangle, if available, to nm. If your version of nm does not have this option, you will need to use the c++filt command after the stack dump has been produced to demangle the C++ names.

  3. Execute the following command:

    shell> resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack
    

    If you were not able to include demangled C++ names in your symbol file, process the resolve_stack_dump output using c++filt:

    shell> resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack | c++filt
    

    This prints out where mysqld died. If that does not help you find out why mysqld died, you should create a bug report and include the output from the preceding command with the bug report.

    However, in most cases it does not help us to have just a stack trace to find the reason for the problem. To be able to locate the bug or provide a workaround, in most cases we need to know the statement that killed mysqld and preferably a test case so that we can repeat the problem! See Section 1.7, “How to Report Bugs or Problems”.

22.4.1.6. Using Server Logs to Find Causes of Errors in mysqld

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, 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”.

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.

22.4.1.7. Making a Test Case If You Experience Table Corruption

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.

22.4.2. Debugging a MySQL Client

To be able to debug a MySQL client with the integrated debug package, you should configure MySQL with --with-debug or --with-debug=full. See Section 2.11.4, “MySQL Source-Configuration Options”.

Before running a client, you should set the MYSQL_DEBUG environment variable:

shell> MYSQL_DEBUG=d:t:O,/tmp/client.trace
shell> export MYSQL_DEBUG

This causes clients to generate a trace file in /tmp/client.trace.

If you have problems with your own client code, you should attempt to connect to the server and run your query using a client that is known to work. Do this by running mysql in debugging mode (assuming that you have compiled MySQL with debugging on):

shell> mysql --debug=d:t:O,/tmp/client.trace

This provides useful information in case you mail a bug report. See Section 1.7, “How to Report Bugs or Problems”.

If your client crashes at some 'legal' looking code, you should check that your mysql.h include file matches your MySQL library file. A very common mistake is to use an old mysql.h file from an old MySQL installation with new MySQL library.

22.4.3. The DBUG Package

The MySQL server and most MySQL clients are compiled with the DBUG package originally created by Fred Fish. When you have configured MySQL for debugging, this package makes it possible to get a trace file of what the program is debugging. See Section 22.4.1.2, “Creating Trace Files”.

This section summarizes the argument values that you can specify in debug options on the command line for MySQL programs that have been built with debugging support. For more information about programming with the DBUG package, see the DBUG manual in the dbug directory of MySQL source distributions. It is best to use a recent distribution to get the most updated DBUG manual.

You use the debug package by invoking a program with the --debug="..." or the -#... option.

Most MySQL programs have a default debug string that is used if you don't specify an option to --debug. The default trace file is usually /tmp/program_name.trace on Unix and \program_name.trace on Windows.

The debug control string is a sequence of colon-separated fields as follows:

<field_1>:<field_2>:...:<field_N>

Each field consists of a mandatory flag character followed by an optional , and comma-separated list of modifiers:

flag[,modifier,modifier,...,modifier]

The following table shows the currently recognized flag characters.

FlagDescription
dEnable output from DBUG_<N> macros for the current state. May be followed by a list of keywords which selects output only for the DBUG macros with that keyword. An empty list of keywords implies output for all macros.
DDelay after each debugger output line. The argument is the number of tenths of seconds to delay, subject to machine capabilities. For example, -#D,20 specifies a delay of two seconds.
fLimit debugging, tracing, and profiling to the list of named functions. Note that a null list disables all functions. The appropriate d or t flags must still be given; this flag only limits their actions if they are enabled.
FIdentify the source file name for each line of debug or trace output.
iIdentify the process with the PID or thread ID for each line of debug or trace output.
gEnable profiling. Create a file called dbugmon.out containing information that can be used to profile the program. May be followed by a list of keywords that select profiling only for the functions in that list. A null list implies that all functions are considered.
LIdentify the source file line number for each line of debug or trace output.
nPrint the current function nesting depth for each line of debug or trace output.
NNumber each line of debug output.
oRedirect the debugger output stream to the specified file. The default output is stderr.
OLike o, but the file is really flushed between each write. When needed, the file is closed and reopened between each write.
pLimit debugger actions to specified processes. A process must be identified with the DBUG_PROCESS macro and match one in the list for debugger actions to occur.
PPrint the current process name for each line of debug or trace output.
rWhen pushing a new state, do not inherit the previous state's function nesting level. Useful when the output is to start at the left margin.
SDo function _sanity(_file_,_line_) at each debugged function until _sanity() returns something that differs from 0. (Mostly used with safemalloc to find memory leaks)
tEnable function call/exit trace lines. May be followed by a list (containing only one modifier) giving a numeric maximum trace level, beyond which no output occurs for either debugging or tracing macros. The default is a compile time option.

Some examples of debug control strings that might appear on a shell command line (the -# is typically used to introduce a control string to an application program) are:

-#d:t
-#d:f,main,subr1:F:L:t,20
-#d,input,output,files:n
-#d:t:i:O,\\mysqld.trace

In MySQL, common tags to print (with the d option) are enter, exit, error, warning, info, and loop.