WL#8795: Evaluate defaults set by MTR and change them to use server defaults where possible

mysql-test-run.pl (MTR) has a configuration file which sets values for various server variables. Some of them maybe needed to run tests with limited resources available on regression test machines, while some others maybe there due to legacy reasons that are not valid anymore. Evaluate all default settings made by MTR and assess whether they are really needed.

User Documentation

Changes for test suite. No user documentation required.

F1 * MTR shall not set value for any server variables unless there are unavoidable reasons to do so

MTR settings are defined in include/default-mysqld.cnf

[mysqld]


# The variables which will be set to their defaults:
#==================================================================

# Server defaults so no need to set it
open-files-limit=           1024
local-infile
character-set-server=      latin1

# Default is 8M. Recorded result of main.partition_key_cache
key_buffer_size=            1M

# Server default so no need to set it
sort_buffer_size=           256K

# Default is 16M. Recorded results of a few tests
max_heap_table_size=        1M

# Server default is ibdata1:12M:autoextend
# No failures if run with default so can set it to default
loose-innodb_data_file_path=      ibdata1:10M:autoextend

# Default is 128M. Modified the innodb.innodb test
# and modified mysql-test-run.pl
loose-innodb_buffer_pool_size=    24M

# Default is 1024. Recorded the result of sys_vars.innodb_lru_scan_depth_basic
loose-innodb_lru_scan_depth=      100

# Default is 4. But set to 2 in rpl tests. Recorded the result of
sys_vars.innodb_write_io_threads_basic
loose-innodb_write_io_threads=    2

# Default is 4. But set to 2 in rpl tests. Recorded the result of
sys_vars.innodb_read_io_threads_basic
loose-innodb_read_io_threads=     2

# No failures when set to its default value of 16M
loose-innodb_log_buffer_size=     1M

# Default is 48M. Recorded the result of main.innodb_log_file_size_functionality
# and modified mysql-test-run.pl
loose-innodb_log_file_size=       5M

# Server default so no need to set it
loose-innodb_log_files_in_group=  2

# Default is OFF. Recorded the result of
# sys_vars.binlog_direct_non_transactional_updates_basic
# added this variable to opt file of some tests.
binlog-direct-non-transactional-updates

# Default is 60. Recorded the results of main.disabled_replication,
# sys_vars.slave_net_timeout_basic, i_rpl.rpl_slave_sysvar_deadlock
slave-net-timeout=120



# Variables which could not be set to the default value:
#===================================================================

# Default is 10 but need to set it to 60
# Increase default connect_timeout to avoid intermittent
# disconnects when test servers are put under load see BUG#28359
connect-timeout=            60

# When binary logging is enabled, this variable controls whether 
# stored function creators can be trusted not to create stored 
# functions that will cause unsafe events to be written to the binary log.
# Set it to 1 so that no restrictions are enforced on 
# stored function creation.
log-bin-trust-function-creators=1

# Default is OFF but necessary to set it ON for rpl tests which
# have multiple mysqld servers and other tests which have replication
# scenarios.
log-bin=mysqld-bin



#====================================================================

# MAINTAINER:
# the loose- syntax is to make sure the cnf file is also
# valid when building without the performance schema.

# Run tests with the performance schema instrumentation
loose-enable-performance-schema
# Run tests with a small number of instrumented objects
# to limit memory consumption with MTR
loose-performance-schema-accounts-size=100
loose-performance-schema-digests-size=200
loose-performance-schema-hosts-size=100
loose-performance-schema-users-size=100
loose-performance-schema-max-mutex-instances=5000
loose-performance-schema-max-rwlock-instances=5000
loose-performance-schema-max-cond-instances=1000
loose-performance-schema-max-file-instances=10000
                                                              
loose-performance-schema-max-socket-instances=1000
loose-performance-schema-max-table-instances=500
loose-performance-schema-max-table-lock-stat=500
loose-performance-schema-max-index-stat=5000
loose-performance-schema-max-table-handles=1000
loose-performance-schema-max-prepared-statements-instances=100
loose-performance-schema-max-program-instances=5000
loose-performance-schema-setup-actors-size=100
loose-performance-schema-setup-objects-size=100

loose-performance-schema-events-waits-history-size=10
loose-performance-schema-events-waits-history-long-size=10000
loose-performance-schema-events-stages-history-size=10
loose-performance-schema-events-stages-history-long-size=1000
loose-performance-schema-events-statements-history-size=10
loose-performance-schema-events-statements-history-long-size=1000
loose-performance-schema-events-transactions-history-size=10
loose-performance-schema-events-transactions-history-long-size=1000
loose-performance-schema-max-thread-instances=200
loose-performance-schema-session-connect-attrs-size=2048
loose-performance-schema-max-metadata-locks=10000

# Enable everything, for maximun code exposure during testing

loose-performance-schema-instrument='%=ON'

