<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 24. Extending MySQL</title><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.0 Reference Manual"><link rel="prev" href="connectors.html" title="Chapter 23. Connectors"><link rel="next" href="problems.html" title="Appendix A. Problems and Common Errors"></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 24. Extending MySQL</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="connectors.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="problems.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 24. 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">24.1. MySQL Internals</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#mysql-threads">24.1.1. MySQL Threads</a></span></dt><dt><span class="section"><a href="extending-mysql.html#mysql-test-suite">24.1.2. MySQL Test Suite</a></span></dt></dl></dd><dt><span class="section"><a href="extending-mysql.html#adding-functions">24.2. Adding New Functions to MySQL</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#udf-features">24.2.1. Features of the User-Defined Function Interface</a></span></dt><dt><span class="section"><a href="extending-mysql.html#create-function">24.2.2. <code class="literal">CREATE FUNCTION/DROP FUNCTION</code> Syntax</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-udf">24.2.3. Adding a New User-Defined Function</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-native-function">24.2.4. Adding a New Native Function</a></span></dt></dl></dd><dt><span class="section"><a href="extending-mysql.html#adding-procedures">24.3. Adding New Procedures to MySQL</a></span></dt><dd><dl><dt><span class="section"><a href="extending-mysql.html#procedure-analyse">24.3.1. Procedure Analyse</a></span></dt><dt><span class="section"><a href="extending-mysql.html#writing-a-procedure">24.3.2. Writing a Procedure</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>24.1. MySQL Internals</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#mysql-threads">24.1.1. MySQL Threads</a></span></dt><dt><span class="section"><a href="extending-mysql.html#mysql-test-suite">24.1.2. MySQL Test Suite</a></span></dt></dl></div><a class="indexterm" name="id3125840"></a><a class="indexterm" name="id3125847"></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 in-between
versions code, or just want to keep track of development, follow
the instructions in <a href="installing.html#installing-source-tree" title="2.8.3. Installing from the Development Source Tree">Section 2.8.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 is
relatively low traffic. For details on how to subscribe, please
see <a href="introduction.html#mailing-list" title="1.7.1.1. The MySQL Mailing Lists">Section 1.7.1.1, “The MySQL Mailing Lists”</a>. All developers at MySQL AB 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>24.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>
The TCP/IP connection thread handles all connection requests
and creates a new dedicated thread to handle the
authentication and SQL query processing for each connection.
</p></li><li><p>
On Windows NT there is a named pipe handler thread that does
the same work as the TCP/IP connection thread on named pipe
connect requests.
</p></li><li><p>
The 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 <span><strong class="command">mysqld</strong></span> 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 one uses the
<code class="option">--flush_time=<em class="replaceable"><code>val</code></em></code>
option, a dedicated thread is created to flush all tables at
the given interval.
</p></li><li><p>
Every connection has its own thread.
</p></li><li><p>
Every different table on which one uses <code class="literal">INSERT
DELAYED</code> gets its own thread.
</p></li><li><p>
If you use <code class="option">--master-host</code>, a slave
replication thread is started to read and apply updates from
the master.
</p></li></ul></div><p>
<span><strong class="command">mysqladmin processlist</strong></span> only shows the
connection, <code class="literal">INSERT DELAYED</code>, and replication
threads.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="mysql-test-suite"></a>24.1.2. MySQL Test Suite</h3></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#running-mysqltest">24.1.2.1. Running the MySQL Test Suite</a></span></dt><dt><span class="section"><a href="extending-mysql.html#extending-mysqltest">24.1.2.2. Extending the MySQL Test Suite</a></span></dt><dt><span class="section"><a href="extending-mysql.html#reporting-mysqltest-bugs">24.1.2.3. Reporting Bugs in the MySQL Test Suite</a></span></dt></dl></div><a class="indexterm" name="id3126036"></a><a class="indexterm" name="id3126046"></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. They cannot currently be run in a native Windows
environment.
</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, OS/library issues, and is quite thorough in testing
replication. Our eventual 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><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="running-mysqltest"></a>24.1.2.1. Running the MySQL Test Suite</h4></div></div></div><p>
The test system consist of a test language interpreter
(<span><strong class="command">mysqltest</strong></span>), a shell script to run all
tests(<span><strong class="command">mysql-test-run</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> or
<code class="literal">mysql-test/mysql-test-run</code> from the source
root. If you have installed a binary distribution,
<code class="literal">cd</code> to the install root (eg.
<code class="literal">/usr/local/mysql</code>), and do
<code class="literal">scripts/mysql-test-run</code>. All tests should
succeed. If not, you should try to find out why and report the
problem if this is a bug in MySQL. See
<a href="extending-mysql.html#reporting-mysqltest-bugs" title="24.1.2.3. Reporting Bugs in the MySQL Test Suite">Section 24.1.2.3, “Reporting Bugs in the MySQL Test Suite”</a>.
</p><p>
If you have a copy of <span><strong class="command">mysqld</strong></span> 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> and <code class="literal">9307</code>. If one of
those ports is taken, you should edit
<span><strong class="command">mysql-test-run</strong></span> and change the values of the
master and/or slave port to one that is available.
</p><p>
You can run one individual test case with
<code class="literal">mysql-test/mysql-test-run test_name</code>.
</p><p>
If one test fails, you should test running
<span><strong class="command">mysql-test-run</strong></span> with the
<code class="option">--force</code> option to check whether any other
tests fail.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="extending-mysqltest"></a>24.1.2.2. Extending the MySQL Test Suite</h4></div></div></div><p>
You can use the <span><strong class="command">mysqltest</strong></span> language to write
your own test cases. Unfortunately, we have not yet written
full documentation for it. You can, however, look at our
current test cases and use them as an example. The following
points should help you get started:
</p><div class="itemizedlist"><ul type="disc"><li><p>
The tests are located in
<code class="filename">mysql-test/t/*.test</code>
</p></li><li><p>
A test case consists of <code class="literal">;</code> terminated
statements and is similar to the input of
<span><strong class="command">mysql</strong></span> command-line client. A statement
by default is a query to be sent to MySQL server, unless
it is recognized as internal command (eg.
<span><strong class="command">sleep</strong></span>).
</p></li><li><p>
All queries that produce results---for example,
<code class="literal">SELECT</code>, <code class="literal">SHOW</code>,
<code class="literal">EXPLAIN</code>, etc., must be preceded with
<em class="replaceable"><code>@/path/to/result/file</code></em>. The file
must contain the expected results. An easy way to generate
the result file is to run <span><strong class="command">mysqltest -r <
t/test-case-name.test</strong></span> from the
<code class="filename">mysql-test</code> directory, and then edit
the generated result files, if needed, to adjust them to
the expected output. In that case, be very careful about
not adding or deleting any invisible characters --- make
sure to only change the text and/or delete lines. If you
have to insert a line, make sure that the fields are
separated by a hard tab, and that there is a hard tab at
the end. You may want to use <span><strong class="command">od -c</strong></span> to
make sure that your text editor has not messed anything up
during edit. We hope that you never have to edit the
output of <span><strong class="command">mysqltest -r</strong></span> as you only have
to do it when you find a bug.
</p></li><li><p>
To be consistent with our setup, you should put your
result files in the <code class="filename">mysql-test/r</code>
directory and name them
<code class="filename">test_name.result</code>. If the test
produces more than one result, you should use
<code class="filename">test_name.a.result</code>,
<code class="filename">test_name.b.result</code>, etc.
</p></li><li><p>
If a statement returns an error, you should specify it
with <code class="option">--error error-number</code> on the line
before the statement. The error number can be a list of
possible error numbers separated by
‘<code class="literal">,</code>’.
</p></li><li><p>
If you are writing a replication test case, you should on
the first line of the test file, put <code class="literal">source
include/master-slave.inc;</code>. To switch between
master and slave, use <code class="literal">connection
master;</code> and <code class="literal">connection
slave;</code>. If you need to do something on an
alternate connection, you can do <code class="literal">connection
master1;</code> for the master, and <code class="literal">connection
slave1;</code> for the slave.
</p></li><li><p>
If you need to do something in a loop, you can use
something like this:
</p><pre class="programlisting">let $1=1000;
while ($1)
{
# do your queries here
dec $1;
}
</pre></li><li><p>
To sleep between queries, use the <span><strong class="command">sleep</strong></span>
command. It supports fractions of a second, so you can use
<span><strong class="command">sleep 1.3;</strong></span>, for example, to sleep 1.3
seconds.
</p></li><li><p>
To run the slave with additional options for your test
case, put them in the command-line format in
<code class="filename">mysql-test/t/test_name-slave.opt</code>. For
the master, put them in
<code class="filename">mysql-test/t/test_name-master.opt</code>.
</p></li><li><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-list" title="1.7.1.1. The MySQL Mailing Lists">Section 1.7.1.1, “The MySQL Mailing Lists”</a>. As this list does not
accept attachments, 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></li></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="reporting-mysqltest-bugs"></a>24.1.2.3. Reporting Bugs in the MySQL Test Suite</h4></div></div></div><p>
If your MySQL version doesn't pass the test suite you should
do the following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Don't send a bug report before you have found out as much
as possible of what when wrong! When you do it, please use
the <span><strong class="command">mysqlbug</strong></span> script so that we can get
information about your system and MySQL version. See
<a href="introduction.html#bug-reports" title="1.7.1.3. How to Report Bugs or Problems">Section 1.7.1.3, “How to Report Bugs or Problems”</a>.
</p></li><li><p>
Make sure to include the output of
<span><strong class="command">mysql-test-run</strong></span>, as well as contents of
all <code class="filename">.reject</code> files in
<code class="filename">mysql-test/r</code> directory.
</p></li><li><p>
If a test in the test suite fails, check whether the test
fails also when run by its own:
</p><pre class="programlisting">cd mysql-test
mysql-test-run --local test-name
</pre><p>
If this fails, then you should configure MySQL with
<code class="option">--with-debug</code> and run
<span><strong class="command">mysql-test-run</strong></span> with the
<code class="option">--debug</code> option. If this also fails send
the trace file <code class="filename">var/tmp/master.trace</code>
to <a href="ftp://ftp.mysql.com/pub/mysql/upload/" target="_top">ftp://ftp.mysql.com/pub/mysql/upload/</a> so
that we can examine it. Please remember to also include a
full description of your system, the version of the
<span><strong class="command">mysqld</strong></span> binary and how you compiled it.
</p></li><li><p>
Try also to run <span><strong class="command">mysql-test-run</strong></span> with the
<code class="option">--force</code> option to see whether there is
any other test that fails.
</p></li><li><p>
If you have compiled MySQL yourself, check our manual for
how to compile MySQL on your platform or, preferable, use
one of the binaries we have compiled for you at
<a href="http://dev.mysql.com/downloads/" target="_top">http://dev.mysql.com/downloads/</a>. All our standard
binaries should pass the test suite!
</p></li><li><p>
If you get an error such as <code class="literal">Result length
mismatch</code> or <code class="literal">Result content
mismatch</code> it means that the output of the test
didn't match exactly the expected output. This could be a
bug in MySQL or that your version of
<span><strong class="command">mysqld</strong></span> produces slightly different
results under some circumstances.
</p><p>
Failed test results are put in a file with the same base
name as the result file with the
<code class="literal">.reject</code> extension. If your test case is
failing, you should do a diff on the two files. If you
cannot see how they are different, examine both with
<code class="literal">od -c</code> and also check their lengths.
</p></li><li><p>
If a test fails totally, you should check the logs file in
the <code class="filename">mysql-test/var/log</code> directory for
hints of what went wrong.
</p></li><li><p>
If you have compiled MySQL with debugging you can try to
debug this by running <span><strong class="command">mysql-test-run</strong></span>
with the <code class="option">--gdb</code> and/or
<code class="option">--debug</code> options. See
<a href="porting.html#making-trace-files" title="E.1.2. Creating Trace Files">Section E.1.2, “Creating Trace Files”</a>.
</p><p>
If you have not compiled MySQL for debugging you should
probably do that. Just specify the
<code class="option">--with-debug</code> options to
<span><strong class="command">configure</strong></span>. See
<a href="installing.html#installing-source" title="2.8. MySQL Installation Using a Source Distribution">Section 2.8, “MySQL Installation Using a Source Distribution”</a>.
</p></li></ul></div></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="adding-functions"></a>24.2. Adding New Functions to MySQL</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#udf-features">24.2.1. Features of the User-Defined Function Interface</a></span></dt><dt><span class="section"><a href="extending-mysql.html#create-function">24.2.2. <code class="literal">CREATE FUNCTION/DROP FUNCTION</code> Syntax</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-udf">24.2.3. Adding a New User-Defined Function</a></span></dt><dt><span class="section"><a href="extending-mysql.html#adding-native-function">24.2.4. Adding a New Native Function</a></span></dt></dl></div><a class="indexterm" name="id3126697"></a><a class="indexterm" name="id3126707"></a><a class="indexterm" name="id3126717"></a><a class="indexterm" name="id3126727"></a><a class="indexterm" name="id3126738"></a><p>
There are two 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 <code class="literal">CREATE FUNCTION</code> and <code class="literal">DROP
FUNCTION</code> statements. See
<a href="extending-mysql.html#create-function" title="24.2.2. CREATE FUNCTION/DROP FUNCTION Syntax">Section 24.2.2, “<code class="literal">CREATE FUNCTION/DROP FUNCTION</code> Syntax”</a>.
</p></li><li><p>
You can add functions as native (built-in) MySQL functions.
Native functions are compiled into the
<span><strong class="command">mysqld</strong></span> server and become available on a
permanent basis.
</p></li></ul></div><p>
Each method 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>
You can add UDFs to a binary MySQL distribution. Native
functions require you to modify a source distribution.
</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
<code class="literal">ABS()</code> or <code class="literal">SOUNDEX()</code>.
</p><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 described
in <a href="stored-procedures.html" title="Chapter 17. Stored Procedures and Functions">Chapter 17, <i>Stored Procedures and Functions</i></a>.
</p><p>
The following sections describe features of the UDF interface,
provide instructions for writing UDFs, and discuss security
precautions that MySQL takes to prevent UDF misuse.
</p><p>
For example source code that illustrates how to write UDFs, take a
look at the <code class="filename">sql/udf_example.cc</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>24.2.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 capabilties:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Functions can return string, integer, or real values.
</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 and types 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="create-function"></a>24.2.2. <code class="literal">CREATE FUNCTION/DROP FUNCTION</code> Syntax</h3></div></div></div><a class="indexterm" name="id3126962"></a><a class="indexterm" name="id3126972"></a><a class="indexterm" name="id3126981"></a><a class="indexterm" name="id3126991"></a><a class="indexterm" name="id3127001"></a><a class="indexterm" name="id3127010"></a><a class="indexterm" name="id3127019"></a><pre class="programlisting">CREATE [AGGREGATE] FUNCTION <em class="replaceable"><code>function_name</code></em> RETURNS {STRING|INTEGER|REAL}
SONAME <em class="replaceable"><code>shared_library_name</code></em>
DROP FUNCTION <em class="replaceable"><code>function_name</code></em>
</pre><p>
A user-defined function (UDF) is a way to extend MySQL with a
new function that works like a native (built-in) MySQL function
such as <code class="literal">ABS()</code> or <code class="literal">CONCAT()</code>.
</p><p>
<em class="replaceable"><code>function_name</code></em> is the name that should
be used in SQL statements to invoke the function. The
<code class="literal">RETURNS</code> clause indicates the type of the
function's return value.
<em class="replaceable"><code>shared_library_name</code></em> is the basename
of the shared object file that contains the code that implements
the function. The file must be located in a directory that is
searched by your system's dynamic linker.
</p><p>
To create a function, you must have the
<code class="literal">INSERT</code> and privilege for the
<code class="literal">mysql</code> database. To drop a function, you must
have the <code class="literal">DELETE</code> privilege for the
<code class="literal">mysql</code> database. This is because
<code class="literal">CREATE FUNCTION</code> adds a row to the
<code class="literal">mysql.func</code> system table that records the
function's name, type, and shared library name, and
<code class="literal">DROP FUNCTION</code> deletes the function's row from
that table. If you do not have this table, you should run the
<span><strong class="command">mysql_fix_privilege_tables</strong></span> script to create
it. See <a href="installing.html#upgrading-grant-tables" title="2.10.3. Upgrading the Grant Tables">Section 2.10.3, “Upgrading the Grant Tables”</a>.
</p><p>
An active function is one that has been loaded with
<code class="literal">CREATE FUNCTION</code> and not removed with
<code class="literal">DROP FUNCTION</code>. All active functions are
reloaded each time the server starts, unless you start
<span><strong class="command">mysqld</strong></span> with the
<code class="option">--skip-grant-tables</code> option. In this case, UDF
initialization is skipped and UDFs are unavailable.
</p><p>
For instructions on writing user-defined functions, see
<a href="extending-mysql.html#adding-udf" title="24.2.3. Adding a New User-Defined Function">Section 24.2.3, “Adding a New User-Defined Function”</a>. For the UDF mechanism to work,
functions must be written in C or C++, your operating system
must support dynamic loading and you must have compiled
<span><strong class="command">mysqld</strong></span> dynamically (not statically).
</p><p>
An <code class="literal">AGGREGATE</code> function works exactly like a
native MySQL aggregate (summary) function such as
<code class="literal">SUM</code> or <code class="literal">COUNT()</code>. For
<code class="literal">AGGREGATE</code> to work, your
<code class="literal">mysql.func</code> table must contain a
<code class="literal">type</code> column. If your
<code class="literal">mysql.func</code> table does not have this column,
you should run the <span><strong class="command">mysql_fix_privilege_tables</strong></span>
script to create it.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="adding-udf"></a>24.2.3. 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">24.2.3.1. UDF Calling Sequences for Simple Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-aggr-calling">24.2.3.2. UDF Calling Sequences for Aggregate Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-arguments">24.2.3.3. UDF Argument Processing</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-return-values">24.2.3.4. UDF Return Values and Error Handling</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-compiling">24.2.3.5. Compiling and Installing User-Defined Functions</a></span></dt><dt><span class="section"><a href="extending-mysql.html#udf-security">24.2.3.6. User-Defined Function Security Precautions</a></span></dt></dl></div><a class="indexterm" name="id3127255"></a><a class="indexterm" name="id3127265"></a><a class="indexterm" name="id3127276"></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.cc</code> that defines 5 new
functions. Consult this file to see how UDF calling conventions
work.
</p><p>
To be able to use UDFs, you need to link
<span><strong class="command">mysqld</strong></span> 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
<span><strong class="command">mysqld</strong></span> (for example, the
<code class="literal">metaphone</code> function in
<code class="filename">sql/udf_example.cc</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>
If you to use a precompiled distribution of MySQL, use
MySQL-Max, which contains a dynamically linked server that
supports dynamic loading.
</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><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><code class="literal">INTEGER</code></td><td><code class="literal">long long</code></td></tr><tr><td><code class="literal">REAL</code></td><td><code class="literal">double</code></td></tr></tbody></table></div></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 to:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Check the number of arguments to
<code class="literal">XXX()</code>.
</p></li><li><p>
Check that the arguments are of a required type or,
alternatively, tell MySQL to coerce arguments to the
types you want when the main function is called.
</p></li><li><p>
Allocate any memory required by the main function.
</p></li><li><p>
Specify the maximum length of the result.
</p></li><li><p>
Specify (for <code class="literal">REAL</code> functions) the
maximum number of decimals.
</p></li><li><p>
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, the SQL statement is aborted with an error message and
the main and deinitialization functions are not called.
Otherwise, the main function <code class="literal">xxx()</code> is called
once for each row. After all rows have been processed, the
deinitialization function <code class="literal">xxx_deinit()</code> is
called so it can perform any required cleanup.
</p><p>
For aggregate functions that work like <code class="literal">SUM()</code>,
you must also provide the following functions:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">xxx_clear()</code> (required in
5.0)
</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> (required)
</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 new 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>24.2.3.1. UDF Calling Sequences for Simple Functions</h4></div></div></div><a class="indexterm" name="id3127768"></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="24.2.3. Adding a New User-Defined Function">Section 24.2.3, “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>, <code class="literal">INTEGER</code>, or
<code class="literal">REAL</code> in the <code class="literal">CREATE
FUNCTION</code> 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 <code class="literal">INTEGER</code> functions:
</p><pre class="programlisting">long long xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
</pre><p>
For <code class="literal">REAL</code> functions:
</p><pre class="programlisting">double xxx(UDF_INIT *initid, UDF_ARGS *args,
char *is_null, char *error);
</pre><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 decimals. The default value is the maximum
number of decimals 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 decimals.
</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 decimals 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
column 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></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-aggr-calling"></a>24.2.3.2. UDF Calling Sequences for Aggregate Functions</h4></div></div></div><a class="indexterm" name="id3128066"></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="24.2.3. Adding a New User-Defined Function">Section 24.2.3, “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">char *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.0, 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">char *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.0.
</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, except for the first row. 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">char *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 non-aggregate UDF.
See <a href="extending-mysql.html#udf-calling" title="24.2.3.1. UDF Calling Sequences for Simple Functions">Section 24.2.3.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 non-aggregate UDF. See
<a href="extending-mysql.html#udf-return-values" title="24.2.3.4. UDF Return Values and Error Handling">Section 24.2.3.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 non-aggregate UDFs. See <a href="extending-mysql.html#udf-arguments" title="24.2.3.3. UDF Argument Processing">Section 24.2.3.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>24.2.3.3. UDF Argument Processing</h4></div></div></div><a class="indexterm" name="id3128409"></a><a class="indexterm" name="id3128416"></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></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>, and
<code class="literal">REAL_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>
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></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 non-constant 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 <code class="literal">SIN(3.14)</code>.
A non-constant argument is an expression that refers to
values that may change from row to row, such as column
names or functions that are called with non-constant
arguments.
</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>
Functions can refer to an argument <code class="literal">i</code> 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>. You
should not assume that strings are 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></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></ul></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-return-values"></a>24.2.3.4. UDF Return Values and Error Handling</h4></div></div></div><a class="indexterm" name="id3128745"></a><a class="indexterm" name="id3128755"></a><a class="indexterm" name="id3128765"></a><a class="indexterm" name="id3128776"></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">*result</code> and <code class="literal">*length</code> to
the contents and length of the return value. For example:
</p><pre class="programlisting">memcpy(result, "result string", 13);
*length = 13;
</pre><p>
The <code class="literal">result</code> buffer that is passed to the
<code class="literal">xxx()</code> function is 255 bytes long. If your
result fits in this, you don't have to worry about memory
allocation for results.
</p><p>
If your string function needs to return a string longer than
255 bytes, you must allocate the space for it 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="24.2.3.1. UDF Calling Sequences for Simple Functions">Section 24.2.3.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>24.2.3.5. Compiling and Installing User-Defined Functions</h4></div></div></div><a class="indexterm" name="id3128974"></a><a class="indexterm" name="id3128985"></a><a class="indexterm" name="id3128995"></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.cc</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.cc</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's 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
hostname.
</p></li><li><p>
<code class="literal">reverse_lookup()</code> returns the hostname
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.cc</code></strong>
</pre><p>
If you are using <span><strong class="command">gcc</strong></span>, 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.so</code></strong>
</pre><p>
You can easily determine the correct compiler options for your
system by running this command in the <code class="filename">sql</code>
directory of your MySQL source tree:
</p><pre class="programlisting">shell> <strong class="userinput"><code>make udf_example.o</code></strong>
</pre><p>
You should run a compile command similar to the one that
<span><strong class="command">make</strong></span> displays, except that you should
remove the <code class="option">-c</code> option near the end of the line
and add <code class="option">-o udf_example.so</code> to the end of the
line. (On some systems, you may need to leave the
<code class="option">-c</code> on the command.)
</p><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.cc</code> produces a file named
something like <code class="filename">udf_example.so</code> (the exact
name may vary from platform to platform). Copy this file to
some directory such as <code class="filename">/usr/lib</code> that
searched by your system's dynamic (runtime) linker, or add the
directory in which you placed the shared object to the linker
configuration file (for example,
<code class="filename">/etc/ld.so.conf</code>).
</p><p>
The dynamic linker name is system-specific (for example,
<span><strong class="command">ld-elf.so.1</strong></span> on FreeBSD,
<span><strong class="command">ld.so</strong></span> on Linux, or <span><strong class="command">dyld</strong></span>
on Mac OS X). Consult your system documentation for
information about the linker name and how to configure it.
</p><p>
On many systems, you can also set the
<code class="literal">LD_LIBRARY</code> or
<code class="literal">LD_LIBRARY_PATH</code> environment variable to
point at the directory where you have the files for your UDF.
The <code class="literal">dlopen</code> manual page tells you which
variable you should use on your system. You should set this in
<span><strong class="command">mysql.server</strong></span> or
<span><strong class="command">mysqld_safe</strong></span> startup scripts and restart
<span><strong class="command">mysqld</strong></span>.
</p><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 BitKeeper source repository for
MySQL 5.0. See
<a href="installing.html#installing-source-tree" title="2.8.3. Installing from the Development Source Tree">Section 2.8.3, “Installing from the Development Source Tree”</a>.
</p></li><li><p>
In the source repository, look in the
<code class="filename">VC++Files/examples/udf_example</code>
directory. There are files named
<code class="filename">udf_example.def</code>,
<code class="filename">udf_example.dsp</code>, and
<code class="filename">udf_example.dsw</code> there.
</p></li><li><p>
In the source repository, look in the
<code class="filename">sql</code> directory. Copy the
<code class="filename">udf_example.cc</code> from this directory to
the <code class="filename">VC++Files/examples/udf_example</code>
directory and rename the file to
<code class="filename">udf_example.cpp</code>.
</p></li><li><p>
Open the <code class="filename">udf_example.dsw</code> file with
Visual Studio VC++ and use it to compile the UDFs as a
normal project.
</p></li></ol></div><p>
After the shared object file has been installed, notify
<span><strong class="command">mysqld</strong></span> about the new functions with these
statements:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';</code></strong>
mysql> <strong class="userinput"><code>CREATE FUNCTION reverse_lookup</code></strong>
-> <strong class="userinput"><code>RETURNS STRING SONAME 'udf_example.so';</code></strong>
mysql> <strong class="userinput"><code>CREATE AGGREGATE FUNCTION avgcost</code></strong>
-> <strong class="userinput"><code>RETURNS REAL SONAME 'udf_example.so';</code></strong>
</pre><p>
Functions can be deleted using <code class="literal">DROP
FUNCTION</code>:
</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 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 <code class="literal">CREATE FUNCTION</code> and <code class="literal">DROP
FUNCTION</code> 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 <code class="literal">INSERT</code> and <code class="literal">DELETE</code>
privileges for the <code class="literal">mysql</code> database to create
and drop functions.
</p><p>
You should not use <code class="literal">CREATE FUNCTION</code> to add a
function that has previously been created. If you need to
reinstall a function, you should remove it with <code class="literal">DROP
FUNCTION</code> and then reinstall it with <code class="literal">CREATE
FUNCTION</code>. You would need to do this, for example, if
you recompile a new version of your function, so that
<span><strong class="command">mysqld</strong></span> 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
<code class="literal">CREATE FUNCTION</code> and not removed with
<code class="literal">DROP FUNCTION</code>. All active functions are
reloaded each time the server starts, unless you start
<span><strong class="command">mysqld</strong></span> with the
<code class="option">--skip-grant-tables</code> option. In this case, UDF
initialization is skipped and UDFs are unavailable.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h4 class="title"><a name="udf-security"></a>24.2.3.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 <code class="literal">INSERT</code> privilege to be
able to use <code class="literal">CREATE FUNCTION</code> and the
<code class="literal">DELETE</code> privilege to be able to use
<code class="literal">DROP FUNCTION</code>. 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. As of MySQL 5.0.3,
<span><strong class="command">mysqld</strong></span> supports an
<code class="option">--allow-suspicious-udfs</code> 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
<code class="option">--allow-suspicious-udfs</code> option. Otherwise,
you should avoid enabling this capability.
</p><p>
UDF object files cannot be placed in arbitrary directories.
They must be located in some system directory that the dynamic
linker is configured to search. To enforce this restriction
and prevent attempts at specifying pathnames outside of
directories searched by the dynamic linker, MySQL checks the
shared object file name specified in <code class="literal">CREATE
FUNCTION</code> statements for pathname delimiter
characters. As of MySQL 5.0.3, MySQL also checks for pathname
delimiters in filenames stored in the
<code class="literal">mysql.func</code> table when it loads functions.
This prevents attempts at specifying illegitimate pathnames
through direct manipulation of the
<code class="literal">mysql.func</code> table. For information about
UDFs and the runtime linker, see
<a href="extending-mysql.html#udf-compiling" title="24.2.3.5. Compiling and Installing User-Defined Functions">Section 24.2.3.5, “Compiling and Installing User-Defined Functions”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="adding-native-function"></a>24.2.4. Adding a New Native Function</h3></div></div></div><a class="indexterm" name="id3129691"></a><a class="indexterm" name="id3129702"></a><a class="indexterm" name="id3129712"></a><p>
The procedure for adding a new native function is described
here. Note that you cannot add native functions to a binary
distribution because the procedure involves modifying MySQL
source code. You must compile MySQL yourself from a source
distribution. Also note that if you migrate to another version
of MySQL (for example, when a new version is released), you need
to repeat the procedure with the new version.
</p><p>
To add a new native MySQL function, follow these steps:
</p><div class="orderedlist"><ol type="1"><li><p>
Add one line to <code class="filename">lex.h</code> that defines the
function name in the <code class="literal">sql_functions[]</code>
array.
</p></li><li><p>
If the function prototype is simple (just takes zero, one,
two or three arguments), you should in
<code class="filename">lex.h</code> specify
<code class="literal">SYM(FUNC_ARG<em class="replaceable"><code>N</code></em>)</code>
(where <em class="replaceable"><code>N</code></em> is the number of
arguments) as the second argument in the
<code class="literal">sql_functions[]</code> array and add a function
that creates a function object in
<code class="filename">item_create.cc</code>. Take a look at
<code class="literal">"ABS"</code> and
<code class="literal">create_funcs_abs()</code> for an example of
this.
</p><p>
If the function prototype is complicated (for example, if it
takes a variable number of arguments), you should add two
lines to <code class="filename">sql_yacc.yy</code>. One indicates the
preprocessor symbol that <span><strong class="command">yacc</strong></span> should
define (this should be added at the beginning of the file).
Then define the function parameters and add an
“<span class="quote">item</span>” with these parameters to the
<code class="literal">simple_expr</code> parsing rule. For an example,
check all occurrences of <code class="literal">ATAN</code> in
<code class="filename">sql_yacc.yy</code> to see how this is done.
</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>
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. You can take a 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, don't 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 some
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>24.3. Adding New Procedures to MySQL</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="extending-mysql.html#procedure-analyse">24.3.1. Procedure Analyse</a></span></dt><dt><span class="section"><a href="extending-mysql.html#writing-a-procedure">24.3.2. Writing a Procedure</a></span></dt></dl></div><a class="indexterm" name="id3130057"></a><a class="indexterm" name="id3130067"></a><a class="indexterm" name="id3130077"></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, we recommend that you take a look at
<code class="literal">mylua</code>. With this you can use the LUA language
to load a procedure at runtime into <span><strong class="command">mysqld</strong></span>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="procedure-analyse"></a>24.3.1. Procedure Analyse</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>
This procedure is defined in the
<code class="filename">sql/sql_analyse.cc</code>. This examines the
result from your query and returns an analysis of the results:
</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 <code class="literal">analyse</code>
does notice per column. This is used by
<code class="literal">analyse</code> to check whether the optimal
column type should be of type <code class="literal">ENUM</code>.
</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><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></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="writing-a-procedure"></a>24.3.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><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="connectors.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="problems.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 23. Connectors </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Appendix A. Problems and Common Errors</td></tr></table></div></body></html>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-release
>
by-pkgid
>
ebb1914cf182a88528b4547490db1dd8
>
files
>
349
