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]. NOTE: Don't do this with error numbers > 2000. Use of the error name does not seem to work well in these cases.
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.
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 to drop just 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 and please ensure that the disconnect is finished = the sessions are no more visible within the processlist.
Remove all auxiliary files created within your test.
MTR2 runs now by default with "check-testcases" enabled. It checks if there are additional objects like user or tables, modifed system table etc. "check-testcases" will be soon improved. However, proper cleanup should still be a responsibility of both the test author and reviewer.
The exec and system commands enable tests to execute external commands. However, many of 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.
