How to Create Good Tests
[edit] How to Create Good Test Cases
- There are a lot of strict rules and rules of thumb which may increase the quality of tests written for the tool pair mysqltest/mysqltest-run.pl.
- The following presentation should give you some hints, but it should not replace reading our excellent manual about testing http://dev.mysql.com/doc/mysqltest/en/writing-tests.html.
- Formal stuff
- Some rules outside of the formal stuff
- "Negative" tests
- Variants of test cases
- Miscellaneous tips
In this document, the term "protocol" means "output from a test case."
[edit] Formal stuff
- Rules about formatting within scripts and similar stuff.
- Coding style
- SQL statement example
- Header of scripts
[edit] Coding style
Please have a look at the C/C++ coding guidelines and apply them when they make sense: Coding Guidelines
Some more or less strict rules:
- Avoid lines longer than 80 characters unless there is no other choice.
- Comment lines should start with '#' and not with '--'.
- Use spaces, not tabs.
- Write SQL statements in the style of the MySQL Reference Manual
- SQL keywords and reserved words: uppercase
- Identifiers (table names, column names, etc.): lowercase
- Ignore this guideline if your intent is to check the processing of mixed lettercases ;-)
- Please follow this guideline for new tests. IMHO this should be better not done in existing tests except you rewrite huge parts.
- If an SQL statement is long, add line breaks to reformat it and make it easier to read.
[edit] SQL statement example
SELECT f1 AS "my_column", f10 ....
FROM mysqltest1.t5
WHERE (f2 BETWEEN 17 AND 25 OR f2 = 61)
AND f3 IN (SELECT ....
FROM mysqltest1.t4
WHERE .....)
ORDER BY ...
[edit] Header of scripts
For each test or auxiliary script, create a header that contains the following information:
- Purpose of the test or script
- Corresponding WL task, if there is any
- Creator of the test and date of creation
- Author of last significant change + date of change + what was changed
- Dates should be in ISO format (ISO 8601): YYYY-MM-DD
- In case that the script assigns values to some variables and sources some master test script, please explain the purpose of these variables and why you use these values
Note: The header, like the rest of the test, should not mention confidential information. Remember, our tests are available publicly.
[edit] Header example
########### suite/funcs_1/t/a_processlist_val_no_prot.test ############# # # # Testing of values within INFORMATION_SCHEMA.PROCESSLIST # # # # The prepared statement variant of this test is # # suite/funcs_1/t/b_processlist_val_ps.test. # # # # There is important documentation within # # suite/funcs_1/datadict/processlist_val.inc # # # # Note(mleich): # # The name "a_process..." with the unusual prefix "a_" is # # caused by the fact that this test should run as second test, that # # means direct after server startup and a_processlist_priv_no_prot. # # Otherwise the connection IDs within the processlist would differ. # # # # Creation: # # 2007-08-09 mleich Implement this test as part of # # WL#3982 Test information_schema.processlist # # # ########################################################################
[edit] Comments Everywhere
Write comments, they save the time of others.
- Complicated test architecture or tricky code
- A sub test case is able to reveal a bug
- Sub tests
- Make test protocols more understandable
- Better protocols example
[edit] Complicated test architecture or tricky code
If your test is very complicated, write a sufficient explanation of the test architecture. This helps avoid having someone else come along and unintentially change the test in ways that:
- Shift the goal of the check
- Destroy your test architecture
- etc.
Comment your code when you do or check something that someone else may think is not trivial. Please write what you intend to check if it is not obvious.
[edit] A subtest case is able to reveal a bug
Write a comment if the next subtest revealed a bug in history. Please mention number and title.
Example:
# Bug#3671 Stored procedure crash if function has "set @variable=param"
[edit] Subtests
At least in cases where your file contains many subtests
- Mark these subtests for better readability of the script
- Write also a message into the protocol.
And please explain what each subtest checks, unless it is obvious.
Example:
--echo #----------------- Testcase 3.3.1.52 ---------------------# ############################################################################### # Ensure that a view that is a subset of every column and some rows of a single # underlying table, contains the correct row-and-column data; such a view has # a definition that is semantically equivalent to CREATE VIEW <view_name> # AS SELECT * FROM <table_name> WHERE ... ###############################################################################
[edit] Make test protocols more understandable
Please have the test write comments into the protocol if this makes the surrounding protocol content much more understandable. This is especially true for tests that do the following:
- Suppress the printing of SQL statements and result sets
- Work with more than one connection
[edit] Better protocols example
Good script with message about switching the connection:
SET @aux = 1; SELECT @aux AS "content of @aux is"; --echo # Establish session con1 (user=root) connect (con1,localhost,root,,,,); SELECT @aux AS "content of @aux is";
Protocol of good script:
SET @aux = 1; SELECT @aux AS "content of @aux is"; content of @aux is 1 # Establish session con1 (user=root) SELECT @aux AS "content of @aux is"; content of @aux is NULL
This is what the protocol looks like without the comment. The output becomes less clear because there is no indication that the connection has been changed.
SET @aux = 1; SELECT @aux AS "content of @aux is"; content of @aux is 1 SELECT @aux AS "content of @aux is"; content of @aux is NULL
[edit] Some Rules Outside of the Formal Stuff
- Prerequisites checks
- Error masking
- Use of the option "--disable_abort_on_error"
- Perfect cleanup at start and end of test
- Use of OS-specific commands
[edit] Error masking
Use error names instead of error numbers whenever possible.
Example:
--error ER_BAD_DB_ERROR USE <not existing database>;
You can find the error names in the include/mysqld_error.h file of a MySQL source distribution, or in the Server Error Codes and Messages section of the MySQL Reference Manual].
[edit] Use of the option "--disable_abort_on_error"
This option is very useful when starting to write a new test because the test will not abort if your script contains some failing statements or SQL syntax errors. By disabling the abort, you get to see more of the errors per test run and can fix more of them at a time.
But there are only rare situations where the final versions of a test should use "--disable_abort_on_error", either at all or during a sequence of several SQL statements.
[edit] Perfect cleanup at start and end of test
- Cleaning up becomes much more comfortable and less error prone if you create your "own" database and create all tables there. At the end of the test, you need drop drop this database.
- Do not forget to remove all users you created and all permissions you granted. Otherwise the next test might fail when checking grants in general.
- Close all connections which you have explicitly created.
[edit] Use of OS-specific commands
The exec and system commands enable tests to execute external commands. However, many such commands are available only on certain platforms. (For example, rm is Unix-specific and not available on Windows.) Please avoid these commands if possible. They harm the portability and stability of tests.
Have a look at the mysqltest manual and the t/mysqltest.test test file. There are now several mysqltest built-in commands such as
"--write_file", "--cat_file", "--remove_file", ...
which are reliable on all operating systems. Sometimes OS commands could be also replaced by SQL statement sequences writing to and reading from files and some SQL string functions.
[edit] "Negative" Tests
A "negative" test is a test for which you expect to see a failure. If an error does not occur, that itself indicates a problem.
DO NOT FORGET "NEGATIVE" TESTS where we expect to see fine error messages from the server.
This section contains just a few examples of what to test. Please be creative and imagine what could go wrong in rough reality.
Column with numeric data type:
- NULL, 0
- Minimum - 1, Minimum, Minimum + 1
- Maximum - 1, Maximum, Maximum + 1
- Negative values if data type is unsigned
- Garbage like 'abc', '1a', 'a1'
Column with string data type:
- Null, <empty string>, "exotic" characters like 'รค', single quotes, ...
- String longer than column
Limited number of tables, columns, variables, ...
- Maximum - 1, Maximum, Maximum + 1
Assume an SQL operation that affects the filesystem (LOAD DATA, CREATE SCHEMA or TABLE, backup/restore, ...). What will happen if the following conditions occur for a file or directory to be read, written, or implicitly created or deleted?
- Exists/does not exist
- Is writable/not writable(missing permission)
- Is empty/not empty
- Contains the expected content (Example: text)/unexpected content (Example: JPG)
- Is a regular file/a directory/a softlink pointing to .../a pipe
- Is assigned via an extremely long path
- Becomes victim of file system full
[edit] Tests with Several Variants
Please think twice before you create engine-specific variants of a test or let runs with
- ps-protocol/sp-protocol/cursor-protocol/view-protocol
happen. They might be
- of low value and a permanent wasting of resources and/or
- fail (protocol variants only)
[edit] Example for storage engines
- Checks of the INFORMATION_SCHEMA
- The storage engines to be used for information_schema tables are hardcoded. Therefore tests focussed on permissions, optimizer strategies, column data types etc. when selecting on INFORMATION_SCHEMA tables should not run with storage engine variations.
- Please prepare the scripts at least for use with different storage engines.
- That means mostly:
- DO NOT use hardcoded storage engine assignments within CREATE TABLE statements.
- If you assume that there is no significant impact of storage engines on your testing object,
- create/run with storage engine variants and check this.
Solution 1:
Do not assign the storage engine within your CREATE TABLE statements at all. The default storage engine MyISAM will be used for your tables. Check your assumption with:
./myql-test-run.pl --mysqld="--default-storage-engine=<engine>" <test_case>
Solution 2:
Assign the storage engine to be used via $variable.
Top level script:
let $engine_type= MyISAM;
The same script or a sourced script:
eval CREATE TABLE .... ENGINE = $engine_type ...
Check your assumption by creating and running storage engine variants of the top level test.
--source include/have_falcon.inc let $engine_type= Falcon;
[edit] Example for the protocols
It is usual to run tests with and without the mysql-test-run.pl startup option "--ps-protocol". And there are also attempts of System QA to run the other protocols.
- Effect of the --ps-protocol option: mysqltest will run as many SQL statements as possible as prepared statements.
- Effect of the --sp-protocol/--cursor-protocol/--view-protocol options: As far as I know, mysqltest takes many statements and transforms them into a statement sequence checking the feature (stored procedures, cursors, or views).
We have a lot of tests running very similar and extremely simple SQL just for the creation of a situation to be tested, check of table content, etc. So it can be assumed that the n'th test running again simple statements does not improve the coverage.
Conclusion:
If your test contains
- Simple statements: Prevent the non valuable runs for "protocols".
- Storage engine variations: Prevent the protocol runs for all except one storage engine (my take would be MyISAM).
- "Unique" and complex statements:
- ps-protocol statements = all SQL
- sp-protocol statements = DML
- view/cursor-protocols statements = SELECTs
- Do not prevent the protocol runs.
Another problem around runs with such protocols is that there are cases where we get different protocol content.
Example:
Script:
SELECT * FROM processlist ...
Protocol content if running without any "--*-protocol":
ID USER ... COMMAND ... STATE INFO 1 root ... Query ... preparing SELECT * FROM processlist ...
Protocol content if running with "--ps-protocol":
ID USER ... COMMAND ... STATE INFO 1 root ... Execute ... preparing SELECT * FROM processlist ...
So please check whether every new test gives the same result with every protocol but at least with "--ps-protocol". If not, do something of the following:
- Exclude some protocol variants from execution.
- Disable the the use of the protocols for problematic statements.
- Write protocol variant specific tests.
Example solution (code within the top level scripts):
# The file with expected results fits only to a run without
if (`SELECT $PS_PROTOCOL + $SP_PROTOCOL
+ $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0`)
{
--skip Test requires: ps-protocol/sp-protocol/cursor-protocol/view-protocol disabled
}
--source include/<whatever>.inc
or
# The file with expected results fits only to a run with "--ps-protocol".
if (`SELECT $SP_PROTOCOL + $CURSOR_PROTOCOL + $VIEW_PROTOCOL > 0
OR $PS_PROTOCOL = 0`)
{
--skip Test requires: ps-protocol enabled, other protocols disabled
}
--source include/<whatever>.inc
[edit] Miscellaneous Tips
[edit] Stability checks for random timing problems
One server start, several test runs, use an extremely fast filesystem
./mysql-test-run.pl --mem .... \
`perl -e 'for (1 .. 50) { print "<name of test> "}'`
One server start, several test runs, use a slow disk based filesystem
rmdir var # "var" must not be a symlink pointing into a RAM based filesystem
./mysql-test-run.pl .... \
`perl -e 'for (1 .. 50) { print "<name of test> "}'`
# Recommended: Increase the I/O load on the disk where "var" exists by parallel activities.
# Example: tar -chvf - <some path> > /dev/null
# I found a lot of timing issues when writing tests for PROCESSLIST by using this check.
Several server start+test runs
set +x
run=50
while [ $run -gt 0 ]
do
./mysql-test-run.pl --mem <name of test>
RC=$?
echo RC: $RC
if [ $RC -eq 0 ]
then
run=`expr $run - 1`;
echo $run
else
run=0
fi
done
[edit] Stability checks for hostname-related problems
sudo hostname <other hostname> ./mysql-test-run.pl --mem <name of test>
Good values for <other hostname> to check are "0", "bbb", "mmm", "zzz".
[edit] Stability checks for unexpected problems
Ask a colleague to run your test or try another testing box (preferably one with a different operating system).
[edit] Row order within result sets
Please keep in mind that the row order within a significant number of result sets depends on storage engine properties and in worst cases the current load (NDB!, partitioning?, parallel query?) on the testing box.
Either use
--sorted_result SELECT * FROM t1;
or, if possible, "decorate" your SELECT with ORDER BY to make the row order static.
[edit] Test case behavior should depend on the result of a SHOW
Please have a look at the t/mysqltest.test file where "query_get_value()" is checked.
[edit] Does a test execution hang?
Symptom: No text flooding through the command window where a test is running.
Run this command in a different window:
tail -f <var_dir>/master-data/mysql/general_log.CSV
If you see some changes, your test is alive.
[edit] Your test is complicated, support debugging
Top level test script:
... ##### Option, for debugging support ##### let $debug= 0; ...
At various places within your scripts:
if ($debug)
{
--echo # var1: $var1 , var2: $var2, .....
SELECT ......
}
or
--disable_query_log
if ($debug)
{
--enable_query_log
}
[edit] You need to know some syntax, option etc., but hate reading manuals
Try this:
grep -i '<one keyword you know or guess>' t/* include/* | less