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.
The immediately following instructions are for Unix. Instructions for Windows are given later in this section.
The udf_example.cc
file contains the
following functions:
metaphon()
returns a metaphon string of
the string argument. This is something like a soundex
string, but it is more tuned for English.
myfunc_double()
returns the sum of the
ASCII values of the characters in its arguments, divided
by the sum of the length of its arguments.
myfunc_int()
returns the sum of the
length of its arguments.
sequence([const int])
returns a
sequence starting from the given number or 1 if no number
has been given.
lookup()
returns the IP number for a
host name.
reverse_lookup()
returns the host name
for an IP number. The function may be called either with a
single string argument of the form
'xxx.xxx.xxx.xxx'
or with four numbers.
A dynamically loadable file should be compiled as a sharable object file, using a command something like this:
shell> gcc -shared -o udf_example.so udf_example.cc
If you are using gcc, you should be able to
create udf_example.so
with a simpler
command:
shell> make udf_example.so
You can easily determine the correct compiler options for your
system by running this command in the sql
directory of your MySQL source tree:
shell> make udf_example.o
You should run a compile command similar to the one that
make displays, except that you should
remove the -c
option near the end of the line
and add -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
from udf_example.cc
produces a file named
something like udf_example.so
(the exact
name may vary from platform to platform). Copy this file to
some directory such as /usr/lib
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,
/etc/ld.so.conf
).
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 many systems, you can also set the
LD_LIBRARY
or
LD_LIBRARY_PATH
environment variable to
point at the directory where you have the files for your UDF.
The dlopen
manual page tells you which
variable you should use on your system. You should set this in
mysql.server or
mysqld_safe startup scripts and restart
mysqld.
On some systems, the ldconfig program that
configures the dynamic linker does not recognize a shared
object unless its name begins with lib
. In
this case you should rename a file such as
udf_example.so
to
libudf_example.so
.
On Windows, you can compile user-defined functions by using the following procedure:
You need to obtain the Bazaar source repository for MySQL 4.0 or higher. See Section 2.9.3, “Installing from the Development Source Tree”.
In the source repository, look in the
VC++Files/examples/udf_example
directory. There are files named
udf_example.def
,
udf_example.dsp
, and
udf_example.dsw
there.
In the source repository, look in the
sql
directory. Copy the
udf_example.cc
from this directory to
the VC++Files/examples/udf_example
directory and rename the file to
udf_example.cpp
.
Open the udf_example.dsw
file with
Visual Studio VC++ and use it to compile the UDFs as a
normal project.
After the shared object file has been installed, notify mysqld about the new functions with these statements:
mysql>CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.dll';
mysql>CREATE FUNCTION myfunc_double RETURNS REAL SONAME 'udf_example.dll';
mysql>CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME 'udf_example.dll';
mysql>CREATE FUNCTION sequence RETURNS INTEGER SONAME 'udf_example.dll';
mysql>CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.dll';
mysql>CREATE FUNCTION reverse_lookup
->RETURNS STRING SONAME 'udf_example.dll';
mysql>CREATE AGGREGATE FUNCTION avgcost
->RETURNS REAL SONAME 'udf_example.dll';
Functions can be deleted using DROP
FUNCTION
:
mysql>DROP FUNCTION metaphon;
mysql>DROP FUNCTION myfunc_double;
mysql>DROP FUNCTION myfunc_int;
mysql>DROP FUNCTION sequence;
mysql>DROP FUNCTION lookup;
mysql>DROP FUNCTION reverse_lookup;
mysql>DROP FUNCTION avgcost;
The CREATE FUNCTION
and
DROP FUNCTION
statements update
the func
system table in the
mysql
database. The function's name, type
and shared library name are saved in the table. You must have
the INSERT
and
DELETE
privileges for the
mysql
database to create and drop
functions.
You should not use CREATE
FUNCTION
to add a function that has previously been
created. If you need to reinstall a function, you should
remove it with DROP FUNCTION
and then reinstall it with CREATE
FUNCTION
. You would need to do this, for example, if
you recompile a new version of your function, so that
mysqld gets the new version. Otherwise, the
server continues to use the old version.
An active function is one that has been loaded with
CREATE FUNCTION
and not removed
with DROP FUNCTION
. All active
functions are reloaded each time the server starts, unless you
start mysqld with the
--skip-grant-tables
option. In
this case, UDF initialization is skipped and UDFs are
unavailable.
If the new function will be referred to in statements that will be replicated to slave servers, you must ensure that every slave server also has the function available. Otherwise, replication will fail on the slaves when they attempt to invoke the function.
User Comments
Helpful hint for debugging: fprintf(stderr,"Hello world"); anything printed to stderr will go to the mysql error log.
%lld is the proper format for printing long longs.
After much time trying to track a problem on solaris(2.7, gcc 2.9-gnupro-99r1) I found that I needed -c -shared as compiler options. Almost everything worked without the -c, but strangely, any time I tried to use double quoted strings in the code they got turned into empty strings.
On Mac OS X 10.4 (Tiger), the following incantations were needed, in
order to build a "shared object" file.
export MACOSX_DEPLOYMENT_TARGET="10.4"
gcc -c myfunction.c
gcc -bundle -o myfunction.so myfunction.o -undefined dynamic_lookup
I wanted to install the UDF to compute the levenshtein distance between two strings.
I put here all the commands i used, from scratch (both on the server and my mind), to make it running.
I had several little problems that you can't really solve without knowing some linux tips.
This is different from what I saw in this doc.
Here is the command for a debian sarge stable server.
>apt-get source mysql-server
>locate udf_example.c
>cd /usr/share/doc/libmysqlclient14-dev/examples
>gunzip udf_example.cc.gz
>gcc -shared -o udf_example.so udf_example.cc
>locate mysql.h
>gcc -shared -o udf_example.so udf_example.cc -I/usr/include/mysql/
>ll
>mv udf_example.so /usr/lib/libudf_example.so
#at this step i'm happy because the example from mysql works (note that i had to add the -I for the path and change the file extension to .cc)
# now i download the package from the mysql udf repository
# http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/
>wget http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/dludf.cgi?ckey=28
>ll
>tar -xzvf dludf.cgi\?ckey\=28
>gcc -shared -o libmysqllevenshtein.so mysqllevenshtein.cc -I/usr/include/mysql/
>mv libmysqllevenshtein.so /usr/lib
#Then i launch mysql
>mysql -uroot -pPASS
# in mysql
mysql> use DATABASE
Database changed
mysql> CREATE FUNCTION levenshtein RETURNS INT SONAME 'libmysqllevenshtein.so';
mysql> select levenshtein(w1.word,w2.word) as dist from word w1, word w2 where ETC........... order by dist asc limit 0,10;
Enjoy :-)
To compile mysqllevenshtein (as it happens, though I imagine this would be the case with other functions) on Ubuntu Dapper Drake, I needed to add the -fPIC compiler flag:
gcc -shared -o mysqllevenshtein.so mysqllevenshtein.cc \
-I/usr/include/mysql/ -fPIC
/afb
To clarify the Mac OS X example slightly: I use the following commands to build and install the example UDF on OS X 10.4. These should be executed in the directory where udf_example.c is stored.
# On OS X, use "-bundle" instead of "-shared",
# and specify where the mysql include files are
# (they should be in your main mysql directory).
gcc -bundle -o udf_example.so udf_example.c -I/usr/local/mysql/include
# MySQL will look for the shared library in /usr/lib, so put it there:
sudo cp udf_example.so /usr/lib
If you're still in trouble with the levenshtein function, here you can find a manual concerning the installation of the levenshtein group function: http://www.teamarbyte.de/levenshtein.html
It's in German, but I hope it helps anyway.
Add your own comment.