MySQL distributions include a test suite: a set of test cases and programs for running them.

(If you find that the test suite is not included in your distribution, look for a similar distribution with -test in the name and install that as well.) These tools constitute the MySQL test framework that provides a means for verifying that MySQL Server and its client programs operate according to expectations. The test cases consist mostly of SQL statements, but can also use test language constructs that control how to run tests and verify their results. Distributions also provide facilities for running unit tests and creating new unit tests.

This document describes the components of the MySQL test framework, how the test programs work, and the language used for writing test cases. It also provides a tutorial for developing test cases and executing them.

The application that runs the test suite is named mysql-test-run.pl. Its location is the mysql-test directory, which is present both in source and binary MySQL Server distributions.

On platforms other than Windows, mysql-test-run.pl is also available through the shortened name mtr in the same directory, as either a symbolic link or a copy.

The mysql-test-run.pl application starts MySQL servers, restarts them as necessary when a specific test case needs different start arguments, and presents the test result. For each test case, mysql-test-run.pl invokes the mysqltest program (also referred to as the “test engine”) to read the test case file, intepret the test language constructs, and send SQL statements to the server.

Input for each test case is stored in a file, and the expected result from running the test is stored in another file. The actual result is compared to the expected result after running the test.

For a MySQL source distribution, mysql-test-run.pl is located in the mysql-test directory, and mysqltest is located in the client directory. The mysql-test and client directories are located in the root directory of the distribution.

For a MySQL binary distribution, mysql-test-run.pl is located in the mysql-test directory, and mysqltest is located in the same directory where other client programs such as mysql or mysqladmin are installed. The locations of the mysql-test and other directories depend on the layout used for the distribution format.

Within the mysql-test directory, test case input files and result files are stored in the t and r directories, respectively. The input and result files have the same basename, which is the test name, but have extensions of .test and .result, respectively. For example, for a test named “decimal”, the input and result files are mysql-test/t/decimal.test and mysql-test/r/decimal.result.

Each test file is referred to as one test case, but usually consists of a sequence of related tests. An unexpected failure of a single statement in a test case makes the test case fail.

There are several ways a test case can fail:

The mysqltest test engine checks the result codes from executing each SQL statement in the test input. If the failure is unexpected, the test case fails.
A test case can fail if an error was expected but did not occur (for example, if an SQL statement succeeded when it should have failed).
The test case can fail by producing incorrect output. As a test runs, it produces output (the results from SELECT, SHOW, and other statements). This output is compared to the expected result found in the mysql-test/r directory (in a file with a .result suffix). If the expected and actual results differ, the test case fails. The actual test result is written to a file in the mysql-test/var/log directory with a .reject suffix, and the difference between the .result and .reject files is presented for evaluation.
A test case will fail if the MySQL server dies unexpectedly during the test. If this happens, the mysqltest test client will usually also report a failure due to loosing the connection.
Finally, the test case will fail if the error log written by the MySQL server during the test includes warnings or errors which are not filtered (suppressed). See Suppressing Errors and Warning for more about suppressing warnings.

This method of checking test results puts some restrictions on how test cases can be written. For example, the result cannot contain information that varies from run to run, such as the current time. However, if the information that varies is unimportant for test evaluation, there are ways to instruct the test engine to replace those fields in the output with fixed values.

Because the test cases consist mostly of SQL statements in a text file, there is no direct support for test cases that are written in C, Java, or other languages. Such tests are not within the scope of this test framework. But the framework does support executing your own scripts and initiating them with your own data. Also, a test case can execute an external program, so in some respects the test framework can be extended for uses other than testing SQL statements. Finally, it is possible to embed small pieces of Perl code within the test. This can sometimes be used to perform actions or execute logic which is beyond the capabilities of the test language or SQL.