Files implementing UDFs must be compiled and installed on the
host where the server runs. This process is described below
for the example UDF file
sql/udf_example.cc that is included in
the MySQL source distribution.
If a UDF will be referred to in statements that will be replicated to slave servers, you must ensure that every slave also has the function available. Otherwise, replication will fail on the slaves when they attempt to invoke the function.
The immediately following instructions are for Unix. Instructions for Windows are given later in this section.
udf_example.cc file contains the
metaphon() returns a metaphon string of
the string argument. This is something like a soundex
string, but it is more tuned for English.
myfunc_double() returns the sum of the
ASCII values of the characters in its arguments, divided
by the sum of the length of its arguments.
myfunc_int() returns the sum of the
length of its arguments.
sequence([const int]) returns a
sequence starting from the given number or 1 if no number
has been given.
lookup() returns the IP address for a
reverse_lookup() returns the host name
for an IP address. The function may be called either with
a single string argument of the form
'xxx.xxx.xxx.xxx' or with four numbers.
avgcost() returns an average cost. This
is an aggregate function.
A dynamically loadable file should be compiled as a sharable object file, using a command something like this:
gcc -shared -o udf_example.so udf_example.cc
If you are using gcc, you should be able to
udf_example.so with a simpler
You can easily determine the correct compiler options for your
system by running this command in the
directory of your MySQL source tree:
You should run a compile command similar to the one that
make displays, except that you should
-c option near the end of the line
-o udf_example.so to the end of the
line. (On some systems, you may need to leave the
-c on the command.)
After you compile a shared object containing UDFs, you must
install it and tell MySQL about it. Compiling a shared object
udf_example.cc produces a file named
udf_example.so (the exact
name may vary from platform to platform).
As of MySQL 4.1.25, copy the shared object to server's plugin
directory and name it
This directory is given by the value of the
plugin_dir system variable.
Prior to MySQL 4.1.25, or if the value of
plugin_dir is empty, the
shared object should be placed in a directory such as
/usr/lib that is searched by your
system's dynamic (runtime) linker, or you can add the
directory in which you place the shared object to the linker
configuration file (for example,
On many systems, you can also set the
LD_LIBRARY_PATH environment variable to
point at the directory where you have the files for your UDF.
You should set the variable in mysql.server
or mysqld_safe startup scripts and restart
mysqld. You might do this if you want to
place the object file in a directory accessible only to the
server and not in a public directory. The
dlopen manual page tells you which variable
to use on your system.
The dynamic linker name is system-specific (for example, ld-elf.so.1 on FreeBSD, ld.so on Linux, or dyld on Mac OS X). Consult your system documentation for information about the linker name and how to configure it.
On some systems, the ldconfig program that
configures the dynamic linker does not recognize a shared
object unless its name begins with
this case you should rename a file such as
On Windows, you can compile user-defined functions by using the following procedure:
Obtain the development source for MySQL 4.1. See Section 2.1.3, “How to Get MySQL”.
In the source repository, look in the
directory. There are files named
In the source tree, look in the
directory. Copy the
from this directory to the
directory and rename the file to
udf_example.dsw file with
Visual Studio VC++ and use it to compile the UDFs as a
After the shared object file has been installed, notify
mysqld about the new functions with the
following statements. If object files have a suffix different
.so on your system, substitute the
correct suffix throughout (for example,
.dll on Windows).
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';mysql>
CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.so';mysql>
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.so';mysql>
CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.so';mysql>
CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';mysql>
CREATE FUNCTION reverse_lookup->
RETURNS STRING SONAME 'udf_example.so';mysql>
CREATE AGGREGATE FUNCTION avgcost->
RETURNS REAL SONAME 'udf_example.so';
To delete functions, use
DROP FUNCTION metaphon;mysql>
DROP FUNCTION myfunc_double;mysql>
DROP FUNCTION myfunc_int;mysql>
DROP FUNCTION sequence;mysql>
DROP FUNCTION lookup;mysql>
DROP FUNCTION reverse_lookup;mysql>
DROP FUNCTION avgcost;
CREATE FUNCTION and
DROP FUNCTION statements update
func system table in the
mysql database. The function's name, type
and shared library name are saved in the table. You must have
DELETE privilege for the
mysql database to create or drop functions,
You should not use
FUNCTION to add a function that has previously been
created. If you need to reinstall a function, you should
remove it with
and then reinstall it with
FUNCTION. You would need to do this, for example, if
you recompile a new version of your function, so that
mysqld gets the new version. Otherwise, the
server continues to use the old version.
An active function is one that has been loaded with
CREATE FUNCTION and not removed
DROP FUNCTION. All active
functions are reloaded each time the server starts, unless you
start mysqld with the
--skip-grant-tables option. In
this case, UDF initialization is skipped and UDFs are