1call mtr.add_suppression('Corresponding entry in binary log used default authentication.*'); 2include/master-slave.inc 3Warnings: 4Note #### Sending passwords in plain text without SSL/TLS is extremely insecure. 5Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 6[connection master] 7SHOW GLOBAL VARIABLES LIKE 'log_builtin_as_identified_by_password'; 8Variable_name Value 9log_builtin_as_identified_by_password OFF 10# 11# WL#8540: Support IF [NOT] EXISTS clause in CREATE/DROP USER 12# 13CREATE USER u1@localhost; 14CREATE USER IF NOT EXISTS u1@localhost, u2@localhost; 15Warnings: 16Note 3163 User 'u1'@'localhost' already exists. 17ALTER USER IF EXISTS u1@localhost ACCOUNT LOCK; 18DROP USER u1@localhost; 19DROP USER IF EXISTS u2@localhost; 20# 21# Bug #21807286: "CREATE USER IF NOT EXISTS" REPORTS AN ERROR 22# 23CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'haha'; 24CREATE USER IF NOT EXISTS b21807286@localhost IDENTIFIED BY 'haha2'; 25Warnings: 26Note 3163 User 'b21807286'@'localhost' already exists. 27ALTER USER IF EXISTS b21807286@localhost IDENTIFIED BY 'haha3'; 28ALTER USER IF EXISTS b21807286_not_exists@localhost IDENTIFIED BY 'haha4'; 29Warnings: 30Note 3162 User 'b21807286_not_exists'@'localhost' does not exist. 31DROP USER b21807286@localhost; 32include/sync_slave_sql_with_master.inc 33[On Slave] 34include/show_binlog_events.inc 35Log_name Pos Event_type Server_id End_log_pos Info 36slave-bin.000001 # Query # # use `test`; CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' 37slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' 38slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' ACCOUNT LOCK 39slave-bin.000001 # Query # # use `test`; DROP USER u1@localhost 40slave-bin.000001 # Query # # use `test`; DROP USER IF EXISTS u2@localhost 41slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'b21807286'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*85D0F19E5598AC04AC7B3FCF5383247D28FB59EF' 42slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'b21807286'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*CF18BC9B45678165F755356FBD148E0F84D19A63' 43slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'b21807286'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*7B8BEAA4240FE1A214BFAEDD99FB3842E4234E5E' 44slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'b21807286_not_exists'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*498D18FA98FB65CF8E71D1D9DA5721646499DBD1' 45slave-bin.000001 # Query # # use `test`; DROP USER b21807286@localhost 46# 47# Bug #25813089: "CREATE USER IF NOT EXISTS SHOULD ALWAYS 48# BE WRITTEN TO THE BINLOGS" 49# 50# ------ 51"Scenario 1: CREATE/ALTER USER IF[NOT]EXISTS FOR A SINGLE USER" 52# ------ 53CREATE USER u1@localhost IDENTIFIED BY 'pass1'; 54CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass2'; 55Warnings: 56Note 3163 User 'u1'@'localhost' already exists. 57ALTER USER u1@localhost IDENTIFIED BY 'pass3'; 58ALTER USER IF EXISTS u1@localhost IDENTIFIED BY 'pass4'; 59ALTER USER IF EXISTS u1_not_exists@localhost IDENTIFIED BY 'pass5'; 60Warnings: 61Note 3162 User 'u1_not_exists'@'localhost' does not exist. 62DROP USER u1@localhost; 63[On Master] 64include/show_binlog_events.inc 65Log_name Pos Event_type Server_id End_log_pos Info 66master-bin.000001 # Query # # use `test`; CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*22A99BA288DB55E8E230679259740873101CD636' 67master-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1B884EF334D678B0743C8CE0425F980669D8FBB6' 68master-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*35B5E90BC4F5AE5D02ED515DF6B61141F24EDA02' 69master-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*B718D8767BBACFBCE02BA2669ECCEB05930FF7D7' 70master-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1_not_exists'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*9D3D6FA96FA91117D387A4B990C41F5809A536EE' 71master-bin.000001 # Query # # use `test`; DROP USER u1@localhost 72include/sync_slave_sql_with_master.inc 73[On Slave] 74include/show_binlog_events.inc 75Log_name Pos Event_type Server_id End_log_pos Info 76slave-bin.000001 # Query # # use `test`; CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*22A99BA288DB55E8E230679259740873101CD636' 77slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1B884EF334D678B0743C8CE0425F980669D8FBB6' 78slave-bin.000001 # Query # # use `test`; ALTER USER 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*35B5E90BC4F5AE5D02ED515DF6B61141F24EDA02' 79slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*B718D8767BBACFBCE02BA2669ECCEB05930FF7D7' 80slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1_not_exists'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*9D3D6FA96FA91117D387A4B990C41F5809A536EE' 81slave-bin.000001 # Query # # use `test`; DROP USER u1@localhost 82"Creating a user only on slave" 83CREATE USER IF NOT EXISTS slave_user@localhost IDENTIFIED BY 'slave_pass'; 84# ------ 85"Scenario 2: CREATE/ALTER USER IF[NOT]EXISTS FOR MULTIPLE USERS" 86# ------ 87CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass1', 88u2@localhost IDENTIFIED BY 'pass2'; 89CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED BY 'pass1', 90u2@localhost IDENTIFIED BY 'pass2', 91u3@localhost IDENTIFIED BY 'pass3'; 92Warnings: 93Note 3163 User 'u1'@'localhost' already exists. 94Note 3163 User 'u2'@'localhost' already exists. 95ALTER USER IF EXISTS u1@localhost IDENTIFIED BY 'pass4', 96u2@localhost IDENTIFIED BY 'pass5', 97u3@localhost IDENTIFIED BY 'pass6', 98u4_not_exists@localhost IDENTIFIED BY 'pass7', 99slave_user@localhost IDENTIFIED BY 'pass8'; 100Warnings: 101Note 3162 User 'u4_not_exists'@'localhost' does not exist. 102Note 3162 User 'slave_user'@'localhost' does not exist. 103DROP USER IF EXISTS u1@localhost, u2@localhost, u3@localhost, slave_user@localhost; 104Warnings: 105Note 3162 User 'slave_user'@'localhost' does not exist. 106[On Master] 107include/show_binlog_events.inc 108Log_name Pos Event_type Server_id End_log_pos Info 109master-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*22A99BA288DB55E8E230679259740873101CD636','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1B884EF334D678B0743C8CE0425F980669D8FBB6' 110master-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*22A99BA288DB55E8E230679259740873101CD636','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1B884EF334D678B0743C8CE0425F980669D8FBB6','u3'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*35B5E90BC4F5AE5D02ED515DF6B61141F24EDA02' 111master-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*B718D8767BBACFBCE02BA2669ECCEB05930FF7D7','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*9D3D6FA96FA91117D387A4B990C41F5809A536EE','u3'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E4744236E26ADD8C3C3D727C7851A097FBBB8A87','u4_not_exists'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BEA19EBE4ACA6B1820BF580B4054EB6B5B0D198D','slave_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*F40D7E5342EC4960531243A96CC2E94079BC11B7' 112master-bin.000001 # Query # # use `test`; DROP USER IF EXISTS u1@localhost, u2@localhost, u3@localhost, slave_user@localhost 113include/sync_slave_sql_with_master.inc 114[On Slave] 115include/show_binlog_events.inc 116Log_name Pos Event_type Server_id End_log_pos Info 117slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'slave_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A8204EFAFFB19DEF65BE70042A9D93F1971925BC' 118slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*22A99BA288DB55E8E230679259740873101CD636','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1B884EF334D678B0743C8CE0425F980669D8FBB6' 119slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*22A99BA288DB55E8E230679259740873101CD636','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*1B884EF334D678B0743C8CE0425F980669D8FBB6','u3'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*35B5E90BC4F5AE5D02ED515DF6B61141F24EDA02' 120slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*B718D8767BBACFBCE02BA2669ECCEB05930FF7D7','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*9D3D6FA96FA91117D387A4B990C41F5809A536EE','u3'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*E4744236E26ADD8C3C3D727C7851A097FBBB8A87','u4_not_exists'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*BEA19EBE4ACA6B1820BF580B4054EB6B5B0D198D','slave_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*F40D7E5342EC4960531243A96CC2E94079BC11B7' 121slave-bin.000001 # Query # # use `test`; DROP USER IF EXISTS u1@localhost, u2@localhost, u3@localhost, slave_user@localhost 122include/stop_slave_io.inc 123# ------ 124"Scenario 3: CREATE/ALTER USER IF[NOT]EXISTS WITH" 125"AUTHENTICATION PLUGIN PROVIDED EXPLICITLY" 126# ------ 127include/rpl_restart_server.inc [server_number=1 parameters: --default_authentication_plugin=sha256_password] 128include/start_slave_io.inc 129include/rpl_reset.inc 130SHOW VARIABLES LIKE 'default_authentication_plugin'; 131Variable_name Value 132default_authentication_plugin sha256_password 133CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH mysql_native_password by 'pass_native'; 134CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH mysql_native_password by 'pass_native2'; 135Warnings: 136Note 3163 User 'u1'@'localhost' already exists. 137ALTER USER IF EXISTS u1@localhost IDENTIFIED WITH mysql_native_password BY 'pass_native3'; 138ALTER USER IF EXISTS u2_not_exists@localhost IDENTIFIED WITH mysql_native_password BY 'pass_native4'; 139Warnings: 140Note 3162 User 'u2_not_exists'@'localhost' does not exist. 141SELECT user,authentication_string FROM mysql.user WHERE user='u1'; 142user authentication_string 143u1 *52BA180A884483D54A3CEB45FB50D4E5B8071399 144DROP USER u1@localhost; 145[On Master] 146include/show_binlog_events.inc 147Log_name Pos Event_type Server_id End_log_pos Info 148master-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6DD89A65903AC3BCC4873BA43D17EFFB912C67EE' 149master-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*AD3E6D897B165BF1FA7D3583FA1FF6AF31FA3C4B' 150master-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*52BA180A884483D54A3CEB45FB50D4E5B8071399' 151master-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u2_not_exists'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*53ED7183CBBC6D9995663DD96EFFA60E2CE2C96E' 152master-bin.000001 # Query # # use `test`; DROP USER u1@localhost 153include/sync_slave_sql_with_master.inc 154[On Slave] 155include/show_binlog_events.inc 156Log_name Pos Event_type Server_id End_log_pos Info 157slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6DD89A65903AC3BCC4873BA43D17EFFB912C67EE' 158slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*AD3E6D897B165BF1FA7D3583FA1FF6AF31FA3C4B' 159slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*52BA180A884483D54A3CEB45FB50D4E5B8071399' 160slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u2_not_exists'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*53ED7183CBBC6D9995663DD96EFFA60E2CE2C96E' 161slave-bin.000001 # Query # # use `test`; DROP USER u1@localhost 162# 163# Bug26680035: CREATE/ALTER USER ERRORS OUT BUT GETS RECORDED IN 164# THE BINARY LOG 165# 166call mtr.add_suppression("Following users were specified in CREATE USER IF NOT EXISTS but they already exist. *"); 167# ------ 168"Scenario 1: CREATE USER WITH INVALID PLUGIN" 169# ------ 170# 171# Step 1.1) Create user with invalid plugin. 172# 173CREATE USER u1@localhost IDENTIFIED WITH 'plugin1'; 174ERROR HY000: Plugin 'plugin1' is not loaded 175# 176# Step 1.2) Create user having 'if not exists' clause with invalid 177# plugin. 178# 179CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH 'plugin2'; 180ERROR HY000: Plugin 'plugin2' is not loaded 181include/assert.inc [assert that the above events are not written to binlog] 182# ------ 183"Scenario 2: ALTER USER WITH INVALID PLUGIN" 184# ------ 185CREATE USER u2@localhost; 186# 187# Step 2.1) Alter user with invalid plugin. 188# 189ALTER USER u2@localhost IDENTIFIED WITH 'plugin3'; 190ERROR HY000: Plugin 'plugin3' is not loaded 191# 192# Step 2.2) Alter user having 'if exists' clause with invalid plugin. 193# 194ALTER USER IF EXISTS u2@localhost IDENTIFIED WITH 'plugin4'; 195ERROR HY000: Plugin 'plugin4' is not loaded 196include/assert.inc [assert that the above events are not written to binlog] 197# ------ 198"Scenario 3: CREATE/ALTER USER INVALID PLUGIN WITH MULTIPLE USERS" 199# ------ 200# 201# Step 3) Create/Alter user having multiple users with invalid plugin. 202# 203CREATE USER IF NOT EXISTS u1@localhost IDENTIFIED WITH 'shsh', 204u2@localhost IDENTIFIED BY 'pass', 205u3@localhost IDENTIFIED WITH 'sha256_password'; 206ERROR HY000: Plugin 'shsh' is not loaded 207ALTER USER IF EXISTS u1@localhost IDENTIFIED WITH 'shsh', 208u2@localhost IDENTIFIED BY 'xyz', 209u3@localhost IDENTIFIED WITH 'sha256_password'; 210ERROR HY000: Plugin 'shsh' is not loaded 211include/show_binlog_events.inc 212Log_name Pos Event_type Server_id End_log_pos Info 213slave-bin.000001 # Query # # use `test`; CREATE USER 'u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' 214slave-bin.000001 # Query # # use `test`; CREATE USER IF NOT EXISTS 'u1'@'localhost' IDENTIFIED WITH 'shsh','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7','u3'@'localhost' IDENTIFIED WITH 'sha256_password' 215slave-bin.000001 # Query # # use `test`; ALTER USER IF EXISTS 'u1'@'localhost' IDENTIFIED WITH 'shsh','u2'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*39C549BDECFBA8AFC3CE6B948C9359A0ECE08DE2','u3'@'localhost' IDENTIFIED WITH 'sha256_password' 216# 217# Step 4) Cleanup 218# 219DROP USER u2@localhost,u3@localhost; 220# Search warnings in the server log 221include/rpl_end.inc 222 223End of 5.7 tests! 224 225