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

A test case consists of ; terminated statements and is similar to the input of mysql command-line client. A statement by default is a SQL statment 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 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.

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

Don't send a bug report before you have found out as much as possible of what when wrong! When you do it, please use the mysqlbug script so that we can get information about your system and MySQL version. See Section 1.7.1.3, “How to Report Bugs or Problems”.

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

If a test in the test suite fails, check 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 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”.

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