All tests that modify data to check that everything works good should be in a development environment.
However, some tests can just check data integrity or look for configuration problems, without modifying anything or stressing the server. Tbose tests may be useful in a production environment.
All BTs and TSs should be declared with the MODIFIES SQL DATA option, because data about TRs and their results are always written in STK/Unit tables. If TCs have some internal Stored Functions, those Functions should be declared as NOT DETERMINISTIC (which is the default) even if they are deterministic.
Settings during test creation
When creating tests, a restrictive SQL_MODEshould be used to make debugging easier. The best SQL_MODE is:
SET @@session.SQL_MODE = 'ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
It is a good practice to make sure that log_bin_trust_function_creators is set to 1 (default), to catch some mistakes in your Stored Programs definition.
Settings during test execution
When executing tests, sql_warnings should be set to 1. This way, if the INSERTs and UPDATEs in your Stored Programs generate some warnings, you can catch them.
Also, innodb_strict mode should be enabled to make debugging easier.
Don't set foreign_key_checks or unique_checks to OFF, because this would cause problems.
- Try to avoid generating resultsets within tests, so it will be easier to read results from the mysql client.
- If possible, write in your BT's comments what they test. Use this format: 'Test routine_name()' because in a future version it may be parsed by STK/Unit.