################################################################################ # The aim of this testcase is to test persisted behavior of replication # variables with SET PERSIST_ONLY and RESET PERSIST clauses. # # Test: # 0. Check that there are no persisted variable settings due to improper # cleanup by other testcases. # 1. Test SET PERSIST_ONLY. Verify persisted variables. # 2. Restart server, it must preserve the persisted variable settings. # Verify persisted configuration. # 3. Test RESET PERSIST. Verify persisted variable settings are removed. # 4. Clean up. ################################################################################ --source include/have_log_bin.inc --source include/have_semisync_plugin.inc # Test is agnostic to binlog_format. --source include/have_binlog_format_row.inc --echo ############################################################ --echo # 0. Check that there are no persisted variable settings. --let $assert_text = Expect 0 persisted variables. --let $assert_cond = [SELECT COUNT(*) as count FROM performance_schema.persisted_variables, count, 1] = 0 --source include/assert.inc --echo --echo ############################################################ --echo # 1. Initialization. Test SET PERSIST_ONLY. Verify persisted --echo # variables. --let $MYSQLD_DATADIR= `select @@datadir;` CALL mtr.add_suppression("Unsafe statement written to the binary log"); --echo # Install semi-sync related plugins --replace_result $SEMISYNC_SOURCE_PLUGIN SEMISYNC_SOURCE_PLUGIN eval INSTALL PLUGIN rpl_semi_sync_source SONAME '$SEMISYNC_SOURCE_PLUGIN'; --replace_result $SEMISYNC_REPLICA_PLUGIN SEMISYNC_REPLICA_PLUGIN eval INSTALL PLUGIN rpl_semi_sync_replica SONAME '$SEMISYNC_REPLICA_PLUGIN'; CREATE TABLE rplvars (id INT PRIMARY KEY AUTO_INCREMENT, varname VARCHAR(64), varvalue VARCHAR(256)); # Disable warning to avoid printing of ER_BINLOG_UNSAFE_STATEMENT --disable_warnings INSERT INTO rplvars (varname, varvalue) SELECT * FROM performance_schema.global_variables WHERE (VARIABLE_NAME LIKE '%binlog%' OR VARIABLE_NAME LIKE '%gtid%' OR VARIABLE_NAME LIKE '%log_bin%' OR VARIABLE_NAME LIKE '%master%' OR VARIABLE_NAME LIKE '%relay%' OR VARIABLE_NAME LIKE '%replica%' OR VARIABLE_NAME LIKE '%rpl%' OR VARIABLE_NAME LIKE '%semi_sync%' OR VARIABLE_NAME LIKE '%slave%' OR VARIABLE_NAME LIKE '%source%') AND (VARIABLE_NAME NOT IN ('innodb_api_enable_binlog', 'innodb_master_thread_disabled_debug', 'innodb_replication_delay')) ORDER BY VARIABLE_NAME; --enable_warnings --let $countvars= `SELECT COUNT(*) FROM rplvars;` CREATE TABLE non_persisted (name VARCHAR(100) PRIMARY KEY); INSERT INTO non_persisted VALUES ('gtid_executed'), ('gtid_owned'), ('gtid_purged'), ('log_bin'), ('log_bin_basename'), ('log_bin_index'), ('relay_log'), ('relay_log_basename'), ('relay_log_index'), ('relay_log_info_file'), ('replica_load_tmpdir'), ('slave_load_tmpdir'), ('log_bin_use_v1_row_events'); CREATE TABLE aliases ( name VARCHAR(100) PRIMARY KEY ); INSERT INTO aliases(name) VALUES ('init_slave'), ('rpl_stop_slave_timeout'), ('log_slow_slave_statements'), ('slave_max_allowed_packet'), ('slave_compressed_protocol'), ('slave_exec_mode'), ('slave_type_conversions'), ('slave_sql_verify_checksum'), ('slave_parallel_type'), ('slave_preserve_commit_order'), ('master_verify_checksum'), ('log_slave_updates'), ('slave_allow_batching'), ('slave_load_tmpdir'), ('slave_net_timeout'), ('sql_slave_skip_counter'), ('slave_skip_errors'), ('slave_checkpoint_period'), ('slave_checkpoint_group'), ('sync_master_info'), ('slave_transaction_retries'), ('slave_parallel_workers'), ('slave_pending_jobs_size_max'), ('pseudo_slave_mode'), ('skip_slave_start'); # If this count differs, it means a variable has been added or removed. # In that case, this testcase needs to be updated accordingly. --echo --let $expected = 114 --let $assert_text = Expect $expected variables in the table. --let $assert_cond = [SELECT COUNT(*) as count FROM rplvars, count, 1] = $expected --source include/assert.inc --echo --echo # Test SET PERSIST_ONLY --let $varid=1 while ( $varid <= $countvars ) { --let $varname= `SELECT varname FROM rplvars WHERE id=$varid` # Following variables are non persistent read only variables. if (`SELECT COUNT(*) FROM non_persisted WHERE name = '$varname'`) { --error ER_INCORRECT_GLOBAL_LOCAL_VAR } --eval SET PERSIST_ONLY $varname = @@GLOBAL.$varname # TODO: Remove/update this once Bug#27322592 is FIXED. if (`SELECT '$varname' IN ('binlog_direct_non_transactional_updates', 'binlog_order_commits', 'binlog_rows_query_log_events', 'log_bin_trust_function_creators', 'log_slow_replica_statements', 'log_statements_unsafe_for_binlog', 'source_verify_checksum', 'replica_allow_batching', 'replica_compressed_protocol', 'replica_preserve_commit_order', 'replica_sql_verify_checksum', 'relay_log_purge', 'rpl_semi_sync_source_enabled', 'rpl_semi_sync_source_wait_no_replica', 'rpl_semi_sync_replica_enabled', 'binlog_gtid_simple_recovery', 'log_replica_updates', 'relay_log_recovery', 'binlog_rotate_encryption_master_key_at_startup', 'binlog_transaction_compression')`) { --disable_query_log --eval SELECT varvalue INTO @varvalue FROM rplvars WHERE id=$varid --eval SET PERSIST_ONLY $varname = @varvalue --enable_query_log } --inc $varid } --echo --let $expected = 101 --let $assert_text = Expect $expected persisted variables in persisted_variables table. --let $assert_cond = [SELECT COUNT(*) as count FROM performance_schema.persisted_variables, count, 1] = $expected --source include/assert.inc --echo --echo ############################################################ --echo # 2. Restart server, it must preserve the persisted variable --echo # settings. Verify persisted configuration. --source include/restart_mysqld.inc --source include/wait_until_connected_again.inc --echo --let $assert_text = Expect $expected persisted variables in persisted_variables table. --let $assert_cond = [SELECT COUNT(*) as count FROM performance_schema.persisted_variables, count, 1] = $expected --source include/assert.inc # Due to the following bug, variable_source is wrong for # skip_slave_start/skip_replica_start: # BUG#32640588: SOURCE FOR SKIP_SLAVE_START DISPLAYED AS COMPILED WHEN IT IS PERSISTED --let $assert_text = Expect $expected persisted variables shown as PERSISTED in variables_info table. --let $assert_cond = [SELECT COUNT(*) as count FROM performance_schema.variables_info WHERE variable_source="PERSISTED" OR variable_name LIKE "skip_%_start", count, 1] = $expected --source include/assert.inc --let $assert_text = Expect $expected persisted variables with matching persisted and global values. --let $assert_cond = [SELECT COUNT(*) as count FROM performance_schema.variables_info vi JOIN performance_schema.persisted_variables pv JOIN performance_schema.global_variables gv ON vi.variable_name=pv.variable_name AND vi.variable_name=gv.variable_name AND pv.variable_value=gv.variable_value WHERE vi.variable_source="PERSISTED" OR vi.variable_name LIKE "skip_%_start", count, 1] = $expected --source include/assert.inc --echo --echo ############################################################ --echo # 3. Test RESET PERSIST. Verify persisted variable settings --echo # are removed. --let $varid=1 while ( $varid <= $countvars ) { --let $varname= `SELECT varname FROM rplvars WHERE id=$varid` if (!`SELECT COUNT(*) FROM aliases WHERE name = '$varname'`) { if (`SELECT COUNT(*) FROM non_persisted WHERE name = '$varname'`) { --error ER_VAR_DOES_NOT_EXIST } --eval RESET PERSIST $varname } --inc $varid } --echo --let $assert_text = Expect 0 persisted variables. --let $assert_cond = [SELECT COUNT(*) as count FROM performance_schema.persisted_variables, count, 1] = 0 --source include/assert.inc --echo --echo ############################################################ --echo # 4. Clean up. --let $countvars= --let $varid= --let $varname= --let $varvalue= SET @varvalue=NULL; --remove_file $MYSQLD_DATADIR/mysqld-auto.cnf UNINSTALL PLUGIN rpl_semi_sync_source; UNINSTALL PLUGIN rpl_semi_sync_replica; DROP TABLE rplvars; DROP TABLE aliases; DROP TABLE non_persisted;