Chapter 26. Extending MySQL

Table of Contents

26.1. MySQL Internals
26.1.1. MySQL Threads
26.1.2. MySQL Test Suite
26.2. The MySQL Plugin Interface
26.2.1. Characteristics of the Plugin Interface
26.2.2. Full-Text Parser Plugins
26.2.3. INSTALL PLUGIN Syntax
26.2.4. UNINSTALL PLUGIN Syntax
26.2.5. Writing Plugins
26.3. Adding New Functions to MySQL
26.3.1. Features of the User-Defined Function Interface
26.3.2. CREATE FUNCTION Syntax
26.3.3. DROP FUNCTION Syntax
26.3.4. Adding a New User-Defined Function
26.3.5. Adding a New Native Function
26.4. Adding New Procedures to MySQL
26.4.1. Procedure Analyse
26.4.2. Writing a Procedure

26.1. MySQL Internals

This chapter describes a lot of things that you need to know when working on the MySQL code. If you plan to contribute to MySQL development, want to have access to the bleeding-edge versions of the code, or just want to keep track of development, follow the instructions in Section 2.9.3, “Installing from the 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.7.1, “MySQL Mailing Lists”. All developers at MySQL AB 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!

26.1.1. MySQL Threads

The MySQL server creates the following threads:

  • One thread manages TCP/IP file connection requests and creates a new dedicated thread to handle the authentication and SQL statement processing for each connection. (On Unix, this thread also manages Unix socket file connection requests.) On Windows, a similar thread manages shared-memory connection requests, and a thread manages named-pipe connection requests. Every client connection has its own thread, although the manager threads try to avoid creating threads by consulting the thread cache first to see whether a cached thread can be used for a new connection.

  • On a master replication server, slave server connections are 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.

  • The 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.5.4, “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.

  • If the event scheduler is active, there is one thread for the scheduler, and a thread for each event currently running.

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

MySQL Enterprise For expert advice on thread management subscribe to the MySQL Network Monitoring and Advisory Service. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

26.1.2. 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.

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

You can use the mysqltest language to write your own test cases. This is documented in the MySQL Test Framework manual, available at http://dev.mysql.com/doc/.

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.7.1, “MySQL Mailing Lists”. This list does not accept attachments, so you should FTP all the relevant files to: ftp://ftp.mysql.com/pub/mysql/upload/

26.2. The MySQL Plugin Interface

MySQL 5.1 and up supports a plugin API that allows the loading and unloading of server components at runtime, without restarting the server. Currently, the plugin API supports creation of full-text parser plugins. Such a plugin can be used to replace or augment the built-in full-text parser. For example, 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 intended as the successor to the older user-defined function (UDF) interface. The plugin interface eventually will include an API for creating UDFs, and it is intended this plugin UDF API will replace the older non-plugin UDF API. After that point, it will be possible for UDFs to be revised for use as plugin UDFs so that they can take advantage of the better security and versioning capabilities of the plugin API. Eventually, support for the older UDF API will be phased out.

The plugin interface requires the plugin table in the mysql database. This table is created as part of the MySQL installation process. If you are upgrading from an older version to MySQL 5.1, you should run the mysql_upgrade command to create this table. See Section 5.5.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.

26.2.1. Characteristics of the Plugin Interface

In some respects, the 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:

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

    Some aspects of the plugin API are common to all types of plugins, but the API also allows for 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.

    Although only the interface for full-text parser plugins is implemented currently, others can be added, such as an interface for UDF plugins.

  • 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, library containing a full-text parsing plugin has a general plugin API version number, and the plugin has a version number specific to the full-text plugin interface.

  • Plugin security is improved relative to the UDF interface.

    The older interface for writing non-plugin UDFs allowed libraries to be loaded from any directory searched by the system's dynamic linker, and the symbols that identified the UDF library were relatively non-specific. The newer rules are more strict. 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.

    The newer plugin interface eliminates the security issues of the older UDF interface. When a UDF plugin type is implemented, that will allow non-plugin UDFs to be brought into the plugin framework and the older interface to be phased out.

The plugin implementation includes the following components:

Source files (the locations given indicate where the files are found in a MySQL source distribution):

  • include/mysql/plugin.h exposes the public plugin API. This file should be examined by anyone who wants to write a plugin library.

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

