Debian Codegraph @

mysql-test/suite/rpl/r/rpl_user_if_exists.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 [On Master]
7 #
8 #
9 [On Master]
10 # No users exist:
11 CALL mtr.add_suppression('Following users were specified in CREATE USER IF NOT EXISTS');
12 CALL mtr.add_suppression('Following users were specified in ALTER USER IF EXISTS');
13 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
14 Warnings:
15 Note 3162 Authorization ID 'wl8540'@'nohost1' does not exist.
16 Note 3162 Authorization ID 'wl8540'@'nohost2' does not exist.
17 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
18 COUNT(*)
19 0
20 ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
21 Warnings:
22 Note 3162 Authorization ID 'wl8540'@'nohost1' does not exist.
23 Note 3162 Authorization ID 'wl8540'@'nohost2' does not exist.
24 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
25 COUNT(*)
26 0
27 CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
28 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
29 COUNT(*)
30 2
31 include/sync_slave_sql_with_master.inc
32 [On Slave]
33 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
34 COUNT(*)
35 2
36 [On Master]
37 # All users exist:
38 ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
39 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
40 COUNT(*)
41 2
42 CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
43 Warnings:
44 Note 3163 Authorization ID 'wl8540'@'nohost1' already exists.
45 Note 3163 Authorization ID 'wl8540'@'nohost2' already exists.
46 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
47 COUNT(*)
48 2
49 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
50 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
51 COUNT(*)
52 0
53 include/sync_slave_sql_with_master.inc
54 [On Slave]
55 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
56 COUNT(*)
57 0
58 [On Master]
59 CREATE USER wl8540@nohost1;
60 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2;
61 Warnings:
62 Note 3162 Authorization ID 'wl8540'@'nohost2' does not exist.
63 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
64 COUNT(*)
65 0
66 ALTER USER IF EXISTS wl8540@nohost1, wl8540@nohost2 ACCOUNT LOCK;
67 Warnings:
68 Note 3162 Authorization ID 'wl8540'@'nohost1' does not exist.
69 Note 3162 Authorization ID 'wl8540'@'nohost2' does not exist.
70 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
71 COUNT(*)
72 0
73 CREATE USER IF NOT EXISTS wl8540@nohost1, wl8540@nohost2;
74 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
75 COUNT(*)
76 2
77 include/sync_slave_sql_with_master.inc
78 [On Slave]
79 SELECT COUNT(*) FROM mysql.user WHERE user = 'wl8540';
80 COUNT(*)
81 2
82 CREATE USER IF NOT EXISTS slave_user1@localhost
83 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
84 CREATE USER IF NOT EXISTS slave_user2@localhost;
85 [On MASTER]
86 CREATE USER IF NOT EXISTS slave_user1@localhost
87 IDENTIFIED WITH 'mysql_native_password' BY 'auth_string#%y';
88 ALTER USER IF EXISTS slave_user2@localhost
89 IDENTIFIED WITH 'sha256_password' WITH MAX_CONNECTIONS_PER_HOUR 2;
90 Warnings:
91 Note 3162 Authorization ID 'slave_user2'@'localhost' does not exist.
92 DROP USER IF EXISTS slave_user2@localhost;
93 Warnings:
94 Note 3162 Authorization ID 'slave_user2'@'localhost' does not exist.
95 DROP USER IF EXISTS wl8540@nohost1, wl8540@nohost2,
96 slave_user1@localhost,slave_user2@localhost;
97 Warnings:
98 Note 3162 Authorization ID 'slave_user2'@'localhost' does not exist.
99 FLUSH PRIVILEGES;
100 include/sync_slave_sql_with_master.inc
101 WL#11144: Check that ALTER/CREATE/DROP USER IF [NOT] EXISTS will succesfully replicate in multi user scenarios
102 # All users exist on master, some on slave
103 [connection master]
104 CREATE USER u1,u2;
105 set @@session.sql_log_bin=0;
106 CREATE USER u3,u4;
107 set @@session.sql_log_bin=1;
108 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
109 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
110 Warnings:
111 Note 3163 Authorization ID 'u1'@'%' already exists.
112 Note 3163 Authorization ID 'u2'@'%' already exists.
113 Note 3163 Authorization ID 'u3'@'%' already exists.
114 Note 3163 Authorization ID 'u4'@'%' already exists.
115 DROP USER IF EXISTS u1, u2, u3, u4;
116 CREATE USER u1,u2;
117 set @@session.sql_log_bin=0;
118 CREATE USER u3,u4;
119 set @@session.sql_log_bin=1;
120 DROP USER IF EXISTS u1, u2, u3, u4;
121 include/sync_slave_sql_with_master.inc
122 # All users exist on master, none on slave
123 [connection master]
124 set @@session.sql_log_bin=0;
125 CREATE USER u1,u2,u3,u4;
126 set @@session.sql_log_bin=1;
127 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
128 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
129 Warnings:
130 Note 3163 Authorization ID 'u1'@'%' already exists.
131 Note 3163 Authorization ID 'u2'@'%' already exists.
132 Note 3163 Authorization ID 'u3'@'%' already exists.
133 Note 3163 Authorization ID 'u4'@'%' already exists.
134 DROP USER IF EXISTS u1, u2, u3, u4;
135 set @@session.sql_log_bin=0;
136 CREATE USER u1,u2,u3,u4;
137 set @@session.sql_log_bin=1;
138 DROP USER IF EXISTS u1, u2, u3, u4;
139 include/sync_slave_sql_with_master.inc
140 # Some users exist on master, others on slave
141 [connection master]
142 set @@session.sql_log_bin=0;
143 CREATE USER u3,u4;
144 set @@session.sql_log_bin=1;
145 include/sync_slave_sql_with_master.inc
146 CREATE USER u1,u2;
147 [connection master]
148 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
149 Warnings:
150 Note 3162 Authorization ID 'u1'@'%' does not exist.
151 Note 3162 Authorization ID 'u2'@'%' does not exist.
152 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
153 Warnings:
154 Note 3163 Authorization ID 'u3'@'%' already exists.
155 Note 3163 Authorization ID 'u4'@'%' already exists.
156 set @@session.sql_log_bin=0;
157 DROP USER u1,u2;
158 set @@session.sql_log_bin=1;
159 include/sync_slave_sql_with_master.inc
160 DROP USER u3,u4;
161 [connection master]
162 DROP USER IF EXISTS u1, u2, u3, u4;
163 Warnings:
164 Note 3162 Authorization ID 'u1'@'%' does not exist.
165 Note 3162 Authorization ID 'u2'@'%' does not exist.
166 # Some users exist on master, all on slave
167 include/sync_slave_sql_with_master.inc
168 CREATE USER u1,u2;
169 [connection master]
170 CREATE USER u3,u4;
171 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
172 Warnings:
173 Note 3162 Authorization ID 'u1'@'%' does not exist.
174 Note 3162 Authorization ID 'u2'@'%' does not exist.
175 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
176 Warnings:
177 Note 3163 Authorization ID 'u3'@'%' already exists.
178 Note 3163 Authorization ID 'u4'@'%' already exists.
179 set sql_log_bin=0;
180 DROP USER u1,u2;
181 set sql_log_bin=1;
182 DROP USER IF EXISTS u1, u2, u3, u4;
183 Warnings:
184 Note 3162 Authorization ID 'u1'@'%' does not exist.
185 Note 3162 Authorization ID 'u2'@'%' does not exist.
186 include/sync_slave_sql_with_master.inc
187 # Some users exist on master, none on slave
188 [connection master]
189 set @@session.sql_log_bin=0;
190 CREATE USER u3,u4;
191 set @@session.sql_log_bin=1;
192 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
193 Warnings:
194 Note 3162 Authorization ID 'u1'@'%' does not exist.
195 Note 3162 Authorization ID 'u2'@'%' does not exist.
196 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
197 Warnings:
198 Note 3163 Authorization ID 'u3'@'%' already exists.
199 Note 3163 Authorization ID 'u4'@'%' already exists.
200 DROP USER IF EXISTS u1, u2, u3, u4;
201 set @@session.sql_log_bin=0;
202 CREATE USER u3,u4;
203 set @@session.sql_log_bin=1;
204 DROP USER IF EXISTS u1, u2, u3, u4;
205 Warnings:
206 Note 3162 Authorization ID 'u1'@'%' does not exist.
207 Note 3162 Authorization ID 'u2'@'%' does not exist.
208 # No users exist on master, some on slave
209 include/sync_slave_sql_with_master.inc
210 CREATE USER u3,u4;
211 [connection master]
212 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
213 Warnings:
214 Note 3162 Authorization ID 'u1'@'%' does not exist.
215 Note 3162 Authorization ID 'u2'@'%' does not exist.
216 Note 3162 Authorization ID 'u3'@'%' does not exist.
217 Note 3162 Authorization ID 'u4'@'%' does not exist.
218 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
219 DROP USER u1, u2;
220 set @@session.sql_log_bin=0;
221 DROP USER u3,u4;
222 set @@session.sql_log_bin=1;
223 DROP USER IF EXISTS u1, u2, u3, u4;
224 Warnings:
225 Note 3162 Authorization ID 'u1'@'%' does not exist.
226 Note 3162 Authorization ID 'u2'@'%' does not exist.
227 Note 3162 Authorization ID 'u3'@'%' does not exist.
228 Note 3162 Authorization ID 'u4'@'%' does not exist.
229 # No users exist on master, all on slave
230 include/sync_slave_sql_with_master.inc
231 CREATE USER u1,u2,u3,u4;
232 [connection master]
233 ALTER USER IF EXISTS u1, u2, u3, u4 ACCOUNT LOCK;
234 Warnings:
235 Note 3162 Authorization ID 'u1'@'%' does not exist.
236 Note 3162 Authorization ID 'u2'@'%' does not exist.
237 Note 3162 Authorization ID 'u3'@'%' does not exist.
238 Note 3162 Authorization ID 'u4'@'%' does not exist.
239 CREATE USER IF NOT EXISTS u1, u2, u3, u4;
240 set @@session.sql_log_bin=0;
241 DROP USER IF EXISTS u1, u2, u3, u4;
242 set @@session.sql_log_bin=1;
243 DROP USER IF EXISTS u1, u2, u3, u4;
244 Warnings:
245 Note 3162 Authorization ID 'u1'@'%' does not exist.
246 Note 3162 Authorization ID 'u2'@'%' does not exist.
247 Note 3162 Authorization ID 'u3'@'%' does not exist.
248 Note 3162 Authorization ID 'u4'@'%' does not exist.
249 CREATE USER IF NOT EXISTS u1 PASSWORD EXPIRE INTERVAL 66666 DAY;
250 ERROR HY000: Incorrect DAY value: '66666'
251 ALTER USER IF EXISTS u1 PASSWORD EXPIRE INTERVAL 66666 DAY;
252 ERROR HY000: Incorrect DAY value: '66666'
253 CREATE USER IF NOT EXISTS u1 PASSWORD EXPIRE INTERVAL 66666888888888888888 DAY;
254 ERROR 42000: Only integers allowed as number here near '66666888888888888888 DAY' at line 1
255 ALTER USER IF EXISTS u1 PASSWORD EXPIRE INTERVAL 66666888888888888888 DAY;
256 ERROR 42000: Only integers allowed as number here near '66666888888888888888 DAY' at line 1
257 CREATE USER IF NOT EXISTS u1 IDENTIFIED WITH 'xyz';
258 ERROR HY000: Plugin 'xyz' is not loaded
259 ALTER USER IF EXISTS u1 IDENTIFIED WITH 'xyz';
260 ERROR HY000: Plugin 'xyz' is not loaded
261 include/assert.inc [assert that the above Event has not been added to binlog]
262 include/assert.inc [assert that the GTID has not been generated]
263 CREATE USER u3;
264 CREATE USER IF NOT EXISTS u1 IDENTIFIED WITH 'shsh',u2 IDENTIFIED BY 'pass',u3 IDENTIFIED BY 'nw';
265 ERROR HY000: Plugin 'shsh' is not loaded
266 include/assert.inc [assert that the above Event has not been added to binlog]
267 CREATE USER u1;
268 DROP USER u3;
269 # Errors scenarios related to password_history
270 SET @old_password_history= @@GLOBAL.password_history;
271 SET GLOBAL password_history=1;
272 CREATE USER default_def@localhost IDENTIFIED BY 'haha';
273 ALTER USER IF EXISTS default_def@localhost IDENTIFIED BY 'haha',u1 IDENTIFIED BY 'xyz',u2 IDENTIFIED BY 'pqr';
274 ERROR HY000: Cannot use these credentials for 'default_def@localhost' because they contradict the password history policy
275 include/assert.inc [assert that the above Event has not been added to binlog]
276 DROP USER default_def@localhost;
277 CREATE USER to_be_renamed@localhost IDENTIFIED BY 'haha' PASSWORD HISTORY 1;
278 RENAME USER to_be_renamed@localhost TO now_renamed@localhost;
279 ALTER USER IF EXISTS now_renamed@localhost IDENTIFIED BY 'haha',u1 IDENTIFIED BY 'mno',u2 IDENTIFIED BY 'pqr';
280 ERROR HY000: Cannot use these credentials for 'now_renamed@localhost' because they contradict the password history policy
281 include/assert.inc [assert that the above Event has not been added to binlog]
282 DROP USER now_renamed@localhost;
283 DROP USER u1;
284 CREATE USER no_pwd_history@localhost IDENTIFIED BY 'haha';
285 RENAME TABLE mysql.password_history TO mysql.password_history_backup;
286 # Create will fail: eventual write operation to history
287 CREATE USER IF NOT EXISTS u1, no_pwd_history_err@localhost IDENTIFIED BY 'haha'
288 PASSWORD HISTORY 0 PASSWORD REUSE INTERVAL 0 DAY;
289 ERROR 42S02: Table 'mysql.password_history' doesn't exist
290 # Shall fail as ALTER USER IDENTIFIED BY can write to history
291 ALTER USER IF EXISTS u1, no_pwd_history@localhost IDENTIFIED BY 'hihi';
292 ERROR 42S02: Table 'mysql.password_history' doesn't exist
293 # Shall fail as DROP USER can write to history
294 DROP USER IF EXISTS u1,no_pwd_history@localhost;
295 ERROR 42S02: Table 'mysql.password_history' doesn't exist
296 include/assert.inc [assert that the above Event has not been added to binlog]
297 RENAME TABLE mysql.password_history_backup TO mysql.password_history;
298 SET @@GLOBAL.password_history= @old_password_history;
299 DROP USER no_pwd_history@localhost;
300 include/rpl_end.inc