1--source include/not_embedded.inc 2# Save the initial number of concurrent sessions 3--source include/count_sessions.inc 4call mtr.add_suppression('Corresponding entry in binary log used default authentication.*'); 5 6--source include/master-slave.inc 7--connection master 8SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password'; 9 10--echo # 11--echo # WL#8540: Support IF [NOT] EXISTS clause in CREATE/DROP USER 12--echo # 13CREATE USER u1@localhost; 14CREATE USER IF NOT EXISTS u1@localhost, u2@localhost; 15ALTER USER IF EXISTS u1@localhost ACCOUNT LOCK; 16DROP USER u1@localhost; 17DROP USER IF EXISTS u2@localhost; 18 19--echo # 20--echo # Bug #21807286: "CREATE USER IF NOT EXISTS" REPORTS AN ERROR 21--echo # 22CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'haha'; 23CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'haha2'; 24ALTER USER IF EXISTS b21807286@localhost IDENTIFIED BY 'haha3'; 25ALTER USER IF EXISTS b21807286_not_exists@localhost IDENTIFIED BY 'haha4'; 26DROP USER b21807286@localhost; 27 28--source include/sync_slave_sql_with_master.inc 29--echo [On Slave] 30--source include/show_binlog_events.inc 31 32--echo # 33--echo # Bug #25813089: "CREATE USER IF NOT EXISTS SHOULD ALWAYS 34--echo # BE WRITTEN TO THE BINLOGS" 35--echo # 36 37# Scenario 1: Create/Alter user executed on a single user 38 39--echo # ------ 40--echo "Scenario 1: CREATE/ALTER USER IF[NOT]EXISTS FOR A SINGLE USER" 41--echo # ------ 42--connection master 43# Save master position 44--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1) 45--let $binlog_start= $saved_master_pos 46 47CREATE USER u1@localhost IDENTIFIED BY 'pass1'; 48CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass2'; 49ALTER USER u1@localhost IDENTIFIED BY 'pass3'; 50ALTER USER IF EXISTS u1@localhost IDENTIFIED BY 'pass4'; 51ALTER USER IF EXISTS u1_not_exists@localhost IDENTIFIED BY 'pass5'; 52DROP USER u1@localhost; 53 54--echo [On Master] 55--source include/show_binlog_events.inc 56 57--source include/sync_slave_sql_with_master.inc 58--echo [On Slave] 59--source include/show_binlog_events.inc 60 61--echo "Creating a user only on slave" 62CREATE USER IF NOT EXISTS slave_user@localhost IDENTIFIED BY 'slave_pass'; 63 64# Scenario 2: Create/Alter user executed on multiple users 65# irrespective of whether they are present on master or not 66 67--echo # ------ 68--echo "Scenario 2: CREATE/ALTER USER IF[NOT]EXISTS FOR MULTIPLE USERS" 69--echo # ------ 70--connection master 71# Save master position 72--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1) 73--let $binlog_start= $saved_master_pos 74CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass1', 75 u2@localhost IDENTIFIED BY 'pass2'; 76 77CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass1', 78 u2@localhost IDENTIFIED BY 'pass2', 79 u3@localhost IDENTIFIED BY 'pass3'; 80 81ALTER USER IF EXISTS u1@localhost IDENTIFIED BY 'pass4', 82 u2@localhost IDENTIFIED BY 'pass5', 83 u3@localhost IDENTIFIED BY 'pass6', 84 u4_not_exists@localhost IDENTIFIED BY 'pass7', 85 slave_user@localhost IDENTIFIED BY 'pass8'; 86DROP USER IF EXISTS u1@localhost, u2@localhost, u3@localhost, slave_user@localhost; 87 88--echo [On Master] 89--source include/show_binlog_events.inc 90 91--source include/sync_slave_sql_with_master.inc 92--echo [On Slave] 93--source include/show_binlog_events.inc 94--source include/stop_slave_io.inc 95 96# Scenario 3: Make sure that the default authentication plugin is not used 97# when the plugin is specified explicitly while creating or altering users. 98 99--echo # ------ 100--echo "Scenario 3: CREATE/ALTER USER IF[NOT]EXISTS WITH" 101--echo "AUTHENTICATION PLUGIN PROVIDED EXPLICITLY" 102--echo # ------ 103--connection master 104--let $rpl_server_number= 1 105--let $rpl_server_parameters= --default_authentication_plugin=sha256_password 106--source include/rpl_restart_server.inc 107 108--connection slave 109--source include/start_slave_io.inc 110 111--connection master 112--disable_warnings 113--source include/rpl_reset.inc 114--enable_warnings 115 116# Save master position 117--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1) 118--let $binlog_start= $saved_master_pos 119 120SHOW VARIABLES LIKE 'default_authentication_plugin'; 121CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH mysql_native_password by 'pass_native'; 122CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH mysql_native_password by 'pass_native2'; 123ALTER USER IF EXISTS u1@localhost IDENTIFIED WITH mysql_native_password BY 'pass_native3'; 124ALTER USER IF EXISTS u2_not_exists@localhost IDENTIFIED WITH mysql_native_password BY 'pass_native4'; 125SELECT user,authentication_string FROM mysql.user WHERE user='u1'; 126DROP USER u1@localhost; 127 128--echo [On Master] 129--source include/show_binlog_events.inc 130 131--source include/sync_slave_sql_with_master.inc 132--echo [On Slave] 133--source include/show_binlog_events.inc 134 135################################################################################ 136# Bug#26680035 CREATE/ALTER USER ERRORS OUT BUT GETS RECORDED IN THE BINARY LOG 137# 138# Problem: create/alter user when used with invalid plugin errors out, 139# but still gets recorded in binary log. 140# 141# Steps to reproduce: 142# 1) Scenario 1: 143# 1.1) Create user with invalid plugin. 144# 1.2) Create user having 'if not exists' clause with invalid plugin. 145# 2) Scenario 2: 146# 2.1) Alter user with invalid plugin. 147# 2.2) Alter user having 'if exists' clause with invalid plugin. 148# 3) Create/Alter user having multiple users with invalid plugin. 149# 4) Cleanup 150################################################################################ 151--echo # 152--echo # Bug26680035: CREATE/ALTER USER ERRORS OUT BUT GETS RECORDED IN 153--echo # THE BINARY LOG 154--echo # 155call mtr.add_suppression("Following users were specified in CREATE USER IF NOT EXISTS but they already exist. *"); 156 157# Scenario 1: Create user with invalid plugin 158--echo # ------ 159--echo "Scenario 1: CREATE USER WITH INVALID PLUGIN" 160--echo # ------ 161 162--let $master_pos_before_create_user= query_get_value(SHOW MASTER STATUS,Position,1) 163--let $binlog_start= $master_pos_before_create_user 164--echo # 165--echo # Step 1.1) Create user with invalid plugin. 166--echo # 167--error ER_PLUGIN_IS_NOT_LOADED 168CREATE USER u1@localhost IDENTIFIED WITH 'plugin1'; 169 170--echo # 171--echo # Step 1.2) Create user having 'if not exists' clause with invalid 172--echo # plugin. 173--echo # 174--error ER_PLUGIN_IS_NOT_LOADED 175CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH 'plugin2'; 176 177# Assert that the above statements are not written to binlog. 178--let $master_pos_after_create_user= query_get_value(SHOW MASTER STATUS,Position,1) 179--let $assert_text= assert that the above events are not written to binlog 180--let $assert_cond= $master_pos_before_create_user = $master_pos_after_create_user 181--source include/assert.inc 182 183 184# Scenario 2: Alter user with invalid plugin 185--echo # ------ 186--echo "Scenario 2: ALTER USER WITH INVALID PLUGIN" 187--echo # ------ 188 189CREATE USER u2@localhost; 190--let $master_pos_before_alter_user= query_get_value(SHOW MASTER STATUS,Position,1) 191--echo # 192--echo # Step 2.1) Alter user with invalid plugin. 193--echo # 194--error ER_PLUGIN_IS_NOT_LOADED 195ALTER USER u2@localhost IDENTIFIED WITH 'plugin3'; 196 197--echo # 198--echo # Step 2.2) Alter user having 'if exists' clause with invalid plugin. 199--echo # 200--error ER_PLUGIN_IS_NOT_LOADED 201ALTER USER IF EXISTS u2@localhost IDENTIFIED WITH 'plugin4'; 202 203# Assert that the above statements are not written to binlog. 204--let $master_pos_after_alter_user= query_get_value(SHOW MASTER STATUS,Position,1) 205--let $assert_text= assert that the above events are not written to binlog 206--let $assert_cond= $master_pos_before_alter_user = $master_pos_after_alter_user 207--source include/assert.inc 208 209 210# Scenario 3: Create/Alter user with invalid plugin with multiple users 211--echo # ------ 212--echo "Scenario 3: CREATE/ALTER USER INVALID PLUGIN WITH MULTIPLE USERS" 213--echo # ------ 214 215--echo # 216--echo # Step 3) Create/Alter user having multiple users with invalid plugin. 217--echo # 218 219# CREATE throws ERROR since shsh auth_plugin does not exist 220# but will be written to binlog as,u2 already exists and u3 will be created. 221--error ER_PLUGIN_IS_NOT_LOADED 222CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH 'shsh', 223 u2@localhost IDENTIFIED BY 'pass', 224 u3@localhost IDENTIFIED WITH 'sha256_password'; 225 226# ALTER throws ERROR since shsh auth_plugin does not exist 227# but will be written to binlog as, u2 is altered. 228--error ER_PLUGIN_IS_NOT_LOADED 229ALTER USER IF EXISTS u1@localhost IDENTIFIED WITH 'shsh', 230 u2@localhost IDENTIFIED BY 'xyz', 231 u3@localhost IDENTIFIED WITH 'sha256_password'; 232 233# Check whether the above statements are written to binlog or not. 234--source include/show_binlog_events.inc 235 236--echo # 237--echo # Step 4) Cleanup 238--echo # 239DROP USER u2@localhost,u3@localhost; 240 241# Search for the warnings in the server log 242let server_log= $MYSQLTEST_VARDIR/log/mysqld.1.err; 243let SEARCH_FILE= $server_log; 244--echo # Search warnings in the server log 245 246--let SEARCH_PATTERN= Following users were specified in CREATE USER IF NOT EXISTS 247--source include/search_pattern_in_file.inc 248 249--let SEARCH_PATTERN= Following users were specified in ALTER USER IF EXISTS 250--source include/search_pattern_in_file.inc 251 252#Clean up 253--source include/rpl_end.inc 254 255--echo 256--echo End of 5.7 tests! 257--echo 258