You can add the function through the user-definable function (UDF)
interface. User-definable functions are added and removed dynamically using
the CREATE FUNCTION and DROP FUNCTION statements.
See section 9.1.1 CREATE FUNCTION/DROP FUNCTION Syntax.

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

Each method has advantages and disadvantages:

If you write a user-definable function, you must install the object file
in addition to the server itself. If you compile your function into the
server, you don't need to do that.

You can add UDFs to a binary MySQL distribution. Native functions
require you to modify a source distribution.

If you upgrade your MySQL distribution, you can continue to use your
previously installed UDFs. For native functions, you must repeat your
modifications each time you upgrade.

Whichever method you use to add new functions, they may be used just like
native functions such as ABS() or SOUNDEX().

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

AGGREGATE is a new option for MySQL Version 3.23. An
AGGREGATE function works exactly like a native MySQL
GROUP function like SUM or COUNT().

CREATE FUNCTION saves the function's name, type, and shared library
name in the mysql.func system table. You must have the
insert and delete privileges for the mysql database
to create and drop functions.

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.
(An active function is one that has been loaded with CREATE FUNCTION
and not removed with DROP FUNCTION.)

For instructions on writing user-definable functions, see section 9.1 Adding New Functions to MySQL. 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 mysqld dynamically (not statically).

For the UDF mechanism to work, functions must be written in C or C++ and your
operating system must support dynamic loading. The MySQL source
distribution includes a file `sql/udf_example.cc' that defines 5 new
functions. Consult this file to see how UDF calling conventions work.

For mysqld to be able to use UDF functions, you should configure MySQL
with --with-mysqld-ldflags=-rdynamic The reason is that to on
many platforms (including Linux) you can load a dynamic library (with
dlopen()) from a static linked program, which you would get if
you are using --with-mysqld-ldflags=-all-static If you want to
use an UDF that needs to access symbols from mysqld (like the
methaphone example in `sql/udf_example.cc' that uses
default_charset_info), you must link the program with
-rdynamic. (see man dlopen).

For each function that you want to use in SQL statements, you should define
corresponding C (or C++) functions. In the discussion below, the name
``xxx'' is used for an example function name. To distinquish between SQL and
C/C++ usage, XXX() (uppercase) indicates a SQL function call, and
xxx() (lowercase) indicates a C/C++ function call.

The C/C++ functions that you write to implement the interface for
XXX() are:

xxx() (required)

The main function. This is where the function result is computed.
The correspondence between the SQL type and return type of your C/C++
function is shown below:

SQL type

C/C++ type

STRING

char *

INTEGER

long long

REAL

double

xxx_init() (optional)

The initialization function for xxx(). It can be used to:

Check the number of arguments to XXX().

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.

Allocate any memory required by the main function.

Specify the maximum length of the result.

Specify (for REAL functions) the maximum number of decimals.

Specify whether or not the result can be NULL.

xxx_deinit() (optional)

The deinitialization function for xxx(). It should deallocate any
memory allocated by the initialization function.

When a SQL statement invokes XXX(), MySQL calls the
initialization function xxx_init() to let it perform any required
setup, such as argument checking or memory allocation. If xxx_init()
returns an error, the SQL statement is aborted with an error message and the
main and deinitialization functions are not called. Otherwise, the main
function xxx() is called once for each row. After all rows have been
processed, the deinitialization function xxx_deinit() is called so it
can perform any required cleanup.

All functions must be thread safe (not just the main function,
but the initialization and deinitialization functions as well). This means
that you are not allowed to allocate any global or static variables that
change! If you need memory, you should allocate it in xxx_init()
and free it in xxx_deinit().

The main function should be declared as shown below. Note that the return
type and parameters differ, depending on whether you will declare the SQL
function XXX() to return STRING, INTEGER, or REAL
in the CREATE FUNCTION statement:

The initid parameter is passed to all three functions. It points to a
UDF_INIT structure that is used to communicate information between
functions. The UDF_INIT structure members are listed below. The
initialization function should fill in any members that it wishes to change.
(To use the default for a member, leave it unchanged.):

my_bool maybe_null

xxx_init() should set maybe_null to 1 if xxx()
can return NULL. The default value is 1 if any of the
arguments are declared maybe_null.

unsigned int decimals

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 1.34, 1.345, and 1.3, the default would be 3,
because 1.345 has 3 decimals.

unsigned int max_length

The maximum length of the string result. The default 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 initid->decimals. (For numeric functions, the length
includes any sign or decimal point characters.)

