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 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 is relatively low
traffic. For details on how to subscribe, please see
section 1.4.1.1 The 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:
process_alarm()
to force timeouts on connections
that have been idle too long.
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.
--flush_time=#
option, a dedicated thread is created
to flush all tables at the given interval.
INSERT DELAYED
gets its
own thread.
--master-host
, a slave replication thread is
started to read and apply updates from the master.
mysqladmin processlist
only shows the connection, INSERT DELAYED
,
and replication threads.
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
Unix source distributions and binary distributions starting with Version
3.23.29. The tests can be run under Unix, or on Windows in the Cygwin
environment if the server has been compiled under Cygwin. They cannot be
run in a native Windows environment currently.
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.
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 25.1.2.3 Reporting Bugs in the MySQL Test Suite.
From MySQL 4.1 on,
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.
Before MySQL 4.1, mysql-test-run
does not try to run its own server
by default but tries to use your currently running server. To override this
and cause mysql-test-run
to start its own server, run it with
the --local
option.
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 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:
;
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
).
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 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 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 od -c
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 mysqltest -r
as you only have to do it when you find a bug.
--error error-number
on the line before the statement.
The error number can be
a list of possible error numbers separated by `,'.
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.
let $1=1000; while ($1) { # do your queries here dec $1; }
sleep
command. It supports fractions
of a second, so you can use sleep 1.3;
, for example, to sleep 1.3
seconds.
internals
mailing list.
See section 1.4.1.1 The MySQL Mailing Lists.
As this list does not accept
attachments, you should ftp all the relevant files to:
ftp://ftp.mysql.com/pub/mysql/upload/
If your MySQL version doesn't pass the test suite you should do the following:
mysqlbug
script
so that we can get information about your system and MySQL
version. See section 1.4.1.3 How to Report Bugs or Problems.
mysql-test-run
, as well as
contents of all `.reject' files in `mysql-test/r' directory.
cd mysql-test mysql-test-run --local test-nameIf 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://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.
mysql-test-run
with the --force
option to
see whether there is any other test that fails.
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 version of mysqld
produces slightly 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.
mysql-test-run
with the --gdb
and/or --debug
options.
See section E.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.8 MySQL Installation Using a Source Distribution.
There are two ways to add new functions to MySQL:
CREATE FUNCTION
and
DROP FUNCTION
statements.
See section 25.2.2 CREATE FUNCTION/DROP FUNCTION
Syntax.
mysqld
server and become available
on a permanent basis.
Each method has advantages and disadvantages:
xxx_clear()
must be defined
rather than xxx_reset()
.) 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 Stored
Procedures
.
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.
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.
CREATE FUNCTION/DROP FUNCTION
Syntax
The MySQL interface for user-defined functions provides the following features and capabilties:
NULL
or that an error
occurred.
CREATE FUNCTION/DROP FUNCTION
SyntaxCREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL} SONAME shared_library_name DROP FUNCTION function_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. 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. To drop a function, you must have the DELETE
privilege for the mysql
database. This is because CREATE
FUNCTION
adds a row to the mysql.func
system table that records the
function's name, type, and shared library name, and DROP FUNCTION
deletes the function's row from that table. If you do not have this table,
you should run the mysql_fix_privilege_tables
script to create it.
See section 2.10.7 Upgrading the Grant Tables.
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 25.2.3 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).
AGGREGATE
is a new option for MySQL 3.23. 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_fix_privilege_tables
script to create it.
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.
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 configure MySQL with
--with-mysqld-ldflags=-rdynamic
unless you have a very good reason
not to.
If you to 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)
SQL Type | C/C++ Type |
STRING | char *
|
INTEGER | long long
|
REAL | double
|
xxx_init()
(optional)
xxx()
. It can be used to:
XXX()
.
REAL
functions) the maximum number of decimals.
NULL
.
xxx_deinit()
(optional)
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, 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.
For aggregate functions that work like SUM()
, you must also provide
the following functions:
xxx_reset()
(required before 4.1.1)
xxx_clear()
(required starting from 4.1.1)
xxx_add()
(required)
MySQL handles aggregate UDFs as follows:
xxx_init()
to let the aggregate function allocate any memory it
needs for storing results.
GROUP BY
expression.
xxx_clear()
for the first row in each new group.
xxx_add()
for each new row that belongs in the same group.
xxx()
to get the result for the aggregate when the group changes
or after the last row has been processed.
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 25.2.3 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
1.34
, 1.345
, and 1.3
, the default would be 3,
because 1.345
has 3 decimals.
unsigned int max_length
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 decimals 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
column type to use if there is a need to temporarily store the data.
char *ptr
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.
This section describes the different functions that you need to define when you create an aggregate UDF. section 25.2.3 Adding a New User-defined Function describes the order in which MySQL calls these functions.
xxx_reset()
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 needed only before MySQL 4.1.1. It is not
needed or used as of MySQL 4.1.1, when the UDF interface changed to use
xxx_clear()
instead. However, you can define both xxx_reset()
and xxx_clear()
if you want to have your UDF work both before and
after the interface change. (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()
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 only by MySQL 4.1.1 and above. Before MySQL
4.1.1, use xxx_reset()
instead.
xxx_add()
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 25.2.3.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 25.2.3.4 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 25.2.3.3 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
if (args->arg_count != 2) { strcpy(message,"XXX() requires two arguments"); return 1; }
enum Item_result *arg_type
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:
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.
INT_RESULT
, you must cast
args->args[i]
to a long long
value:
long long int_val; int_val = *((long long*) args->args[i]);
REAL_RESULT
, you must cast
args->args[i]
to a double
value:
double real_val; real_val = *((double*) args->args[i]);
unsigned long *lengths
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 25.2.3.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.) Note: Before
MySQL 3.22.10, you should set both *error
and *is_null
:
*error = 1; *is_null = 1;
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:
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 4.0.24, 4.1.10a, and 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 4.0.24, 4.1.10a,
and 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 25.2.3.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:
sql_functions[]
array.
SYM(FUNC_ARGN)
(where 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.
Item_num_func
or
Item_str_func
, depending on whether your function returns a number or a
string.
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()
.
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:
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.)
::str()
function should return the string that holds the result or
(char*) 0
if the result is NULL
.
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 in MySQL 3.23 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:
analyse
does notice per column. This is used by analyse
to
check whether the optimal column type should be of type ENUM
.
analyse
should allocate per column while trying to find all distinct
values.
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
For the moment, the only documentation for this is the source.
You can find all information about procedures by examining the following files:
Go to the first, previous, next, last section, table of contents.