| 1 |
################################################################################ |
| 2 |
# This test proves that SET variable option works in Read only mode. |
| 3 |
# |
| 4 |
# Test: |
| 5 |
# 0. The test requires two server. |
| 6 |
# 1. Start GR with server1 as Primary and server2 as secondary. |
| 7 |
# 2. Change value of variable binlog_checksum on server2. |
| 8 |
# 3. Stop GR on server1. |
| 9 |
# 4. Assert server is in Read only mode on server1. |
| 10 |
# 5. Test changing value of variables. |
| 11 |
# 6. Cleanup. |
| 12 |
# |
| 13 |
################################################################################ |
| 14 |
|
| 15 |
--source include/have_group_replication_plugin.inc |
| 16 |
--let $rpl_server_count= 2 |
| 17 |
--let $rpl_skip_group_replication_start= 1 |
| 18 |
--let $rpl_group_replication_single_primary_mode=1 |
| 19 |
--source include/group_replication.inc |
| 20 |
|
| 21 |
--echo |
| 22 |
--echo # 1. Start GR with server1 as Primary and server2 as secondary. |
| 23 |
--source include/start_and_bootstrap_group_replication.inc |
| 24 |
|
| 25 |
--echo |
| 26 |
--let $rpl_connection_name= server2 |
| 27 |
--source include/rpl_connection.inc |
| 28 |
--source include/start_group_replication.inc |
| 29 |
|
| 30 |
--echo |
| 31 |
--echo # 2. Change value of variable binlog_checksum on server2. |
| 32 |
SET @binlog_checksum_save= @@GLOBAL.binlog_checksum; |
| 33 |
SET GLOBAL binlog_checksum=none; |
| 34 |
|
| 35 |
--echo |
| 36 |
--echo # 3. Stop GR on server1. |
| 37 |
--let $skip_reset_read_mode=1 |
| 38 |
--source include/stop_group_replication.inc |
| 39 |
|
| 40 |
--let $wait_condition= SELECT count(*) = 1 FROM performance_schema.replication_group_members WHERE MEMBER_STATE = 'OFFLINE'; |
| 41 |
--source include/wait_condition.inc |
| 42 |
|
| 43 |
--echo |
| 44 |
--echo # 4. Assert server is in Read only mode on server1. |
| 45 |
--let $assert_cond= "[SELECT @@GLOBAL.super_read_only]" = 1 |
| 46 |
--let $assert_text= Assert that server is running in super read-only. |
| 47 |
--source include/assert.inc |
| 48 |
|
| 49 |
--let $assert_cond= "[SELECT @@GLOBAL.read_only]" = 1 |
| 50 |
--let $assert_text= Assert that server is running in read-only. |
| 51 |
--source include/assert.inc |
| 52 |
|
| 53 |
--echo |
| 54 |
--echo # 5. Test changing value of variables. |
| 55 |
--disable_query_log |
| 56 |
## Save values |
| 57 |
SET @saved_gtid_mode = @@GLOBAL.gtid_mode; |
| 58 |
SET @saved_gtid_executed_compression_period = @@GLOBAL.gtid_executed_compression_period; |
| 59 |
SET @saved_binlog_checksum = @@GLOBAL.binlog_checksum; |
| 60 |
SET @saved_binlog_cache_size = @@GLOBAL.binlog_cache_size; |
| 61 |
SET @saved_binlog_direct_non_transactional_updates = @@GLOBAL.binlog_direct_non_transactional_updates; |
| 62 |
SET @saved_binlog_error_action = @@GLOBAL.binlog_error_action; |
| 63 |
SET @saved_binlog_format = @@GLOBAL.binlog_format; |
| 64 |
SET @saved_binlog_group_commit_sync_delay = @@GLOBAL.binlog_group_commit_sync_delay; |
| 65 |
SET @saved_binlog_group_commit_sync_no_delay_count = @@GLOBAL.binlog_group_commit_sync_no_delay_count; |
| 66 |
SET @saved_binlog_order_commits = @@GLOBAL.binlog_order_commits; |
| 67 |
SET @saved_binlog_rows_query_log_events = @@GLOBAL.binlog_rows_query_log_events; |
| 68 |
SET @saved_binlog_stmt_cache_size = @@GLOBAL.binlog_stmt_cache_size; |
| 69 |
|
| 70 |
SET @saved_gr_allow_local_lower_version_join = @@GLOBAL.group_replication_allow_local_lower_version_join; |
| 71 |
SET @saved_gr_auto_increment_increment = @@GLOBAL.group_replication_auto_increment_increment; |
| 72 |
SET @saved_gr_bootstrap_group = @@GLOBAL.group_replication_bootstrap_group; |
| 73 |
SET @saved_gr_components_stop_timeout = @@GLOBAL.group_replication_components_stop_timeout; |
| 74 |
SET @saved_gr_compression_threshold = @@GLOBAL.group_replication_compression_threshold; |
| 75 |
SET @saved_gr_force_members = @@GLOBAL.group_replication_force_members; |
| 76 |
SET @saved_gr_group_name = @@GLOBAL.group_replication_group_name; |
| 77 |
SET @saved_gr_gtid_assignment_block_size = @@GLOBAL.group_replication_gtid_assignment_block_size; |
| 78 |
SET @saved_gr_ip_allowlist = @@GLOBAL.group_replication_ip_allowlist; |
| 79 |
SET @saved_gr_local_address = @@GLOBAL.group_replication_local_address; |
| 80 |
SET @saved_gr_group_seeds = @@GLOBAL.group_replication_group_seeds; |
| 81 |
SET @saved_gr_poll_spin_loops = @@GLOBAL.group_replication_poll_spin_loops; |
| 82 |
SET @saved_gr_recovery_complete_at = @@GLOBAL.group_replication_recovery_complete_at; |
| 83 |
SET @saved_gr_recovery_reconnect_interval = @@GLOBAL.group_replication_recovery_reconnect_interval; |
| 84 |
SET @saved_gr_recovery_retry_count = @@GLOBAL.group_replication_recovery_retry_count; |
| 85 |
SET @saved_gr_recovery_ssl_ca = @@GLOBAL.group_replication_recovery_ssl_ca; |
| 86 |
SET @saved_gr_recovery_ssl_capath = @@GLOBAL.group_replication_recovery_ssl_capath; |
| 87 |
SET @saved_gr_recovery_ssl_cert = @@GLOBAL.group_replication_recovery_ssl_cert; |
| 88 |
SET @saved_gr_recovery_ssl_cipher = @@GLOBAL.group_replication_recovery_ssl_cipher; |
| 89 |
SET @saved_gr_recovery_ssl_crl = @@GLOBAL.group_replication_recovery_ssl_crl; |
| 90 |
SET @saved_gr_recovery_ssl_crlpath = @@GLOBAL.group_replication_recovery_ssl_crlpath; |
| 91 |
SET @saved_gr_recovery_ssl_key = @@GLOBAL.group_replication_recovery_ssl_key; |
| 92 |
SET @saved_gr_recovery_ssl_verify_server_cert = @@GLOBAL.group_replication_recovery_ssl_verify_server_cert; |
| 93 |
SET @saved_gr_recovery_use_ssl = @@GLOBAL.group_replication_recovery_use_ssl; |
| 94 |
SET @saved_gr_single_primary_mode = @@GLOBAL.group_replication_single_primary_mode; |
| 95 |
SET @saved_gr_ssl_mode = @@GLOBAL.group_replication_ssl_mode; |
| 96 |
SET @saved_gr_start_on_boot = @@GLOBAL.group_replication_start_on_boot; |
| 97 |
SET @saved_gr_transaction_size_limit = @@GLOBAL.group_replication_transaction_size_limit; |
| 98 |
SET @saved_gr_unreachable_majority_timeout = @@GLOBAL.group_replication_unreachable_majority_timeout; |
| 99 |
SET @saved_gr_member_weight = @@GLOBAL.group_replication_member_weight; |
| 100 |
|
| 101 |
## SET values |
| 102 |
SET GLOBAL gtid_mode= ON_PERMISSIVE; |
| 103 |
SET GLOBAL gtid_executed_compression_period= 2000; |
| 104 |
|
| 105 |
SET GLOBAL binlog_checksum=CRC32; |
| 106 |
SET GLOBAL binlog_cache_size=4096; |
| 107 |
SET GLOBAL binlog_direct_non_transactional_updates=ON; |
| 108 |
SET GLOBAL binlog_error_action=IGNORE_ERROR; |
| 109 |
SET GLOBAL binlog_format=MIXED; |
| 110 |
SET GLOBAL binlog_group_commit_sync_delay=60; |
| 111 |
SET GLOBAL binlog_group_commit_sync_no_delay_count=60; |
| 112 |
SET GLOBAL binlog_order_commits=true; |
| 113 |
SET GLOBAL binlog_rows_query_log_events= ON; |
| 114 |
SET GLOBAL binlog_stmt_cache_size= 4096; |
| 115 |
|
| 116 |
SET GLOBAL group_replication_allow_local_lower_version_join= ON; |
| 117 |
SET GLOBAL group_replication_auto_increment_increment= 5; |
| 118 |
SET GLOBAL group_replication_bootstrap_group= ON; |
| 119 |
SET GLOBAL group_replication_components_stop_timeout= 10; |
| 120 |
SET GLOBAL group_replication_compression_threshold= 10; |
| 121 |
SET GLOBAL group_replication_force_members= ""; |
| 122 |
SET GLOBAL group_replication_group_name= "aaaaaaaa-cccc-bbbb-cccc-aaaaaaaaaaaa"; |
| 123 |
SET GLOBAL group_replication_gtid_assignment_block_size= 2; |
| 124 |
SET GLOBAL group_replication_ip_allowlist= "AUTOMATIC"; |
| 125 |
SET GLOBAL group_replication_local_address= "$saved_gr_local_address"; |
| 126 |
SET GLOBAL group_replication_group_seeds= "$saved_gr_group_seeds"; |
| 127 |
SET GLOBAL group_replication_poll_spin_loops= 10; |
| 128 |
SET GLOBAL group_replication_recovery_complete_at= "TRANSACTIONS_CERTIFIED"; |
| 129 |
SET GLOBAL group_replication_recovery_reconnect_interval= 20; |
| 130 |
SET GLOBAL group_replication_recovery_retry_count= 5; |
| 131 |
SET GLOBAL group_replication_recovery_ssl_ca= '$MYSQL_TEST_DIR/std_data/cacert.pem'; |
| 132 |
SET GLOBAL group_replication_recovery_ssl_capath= ''; |
| 133 |
SET GLOBAL group_replication_recovery_ssl_cert= '$MYSQL_TEST_DIR/std_data/client-cert.pem'; |
| 134 |
SET GLOBAL group_replication_recovery_ssl_cipher= 'ECDHE-RSA-AES128-GCM-SHA256'; |
| 135 |
SET GLOBAL group_replication_recovery_ssl_crl= 'crl-client-revoked.crl'; |
| 136 |
SET GLOBAL group_replication_recovery_ssl_crlpath= ''; |
| 137 |
SET GLOBAL group_replication_recovery_ssl_key= '$MYSQL_TEST_DIR/std_data/client-key.pem'; |
| 138 |
SET GLOBAl group_replication_recovery_ssl_verify_server_cert= ON; |
| 139 |
SET GLOBAl group_replication_single_primary_mode= OFF; |
| 140 |
SET GLOBAL group_replication_recovery_use_ssl= ON; |
| 141 |
SET GLOBAL group_replication_ssl_mode= "REQUIRED"; |
| 142 |
SET GLOBAL group_replication_start_on_boot= ON; |
| 143 |
SET GLOBAL group_replication_transaction_size_limit= 100000; |
| 144 |
SET GLOBAL group_replication_unreachable_majority_timeout= 100000; |
| 145 |
SET GLOBAL group_replication_member_weight= 70; |
| 146 |
|
| 147 |
--echo |
| 148 |
--echo # 6. Cleanup. |
| 149 |
## Restore values for server1 |
| 150 |
SET @@GLOBAL.gtid_mode = @saved_gtid_mode; |
| 151 |
SET @@GLOBAL.gtid_executed_compression_period = @saved_gtid_executed_compression_period; |
| 152 |
SET @@GLOBAL.binlog_checksum = @saved_binlog_checksum; |
| 153 |
SET @@GLOBAL.binlog_cache_size = @saved_binlog_cache_size; |
| 154 |
SET @@GLOBAL.binlog_direct_non_transactional_updates = @saved_binlog_direct_non_transactional_updates; |
| 155 |
SET @@GLOBAL.binlog_error_action = @saved_binlog_error_action; |
| 156 |
SET @@GLOBAL.binlog_format = @saved_binlog_format; |
| 157 |
SET @@GLOBAL.binlog_group_commit_sync_delay = @saved_binlog_group_commit_sync_delay; |
| 158 |
SET @@GLOBAL.binlog_group_commit_sync_no_delay_count = @saved_binlog_group_commit_sync_no_delay_count; |
| 159 |
SET @@GLOBAL.binlog_order_commits = @saved_binlog_order_commits; |
| 160 |
SET @@GLOBAL.binlog_rows_query_log_events = @saved_binlog_rows_query_log_events; |
| 161 |
SET @@GLOBAL.binlog_stmt_cache_size = @saved_binlog_stmt_cache_size; |
| 162 |
|
| 163 |
SET @@GLOBAL.group_replication_allow_local_lower_version_join= @saved_gr_allow_local_lower_version_join; |
| 164 |
SET @@GLOBAL.group_replication_auto_increment_increment= @saved_gr_auto_increment_increment; |
| 165 |
SET @@GLOBAL.group_replication_bootstrap_group= @saved_gr_bootstrap_group; |
| 166 |
SET @@GLOBAL.group_replication_components_stop_timeout= @saved_gr_components_stop_timeout; |
| 167 |
SET @@GLOBAL.group_replication_compression_threshold= @saved_gr_compression_threshold; |
| 168 |
SET @@GLOBAL.group_replication_single_primary_mode= @saved_gr_single_primary_mode; |
| 169 |
SET @@GLOBAL.group_replication_force_members= @saved_gr_force_members; |
| 170 |
SET @@GLOBAL.group_replication_group_name= @saved_gr_group_name; |
| 171 |
SET @@GLOBAL.group_replication_gtid_assignment_block_size= @saved_gr_gtid_assignment_block_size; |
| 172 |
SET @@GLOBAL.group_replication_ip_allowlist= @saved_gr_ip_allowlist; |
| 173 |
SET @@GLOBAL.group_replication_local_address= @saved_gr_local_address; |
| 174 |
SET @@GLOBAL.group_replication_group_seeds= @saved_gr_group_seeds; |
| 175 |
SET @@GLOBAL.group_replication_poll_spin_loops= @saved_gr_poll_spin_loops; |
| 176 |
SET @@GLOBAL.group_replication_recovery_complete_at= @saved_gr_recovery_complete_at; |
| 177 |
SET @@GLOBAL.group_replication_recovery_reconnect_interval= @saved_gr_recovery_reconnect_interval; |
| 178 |
SET @@GLOBAL.group_replication_recovery_retry_count= @saved_gr_recovery_retry_count; |
| 179 |
SET @@GLOBAL.group_replication_recovery_ssl_ca= @saved_gr_recovery_ssl_ca; |
| 180 |
SET @@GLOBAL.group_replication_recovery_ssl_capath= @saved_gr_recovery_ssl_capath; |
| 181 |
SET @@GLOBAL.group_replication_recovery_ssl_cert= @saved_gr_recovery_ssl_cert; |
| 182 |
SET @@GLOBAL.group_replication_recovery_ssl_cipher= @saved_gr_recovery_ssl_cipher; |
| 183 |
SET @@GLOBAL.group_replication_recovery_ssl_crl= @saved_gr_recovery_ssl_crl; |
| 184 |
SET @@GLOBAL.group_replication_recovery_ssl_crlpath= @saved_gr_recovery_ssl_crlpath; |
| 185 |
SET @@GLOBAL.group_replication_recovery_ssl_key= @saved_gr_recovery_ssl_key; |
| 186 |
SET @@GLOBAL.group_replication_recovery_ssl_verify_server_cert= @saved_gr_recovery_ssl_verify_server_cert; |
| 187 |
SET @@GLOBAL.group_replication_recovery_use_ssl= @saved_gr_recovery_use_ssl; |
| 188 |
SET @@GLOBAL.group_replication_ssl_mode= @saved_gr_ssl_mode; |
| 189 |
SET @@GLOBAL.group_replication_start_on_boot= @saved_gr_start_on_boot; |
| 190 |
SET @@GLOBAL.group_replication_transaction_size_limit= @saved_gr_transaction_size_limit; |
| 191 |
SET @@GLOBAL.group_replication_unreachable_majority_timeout= @saved_gr_unreachable_majority_timeout; |
| 192 |
SET @@GLOBAL.group_replication_member_weight= @saved_gr_member_weight; |
| 193 |
|
| 194 |
--enable_query_log |
| 195 |
|
| 196 |
## Restore values for server2 |
| 197 |
--let $rpl_connection_name= server2 |
| 198 |
--source include/rpl_connection.inc |
| 199 |
SET @@GLOBAL.binlog_checksum= @binlog_checksum_save; |
| 200 |
--source include/group_replication_end.inc |