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