Writing good test cases for MariaDB Server

Before attempting to fix a bug, it is recommended to create a test case that showcases the wrong behaviour. One should run the test at least once before fixing and once after fixing the bug to ensure the fix is correct. Most server tests are run using mysql-test-run.pl in the mysql-test directory.

Inside the MariaDB source directory, you will find a directory named mysql-test. Until MariaDB 10.2, inside it there are two directories, t and r. t stands for the tests directory. Inside it you will find files with the .test extension. These are regular text files containing mostly valid SQL code. Each line in a test file is executed against a running server and its outputs compared to the ones found in the corresponding .result file in the r directory.

From MariaDB 10.3, the starting point for writing test cases is the mysql-test/main directory, where the .test and .result files should be located.

mysql-test-run.pl supports special extensions that allow one to run other things, such as outputting a message into the test result via --echo <Message>, or setting and using test-local variables.

The simplest way to create a test case is to create a file in the mysql-test/t/ directory.

 ~/server/$ cd mysql-test && touch t/hello.test

Inside hello.test write the following code:

--echo #
--echo # Bug #XXXXXX: INET_ATON() returns signed, not unsigned
--echo #
create table t1 select INET_ATON('255.255.0.1') as `a`;
show create table t1;
drop table t1;

The test case does multiple things. First it makes use of mysql-test-run‘s special --echo syntax. The text following echo will be printed out during the test run. This is useful for making the result file more readable and easier to track. Afterwards we create a table, show the output of show create table and finally drop the table. It is important not to leave any leftover artifacts from running a test. Any remaining tables, functions or databases will cause a test failure. It is also important to restore any used session or global variables to their initial default state.

If we want to run two or more tests and ensure that all tests run even in case of failure, we need to use (delimiter of tests is space or comma): ./mtr hello hello2 --mem --force --max-test-fail=0.
If we want to run all test cases from a specific suite: ./mtr --suite=funcs_1
If we want to run all test cases from specific suites (delimiter of a suites is a comma): ./mtr --suite=funcs_1,funcs_2.
If we want to run a specific test case from a specific suite:./mtr  funcs_1.is_check_constraints.

If we want to run a specific test case with the embedded server, make sure that the server is compiled as the embedded server and then run: ./mtr is_tables_is_embedded --embedded.

After the file is created, we can run the test case. Make sure the server is compiled. Go to the mysql-test directory and invoke mysql-test-run.pl using the symlink mtr followed by the filename you just added, minus the .test extension. The full list of mysql-test-run.pl options can be found here.

~/server/mysql-test/$./mtr hello

MTR will print status information about what it’s doing and will, at some point, start running the test. You’ll see the output of the test directly in the console. It should look something like this:

Checking supported features...
MariaDB Version 10.3.4-MariaDB
 - SSL connections supported
Collecting tests...
Installing system database...

==============================================================================

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 302, with reserved ports 16040..16059
#
# Bug #XXXXXX: INET_ATON() returns signed, not unsigned
#
create table t1 select INET_ATON('255.255.0.1') as `a`;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(21) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
main.hello                               [ pass ]      2
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.002 of 4 seconds executing testcases

Completed: All 1 tests were successful.

If the test passes and the output is correct, we need to record the result. To do this, just run the test again with:

~/server/mysql-test/$./mtr hello --record

Running the test with --record will create a .result file in the mysql-test/r/ directory. When tests have a result file, their output is no longer shown when running mtr. Make sure to always include result files too when submitting a test case for review!

Good practices for test cases

  • Ensure your test is deterministic. MTR checks for an exact match between the result file and the actual output from running the test. If rows from a select are not in the correct order, the test will be marked as failed.
  • Make sure the test case contains no environment specific contents in results, for example absolute paths. If the results must contain such information, use --replace_regex to define how to replace that information with something generic such as <ABS_PATH>
  • When writing test cases for new features, test error cases too. To require a statement to fail, use the MTR instruction: --error <error-name>. Use error names instead of error numbers as it makes the test easier to read. The error name should be the same as the one encountered in the code that throws it.
  • Make sure tests use a readable SQL syntax coding style. For example, put the SELECT clause on a separate line from the FROM clause, split the WHERE clause on different lines if the condition is too complex and separate it accordingly.
  • Name things appropriately. Instead of using just a, b as column names, use First_name, Last_name, etc.
  • Explain the purpose of SQL statements, if it makes sense to do so. For example, 
    --echo #
--echo # Create 2 users, one to test full database access and one
--echo # to test granting rights to.
--echo #
  • Add a comment before the test starts to explain the bug that it tests for, briefly. 
    --echo #
--echo # MDEV-XXXX Users not created automatically when granting a privilege.
--echo #
--echo # Test to see if granting a privilege to a non-existing user actually
--echo # creates that user.
--echo #
  • If the test already has a result file and you modified your patch, and compiled and built, you need to run ./mtr for the whole server. If some failed tests exist, you need to run them manually with ./mtr failed_test --record. A new .result file will be created which, if your patch is valid, should take into consideration modifications of your patch and pass. If you again have some failing cases, then find what is creating the failing case and if it is because of your patch try to solve it.
  • In order to see the full list of flags used for mtr tests go to the client folder and look into mysqltest.cc. For example, when you run your test and you want to display the result set vertically (which you would normally do with the ego command \Gin the mysql client), you will need to use the --vertical_resultsflag. In order to switch it back to the default flag, use --horizontal_results.

See also