<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 22. Extending MySQL</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="prev" href="connectors-apis.html" title="Chapter 21. Connectors and APIs"><link rel="next" href="faqs.html" title="Appendix A. MySQL 5.1 Frequently Asked Questions"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 22. Extending MySQL</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="connectors-apis.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="faqs.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="extending-mysql"></a>Chapter 22. Extending MySQL</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="extending-mysql.html#mysql-internals">22.1. MySQL Internals</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#mysql-threads">22.1.1. MySQL Threads</a></span></dt><dt><span class="section"><a href="extending-mysql.html#mysql-test-suite">22.1.2. MySQL Test Suite</a></span></dt></dl></dd><dt><span class="section"><a href="extending-mysql.html#plugin-api">22.2. The MySQL Plugin Interface</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#plugin-api-characteristics">22.2.1. Characteristics of the Plugin Interface</a></span></dt><dt><span class="section"><a href="extending-mysql.html#plugin-full-text-plugins">22.2.2. Full-Text Parser Plugins</a></span></dt><dt><span class="section"><a href="extending-mysql.html#plugin-writing">22.2.3. Writing Plugins</a></span></dt></dl></dd><dt><span class="section"><a href="extending-mysql.html#adding-functions">22.3. Adding New Functions to MySQL</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#udf-features">22.3.1. Features of the User-Defined Function Interface</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-udf">22.3.2. Adding a New User-Defined Function</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-native-function">22.3.3. Adding a New Native Function</a></span></dt></dl></dd><dt><span class="section"><a href="extending-mysql.html#adding-procedures">22.4. Adding New Procedures to MySQL</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#procedure-analyse">22.4.1. <code class="literal">PROCEDURE ANALYSE</code></a></span></dt><dt><span class="section"><a href="extending-mysql.html#writing-a-procedure">22.4.2. Writing a Procedure</a></span></dt></dl></dd><dt><span class="section"><a href="extending-mysql.html#porting">22.5. Debugging and Porting MySQL</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#debugging-server">22.5.1. Debugging a MySQL Server</a></span></dt><dt><span class="section"><a href="extending-mysql.html#debugging-client">22.5.2. Debugging a MySQL Client</a></span></dt><dt><span class="section"><a href="extending-mysql.html#the-dbug-package">22.5.3. The DBUG Package</a></span></dt><dt><span class="section"><a href="extending-mysql.html#rts-threads">22.5.4. Comments about RTS Threads</a></span></dt><dt><span class="section"><a href="extending-mysql.html#thread-packages">22.5.5. Differences Between Thread Packages</a></span></dt></dl></dd></dl></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="mysql-internals"></a>22.1. MySQL Internals</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#mysql-threads">22.1.1. MySQL Threads</a></span></dt><dt><span class="section"><a href="extending-mysql.html#mysql-test-suite">22.1.2. MySQL Test Suite</a></span></dt></dl></div><a class="indexterm" name="id5046758"></a><a class="indexterm" name="id5046767"></a><p>
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 <a href="installing.html#installing-source-tree" title="2.3.3. Installing from the Development Source Tree">Section 2.3.3, “Installing from the Development Source Tree”</a>. If you
are interested in MySQL internals, you should also subscribe to
our <code class="literal">internals</code> mailing list. This list has
relatively low traffic. For details on how to subscribe, please
see <a href="introduction.html#mailing-lists" title="1.5.1. MySQL Mailing Lists">Section 1.5.1, “MySQL Mailing Lists”</a>. Many MySQL developers at Sun
Microsystems, Inc. are on the <code class="literal">internals</code> 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!
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="mysql-threads"></a>22.1.1. MySQL Threads</h3></div></div></div><p>
The MySQL server creates the following threads:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Connection manager threads handle client connection requests
on the network interfaces that the server listens to. On all
platforms, one manager thread handles TCP/IP connection
requests. On Unix, this manager thread also handles Unix
socket file connection requests. On Windows, a manager
thread handles shared-memory connection requests, and
another handles named-pipe connection requests. The server
does not create threads to handle interfaces that it does
not listen to. For example, a Windows server that does not
have support for named-pipe connections enabled does not
create a thread to handle them.
</p></li><li><p>
Connection manager threads associate each client connection
with a thread dedicated to it that handles authentication
and request processing for that connection. Manager threads
create a new thread when necessary but try to avoid doing so
by consulting the thread cache first to see whether it
contains a thread that can be used for the connection. When
a connection ends, its thread is returned to the thread
cache if the cache is not full.
</p><p>
For information about tuning the parameters that control
thread resources, see <a href="optimization.html#connection-threads" title="7.5.7. How MySQL Uses Threads for Client Connections">Section 7.5.7, “How MySQL Uses Threads for Client Connections”</a>.
</p></li><li><p>
On a master replication server, connections from slave
servers are handled like client connections: There is one
thread per connected slave.
</p></li><li><p>
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.
</p></li><li><p>
A signal thread handles all signals. This thread also
normally handles alarms and calls
<code class="literal">process_alarm()</code> to force timeouts on
connections that have been idle too long.
</p></li><li><p>
If <code class="literal">InnoDB</code> is used, there will be 4
additional threads by default. Those are file I/O threads,
controlled by the
<a href="storage-engines.html#sysvar_innodb_file_io_threads"><code class="literal">innodb_file_io_threads</code></a>
parameter. See <a href="storage-engines.html#innodb-parameters" title="13.6.3. InnoDB Startup Options and System Variables">Section 13.6.3, “<code class="literal">InnoDB</code> Startup Options and System Variables”</a>.
</p></li><li><p>
If <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> is compiled with
<code class="option">-DUSE_ALARM_THREAD</code>, a dedicated thread that
handles alarms is created. This is only used on some systems
where there are problems with <code class="literal">sigwait()</code>
or if you want to use the <code class="literal">thr_alarm()</code>
code in your application without a dedicated signal handling
thread.
</p></li><li><p>
If the server is started with the
<a href="server-administration.html#sysvar_flush_time"><code class="option">--flush_time=<em class="replaceable"><code>val</code></em></code></a>
option, a dedicated thread is created to flush all tables
every <em class="replaceable"><code>val</code></em> seconds.
</p></li><li><p>
Each table for which <a href="sql-syntax.html#insert-delayed" title="12.2.5.2. INSERT DELAYED Syntax"><code class="literal">INSERT
DELAYED</code></a> statements are issued gets its own thread.
See <a href="sql-syntax.html#insert-delayed" title="12.2.5.2. INSERT DELAYED Syntax">Section 12.2.5.2, “<code class="literal">INSERT DELAYED</code> Syntax”</a>.
</p></li><li><p>
If the event scheduler is active, there is one thread for
the scheduler, and a thread for each event currently
running. See <a href="stored-programs-views.html#events-overview" title="19.4.1. Event Scheduler Overview">Section 19.4.1, “Event Scheduler Overview”</a>.
</p></li></ul></div><p>
<a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin processlist</strong></span></a> only shows the
connection, <a href="sql-syntax.html#insert-delayed" title="12.2.5.2. INSERT DELAYED Syntax"><code class="literal">INSERT DELAYED</code></a>,
replication, and event threads.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For expert advice on thread management subscribe to the MySQL
Enterprise Monitor. For more information, see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="mysql-test-suite"></a>22.1.2. MySQL Test Suite</h3></div></div></div><a class="indexterm" name="id5047041"></a><a class="indexterm" name="id5047053"></a><p>
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.
</p><p>
You can also write your own test cases. For information about
the MySQL Test Framework, including system requirements, see the
manual available at <a href="http://dev.mysql.com/doc/" target="_top">http://dev.mysql.com/doc/</a>.
</p><p>
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.
</p><p>
The test system consists of a test language interpreter
(<span><strong class="command">mysqltest</strong></span>), a Perl script to run all tests
(<span><strong class="command">mysql-test-run.pl</strong></span>), 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
<span><strong class="command">make test</strong></span> from the source root directory, or
change location to the <code class="filename">mysql-test</code> directory
and type <span><strong class="command">./mysql-test-run.pl</strong></span>. If you have
installed a binary distribution, change location to the
<code class="filename">mysql-test</code> directory under the installation
root directory (for example,
<code class="filename">/usr/local/mysql/mysql-test</code>), and run
<span><strong class="command">./mysql-test-run.pl</strong></span>. 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
<a href="introduction.html#bug-reports" title="1.6. How to Report Bugs or Problems">Section 1.6, “How to Report Bugs or Problems”</a>.
</p><p>
If one test fails, you should run
<span><strong class="command">mysql-test-run.pl</strong></span> with the
<code class="option">--force</code> option to check whether any other tests
fail.
</p><p>
If you have a copy of <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> 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
<code class="literal">9306</code> or <code class="literal">9307</code>. If either of
those ports is taken, you should set the
<code class="literal">MTR_BUILD_THREAD</code> 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:
</p><pre class="programlisting">shell> export MTR_BUILD_THREAD=31
shell> ./mysql-test-run.pl [<em class="replaceable"><code>options</code></em>] [<em class="replaceable"><code>test_name</code></em>]
</pre><p>
In the <code class="filename">mysql-test</code> directory, you can run an
individual test case with <span><strong class="command">./mysql-test-run.pl
<em class="replaceable"><code>test_name</code></em></strong></span>.
</p><p>
If you have a question about the test suite, or have a test case
to contribute, send an email message to the MySQL
<code class="literal">internals</code> mailing list. See
<a href="introduction.html#mailing-lists" title="1.5.1. MySQL Mailing Lists">Section 1.5.1, “MySQL Mailing Lists”</a>. This list does not accept
attachments, so you should FTP all the relevant files to:
<a href="ftp://ftp.mysql.com/pub/mysql/upload/" target="_top">ftp://ftp.mysql.com/pub/mysql/upload/</a>
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="plugin-api"></a>22.2. The MySQL Plugin Interface</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#plugin-api-characteristics">22.2.1. Characteristics of the Plugin Interface</a></span></dt><dt><span class="section"><a href="extending-mysql.html#plugin-full-text-plugins">22.2.2. Full-Text Parser Plugins</a></span></dt><dt><span class="section"><a href="extending-mysql.html#plugin-writing">22.2.3. Writing Plugins</a></span></dt></dl></div><a class="indexterm" name="id5047258"></a><a class="indexterm" name="id5047267"></a><p>
MySQL 5.1 and up supports a plugin API that allows the loading and
unloading of server components at runtime, without restarting the
server. The components supported by this operation include, but
are not limited to, full-text parser plugins, storage-engines and
server extensions. In MySQL 5.1, the storage engines
can be included in the server as plugins.
</p><p>
Full-text parser plugins 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.
</p><p>
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 nonplugin 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.
</p><p>
The plugin interface requires the <code class="literal">plugin</code> table
in the <code class="literal">mysql</code> database. This table is created as
part of the MySQL installation process. If you are upgrading from
a version older than MySQL 5.1, you should run the
<a href="programs.html#mysql-upgrade" title="4.4.8. mysql_upgrade — Check Tables for MySQL Upgrade"><span><strong class="command">mysql_upgrade</strong></span></a> command to create this table. See
<a href="programs.html#mysql-upgrade" title="4.4.8. mysql_upgrade — Check Tables for MySQL Upgrade">Section 4.4.8, “<span><strong class="command">mysql_upgrade</strong></span> — Check Tables for MySQL Upgrade”</a>.
</p><p>
For more information on the Plugin API and how it can be used with
storage engines, see
<a href="http://forge.mysql.com/wiki/MySQL_Internals_Custom_Engine" target="_top">MySQL
Internals: Custom Engine</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="plugin-api-characteristics"></a>22.2.1. Characteristics of the Plugin Interface</h3></div></div></div><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The plugin framework is extendable to accommodate different
kinds of plugins.
</p><p>
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.
</p><p>
Although only the interface for full-text parser plugins is
implemented currently, others can be added, such as an
interface for UDF plugins.
</p></li><li><p>
The plugin API includes versioning information.
</p><p>
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.
</p><p>
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.
</p></li><li><p>
Plugin security is improved relative to the UDF interface.
</p><p>
The older interface for writing nonplugin 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 nonspecific. 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.
</p><p>
The newer plugin interface eliminates the security issues of
the older UDF interface. When a UDF plugin type is
implemented, that will allow nonplugin UDFs to be brought
into the plugin framework and the older interface to be
phased out.
</p></li></ul></div><p>
The plugin implementation includes the following components:
</p><p>
Source files (the locations given indicate where the files are
found in a MySQL source distribution):
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="filename">include/mysql/plugin.h</code> exposes the
public plugin API. This file should be examined by anyone
who wants to write a plugin library.
</p></li><li><p>
<code class="filename">sql/sql_plugin.h</code> and
<code class="filename">sql/sql_plugin.cc</code> 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.
</p></li></ul></div><p>
System table:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">plugin</code> table in the
<code class="literal">mysql</code> 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 <a href="programs.html#mysql-upgrade" title="4.4.8. mysql_upgrade — Check Tables for MySQL Upgrade"><span><strong class="command">mysql_upgrade</strong></span></a>
to update your system tables and create the
<code class="literal">plugin</code> table (see
<a href="programs.html#mysql-upgrade" title="4.4.8. mysql_upgrade — Check Tables for MySQL Upgrade">Section 4.4.8, “<span><strong class="command">mysql_upgrade</strong></span> — Check Tables for MySQL Upgrade”</a>).
</p></li></ul></div><p>
SQL statements:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="sql-syntax.html#install-plugin" title="12.5.3.3. INSTALL PLUGIN Syntax"><code class="literal">INSTALL PLUGIN</code></a> registers a
plugin in the <code class="literal">plugin</code> table and loads the
plugin code.
</p></li><li><p>
<a href="sql-syntax.html#uninstall-plugin" title="12.5.3.4. UNINSTALL PLUGIN Syntax"><code class="literal">UNINSTALL PLUGIN</code></a> unregisters
a plugin from the <code class="literal">plugin</code> table and
unloads the plugin code.
</p></li><li><p>
The <code class="literal">WITH PARSER</code> clause for full-text
index creation associates a full-text parser plugin with a
given <code class="literal">FULLTEXT</code> index.
</p></li><li><p>
<a href="sql-syntax.html#show-plugins" title="12.5.5.27. SHOW PLUGINS Syntax"><code class="literal">SHOW PLUGINS</code></a> displays
information about known plugins. The
<a href="information-schema.html#plugins-table" title="20.17. The INFORMATION_SCHEMA PLUGINS Table"><code class="literal">PLUGINS</code></a> table in
<code class="literal">INFORMATION_SCHEMA</code> also contains plugin
information.
</p></li></ul></div><p>
System variable:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="server-administration.html#sysvar_plugin_dir"><code class="literal">plugin_dir</code></a> 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
<a href="server-administration.html#sysvar_plugin_dir"><code class="option">--plugin_dir=<em class="replaceable"><code>path</code></em></code></a>
option.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="plugin-full-text-plugins"></a>22.2.2. Full-Text Parser Plugins</h3></div></div></div><p>
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, “<span class="quote">parsing</span>” 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.
</p><p>
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.
</p><p>
The parsing properties of the built-in full-text parser are
described in <a href="functions.html#fulltext-search" title="11.8. Full-Text Search Functions">Section 11.8, “Full-Text Search Functions”</a>. These properties
include rules for determining how to extract words from text.
The parser is influenced by certain system variables such as
<a href="server-administration.html#sysvar_ft_min_word_len"><code class="literal">ft_min_word_len</code></a> and
<a href="server-administration.html#sysvar_ft_max_word_len"><code class="literal">ft_max_word_len</code></a> that cause
words shorter or longer to be excluded, and by the stopword list
that identifies common words to be ignored.
</p><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
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).
</p><p>
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
“<span class="quote">case-sensitive</span>” to consist of two words
“<span class="quote">case</span>” and “<span class="quote">sensitive,</span>” whereas an
application might need to treat the text as a single word.
</p></li><li><p>
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 <code class="literal">ft_<em class="replaceable"><code>xxx</code></em></code>
system variables and the stopword list.
</p><p>
One reason to use a parser this way is that you need to
index content such as PDF documents, XML documents, or
<code class="filename">.doc</code> 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.
</p></li></ul></div><p>
It is also possible for a parser plugin to operate in both
roles. That is, it could extract text from nonplaintext input
(the front end role), and also parse the text into words (thus
replacing the built-in parser).
</p><p>
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
<code class="literal">WITH PARSER</code> clause when creating individual
<code class="literal">FULLTEXT</code> indexes. To create such an index at
table-creation time, do this:
</p><pre class="programlisting">CREATE TABLE t
(
doc CHAR(255),
FULLTEXT INDEX (doc) WITH PARSER my_parser
);
</pre><p>
Or you can add the index after the table has been created:
</p><pre class="programlisting">ALTER TABLE t ADD FULLTEXT INDEX (doc) WITH PARSER my_parser;
</pre><p>
The only SQL change for associating the parser with the index is
the <code class="literal">WITH PARSER</code> clause. Searches are
specified as before, with no changes needed for queries.
</p><p>
When you associate a parser plugin with a
<code class="literal">FULLTEXT</code> 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 <a href="sql-syntax.html#drop-table" title="12.1.28. DROP TABLE Syntax"><code class="literal">DROP TABLE</code></a> is still
possible.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="plugin-writing"></a>22.2.3. Writing Plugins</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#plugin-api-general">22.2.3.1. General Plugin Structures and Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#plugin-api-type-specific">22.2.3.2. Type-Specific Plugin Structures and Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#plugin-creating">22.2.3.3. Creating a Plugin Library</a></span></dt></dl></div><p>
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 <code class="filename">plugin/fulltext</code> directory of a MySQL
source distribution.
</p><p>
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 <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>
dynamically (not statically).
</p><p>
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 <code class="literal">libstdc++</code> 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 <a href="installing.html#configure-options" title="2.3.2. Typical configure Options">Section 2.3.2, “Typical <span><strong class="command">configure</strong></span> Options”</a>, and
<a href="installing.html#compilation-problems" title="2.3.4. Dealing with Problems Compiling MySQL">Section 2.3.4, “Dealing with Problems Compiling MySQL”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="plugin-api-general"></a>22.2.3.1. General Plugin Structures and Functions</h4></div></div></div><p>
Every plugin must have a general plugin declaration. The
declaration corresponds to the
<code class="literal">st_mysql_plugin</code> structure in the
<code class="filename">plugin.h</code> file:
</p><pre class="programlisting">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;
struct st_mysql_sys_var **system_vars;
void * __reserved1; /* placeholder for system variables */
};
</pre><p>
The <code class="literal">st_mysql_plugin</code> structure is common to
every type of plugin. Its members should be filled in as
follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">type</code>
</p><p>
The plugin type. This must be one of the plugin-type
values from <code class="filename">plugin.h</code>. For a full-text
parser plugin, the <code class="literal">type</code> value is
<code class="literal">MYSQL_FTPARSER_PLUGIN</code>.
</p></li><li><p>
<code class="literal">info</code>
</p><p>
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 <a href="extending-mysql.html#plugin-api-type-specific" title="22.2.3.2. Type-Specific Plugin Structures and Functions">Section 22.2.3.2, “Type-Specific Plugin Structures and Functions”</a>.
</p></li><li><p>
<code class="literal">name</code>
</p><p>
The plugin name. This is the name that will be listed in
the <code class="literal">plugin</code> table and by which you refer
to the plugin in SQL statements such as
<a href="sql-syntax.html#install-plugin" title="12.5.3.3. INSTALL PLUGIN Syntax"><code class="literal">INSTALL PLUGIN</code></a> and
<a href="sql-syntax.html#uninstall-plugin" title="12.5.3.4. UNINSTALL PLUGIN Syntax"><code class="literal">UNINSTALL PLUGIN</code></a>.
</p></li><li><p>
<code class="literal">author</code>
</p><p>
The plugin author. This can be whatever you like.
</p></li><li><p>
<code class="literal">desc</code>
</p><p>
A general description of the plugin. This can be whatever
you like.
</p></li><li><p>
<a href="server-administration.html#sysvar_license"><code class="literal">license</code></a>
</p><p>
The plugin license type. The value can be one of
<code class="literal">PLUGIN_LICENSE_PROPRIETARY</code>,
<code class="literal">PLUGIN_LICENSE_GPL</code>, or
<code class="literal">PLUGIN_LICENSE_BSD</code>.
</p></li><li><p>
<code class="literal">init</code>
</p><p>
A once-only initialization function. This is executed when
the plugin is loaded, which happens for
<a href="sql-syntax.html#install-plugin" title="12.5.3.3. INSTALL PLUGIN Syntax"><code class="literal">INSTALL PLUGIN</code></a> or, for
plugins listed in the <code class="literal">plugin</code> table, at
server startup. The function takes no arguments. It
returns zero for success and nonzero for failure. If an
<code class="literal">init</code> function is unneeded for a plugin,
it can be specified as 0.
</p></li><li><p>
<code class="literal">deinit</code>
</p><p>
A once-only deinitialization function. This is executed
when the plugin is unloaded, which happens for
<a href="sql-syntax.html#uninstall-plugin" title="12.5.3.4. UNINSTALL PLUGIN Syntax"><code class="literal">UNINSTALL PLUGIN</code></a> or, for
plugins listed in the <code class="literal">plugin</code> table, at
server shutdown. The function takes no arguments. It
returns zero for success and nonzero for failure. If a
<code class="literal">deinit</code> function is unneeded for a
plugin, it can be specified as 0.
</p></li><li><p>
<code class="literal">version</code>
</p><p>
The plugin version number. When the plugin is installed,
this value can be retrieved from the
<a href="information-schema.html#plugins-table" title="20.17. The INFORMATION_SCHEMA PLUGINS Table"><code class="literal">INFORMATION_SCHEMA.PLUGINS</code></a>
table. The value includes major and minor numbers. If you
write the value as a hex constant, the format is
<code class="literal">0x<em class="replaceable"><code>MMNN</code></em></code>,
where <em class="replaceable"><code>MM</code></em> and
<code class="literal">NN</code> are the major and minor numbers,
respectively. For example, <code class="literal">0x0302</code>
represents version 3.2.
</p></li><li><p>
<code class="literal">status_vars</code>
</p><p>
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 <a href="sql-syntax.html#show-status" title="12.5.5.37. SHOW STATUS Syntax"><code class="literal">SHOW
STATUS</code></a> statement.
</p></li><li><p>
<code class="literal">__reserved1</code>,
<code class="literal">__reserved2</code>
</p><p>
These are placeholders for the future. Currently, they
should be set to <code class="literal">NULL</code>.
</p></li></ul></div><p>
The <code class="literal">init</code> and <code class="literal">deinit</code>
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.
</p><p>
The <code class="literal">status_vars</code> member, if not 0, points to
an array of <code class="literal">st_mysql_show_var</code> structures,
each of which describes one status variable, followed by a
structure with all members set to 0. The
<code class="literal">st_mysql_show_var</code> structure has this
definition:
</p><pre class="programlisting">struct st_mysql_show_var {
const char *name;
char *value;
enum enum_mysql_show_type type;
};
</pre><p>
When the plugin is installed, the plugin name and the
<code class="literal">name</code> value are joined with an underscore to
form the name displayed by <a href="sql-syntax.html#show-status" title="12.5.5.37. SHOW STATUS Syntax"><code class="literal">SHOW
STATUS</code></a>.
</p><p>
The following table shows the allowable status variable
<code class="literal">type</code> values and what the corresponding
variable should be.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Type</strong></span></td><td><span class="bold"><strong>Meaning</strong></span></td></tr><tr><td><code class="literal">SHOW_BOOL</code></td><td>Pointer to a boolean variable</td></tr><tr><td><code class="literal">SHOW_INT</code></td><td>Pointer to an integer variable</td></tr><tr><td><code class="literal">SHOW_LONG</code></td><td>Pointer to a long integer variable</td></tr><tr><td><code class="literal">SHOW_LONGLONG</code></td><td>Pointer to a longlong integer variable</td></tr><tr><td><code class="literal">SHOW_CHAR</code></td><td>A string</td></tr><tr><td><code class="literal">SHOW_CHAR_PTR</code></td><td>Pointer to a string</td></tr><tr><td><code class="literal">SHOW_ARRAY</code></td><td>Pointer to another <code class="literal">st_mysql_show_var</code> array</td></tr><tr><td><code class="literal">SHOW_FUNC</code></td><td>Pointer to a function</td></tr></tbody></table></div><p>
For the <code class="literal">SHOW_FUNC</code> type, the function is
called and fills in its <code class="literal">out</code> parameter,
which then provides information about the variable to be
displayed. The function has this signature:
</p><pre class="programlisting">#define SHOW_VAR_FUNC_BUFF_SIZE 1024
typedef int (*mysql_show_var_func) (void *thd,
struct st_mysql_show_var *out,
char *buf);
</pre><p>
The <code class="literal">system_vars</code> member, if not 0, points to
an array of <code class="literal">st_mysql_sys_var</code> structures,
each of which describes one system variable (which can also be
set from the command-line or configuration file), followed by
a structure with all members set to 0. The
<code class="literal">st_mysql_sys_var</code> structure is defined as
follows:
</p><pre class="programlisting">struct st_mysql_sys_var {
int flags;
const char *name, *comment;
int (*check)(THD*, struct st_mysql_sys_var *, void*, st_mysql_value*);
void (*update)(THD*, struct st_mysql_sys_var *, void*, const void*);
};</pre><p>
Additional fields are append as required depending upon the
flags.
</p><p>
For convenience, a number of macros are defined that make
creating new system variables within a plugin much simpler.
</p><p>
Throughout the macros, the following fields are available:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">name</code> — is an unquoted identifier
for the system variable.
</p></li><li><p>
<code class="literal">varname</code> — is the identifier for
the static variable, where not available, it is the same
as field <code class="literal">name</code>.
</p></li><li><p>
<code class="literal">opt</code> — additional use flags for
the system variable. Supported flags are shown in the
table below:
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><thead><tr><th>Flag</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">PLUGIN_VAR_READONLY</code></td><td>Indicates that the system variable is READ ONLY.</td></tr><tr><td><code class="literal">PLUGIN_VAR_NOSYSVAR</code></td><td>Indicates that the system variable is not user visible at run time.</td></tr><tr><td><code class="literal">PLUGIN_VAR_NOCMDOPT</code></td><td>Indicates that the system variable is not configuable from the command
line.</td></tr><tr><td><code class="literal">PLUGIN_VAR_NOCMDARG</code></td><td>Indicates that no argument is required at the command line. (typically
used for boolean variables).</td></tr><tr><td><code class="literal">PLUGIN_VAR_RQCMDARG</code></td><td>Indicates that an argument is required at the command line (this is the
default).</td></tr><tr><td><code class="literal"> PLUGIN_VAR_OPCMDARG</code></td><td>Indicates that an argument is optional at the command line.</td></tr><tr><td><code class="literal">PLUGIN_VAR_MEMALLOC</code></td><td>Used for string variables. Indicates that memory is to be alloced for
storage of the string.</td></tr></tbody></table></div></li><li><p>
<code class="literal">comment</code> — descriptive comment,
appears in help text. <code class="literal">NULL</code> if this
variable is to be hidden.
</p></li><li><p>
<code class="literal">check</code> — check function,
<code class="literal">NULL</code> for default.
</p></li><li><p>
<code class="literal">update</code> — update function,
<code class="literal">NULL</code> for default.
</p></li><li><p>
<code class="literal">default</code> — the default value.
</p></li><li><p>
<code class="literal">minimum</code> — the minimum value.
</p></li><li><p>
<code class="literal">maximum</code> — the maximum value.
</p></li><li><p>
<code class="literal">blocksize</code> — the blocksize. When a
value is set, it will be rounded to the nearest
<code class="literal">blocksize</code>.
</p></li></ul></div><p>
System variables may be access by either using the static
variable directly or by using the <code class="literal">SYSVAR()
</code>accessor macro. Use of the
<code class="literal">SYSVAR()</code> macro should only usually occur
when the code cannot directly access the underlying variable
and is provided for completeness.
</p><p>
For example:
</p><pre class="programlisting"> static int my_foo;
static MYSQL_SYSVAR_INT(foo_var, my_foo,
PLUGIN_VAR_RQCMDARG, "foo comment",
NULL, NULL, 0, 0, INT_MAX, 0);
...
SYSVAR(foo_var)= value;
value= SYSVAR(foo_var);
my_foo= value;
value= my_foo;</pre><p>
Session variables may only be accessed via the THDVAR()
accessor macro. example:
</p><pre class="programlisting">static MYSQL_THDVAR_BOOL(some_flag,
PLUGIN_VAR_NOCMDARG, "flag comment",
NULL, NULL, FALSE);
...
if (THDVAR(thd, some_flag))
{
do_something();
THDVAR(thd, some_flag)= FALSE;
}
</pre><p>
All global and session variables must published to mysqld
before use. This is done by constructing a NULL terminated
array of the variables and linking to it in the plugin public
interface, for example:
</p><pre class="programlisting">static struct st_mysql_sys_var *my_plugin_vars[]= {
MYSQL_SYSVAR(my_foo),
MYSQL_SYSVAR(some_flag),
NULL
};
mysql_declare_plugin(fooplug)
{
MYSQL_..._PLUGIN,
&plugin_data,
"fooplug",
"This does foo!",
PLUGIN_LICENSE_GPL,
foo_init,
foo_fini,
0x0001,
NULL,
my_plugin_vars,
NULL
}
mysql_declare_plugin_end;</pre><p>
For convenience, you can use the following macros to define
different types of system variable:
</p><div class="itemizedlist"><ul type="disc"><li><pre class="programlisting"> MYSQL_THDVAR_BOOL(name, opt, comment, check, update, default)
MYSQL_SYSVAR_BOOL(name, varname, opt, comment, check, update, default)
</pre><p>
Used to declare a system variable of type
<code class="literal">my_bool</code>, which is a 1 byte boolean. (0
== FALSE, 1 == TRUE)
</p></li><li><pre class="programlisting"> MYSQL_THDVAR_STR(name, opt, comment, check, update, default)
MYSQL_SYSVAR_STR(name, varname, opt, comment, check, update, default)
</pre><p>
Used to declare a system variable of type
<code class="literal">char*</code>, which is a pointer to a NULL
terminated string.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_INT(name, opt, comment, check, update, default, min, max, blk)
MYSQL_SYSVAR_INT(name, varname, opt, comment, check, update, default,
minimum, maximum, blocksize)</pre><p>
Used to declare a system variable of type
<code class="literal">int</code>, which is typically a 4 byte signed
word.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_UINT(name, opt, comment, check, update, default, min, max, blk)
MYSQL_SYSVAR_UINT(name, varname, opt, comment, check, update, default,
minimum, maximum, blocksize)</pre><p>
Used to declare a system variable of type
<code class="literal">unsigned int</code>, which is typically a 4
byte unsigned word.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_LONG(name, opt, comment, check, update, default, min, max, blk)
MYSQL_SYSVAR_LONG(name, varname, opt, comment, check, update, default,
minimum, maximum, blocksize)</pre><p>
Used to declare a system variable of type
<code class="literal">long</code>, which is typically either a 4 or
8 byte signed word.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_ULONG(name, opt, comment, check, update, default, min, max, blk)
MYSQL_SYSVAR_ULONG(name, varname, opt, comment, check, update, default,
minimum, maximum, blocksize)</pre><p>
Used to declare a system variable of type
<code class="literal">unsigned long</code>, which is typically
either a 4 or 8 byte unsigned word.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_LONGLONG(name, opt, comment, check, update,
default, minimum, maximum, blocksize)
MYSQL_SYSVAR_LONGLONG(name, varname, opt, comment, check, update,
default, minimum, maximum, blocksize)</pre><p>
Used to declare a system variable of type <code class="literal">long
long</code>, which is typically an 8 byte signed word.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_ULONGLONG(name, opt, comment, check, update,
default, minimum, maximum, blocksize)
MYSQL_SYSVAR_ULONGLONG(name, varname, opt, comment, check, update,
default, minimum, maximum, blocksize)</pre><p>
Used to declare a system variable of type
<code class="literal">unsigned long long</code>, which is typically
an 8 byte unsigned word.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_ENUM(name, opt, comment, check, update, default, typelib)
MYSQL_SYSVAR_ENUM(name, varname, opt, comment, check, update,
default, typelib)</pre><p>
Used to declare a system variable of type
<code class="literal">unsigned long</code>, which is typically
either 4 or 8 byte unsigned word. The range of possible
values is an ordinal of the number of elements in the
typelib, starting from 0.
</p></li><li><pre class="programlisting">MYSQL_THDVAR_SET(name, opt, comment, check, update, default, typelib)
MYSQL_SYSVAR_SET(name, varname, opt, comment, check, update,
default, typelib)</pre><p>
Used to declare a system variable of type
<code class="literal">unsigned long long</code>, which is typically
an 8 byte unsigned word. Each bit represents an element in
the typelib.
</p></li></ul></div><p>
Internally, all mutable and plugin system variables are stored
in a <code class="literal">HASH</code>.
</p><p>
Display of the server command line verbose help text is
handled by compiling a <code class="literal">DYNAMIC_ARRAY</code> of all
variables relevent to command line options, sorting them, and
then enumerating through then to display each option.
</p><p>
When a command line option has been handled, it is then
removed from the <code class="literal">argv</code> by the
<code class="function">handle_option</code> function
(<code class="filename">my_getopt.c</code>), in effect, it is consumed.
</p><p>
The processing of command line options is performed during the
plugin installation process, immediately after the plugin has
been successfully loaded but before the plugin initialization
function has been called
</p><p>
Plugins loaded at run time do not benefit from any
configuration options and must have usuable defaults. Once
they are installed, they are loaded at
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> initialization time and so
configuration options can be set at the command line and
within <code class="filename">my.cnf</code>.
</p><p>
Plugins should consider the <code class="literal">thd</code> parameter
to be read only.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="plugin-api-type-specific"></a>22.2.3.2. Type-Specific Plugin Structures and Functions</h4></div></div></div><p>
In the <code class="literal">st_mysql_plugin</code> structure that
defines a plugin's general declaration, the
<code class="literal">info</code> member points to a type-specific
plugin descriptor. For a full-text parser plugin, the
descriptor corresponds to the
<code class="literal">st_mysql_ftparser</code> structure in the
<code class="filename">plugin.h</code> file:
</p><pre class="programlisting">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);
};
</pre><p>
As shown by the structure definition, the descriptor has a
version number
(<code class="literal">MYSQL_FTPARSER_INTERFACE_VERSION</code> for
full-text parser plugins) and contains pointers to three
functions. The <code class="literal">init</code> and
<code class="literal">deinit</code> members should point to a function
or be set to 0 if the function is not needed. The
<code class="literal">parse</code> member must point to the function
that performs the parsing.
</p><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For indexing, the server calls the parser for each column
value to be indexed.
</p></li><li><p>
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
<code class="literal">FULLTEXT</code> index, the built-in parser
will be called. (Plugins are associated with specific
indexes. If there is no index, no plugin is used.)
</p></li></ul></div><p>
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:
</p><div class="itemizedlist"><ul type="disc"><li><p>
For the plugin declaration in the plugin library
descriptor, the initialization and deinitialization
functions are invoked when the plugin is loaded and
unloaded.
</p></li><li><p>
For the plugin descriptor, the initialization and
deinitialization functions are invoked per SQL statement
for which the plugin is used.
</p></li></ul></div><p>
Each interface function named in the plugin descriptor should
return zero for success or nonzero for failure, and each of
them receives an argument that points to a
<code class="literal">MYSQL_FTPARSER_PARAM</code> structure containing
the parsing context. The structure has this definition:
</p><pre class="programlisting">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;
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
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.
</p></div><p>
The structure members are used as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">mysql_parse</code>
</p><p>
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
<code class="literal">mysql_parse</code> callback.
</p><p>
The first parameter for this callback function should be
the <code class="literal">param</code> value itself:
</p><pre class="programlisting">param->mysql_parse(param, ...);
</pre><p>
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.
</p></li><li><p>
<code class="literal">mysql_add_word</code>
</p><p>
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 <code class="literal">mysql_add_word</code> callback for each
word.
</p><p>
The first parameter for this callback function should be
the <code class="literal">param</code> value itself:
</p><pre class="programlisting">param->mysql_add_word(param, ...);
</pre></li><li><p>
<code class="literal">ftparser_state</code>
</p><p>
This is a generic pointer. The plugin can set it to point
to information to be used internally for its own purposes.
</p></li><li><p>
<code class="literal">mysql_ftparam</code>
</p><p>
This is set by the server. It is passed as the first
argument to the <code class="literal">mysql_parse</code> or
<code class="literal">mysql_add_word</code> callback.
</p></li><li><p>
<code class="literal">cs</code>
</p><p>
A pointer to information about the character set of the
text, or 0 if no information is available.
</p></li><li><p>
<code class="literal">doc</code>
</p><p>
A pointer to the text to be parsed.
</p></li><li><p>
<code class="literal">length</code>
</p><p>
The length of the text to be parsed, in bytes.
</p></li><li><p>
<code class="literal">flags</code>
</p><p>
Parser flags. This is zero if there are no special flags.
Currently, the only nonzero flag is
<code class="literal">MYSQL_FTFLAGS_NEED_COPY</code>, which means
that <code class="function">mysql_add_word()</code> 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.
</p><p>
This flag might be set or reset by MySQL before calling
the parser plugin, by the parser plugin itself, or by the
<code class="function">mysql_parse()</code> function.
</p></li><li><p>
<code class="literal">mode</code>
</p><p>
The parsing mode. This value will be one of the folowing
constants:
</p><div class="itemizedlist"><ul type="circle"><li><p>
<code class="literal">MYSQL_FTPARSER_SIMPLE_MODE</code>
</p><p>
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.
</p></li><li><p>
<code class="literal">MYSQL_FTPARSER_WITH_STOPWORDS</code>
</p><p>
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.
</p></li><li><p>
<code class="literal">MYSQL_FTPARSER_FULL_BOOLEAN_INFO</code>
</p><p>
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
<code class="literal">mysql_add_word</code> callback. To tell
the server what kind of token is being passed, the
plugin needs to fill in a
<code class="literal">MYSQL_FTPARSER_BOOLEAN_INFO</code>
structure and pass a pointer to it.
</p></li></ul></div></li></ul></div><p>
If the parser is called in boolean mode, the
<code class="literal">param->mode</code> value will be
<code class="literal">MYSQL_FTPARSER_FULL_BOOLEAN_INFO</code>. The
<code class="literal">MYSQL_FTPARSER_BOOLEAN_INFO</code> structure that
the parser uses for passing token information to the server
looks like this:
</p><pre class="programlisting">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;
</pre><p>
The parser should fill in the structure members as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">type</code>
</p><p>
The token type. This should be one of values shown in the
following table.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Type</strong></span></td><td><span class="bold"><strong>Meaning</strong></span></td></tr><tr><td><code class="literal">FT_TOKEN_EOF</code></td><td>End of data</td></tr><tr><td><code class="literal">FT_TOKEN_WORD</code></td><td>A regular word</td></tr><tr><td><code class="literal">FT_TOKEN_LEFT_PAREN</code></td><td>The beginning of a group or subexpression</td></tr><tr><td><code class="literal">FT_TOKEN_RIGHT_PAREN</code></td><td>The end of a group or subexpression</td></tr><tr><td><code class="literal">FT_TOKEN_STOPWORD</code></td><td>A stopword</td></tr></tbody></table></div></li><li><p>
<code class="literal">yesno</code>
</p><p>
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.
</p></li><li><p>
<code class="literal">weight_adjust</code>
</p><p>
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
<a href="functions.html#fulltext-boolean" title="11.8.2. Boolean Full-Text Searches">Section 11.8.2, “Boolean Full-Text Searches”</a>, that use the
<code class="literal"><</code> and <code class="literal">></code>
operators illustrate how weighting works.
</p></li><li><p>
<code class="literal">wasign</code>
</p><p>
The sign of the weighting factor. A negative value acts
like the <code class="literal">~</code> boolean-search operator,
which causes the word's contribution to the relevance to
be negative.
</p></li><li><p>
<code class="literal">trunc</code>
</p><p>
Whether matching should be done as if the boolean-mode
<code class="literal">*</code> truncation operator had been given.
</p></li></ul></div><p>
Plugins should not use the <code class="literal">prev</code> and
<code class="literal">quot</code> members of the
<code class="literal">MYSQL_FTPARSER_BOOLEAN_INFO</code> structure.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="plugin-creating"></a>22.2.3.3. Creating a Plugin Library</h4></div></div></div><p>
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
<code class="literal">simple_parser</code>. This plugin performs parsing
based on simpler rules than those used by the MySQL built-in
full-text parser: Words are nonempty runs of whitespace
characters.
</p><p>
Each plugin library has the following contents:
</p><div class="itemizedlist"><ul type="disc"><li><p>
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.
</p></li><li><p>
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.
</p></li><li><p>
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.
</p></li><li><p>
The plugin library contains the interface functions that
are referenced by the library descriptor and by the plugin
descriptors.
</p></li></ul></div><p>
The easiest way to follow the instructions in this section is
to use the source code in the
<code class="filename">plugin/fulltext</code> 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 <code class="filename">mysql-5.1</code> under your
current directory:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mkdir fulltext_plugin</code></strong>
shell> <strong class="userinput"><code>cp mysql-5.1/plugin/fulltext/* fulltext_plugin</code></strong>
</pre><p>
After copying the source files, use the following procedure to
create a plugin library:
</p><div class="orderedlist"><ol type="1"><li><p>
Change location into the
<code class="filename">fulltext_plugin</code> directory:
</p><pre class="programlisting">shell> <strong class="userinput"><code>cd fulltext_plugin</code></strong>
</pre></li><li><p>
The plugin source file should include the header files
that the plugin library needs. The
<code class="filename">plugin.h</code> file is required, and the
library might require other files as well. For example:
</p><pre class="programlisting">#include <stdlib.h>
#include <ctype.h>
#include <mysql/plugin.h>
</pre></li><li><p>
Set up the plugin library file descriptor.
</p><p>
Every plugin library must include a library descriptor
that must define two symbols:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">_mysql_plugin_interface_version_</code>
specifies the version number of the general plugin
framework. This is given by the
<code class="literal">MYSQL_PLUGIN_INTERFACE_VERSION</code>
symbol, which is defined in the
<code class="filename">plugin.h</code> file.
</p></li><li><p>
<code class="literal">_mysql_plugin_declarations_</code> defines
an array of plugin declarations, terminated by a
declaration with all members set to 0. Each
declaration is an instance of the
<code class="literal">st_mysql_plugin</code> structure (also
defined in <code class="filename">plugin.h</code>). There must
be one of these for each plugin in the library.
</p></li></ul></div><p>
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.
</p><p>
The standard (and most convenient) way to define the two
required symbols is by using the
<code class="literal">mysql_declare_plugin</code> and
<code class="literal">mysql_declare_plugin_end</code> macros from
the <code class="filename">plugin.h</code> file:
</p><pre class="programlisting">mysql_declare_plugin
<em class="replaceable"><code>... one or more plugin declarations here ...</code></em>
mysql_declare_plugin_end;
</pre><p>
For example, the library descriptor for a library that
contains a single plugin named
<code class="literal">simple_parser</code> looks like this:
</p><pre class="programlisting">mysql_declare_plugin
{
MYSQL_FTPARSER_PLUGIN, /* type */
&simple_parser_descriptor, /* descriptor */
"simple_parser", /* name */
"Sun Microsystems, Inc.", /* 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;
</pre><p>
For a full-text parser plugin, the type must be
<code class="literal">MYSQL_FTPARSER_PLUGIN</code>. This is the
value that identifies the plugin as being legal for use in
a <code class="literal">WITH PARSER</code> clause when creating a
<code class="literal">FULLTEXT</code> index. (No other plugin type
is legal for this clause.)
</p><p>
The <code class="literal">mysql_declare_plugin</code> and
<code class="literal">mysql_declare_plugin_end</code> macros are
defined in <code class="filename">plugin.h</code> like this:
</p><pre class="programlisting">#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}}
</pre><p>
One point to note about those definitions is that the
<code class="literal">_mysql_plugin_interface_version_</code> symbol
is defined only if the
<code class="literal">MYSQL_DYNAMIC_PLUGIN</code> symbol is defined.
This means that you'll need to provide
<code class="literal">-DMYSQL_DYNAMIC_PLUGIN</code> as part of the
compilation command when you build the plugin.
</p><p>
When the macros are used as just shown, they expand to the
following code, which defines both of the required symbols
(<code class="literal">_mysql_plugin_interface_version_</code> and
<code class="literal">_mysql_plugin_declarations_</code>):
</p><pre class="programlisting">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 */
"Sun Microsystems, Inc.", /* 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}
};
</pre><p>
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
<code class="literal">mysql_declare_plugin</code> and
<code class="literal">mysql_declare_plugin_end</code>, separated by
commas.
</p><p>
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 nonconstant variables to
initialize global structures. However, if you write a C++
plugin, you should not use this feature. Members of
structures such as the <code class="literal">st_mysql_plugin</code>
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.
</p></li><li><p>
Set up the plugin descriptor.
</p><p>
Each plugin declaration in the library descriptor points
to a type-specific descriptor for the corresponding
plugin. In the <code class="literal">simple_parser</code>
declaration, that descriptor is indicated by
<code class="literal">&simple_parser_descriptor</code>. The
descriptor specifies the version number for the full-text
plugin interface (as given by
<code class="literal">MYSQL_FTPARSER_INTERFACE_VERSION</code>), and
the plugin's parsing, initialization, and deinitialization
functions:
</p><pre class="programlisting">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 */
};
</pre></li><li><p>
Set up the plugin interface functions.
</p><p>
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 <code class="literal">simple_parser</code>, these
functions do nothing but return zero to indicate that they
succeeded:
</p><pre class="programlisting">static int simple_parser_plugin_init(void)
{
return(0);
}
static int simple_parser_plugin_deinit(void)
{
return(0);
}
</pre><p>
Because those functions do not actually do anything, you
could omit them and specify 0 for each of them in the
plugin declaration.
</p><p>
The type-specific plugin descriptor for
<code class="literal">simple_parser</code> names the initialization,
deinitialization, and parsing functions that the server
invokes when the plugin is used. For
<code class="literal">simple_parser</code>, the initialization and
deinitialization functions do nothing:
</p><pre class="programlisting">static int simple_parser_init(MYSQL_FTPARSER_PARAM *param)
{
return(0);
}
static int simple_parser_deinit(MYSQL_FTPARSER_PARAM *param)
{
return(0);
}
</pre><p>
Here too, because those functions do nothing, you could
omit them and specify 0 for each of them in the plugin
descriptor.
</p><p>
The main parsing function,
<code class="literal">simple_parser_parse()</code>, 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 <code class="literal">doc</code> member that points
to the text to be parsed, and a <code class="literal">length</code>
member that indicates how long the text is. The simple
parsing done by the plugin considers nonempty runs of
whitespace characters to be words, so it identifies words
like this:
</p><pre class="programlisting">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);
}
</pre><p>
As the parser finds each word, it invokes a function
<code class="literal">add_word()</code> to pass the word to the
server. <code class="literal">add_word()</code> is a helper function
only; it is not part of the plugin interface. The parser
passes the parsing context pointer to
<code class="literal">add_word()</code>, as well as a pointer to the
word and a length value:
</p><pre class="programlisting">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);
}
</pre><p>
For boolean-mode parsing, <code class="literal">add_word()</code>
fills in the members of the <code class="literal">bool_info</code>
structure as described in
<a href="extending-mysql.html#plugin-api-type-specific" title="22.2.3.2. Type-Specific Plugin Structures and Functions">Section 22.2.3.2, “Type-Specific Plugin Structures and Functions”</a>.
</p></li><li><p>
Set up the status variables, if there are any. For the
<code class="literal">simple_parser</code> 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:
</p><pre class="programlisting">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}
};
</pre><p>
When the plugin is installed, the plugin name and the
<code class="literal">name</code> value are joined with an
underscore to form the name displayed by
<a href="sql-syntax.html#show-status" title="12.5.5.37. SHOW STATUS Syntax"><code class="literal">SHOW STATUS</code></a>. For the array
just shown, the resulting status variable names are
<code class="literal">simple_parser_static</code> and
<code class="literal">simple_parser_called</code>. This convention
means that you can easily display the variables for a
plugin using its name:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW STATUS LIKE 'simple_parser%';</code></strong>
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| simple_parser_static | just a static text |
| simple_parser_called | 0 |
+----------------------+--------------------+
</pre></li><li><p>
Compile the plugin library as a shared library and install
it in the plugin directory.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
As mentioned earlier, be sure to specify
<code class="literal">-DMYSQL_DYNAMIC_PLUGIN</code> as part of the
compilation command when you build the plugin.
</p></div><p>
The procedure for compiling shared objects varies from
system to system. If you build your library using the GNU
autotools, <span><strong class="command">libtool</strong></span> should be able to
generate the correct compilation commands for your system.
If the library is named <code class="literal">mypluglib</code>, you
should end up with a shared object file that has a name
something like <code class="filename">libmypluglib.so</code>. (The
file name might have a different extension on your
system.)
</p><p>
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
<code class="filename">/usr/local/mysql</code> and that its header
files are located in the <code class="filename">include</code>
directory under the base directory.
</p><p>
Edit <code class="filename">Makefile.am</code>, which should look
something like this:
</p><pre class="programlisting">#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
</pre><p>
The <code class="literal">mypluglib_la_CFLAGS</code> line takes care
of passing the <code class="literal">-DMYSQL_DYNAMIC_PLUGIN</code>
flag to the compilation command.
</p><p>
Adjust the <code class="literal">INCLUDES</code> line to specify the
path name to the installed MySQL header files. Edit it to
look like this:
</p><pre class="programlisting">INCLUDES= -I/usr/local/mysql/include
</pre><p>
Make sure that the <code class="literal">noinst_LTLIBRARIES</code>
line is commented out or remove it. Make sure that the
<code class="literal">pkglib_LTLIBRARIES</code> line is not
commented out; it enables the <span><strong class="command">make
install</strong></span> command.
</p><p>
Set up the files needed for the
<span><strong class="command">configure</strong></span> command, invoke it, and run
<span><strong class="command">make</strong></span>:
</p><pre class="programlisting">shell> <strong class="userinput"><code>autoreconf --force --install --symlink</code></strong>
shell> <strong class="userinput"><code>./configure --prefix=/usr/local/mysql</code></strong>
shell> <strong class="userinput"><code>make</code></strong>
</pre><p>
The <a href="installing.html#option_configure_prefix"><code class="option">--prefix</code></a> option to
<span><strong class="command">configure</strong></span> indicates the MySQL base
directory under which the plugin should be installed. You
can see what value to use for this option with
<a href="sql-syntax.html#show-variables" title="12.5.5.41. SHOW VARIABLES Syntax"><code class="literal">SHOW VARIABLES</code></a>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW VARIABLES LIKE 'basedir';</code></strong>
+---------------+------------------+
| Variable_name | Value |
+---------------+------------------+
| base | /usr/local/mysql |
+---------------+------------------+
</pre><p>
The location of the plugin directory where you should
install the library is given by the
<a href="server-administration.html#sysvar_plugin_dir"><code class="literal">plugin_dir</code></a> system
variable. For example:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW VARIABLES LIKE 'plugin_dir';</code></strong>
+---------------+-----------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------+
| plugin_dir | /usr/local/mysql/lib/mysql/plugin |
+---------------+-----------------------------------+
</pre><p>
To install the plugin library, use
<span><strong class="command">make</strong></span>:
</p><pre class="programlisting">shell> <strong class="userinput"><code>make install</code></strong>
</pre><p>
Verify that <span><strong class="command">make install</strong></span> 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.
</p></li><li><p>
Register the plugin with the server.
</p><p>
The <a href="sql-syntax.html#install-plugin" title="12.5.3.3. INSTALL PLUGIN Syntax"><code class="literal">INSTALL PLUGIN</code></a>
statement causes the server to list the plugin in the
<code class="literal">plugin</code> table and to load the plugin
code from the library file. Use that statement to register
<code class="literal">simple_parser</code> with the server, and then
verify that the plugin is listed in the
<code class="literal">plugin</code> table:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSTALL PLUGIN simple_parser SONAME 'libmypluglib.so';</code></strong>
Query OK, 0 rows affected (0.00 sec)
mysql> <strong class="userinput"><code>SELECT * FROM mysql.plugin;</code></strong>
+---------------+-----------------+
| name | dl |
+---------------+-----------------+
| simple_parser | libmypluglib.so |
+---------------+-----------------+
1 row in set (0.00 sec)
</pre></li><li><p>
Try the plugin.
</p><p>
Create a table that contains a string column and associate
the parser plugin with a <code class="literal">FULLTEXT</code> index
on the column:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE TABLE t (c VARCHAR(255),</code></strong>
-> <strong class="userinput"><code> FULLTEXT (c) WITH PARSER simple_parser);</code></strong>
Query OK, 0 rows affected (0.01 sec)
</pre><p>
Insert some text into the table and try some searches.
These should verify that the parser plugin treats all
nonwhitespace characters as word characters:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>INSERT INTO t VALUES</code></strong>
-> <strong class="userinput"><code> ('latin1_general_cs is a case-sensitive collation'),</code></strong>
-> <strong class="userinput"><code> ('I\'d like a case of oranges'),</code></strong>
-> <strong class="userinput"><code> ('this is sensitive information'),</code></strong>
-> <strong class="userinput"><code> ('another row'),</code></strong>
-> <strong class="userinput"><code> ('yet another row');</code></strong>
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> <strong class="userinput"><code>SELECT c FROM t;</code></strong>
+-------------------------------------------------+
| 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> <strong class="userinput"><code>SELECT MATCH(c) AGAINST('case') FROM t;</code></strong>
+--------------------------+
| MATCH(c) AGAINST('case') |
+--------------------------+
| 0 |
| 1.2968142032623 |
| 0 |
| 0 |
| 0 |
+--------------------------+
5 rows in set (0.00 sec)
mysql> <strong class="userinput"><code>SELECT MATCH(c) AGAINST('sensitive') FROM t;</code></strong>
+-------------------------------+
| MATCH(c) AGAINST('sensitive') |
+-------------------------------+
| 0 |
| 0 |
| 1.3253291845322 |
| 0 |
| 0 |
+-------------------------------+
5 rows in set (0.01 sec)
mysql> <strong class="userinput"><code>SELECT MATCH(c) AGAINST('case-sensitive') FROM t;</code></strong>
+------------------------------------+
| MATCH(c) AGAINST('case-sensitive') |
+------------------------------------+
| 1.3109166622162 |
| 0 |
| 0 |
| 0 |
| 0 |
+------------------------------------+
5 rows in set (0.01 sec)
mysql> <strong class="userinput"><code>SELECT MATCH(c) AGAINST('I\'d') FROM t;</code></strong>
+--------------------------+
| MATCH(c) AGAINST('I\'d') |
+--------------------------+
| 0 |
| 1.2968142032623 |
| 0 |
| 0 |
| 0 |
+--------------------------+
5 rows in set (0.01 sec)
</pre></li></ol></div><p>
Note how neither “<span class="quote">case</span>” nor
“<span class="quote">insensitive</span>” match
“<span class="quote">case-insensitive</span>” the way that they would for
the built-in parser.
</p><p>
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 nonconstant variables to initialize global
structures. However, if you write a C++ plugin, you should not
use this feature. Members of structures such as the
<code class="literal">st_mysql_plugin</code> structure should be
initialized with constant variables. The
<code class="literal">simple_parser</code> descriptor shown earlier is
allowable in a C++ plugin because it satisfies that
requirement:
</p><pre class="programlisting">mysql_declare_plugin
{
MYSQL_FTPARSER_PLUGIN, /* type */
&simple_parser_descriptor, /* descriptor */
"simple_parser", /* name */
"Sun Microsystems, Inc.", /* 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;
</pre><p>
Here is another valid way to write the descriptor. It uses
constant variables to indicate the plugin name, author, and
description:
</p><pre class="programlisting">const char *simple_parser_name = "simple_parser";
const char *simple_parser_author = "Sun Microsystems, Inc.";
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;
</pre><p>
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++:
</p><pre class="programlisting">typedef struct
{
const char *name;
const char *author;
const char *description;
} plugin_info;
plugin_info parser_info = {
"simple_parser",
"Sun Microsystems, Inc.",
"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;
</pre></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="adding-functions"></a>22.3. Adding New Functions to MySQL</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#udf-features">22.3.1. Features of the User-Defined Function Interface</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-udf">22.3.2. Adding a New User-Defined Function</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-native-function">22.3.3. Adding a New Native Function</a></span></dt></dl></div><a class="indexterm" name="id5051316"></a><a class="indexterm" name="id5051328"></a><a class="indexterm" name="id5051340"></a><a class="indexterm" name="id5051353"></a><a class="indexterm" name="id5051365"></a><p>
There are three ways to add new functions to MySQL:
</p><div class="itemizedlist"><ul type="disc"><li><p>
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 <a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a> and
<a href="sql-syntax.html#drop-function" title="12.1.23. DROP FUNCTION Syntax"><code class="literal">DROP FUNCTION</code></a> statements. See
<a href="sql-syntax.html#create-function-udf" title="12.5.3.1. CREATE FUNCTION Syntax">Section 12.5.3.1, “<code class="literal">CREATE FUNCTION</code> Syntax”</a>.
</p></li><li><p>
You can add functions as native (built-in) MySQL functions.
Native functions are compiled into the
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server and become available on a
permanent basis.
</p></li><li><p>
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 not covered here. See <a href="stored-programs-views.html#stored-routines" title="19.2. Using Stored Routines (Procedures and Functions)">Section 19.2, “Using Stored Routines (Procedures and Functions)”</a>.
</p></li></ul></div><p>
Each method of creating compiled functions has advantages and
disadvantages:
</p><div class="itemizedlist"><ul type="disc"><li><p>
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.
</p></li><li><p>
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.
</p></li><li><p>
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.
</p></li></ul></div><p>
Whichever method you use to add new functions, they can be invoked
in SQL statements just like native functions such as
<a href="functions.html#function_abs"><code class="literal">ABS()</code></a> or
<a href="functions.html#function_soundex"><code class="literal">SOUNDEX()</code></a>.
</p><p>
See <a href="language-structure.html#function-resolution" title="8.2.4. Function Name Parsing and Resolution">Section 8.2.4, “Function Name Parsing and Resolution”</a>, for the rules
describing how the server interprets references to different kinds
of functions.
</p><p>
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.
</p><p>
For example source code that illustrates how to write UDFs, take a
look at the <code class="filename">sql/udf_example.c</code> file that is
provided in MySQL source distributions.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="udf-features"></a>22.3.1. Features of the User-Defined Function Interface</h3></div></div></div><p>
The MySQL interface for user-defined functions provides the
following features and capabilities:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Functions can return string, integer, or real values and can
accept arguments of those same types.
</p></li><li><p>
You can define simple functions that operate on a single row
at a time, or aggregate functions that operate on groups of
rows.
</p></li><li><p>
Information is provided to functions that enables them to
check the number, types, and names of the arguments passed
to them.
</p></li><li><p>
You can tell MySQL to coerce arguments to a given type
before passing them to a function.
</p></li><li><p>
You can indicate that a function returns
<code class="literal">NULL</code> or that an error occurred.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="adding-udf"></a>22.3.2. Adding a New User-Defined Function</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#udf-calling">22.3.2.1. UDF Calling Sequences for Simple Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-aggr-calling">22.3.2.2. UDF Calling Sequences for Aggregate Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-arguments">22.3.2.3. UDF Argument Processing</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-return-values">22.3.2.4. UDF Return Values and Error Handling</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-compiling">22.3.2.5. Compiling and Installing User-Defined Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-security">22.3.2.6. User-Defined Function Security Precautions</a></span></dt></dl></div><a class="indexterm" name="id5051593"></a><a class="indexterm" name="id5051605"></a><a class="indexterm" name="id5051617"></a><p>
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
<code class="filename">sql/udf_example.c</code> 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
<code class="filename">include/mysql_com.h</code> header file. (You need
not include this header file directly because it is included by
<code class="filename">mysql.h</code>.)
</p><p>
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
<code class="literal">libstdc++</code> 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 <a href="installing.html#configure-options" title="2.3.2. Typical configure Options">Section 2.3.2, “Typical <span><strong class="command">configure</strong></span> Options”</a>, and
<a href="installing.html#compilation-problems" title="2.3.4. Dealing with Problems Compiling MySQL">Section 2.3.4, “Dealing with Problems Compiling MySQL”</a>.
</p><p>
To be able to use UDFs, you need to link
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> dynamically. Don't configure MySQL
using <code class="option">--with-mysqld-ldflags=-all-static</code>. If you
want to use a UDF that needs to access symbols from
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> (for example, the
<code class="literal">metaphone</code> function in
<code class="filename">sql/udf_example.c</code> that uses
<code class="literal">default_charset_info</code>), you must link the
program with <code class="option">-rdynamic</code> (see <code class="literal">man
dlopen</code>). If you plan to use UDFs, the rule of thumb is
to configure MySQL with
<code class="option">--with-mysqld-ldflags=-rdynamic</code> unless you have
a very good reason not to.
</p><p>
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 “<span class="quote">xxx</span>” is used for an
example function name. To distinguish between SQL and C/C++
usage, <code class="literal">XXX()</code> (uppercase) indicates an SQL
function call, and <code class="literal">xxx()</code> (lowercase)
indicates a C/C++ function call.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
When using C++ you can encapsulate your C functions within:
</p><pre class="programlisting">extern "C" { ... }</pre><p>
This will ensure that your C++ function names remain readble
in the completed UDF.
</p></div><p>
The C/C++ functions that you write to implement the interface
for <code class="literal">XXX()</code> are:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">xxx()</code> (required)
</p><p>
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.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>SQL Type</strong></span></td><td><span class="bold"><strong>C/C++ Type</strong></span></td></tr><tr><td><code class="literal">STRING</code></td><td><code class="literal">char *</code></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INTEGER</code></a></td><td><code class="literal">long long</code></td></tr><tr><td><a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a></td><td><code class="literal">double</code></td></tr></tbody></table></div><p>
It is also possible to declare a
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> function, but
currently the value is returned as a string, so you should
write the UDF as though it were a <code class="literal">STRING</code>
function. <code class="literal">ROW</code> functions are not
implemented.
</p></li><li><p>
<code class="literal">xxx_init()</code> (optional)
</p><p>
The initialization function for <code class="literal">xxx()</code>. It
can be used for the following purposes:
</p><div class="itemizedlist"><ul type="circle"><li><p>
To check the number of arguments to
<code class="literal">XXX()</code>.
</p></li><li><p>
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.
</p></li><li><p>
To allocate any memory required by the main function.
</p></li><li><p>
To specify the maximum length of the result.
</p></li><li><p>
To specify (for <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a>
functions) the maximum number of decimal places in the
result.
</p></li><li><p>
To specify whether the result can be
<code class="literal">NULL</code>.
</p></li></ul></div></li><li><p>
<code class="literal">xxx_deinit()</code> (optional)
</p><p>
The deinitialization function for <code class="literal">xxx()</code>.
It should deallocate any memory allocated by the
initialization function.
</p></li></ul></div><p>
When an SQL statement invokes <code class="literal">XXX()</code>, MySQL
calls the initialization function <code class="literal">xxx_init()</code>
to let it perform any required setup, such as argument checking
or memory allocation. If <code class="literal">xxx_init()</code> 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
<code class="literal">xxx()</code> once for each row. After all rows have
been processed, MySQL calls the deinitialization function
<code class="literal">xxx_deinit()</code> so that it can perform any
required cleanup.
</p><p>
For aggregate functions that work like
<a href="functions.html#function_sum"><code class="literal">SUM()</code></a>, you must also provide the
following functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">xxx_clear()</code>
</p><p>
Reset the current aggregate value but do not insert the
argument as the initial aggregate value for a new group.
</p></li><li><p>
<code class="literal">xxx_add()</code>
</p><p>
Add the argument to the current aggregate value.
</p></li></ul></div><p>
MySQL handles aggregate UDFs as follows:
</p><div class="orderedlist"><ol type="1"><li><p>
Call <code class="literal">xxx_init()</code> to let the aggregate
function allocate any memory it needs for storing results.
</p></li><li><p>
Sort the table according to the <code class="literal">GROUP BY</code>
expression.
</p></li><li><p>
Call <code class="literal">xxx_clear()</code> for the first row in
each new group.
</p></li><li><p>
Call <code class="literal">xxx_add()</code> for each row that belongs
in the same group.
</p></li><li><p>
Call <code class="literal">xxx()</code> to get the result for the
aggregate when the group changes or after the last row has
been processed.
</p></li><li><p>
Repeat 3–5 until all rows has been processed
</p></li><li><p>
Call <code class="literal">xxx_deinit()</code> to let the UDF free any
memory it has allocated.
</p></li></ol></div><p>
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
<code class="literal">xxx_init()</code> and free it in
<code class="literal">xxx_deinit()</code>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-calling"></a>22.3.2.1. UDF Calling Sequences for Simple Functions</h4></div></div></div><a class="indexterm" name="id5052243"></a><p>
This section describes the different functions that you need
to define when you create a simple UDF.
<a href="extending-mysql.html#adding-udf" title="22.3.2. Adding a New User-Defined Function">Section 22.3.2, “Adding a New User-Defined Function”</a>, describes the order in which
MySQL calls these functions.
</p><p>
The main <code class="literal">xxx()</code> 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 <code class="literal">XXX()</code> to return
<code class="literal">STRING</code>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INTEGER</code></a>, or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a> in the
<a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a> statement:
</p><p>
For <code class="literal">STRING</code> functions:
</p><pre class="programlisting">char *xxx(UDF_INIT *initid, UDF_ARGS *args,
char *result, unsigned long *length,
char *is_null, char *error);
</pre><p>
For <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">INTEGER</code></a> functions:
</p><pre class="programlisting">long long xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
</pre><p>
For <a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">REAL</code></a> functions:
</p><pre class="programlisting">double xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
</pre><p>
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> functions return string
values and should be declared the same way as
<code class="literal">STRING</code> functions. <code class="literal">ROW</code>
functions are not implemented.
</p><p>
The initialization and deinitialization functions are declared
like this:
</p><pre class="programlisting">my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
void xxx_deinit(UDF_INIT *initid);
</pre><p>
The <code class="literal">initid</code> parameter is passed to all three
functions. It points to a <code class="literal">UDF_INIT</code>
structure that is used to communicate information between
functions. The <code class="literal">UDF_INIT</code> 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.)
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">my_bool maybe_null</code>
</p><p>
<code class="literal">xxx_init()</code> should set
<code class="literal">maybe_null</code> to <code class="literal">1</code> if
<code class="literal">xxx()</code> can return
<code class="literal">NULL</code>. The default value is
<code class="literal">1</code> if any of the arguments are declared
<code class="literal">maybe_null</code>.
</p></li><li><p>
<code class="literal">unsigned int decimals</code>
</p><p>
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
<code class="literal">1.34</code>, <code class="literal">1.345</code>, and
<code class="literal">1.3</code>, the default would be 3, because
<code class="literal">1.345</code> has 3 decimal digits.
</p><p>
For arguments that have no fixed number of decimals, the
<code class="literal">decimals</code> value is set to 31, which is 1
more than the maximum number of decimals allowed for the
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a>,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a>, and
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> data types.
</p><p>
A <code class="literal">decimals</code> value of 31 is used for
arguments in cases such as a
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DOUBLE</code></a> column declared
without an explicit number of decimals (for example,
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">FLOAT</code></a> rather than
<code class="literal">FLOAT(10,3)</code>) and for floating-point
constants such as <code class="literal">1345E-3</code>. It is also
used for string and other nonnumber arguments that might
be converted within the function to numeric form.
</p><p>
The value to which the <code class="literal">decimals</code> member
is initialized is only a default. It can be changed within
the function to reflect the actual calculation performed.
The default is determined such that the largest number of
decimals of the arguments is used. If the number of
decimals is 31 for even one of the arguments, that is the
value used for <code class="literal">decimals</code>.
</p></li><li><p>
<code class="literal">unsigned int max_length</code>
</p><p>
The maximum length of the result. The default
<code class="literal">max_length</code> 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 <code class="literal">initid->decimals</code>. (For
numeric functions, the length includes any sign or decimal
point characters.)
</p><p>
If you want to return a blob value, you can set
<code class="literal">max_length</code> 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.
</p></li><li><p>
<code class="literal">char *ptr</code>
</p><p>
A pointer that the function can use for its own purposes.
For example, functions can use
<code class="literal">initid->ptr</code> to communicate allocated
memory among themselves. <code class="literal">xxx_init()</code>
should allocate the memory and assign it to this pointer:
</p><pre class="programlisting">initid->ptr = allocated_memory;
</pre><p>
In <code class="literal">xxx()</code> and
<code class="literal">xxx_deinit()</code>, refer to
<code class="literal">initid->ptr</code> to use or deallocate the
memory.
</p></li><li><p>
<code class="literal">my_bool const_item</code>
</p><p>
<code class="literal">xxx_init()</code> should set
<code class="literal">const_item</code> to <code class="literal">1</code> if
<code class="literal">xxx()</code> always returns the same value and
to <code class="literal">0</code> otherwise.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-aggr-calling"></a>22.3.2.2. UDF Calling Sequences for Aggregate Functions</h4></div></div></div><a class="indexterm" name="id5052772"></a><p>
This section describes the different functions that you need
to define when you create an aggregate UDF.
<a href="extending-mysql.html#adding-udf" title="22.3.2. Adding a New User-Defined Function">Section 22.3.2, “Adding a New User-Defined Function”</a>, describes the order in which
MySQL calls these functions.
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">xxx_reset()</code>
</p><p>
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
<code class="literal">UDF_ARGS</code> argument as the first value in
your internal summary value for the group. Declare
<code class="literal">xxx_reset()</code> as follows:
</p><pre class="programlisting">void xxx_reset(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
</pre><p>
<code class="literal">xxx_reset()</code> is not needed or used in
MySQL 5.1, in which the UDF interface uses
<code class="literal">xxx_clear()</code> instead. However, you can
define both <code class="literal">xxx_reset()</code> and
<code class="literal">xxx_clear()</code> if you want to have your
UDF work with older versions of the server. (If you do
include both functions, the <code class="literal">xxx_reset()</code>
function in many cases can be implemented internally by
calling <code class="literal">xxx_clear()</code> to reset all
variables, and then calling <code class="literal">xxx_add()</code>
to add the <code class="literal">UDF_ARGS</code> argument as the
first value in the group.)
</p></li><li><p>
<code class="literal">xxx_clear()</code>
</p><p>
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
<code class="literal">xxx_clear()</code> as follows:
</p><pre class="programlisting">void xxx_clear(UDF_INIT *initid, char *is_null, char *error);
</pre><p>
<code class="literal">is_null</code> is set to point to
<code class="literal">CHAR(0)</code> before calling
<code class="literal">xxx_clear()</code>.
</p><p>
If something went wrong, you can store a value in the
variable to which the <code class="literal">error</code> argument
points. <code class="literal">error</code> points to a single-byte
variable, not to a string buffer.
</p><p>
<code class="literal">xxx_clear()</code> is required by MySQL
5.1.
</p></li><li><p>
<code class="literal">xxx_add()</code>
</p><p>
This function is called for all rows that belong to the
same group. You should use it to add the value in the
<code class="literal">UDF_ARGS</code> argument to your internal
summary variable.
</p><pre class="programlisting">void xxx_add(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
</pre></li></ul></div><p>
The <code class="literal">xxx()</code> function for an aggregate UDF
should be declared the same way as for a nonaggregate UDF. See
<a href="extending-mysql.html#udf-calling" title="22.3.2.1. UDF Calling Sequences for Simple Functions">Section 22.3.2.1, “UDF Calling Sequences for Simple Functions”</a>.
</p><p>
For an aggregate UDF, MySQL calls the <code class="literal">xxx()</code>
function after all rows in the group have been processed. You
should normally never access its <code class="literal">UDF_ARGS</code>
argument here but instead return a value based on your
internal summary variables.
</p><p>
Return value handling in <code class="literal">xxx()</code> should be
done the same way as for a nonaggregate UDF. See
<a href="extending-mysql.html#udf-return-values" title="22.3.2.4. UDF Return Values and Error Handling">Section 22.3.2.4, “UDF Return Values and Error Handling”</a>.
</p><p>
The <code class="literal">xxx_reset()</code> and
<code class="literal">xxx_add()</code> functions handle their
<code class="literal">UDF_ARGS</code> argument the same way as functions
for nonaggregate UDFs. See <a href="extending-mysql.html#udf-arguments" title="22.3.2.3. UDF Argument Processing">Section 22.3.2.3, “UDF Argument Processing”</a>.
</p><p>
The pointer arguments to <code class="literal">is_null</code> and
<code class="literal">error</code> are the same for all calls to
<code class="literal">xxx_reset()</code>,
<code class="literal">xxx_clear()</code>, <code class="literal">xxx_add()</code>
and <code class="literal">xxx()</code>. You can use this to remember
that you got an error or whether the <code class="literal">xxx()</code>
function should return <code class="literal">NULL</code>. You should not
store a string into <code class="literal">*error</code>!
<code class="literal">error</code> points to a single-byte variable, not
to a string buffer.
</p><p>
<code class="literal">*is_null</code> is reset for each group (before
calling <code class="literal">xxx_clear()</code>).
<code class="literal">*error</code> is never reset.
</p><p>
If <code class="literal">*is_null</code> or <code class="literal">*error</code>
are set when <code class="literal">xxx()</code> returns, MySQL returns
<code class="literal">NULL</code> as the result for the group function.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-arguments"></a>22.3.2.3. UDF Argument Processing</h4></div></div></div><a class="indexterm" name="id5053193"></a><a class="indexterm" name="id5053202"></a><p>
The <code class="literal">args</code> parameter points to a
<code class="literal">UDF_ARGS</code> structure that has the members
listed here:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">unsigned int arg_count</code>
</p><p>
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:
</p><pre class="programlisting">if (args->arg_count != 2)
{
strcpy(message,"XXX() requires two arguments");
return 1;
}
</pre><p>
For other <code class="literal">UDF_ARGS</code> member values that
are arrays, array references are zero-based. That is,
refer to array members using index values from 0 to
<code class="literal">args->arg_count</code> – 1.
</p></li><li><p>
<code class="literal">enum Item_result *arg_type</code>
</p><p>
A pointer to an array containing the types for each
argument. The possible type values are
<code class="literal">STRING_RESULT</code>,
<code class="literal">INT_RESULT</code>,
<code class="literal">REAL_RESULT</code>, and
<code class="literal">DECIMAL_RESULT</code>.
</p><p>
To make sure that arguments are of a given type and return
an error if they are not, check the
<code class="literal">arg_type</code> array in the initialization
function. For example:
</p><pre class="programlisting">if (args->arg_type[0] != STRING_RESULT ||
args->arg_type[1] != INT_RESULT)
{
strcpy(message,"XXX() requires a string and an integer");
return 1;
}
</pre><p>
Arguments of type <code class="literal">DECIMAL_RESULT</code> are
passed as strings, so you should handle them the same way
as <code class="literal">STRING_RESULT</code> values.
</p><p>
As an alternative to requiring your function's arguments
to be of particular types, you can use the initialization
function to set the <code class="literal">arg_type</code> elements
to the types you want. This causes MySQL to coerce
arguments to those types for each call to
<code class="literal">xxx()</code>. For example, to specify that the
first two arguments should be coerced to string and
integer, respectively, do this in
<code class="literal">xxx_init()</code>:
</p><pre class="programlisting">args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;
</pre><p>
Exact-value decimal arguments such as
<code class="literal">1.3</code> or
<a href="data-types.html#numeric-types" title="10.2. Numeric Types"><code class="literal">DECIMAL</code></a> column values are
passed with a type of <code class="literal">DECIMAL_RESULT</code>.
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
<code class="literal">REAL_RESULT</code> value:
</p><pre class="programlisting">args->arg_type[2] = REAL_RESULT;
</pre></li><li><p>
<code class="literal">char **args</code>
</p><p>
<code class="literal">args->args</code> communicates information
to the initialization function about the general nature of
the arguments passed to your function. For a constant
argument <code class="literal">i</code>,
<code class="literal">args->args[i]</code> points to the argument
value. (See below for instructions on how to access the
value properly.) For a nonconstant argument,
<code class="literal">args->args[i]</code> is
<code class="literal">0</code>. A constant argument is an expression
that uses only constants, such as <code class="literal">3</code> or
<code class="literal">4*7-2</code> or
<a href="functions.html#function_sin"><code class="literal">SIN(3.14)</code></a>. A nonconstant
argument is an expression that refers to values that may
change from row to row, such as column names or functions
that are called with nonconstant arguments.
</p><p>
For each invocation of the main function,
<code class="literal">args->args</code> contains the actual
arguments that are passed for the row currently being
processed.
</p><p>
If argument <code class="literal">i</code> represents
<code class="literal">NULL</code>,
<code class="literal">args->args[i]</code> is a null pointer (0).
If the argument is not <code class="literal">NULL</code>, functions
can refer to it as follows:
</p><div class="itemizedlist"><ul type="circle"><li><p>
An argument of type <code class="literal">STRING_RESULT</code>
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
<code class="literal">args->args[i]</code> and the string
length is <code class="literal">args->lengths[i]</code>. Do
not assume that the string is null-terminated.
</p></li><li><p>
For an argument of type <code class="literal">INT_RESULT</code>,
you must cast <code class="literal">args->args[i]</code> to a
<code class="literal">long long</code> value:
</p><pre class="programlisting">long long int_val;
int_val = *((long long*) args->args[i]);
</pre></li><li><p>
For an argument of type
<code class="literal">REAL_RESULT</code>, you must cast
<code class="literal">args->args[i]</code> to a
<code class="literal">double</code> value:
</p><pre class="programlisting">double real_val;
real_val = *((double*) args->args[i]);
</pre></li><li><p>
For an argument of type
<code class="literal">DECIMAL_RESULT</code>, the value is passed
as a string and should be handled like a
<code class="literal">STRING_RESULT</code> value.
</p></li><li><p>
<code class="literal">ROW_RESULT</code> arguments are not
implemented.
</p></li></ul></div></li><li><p>
<code class="literal">unsigned long *lengths</code>
</p><p>
For the initialization function, the
<code class="literal">lengths</code> array indicates the maximum
string length for each argument. You should not change
these. For each invocation of the main function,
<code class="literal">lengths</code> contains the actual lengths of
any string arguments that are passed for the row currently
being processed. For arguments of types
<code class="literal">INT_RESULT</code> or
<code class="literal">REAL_RESULT</code>, <code class="literal">lengths</code>
still contains the maximum length of the argument (as for
the initialization function).
</p></li><li><p>
<code class="literal">char *maybe_null</code>
</p><p>
For the initialization function, the
<code class="literal">maybe_null</code> array indicates for each
argument whether the argument value might be null (0 if
no, 1 if yes).
</p></li><li><p>
<code class="literal">char **attributes</code>
</p><p>
<code class="literal">args->attributes</code> communicates
information about the names of the UDF arguments. For
argument <code class="literal">i</code>, the attribute name is
available as a string in
<code class="literal">args->attributes[i]</code> and the
attribute length is
<code class="literal">args->attribute_lengths[i]</code>. Do not
assume that the string is null-terminated.
</p><p>
By default, the name of a UDF argument is the text of the
expression used to specify the argument. For UDFs, an
argument may also have an optional <code class="literal">[AS]
<em class="replaceable"><code>alias_name</code></em></code> clause, in
which case the argument name is
<em class="replaceable"><code>alias_name</code></em>. The
<code class="literal">attributes</code> value for each argument thus
depends on whether an alias was given.
</p><p>
Suppose that a UDF <code class="literal">my_udf()</code> is invoked
as follows:
</p><pre class="programlisting">SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);
</pre><p>
In this case, the <code class="literal">attributes</code> and
<code class="literal">attribute_lengths</code> arrays will have
these values:
</p><pre class="programlisting">args->attributes[0] = "expr1"
args->attribute_lengths[0] = 5
args->attributes[1] = "alias1"
args->attribute_lengths[1] = 6
args->attributes[2] = "alias2"
args->attribute_lengths[2] = 6
</pre></li><li><p>
<code class="literal">unsigned long *attribute_lengths</code>
</p><p>
The <code class="literal">attribute_lengths</code> array indicates
the length of each argument name.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-return-values"></a>22.3.2.4. UDF Return Values and Error Handling</h4></div></div></div><a class="indexterm" name="id5053866"></a><a class="indexterm" name="id5053878"></a><a class="indexterm" name="id5053891"></a><a class="indexterm" name="id5053903"></a><p>
The initialization function should return <code class="literal">0</code>
if no error occurred and <code class="literal">1</code> otherwise. If an
error occurs, <code class="literal">xxx_init()</code> should store a
null-terminated error message in the
<code class="literal">message</code> parameter. The message is returned
to the client. The message buffer is
<code class="literal">MYSQL_ERRMSG_SIZE</code> 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.
</p><p>
The return value of the main function <code class="literal">xxx()</code>
is the function value, for <code class="literal">long long</code> and
<code class="literal">double</code> functions. A string function should
return a pointer to the result and set
<code class="literal">*length</code> to the length (in bytes) of the
return value. For example:
</p><pre class="programlisting">memcpy(result, "result string", 13);
*length = 13;
</pre><p>
MySQL passes a buffer to the <code class="literal">xxx()</code> function
via the <code class="literal">result</code> parameter. This buffer is
sufficiently long to hold 255 characters, which can be
multi-byte characters. The <code class="literal">xxx()</code> function
can store the result in this buffer if it fits, in which case
the return value should be a pointer to the buffer. If the
function stores the result in a different buffer, it should
return a pointer to that buffer.
</p><p>
If your string function does not use the supplied buffer (for
example, if it needs to return a string longer than 255
characters), you must allocate the space for your own buffer
with <code class="literal">malloc()</code> in your
<code class="literal">xxx_init()</code> function or your
<code class="literal">xxx()</code> function and free it in your
<code class="literal">xxx_deinit()</code> function. You can store the
allocated memory in the <code class="literal">ptr</code> slot in the
<code class="literal">UDF_INIT</code> structure for reuse by future
<code class="literal">xxx()</code> calls. See
<a href="extending-mysql.html#udf-calling" title="22.3.2.1. UDF Calling Sequences for Simple Functions">Section 22.3.2.1, “UDF Calling Sequences for Simple Functions”</a>.
</p><p>
To indicate a return value of <code class="literal">NULL</code> in the
main function, set <code class="literal">*is_null</code> to
<code class="literal">1</code>:
</p><pre class="programlisting">*is_null = 1;
</pre><p>
To indicate an error return in the main function, set
<code class="literal">*error</code> to <code class="literal">1</code>:
</p><pre class="programlisting">*error = 1;
</pre><p>
If <code class="literal">xxx()</code> sets <code class="literal">*error</code> to
<code class="literal">1</code> for any row, the function value is
<code class="literal">NULL</code> for the current row and for any
subsequent rows processed by the statement in which
<code class="literal">XXX()</code> was invoked.
(<code class="literal">xxx()</code> is not even called for subsequent
rows.)
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-compiling"></a>22.3.2.5. Compiling and Installing User-Defined Functions</h4></div></div></div><a class="indexterm" name="id5054163"></a><a class="indexterm" name="id5054176"></a><a class="indexterm" name="id5054188"></a><p>
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
<code class="filename">sql/udf_example.c</code> that is included in the
MySQL source distribution.
</p><p>
The immediately following instructions are for Unix.
Instructions for Windows are given later in this section.
</p><p>
The <code class="filename">udf_example.c</code> file contains the
following functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">metaphon()</code> returns a metaphon string of
the string argument. This is something like a soundex
string, but it is more tuned for English.
</p></li><li><p>
<code class="literal">myfunc_double()</code> returns the sum of the
ASCII values of the characters in its arguments, divided
by the sum of the length of its arguments.
</p></li><li><p>
<code class="literal">myfunc_int()</code> returns the sum of the
length of its arguments.
</p></li><li><p>
<code class="literal">sequence([const int])</code> returns a
sequence starting from the given number or 1 if no number
has been given.
</p></li><li><p>
<code class="literal">lookup()</code> returns the IP number for a
host name.
</p></li><li><p>
<code class="literal">reverse_lookup()</code> returns the host name
for an IP number. The function may be called either with a
single string argument of the form
<code class="literal">'xxx.xxx.xxx.xxx'</code> or with four numbers.
</p></li></ul></div><p>
A dynamically loadable file should be compiled as a sharable
object file, using a command something like this:
</p><pre class="programlisting">shell> <strong class="userinput"><code>gcc -shared -o udf_example.so udf_example.c</code></strong>
</pre><p>
If you are using <span><strong class="command">gcc</strong></span> with
<span><strong class="command">configure</strong></span> and <span><strong class="command">libtool</strong></span>
(which is how MySQL is configured), you should be able to
create <code class="filename">udf_example.so</code> with a simpler
command:
</p><pre class="programlisting">shell> <strong class="userinput"><code>make udf_example.la</code></strong>
</pre><p>
After you compile a shared object containing UDFs, you must
install it and tell MySQL about it. Compiling a shared object
from <code class="filename">udf_example.c</code> using
<span><strong class="command">gcc</strong></span> directly produces a file named
<code class="filename">udf_example.so</code>. Compiling the shared
object using <span><strong class="command">make</strong></span> produces a file named
something like <code class="filename">udf_example.so.0.0.0</code> in
the <code class="filename">.libs</code> directory (the exact name may
vary from platform to platform). Copy the shared object to the
server's plugin directory and name it
<code class="filename">udf_example.so</code>. This directory is given
by the value of the
<a href="server-administration.html#sysvar_plugin_dir"><code class="literal">plugin_dir</code></a> system variable.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
This is a change in MySQL 5.1. For earlier versions of
MySQL, the shared object can be located in any directory
that is searched by your system's dynamic linker.
</p></div><p>
On some systems, the <span><strong class="command">ldconfig</strong></span> program that
configures the dynamic linker does not recognize a shared
object unless its name begins with <code class="literal">lib</code>. In
this case you should rename a file such as
<code class="filename">udf_example.so</code> to
<code class="filename">libudf_example.so</code>.
</p><p>
On Windows, you can compile user-defined functions by using
the following procedure:
</p><div class="orderedlist"><ol type="1"><li><p>
You need to obtain the Bazaar source repository for MySQL
5.1. See
<a href="installing.html#installing-source-tree" title="2.3.3. Installing from the Development Source Tree">Section 2.3.3, “Installing from the Development Source Tree”</a>.
</p></li><li><p>
You must obtain the CMake build utility from
<a href="http://www.cmake.org" target="_top">http://www.cmake.org</a>. (Version 2.4.2 or
later is required).
</p></li><li><p>
In the source repository, look in the
<code class="filename">sql</code> directory. There are files named
<code class="filename">udf_example.def</code>
<code class="filename">udf_example.c</code> there. Copy both files
from this directory to your working directory.
</p></li><li><p>
Create a CMake <code class="filename">makefile</code>
(<code class="filename">CMakeLists.txt</code>) with these contents:
</p><pre class="programlisting">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)
</pre></li><li><p>
Create the VC project and solution files:
</p><pre class="programlisting">cmake -G "<Generator>"
</pre><p>
Invoking <span><strong class="command">cmake --help</strong></span> shows you a list
of valid Generators.
</p></li><li><p>
Create <code class="filename">udf_example.dll</code>:
</p><pre class="programlisting">devenv udf_example.sln /build Release
</pre></li></ol></div><p>
After the shared object file has been installed, notify
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> about the new functions with these
statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.dll';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.dll';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.dll';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.dll';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.dll';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION reverse_lookup</code></strong>
-> <strong class="userinput"><code>RETURNS STRING SONAME 'udf_example.dll';</code></strong>
mysql> <strong class="userinput"><code>CREATE AGGREGATE FUNCTION avgcost</code></strong>
-> <strong class="userinput"><code>RETURNS REAL SONAME 'udf_example.dll';</code></strong>
</pre><p>
Functions can be deleted using <a href="sql-syntax.html#drop-function" title="12.1.23. DROP FUNCTION Syntax"><code class="literal">DROP
FUNCTION</code></a>:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>DROP FUNCTION metaphon;</code></strong>
mysql> <strong class="userinput"><code>DROP FUNCTION myfunc_double;</code></strong>
mysql> <strong class="userinput"><code>DROP FUNCTION myfunc_int;</code></strong>
mysql> <strong class="userinput"><code>DROP FUNCTION sequence;</code></strong>
mysql> <strong class="userinput"><code>DROP FUNCTION lookup;</code></strong>
mysql> <strong class="userinput"><code>DROP FUNCTION reverse_lookup;</code></strong>
mysql> <strong class="userinput"><code>DROP FUNCTION avgcost;</code></strong>
</pre><p>
The <a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a> and
<a href="sql-syntax.html#drop-function" title="12.1.23. DROP FUNCTION Syntax"><code class="literal">DROP FUNCTION</code></a> statements update
the <code class="literal">func</code> system table in the
<code class="literal">mysql</code> database. The function's name, type
and shared library name are saved in the table. You must have
the <a href="server-administration.html#priv_insert"><code class="literal">INSERT</code></a> and
<a href="server-administration.html#priv_delete"><code class="literal">DELETE</code></a> privileges for the
<code class="literal">mysql</code> database to create and drop
functions.
</p><p>
You should not use <a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE
FUNCTION</code></a> to add a function that has previously been
created. If you need to reinstall a function, you should
remove it with <a href="sql-syntax.html#drop-function" title="12.1.23. DROP FUNCTION Syntax"><code class="literal">DROP FUNCTION</code></a>
and then reinstall it with <a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE
FUNCTION</code></a>. You would need to do this, for example, if
you recompile a new version of your function, so that
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> gets the new version. Otherwise, the
server continues to use the old version.
</p><p>
An active function is one that has been loaded with
<a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE FUNCTION</code></a> and not removed
with <a href="sql-syntax.html#drop-function" title="12.1.23. DROP FUNCTION Syntax"><code class="literal">DROP FUNCTION</code></a>. All active
functions are reloaded each time the server starts, unless you
start <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the
<a href="server-administration.html#option_mysqld_skip-grant-tables"><code class="option">--skip-grant-tables</code></a> option. In
this case, UDF initialization is skipped and UDFs are
unavailable.
</p><p>
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.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-security"></a>22.3.2.6. User-Defined Function Security Precautions</h4></div></div></div><p>
MySQL takes the following measures to prevent misuse of
user-defined functions.
</p><p>
You must have the <a href="server-administration.html#priv_insert"><code class="literal">INSERT</code></a>
privilege to be able to use <a href="sql-syntax.html#create-function" title="12.1.12. CREATE FUNCTION Syntax"><code class="literal">CREATE
FUNCTION</code></a> and the
<a href="server-administration.html#priv_delete"><code class="literal">DELETE</code></a> privilege to be able to
use <a href="sql-syntax.html#drop-function" title="12.1.23. DROP FUNCTION Syntax"><code class="literal">DROP FUNCTION</code></a>. This is
necessary because these statements add and delete rows from
the <code class="literal">mysql.func</code> table.
</p><p>
UDFs should have at least one symbol defined in addition to
the <code class="literal">xxx</code> symbol that corresponds to the main
<code class="literal">xxx()</code> function. These auxiliary symbols
correspond to the <code class="literal">xxx_init()</code>,
<code class="literal">xxx_deinit()</code>,
<code class="literal">xxx_reset()</code>,
<code class="literal">xxx_clear()</code>, and
<code class="literal">xxx_add()</code> functions.
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> also supports an
<a href="server-administration.html#option_mysqld_allow-suspicious-udfs"><code class="option">--allow-suspicious-udfs</code></a> option
that controls whether UDFs that have only an
<code class="literal">xxx</code> 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
<code class="literal">xxx</code> symbol and that cannot be recompiled to
include an auxiliary symbol, it may be necessary to specify
the <a href="server-administration.html#option_mysqld_allow-suspicious-udfs"><code class="option">--allow-suspicious-udfs</code></a>
option. Otherwise, you should avoid enabling this capability.
</p><p>
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
<a href="server-administration.html#sysvar_plugin_dir"><code class="literal">plugin_dir</code></a> system variable.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
This is a change in MySQL 5.1. For earlier versions of
MySQL, the shared object can be located in any directory
that is searched by your system's dynamic linker.
</p></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="adding-native-function"></a>22.3.3. Adding a New Native Function</h3></div></div></div><a class="indexterm" name="id5055019"></a><a class="indexterm" name="id5055031"></a><a class="indexterm" name="id5055044"></a><p>
To add a new native MySQL function, use the procedure described
here, which requires that you use a source distribution. You
cannot add native functions to a binary distribution because it
is necessary to modify MySQL source code and compile MySQL from
the modified source. If you migrate to another version of MySQL
(for example, when a new version is released), you must repeat
the procedure with the new version.
</p><p>
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.
</p><p>
To add a new native function, follow these steps to modify
source files in the <code class="filename">sql</code> directory. For
MySQL 5.1, the first two steps apply only as of 5.1.13. For
older versions, see the instructions in the corresponding
section of the MySQL 5.0 manual.
</p><div class="orderedlist"><ol type="1"><li><p>
Create a subclass for the function in
<code class="filename">item_create.cc</code>:
</p><div class="itemizedlist"><ul type="disc"><li><p>
If the function takes a fixed number of arguments,
create a subclass of
<code class="function">Create_func_arg0</code>,
<code class="function">Create_func_arg1</code>,
<code class="function">Create_func_arg2</code>, or
<code class="function">Create_func_arg3</code>, respectively,
depending on whether the function takes zero, one, two,
or three arguments. For examples, see the
<code class="function">Create_func_uuid</code>,
<code class="function">Create_func_abs</code>,
<code class="function">Create_func_pow</code>, and
<code class="function">Create_func_lpad</code> classes.
</p></li><li><p>
If the function takes a variable number of arguments,
create a subclass of
<code class="function">Create_native_func</code>. For an example,
see <code class="function">Create_func_concat</code>.
</p></li></ul></div></li><li><p>
To provide a name by which the function can be referred to
in SQL statements, register the name in
<code class="filename">item_create.cc</code> by adding a line to this
array:
</p><pre class="programlisting">static Native_func_registry func_array[]
</pre><p>
You can register several names for the same function. For
example, see the lines for <code class="literal">"LCASE"</code> and
<code class="literal">"LOWER"</code>, which are aliases for
<code class="function">Create_func_lcase</code>.
</p></li><li><p>
In <code class="filename">item_func.h</code>, declare a class
inheriting from <code class="literal">Item_num_func</code> or
<code class="literal">Item_str_func</code>, depending on whether your
function returns a number or a string.
</p></li><li><p>
In <code class="filename">item_func.cc</code>, add one of the
following declarations, depending on whether you are
defining a numeric or string function:
</p><pre class="programlisting">double Item_func_newname::val()
longlong Item_func_newname::val_int()
String *Item_func_newname::Str(String *str)
</pre><p>
If you inherit your object from any of the standard items
(like <code class="literal">Item_num_func</code>), you probably only
have to define one of these functions and let the parent
object take care of the other functions. For example, the
<code class="literal">Item_str_func</code> class defines a
<code class="literal">val()</code> function that executes
<code class="literal">atof()</code> on the value returned by
<code class="literal">::str()</code>.
</p></li><li><p>
If the function is nondeterministic, include the following
statement in the item constructor to indicate that function
results should not be cached:
</p><pre class="programlisting">current_thd->lex->safe_to_cache_query=0;
</pre><p>
A function is nondeterministic if, given fixed values for
its arguments, it can return different results for different
invocations.
</p></li><li><p>
You should probably also define the following object
function:
</p><pre class="programlisting">void Item_func_newname::fix_length_and_dec()
</pre><p>
This function should at least calculate
<code class="literal">max_length</code> based on the given arguments.
<code class="literal">max_length</code> is the maximum number of
characters the function may return. This function should
also set <code class="literal">maybe_null = 0</code> if the main
function can't return a <code class="literal">NULL</code> value. The
function can check whether any of the function arguments can
return <code class="literal">NULL</code> by checking the arguments'
<code class="literal">maybe_null</code> variable. Look at
<code class="literal">Item_func_mod::fix_length_and_dec</code> for a
typical example of how to do this.
</p></li></ol></div><p>
All functions must be thread-safe. In other words, do not use
any global or static variables in the functions without
protecting them with mutexes.
</p><p>
If you want to return <code class="literal">NULL</code> from
<code class="literal">::val()</code>, <code class="literal">::val_int()</code>, or
<code class="literal">::str()</code>, you should set
<code class="literal">null_value</code> to 1 and return 0.
</p><p>
For <code class="literal">::str()</code> object functions, there are
additional considerations to be aware of:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The <code class="literal">String *str</code> argument provides a
string buffer that may be used to hold the result. (For more
information about the <code class="literal">String</code> type, take a
look at the <code class="filename">sql_string.h</code> file.)
</p></li><li><p>
The <code class="literal">::str()</code> function should return the
string that holds the result, or <code class="literal">(char*)
0</code> if the result is <code class="literal">NULL</code>.
</p></li><li><p>
All current string functions try to avoid allocating any
memory unless absolutely necessary!
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="adding-procedures"></a>22.4. Adding New Procedures to MySQL</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#procedure-analyse">22.4.1. <code class="literal">PROCEDURE ANALYSE</code></a></span></dt><dt><span class="section"><a href="extending-mysql.html#writing-a-procedure">22.4.2. Writing a Procedure</a></span></dt></dl></div><a class="indexterm" name="id5055511"></a><a class="indexterm" name="id5055523"></a><a class="indexterm" name="id5055536"></a><p>
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 <code class="literal">GROUP
BY</code> level.
</p><p>
We have created an example procedure to show you what can be done.
</p><p>
Additionally, it is a good idea to take a look at
<code class="literal">mylua</code>. With this you can use the LUA language
to load a procedure at runtime into <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="procedure-analyse"></a>22.4.1. <code class="literal">PROCEDURE ANALYSE</code></h3></div></div></div><p>
<code class="literal">ANALYSE([<em class="replaceable"><code>max_elements</code></em>[,<em class="replaceable"><code>max_memory</code></em>]])</code>
</p><p>
<code class="literal">ANALYSE()</code> is defined in the
<code class="filename">sql/sql_analyse.cc</code> source file, which
serves as an example of how to create a procedure for use with
the <code class="literal">PROCEDURE</code> clause of
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statements.
<code class="literal">ANALYSE()</code> is built in and is available by
default; other procedures can be created using the format
demonstrated in the source file.
</p><p>
<code class="literal">ANALYSE()</code> examines the result from a query
and returns an analysis of the results that suggests optimal
data types for each column that may help reduce table sizes. To
obtain this analysis, append <code class="literal">PROCEDURE
ANALYSE</code> to the end of a
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statement:
</p><pre class="programlisting">SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([<em class="replaceable"><code>max_elements</code></em>,[<em class="replaceable"><code>max_memory</code></em>]])
</pre><p>
For example:
</p><pre class="programlisting">SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
</pre><p>
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 <code class="literal">PROCEDURE ANALYSE()</code>
does not suggest the <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> data
type when it is not appropriate.
</p><p>
The arguments are optional and are used as follows:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<em class="replaceable"><code>max_elements</code></em> (default 256) is the
maximum number of distinct values that
<code class="literal">ANALYSE()</code> notices per column. This is
used by <code class="literal">ANALYSE()</code> to check whether the
optimal data type should be of type
<a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a>; if there are more than
<em class="replaceable"><code>max_elements</code></em> distinct values,
then <a href="data-types.html#enum" title="10.4.4. The ENUM Type"><code class="literal">ENUM</code></a> is not a suggested
type.
</p></li><li><p>
<em class="replaceable"><code>max_memory</code></em> (default 8192) is the
maximum amount of memory that <code class="literal">ANALYSE()</code>
should allocate per column while trying to find all distinct
values.
</p></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="writing-a-procedure"></a>22.4.2. Writing a Procedure</h3></div></div></div><p>
For the moment, the only documentation for this is the source.
</p><p>
You can find all information about procedures by examining the
following files:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="filename">sql/sql_analyse.cc</code>
</p></li><li><p>
<code class="filename">sql/procedure.h</code>
</p></li><li><p>
<code class="filename">sql/procedure.cc</code>
</p></li><li><p>
<code class="filename">sql/sql_select.cc</code>
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="porting"></a>22.5. Debugging and Porting MySQL</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#debugging-server">22.5.1. Debugging a MySQL Server</a></span></dt><dt><span class="section"><a href="extending-mysql.html#debugging-client">22.5.2. Debugging a MySQL Client</a></span></dt><dt><span class="section"><a href="extending-mysql.html#the-dbug-package">22.5.3. The DBUG Package</a></span></dt><dt><span class="section"><a href="extending-mysql.html#rts-threads">22.5.4. Comments about RTS Threads</a></span></dt><dt><span class="section"><a href="extending-mysql.html#thread-packages">22.5.5. Differences Between Thread Packages</a></span></dt></dl></div><a class="indexterm" name="id5055886"></a><p>
This appendix helps you port MySQL to other operating systems. Do
check the list of currently supported operating systems first. See
<a href="installing.html#general-installation-issues" title="2.1. General Installation Guidance">Section 2.1, “General Installation Guidance”</a>. If you have created
a new port of MySQL, please let us know so that we can list it
here and on our Web site (<a href="http://www.mysql.com/" target="_top">http://www.mysql.com/</a>),
recommending it to other users.
</p><p>
Note: If you create a new port of MySQL, you are free to copy and
distribute it under the GPL license, but it does not make you a
copyright holder of MySQL.
</p><p>
A working POSIX thread library is needed for the server. On
Solaris 2.5 we use Sun PThreads (the native thread support in 2.4
and earlier versions is not good enough), on Linux we use
LinuxThreads by Xavier Leroy,
<code class="email"><<a href="mailto:Xavier.Leroy@inria.fr">Xavier.Leroy@inria.fr</a>></code>.
</p><p>
The hard part of porting to a new Unix variant without good native
thread support is probably to port MIT-pthreads. See
<code class="filename">mit-pthreads/README</code> and Programming POSIX
Threads (<a href="http://www.humanfactor.com/pthreads/" target="_top">http://www.humanfactor.com/pthreads/</a>).
</p><p>
Up to MySQL 4.0.2, the MySQL distribution included a patched
version of Chris Provenzano's Pthreads from MIT (see the MIT
Pthreads Web page at
<a href="http://www.mit.edu/afs/sipb/project/pthreads/" target="_top">http://www.mit.edu/afs/sipb/project/pthreads/</a> and a
programming introduction at
<a href="http://www.mit.edu:8001/people/proven/IAP_2000/" target="_top">http://www.mit.edu:8001/people/proven/IAP_2000/</a>).
These can be used for some operating systems that do not have
POSIX threads. See <a href="installing.html#mit-pthreads" title="2.3.5. MIT-pthreads Notes">Section 2.3.5, “MIT-pthreads Notes”</a>.
</p><p>
It is also possible to use another user level thread package named
FSU Pthreads (see
<a href="http://moss.csc.ncsu.edu/~mueller/pthreads/" target="_top">http://moss.csc.ncsu.edu/~mueller/pthreads/</a>). This
implementation is being used for the SCO port.
</p><p>
See the <code class="filename">thr_lock.c</code> and
<code class="filename">thr_alarm.c</code> programs in the
<code class="filename">mysys</code> directory for some tests/examples of
these problems.
</p><p>
Both the server and the client need a working C++ compiler. We use
<span><strong class="command">gcc</strong></span> on many platforms. Other compilers that are
known to work are SPARCworks, Sun Forte, Irix
<span><strong class="command">cc</strong></span>, HP-UX <span><strong class="command">aCC</strong></span>, IBM AIX
<span><strong class="command">xlC_r</strong></span>), Intel <span><strong class="command">ecc/icc</strong></span> and
Compaq <span><strong class="command">cxx</strong></span>).
</p><a class="indexterm" name="id5056034"></a><a class="indexterm" name="id5056047"></a><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
If you are trying to build MySQL 5.1 with <span><strong class="command">icc</strong></span>
on the IA64 platform, and need support for MySQL Cluster, you
should first ensure that you are using <span><strong class="command">icc</strong></span>
version 9.1.043 or later. (For details, see <a href="http://bugs.mysql.com/21875" target="_top">Bug#21875</a>.)
</p></div><p>
To compile only the client use <span><strong class="command">./configure
--without-server</strong></span>.
</p><p>
There is currently no support for only compiling the server, nor
is it likely to be added unless someone has a good reason for it.
</p><p>
If you want/need to change any <code class="filename">Makefile</code> or
the configure script you also need GNU Automake and Autoconf. See
<a href="installing.html#installing-source-tree" title="2.3.3. Installing from the Development Source Tree">Section 2.3.3, “Installing from the Development Source Tree”</a>.
</p><p>
All steps needed to remake everything from the most basic files.
</p><pre class="programlisting">/bin/rm */.deps/*.P
/bin/rm -f config.cache
aclocal
autoheader
aclocal
automake
autoconf
./configure --with-debug=full --prefix='your installation directory'
# The makefiles generated above need GNU make 3.75 or newer.
# (called gmake below)
gmake clean all install init-db
</pre><p>
If you run into problems with a new port, you may have to do some
debugging of MySQL! See <a href="extending-mysql.html#debugging-server" title="22.5.1. Debugging a MySQL Server">Section 22.5.1, “Debugging a MySQL Server”</a>.
</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Before you start debugging <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>, first get
the test programs <code class="literal">mysys/thr_alarm</code> and
<code class="literal">mysys/thr_lock</code> to work. This ensures that
your thread installation has even a remote chance to work!
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="debugging-server"></a>22.5.1. Debugging a MySQL Server</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#compiling-for-debugging">22.5.1.1. Compiling MySQL for Debugging</a></span></dt><dt><span class="section"><a href="extending-mysql.html#making-trace-files">22.5.1.2. Creating Trace Files</a></span></dt><dt><span class="section"><a href="extending-mysql.html#making-windows-dumps">22.5.1.3. Using <code class="filename">pdb</code> to create a Windows crashdump</a></span></dt><dt><span class="section"><a href="extending-mysql.html#using-gdb-on-mysqld">22.5.1.4. Debugging <span><strong class="command">mysqld</strong></span> under <span><strong class="command">gdb</strong></span></a></span></dt><dt><span class="section"><a href="extending-mysql.html#using-stack-trace">22.5.1.5. Using a Stack Trace</a></span></dt><dt><span class="section"><a href="extending-mysql.html#using-log-files">22.5.1.6. Using Server Logs to Find Causes of Errors in <span><strong class="command">mysqld</strong></span></a></span></dt><dt><span class="section"><a href="extending-mysql.html#reproducible-test-case">22.5.1.7. Making a Test Case If You Experience Table Corruption</a></span></dt></dl></div><a class="indexterm" name="id5056169"></a><a class="indexterm" name="id5056181"></a><a class="indexterm" name="id5056194"></a><p>
If you are using some functionality that is very new in MySQL,
you can try to run <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the
<code class="option">--skip-new</code> (which disables all new, potentially
unsafe functionality) or with
<a href="server-administration.html#option_mysqld_safe-mode"><code class="option">--safe-mode</code></a> which disables a lot
of optimization that may cause problems. See
<a href="error-handling.html#crashing" title="B.1.4.2. What to Do If MySQL Keeps Crashing">Section B.1.4.2, “What to Do If MySQL Keeps Crashing”</a>.
</p><p>
If <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> doesn't want to start, you should
verify that you don't have any <code class="filename">my.cnf</code> files
that interfere with your setup! You can check your
<code class="filename">my.cnf</code> arguments with <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld
--print-defaults</strong></span></a> and avoid using them by starting with
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld --no-defaults ...</strong></span></a>.
</p><p>
If <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> starts to eat up CPU or memory or
if it “<span class="quote">hangs,</span>” you can use <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin
processlist status</strong></span></a> to find out if someone is executing
a query that takes a long time. It may be a good idea to run
<a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin -i10 processlist status</strong></span></a> in some
window if you are experiencing performance problems or problems
when new clients can't connect.
</p><p>
The command <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin debug</strong></span></a> dumps some
information about locks in use, used memory and query usage to
the MySQL log file. This may help solve some problems. This
command also provides some useful information even if you
haven't compiled MySQL for debugging!
</p><p>
If the problem is that some tables are getting slower and slower
you should try to optimize the table with
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> or
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>. See
<a href="server-administration.html" title="Chapter 5. MySQL Server Administration">Chapter 5, <i>MySQL Server Administration</i></a>. You should also check
the slow queries with <a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN</code></a>.
</p><p>
You should also read the OS-specific section in this manual for
problems that may be unique to your environment. See
<a href="installing.html#general-installation-issues" title="2.1. General Installation Guidance">Section 2.1, “General Installation Guidance”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="compiling-for-debugging"></a>22.5.1.1. Compiling MySQL for Debugging</h4></div></div></div><p>
If you have some very specific problem, you can always try to
debug MySQL. To do this you must configure MySQL with the
<a href="installing.html#option_configure_with-debug"><code class="option">--with-debug</code></a> or the
<a href="installing.html#option_configure_with-debug"><code class="option">--with-debug=full</code></a> option.
You can check whether MySQL was compiled with debugging by
doing: <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld --help</strong></span></a>. If the
<a href="server-administration.html#option_mysqld_debug"><code class="option">--debug</code></a> flag is listed with the
options then you have debugging enabled. <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin
ver</strong></span></a> also lists the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> version
as <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql ... --debug</strong></span></a> in this case.
</p><p>
If you are using <span><strong class="command">gcc</strong></span>, the recommended
<span><strong class="command">configure</strong></span> line is:
</p><pre class="programlisting">CC=gcc CFLAGS="-O2" CXX=gcc CXXFLAGS="-O2 -felide-constructors \
-fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql \
--with-debug --with-extra-charsets=complex
</pre><p>
This avoids problems with the <code class="literal">libstdc++</code>
library and with C++ exceptions (many compilers have problems
with C++ exceptions in threaded code) and compile a MySQL
version with support for all character sets.
</p><p>
If you suspect a memory overrun error, you can configure MySQL
with <a href="installing.html#option_configure_with-debug"><code class="option">--with-debug=full</code></a>,
which installs a memory allocation
(<code class="literal">SAFEMALLOC</code>) checker. However, running with
<code class="literal">SAFEMALLOC</code> is quite slow, so if you get
performance problems you should start
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the
<a href="server-administration.html#option_mysqld_skip-safemalloc"><code class="option">--skip-safemalloc</code></a> option. This
disables the memory overrun checks for each call to
<code class="literal">malloc()</code> and <code class="literal">free()</code>.
</p><p>
If <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> stops crashing when you compile
it with <a href="installing.html#option_configure_with-debug"><code class="option">--with-debug</code></a>, you
probably have found a compiler bug or a timing bug within
MySQL. In this case, you can try to add <code class="option">-g</code> to
the <code class="literal">CFLAGS</code> and <code class="literal">CXXFLAGS</code>
variables above and not use
<a href="installing.html#option_configure_with-debug"><code class="option">--with-debug</code></a>. If
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> dies, you can at least attach to it
with <span><strong class="command">gdb</strong></span> or use <span><strong class="command">gdb</strong></span> on
the core file to find out what happened.
</p><p>
When you configure MySQL for debugging you automatically
enable a lot of extra safety check functions that monitor the
health of <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>. If they find something
“<span class="quote">unexpected,</span>” an entry is written to
<code class="literal">stderr</code>, which
<a href="programs.html#mysqld-safe" title="4.3.2. mysqld_safe — MySQL Server Startup Script"><span><strong class="command">mysqld_safe</strong></span></a> directs to the error log! This
also means that if you are having some unexpected problems
with MySQL and are using a source distribution, the first
thing you should do is to configure MySQL for debugging! (The
second thing is to send mail to a MySQL mailing list and ask
for help. See <a href="introduction.html#mailing-lists" title="1.5.1. MySQL Mailing Lists">Section 1.5.1, “MySQL Mailing Lists”</a>. If you believe
that you have found a bug, please use the instructions at
<a href="introduction.html#bug-reports" title="1.6. How to Report Bugs or Problems">Section 1.6, “How to Report Bugs or Problems”</a>.
</p><p>
In the Windows MySQL distribution,
<code class="literal">mysqld.exe</code> is by default compiled with
support for trace files.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="making-trace-files"></a>22.5.1.2. Creating Trace Files</h4></div></div></div><p>
If the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server doesn't start or if
you can cause it to crash quickly, you can try to create a
trace file to find the problem.
</p><p>
To do this, you must have a <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> that has
been compiled with debugging support. You can check this by
executing <code class="literal">mysqld -V</code>. If the version number
ends with <code class="literal">-debug</code>, it is compiled with
support for trace files. (On Windows, the debugging server is
named <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld-debug</strong></span></a> rather than
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> as of MySQL 4.1.)
</p><p>
Start the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server with a trace log in
<code class="filename">/tmp/mysqld.trace</code> on Unix or
<code class="filename">C:\mysqld.trace</code> on Windows:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqld --debug</code></strong>
</pre><p>
On Windows, you should also use the
<a href="server-administration.html#option_mysqld_standalone"><code class="option">--standalone</code></a> flag to not start
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> as a service. In a console window,
use this command:
</p><pre class="programlisting">C:\> <strong class="userinput"><code>mysqld-debug --debug --standalone</code></strong>
</pre><p>
After this, you can use the <code class="literal">mysql.exe</code>
command-line tool in a second console window to reproduce the
problem. You can stop the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server
with <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin shutdown</strong></span></a>.
</p><p>
Note that the trace file become <span class="bold"><strong>very
big</strong></span>! If you want to generate a smaller trace file,
you can use debugging options something like this:
</p><p>
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld
--debug=d,info,error,query,general,where:O,/tmp/mysqld.trace</strong></span></a>
</p><p>
This only prints information with the most interesting tags to
the trace file.
</p><p>
If you make a bug report about this, please only send the
lines from the trace file to the appropriate mailing list
where something seems to go wrong! If you can't locate the
wrong place, you can ftp the trace file, together with a full
bug report, to <a href="ftp://ftp.mysql.com/pub/mysql/upload/" target="_top">ftp://ftp.mysql.com/pub/mysql/upload/</a> so that a
MySQL developer can take a look at it.
</p><p>
The trace file is made with the
<span class="bold"><strong>DBUG</strong></span> package by Fred Fish.
See <a href="extending-mysql.html#the-dbug-package" title="22.5.3. The DBUG Package">Section 22.5.3, “The DBUG Package”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="making-windows-dumps"></a>22.5.1.3. Using <code class="filename">pdb</code> to create a Windows crashdump</h4></div></div></div><p>
Starting with MySQL 5.1.12 the Program Database files
(extension <code class="filename">pdb</code>) are included in the
Noinstall distribution of MySQL. These files provide
information for debugging your MySQL installation in the event
of a problem.
</p><p>
The PDB file contains more detailed information about
<code class="literal">mysqld</code> and other tools that enables more
detailed trace and dump files to be created. You can use these
with Dr Watson, <span><strong class="command">WinDbg</strong></span> and Visual Studio to
debug <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>.
</p><p>
For more information on PDB files, see
<a href="http://support.microsoft.com/kb/121366/" target="_top">Microsoft
Knowledge Base Article 121366</a>. For more information on
the debugging options available, see
<a href="http://www.microsoft.com/whdc/devtools/debugging/default.mspx" target="_top">Debugging
Tools for Windows</a>.
</p><p>
Dr Watson is installed with all Windows distributions, but if
you have installed Windows development tools, Dr Watson may
have been replaced with WinDbg, the debugger included with
Visual Studio, or the debugging tools provided with Borland or
Delphi.
</p><p>
To generate a crash file using Dr Watson, follow these steps:
</p><div class="orderedlist"><ol type="1"><li><p>
Start Dr Watson by running <span><strong class="command">drwtsn32.exe</strong></span>
interactively using the <code class="option">-i</code> option:
</p><pre class="programlisting">C:\> drwtsn32 -i</pre></li><li><p>
Set the <span class="guilabel">Log File Path</span> to the
directory where you want to store trace files.
</p></li><li><p>
Make sure <span class="guilabel">Dump All Thread Contexts</span>
and <span class="guilabel">Append To Existing Log File</span>.
</p></li><li><p>
Uncheck <span class="guilabel">Dump Sumbol Table</span>,
<span class="guilabel">Visual Notification</span>, <span class="guilabel">Sound
Notification</span> and <span class="guilabel">Create Crash Dump
File</span>.
</p></li><li><p>
Set the <span class="guilabel">Number of Instructions</span> to a
suitable value to capture enough calls in the stacktrace.
A value of at 25 should be enough.
</p></li></ol></div><p>
Note that the file generated can be very large.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="using-gdb-on-mysqld"></a>22.5.1.4. Debugging <span><strong class="command">mysqld</strong></span> under <span><strong class="command">gdb</strong></span></h4></div></div></div><a class="indexterm" name="id5057020"></a><p>
On most systems you can also start <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>
from <span><strong class="command">gdb</strong></span> to get more information if
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> crashes.
</p><p>
With some older <span><strong class="command">gdb</strong></span> versions on Linux you
must use <code class="literal">run --one-thread</code> if you want to be
able to debug <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> threads. In this case,
you can only have one thread active at a time. It is best to
upgrade to <span><strong class="command">gdb</strong></span> 5.1 because thread debugging
works much better with this version!
</p><p>
NPTL threads (the new thread library on Linux) may cause
problems while running <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> under
<span><strong class="command">gdb</strong></span>. Some symptoms are:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> hangs during startup (before it
writes <code class="literal">ready for connections</code>).
</p></li><li><p>
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> crashes during a
<code class="literal">pthread_mutex_lock()</code> or
<code class="literal">pthread_mutex_unlock()</code> call.
</p></li></ul></div><p>
In this case, you should set the following environment
variable in the shell before starting <span><strong class="command">gdb</strong></span>:
</p><pre class="programlisting">LD_ASSUME_KERNEL=2.4.1
export LD_ASSUME_KERNEL
</pre><p>
When running <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> under
<span><strong class="command">gdb</strong></span>, you should disable the stack trace
with <a href="server-administration.html#option_mysqld_skip-stack-trace"><code class="option">--skip-stack-trace</code></a> to be
able to catch segfaults within <span><strong class="command">gdb</strong></span>.
</p><p>
In MySQL 4.0.14 and above you should use the
<a href="server-administration.html#option_mysqld_gdb"><code class="option">--gdb</code></a> option to
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>. This installs an interrupt handler
for <code class="literal">SIGINT</code> (needed to stop
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with <code class="literal">^C</code> to set
breakpoints) and disable stack tracing and core file handling.
</p><p>
It is very hard to debug MySQL under <span><strong class="command">gdb</strong></span> if
you do a lot of new connections the whole time as
<span><strong class="command">gdb</strong></span> doesn't free the memory for old
threads. You can avoid this problem by starting
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with
<a href="server-administration.html#sysvar_thread_cache_size"><code class="literal">thread_cache_size</code></a> set to a
value equal to
<a href="server-administration.html#sysvar_max_connections"><code class="literal">max_connections</code></a> + 1. In most
cases just using
<a href="server-administration.html#sysvar_thread_cache_size"><code class="option">--thread_cache_size=5'</code></a> helps a
lot!
</p><p>
If you want to get a core dump on Linux if
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> dies with a SIGSEGV signal, you can
start <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the
<a href="server-administration.html#option_mysqld_core-file"><code class="option">--core-file</code></a> option. This core
file can be used to make a backtrace that may help you find
out why <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> died:
</p><pre class="programlisting">shell> <strong class="userinput"><code>gdb mysqld core</code></strong>
gdb> backtrace full
gdb> quit
</pre><p>
See <a href="error-handling.html#crashing" title="B.1.4.2. What to Do If MySQL Keeps Crashing">Section B.1.4.2, “What to Do If MySQL Keeps Crashing”</a>.
</p><p>
If you are using <span><strong class="command">gdb</strong></span> 4.17.x or above on
Linux, you should install a <code class="filename">.gdb</code> file,
with the following information, in your current directory:
</p><pre class="programlisting">set print sevenbit off
handle SIGUSR1 nostop noprint
handle SIGUSR2 nostop noprint
handle SIGWAITING nostop noprint
handle SIGLWP nostop noprint
handle SIGPIPE nostop
handle SIGALRM nostop
handle SIGHUP nostop
handle SIGTERM nostop noprint
</pre><p>
If you have problems debugging threads with
<span><strong class="command">gdb</strong></span>, you should download gdb 5.x and try
this instead. The new <span><strong class="command">gdb</strong></span> version has very
improved thread handling!
</p><p>
Here is an example how to debug mysqld:
</p><pre class="programlisting">shell> <strong class="userinput"><code>gdb /usr/local/libexec/mysqld</code></strong>
gdb> run
...
backtrace full # Do this when mysqld crashes
</pre><p>
Include the above output in a bug report, which you can file
using the instructions in <a href="introduction.html#bug-reports" title="1.6. How to Report Bugs or Problems">Section 1.6, “How to Report Bugs or Problems”</a>.
</p><p>
If <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> hangs you can try to use some
system tools like <code class="literal">strace</code> or
<code class="literal">/usr/proc/bin/pstack</code> to examine where
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> has hung.
</p><pre class="programlisting">strace /tmp/log libexec/mysqld
</pre><a class="indexterm" name="id5057436"></a><a class="indexterm" name="id5057445"></a><a class="indexterm" name="id5057454"></a><a class="indexterm" name="id5057463"></a><p>
If you are using the Perl <code class="literal">DBI</code> interface,
you can turn on debugging information by using the
<code class="literal">trace</code> method or by setting the
<code class="literal">DBI_TRACE</code> environment variable.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="using-stack-trace"></a>22.5.1.5. Using a Stack Trace</h4></div></div></div><p>
On some operating systems, the error log contains a stack
trace if <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> dies unexpectedly. You can
use this to find out where (and maybe why)
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> died. See
<a href="server-administration.html#error-log" title="5.2.2. The Error Log">Section 5.2.2, “The Error Log”</a>. To get a stack trace, you must
not compile <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the
<code class="option">-fomit-frame-pointer</code> option to gcc. See
<a href="extending-mysql.html#compiling-for-debugging" title="22.5.1.1. Compiling MySQL for Debugging">Section 22.5.1.1, “Compiling MySQL for Debugging”</a>.
</p><p>
A stack trace in the error log looks something like this:
</p><pre class="programlisting">mysqld got signal 11;
Attempting backtrace. You can use the following information
to find out where mysqld died. If you see no messages after
this, something went terribly wrong...
stack_bottom = 0x41fd0110 thread_stack 0x40000
mysqld(my_print_stacktrace+0x32)[0x9da402]
mysqld(handle_segfault+0x28a)[0x6648e9]
/lib/libpthread.so.0[0x7f1a5af000f0]
/lib/libc.so.6(strcmp+0x2)[0x7f1a5a10f0f2]
mysqld(_Z21check_change_passwordP3THDPKcS2_Pcj+0x7c)[0x7412cb]
mysqld(_ZN16set_var_password5checkEP3THD+0xd0)[0x688354]
mysqld(_Z17sql_set_variablesP3THDP4ListI12set_var_baseE+0x68)[0x688494]
mysqld(_Z21mysql_execute_commandP3THD+0x41a0)[0x67a170]
mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x282)[0x67f0ad]
mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xbb7[0x67fdf8]
mysqld(_Z10do_commandP3THD+0x24d)[0x6811b6]
mysqld(handle_one_connection+0x11c)[0x66e05e]
</pre><p>
If resolution of function names for the trace fails, the trace
contains less information:
</p><pre class="programlisting">mysqld got signal 11;
Attempting backtrace. You can use the following information
to find out where mysqld died. If you see no messages after
this, something went terribly wrong...
stack_bottom = 0x41fd0110 thread_stack 0x40000
[0x9da402]
[0x6648e9]
[0x7f1a5af000f0]
[0x7f1a5a10f0f2]
[0x7412cb]
[0x688354]
[0x688494]
[0x67a170]
[0x67f0ad]
[0x67fdf8]
[0x6811b6]
[0x66e05e]
</pre><p>
In the latter case, you can use the
<a href="programs.html#resolve-stack-dump" title="4.7.4. resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols"><span><strong class="command">resolve_stack_dump</strong></span></a> utility to determine
where <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> died by using the following
procedure:
</p><div class="orderedlist"><ol type="1"><li><p>
Copy the numbers from the stack trace to a file, for
example <code class="filename">mysqld.stack</code>. The numbers
should not include the surrounding square brackets:
</p><pre class="programlisting">0x9da402
0x6648e9
0x7f1a5af000f0
0x7f1a5a10f0f2
0x7412cb
0x688354
0x688494
0x67a170
0x67f0ad
0x67fdf8
0x6811b6
0x66e05e
</pre></li><li><p>
Make a symbol file for the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>
server:
</p><pre class="programlisting">shell> <strong class="userinput"><code>nm -n libexec/mysqld > /tmp/mysqld.sym</code></strong>
</pre><p>
If <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> is not linked statically, use
the following command instead:
</p><pre class="programlisting">shell> <strong class="userinput"><code>nm -D -n libexec/mysqld > /tmp/mysqld.sym</code></strong>
</pre><p>
If you want to decode C++ symbols, use the
<code class="option">--demangle</code>, if available, to
<span><strong class="command">nm</strong></span>. If your version of
<span><strong class="command">nm</strong></span> does not have this option, you will
need to use the <span><strong class="command">c++filt</strong></span> command after
the stack dump has been produced to demangle the C++
names.
</p></li><li><p>
Execute the following command:
</p><pre class="programlisting">shell> <strong class="userinput"><code>resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack</code></strong>
</pre><p>
If you were not able to include demangled C++ names in
your symbol file, process the
<a href="programs.html#resolve-stack-dump" title="4.7.4. resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols"><span><strong class="command">resolve_stack_dump</strong></span></a> output using
<span><strong class="command">c++filt</strong></span>:
</p><pre class="programlisting">shell> <strong class="userinput"><code>resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack | c++filt</code></strong>
</pre><p>
This prints out where <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> died. If
that does not help you find out why
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> died, you should create a bug
report and include the output from the preceding command
with the bug report.
</p><p>
However, in most cases it does not help us to have just a
stack trace to find the reason for the problem. To be able
to locate the bug or provide a workaround, in most cases
we need to know the statement that killed
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> and preferably a test case so
that we can repeat the problem! See
<a href="introduction.html#bug-reports" title="1.6. How to Report Bugs or Problems">Section 1.6, “How to Report Bugs or Problems”</a>.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="using-log-files"></a>22.5.1.6. Using Server Logs to Find Causes of Errors in <span><strong class="command">mysqld</strong></span></h4></div></div></div><p>
Note that before starting <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the
general query log enabled, you should check all your tables
with <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>. See
<a href="server-administration.html" title="Chapter 5. MySQL Server Administration">Chapter 5, <i>MySQL Server Administration</i></a>.
</p><p>
If <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> dies or hangs, you should start
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the general query log enabled.
See <a href="server-administration.html#query-log" title="5.2.3. The General Query Log">Section 5.2.3, “The General Query Log”</a>. When
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> dies again, you can examine the end
of the log file for the query that killed
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>.
</p><p>
If you use the default general query log file, the log is
stored in the database directory as
<code class="filename"><em class="replaceable"><code>host_name</code></em>.log</code>
In most cases it is the last query in the log file that killed
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>, but if possible you should verify
this by restarting <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> and executing the
found query from the <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> command-line
tools. If this works, you should also test all complicated
queries that didn't complete.
</p><p>
You can also try the command
<a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax"><code class="literal">EXPLAIN</code></a> on all
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT</code></a> statements that takes a
long time to ensure that <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> is using
indexes properly. See <a href="sql-syntax.html#explain" title="12.3.2. EXPLAIN Syntax">Section 12.3.2, “<code class="literal">EXPLAIN</code> Syntax”</a>.
</p><p>
You can find the queries that take a long time to execute by
starting <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the slow query log
enabled. See <a href="server-administration.html#slow-query-log" title="5.2.5. The Slow Query Log">Section 5.2.5, “The Slow Query Log”</a>.
</p><p>
If you find the text <code class="literal">mysqld restarted</code> in
the error log file (normally named
<code class="filename">hostname.err</code>) you probably have found a
query that causes <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> to fail. If this
happens, you should check all your tables with
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> (see
<a href="server-administration.html" title="Chapter 5. MySQL Server Administration">Chapter 5, <i>MySQL Server Administration</i></a>), and test the queries
in the MySQL log files to see whether one fails. If you find
such a query, try first upgrading to the newest MySQL version.
If this doesn't help and you can't find anything in the
<code class="literal">mysql</code> mail archive, you should report the
bug to a MySQL mailing list. The mailing lists are described
at <a href="http://lists.mysql.com/" target="_top">http://lists.mysql.com/</a>, which also has
links to online list archives.
</p><p>
If you have started <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with
<a href="server-administration.html#option_mysqld_myisam-recover"><code class="option">--myisam-recover</code></a>, MySQL
automatically checks and tries to repair
<code class="literal">MyISAM</code> tables if they are marked as 'not
closed properly' or 'crashed'. If this happens, MySQL writes
an entry in the <code class="literal">hostname.err</code> file
<code class="literal">'Warning: Checking table ...'</code> which is
followed by <code class="literal">Warning: Repairing table</code> if the
table needs to be repaired. If you get a lot of these errors,
without <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> having died unexpectedly
just before, then something is wrong and needs to be
investigated further. See <a href="server-administration.html#server-options" title="5.1.2. Server Command Options">Section 5.1.2, “Server Command Options”</a>.
</p><p>
It is not a good sign if <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> did die
unexpectedly, but in this case, you should not investigate the
<code class="literal">Checking table...</code> messages, but instead try
to find out why <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> died.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="reproducible-test-case"></a>22.5.1.7. Making a Test Case If You Experience Table Corruption</h4></div></div></div><p>
If you get corrupted tables or if <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>
always fails after some update commands, you can test whether
this bug is reproducible by doing the following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Take down the MySQL daemon (with <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin
shutdown</strong></span></a>).
</p></li><li><p>
Make a backup of the tables (to guard against the very
unlikely case that the repair does something bad).
</p></li><li><p>
Check all tables with <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -s
database/*.MYI</strong></span></a>. Repair any wrong tables with
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -r
database/<em class="replaceable"><code>table</code></em>.MYI</strong></span></a>.
</p></li><li><p>
Make a second backup of the tables.
</p></li><li><p>
Remove (or move away) any old log files from the MySQL
data directory if you need more space.
</p></li><li><p>
Start <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with the binary log
enabled. If you want to find a query that crashes
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>, you should start the server
with both the general query log enabled as well. See
<a href="server-administration.html#query-log" title="5.2.3. The General Query Log">Section 5.2.3, “The General Query Log”</a>, and
<a href="server-administration.html#binary-log" title="5.2.4. The Binary Log">Section 5.2.4, “The Binary Log”</a>.
</p></li><li><p>
When you have gotten a crashed table, stop the
<code class="literal">mysqld server</code>.
</p></li><li><p>
Restore the backup.
</p></li><li><p>
Restart the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server
<span class="bold"><strong>without</strong></span> the binary log
enabled.
</p></li><li><p>
Re-execute the commands with <a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog
binary-log-file | mysql</strong></span></a>. The binary log is saved
in the MySQL database directory with the name
<code class="literal">hostname-bin.<em class="replaceable"><code>NNNNNN</code></em></code>.
</p></li><li><p>
If the tables are corrupted again or you can get
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> to die with the above command,
you have found reproducible bug that should be easy to
fix! FTP the tables and the binary log to
<a href="ftp://ftp.mysql.com/pub/mysql/upload/" target="_top">ftp://ftp.mysql.com/pub/mysql/upload/</a> and report it in our
bugs database using the instructions given in
<a href="introduction.html#bug-reports" title="1.6. How to Report Bugs or Problems">Section 1.6, “How to Report Bugs or Problems”</a>. (Please note that the
<code class="filename">/pub/mysql/upload/</code> FTP directory is
not listable, so you'll not see what you've uploaded in
your FTP client.) If you are a support customer, you can
use the MySQL Customer Support Center
<a href="https://support.mysql.com/" target="_top">https://support.mysql.com/</a> to alert the
MySQL team about the problem and have it fixed as soon as
possible.
</p></li></ul></div><p>
You can also use the script <a href="programs.html#mysql-find-rows" title="4.6.12. mysql_find_rows — Extract SQL Statements from Files"><span><strong class="command">mysql_find_rows</strong></span></a>
to just execute some of the update statements if you want to
narrow down the problem.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="debugging-client"></a>22.5.2. Debugging a MySQL Client</h3></div></div></div><a class="indexterm" name="id5058315"></a><a class="indexterm" name="id5058327"></a><p>
To be able to debug a MySQL client with the integrated debug
package, you should configure MySQL with
<a href="installing.html#option_configure_with-debug"><code class="option">--with-debug</code></a> or
<a href="installing.html#option_configure_with-debug"><code class="option">--with-debug=full</code></a>. See
<a href="installing.html#configure-options" title="2.3.2. Typical configure Options">Section 2.3.2, “Typical <span><strong class="command">configure</strong></span> Options”</a>.
</p><a class="indexterm" name="id5058363"></a><a class="indexterm" name="id5058372"></a><p>
Before running a client, you should set the
<code class="literal">MYSQL_DEBUG</code> environment variable:
</p><pre class="programlisting">shell> <strong class="userinput"><code>MYSQL_DEBUG=d:t:O,/tmp/client.trace</code></strong>
shell> <strong class="userinput"><code>export MYSQL_DEBUG</code></strong>
</pre><p>
This causes clients to generate a trace file in
<code class="filename">/tmp/client.trace</code>.
</p><p>
If you have problems with your own client code, you should
attempt to connect to the server and run your query using a
client that is known to work. Do this by running
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> in debugging mode (assuming that you
have compiled MySQL with debugging on):
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql --debug=d:t:O,/tmp/client.trace</code></strong>
</pre><p>
This provides useful information in case you mail a bug report.
See <a href="introduction.html#bug-reports" title="1.6. How to Report Bugs or Problems">Section 1.6, “How to Report Bugs or Problems”</a>.
</p><p>
If your client crashes at some 'legal' looking code, you should
check that your <code class="filename">mysql.h</code> include file
matches your MySQL library file. A very common mistake is to use
an old <code class="filename">mysql.h</code> file from an old MySQL
installation with new MySQL library.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="the-dbug-package"></a>22.5.3. The DBUG Package</h3></div></div></div><a class="indexterm" name="id5058481"></a><p>
The MySQL server and most MySQL clients are compiled with the
DBUG package originally created by Fred Fish. When you have
configured MySQL for debugging, this package makes it possible
to get a trace file of what the program is debugging. See
<a href="extending-mysql.html#making-trace-files" title="22.5.1.2. Creating Trace Files">Section 22.5.1.2, “Creating Trace Files”</a>.
</p><p>
This section summaries the argument values that you can specify
in debug options on the command line for MySQL programs that
have been built with debugging support. For more information
about programming with the DBUG package, see the DBUG manual in
the <code class="filename">dbug</code> directory of MySQL source
distributions. It is best to use a recent distribution to get
the most updated DBUG manual.
</p><p>
You use the debug package by invoking a program with the
<code class="option">--debug="..."</code> or the <code class="option">-#...</code>
option.
</p><p>
Most MySQL programs have a default debug string that is used if
you don't specify an option to <code class="option">--debug</code>. The
default trace file is usually
<code class="literal">/tmp/program_name.trace</code> on Unix and
<code class="literal">\program_name.trace</code> on Windows.
</p><p>
The debug control string is a sequence of colon-separated fields
as follows:
</p><pre class="programlisting"><field_1>:<field_2>:...:<field_N>
</pre><p>
Each field consists of a mandatory flag character followed by an
optional “<span class="quote"><code class="literal">,</code></span>” and comma-separated
list of modifiers:
</p><pre class="programlisting">flag[,modifier,modifier,...,modifier]
</pre><p>
The following table shows the currently recognized flag
characters.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>Flag</strong></span></td><td><span class="bold"><strong>Description</strong></span></td></tr><tr><td><code class="literal">d</code></td><td>Enable output from DBUG_<N> macros for the current state. May be
followed by a list of keywords which selects output only
for the DBUG macros with that keyword. An empty list of
keywords implies output for all macros.</td></tr><tr><td><code class="literal">D</code></td><td>Delay after each debugger output line. The argument is the number of
tenths of seconds to delay, subject to machine
capabilities. For example, <code class="option">-#D,20</code>
specifies a delay of two seconds.</td></tr><tr><td><code class="literal">f</code></td><td>Limit debugging, tracing, and profiling to the list of named functions.
Note that a null list disables all functions. The
appropriate <code class="literal">d</code> or <code class="literal">t</code>
flags must still be given; this flag only limits their
actions if they are enabled.</td></tr><tr><td><code class="literal">F</code></td><td>Identify the source file name for each line of debug or trace output.</td></tr><tr><td><code class="literal">i</code></td><td>Identify the process with the PID or thread ID for each line of debug or
trace output.</td></tr><tr><td><code class="literal">g</code></td><td>Enable profiling. Create a file called <code class="filename">dbugmon.out</code>
containing information that can be used to profile the
program. May be followed by a list of keywords that
select profiling only for the functions in that list. A
null list implies that all functions are considered.</td></tr><tr><td><code class="literal">L</code></td><td>Identify the source file line number for each line of debug or trace
output.</td></tr><tr><td><code class="literal">n</code></td><td>Print the current function nesting depth for each line of debug or trace
output.</td></tr><tr><td><code class="literal">N</code></td><td>Number each line of debug output.</td></tr><tr><td><code class="literal">o</code></td><td>Redirect the debugger output stream to the specified file. The default
output is <code class="literal">stderr</code>.</td></tr><tr><td><code class="literal">O</code></td><td>Like <code class="literal">o</code>, but the file is really flushed between each
write. When needed, the file is closed and reopened
between each write.</td></tr><tr><td><code class="literal">p</code></td><td>Limit debugger actions to specified processes. A process must be
identified with the <code class="literal">DBUG_PROCESS</code>
macro and match one in the list for debugger actions to
occur.</td></tr><tr><td><code class="literal">P</code></td><td>Print the current process name for each line of debug or trace output.</td></tr><tr><td><code class="literal">r</code></td><td>When pushing a new state, do not inherit the previous state's function
nesting level. Useful when the output is to start at the
left margin.</td></tr><tr><td><code class="literal">S</code></td><td>Do function <code class="literal">_sanity(_file_,_line_)</code> at each debugged
function until <code class="literal">_sanity()</code> returns
something that differs from 0. (Mostly used with
<code class="literal">safemalloc</code> to find memory leaks)</td></tr><tr><td><code class="literal">t</code></td><td>Enable function call/exit trace lines. May be followed by a list
(containing only one modifier) giving a numeric maximum
trace level, beyond which no output occurs for either
debugging or tracing macros. The default is a compile
time option.</td></tr></tbody></table></div><p>
Some examples of debug control strings that might appear on a
shell command line (the <code class="option">-#</code> is typically used to
introduce a control string to an application program) are:
</p><pre class="programlisting">-#d:t
-#d:f,main,subr1:F:L:t,20
-#d,input,output,files:n
-#d:t:i:O,\\mysqld.trace
</pre><p>
In MySQL, common tags to print (with the <code class="literal">d</code>
option) are <code class="literal">enter</code>, <code class="literal">exit</code>,
<code class="literal">error</code>, <code class="literal">warning</code>,
<code class="literal">info</code>, and <code class="literal">loop</code>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="rts-threads"></a>22.5.4. Comments about RTS Threads</h3></div></div></div><a class="indexterm" name="id5058971"></a><a class="indexterm" name="id5058980"></a><p>
I have tried to use the RTS thread packages with MySQL but
stumbled on the following problems:
</p><p>
They use old versions of many POSIX calls and it is very tedious
to make wrappers for all functions. I am inclined to think that
it would be easier to change the thread libraries to the newest
POSIX specification.
</p><p>
Some wrappers are currently written. See
<code class="filename">mysys/my_pthread.c</code> for more info.
</p><p>
At least the following should be changed:
</p><p>
<code class="literal">pthread_get_specific</code> should use one argument.
<code class="literal">sigwait</code> should take two arguments. A lot of
functions (at least <code class="literal">pthread_cond_wait</code>,
<code class="literal">pthread_cond_timedwait()</code>) should return the
error code on error. Now they return -1 and set
<code class="literal">errno</code>.
</p><p>
Another problem is that user-level threads use the
<code class="literal">ALRM</code> signal and this aborts a lot of
functions (<code class="literal">read</code>, <code class="literal">write</code>,
<code class="literal">open</code>...). MySQL should do a retry on
interrupt on all of these but it is not that easy to verify it.
</p><p>
The biggest unsolved problem is the following:
</p><p>
To get thread-level alarms I changed
<code class="filename">mysys/thr_alarm.c</code> to wait between alarms
with <code class="literal">pthread_cond_timedwait()</code>, but this
aborts with error <code class="literal">EINTR</code>. I tried to debug the
thread library as to why this happens, but couldn't find any
easy solution.
</p><p>
If someone wants to try MySQL with RTS threads I suggest the
following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Change functions MySQL uses from the thread library to
POSIX. This shouldn't take that long.
</p></li><li><p>
Compile all libraries with the
<code class="option">-DHAVE_rts_threads</code>.
</p></li><li><p>
Compile <code class="literal">thr_alarm</code>.
</p></li><li><p>
If there are some small differences in the implementation,
they may be fixed by changing
<code class="filename">my_pthread.h</code> and
<code class="filename">my_pthread.c</code>.
</p></li><li><p>
Run <code class="literal">thr_alarm</code>. If it runs without any
“<span class="quote">warning,</span>” “<span class="quote">error,</span>” or aborted
messages, you are on the right track. Here is a successful
run on Solaris:
</p><pre class="programlisting">Main thread: 1
Thread 0 (5) started
Thread: 5 Waiting
process_alarm
Thread 1 (6) started
Thread: 6 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 1 (1) sec
Thread: 6 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 2 (2) sec
Thread: 6 Simulation of no alarm needed
Thread: 6 Slept for 0 (3) sec
Thread: 6 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 4 (4) sec
Thread: 6 Waiting
process_alarm
thread_alarm
Thread: 5 Slept for 10 (10) sec
Thread: 5 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 5 (5) sec
Thread: 6 Waiting
process_alarm
process_alarm
...
thread_alarm
Thread: 5 Slept for 0 (1) sec
end
</pre></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="thread-packages"></a>22.5.5. Differences Between Thread Packages</h3></div></div></div><a class="indexterm" name="id5059205"></a><p>
MySQL is very dependent on the thread package used. So when
choosing a good platform for MySQL, the thread package is very
important.
</p><p>
There are at least three types of thread packages:
</p><div class="itemizedlist"><ul type="disc"><li><p>
User threads in a single process. Thread switching is
managed with alarms and the threads library manages all
nonthread-safe functions with locks. Read, write and select
operations are usually managed with a thread-specific select
that switches to another thread if the running threads have
to wait for data. If the user thread packages are integrated
in the standard libs (FreeBSD and BSDI threads) the thread
package requires less overhead than thread packages that
have to map all unsafe calls (MIT-pthreads, FSU Pthreads and
RTS threads). In some environments (for example, SCO), all
system calls are thread-safe so the mapping can be done very
easily (FSU Pthreads on SCO). Downside: All mapped calls
take a little time and it is quite tricky to be able to
handle all situations. There are usually also some system
calls that are not handled by the thread package (like
MIT-pthreads and sockets). Thread scheduling isn't always
optimal.
</p></li><li><p>
User threads in separate processes. Thread switching is done
by the kernel and all data are shared between threads. The
thread package manages the standard thread calls to allow
sharing data between threads. LinuxThreads is using this
method. Downside: Lots of processes. Thread creating is
slow. If one thread dies the rest are usually left hanging
and you must kill them all before restarting. Thread
switching is somewhat expensive.
</p></li><li><p>
Kernel threads. Thread switching is handled by the thread
library or the kernel and is very fast. Everything is done
in one process, but on some systems, <span><strong class="command">ps</strong></span>
may show the different threads. If one thread aborts, the
whole process aborts. Most system calls are thread-safe and
should require very little overhead. Solaris, HP-UX, AIX and
OSF/1 have kernel threads.
</p></li></ul></div><p>
In some systems kernel threads are managed by integrating user
level threads in the system libraries. In such cases, the thread
switching can only be done by the thread library and the kernel
isn't really “<span class="quote">thread aware.</span>”
</p></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="connectors-apis.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="faqs.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 21. Connectors and APIs </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Appendix A. MySQL 5.1 Frequently Asked Questions</td></tr></table></div></body></html>