System table:

  • The plugin table in the mysql database lists each installed plugin and is required for plugin use. For new MySQL installations, this table is created during the installation process. If you are upgrading from a version older than MySQL 5.1, you should run mysql_upgrade to update your system tables and create the plugin table (see Section 5.5.7, “mysql_upgrade — Check Tables for MySQL Upgrade”).

SQL statements:

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

  • UNINSTALL PLUGIN unregisters a plugin from the 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 known plugins. The PLUGINS table in INFORMATION_SCHEMA also contains plugin information.

System variable:

  • plugin_dir 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.

26.2.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.8, “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 non-plaintext 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
);

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.

26.2.3. INSTALL PLUGIN Syntax

INSTALL PLUGIN plugin_name SONAME 'plugin_library'

This statement installs a plugin.

plugin_name is the name of the plugin as defined in the plugin declaration structure contained in the library file. Plugin name case sensitivity is determined by the host system filename semantics.

plugin_library is the name of the shared library that contains the plugin code. The name includes the filename extension (for example, libmyplugin.so or libmyplugin.dylib).

The shared library must be located in the plugin directory (that is, the directory named by the plugin_dir system variable). The library must be in the plugin directory itself, not in a subdirectory. By default, plugin_dir is the directory named by the pkglibdir configuration variable, but it can be changed by setting the value of plugin_dir at server startup. For example, set its value in a my.cnf file:

[mysqld]
plugin_dir=/path/to/plugin/directory

If the value of plugin_dir is a relative pathname, it is taken to be relative to the MySQL base directory (the value of the basedir system variable).

INSTALL PLUGIN adds a line to the mysql.plugin table that describes the plugin. This table contains the plugin name and library filename.

INSTALL PLUGIN also loads and initializes the plugin code to make the plugin available for use. A plugin is initialized by executing its initialization function, which handles any setup that the plugin must perform before it can be used.

To use INSTALL PLUGIN, you must have the INSERT privilege for the mysql.plugin table.

At server startup, the server loads and initializes any plugin that is listed in the mysql.plugin table. This means that a plugin is installed with INSTALL PLUGIN only once, not every time the server starts. Plugin loading at startup does not occur if the server is started with the --skip-grant-tables option.

When the server shuts down, it executes the deinitialization function for each plugin that is loaded so that the plugin has a change to perform any final cleanup.

To remove a plugin entirely, use the UNINSTALL PLUGIN statement:

To see what plugins are installed, use the SHOW PLUGIN statement.

If you recompile a plugin library and need to reinstall it, you can use either of the following procedures:

  • Use UNINSTALL PLUGIN to uninstall all plugins in the library, install the new plugin library file in the plugin directory, and then use INSTALL PLUGIN to install all plugins in the library. This procedure has the advantage that it can be used without stopping the server. However, if the plugin library contains many plugins, you must issue many INSTALL PLUGIN and UNINSTALL PLUGIN statements.

  • Alternatively, stop the server, install the new plugin library file in the plugin directory, and then restart the server.

26.2.4. UNINSTALL PLUGIN Syntax

UNINSTALL PLUGIN plugin_name

This statement removes an installed plugin. You cannot uninstall a plugin if any table that uses it is open.

plugin_name must be the name of some plugin that is listed in the mysql.plugin table. The server executes the plugin's deinitialization function and removes the row for the plugin from the mysql.plugin table, so that subsequent server restarts will not load and initialize the plugin. UNINSTALL PLUGIN does not remove the plugin's shared library file.

To use UNINSTALL PLUGIN, you must have the DELETE privilege for the mysql.plugin table.

Plugin removal has implications for the use of associated tables. For example, if a full-text parser plugin is associated with a FULLTEXT index on the table, uninstalling the plugin makes the table unusable. Any attempt to access the table results in an error. The table cannot even be opened, so you cannot drop an index for which the plugin is used. This means that uninstalling a plugin is something to do with care unless you do not care about the table contents. If you are uninstalling a plugin with no intention of reinstalling it later and you care about the table contents, you should dump the table with mysqldump and remove the WITH PARSER clause from the dumped CREATE TABLE statement so that you can reload the table later. If you do not care about the table, DROP TABLE can be used even if any plugins associated with the table are missing.

