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

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

All queries that produce results—for example, SELECT, SHOW, or EXPLAIN—must be preceded with @/path/to/result/file. The file must contain the expected results. An easy way to generate the result file is to run mysqltest -r < t/test-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 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.

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

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

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

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

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

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

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

If you have a question about the test suite, or have a test case to contribute, send an email message to the MySQL internals mailing list. See Section 1.7.1, “MySQL Mailing Lists”. This list does not accept attachments, so you should FTP all the relevant files to: ftp://ftp.mysql.com/pub/mysql/upload/

Don't file a bug report before you have found out as much as possible of what when wrong! See the instructions at Section 1.8, “How to Report Bugs or Problems”.

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

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

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

If this fails, then you should configure MySQL with --with-debug and run mysql-test-run with the --debug option. If this also fails send the trace file var/tmp/master.trace to ftp://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.

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

If you have compiled MySQL yourself, check our manual for how to compile MySQL on your platform or, preferable, use one of the binaries we have compiled for you at http://dev.mysql.com/downloads/. All our standard binaries should pass the test suite!

If you get an error such as Result length mismatch or Result content mismatch it means that the output of the test 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.

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

If you have compiled MySQL with debugging you can try to debug this by running mysql-test-run with either or both of the --gdb and --debug options. See Section E.1.2, “Creating Trace Files”.

If you have not compiled MySQL for debugging you should probably do that. Just specify the --with-debug options to configure. See Section 2.8, “MySQL Installation Using a Source Distribution”.

type

The plugin type. This must be one of the plugin-type values from plugin.h. For a full-text parser plugin, the type value is MYSQL_FTPARSER_PLUGIN.

info

A pointer to the descriptor for the plugin. Unlike the general plugin declaration structure, this descriptor's structure depends on the particular type of plugin. Each descriptor has a version number that indicates the API version for that type of plugin, plus any other members needed. The descriptor for full-text plugins is described in Section 27.2.5.2, “Type-Specific Plugin Structures and Functions”.

name

The plugin name. This is the name that will be listed in the plugin table and by which you refer to the plugin in SQL statements such as INSTALL PLUGIN and UNINSTALL PLUGIN.

author

The plugin author. This can be whatever you like.

desc

A general description of the plugin. This can be whatever you like.

init

A once-only initialization function. This is executed when the plugin is loaded, which happens for INSTALL PLUGIN or, for plugins listed in the plugin table, at server startup. The function takes no arguments. It returns zero for success and non-zero for failure.

deinit

A once-only deinitialization function. This is executed when the plugin is unloaded, which happens for UNINSTALL PLUGIN or, for plugins listed in the plugin table, at server shutdown. The function takes no arguments. It returns zero for success and non-zero for failure.

For indexing, the server calls the parser for each column value to be indexed.

For searching, the server calls the parser to parse the search string. The parser might also be called for rows processed by the statement. In natural language mode, there is no need for the server to call the parser. For boolean mode phrase searches or natural language searches with query expansion, the parser is used to parse column values for information that is not in the index. Also, if a boolean mode search is done for a column that has no FULLTEXT index, the built-in parser will be called. (Plugins are associated with specific indexes. If there is no index, no plugin is used.)

For the plugin declaration in the plugin library descriptor, the initialization and deinitialization functions are invoked when the plugin is loaded and unloaded.

For the plugin descriptor, the initialization and deinitialization functions are invoked per SQL statement for which the plugin is used.

mysql_parse

A pointer to a callback function that invokes the server's built-in parser. Use this callback when the plugin acts as a front end to the built-in parser. That is, when the plugin parsing function is called, it should process the input to extract the text and pass the text to the mysql_parse callback.

The first parameter for this callback function should be the mysql_ftparam member of the parsing context structure. That is, if param points to the structure, invoke the callback like this:

param->mysql_parse(param->mysql_ftparam, ...);

A front end plugin can extract text and pass it all at once to the built-in parser, or it can extract and pass text to the built-in parser a piece at a time. However, in this case, the built-in parser treats the pieces of text as though there are implicit word breaks between them.

mysql_add_word

A pointer to a callback function that adds a word to a full-text index or to the list of search terms. Use this callback when the parser plugin replaces the built-in parser. That is, when the plugin parsing function is called, it should parse the input into words and invoke the mysql_add_word callback for each word.

The first parameter for this callback function should be the mysql_ftparam member of the parsing context structure. That is, if param points to the structure, invoke the callback like this:

param->mysql_add_word(param->mysql_ftparam, ...);

ftparser_state

This is a generic pointer. The plugin can set it to point to information to be used internally for its own purposes.

mysql_ftparam

This is set by the server. It is passed as the first argument to the mysql_parse or mysql_add_word callback.

cs

A pointer to information about the character set of the text, or 0 if no information is available.

doc

A pointer to the text to be parsed.

length

The length of the text to be parsed, in bytes.

mode

The parsing mode. This value will be one of the folowing constants:

type

The token type. This should be one of values shown in the following table:

yesno

Whether the word must be present for a match to occur. 0 means that the word is optional but increases the match relevance if it is present. Values larger than 0 mean that the word must be present. Values smaller than 0 mean that the word must not be present.

weight_adjust

A weighting factor that determines how much a match for the word counts. It can be used to increase or decrease the word's importance in relevance calculations. A value of zero indicates no weight adjustment. Values greater than or less than zero mean higher or lower weight, respectively. The examples at Section 12.7.1, “Boolean Full-Text Searches”, that use the < and > operators illustrate how weighting works.

wasign

The sign of the weighting factor. A negative value acts like the ~ boolean-search operator, which causes the word's contribution to the relevance to be negative.

trunc

Whether matching should be done as if the boolean-mode * truncation operator had been given.

A plugin library descriptor that indicates the version number of the general plugin API that the library uses and that contains a general declaration for each plugin in the library.

Each plugin general declaration contains information that is common to all types of plugin: A value that indicates the plugin type; the plugin name, author, and description; and pointers to the initialization and deinitialization functions that the server invokes when it loads and unloads the plugin.

The plugin general declaration also contains a pointer to a type-specific plugin descriptor. The structure of these descriptors can vary from one plugin type to another, because each type of plugin can have its own API. A plugin descriptor contains a type-specific API version number and pointers to the functions that are needed to implement that plugin type. For example, a full-text parser plugin has initialization and deinitialization functions, and a main parsing function. The server invokes these functions when it uses the plugin to parse text.

The plugin library contains the interface functions that are referenced by the library descriptor and by the plugin descriptors.

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.

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.1, 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.1.

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);

unsigned int arg_count

The number of arguments. Check this value in the initialization function if you require your function to be called with a particular number of arguments. For example:

if (args->arg_count != 2)
{
    strcpy(message,"XXX() requires two arguments");
    return 1;
}

enum Item_result *arg_type

A pointer to an array containing the types for each argument. The possible type values are STRING_RESULT, INT_RESULT, and REAL_RESULT.

To make sure that arguments are of a given type and return an error if they are not, check the arg_type array in the initialization function. For example:

if (args->arg_type[0] != STRING_RESULT ||
    args->arg_type[1] != INT_RESULT)
{
    strcpy(message,"XXX() requires a string and an integer");
    return 1;
}

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

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

char **args

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

For each invocation of the main function, args->args contains the actual arguments that are passed for the row currently being processed.

Functions can refer to an argument i as follows:

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).

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.