char *ptr

A pointer that the function can use for its own purposes. For example,
functions can use initid->ptr to communicate allocated memory
between functions. In xxx_init(), allocate the memory and assign it
to this pointer:

initid->ptr = allocated_memory;

In xxx() and xxx_deinit(), refer to initid->ptr to use
or deallocate the memory.

The types for each argument. The possible type values are
STRING_RESULT, INT_RESULT, and REAL_RESULT.
To make sure that arguments are of a given type and return an
error if they are not, check the arg_type array in the initialization
function. For example:

As an alternative to requiring your function's arguments to be of particular
types, you can use the initialization function to set the arg_type
elements to the types you want. This causes MySQL to coerce
arguments to those types for each call to xxx(). For example, to
specify coercion of the first two arguments to string and integer, do this in
xxx_init():

args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = INT_RESULT;

char **args

args->args communicates information to the initialization function
about the general nature of the arguments your function was called with. For a
constant argument i, args->args[i] points to the argument
value. (See below for instructions on how to access the value properly.)
For a non-constant argument, args->args[i] is 0.
A constant argument is an expression that uses only constants, such as
3 or 4*7-2 or SIN(3.14). A non-constant argument is an
expression that refers to values that may change from row to row, such as
column names or functions that are called with non-constant arguments.
For each invocation of the main function, args->args contains the
actual arguments that are passed for the row currently being processed.
Functions can refer to an argument i as follows:

An argument of type STRING_RESULT is given as a string pointer plus a
length, to allow handling of binary data or data of arbitrary length. The
string contents are available as args->args[i] and the string length
is args->lengths[i]. You should not assume that strings are
null-terminated.

For an argument of type INT_RESULT, you must cast
args->args[i] to a long long value:

long long int_val;
int_val = *((long long*) args->args[i]);

For an argument of type REAL_RESULT, you must cast
args->args[i] to a double value:

double real_val;
real_val = *((double*) args->args[i]);

unsigned long *lengths

For the initialization function, the lengths array indicates the
maximum string length for each argument. For each invocation of the main
function, lengths contains the actual lengths of any string arguments
that are passed for the row currently being processed. For arguments of
types INT_RESULT or REAL_RESULT, lengths still contains
the maximum length of the argument (as for the initialization function).

The initialization function should return 0 if no error occurred and
1 otherwise. If an error occurs, xxx_init() should store a
null-terminated error message in the message parameter. The message
will be returned to the client. The message buffer is
MYSQL_ERRMSG_SIZE characters long, but you should try to keep the
message to less than 80 characters so that it fits the width of a standard
terminal screen.

The return value of the main function xxx() is the function value, for
long long and double functions. A string functions should
return a pointer to the result and store the length of the string in the
length arguments. result is a buffer at least 255 bytes long.
Set these to the contents and length of the return value. For example:

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

If your string functions that needs to return a string longer than 255
bytes, you must allocate the space for it with malloc() in your
xxx_init() function or your xxx() function and free it in
your xxx_deinit() function. You can store the allocated memory
in the ptr slot in the UDF_INIT structure for reuse by
future xxx() calls. See section 9.1.2.1 UDF Calling Sequences.

To indicate a return value of NULL in the main function, set
is_null to 1:

*is_null = 1;

To indicate an error return in the main function, set the error
parameter to 1:

*error = 1;

If xxx() sets *error to 1 for any row, the function
value is NULL for the current row and for any subsequent rows
processed by the statement in which XXX() was invoked. (xxx()
will not even be called for subsequent rows.) NOTE: In
MySQL versions prior to 3.22.10, you should set both *error
and *is_null:

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
`udf_example.cc' that is included in the MySQL source
distribution. This file contains the following functions:

metaphon() returns a metaphon string of the string argument.
This is something like a soundex string, but it's more tuned for English.

myfunc_double() returns the sum of the ASCII values of the
characters in its arguments, divided by the sum of the length of its arguments.

myfunc_int() returns the sum of the length of its arguments.

sequence([const int]) returns an sequence starting from the given
number or 1 if no number has been given.

lookup() returns the IP number for a hostname.

reverse_lookup() returns the hostname for an IP number.
The function may be called with a string "xxx.xxx.xxx.xxx" or
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 myfunc.cc

You can easily find out 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.)

Once 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 searched by ld, such as
`/usr/lib'. On many systems, you can set the LD_LIBRARY or
LD_LIBRARY_PATH environment variable to point at the directory where
you have your UDF function files. The dlopen manual page tells you
which variable you should use on your system. You should set this in
mysql.server or safe_mysqld and restart mysqld.