26.2.5. Writing Plugins

This section describes the general and type-specific parts of the plugin API. It also provides a step-by-step guide to creating a plugin library. For example plugin source code, see the plugin/fulltext directory of a MySQL source distribution.

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 plugin contains code that becomes part of the running server, so when you write a 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 that were originally written for older servers. For information about these constraints, see Section 2.9.2, “Typical configure Options”, and Section 2.9.4, “Dealing with Problems Compiling MySQL”.

26.2.5.1. General Plugin Structures and Functions

Every plugin must have a general plugin declaration. The declaration corresponds to the st_mysql_plugin structure in the plugin.h file:

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 SHOW PLUGINS)             */
  const char *descr;    /* general descriptive text (for SHOW 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 SHOW PLUGINS)            */
  struct st_mysql_show_var *status_vars;
  void * __reserved1;   /* placeholder for system variables             */
  void * __reserved2;   /* placeholder for config options               */
};

The st_mysql_plugin structure is common to every type of plugin. Its members should be filled in as follows:

  • type

    The plugin type. This must be one of the plugin-type values from plugin.h. For a full-text parser plugin, the type value is MYSQL_FTPARSER_PLUGIN.

  • info

    A pointer to the descriptor for the plugin. Unlike the general plugin declaration structure, this descriptor's structure depends on the particular type of plugin. Each descriptor has a version number that indicates the API version for that type of plugin, plus any other members needed. The descriptor for full-text plugins is described in Section 26.2.5.2, “Type-Specific Plugin Structures and Functions”.

  • name

    The plugin name. This is the name that will be listed in the plugin table and by which you refer to the plugin in SQL statements such as INSTALL PLUGIN and UNINSTALL PLUGIN.

  • author

    The plugin author. This can be whatever you like.

  • desc

    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. This is executed when the plugin is loaded, which happens for INSTALL PLUGIN or, for plugins listed in the plugin table, at server startup. The function takes no arguments. It returns zero for success and non-zero for failure. If an init function is unneeded for a plugin, it can be specified as 0.

  • deinit

    A once-only deinitialization function. This is executed when the plugin is unloaded, which happens for UNINSTALL PLUGIN or, for plugins listed in the plugin table, at server shutdown. The function takes no arguments. It returns zero for success and non-zero for failure. If a deinit function is unneeded for a plugin, it can be specified as 0.

  • 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 0 if there are no such variables. When the plugin is installed, these variables are displayed in the output of the SHOW STATUS statement.

  • __reserved1, __reserved2

    These are placeholders for the future. Currently, they should be set to NULL.

The init and deinit functions in the general plugin declaration are invoked 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.

The status_vars member, 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 allowable status variable type values and what the corresponding variable should be:

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

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 calling sequence:

#define SHOW_VAR_FUNC_BUFF_SIZE 1024

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

Plugins should consider the thd parameter to be read-only.

26.2.5.2. Type-Specific Plugin Structures and Functions

In the st_mysql_plugin structure that defines a plugin's general declaration, the info member points to a type-specific plugin descriptor. For a full-text parser plugin, the descriptor corresponds to 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 a version number (MYSQL_FTPARSER_INTERFACE_VERSION for full-text parser plugins) and contains pointers to three functions. 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.

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

Note that the plugin declaration in the plugin library descriptor has initialization and deinitialization functions, and so does the plugin descriptor to which it points. These pairs of functions have different purposes and are invoked for different reasons:

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

  • For the 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 non-zero 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 non-zero 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 folowing 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 via 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. This should be one of values shown in the following table:

    TypeMeaning
    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.8.1, “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.

26.2.5.3. Creating a Plugin Library

This section provides a step-by-step procedure for creating a plugin library. It shows how to develop a library that contains a full-text parsing 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 non-empty runs of whitespace characters.

Each plugin library has the following contents:

  • A plugin library descriptor that indicates the version number of the general plugin API that the library uses and that contains a general declaration for each plugin in the library.

  • Each plugin general declaration contains information that is common to all types of plugin: A value that indicates the plugin type; the plugin name, author, description, and license type; and pointers to the initialization and deinitialization functions that the server invokes when it loads and unloads the plugin.

  • The plugin general declaration also contains a pointer to a type-specific plugin descriptor. The structure of these descriptors can vary from one plugin type to another, because each type of plugin can have its own API. A 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 contains the interface functions that are referenced by the library descriptor and by the plugin descriptors.

