Debian Codegraph @

mysql-test/suite/rpl/t/rpl_user_if_exists.test

raw
1 # Created: 2015-07-01 Lalit Choudhary
2 # WL#8540
3 # Testing IF [NOT] EXISTS clause in CREATE/DROP/ALTER USER with Replication.
4 --source include/master-slave.inc
5 --connection master
6 --echo [On Master]
7 --echo #
8 --echo #
9 --connection master
10 --echo [On Master]
11 --echo # No users exist:
12
13 CALL mtr.add_suppression('Following users were specified in CREATE USER IF NOT EXISTS');
14 CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS');
15
16 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
17 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
18 ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
19 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
20 CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
21 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
22
23 --source include/sync_slave_sql_with_master.inc
24 --echo [On Slave]
25 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
26
27 --connection master
28 --echo [On Master]
29 --echo # All users exist:
30
31 ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
32 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
33 CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
34 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
35 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
36 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
37
38 --source include/sync_slave_sql_with_master.inc
39 --echo [On Slave]
40 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
41
42 --connection master
43 --echo [On Master]
44 CREATE USER wl8540@nohost1;
45
46 # One of two users exist:
47
48 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
49 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
50 ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
51 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
52 CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
53 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
54
55 --source include/sync_slave_sql_with_master.inc
56 --echo [On Slave]
57 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
58
59 # Creating users on SLAVE
60 CREATE USER IF NOT EXISTS slave_user1@localhost
61 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
62
63 CREATE USER IF NOT EXISTS slave_user2@localhost;
64
65 --connection master
66 --echo [On MASTER]
67
68 # Creating slave_user1 not exists on MASTER and exist on SLAVE.
69 CREATE USER IF NOT EXISTS slave_user1@localhost
70 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
71
72 # ALTER slave_user2 not exists on MASTER and exist on SLAVE.
73 ALTER USER IF EXISTS slave_user2@localhost
74 IDENTIFIED WITH 'sha256_password' WITH MAX_CONNECTIONS_PER_HOUR 2;
75
76 # Dropping slave_user2 not exists on MASTER and exist on SLAVE.
77 DROP USER IF EXISTS slave_user2@localhost;
78
79 # Cleanup
80 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2,
81 slave_user1@localhost,slave_user2@localhost;
82 FLUSH PRIVILEGES;
83
84 --source include/sync_slave_sql_with_master.inc
85
86 --echo WL#11144: Check that ALTER/CREATE/DROP USER IF [NOT] EXISTS will succesfully replicate in multi user scenarios
87
88 --echo # All users exist on master, some on slave
89
90 --source include/rpl_connection_master.inc
91 CREATE USER u1,u2;
92 set @@session.sql_log_bin=0;
93 CREATE USER u3,u4;
94 set @@session.sql_log_bin=1;
95
96 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
97 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
98 DROP USER IF EXISTS u1, u2, u3, u4;
99
100 CREATE USER u1,u2;
101 set @@session.sql_log_bin=0;
102 CREATE USER u3,u4;
103 set @@session.sql_log_bin=1;
104
105 DROP USER IF EXISTS u1, u2, u3, u4;
106 --source include/sync_slave_sql_with_master.inc
107
108 --echo # All users exist on master, none on slave
109
110 --source include/rpl_connection_master.inc
111 set @@session.sql_log_bin=0;
112 CREATE USER u1,u2,u3,u4;
113 set @@session.sql_log_bin=1;
114
115 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
116 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
117 DROP USER IF EXISTS u1, u2, u3, u4;
118
119 set @@session.sql_log_bin=0;
120 CREATE USER u1,u2,u3,u4;
121 set @@session.sql_log_bin=1;
122
123 DROP USER IF EXISTS u1, u2, u3, u4;
124 --source include/sync_slave_sql_with_master.inc
125
126 --echo # Some users exist on master, others on slave
127
128 --source include/rpl_connection_master.inc
129 set @@session.sql_log_bin=0;
130 CREATE USER u3,u4;
131 set @@session.sql_log_bin=1;
132
133 --source include/sync_slave_sql_with_master.inc
134 CREATE USER u1,u2;
135
136 --source include/rpl_connection_master.inc
137 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
138 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
139
140 set @@session.sql_log_bin=0;
141 DROP USER u1,u2;
142 set @@session.sql_log_bin=1;
143
144 --source include/sync_slave_sql_with_master.inc
145 DROP USER u3,u4;
146
147 --source include/rpl_connection_master.inc
148 DROP USER IF EXISTS u1, u2, u3, u4;
149
150 --echo # Some users exist on master, all on slave
151
152 --source include/sync_slave_sql_with_master.inc
153 CREATE USER u1,u2;
154
155 --source include/rpl_connection_master.inc
156 CREATE USER u3,u4;
157
158 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
159 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
160
161 set sql_log_bin=0;
162 DROP USER u1,u2;
163 set sql_log_bin=1;
164
165 DROP USER IF EXISTS u1, u2, u3, u4;
166 --source include/sync_slave_sql_with_master.inc
167
168 --echo # Some users exist on master, none on slave
169
170 --source include/rpl_connection_master.inc
171 set @@session.sql_log_bin=0;
172 CREATE USER u3,u4;
173 set @@session.sql_log_bin=1;
174
175 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
176 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
177 DROP USER IF EXISTS u1, u2, u3, u4;
178
179 set @@session.sql_log_bin=0;
180 CREATE USER u3,u4;
181 set @@session.sql_log_bin=1;
182 DROP USER IF EXISTS u1, u2, u3, u4;
183
184 --echo # No users exist on master, some on slave
185
186 --source include/sync_slave_sql_with_master.inc
187 CREATE USER u3,u4;
188
189 --source include/rpl_connection_master.inc
190
191 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
192 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
193 DROP USER u1, u2;
194
195 set @@session.sql_log_bin=0;
196 DROP USER u3,u4;
197 set @@session.sql_log_bin=1;
198
199 DROP USER IF EXISTS u1, u2, u3, u4;
200
201 --echo # No users exist on master, all on slave
202
203 --source include/sync_slave_sql_with_master.inc
204
205 CREATE USER u1,u2,u3,u4;
206
207 --source include/rpl_connection_master.inc
208
209 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
210 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
211
212 set @@session.sql_log_bin=0;
213 DROP USER IF EXISTS u1, u2, u3, u4;
214 set @@session.sql_log_bin=1;
215
216 DROP USER IF EXISTS u1, u2, u3, u4;
217
218 # Check that if Create/alter user if exists gets ERROR it won't be logged in the binary log.
219 # If GTID mode is ON check that GTID won't be generated.
220
221 --let $pos_before= query_get_value(show master status,Position,1)
222 --let $gtid_executed_before= `SELECT @@GLOBAL.GTID_EXECUTED`
223
224 # Gets error due to INCORRECT day value specified.
225 --error ER_WRONG_VALUE
226 CREATE USER IF NOT EXISTS u1 PASSWORD EXPIRE INTERVAL 66666 DAY;
227 --error ER_WRONG_VALUE
228 ALTER USER IF EXISTS u1 PASSWORD EXPIRE INTERVAL 66666 DAY;
229
230 # Gets error as only INTEGERS are allowed.
231 --error ER_PARSE_ERROR
232 CREATE USER IF NOT EXISTS u1 PASSWORD EXPIRE INTERVAL 66666888888888888888 DAY;
233 --error ER_PARSE_ERROR
234 ALTER USER IF EXISTS u1 PASSWORD EXPIRE INTERVAL 66666888888888888888 DAY;
235
236 # Gets error as there is no auth_plugin named 'xyz'
237 --error ER_PLUGIN_IS_NOT_LOADED
238 CREATE USER IF NOT EXISTS u1 IDENTIFIED WITH 'xyz';
239 --error ER_PLUGIN_IS_NOT_LOADED
240 ALTER USER IF EXISTS u1 IDENTIFIED WITH 'xyz';
241
242 # Check that the above statements have not been logged in binary log
243 --let $assert_text= assert that the above Event has not been added to binlog
244 --let $assert_cond= [SHOW MASTER STATUS, Position,1] = $pos_before
245 --source include/assert.inc
246
247 --let $gtid_executed_after= `SELECT @@GLOBAL.GTID_EXECUTED`
248 # Check that the GTID has not been generated for the above failed statements
249 --let $assert_text= assert that the GTID has not been generated
250 --let $assert_cond= "$gtid_executed_after" = "$gtid_executed_before"
251 --source include/assert.inc
252
253 # Check that replication works correctly in multi create/alter statements
254 # where create/alter of some user gets failed.
255
256 CREATE USER u3;
257
258 # User u1 does not exists
259 --let $pos_before= query_get_value(show master status,Position,1)
260
261 # CREATE gets ERROR since shsh auth_plugin do not exists
262 --error ER_PLUGIN_IS_NOT_LOADED
263 CREATE USER IF NOT EXISTS u1 IDENTIFIED WITH 'shsh',u2 IDENTIFIED BY 'pass',u3 IDENTIFIED BY 'nw';
264
265 # Check that the above statement has not been logged
266 --let $assert_text= assert that the above Event has not been added to binlog
267 --let $assert_cond= [SHOW MASTER STATUS, Position,1] = $pos_before
268 --source include/assert.inc
269
270 CREATE USER u1;
271 DROP USER u3;
272
273 --echo # Errors scenarios related to password_history
274
275 SET @old_password_history= @@GLOBAL.password_history;
276 SET GLOBAL password_history=1;
277
278 CREATE USER default_def@localhost IDENTIFIED BY 'haha';
279
280 --let $pos_before= query_get_value(show master status,Position,1)
281
282 # Alter will fail as password is in history
283 --error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
284 ALTER USER IF EXISTS default_def@localhost IDENTIFIED BY 'haha',u1 IDENTIFIED BY 'xyz',u2 IDENTIFIED BY 'pqr';
285
286 # Check that the above statement has not been logged
287 --let $assert_text= assert that the above Event has not been added to binlog
288 --let $assert_cond= [SHOW MASTER STATUS, Position,1] = $pos_before
289 --source include/assert.inc
290
291 DROP USER default_def@localhost;
292
293 CREATE USER to_be_renamed@localhost IDENTIFIED BY 'haha' PASSWORD HISTORY 1;
294 RENAME USER to_be_renamed@localhost TO now_renamed@localhost;
295
296 --let $pos_before= query_get_value(show master status,Position,1)
297
298 # Alter will fail as password is in history
299 --error ER_CREDENTIALS_CONTRADICT_TO_HISTORY
300 ALTER USER IF EXISTS now_renamed@localhost IDENTIFIED BY 'haha',u1 IDENTIFIED BY 'mno',u2 IDENTIFIED BY 'pqr';
301
302 # Check that the above statement has not been logged
303 --let $assert_text= assert that the above Event has not been added to binlog
304 --let $assert_cond= [SHOW MASTER STATUS, Position,1] = $pos_before
305 --source include/assert.inc
306
307 DROP USER now_renamed@localhost;
308 DROP USER u1;
309
310 CREATE USER no_pwd_history@localhost IDENTIFIED BY 'haha';
311
312 RENAME TABLE mysql.password_history TO mysql.password_history_backup;
313
314 --let $pos_before= query_get_value(show master status,Position,1)
315
316 --echo # Create will fail: eventual write operation to history
317 --error ER_NO_SUCH_TABLE
318 CREATE USER IF NOT EXISTS u1, no_pwd_history_err@localhost IDENTIFIED BY 'haha'
319 PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY;
320
321 --echo # Shall fail as ALTER USER IDENTIFIED BY can write to history
322 --error ER_NO_SUCH_TABLE
323 ALTER USER IF EXISTS u1, no_pwd_history@localhost IDENTIFIED BY 'hihi';
324
325 --echo # Shall fail as DROP USER can write to history
326 --error ER_NO_SUCH_TABLE
327 DROP USER IF EXISTS u1,no_pwd_history@localhost;
328
329 # Check that the above statements have not been logged in binary log
330 --let $assert_text= assert that the above Event has not been added to binlog
331 --let $assert_cond= [SHOW MASTER STATUS, Position,1] = $pos_before
332 --source include/assert.inc
333
334 RENAME TABLE mysql.password_history_backup TO mysql.password_history;
335
336 # Cleanup
337 SET @@GLOBAL.password_history= @old_password_history;
338 DROP USER no_pwd_history@localhost;
339 --source include/rpl_end.inc