After the library is installed, notify mysqld about the new
functions with these commands:

The CREATE FUNCTION and DROP FUNCTION statements update the
system table func 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 already
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
will continue to use the old version.

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. (An active function is
one that has been loaded with CREATE FUNCTION and not removed with
DROP FUNCTION.)

The procedure for adding a new native function is described below. 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 will need to repeat the procedure
with the new version.

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

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

If the function prototype is simple (just takes zero, one, two or three
arguments), you should in lex.h specify SYM(FUNC_ARG#) (where # is the
number of arguments) as the second argument in the
sql_functions[] array and add a function that creates a function
object in `item_create.cc'. Take a look at "ABS" and
create_funcs_abs() for an example of this.
If the function prototype is complicated (for example takes a variable number
of arguments), you should add two lines to `sql_yacc.yy'. One
indicates the preprocessor symbol that yacc should define (this
should be added at the beginning of the file). Then define the function
parameters and add an ``item'' with these parameters to the
simple_expr parsing rule. For an example, check all occurrences
of ATAN in `sql_yacc.yy' to see how this is done.

In `item_func.h', declare a class inheriting from Item_num_func or
Item_str_func, depending on whether your function returns a number or a
string.

In `item_func.cc', add one of the following declarations, depending
on whether you are defining a numeric or string function:

If you inherit your object from any of the standard items (like
Item_num_func you probably only have to define one of the above
functions and let the parent object take care of the other functions.
For example, the Item_str_func class defines a val() function
that executes atof() on the value returned by ::str().

You should probably also define the following object function:

void Item_func_newname::fix_length_and_dec()

This function should at least calculate max_length based on the
given arguments. max_length is the maximum number of characters
the function may return. This function should also set maybe_null
= 0 if the main function can't return a NULL value. The
function can check if any of the function arguments can return
NULL by checking the arguments maybe_null variable. You
can take a look at Item_func_mod::fix_length_and_dec for a
typical example of how to do this.

All functions must be thread safe (In other words, don't use any global or
static variables in the functions without protecting them with mutexes).

If you want to return NULL, from ::val(), ::val_int()
or ::str() you should set null_value to 1 and return 0.

For ::str() object functions, there are some additional
considerations to be aware of:

The String *str argument provides a string buffer that may be
used to hold the result. (For more information about the String type,
take a look at the `sql_string.h' file.)

The ::str() function should return the string that holds the result or
(char*) 0 if the result is NULL.

All current string functions try to avoid allocating any memory unless
absolutely necessary!

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 row-by-row or GROUP BY level.

We have created an example procedure in MySQL Version 3.23 to
show you what can be done.

Additionally we recommend you to take a look at 'mylua', which you can find in the Contrib directory. See section D Contributed Programs. Which this you can use the LUA
language to load a procedure at runtime into mysqld.

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 See section 2.3.4 Installing from the Development Source Tree. If you are interested in MySQL
internals, you should also subscribe to internals@lists.mysql.com.
This is a relatively low traffic list, in comparison with
mysql@lists.mysql.com.

The TCP/IP connection thread handles all connection requests and
creates a new dedicated thread to handle the authentication and
and SQL query processing for each connection.

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.

The signal thread handles all signals. This thread also normally handles
alarms and calls process_alarm() to force timeouts on connections
that have been idle too long.

If mysqld is compiled with -DUSE_ALARM_THREAD, a dedicated
thread that handles alarms is created. This is only used on some systems where
there are problems with sigwait() or if one wants to use the
thr_alarm() code in ones application without a dedicated signal
handling thread.

If one uses the --flush_time=# option, a dedicated thread is created
to flush all tables at the given interval.

Every connection has its own thread.

Every different table on which one uses INSERT DELAYED gets its
own thread.

If you use --master-host, a slave replication thread will be
started to read and apply updates from the master.

Until recently, our main full-coverage test suite was based on proprietary
customer data and for that reason has not been publicly available. The only
publicly available part of our testing process consisted of the crash-me
test, a Perl DBI/DBD benchmark found in the sql-bench directory, and
miscellaneous tests located in tests directory. The lack of a
standardized publicly available test suite has made it difficult for our users,
as well developers, to do regression tests on the MySQL code. To
address this problem, we have created a new test system that is included in
the source and binary distributions starting in Version 3.23.29.

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, as this will ensure
that all future MySQL releases will work well with your
applications.