loose-performance-schema-consumer-events-stages-current=ON
loose-performance-schema-consumer-events-stages-history=ON
loose-performance-schema-consumer-events-stages-history-long=ON
loose-performance-schema-consumer-events-statements-current=ON
loose-performance-schema-consumer-events-statements-history=ON
loose-performance-schema-consumer-events-statements-history-long=ON
loose-performance-schema-consumer-events-transactions-current=ON
loose-performance-schema-consumer-events-transactions-history=ON
loose-performance-schema-consumer-events-transactions-history-long=ON
loose-performance-schema-consumer-events-waits-current=ON
loose-performance-schema-consumer-events-waits-history=ON
loose-performance-schema-consumer-events-waits-history-long=ON
loose-performance-schema-consumer-global-instrumentation=ON
loose-performance-schema-consumer-thread-instrumentation=ON



loose-show-compatibility-56=ON

For this WL, when the variables in default_mysqld.cnf were set to their default values, a few tests were affected.
These tests had to be modified or recorded again.
The files and tests which were affected during this worklog, and due to which variable's value change are listed below.

General changes:
===================

1. mysql-test/mysql-test-run.pl - Removed some configurations so that those variables run with default values.
2. mysql-test/include/default_my.cnf - Removed copyright notice since cnf files should not have it.
3. mysql-test/include/default_mysqld.cnf - Removed setting of variables to non-default values.
4. mysql-test/include/default_mysqld_autosize.cnf - This file was used in tests that have now been removed. So this cnf has also been removed now.


binlog-direct-non-transactional-updates:
========================================

Recorded the results of the following tests which were failing due to result content mismatch, as they had a statement printing the global value
of binlog-direct-non-transactional-updates, after the variable was set to its default value of 0 in default_mysqld.cnf:

1. sys_vars.binlog_direct_non_transactional_updates_basic
2. binlog.binlog_switch_inside_trans


Set the variable ON in the opt files of the following tests, as they were failing with binlog-direct-non-transactional-updates=OFF:

1. i_binlog.binlog_stm_gtid_myisam_tmp_table
2. i_rpl.rpl_mixed_backtick_myisam
3. i_rpl.rpl_stm_backtick_myisam
4. binlog.binlog_enforce_gtid_consistency_multiple_warnings
5. binlog.binlog_set_gtid_mode_ongoing_transaction_errors
6. binlog.binlog_stm_cache_stat
7. rpl.rpl_concurrency_error
8. rpl.rpl_mixed_mixing_engines
9. rpl.rpl_mts_submode_switch
10. rpl.rpl_stm_binlog_max_cache_size
11. rpl.rpl_stm_drop_create_temp_table
12. rpl.rpl_row_drop_create_temp_table
13. rpl.rpl_stm_mixing_engines
14. rpl.rpl_stop_slave
15. rpl.transactional_ddl_locking
16. rpl.rpl_mixed_drop_create_temp_table
17. rpl.rpl_mts_relay_log_post_crash_recovery



innodb-read-io-threads and innodb-write-io-threads:
===================================================

When these variables were changed to their default value of 4, instead of 2 in the default_mysqld.cnf, there were a few rpl tests that were failing on pb2 due to aio-max-nr exceeding its limit.
Setting the innodb io threads to default values, and running MTR on pb2 with parallel threads caused the aio-max-nr limit to be exceeded easily. This was specially the case with rpl tests with more than two mysqld servers and it caused them to fail because of the increased number of concurrent mysqld instances. Please see Bug#12677594 for more information.
One solution was to increase the limit on the pb2 machines, but that could not be done, so the variables were set to 2 for rpl tests.
They were set to 2 in the cnf file of the rpl suite and inside cnf files for individual testcases that had more than 2 mysqld servers.



The following cnf files were modified:

1. mysql-test/suite/rpl/rpl_1slave_base.cnf
2. mysql-test/suite/rpl/t/rpl_circular_for_4_hosts.cnf
3. mysql-test/suite/rpl/t/rpl_current_user.cnf
4. mysql-test/suite/rpl/t/rpl_gtid_drop_table.cnf
5. mysql-test/suite/rpl/t/rpl_gtid_empty_transaction.cnf
6. mysql-test/suite/rpl/t/rpl_gtid_failover.cnf
7. mysql-test/suite/rpl/t/rpl_gtid_heartbeat_2slave.cnf
8. mysql-test/suite/rpl/t/rpl_gtid_stress_failover.cnf
9. mysql-test/suite/rpl/t/rpl_gtid_transaction_split_across_relay_logs.cnf
10. mysql-test/suite/rpl/t/rpl_heartbeat_2slaves.cnf
11. mysql-test/suite/rpl/t/rpl_mix_missing_data_on_slave.cnf
12. mysql-test/suite/rpl/t/rpl_multi_source_basic.cnf
13. mysql-test/suite/rpl/t/rpl_multi_source_cmd_errors.cnf
14. mysql-test/suite/rpl/t/rpl_multi_source_flush_relay_logs.cnf
15. mysql-test/suite/rpl/t/rpl_multi_source_open_temp_tables_warning.cnf
16. mysql-test/suite/rpl/t/rpl_multi_source_perfschema.cnf
17. mysql-test/suite/rpl/t/rpl_multi_source_repository.cnf
18. mysql-test/suite/rpl/t/rpl_multi_source_slave_files.cnf
19. mysql-test/suite/rpl/t/rpl_multi_source_slave_skip_counter.cnf
20. mysql-test/suite/rpl/t/rpl_multi_source_slave_start_stop.cnf
21. mysql-test/suite/rpl/t/rpl_row_img.cnf
22. mysql-test/suite/rpl/t/rpl_semi_sync_ack_thread.cnf
23. mysql-test/suite/rpl/t/rpl_semi_sync_install_at_start_server.cnf
24. mysql-test/suite/rpl/t/rpl_semi_sync_wait_slave_count.cnf
25. mysql-test/suite/rpl/t/rpl_server_uuid.cnf
26. mysql-test/suite/rpl/t/rpl_show_slave_hosts.cnf
27. mysql-test/suite/rpl/t/rpl_test_framework.cnf
28. mysql-test/suite/rpl/t/rpl_wait_for_executed_gtid_set.cnf


