Writing loops

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

let $1= 1000;
while ($1)
{
 # execute your statements here
 dec $1;
}

Pausing between statements

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

Try not to use sleep or real_sleep commands more than necessary. The more of them there are, the slower the test suite becomes. In some cases, heavy reliance on sleep operations is an indicator that the logic of a test should be reconsidered.

Commenting the test result

When the output in a result file is not understandable by inspection, it can be helpful to have the test case write comments to the result file that provide context. You can use the echo command for this:

--echo # Comment to explain the following output

Of course, the same line (without --echo) will need to be put in the corresponding place in the result file, if you are adding this to an existing test.

Sorting result sets

If a test case depends on SELECT output being in a particular row order, use an ORDER BY clause. Do not assume that rows will be selected in the same order they are inserted, particularly for tests that might be run multiple times under conditions that can change the order, such as with different storage engines, or with and without indexing.

If you do not want to add ORDER BY to the query, an alternative is to use the test command sorted_result which will sort the result of the following statement before putting it into the result file.

Performing file system operations

Avoid using exec or system to execute operating system commands for file system operations. This used to be very common, but OS commands tend to be platform specific, which reduces test portability. mysqltest now has several commands to perform these operations portably, so these commands should be used instead: remove_file, chmod, mkdir, and so forth.

Local versus remote storage

Some test cases depend on being run on local storage, and may fail when run on remote storage such as a network share. For example, if the test result can be affected by differences between local and remote file system times, the expected result might not be obtained. Failure of these test cases under such circumstances does not indicate an actual malfunction. It is not generally possible to determine whether tests are being run on local storage.