Supporting continuity and open collaboration

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 in the mysql-test directory.

Inside the MariaDB source directory, you will find a directory named mysql-test. 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. 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('') 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.

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 using the symlink mtr followed by the filename you just added, minus the .test extension.

~/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('') as `a`;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` bigint(21) unsigned DEFAULT NULL
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 #

Platinum Sponsors

MariaDB Foundation Platinum sponsors

Gold Sponsors

MariaDB Foundation Gold sponsors

Code statistics