The results of the following tests were recorded because they failed with result content mismatch caused by the statements printing the values of these variables:

1. sys_vars.innodb_read_io_threads_basic
2. sys_vars.innodb_write_io_threads_basic


max-heap-table-size
====================

When max-heap-table size value was changed to its default value of 16M from 1M in the default_mysqld.cnf, there were a few failing tests.
The results of these tests that were failing were recorded again.
The failures were due to result content mismatches in the optimizer trace, where the row_limit_estimate is printed.
This row_limit_estimate is expected to increase since the max-heap-table-size value sets the maximum size to which user-created MEMORY tables are permitted to grow and is used to calculate the maximum number of rows of the table.
Also, mysql-test/suite/opt_trace/include/security.inc was modified to
remove the white space errors in the result files.

The following tests results were recorded:

1. i_main.subquery_mat_cost_based
2. i_opt_trace.bugs_no_prot
3. i_opt_trace.bugs_ps_prot
4. main.show_check
5. opt_trace.bugs_no_prot_all
6. opt_trace.bugs_ps_prot_all
7. opt_trace.charset
8. opt_trace.filesort_pq
9. opt_trace.general2_no_prot
10. opt_trace.general2_ps_prot
11. opt_trace.general_no_prot_all
12. opt_trace.general_no_prot_none
13. opt_trace.general_ps_prot_all
14. opt_trace.general_ps_prot_none
15. opt_trace.range_no_prot
16. opt_trace.range_ps_prot
17. opt_trace.security_no_prot
18. opt_trace.security_ps_prot
19. opt_trace.temp_table
20. sys_vars.max_heap_table_size_basic


This test was modified since it was testing max-heap-table-size with replication.
In a replication environment, the max-heap-table-size was set to its minimum value on the slave. A table was created on the master and then was
replicated on the slave. However this table still had the default value of the max-heap-table-size on the slave. For this value to be changed for
an existing table, the table has to be truncated or altered. Hence the test was modified to add a truncate table statement and recorded again:

1. rpl.rpl_spec_variables


slave-net-timeout
===================

When slave-net-timeout was changed to its default value of 60 from 120 in the default_mysqld.cnf, a few tests failed with result content mismatches.
Recorded the results of the following tests which were printing slave_heartbeat_period, and the variable slave_net_timeout
because the values were expected to change after the MTR default of the variable was changed.

1. i_rpl.rpl_slave_sysvar_deadlock
2. main.disabled_replication
3. sys_vars.slave_net_timeout_basic

One test failed with a result content mismatch due to an extra warning
being printed: "The requested value for the heartbeat period exceeds the value of `slave_net_timeout' seconds". This was recorded again because it was
expected.

1. rpl.rpl_heartbeat_basic


key-buffer-size
================

One test failed with result content mismatch when MTR was run with the default value ( 8M ) of this variable since it was printing the key_buffer_size.
Hence it was recorded again.

1. main.partition_key_cache

innodb-buffer-pool-size
=========================

A few tests failed with result content mismatch when MTR was run with the default value ( 128M ) of this variable since the tests were printing the value
of innodb_buffer_pool_pages_total which is dependent on the innodb_buffer_pool_size the innodb_page_size and hence the tests were modified.

1. innodb.innodb
2. innodb_zip.16k
3. innodb_zip.8k
4. innodb_zip.4k

innodb-lru-scan-depth
=======================

One test failed with result content mismatch when MTR was run with the default value ( 1024 ) of this variable, since the test was
printing the value of this variable. Hence the result was recorded again.

1. sys_vars.innodb_lru_scan_depth_basic

innodb-log-file-size
=========================

One test failed with result content mismatch when MTR was run with the default value ( 48M ) of this variable, since the test was printing the value of log_file_size. Hence the result was recorded. Also,
mysql-test/include/ib_logfile_size_check.inc was modified to remove the white space errors in the result file of this test. 

1. main.innodb_log_file_size_functionality