The test system consist of a test language interpreter
(mysqltest), a shell script to run all
tests(mysql-test-run), the actual test cases written in a special
test language, and their expected results. To run the test suite on
your system after a build, type make test or
mysql-test/mysql-test-run from the source root. If you have
installed a binary distribution, cd to the install root
(eg. /usr/local/mysql), and do scripts/mysql-test-run.
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 section 9.3.2.3 Reporting Bugs in the MySQL Test Suite.

If you have a copy of mysqld running on the machine where you want to
run the test suite you do not have to stop it, as long as it is not using
ports 9306 and 9307. If one of those ports is taken, you should
edit mysql-test-run and change the values of the master and/or slave
port to one that is available.

You can run one individual test case with
mysql-test/mysql-test-run test_name.

If one test fails, you should test running mysql-test-run with
the --force option to check if any other tests fails.

You can use the mysqltest language to write your own test cases.
Unfortunately, we have not yet written full documentation for it - we plan to
do this shortly. You can, however, look at our current test cases and use
them as an example. The following points should help you get started:

The tests are located in mysql-test/t/*.test

A test case consists of ; terminated statements and is similar to the
input of mysql command line client. A statement by default is a query
to be sent to MySQL server, unless it is recognized as internal
command ( eg. sleep ).

All queries that produce results, e.g. SELECT, SHOW,
EXPLAIN, etc., must be preceded with @/path/to/result/file. The
file must contain the expected results. An easy way to generate the result
file is to run mysqltest -r < t/test-case-name.test from
mysql-test 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
the fields are separated with a hard tab, and there is a hard tab at the end.
You may want to use od -c to make sure your text editor has not messed
anything up during edit. We, of course, hope that you will never have to edit
the output of mysqltest -r as you only have to do it when you find a
bug.

To be consistent with our setup, you should put your result files in
mysql-test/r directory and name them test_name.result. If the
test produces more than one result, you should use test_name.a.result,
test_name.b.result, etc.

If a statement returns an error, you should on the line before the statement
specify with the --error error-number. The error number can be
a list of possible error numbers separated with ','.

If you are writing a replication test case, you should on the first line of
the test file, put source include/master-slave.inc;. To switch between
master and slave, use connection master; and connection slave;.
If you need to do something on an alternate connection, you can do
connection master1; for the master, and connection slave1; for
the slave.

If you need to do something in a loop, you can use something like this:

let $1=1000;
while ($1)
{
# do your queries here
dec $1;
}

To sleep between queries, use the sleep command. It supports fractions
of a second, so you can do sleep 1.3;, for example, to sleep 1.3
seconds.

To run the slave with additional options for your test case, put them
in the command-line format in mysql-test/t/test_name-slave.opt. For
the master, put them in mysql-test/t/test_name-master.opt.

If you have a question about the test suite, or have a test case to contribute,
e-mail to internals@lists.mysql.com. As the list does not accept
attachments, you should ftp all the relevant files to:
ftp://support.mysql.com/pub/mysql/Incoming

If your MySQL version doesn't pass the test suite you should
do the following:

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 mysqlbug script
so that we can get information about your system and MySQL
version. See section 1.2.22.3 How to Report Bugs or Problems.

Make sure to include the output of mysql-test-run, as well as
contents of all .reject files in mysql-test/r directory.

If a test in the test suite fails, check if the test fails also when run
by its own:

cd mysql-test
mysql-test-run --local test-name

If this fails, then you should configure MySQL with
--with-debug and run mysql-test-run with the
--debug option. If this also fails send the trace file
`var/tmp/master.trace' to ftp://support.mysql.com/pub/mysql/secret
so that we can examine it. Please remember to also include a full
description of your system, the version of the mysqld binary and how you
compiled it.

Try also to run mysql-test-run with the --force option to
see if there is any other test that fails.

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
http://www.mysql.com/downloads/. All our standard binaries should
pass the test suite !

If you get an error, like Result length mismatch or Result
content mismatch 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 mysqld version produces slight different results under some
circumstances.
Failed test results are put in a file with the same base name as the
result file with the .reject 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 od -c and also check their
lengths.

If a test fails totally, you should check the logs file in the
mysql-test/var/log directory for hints of what went wrong.

If you have compiled MySQL with debugging you can try to debug this
by running mysql-test-run with the --gdb and/or --debug
options.
See section G.1.2 Creating trace files.
If you have not compiled MySQL for debugging you should probably
do that. Just specify the --with-debug options to configure!
See section 2.3 Installing a MySQL Source Distribution.