The easiest way to follow the instructions in this section is to use the source code in the plugin/fulltext directory of a MySQL source distribution. The instructions assume that you make a copy of that directory and use it to build the plugin library. To make a copy of the directory, use the following commands, which assume that the MySQL source tree is in a directory named mysql-5.1 under your current directory:

shell> mkdir fulltext_plugin
shell> cp mysql-5.1/plugin/fulltext/* fulltext_plugin

If you are copying files from a BitKeeper source tree, cp will display an error message about the SCCS directory, which you can ignore.

After copying the source files, use the following procedure to create a plugin library:

  1. Change location into the fulltext_plugin directory:

    shell> cd fulltext_plugin
    
  2. The plugin source file should 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>
    
  3. Set up the plugin library file descriptor.

    Every plugin library must include a library descriptor that 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 plugin in the library.

    If the server does not find these 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 standard (and most convenient) 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
     ... one or more plugin declarations here ...
    mysql_declare_plugin_end;
    

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

    mysql_declare_plugin
    {
      MYSQL_FTPARSER_PLUGIN,      /* type                            */
      &simple_parser_descriptor,  /* descriptor                      */
      "simple_parser",            /* name                            */
      "MySQL AB",                 /* 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                */
    }
    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.)

    The mysql_declare_plugin and mysql_declare_plugin_end macros are defined in plugin.h 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)           \
    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_[]= {
    #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}}
    

    One point to note about those definitions is that the _mysql_plugin_interface_version_ symbol is defined only if the MYSQL_DYNAMIC_PLUGIN symbol is defined. This means that you'll need to provide -DMYSQL_DYNAMIC_PLUGIN as part of the compilation command when you build the plugin.

    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;
    struct st_mysql_plugin _mysql_plugin_declarations_[]= {
    {
      MYSQL_FTPARSER_PLUGIN,      /* type                            */
      &simple_parser_descriptor,  /* descriptor                      */
      "simple_parser",            /* name                            */
      "MySQL AB",                 /* 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                */
    }
      ,{0,0,0,0,0,0,00,0}
    };
    

    The preceding example declares a single plugin in the library 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 plugins can be written in C or C++ (or another language that can use C calling conventions). One feature of C++ is that you can use non-constant variables to initialize global structures. However, if you write a C++ plugin, you should not use this feature. Members of structures such as the st_mysql_plugin structure should be initialized with constant variables. See the discussion at the end of this section that describes some legal and illegal initializers for plugins.

  4. Set up the plugin descriptor.

    Each plugin declaration in the library descriptor points to a type-specific descriptor for the corresponding plugin. 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 */
    };
    
  5. Set up the plugin interface functions.

    The general plugin declaration 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)
    {
      return(0);
    }
    
    static int simple_parser_plugin_deinit(void)
    {
      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)
    {
      return(0);
    }
    
    static int simple_parser_deinit(MYSQL_FTPARSER_PARAM *param)
    {
      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 non-empty 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 in Section 26.2.5.2, “Type-Specific Plugin Structures and Functions”.

  6. Set up the status variables, if there are any. 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                  |
    +----------------------+--------------------+
    
  7. Compile the plugin library as a shared library and install it in the plugin directory.

    Note: As mentioned earlier, be sure to specify -DMYSQL_DYNAMIC_PLUGIN as part of the compilation command when you build the plugin.

    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 mypluglib, you should end up with a shared object file that has a name something like libmypluglib.so. (The filename 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
    
    pkglibdir=$(libdir)/mysql
    INCLUDES= -I$(top_builddir)/include -I$(top_srcdir)/include
    #noinst_LTLIBRARIES= mypluglib.la
    pkglib_LTLIBRARIES= mypluglib.la
    mypluglib_la_SOURCES= plugin_example.c
    mypluglib_la_LDFLAGS= -module -rpath $(pkglibdir)
    mypluglib_la_CFLAGS= -DMYSQL_DYNAMIC_PLUGIN
    

    The mypluglib_la_CFLAGS line takes care of passing the -DMYSQL_DYNAMIC_PLUGIN flag to the compilation command.

    Adjust the INCLUDES line to specify the pathname 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 |
    +---------------+----------------------------+
    

    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 allow it to be executed by the server.

  8. Register the plugin with the server.

    The INSTALL PLUGIN statement causes the server to list the plugin in the plugin table and to load the plugin code from the library file. Use that statement to register simple_parser with the server, and then verify that the plugin is listed in the plugin table:

    mysql> INSTALL PLUGIN simple_parser SONAME 'libmypluglib.so';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT * FROM mysql.plugin;
    +---------------+-----------------+
    | name          | dl              |
    +---------------+-----------------+
    | simple_parser | libmypluglib.so |
    +---------------+-----------------+
    1 row in set (0.00 sec)
    
  9. Try the plugin.

    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);
    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 non-whitespace 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.

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

mysql_declare_plugin
{
  MYSQL_FTPARSER_PLUGIN,      /* type                            */
  &simple_parser_descriptor,  /* descriptor                      */
  "simple_parser",            /* name                            */
  "MySQL AB",                 /* 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                */
}
mysql_declare_plugin_end;

Here is another valid way to write the 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 = "MySQL AB";
const char *simple_parser_description = "Simple Full-Text Parser";

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

However, the following 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",
  "MySQL AB",
  "Simple Full-Text Parser"
};

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

