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.4.15.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.6.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:
Connection manager threads handle client connection requests on the network interfaces that the server listens to. On all platforms, one manager thread handles TCP/IP connection requests. On Unix, this manager thread also handles Unix socket file connection requests. On Windows, a manager thread handles shared-memory connection requests, and another handles named-pipe connection requests. The server does not create threads to handle interfaces that it does not listen to. For example, a Windows server that does not have support for named-pipe connections enabled does not create a thread to handle them.
Connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.
For information about tuning the parameters that control thread resources, see Section 7.5.7, “How MySQL Uses Threads for Client Connections”.
On a master replication server, connections from slave servers are handled 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.
A 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 InnoDB
is used, there will be 4
additional threads by default. Those are file I/O threads,
controlled by the innodb_file_io_threads
parameter. See Section 13.2.4, “InnoDB
Startup Options and System Variables”.
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. See
Section 12.2.4.2, “INSERT DELAYED
Syntax”.
mysqladmin processlist only shows the
connection, INSERT DELAYED
, and replication
threads.
MySQL Enterprise For expert advice on thread management subscribe to the MySQL Enterprise Monitor. For more information see, http://www.mysql.com/products/enterprise/advisors.html.
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.
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 Perl script to run all tests
(mysql-test-run.pl), 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 from the source root directory, or
change location to the mysql-test
directory
and type ./mysql-test-run.pl. If you have
installed a binary distribution, change location to the
mysql-test
directory under the installation
root directory (for example,
/usr/local/mysql/mysql-test
), and run
./mysql-test-run.pl. All tests should
succeed. If any do not, feel free to try to find out why and
report the problem if it indicates a bug in MySQL. See
Section 1.7, “How to Report Bugs or Problems”.
If one test fails, you should run
mysql-test-run.pl with the
--force
option to check whether any other tests
fail.
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 set the
MTR_BUILD_THREAD
environment variable to an
appropriate value, and the test suite will use a different set
of ports for master, slave, NDB, and Instance Manager). For
example:
shell> export MTR_BUILD_THREAD=31 shell> ./mysql-test-run.pl [options
] [test_name
]
In the mysql-test
directory, you can run an
individual test case with ./mysql-test-run.pl
test_name
.
You can use the mysqltest language to write your own test cases. This is documented in the MySQL Test Framework manual, available at http://dev.mysql.com/doc/.
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.6.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/
There are three 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 26.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.
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 not covered here. See Chapter 18, Stored Procedures and Functions.
Each method of creating compiled functions 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()
.
See Section 8.2.3, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of 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.c
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 and can accept arguments of those same types.
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, types, and names 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 4.4.9, “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 26.2.4, “Adding a New User-Defined Function”. For the UDF mechanism to work, functions must be written in C or C++ (or another language that can use C calling conventions), 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 program to
create it (see Section 4.4.9, “mysql_upgrade — Check Tables for MySQL Upgrade”).
To upgrade the shared library associated with a UDF, issue a
DROP FUNCTION
statement, upgrade the shared
library, and then issue a CREATE FUNCTION
statement. If you upgrade the shared library first and then
use DROP FUNCTION
, the server may crash.
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.
To upgrade the shared library associated with a UDF, issue a
DROP FUNCTION
statement, upgrade the shared
library, and then issue a CREATE FUNCTION
statement. If you upgrade the shared library first and then
use DROP FUNCTION
, the server may crash.
DROP FUNCTION
is also used to drop stored
functions (see Section 18.2.3, “DROP PROCEDURE
and DROP FUNCTION
Syntax”).
For the UDF mechanism to work, functions must be written in C or
C++ (or another language that can use C calling conventions),
and your operating system must support dynamic loading. The
MySQL source distribution includes a file
sql/udf_example.c
that defines 5 new
functions. Consult this file to see how UDF calling conventions
work. UDF-related symbols and data structures are defined in the
include/mysql_com.h
header file. (You need
not include this header file directly because it is included by
mysql.h
.)
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.4.15.2, “Typical configure Options”, and
Section 2.4.15.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.c
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.
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. ROW
functions are not implemented.
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 26.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);
DECIMAL
functions return string values and
should be declared the same way as STRING
functions. ROW
functions are not
implemented.
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 26.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 26.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 26.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 26.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; }
For other UDF_ARGS
member values that
are arrays, array references are zero-based. That is,
refer to array members using index values from 0 to
args->arg_count
– 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
,
REAL_RESULT
, and
DECIMAL_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; }
Arguments of type DECIMAL_RESULT
are
passed as strings, so you should handle them the same way
as STRING_RESULT
values.
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;
Exact-value decimal arguments such as
1.3
or DECIMAL
column values are passed with a type of
DECIMAL_RESULT
. However, the values are
passed as strings. If you want to receive a number, use
the initialization function to specify that the argument
should be coerced to a REAL_RESULT
value:
args->arg_type[2] = REAL_RESULT;
Prior to MySQL 5.0.3, decimal arguments were passed as
REAL_RESULT
values. If you upgrade to
a newer version and find that your UDF now receives
string values, use the initialization function to coerce
the arguments to numbers as just described.
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.
If argument i
represents
NULL
,
args->args[i]
is a null pointer (0).
If the argument is not NULL
, functions
can refer to it 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]
. Do
not assume that the string is 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]);
For an argument of type
DECIMAL_RESULT
, the value is passed
as a string and should be handled like a
STRING_RESULT
value.
ROW_RESULT
arguments are not
implemented.
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).
char *maybe_null
For the initialization function, the
maybe_null
array indicates for each
argument whether the argument value might be null (0 if
no, 1 if yes).
char **attributes
args->attributes
communicates
information information about the names of the UDF
arguments. For argument i
, the
attribute name is available as a string in
args->attributes[i]
and the
attribute length is
args->attribute_lengths[i]
. Do not
assume that the string is null-terminated.
By default, the name of a UDF argument is the text of the
expression used to specify the argument. For UDFs, an
argument may also have an optional [AS]
clause, in
which case the argument name is
alias_name
alias_name
. The
attributes
value for each argument thus
depends on whether an alias was given.
Suppose that a UDF my_udf()
is invoked
as follows:
SELECT my_udf(expr1, expr2 AS alias1, expr3 alias2);
In this case, the attributes
and
attribute_lengths
arrays will have
these values:
args->attributes[0] = "expr1" args->attribute_lengths[0] = 5 args->attributes[1] = "alias1" args->attribute_lengths[1] = 6 args->attributes[2] = "alias2" args->attribute_lengths[2] = 6
unsigned long *attribute_lengths
The attribute_lengths
array indicates
the length of each argument name.
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 26.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.c
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.c
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.c
If you are using gcc with
configure and libtool
(which is how MySQL is configured), you should be able to
create udf_example.so
with a simpler
command:
shell> make udf_example.la
After you compile a shared object containing UDFs, you must
install it and tell MySQL about it. Compiling a shared object
from udf_example.c
using
gcc directly produces a file named
udf_example.so
. Compiling the shared
object using make produces a file named
something like udf_example.so.0.0.0
in
the .libs
directory (the exact name may
vary from platform to platform). Copy the shared object to
some directory such as /usr/lib
that is
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.4.15.3, “Installing from the Development Source Tree”.
You must obtain the CMake build utility from http://www.cmake.org. (Version 2.4.2 or later is required).
In the source repository, look in the
sql
directory. There are files named
udf_example.def
udf_example.c
there. Copy both files
from this directory to your working directory.
Create a CMake makefile
with these
contents:
PROJECT(udf_example) # Path for MySQL include directory INCLUDE_DIRECTORIES("c:/mysql/include") ADD_DEFINITIONS("-DHAVE_DLOPEN") ADD_LIBRARY(udf_example MODULE udf_example.c udf_example.def) TARGET_LINK_LIBRARIES(udf_example wsock32)
Create the VC project and solution files:
cmake -G "<Generator>"
Invoking cmake --help shows you a list of valid Generators.
Create udf_example.dll
:
devenv udf_example.sln /build Release
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.
If the new function will be referred to in statements that will be replicated to slave servers, you must ensure that every slave server also has the function available. Otherwise, replication will fail on the slaves when they attempt to invoke the function.
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 26.2.4.5, “Compiling and Installing User-Defined Functions”.
To add a new native MySQL function, use the procedure described here, which requires that you use a source distribution. You cannot add native functions to a binary distribution because it is necessary to modify MySQL source code and compile MySQL from the modified source. If you migrate to another version of MySQL (for example, when a new version is released), you must repeat the procedure with the new version.
If the new native function will be referred to in statements that will be replicated to slave servers, you must ensure that every slave server also has the function available. Otherwise, replication will fail on the slaves when they attempt to invoke the function.
To add a new native function, follow these steps to modify
source files in the sql
directory:
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), add a line to the
sql_functions[]
array in
lex.h
that specifies
SYM(FUNC_ARG
as the second argument (where N
)N
is the number of arguments the function takes). Also, add a
function in item_create.cc
that creates
a function object. Look at "ABS"
and
create_funcs_abs()
for an example of
this.
If the function prototype is not simple (for example, if it
takes a variable number of arguments), you should make two
changes to sql_yacc.yy
. One is a line
that indicates the preprocessor symbol that
yacc should define; this should be added
at the beginning of the file. The other is an
“item” to be added to the
simple_expr
parsing rule that defines the
function parameters. You will need an item for each syntax
with which the function can be called. For an example that
shows how this this is done, check all occurrences of
ATAN
in sql_yacc.yy
.
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()
.
If the function is non-deterministic, include the following statement in the item constructor to indicate that function results should not be cached:
current_thd->lex->safe_to_cache_query=0;
A function is non-deterministic if, given fixed values for its arguments, it can return different results for different invocations.
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. 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, do not 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
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
file. It examines the
result from a query and returns an analysis of the results that
suggests optimal data types for each column. To obtain this
analysis, append PROCEDURE ANALYSE
to the end
of a SELECT
statement:
SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements
,[max_memory
]])
For example:
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE(10, 2000);
The results show some statistics for the values returned by the
query, and propose an optimal data type for the columns. This
can be helpful for checking your existing tables, or after
importing new data. You may need to try different settings for
the arguments so that PROCEDURE ANALYSE()
does not suggest the ENUM
data type when it
is not appropriate.
The arguments are optional and are used as follows:
max_elements
(default 256) is the
maximum number of distinct values that
analyse
notices 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.
This appendix helps you port MySQL to other operating systems. Do check the list of currently supported operating systems first. See Section 2.4.2, “Operating Systems Supported by MySQL Community Server”. If you have created a new port of MySQL, please let us know so that we can list it here and on our Web site (http://www.mysql.com/), recommending it to other users.
Note: If you create a new port of MySQL, you are free to copy and distribute it under the GPL license, but it does not make you a copyright holder of MySQL.
A working POSIX thread library is needed for the server. On
Solaris 2.5 we use Sun PThreads (the native thread support in 2.4
and earlier versions is not good enough), on Linux we use
LinuxThreads by Xavier Leroy,
<Xavier.Leroy@inria.fr>
.
The hard part of porting to a new Unix variant without good native
thread support is probably to port MIT-pthreads. See
mit-pthreads/README
and Programming POSIX
Threads (http://www.humanfactor.com/pthreads/).
Up to MySQL 4.0.2, the MySQL distribution included a patched version of Chris Provenzano's Pthreads from MIT (see the MIT Pthreads Web page at http://www.mit.edu/afs/sipb/project/pthreads/ and a programming introduction at http://www.mit.edu:8001/people/proven/IAP_2000/). These can be used for some operating systems that do not have POSIX threads. See Section 2.4.15.5, “MIT-pthreads Notes”.
It is also possible to use another user level thread package named FSU Pthreads (see http://moss.csc.ncsu.edu/~mueller/pthreads/). This implementation is being used for the SCO port.
See the thr_lock.c
and
thr_alarm.c
programs in the
mysys
directory for some tests/examples of
these problems.
Both the server and the client need a working C++ compiler. We use gcc on many platforms. Other compilers that are known to work are SPARCworks, Sun Forte, Irix cc, HP-UX aCC, IBM AIX xlC_r), Intel ecc/icc and Compaq cxx).
If you are trying to build MySQL 5.1 with icc on the IA64 platform, and need support for MySQL Cluster, you should first ensure that you are using icc version 9.1.043 or later. (For details, see Bug#21875.)
To compile only the client use ./configure --without-server.
There is currently no support for only compiling the server, nor is it likely to be added unless someone has a good reason for it.
If you want/need to change any Makefile
or
the configure script you also need GNU Automake and Autoconf. See
Section 2.4.15.3, “Installing from the Development Source Tree”.
All steps needed to remake everything from the most basic files.
/bin/rm */.deps/*.P /bin/rm -f config.cache aclocal autoheader aclocal automake autoconf ./configure --with-debug=full --prefix='your installation directory' # The makefiles generated above need GNU make 3.75 or newer. # (called gmake below) gmake clean all install init-db
If you run into problems with a new port, you may have to do some debugging of MySQL! See Section 26.4.1, “Debugging a MySQL Server”.
Before you start debugging mysqld, first get
the test programs mysys/thr_alarm
and
mysys/thr_lock
to work. This ensures that
your thread installation has even a remote chance to work!
pdb
to create a Windows crashdump
If you are using some functionality that is very new in MySQL,
you can try to run mysqld with the
--skip-new
(which disables all new, potentially
unsafe functionality) or with --safe-mode
which
disables a lot of optimization that may cause problems. See
Section B.1.4.2, “What to Do If MySQL Keeps Crashing”.
If mysqld doesn't want to start, you should
verify that you don't have any my.cnf
files
that interfere with your setup! You can check your
my.cnf
arguments with mysqld
--print-defaults and avoid using them by starting with
mysqld --no-defaults ....
If mysqld starts to eat up CPU or memory or if it “hangs,” you can use mysqladmin processlist status to find out if someone is executing a query that takes a long time. It may be a good idea to run mysqladmin -i10 processlist status in some window if you are experiencing performance problems or problems when new clients can't connect.
The command mysqladmin debug dumps some information about locks in use, used memory and query usage to the MySQL log file. This may help solve some problems. This command also provides some useful information even if you haven't compiled MySQL for debugging!
If the problem is that some tables are getting slower and slower
you should try to optimize the table with OPTIMIZE
TABLE
or myisamchk. See
Chapter 5, MySQL Server Administration. You should also check
the slow queries with EXPLAIN
.
You should also read the OS-specific section in this manual for problems that may be unique to your environment. See Section 2.4.19, “Operating System-Specific Notes”.
If you have some very specific problem, you can always try to
debug MySQL. To do this you must configure MySQL with the
--with-debug
or the
--with-debug=full
option. You can check
whether MySQL was compiled with debugging by doing:
mysqld --help. If the
--debug
flag is listed with the options then
you have debugging enabled. mysqladmin ver
also lists the mysqld version as
mysql ... --debug in this case.
If you are using gcc, the recommended configure line is:
CC=gcc CFLAGS="-O2" CXX=gcc CXXFLAGS="-O2 -felide-constructors \ -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql \ --with-debug --with-extra-charsets=complex
This avoids problems with the libstdc++
library and with C++ exceptions (many compilers have problems
with C++ exceptions in threaded code) and compile a MySQL
version with support for all character sets.
If you suspect a memory overrun error, you can configure MySQL
with --with-debug=full
, which installs a
memory allocation (SAFEMALLOC
) checker.
However, running with SAFEMALLOC
is quite
slow, so if you get performance problems you should start
mysqld with the
--skip-safemalloc
option. This disables the
memory overrun checks for each call to
malloc()
and free()
.
If mysqld stops crashing when you compile
it with --with-debug
, you probably have found
a compiler bug or a timing bug within MySQL. In this case, you
can try to add -g
to the
CFLAGS
and CXXFLAGS
variables above and not use --with-debug
. If
mysqld dies, you can at least attach to it
with gdb or use gdb on
the core file to find out what happened.
When you configure MySQL for debugging you automatically
enable a lot of extra safety check functions that monitor the
health of mysqld. If they find something
“unexpected,” an entry is written to
stderr
, which
mysqld_safe directs to the error log! This
also means that if you are having some unexpected problems
with MySQL and are using a source distribution, the first
thing you should do is to configure MySQL for debugging! (The
second thing is to send mail to a MySQL mailing list and ask
for help. See Section 1.6.1, “MySQL Mailing Lists”. If you believe
that you have found a bug, please use the instructions at
Section 1.7, “How to Report Bugs or Problems”.
In the Windows MySQL distribution,
mysqld.exe
is by default compiled with
support for trace files. See also
Section 26.4.1.2, “Creating Trace Files”.
If the mysqld server doesn't start or if you can cause it to crash quickly, you can try to create a trace file to find the problem.
To do this, you must have a mysqld that has
been compiled with debugging support. You can check this by
executing mysqld -V
. If the version number
ends with -debug
, it's compiled with support
for trace files. (On Windows, the debugging server is named
mysqld-debug rather than
mysqld as of MySQL 4.1.)
Start the mysqld server with a trace log in
/tmp/mysqld.trace
on Unix or
C:\mysqld.trace
on Windows:
shell> mysqld --debug
On Windows, you should also use the
--standalone
flag to not start
mysqld as a service. In a console window,
use this command:
C:\> mysqld-debug --debug --standalone
After this, you can use the mysql.exe
command-line tool in a second console window to reproduce the
problem. You can stop the mysqld server
with mysqladmin shutdown.
Note that the trace file become very big! If you want to generate a smaller trace file, you can use debugging options something like this:
mysqld --debug=d,info,error,query,general,where:O,/tmp/mysqld.trace
This only prints information with the most interesting tags to the trace file.
If you make a bug report about this, please only send the lines from the trace file to the appropriate mailing list where something seems to go wrong! If you can't locate the wrong place, you can ftp the trace file, together with a full bug report, to ftp://ftp.mysql.com/pub/mysql/upload/ so that a MySQL developer can take a look at it.
The trace file is made with the DBUG package by Fred Fish. See Section 26.4.3, “The DBUG Package”.
Starting with MySQL 5.0.24 the Program Database files
(extension pdb
) are included in the
Noinstall distribution of MySQL. These files provide
information for debugging your MySQL installation in the event
of a problem.
The PDB file contains more detailed information about
mysqld
and other tools that enables more
detailed trace and dump files to be created. You can use these
with Dr Watson, WinDbg and Visual Studio to
debug mysqld.
For more information on PDB files, see Microsoft Knowledge Base Article 121366. For more information on the debugging options available, see Debugging Tools for Windows.
Dr Watson is installed with all Windows distributions, but if you have installed Windows development tools, Dr Watson may have been replaced with WinDbg, the debugger included with Visual Studio, or the debugging tools provided with Borland or Delphi.
To generate a crash file using Dr Watson, follow these steps:
Start Dr Watson by running drwtsn32.exe
interactively using the -i
option:
C:\> drwtsn32 -i
Set the Log File Path to the directory where you want to store trace files.
Make sure Dump All Thread Contexts and Append To Existing Log File.
Uncheck Dump Sumbol Table, Visual Notification, Sound Notification and Create Crash Dump File.
Set the Number of Instructions to a suitable value to capture enough calls in the stacktrace. A value of at 25 should be enough.
Note that the file generated can be very large.
On most systems you can also start mysqld from gdb to get more information if mysqld crashes.
With some older gdb versions on Linux you
must use run --one-thread
if you want to be
able to debug mysqld threads. In this case,
you can only have one thread active at a time. We recommend
you to upgrade to gdb 5.1 ASAP as thread debugging works much
better with this version!
NPTL threads (the new thread library on Linux) may cause problems while running mysqld under gdb. Some symptoms are:
mysqld hangs during startup (before it
writes ready for connections
).
mysqld crashes during a
pthread_mutex_lock()
or
pthread_mutex_unlock()
call.
In this case, you should set the following environment variable in the shell before starting gdb:
LD_ASSUME_KERNEL=2.4.1 export LD_ASSUME_KERNEL
When running mysqld under
gdb, you should disable the stack trace
with --skip-stack-trace
to be able to catch
segfaults within gdb.
In MySQL 4.0.14 and above you should use the
--gdb
option to mysqld.
This installs an interrupt handler for
SIGINT
(needed to stop
mysqld with ^C
to set
breakpoints) and disable stack tracing and core file handling.
It's very hard to debug MySQL under gdb if
you do a lot of new connections the whole time as
gdb doesn't free the memory for old
threads. You can avoid this problem by starting
mysqld with
thread_cache_size
set to a value equal to
max_connections
+ 1. In most cases just
using --thread_cache_size=5'
helps a lot!
If you want to get a core dump on Linux if
mysqld dies with a SIGSEGV signal, you can
start mysqld with the
--core-file
option. This core file can be
used to make a backtrace that may help you find out why
mysqld died:
shell> gdb mysqld core
gdb> backtrace full
gdb> quit
See Section B.1.4.2, “What to Do If MySQL Keeps Crashing”.
If you are using gdb 4.17.x or above on
Linux, you should install a .gdb
file,
with the following information, in your current directory:
set print sevenbit off handle SIGUSR1 nostop noprint handle SIGUSR2 nostop noprint handle SIGWAITING nostop noprint handle SIGLWP nostop noprint handle SIGPIPE nostop handle SIGALRM nostop handle SIGHUP nostop handle SIGTERM nostop noprint
If you have problems debugging threads with gdb, you should download gdb 5.x and try this instead. The new gdb version has very improved thread handling!
Here is an example how to debug mysqld:
shell> gdb /usr/local/libexec/mysqld
gdb> run
...
backtrace full # Do this when mysqld crashes
Include the above output in a bug report, which you can file using the instructions in Section 1.7, “How to Report Bugs or Problems”.
If mysqld hangs you can try to use some
system tools like strace
or
/usr/proc/bin/pstack
to examine where
mysqld has hung.
strace /tmp/log libexec/mysqld
If you are using the Perl DBI
interface,
you can turn on debugging information by using the
trace
method or by setting the
DBI_TRACE
environment variable.
On some operating systems, the error log contains a stack
trace if mysqld dies unexpectedly. You can
use this to find out where (and maybe why)
mysqld died. See
Section 5.2.1, “The Error Log”. To get a stack trace, you must
not compile mysqld with the
-fomit-frame-pointer
option to gcc. See
Section 26.4.1.1, “Compiling MySQL for Debugging”.
If the error file contains something like the following:
mysqld got signal 11; The manual section 'Debugging a MySQL server' tells you how to use a stack trace and/or the core file to produce a readable backtrace that may help in finding out why mysqld died Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack range sanity check, ok, backtrace follows 0x40077552 0x81281a0 0x8128f47 0x8127be0 0x8127995 0x8104947 0x80ff28f 0x810131b 0x80ee4bc 0x80c3c91 0x80c6b43 0x80c1fd9 0x80c1686
you can find where mysqld died by doing the following:
Copy the preceding numbers to a file, for example
mysqld.stack
.
Make a symbol file for the mysqld server:
nm -n libexec/mysqld > /tmp/mysqld.sym
If you have not linked mysqld statically, use the following command:
nm -D -n libexec/mysqld > /tmp/mysqld.sym
If you want to decode C++ symbols, use the
--demangle
, if available, to
nm. If your version of
nm does not have this option, you will
need to use the c++filt command after
the stack dump has been produced to demangle the C++
names.
Note that most MySQL binary distributions (except for the
"debug" packages, where this information is included
inside of the binaries themselves) ship with the above
file, named mysqld.sym.gz
. In this
case, you can simply unpack it by doing:
gunzip < bin/mysqld.sym.gz > /tmp/mysqld.sym
Execute the following line:
resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack
If you were not able to include demangled C++ names in your symbol file, use c++filt on the output:
resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack|c++filt
This prints out where mysqld died. If this doesn't help you find out why mysqld died, you should make a bug report and include the output from the above command with the bug report.
Note however that in most cases it does not help us to just have a stack trace to find the reason for the problem. To be able to locate the bug or provide a workaround, we would in most cases need to know the query that killed mysqld and preferable a test case so that we can repeat the problem! See Section 1.7, “How to Report Bugs or Problems”.
Note that before starting mysqld with
--log
you should check all your tables with
myisamchk. See
Chapter 5, MySQL Server Administration.
If mysqld dies or hangs, you should start
mysqld with --log
. When
mysqld dies again, you can examine the end
of the log file for the query that killed
mysqld.
If you are using --log
without a file name,
the log is stored in the database directory as
In most cases it is the last query in the log file that killed
mysqld, but if possible you should verify
this by restarting mysqld and executing the
found query from the mysql command-line
tools. If this works, you should also test all complicated
queries that didn't complete.
host_name
.log
You can also try the command EXPLAIN
on all
SELECT
statements that takes a long time to
ensure that mysqld is using indexes
properly. See Section 12.3.2, “EXPLAIN
Syntax”.
You can find the queries that take a long time to execute by
starting mysqld with
--log-slow-queries
. See
Section 5.2.4, “The Slow Query Log”.
If you find the text mysqld restarted
in
the error log file (normally named
hostname.err
) you probably have found a
query that causes mysqld to fail. If this
happens, you should check all your tables with
myisamchk (see
Chapter 5, MySQL Server Administration), and test the queries
in the MySQL log files to see whether one fails. If you find
such a query, try first upgrading to the newest MySQL version.
If this doesn't help and you can't find anything in the
mysql
mail archive, you should report the
bug to a MySQL mailing list. The mailing lists are described
at http://lists.mysql.com/, which also has
links to online list archives.
If you have started mysqld with
--myisam-recover
, MySQL automatically checks
and tries to repair MyISAM
tables if they
are marked as 'not closed properly' or 'crashed'. If this
happens, MySQL writes an entry in the
hostname.err
file 'Warning:
Checking table ...'
which is followed by
Warning: Repairing table
if the table needs
to be repaired. If you get a lot of these errors, without
mysqld having died unexpectedly just
before, then something is wrong and needs to be investigated
further. See Section 5.1.2, “Command Options”.
It is not a good sign if mysqld did die
unexpectedly, but in this case, you should not investigate the
Checking table...
messages, but instead try
to find out why mysqld died.
If you get corrupted tables or if mysqld always fails after some update commands, you can test whether this bug is reproducible by doing the following:
Take down the MySQL daemon (with mysqladmin shutdown).
Make a backup of the tables (to guard against the very unlikely case that the repair does something bad).
Check all tables with myisamchk -s
database/*.MYI. Repair any wrong tables with
myisamchk -r
database/table
.MYI.
Make a second backup of the tables.
Remove (or move away) any old log files from the MySQL data directory if you need more space.
Start mysqld with
--log-bin
. See
Section 5.2.3, “The Binary Log”. If you want to find a query
that crashes mysqld, you should use
--log --log-bin
.
When you have gotten a crashed table, stop the
mysqld server
.
Restore the backup.
Restart the mysqld server
without
--log-bin
Re-execute the commands with mysqlbinlog
binary-log-file | mysql. The binary log is saved
in the MySQL database directory with the name
hostname-bin.#
.
If the tables are corrupted again or you can get
mysqld to die with the above command,
you have found reproducible bug that should be easy to
fix! FTP the tables and the binary log to
ftp://ftp.mysql.com/pub/mysql/upload/ and report it in our
bugs database using the instructions given in
Section 1.7, “How to Report Bugs or Problems”. (Please note that the
/pub/mysql/upload/
FTP directory is
not listable, so you'll not see what you've uploaded in
your FTP client.) If you are a support customer, you can
use the MySQL Customer Support Center
https://support.mysql.com/ to alert the
MySQL team about the problem and have it fixed as soon as
possible.
You can also use the script mysql_find_rows to just execute some of the update statements if you want to narrow down the problem.
The preceding discussion applies only to RHEL4. The patch is unnecessary for RHEL5.
To be able to debug a MySQL client with the integrated debug
package, you should configure MySQL with
--with-debug
or
--with-debug=full
. See
Section 2.4.15.2, “Typical configure Options”.
Before running a client, you should set the
MYSQL_DEBUG
environment variable:
shell>MYSQL_DEBUG=d:t:O,/tmp/client.trace
shell>export MYSQL_DEBUG
This causes clients to generate a trace file in
/tmp/client.trace
.
If you have problems with your own client code, you should attempt to connect to the server and run your query using a client that is known to work. Do this by running mysql in debugging mode (assuming that you have compiled MySQL with debugging on):
shell> mysql --debug=d:t:O,/tmp/client.trace
This provides useful information in case you mail a bug report. See Section 1.7, “How to Report Bugs or Problems”.
If your client crashes at some 'legal' looking code, you should
check that your mysql.h
include file
matches your MySQL library file. A very common mistake is to use
an old mysql.h
file from an old MySQL
installation with new MySQL library.
The MySQL server and most MySQL clients are compiled with the DBUG package originally created by Fred Fish. When you have configured MySQL for debugging, this package makes it possible to get a trace file of what the program is debugging. See Section 26.4.1.2, “Creating Trace Files”.
This section summaries the argument values that you can specify
in debug options on the command line for MySQL programs that
have been built with debugging support. For more information
about programming with the DBUG package, see the DBUG manual in
the dbug
directory of MySQL source
distributions. It's best to use a recent distribution to get the
most updated DBUG manual.
You use the debug package by invoking a program with the
--debug="..."
or the -#...
option.
Most MySQL programs have a default debug string that is used if
you don't specify an option to --debug
. The
default trace file is usually
/tmp/program_name.trace
on Unix and
\program_name.trace
on Windows.
The debug control string is a sequence of colon-separated fields as follows:
<field_1>:<field_2>:...:<field_N>
Each field consists of a mandatory flag character followed by an
optional “,
” and comma-separated
list of modifiers:
flag[,modifier,modifier,...,modifier]
The currently recognized flag characters are:
Flag | Description |
d | Enable output from DBUG_<N> macros for the current state. May be followed by a list of keywords which selects output only for the DBUG macros with that keyword. An empty list of keywords implies output for all macros. |
D | Delay after each debugger output line. The argument is the number of
tenths of seconds to delay, subject to machine
capabilities. For example, -#D,20
specifies a delay of two seconds. |
f | Limit debugging, tracing, and profiling to the list of named functions.
Note that a null list disables all functions. The
appropriate d or t
flags must still be given; this flag only limits their
actions if they are enabled. |
F | Identify the source file name for each line of debug or trace output. |
i | Identify the process with the PID or thread ID for each line of debug or trace output. |
g | Enable profiling. Create a file called dbugmon.out
containing information that can be used to profile the
program. May be followed by a list of keywords that
select profiling only for the functions in that list. A
null list implies that all functions are considered. |
L | Identify the source file line number for each line of debug or trace output. |
n | Print the current function nesting depth for each line of debug or trace output. |
N | Number each line of debug output. |
o | Redirect the debugger output stream to the specified file. The default
output is stderr . |
O | Like o , but the file is really flushed between each
write. When needed, the file is closed and reopened
between each write. |
p | Limit debugger actions to specified processes. A process must be
identified with the DBUG_PROCESS
macro and match one in the list for debugger actions to
occur. |
P | Print the current process name for each line of debug or trace output. |
r | When pushing a new state, do not inherit the previous state's function nesting level. Useful when the output is to start at the left margin. |
S | Do function _sanity(_file_,_line_) at each debugged
function until _sanity() returns
something that differs from 0. (Mostly used with
safemalloc to find memory leaks) |
t | Enable function call/exit trace lines. May be followed by a list (containing only one modifier) giving a numeric maximum trace level, beyond which no output occurs for either debugging or tracing macros. The default is a compile time option. |
Some examples of debug control strings that might appear on a
shell command line (the -#
is typically used to
introduce a control string to an application program) are:
-#d:t -#d:f,main,subr1:F:L:t,20 -#d,input,output,files:n -#d:t:i:O,\\mysqld.trace
In MySQL, common tags to print (with the d
option) are enter
, exit
,
error
, warning
,
info
, and loop
.
I have tried to use the RTS thread packages with MySQL but stumbled on the following problems:
They use old versions of many POSIX calls and it is very tedious to make wrappers for all functions. I am inclined to think that it would be easier to change the thread libraries to the newest POSIX specification.
Some wrappers are currently written. See
mysys/my_pthread.c
for more info.
At least the following should be changed:
pthread_get_specific
should use one argument.
sigwait
should take two arguments. A lot of
functions (at least pthread_cond_wait
,
pthread_cond_timedwait()
) should return the
error code on error. Now they return -1 and set
errno
.
Another problem is that user-level threads use the
ALRM
signal and this aborts a lot of
functions (read
, write
,
open
...). MySQL should do a retry on
interrupt on all of these but it is not that easy to verify it.
The biggest unsolved problem is the following:
To get thread-level alarms I changed
mysys/thr_alarm.c
to wait between alarms
with pthread_cond_timedwait()
, but this
aborts with error EINTR
. I tried to debug the
thread library as to why this happens, but couldn't find any
easy solution.
If someone wants to try MySQL with RTS threads I suggest the following:
Change functions MySQL uses from the thread library to POSIX. This shouldn't take that long.
Compile all libraries with the
-DHAVE_rts_threads
.
Compile thr_alarm
.
If there are some small differences in the implementation,
they may be fixed by changing
my_pthread.h
and
my_pthread.c
.
Run thr_alarm
. If it runs without any
“warning,” “error,” or aborted
messages, you are on the right track. Here is a successful
run on Solaris:
Main thread: 1 Thread 0 (5) started Thread: 5 Waiting process_alarm Thread 1 (6) started Thread: 6 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 1 (1) sec Thread: 6 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 2 (2) sec Thread: 6 Simulation of no alarm needed Thread: 6 Slept for 0 (3) sec Thread: 6 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 4 (4) sec Thread: 6 Waiting process_alarm thread_alarm Thread: 5 Slept for 10 (10) sec Thread: 5 Waiting process_alarm process_alarm thread_alarm Thread: 6 Slept for 5 (5) sec Thread: 6 Waiting process_alarm process_alarm ... thread_alarm Thread: 5 Slept for 0 (1) sec end
MySQL is very dependent on the thread package used. So when choosing a good platform for MySQL, the thread package is very important.
There are at least three types of thread packages:
User threads in a single process. Thread switching is managed with alarms and the threads library manages all non-thread-safe functions with locks. Read, write and select operations are usually managed with a thread-specific select that switches to another thread if the running threads have to wait for data. If the user thread packages are integrated in the standard libs (FreeBSD and BSDI threads) the thread package requires less overhead than thread packages that have to map all unsafe calls (MIT-pthreads, FSU Pthreads and RTS threads). In some environments (for example, SCO), all system calls are thread-safe so the mapping can be done very easily (FSU Pthreads on SCO). Downside: All mapped calls take a little time and it's quite tricky to be able to handle all situations. There are usually also some system calls that are not handled by the thread package (like MIT-pthreads and sockets). Thread scheduling isn't always optimal.
User threads in separate processes. Thread switching is done by the kernel and all data are shared between threads. The thread package manages the standard thread calls to allow sharing data between threads. LinuxThreads is using this method. Downside: Lots of processes. Thread creating is slow. If one thread dies the rest are usually left hanging and you must kill them all before restarting. Thread switching is somewhat expensive.
Kernel threads. Thread switching is handled by the thread library or the kernel and is very fast. Everything is done in one process, but on some systems, ps may show the different threads. If one thread aborts, the whole process aborts. Most system calls are thread-safe and should require very little overhead. Solaris, HP-UX, AIX and OSF/1 have kernel threads.
In some systems kernel threads are managed by integrating user level threads in the system libraries. In such cases, the thread switching can only be done by the thread library and the kernel isn't really “thread aware.”