|
|
Table of Contents
This chapter describes a lot of things that you need to know when
working on the MySQL code. If you plan to contribute to MySQL
development, want to have access to the bleeding-edge versions of
the code, or just want to keep track of development, follow the
instructions in Section 2.8.3, “Installing from the Development Source Tree”. If you
are interested in MySQL internals, you should also subscribe to
our internals
mailing list. This list has
relatively low traffic. For details on how to subscribe, please
see Section 1.7.1, “MySQL Mailing Lists”. All developers at MySQL AB
are on the internals
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!
The MySQL server creates the following threads:
One thread manages TCP/IP file connection requests and creates a new dedicated thread to handle the authentication and SQL statement processing for each connection. (On Unix, this thread also manages Unix socket file connection requests.) On Windows, a similar thread manages shared-memory connection requests, and on Windows NT-based systems, a thread manages named-pipe connection requests. Every client connection has its own thread, although the manager threads try to avoid creating threads by consulting the thread cache first to see whether a cached thread can be used for a new 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.
On a master replication server, slave server connections are like client connections: There is one thread per connected slave.
On a slave replication server, an I/O thread is started to connect to the master server and read updates from it. An SQL thread is started to apply updates read from the master. These two threads run independently and can be started and stopped independently.
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 you want to use the thr_alarm()
code in your application without a dedicated signal handling
thread.
If the server is started with the
--flush_time=
option, a dedicated thread is created to flush all tables
every val
val
seconds.
Each table for which INSERT DELAYED
statements are issued gets its own thread.
mysqladmin processlist only shows the
connection, INSERT DELAYED
, and replication
threads.
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.
The current set of test cases doesn't test everything in MySQL, but it should catch most obvious bugs in the SQL processing code, operating system or library issues, and is quite thorough in testing replication. Our goal is to have the tests cover 100% of the code. We welcome contributions to our test suite. You may especially want to contribute tests that examine the functionality critical to your system because this ensures that all future MySQL releases work well with your applications.
The test system consists 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 directory. If you have installed a binary distribution,
change location to the installation root directory (for
example, /usr/local/mysql
), and run
mysql-test/mysql-test-run. All tests should
succeed. If any do not, you should try to find out why and
report the problem if it indicates a bug in MySQL. See
Section 24.1.2.3, “How to Report 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
or 9307
. If either
of those ports is taken, you should edit
mysql-test-run and change the values of the
master 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 run
mysql-test-run with the
--force
option to check whether any other
tests fail.
You can use the mysqltest 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:
The tests are located in
mysql-test/t/*.test
A test case consists of statements terminated by
;
and is similar to the input of
mysql command-line client. A statement
by default is an SQL statement to be sent to MySQL server,
unless it is recognized as an internal
mysqltest command (for example,
sleep).
All statements that produce results—for example,
SELECT
, SHOW
, or
EXPLAIN
—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_name
.test from
the 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 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 od -c to
make sure that your text editor has not messed up anything
during editing. We hope that you never have to edit the
output of mysqltest -r because this
should be necessary only when you find a bug.
To be consistent with our setup, you should put your
result files in the mysql-test/r
directory and name them
.
If the test produces more than one result, you should use
test_name
.result
,
test_name
.a.result
,
and so forth.
test_name
.b.result
If a statement is expected to return an error, you should
specify it with --error
on the
line before the statement. You can specify more than one
error number, separated by commas.
error_number
If you are writing a replication test case, the first line
of the test file should be 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
alternative 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 statements, use the sleep command. It supports fractions of a second. For example, sleep 1.3; sleeps 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/
.
For the master, put them in
test_name
-slave.optmysql-test/t/
.
test_name
-master.opt
If you have a question about the test suite, or have a
test case to contribute, send an email message to the
MySQL internals
mailing list. See
Section 1.7.1, “MySQL Mailing Lists”. This list does not accept
attachments, so you should FTP all the relevant files to:
ftp://ftp.mysql.com/pub/mysql/upload/
If test cases from the test suite fail, you should do the following:
Do not file a bug report before you have found out as much as possible about what when wrong. See the instructions at Section 1.8, “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 the
mysql-test/r
directory.
Check whether an individual test in the test suite also fails when run on its own:
cd mysql-test
mysql-test-run --local test_name
If this fails, you should configure MySQL with
--with-debug
and run
mysql-test-run with the
--debug
option. If this also fails, send
the trace file
mysql-test/var/tmp/master.trace
to
ftp://ftp.mysql.com/pub/mysql/upload/ 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.
Run mysql-test-run with the
--force
option to see whether any other
tests fail.
If you have compiled MySQL yourself, check our manual to see whether there are any platform-specific issues for your system (see Section 2.12, “Operating System-Specific Notes”). There might be configuration workarounds to deal with the problems that you observe. Also, consider using one of the binaries we have compiled for you at http://dev.mysql.com/downloads/. All our standard binaries should pass the test suite!
If you get an error such as Result length
mismatch
or Result content
mismatch
it means that the output of the test
was not an exact match for the expected output. This could
be a bug in MySQL or it could be that your version of
mysqld produces slightly different
results under some circumstances.
The results file is located in the r
directory and has a name with a
.result
extension. A failed test
result is put in a file with the same basename as the
result file and a .reject
extension.
If your test case is failing, you should use
diff to compare the
.result
and
.reject
files. If you cannot see how
they are different, examine both with od
-c and also check their lengths.
If a test fails completely, 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 test failures by running
mysql-test-run with either or both of
the --gdb
and --debug
options. See Section E.1.2, “Creating Trace Files”.
If you have not compiled MySQL for debugging you should
probably do so by specifying the
--with-debug
option when you invoke
configure. See
Section 2.8.2, “Typical configure Options”.
There are two ways to add new functions to MySQL:
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 CREATE FUNCTION
and DROP
FUNCTION
statements. See
Section 24.2.2, “CREATE FUNCTION
Syntax”.
You can add functions as native (built-in) MySQL functions. Native functions are compiled into the mysqld server and become available on a permanent basis.
Each method has advantages and disadvantages:
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.
Native functions require you to modify a source distribution. UDFs do not. You can add UDFs to a binary MySQL distribution. No access to MySQL source is necessary.
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.
Whichever method you use to add new functions, they can be invoked
in SQL statements just like native functions such as
ABS()
or SOUNDEX()
.
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 Chapter 17, Stored Procedures and Functions.
The following sections describe features of the UDF interface, provide instructions for writing UDFs, discuss security precautions that MySQL takes to prevent UDF misuse, and describe how to add native mySQL functions.
For example source code that illustrates how to write UDFs, take a
look at the sql/udf_example.cc
file that is
provided in MySQL source distributions.
The MySQL interface for user-defined functions provides the following features and capabilities:
Functions can return string, integer, or real values.
You can define simple functions that operate on a single row at a time, or aggregate functions that operate on groups of rows.
Information is provided to functions that enables them to check the number and types of the arguments passed to them.
You can tell MySQL to coerce arguments to a given type before passing them to a function.
You can indicate that a function returns
NULL
or that an error occurred.
CREATE [AGGREGATE] FUNCTIONfunction_name
RETURNS {STRING|INTEGER|REAL|DECIMAL} SONAMEshared_library_name
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 ABS()
or CONCAT()
.
function_name
is the name that should
be used in SQL statements to invoke the function. The
RETURNS
clause indicates the type of the
function's return value. As of MySQL 5.0.3,
DECIMAL
is a legal value after
RETURNS
, but currently
DECIMAL
functions return string values and
should be written like STRING
functions.
shared_library_name
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.
To create a function, you must have the
INSERT
and privilege for the
mysql
database. This is necessary because
CREATE FUNCTION
adds a row to the
mysql.func
system table that records the
function's name, type, and shared library name. If you do not
have this table, you should run the
mysql_upgrade command to create it. See
Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
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.
For instructions on writing user-defined functions, see Section 24.2.4, “Adding a New User-Defined Function”. 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).
An AGGREGATE
function works exactly like a
native MySQL aggregate (summary) function such as
SUM
or COUNT()
. For
AGGREGATE
to work, your
mysql.func
table must contain a
type
column. If your
mysql.func
table does not have this column,
you should run the mysql_upgrade script to
create it (see Section 5.6.2, “mysql_upgrade — Check Tables for MySQL Upgrade”).
DROP FUNCTION function_name
This statement drops the user-defined function (UDF) named
function_name
.
To drop a function, you must have the DELETE
privilege for the mysql
database. This is
because DROP FUNCTION
removes a row from the
mysql.func
system table that records the
function's name, type, and shared library name.
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.
A UDF contains code that becomes part of the running server, so
when you write a UDF, you are bound by any and all constraints
that otherwise apply to writing server code. For example, you
may have problems if you attempt to use functions from the
libstdc++
library. Note that these
constraints may change in future versions of the server, so it
is possible that server upgrades will require revisions to UDFs
that were originally written for older servers. For information
about these constraints, see
Section 2.8.2, “Typical configure Options”, and
Section 2.8.4, “Dealing with Problems Compiling MySQL”.
To be able to use UDFs, you need to link
mysqld dynamically. Don't configure MySQL
using --with-mysqld-ldflags=-all-static
. If you
want to use a UDF that needs to access symbols from
mysqld (for example, the
metaphone
function in
sql/udf_example.cc
that uses
default_charset_info
), you must link the
program with -rdynamic
(see man
dlopen
). If you plan to use UDFs, the rule of thumb is
to configure MySQL with
--with-mysqld-ldflags=-rdynamic
unless you have
a very good reason not to.
If you must use a precompiled distribution of MySQL, use MySQL-Max, which contains a dynamically linked server that supports dynamic loading.
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 “xxx” is used for an
example function name. To distinguish between SQL and C/C++
usage, XXX()
(uppercase) indicates an 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 function data type and the return type of your C/C++ function is shown here:
SQL Type | C/C++ Type |
STRING | char * |
INTEGER | long long |
REAL | double |
It is also possible to declare a DECIMAL
function, but currently the value is returned as a string,
so you should write the UDF as though it were a
STRING
function.
xxx_init()
(optional)
The initialization function for xxx()
. It
can be used for the following purposes:
To check the number of arguments to
XXX()
.
To check that the arguments are of a required type or, alternatively, to tell MySQL to coerce arguments to the types you want when the main function is called.
To allocate any memory required by the main function.
To specify the maximum length of the result.
To specify (for REAL
functions) the
maximum number of decimal places in the result.
To specify whether the result can be
NULL
.
xxx_deinit()
(optional)
The deinitialization function for xxx()
.
It should deallocate any memory allocated by the
initialization function.
When an 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, MySQL aborts the SQL statement with an error message
and does not call the main or deinitialization functions.
Otherwise, MySQL calls the main function
xxx()
once for each row. After all rows have
been processed, MySQL calls the deinitialization function
xxx_deinit()
so that it can perform any
required cleanup.
For aggregate functions that work like SUM()
,
you must also provide the following functions:
xxx_clear()
(required in
5.0)
Reset the current aggregate value but do not insert the argument as the initial aggregate value for a new group.
xxx_add()
(required)
Add the argument to the current aggregate value.
MySQL handles aggregate UDFs as follows:
Call xxx_init()
to let the aggregate
function allocate any memory it needs for storing results.
Sort the table according to the GROUP BY
expression.
Call xxx_clear()
for the first row in
each new group.
Call xxx_add()
for each new row that
belongs in the same group.
Call xxx()
to get the result for the
aggregate when the group changes or after the last row has
been processed.
Repeat 3-5 until all rows has been processed
Call xxx_deinit()
to let the UDF free any
memory it has allocated.
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
xxx_init()
and free it in
xxx_deinit()
.
This section describes the different functions that you need to define when you create a simple UDF. Section 24.2.4, “Adding a New User-Defined Function”, describes the order in which MySQL calls these functions.
The main xxx()
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 XXX()
to return
STRING
, INTEGER
, or
REAL
in the CREATE
FUNCTION
statement:
For STRING
functions:
char *xxx(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error);
For INTEGER
functions:
long long xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
For REAL
functions:
double xxx(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
The initialization and deinitialization functions are declared like this:
my_bool xxx_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void xxx_deinit(UDF_INIT *initid);
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
follow. 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
The number of decimal digits to the right of the decimal
point. The default value is the maximum number of decimal
digits in the arguments passed to the main function. (For
example, if the function is passed
1.34
, 1.345
, and
1.3
, the default would be 3, because
1.345
has 3 decimal digits.
unsigned int max_length
The maximum length of the result. The default
max_length
value differs depending on
the result type of the function. For string functions, the
default is the length of the longest argument. For integer
functions, the default is 21 digits. For real functions,
the default is 13 plus the number of decimal digits
indicated by initid->decimals
. (For
numeric functions, the length includes any sign or decimal
point characters.)
If you want to return a blob value, you can set
max_length
to 65KB or 16MB. This memory
is not allocated, but the value is used to decide which
data type to use if there is a need to temporarily store
the data.
char *ptr
A pointer that the function can use for its own purposes.
For example, functions can use
initid->ptr
to communicate allocated
memory among themselves. xxx_init()
should 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.
my_bool const_item
xxx_init()
should set
const_item
to 1
if
xxx()
always returns the same value and
to 0
otherwise.
This section describes the different functions that you need to define when you create an aggregate UDF. Section 24.2.4, “Adding a New User-Defined Function”, describes the order in which MySQL calls these functions.
xxx_reset()
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
UDF_ARGS
argument as the first value in
your internal summary value for the group. Declare
xxx_reset()
as follows:
char *xxx_reset(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
xxx_reset()
is not needed or used in
MySQL 5.0, in which the UDF interface uses
xxx_clear()
instead. However, you can
define both xxx_reset()
and
xxx_clear()
if you want to have your
UDF work with older versions of the server. (If you do
include both functions, the xxx_reset()
function in many cases can be implemented internally by
calling xxx_clear()
to reset all
variables, and then calling xxx_add()
to add the UDF_ARGS
argument as the
first value in the group.)
xxx_clear()
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
xxx_clear()
as follows:
char *xxx_clear(UDF_INIT *initid, char *is_null, char *error);
is_null
is set to point to
CHAR(0)
before calling
xxx_clear()
.
If something went wrong, you can store a value in the
variable to which the error
argument
points. error
points to a single-byte
variable, not to a string buffer.
xxx_clear()
is required by MySQL
5.0.
xxx_add()
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 UDF_ARGS
argument
to your internal summary variable.
char *xxx_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
The xxx()
function for an aggregate UDF
should be declared the same way as for a non-aggregate UDF.
See Section 24.2.4.1, “UDF Calling Sequences for Simple Functions”.
For an aggregate UDF, MySQL calls the xxx()
function after all rows in the group have been processed. You
should normally never access its UDF_ARGS
argument here but instead return a value based on your
internal summary variables.
Return value handling in xxx()
should be
done the same way as for a non-aggregate UDF. See
Section 24.2.4.4, “UDF Return Values and Error Handling”.
The xxx_reset()
and
xxx_add()
functions handle their
UDF_ARGS
argument the same way as functions
for non-aggregate UDFs. See Section 24.2.4.3, “UDF Argument Processing”.
The pointer arguments to is_null
and
error
are the same for all calls to
xxx_reset()
,
xxx_clear()
, xxx_add()
and xxx()
. You can use this to remember
that you got an error or whether the xxx()
function should return NULL
. You should not
store a string into *error
!
error
points to a single-byte variable, not
to a string buffer.
*is_null
is reset for each group (before
calling xxx_clear()
).
*error
is never reset.
If *is_null
or *error
are set when xxx()
returns, MySQL returns
NULL
as the result for the group function.
The args
parameter points to a
UDF_ARGS
structure that has the members
listed here:
unsigned int arg_count
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:
if (args->arg_count != 2) { strcpy(message,"XXX() requires two arguments"); return 1; }
enum Item_result *arg_type
A pointer to an array containing 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:
if (args->arg_type[0] != STRING_RESULT || args->arg_type[1] != INT_RESULT) { strcpy(message,"XXX() requires a string and an integer"); return 1; }
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 that the
first two arguments should be coerced to string and
integer, respectively, 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 passed to your function. 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. You should not change
these. 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 is 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 function should
return a pointer to the result and set
*result
and *length
to
the contents and length of the return value. For example:
memcpy(result, "result string", 13); *length = 13;
The result
buffer that is passed to the
xxx()
function is 255 bytes long. If your
result fits in this, you don't have to worry about memory
allocation for results.
If your string function 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 24.2.4.1, “UDF Calling Sequences for Simple Functions”.
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
*error
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()
is not even called for subsequent
rows.)
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'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 a
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 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 BitKeeper source repository for MySQL 5.0. See Section 2.8.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.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 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';
Functions can be deleted using DROP
FUNCTION
:
mysql>DROP FUNCTION metaphon;
mysql>DROP FUNCTION myfunc_double;
mysql>DROP FUNCTION myfunc_int;
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.
MySQL takes the following measures to prevent misuse of user-defined functions.
You must have the INSERT
privilege to be
able to use CREATE FUNCTION
and the
DELETE
privilege to be able to use
DROP FUNCTION
. This is necessary because
these statements add and delete rows from the
mysql.func
table.
UDFs should have at least one symbol defined in addition to
the xxx
symbol that corresponds to the main
xxx()
function. These auxiliary symbols
correspond to the xxx_init()
,
xxx_deinit()
,
xxx_reset()
,
xxx_clear()
, and
xxx_add()
functions. As of MySQL 5.0.3,
mysqld supports an
--allow-suspicious-udfs
option that controls
whether UDFs that have only an xxx
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 xxx
symbol and that
cannot be recompiled to include an auxiliary symbol, it may be
necessary to specify the
--allow-suspicious-udfs
option. Otherwise,
you should avoid enabling this capability.
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 CREATE
FUNCTION
statements for pathname delimiter
characters. As of MySQL 5.0.3, MySQL also checks for pathname
delimiters in filenames stored in the
mysql.func
table when it loads functions.
This prevents attempts at specifying illegitimate pathnames
through direct manipulation of the
mysql.func
table. For information about
UDFs and the runtime linker, see
Section 24.2.4.5, “Compiling and Installing User-Defined Functions”.
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.
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 N
)N
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, if it
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:
double Item_func_newname::val() longlong Item_func_newname::val_int() String *Item_func_newname::Str(String *str)
If you inherit your object from any of the standard items
(like Item_num_func
), you probably only
have to define one of these 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 whether 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 a row-by-row or GROUP
BY
level.
We have created an example procedure to show you what can be done.
Additionally, we recommend that you take a look at
mylua
. With this you can use the LUA language
to load a procedure at runtime into mysqld.
analyse([
max_elements
,[max_memory
]])
This procedure is defined in the
sql/sql_analyse.cc
. This examines the
result from your query and returns an analysis of the results:
max_elements
(default 256) is the
maximum number of distinct values analyse
does notice per column. This is used by
analyse
to check whether the optimal data
type should be of type ENUM
.
max_memory
(default 8192) is the
maximum amount of memory that analyse
should allocate per column while trying to find all distinct
values.
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements
,[max_memory
]])