26.3. Adding New Functions to MySQL

There are two ways to add new functions to MySQL:

  • You can add functions through the user-defined function (UDF) interface. User-defined functions are compiled as object files and then added to and removed from the server dynamically using the CREATE FUNCTION and DROP FUNCTION statements. See Section 26.3.2, “CREATE FUNCTION Syntax”.

  • You can add functions as native (built-in) MySQL functions. Native functions are compiled into the mysqld server and become available on a permanent basis.

Each method 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().

Another way to add functions is by creating stored functions. These are written using SQL statements rather than by compiling object code. The syntax for writing stored functions is described in Chapter 18, Stored Procedures and Functions.

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.

26.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.

  • 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 and types 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.

26.3.2. CREATE FUNCTION Syntax

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

A user-defined function (UDF) is a way to extend MySQL with a new function that works like a native (built-in) MySQL function such as ABS() or CONCAT().

function_name is the name that should be used in SQL statements to invoke the function. The RETURNS clause indicates the type of the function's return value. DECIMAL is a legal value after RETURNS, but currently DECIMAL functions return string values and should be written like STRING functions.

shared_library_name is the basename of the shared object file that contains the code that implements the function. The file must be located in the plugin directory. This directory is given by the value of the plugin_dir system variable. (Note: This 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.)

To create a function, you must have the INSERT and privilege for the mysql database. This is necessary because CREATE FUNCTION adds a row to the mysql.func system table that records the function's name, type, and shared library name. If you do not have this table, you should run the mysql_upgrade command to create it. See Section 5.5.7, “mysql_upgrade — Check Tables for MySQL Upgrade”.

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.

For instructions on writing user-defined functions, see Section 26.3.4, “Adding a New User-Defined Function”. For the UDF mechanism to work, functions must be written in C or C++ (or another language that can use C calling conventions), your operating system must support dynamic loading and you must have compiled mysqld dynamically (not statically).

An AGGREGATE function works exactly like a native MySQL aggregate (summary) function such as SUM or COUNT(). For AGGREGATE to work, your mysql.func table must contain a type column. If your mysql.func table does not have this column, you should run the mysql_upgrade program to create it (see Section 5.5.7, “mysql_upgrade — Check Tables for MySQL Upgrade”).

26.3.3. DROP FUNCTION Syntax

DROP FUNCTION function_name

This statement drops the user-defined function (UDF) named function_name.

To drop a function, you must have the DELETE privilege for the mysql database. This is because DROP FUNCTION removes a row from the mysql.func system table that records the function's name, type, and shared library name.

