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