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

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

All statements that produce results—for example, SELECT, SHOW, or EXPLAIN—must be preceded with @/path/to/result/file. The file must contain the expected results. An easy way to generate the result file is to run mysqltest -r < t/test_name.test from the mysql-test directory, and then edit the generated result files, if needed, to adjust them to the expected output. In that case, be very careful about not adding or deleting any invisible characters. Make sure to only change the text or delete lines. If you have to insert a line, make sure that the fields are separated by a hard tab, and that there is a hard tab at the end. You may want to use od -c to make sure that your text editor has not messed up anything during editing. We hope that you never have to edit the output of mysqltest -r because this should be necessary only when you find a bug.

To be consistent with our setup, you should put your result files in the mysql-test/r directory and name them 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 is expected to return an error, you should specify it with --error error_number on the line before the statement. You can specify more than one error number, separated by commas.

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

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

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

To sleep between statements, use the sleep command. It supports fractions of a second. For example, sleep 1.3; sleeps 1.3 seconds.

To run the slave with additional options for your test case, put them in the command-line format in mysql-test/t/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/

Do not file a bug report before you have found out as much as possible about what when wrong. See the instructions at Section 1.8, “How to Report Bugs or Problems”.

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

Check whether an individual test in the test suite also fails when run on its own:

cd mysql-test
mysql-test-run --local test_name

If this fails, you should configure MySQL with --with-debug and run mysql-test-run with the --debug option. If this also fails, send the trace file mysql-test/var/tmp/master.trace to ftp://ftp.mysql.com/pub/mysql/upload/ so that we can examine it. Please remember to also include a full description of your system, the version of the mysqld binary and how you compiled it.

Run mysql-test-run with the --force option to see whether any other tests fail.

If you have compiled MySQL yourself, check our manual to see whether there are any platform-specific issues for your system (see Section 2.12, “Operating System-Specific Notes”). There might be configuration workarounds to deal with the problems that you observe. Also, consider using one of the binaries we have compiled for you at http://dev.mysql.com/downloads/. All our standard binaries should pass the test suite!

If you get an error such as Result length mismatch or Result content mismatch it means that the output of the test was not an exact match for the expected output. This could be a bug in MySQL or it could be that your version of mysqld produces slightly different results under some circumstances.

The results file is located in the r directory and has a name with a .result extension. A failed test result is put in a file with the same basename as the result file and a .reject extension. If your test case is failing, you should use diff to compare the .result and .reject files. If you cannot see how they are different, examine both with od -c and also check their lengths.

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

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

If you have not compiled MySQL for debugging you should probably do so by specifying the --with-debug option when you invoke configure. See Section 2.8.2, “Typical configure Options”.

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

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.