26.3.4. 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. The MySQL source distribution includes a file sql/udf_example.c that defines 5 new functions. Consult this file to see how UDF calling conventions work. UDF-related symbols and data structures are defined in the include/mysql_com.h header file. (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.9.2, “Typical configure Options”, and Section 2.9.4, “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.

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() (required in 5.1)

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

  • xxx_add() (required)

    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 new 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 3-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 allowed 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().

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

  • 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.

26.3.4.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 26.3.4, “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:

    char *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:

    char *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, except for the first row. You should use it to add the value in the UDF_ARGS argument to your internal summary variable.

    char *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 non-aggregate UDF. See Section 26.3.4.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 non-aggregate UDF. See Section 26.3.4.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 non-aggregate UDFs. See Section 26.3.4.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.

26.3.4.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;
    }
    
  • 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 like 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 below for instructions on how to access the value properly.) For a non-constant 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 non-constant 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 non-constant 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 allow 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]. You should not assume that strings are 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).

26.3.4.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 *result and *length to the contents and length of the return value. For example:

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

The result buffer that is passed to the xxx() function is 255 bytes long. If your result fits in this, you don't have to worry about memory allocation for results.

If your string function needs to return a string longer than 255 bytes, you must allocate the space for it 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 26.3.4.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.)

26.3.4.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 the MySQL source distribution.

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's 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 number for a hostname.

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

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 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. You need to obtain the BitKeeper source repository for MySQL 5.1. See Section 2.9.3, “Installing from the Development Source Tree”.

  2. You must obtain the CMake build utility from http://www.cmake.org. (Version 2.4.2 or later is required).

  3. In the source repository, 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 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 these statements:

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

Functions can be deleted using DROP FUNCTION:

mysql> DROP FUNCTION metaphon;
mysql> DROP FUNCTION myfunc_double;
mysql> DROP FUNCTION myfunc_int;
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 and DELETE privileges for the mysql database to create and drop functions.

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.

If the new 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.

26.3.4.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 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.)

26.3.5. Adding a New Native Function

The procedure for adding a new native function is described here. Note that you cannot add native functions to a binary distribution because the procedure involves modifying MySQL source code. You must compile MySQL yourself from a source distribution. Also note that if you migrate to another version of MySQL (for example, when a new version is released), you need to repeat the procedure with the new version.

To add a new native MySQL function, follow these steps:

  1. Add one line to lex.h that defines the function name in the sql_functions[] array.

  2. If the function prototype is simple (just takes zero, one, two or three arguments), you should in lex.h specify SYM(FUNC_ARGN) (where N is the number of arguments) as the second argument in the sql_functions[] array and add a function that creates a function object in item_create.cc. Take a look at "ABS" and create_funcs_abs() for an example of this.

    If the function prototype is complicated (for example, if it takes a variable number of arguments), you should add two lines to sql_yacc.yy. One indicates the preprocessor symbol that yacc should define (this should be added at the beginning of the file). Then define the function parameters and add an “item” with these parameters to the simple_expr parsing rule. For an example, check all occurrences of ATAN in sql_yacc.yy to see how this is done.

  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 non-deterministic, you should 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 non-deterministic 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. You can take a 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, don't 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 some 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!

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.

26.4. Adding New Procedures to MySQL

In MySQL, you can define a procedure in C++ that can access and modify the data in a query before it is sent to the client. The modification can be done on a row-by-row or GROUP BY level.

We have created an example procedure to show you what can be done.

Additionally, we recommend that you take a look at mylua. With this you can use the LUA language to load a procedure at runtime into mysqld.

26.4.1. Procedure Analyse

analyse([max_elements[,max_memory]])

This procedure is defined in the sql/sql_analyse.cc file. It examines the result from a query and returns an analysis of the results that suggests optimal data types for each column. To obtain this analysis, append PROCEDURE ANALYSE to the end of a SELECT statement:

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])

For example:

SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);

The results show some statistics for the values returned by the query, and propose an optimal data type for the columns. This can be helpful for checking your existing tables, or after importing new data. You may need to try different settings for the arguments so that PROCEDURE ANALYSE() does not suggest the ENUM data type when it is not appropriate.

The arguments are optional and are used as follows:

  • max_elements (default 256) is the maximum number of distinct values that analyse notices per column. This is used by analyse to check whether the optimal data type should be of type ENUM.

  • max_memory (default 8192) is the maximum amount of memory that analyse should allocate per column while trying to find all distinct values.

26.4.2. Writing a Procedure

For the moment, the only documentation for this is the source.

You can find all information about procedures by examining the following files:

  • sql/sql_analyse.cc

  • sql/procedure.h

  • sql/procedure.cc

  • sql/sql_select.cc