WL#10928: MTR: Improvements to --check-testcases option

This WL focuses on improving check-testcases logic in MTR. MTR check-testcases 
perform checks just before and after the test execution to ensure there are no 
changes in the internal state of the Database.

Based on the observations and filed bugs, below scenarios have been added to 
check before and after a test is run:

- Following MTR bugs should be fixed(May be not all scenarios)
  1. MTR'S CHECK-TESTCASE DOES NOT CHECK FOR TABLESPACES
  2. MTR DOES NOT RUN CHECK-TESTCASE ON MYSQL.GLOBAL_GRANTS
  3. MTR IS NOT CHECKING IF PERSISTENT VARIABLE HAS BEEN RESET AT END OF TEST.

- Evaluate for all the possible performance-schema tables that can be added in 
check-testcases

- Evaluate for all the possible information-schema tables that can be added in 
check-testcases.

- Evaluate for all the possible mysql tables that can be added in check-testcases

- Evaluate for any new type of server variable (eg. persisted).

FR1. check-testcase shall fail in case there is a mismatch in the tablespace 
created before and after the test.

FR2. check-testcase shall fail if a persisted variable has a different value 
before and after the test

FR3. check-testcase shall fail for all the listed performance_schema tables in 
case there is a difference in checksum before and after the test. 

FR4. check-testcase shall fail for all the listed mysql tables in case there is a 
difference in checksum before and after the test.

FR5. check-testcase shall fail if the cleanup of temporary files created within 
the test is not done.

FR6. check-testcase shall fail if the new client connections are created in a 
replication setup and not closed within the test.

 

There are no observable consequences of the feature in terms of how MTR is used. 
Only user visible change is that check-testcase has more checks. So the users 
will see test failures when the below conditions are not met:

1. When a component is installed within the test, but the test author misses to 
uninstall the same within the test.

2. When a plugin is installed within the test, but the test author misses to 
uninstall the same within the test.

3. When a new file (eg. log or data file) is created within the test in the var 
or tmp directory but the test author misses to remove them within the test.

4. When replication tests creates multiple master/slave connections, but miss to
close the connection.

5. When a new tablespace is added within the test but the test author misses to 
drop the tablespace within the test.

6. When a variable is set to persisted within the test, but the test author 
misses to reset the same within the test.

7. When any UDF (user defined function) is created in the test, but not dropped.

 The currently checked mysql tables are:
 1. mysql.columns_priv,
 2. mysql.db,
 3. mysql.func,
 4. mysql.help_category,
 5. mysql.help_keyword,
 6. mysql.help_relation,
 7. mysql.host,
 8. mysql.procs_priv,
 9. mysql.tables_priv,
 10. mysql.time_zone,
 11. mysql.time_zone_leap_second,
 12. mysql.time_zone_name,
 13. mysql.time_zone_transition,
 14. mysql.time_zone_transition_type,
 15. mysql.user,
 16. mysql.global_grants;

Following tables are planned to be added as part of this WL:
1. Added checks for mysql.component
2. Added checks for mysql.help_topic
3. Added checks for mysql.plugin


Following tables are planned to be removed from mtr_check.sql
1. mysql.host - This table does not exist anymore.


Following mysql tables cannot be included due to various reasons:

*Contains cost estimates info for operations that occur during query execution.
1. engine_cost 
2. server_cost 

*This table stores the general activities like queries executed from a session.
3. general_log 
4. slow_log

*Replication tables
5. slave_master_info
6. slave_relay_log_info
7. slave_worker_info
8. gtid_executed

Few Innodb tables
9. innodb_ddl_log           - Stores DDL logs for crash-safe DDL operations.
10. innodb_index_stats       - Used for InnoDB persistent optimizer statistics.
11. innodb_table_stats       - Used for InnoDB persistent optimizer statistics.
12. innodb_dynamic_metadata - Used by the InnoDB storage engine to store fast-
                              changing table metadata such as auto-increment 
                              counter values.

13. password_history - Not needed. As the table is truncated when a user is 
                       dropped.

14. servers - Used by Federated storage engine.

=======================================================================

Most of the PFS tables are dynamic in nature as it contains real time data based 
upon instruments/events enabled. Other few tables are data place holders which 
stores report data. Some tables contains memory related information. All such 
tables cannot be included in MTR check-testcase:

The currently checked performance_schema tables are:
1. setup_instruments
2. setup_consumers
3. setup_actors
4. setup_objects

Following new tables are added:
1. prepared_statement_instances
2. user_defined_functions

==========================================================================
Currently following IS tables are checked:
1. schemata /*checks for any new database created ans not dropped*/
2. files /* check for any new file added - eg. tablespace (.ibd), log files */
3. tables /* check for any new tables added */
4. events /* check for any new event */
5. triggers /* check for any new triggers */
6. routines /* check for any routines */
7. views /* check for any new views */

No new inclusions.