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
|
# ==== Purpose ====
#
# Verify that a slave without replication privileges has
# Slave_IO_Running = No
#
# ==== Method ====
#
# We do the following steps:
# - Create a new replication user on master
# - Connect to slave and start replication as this user.
# - Verify that slave can replicate well, by creating a table and
# inserting a row into it.
# - Delete the user from the master.
# - Stop and start the slave (this should fail).
# - Check the Slave_IO_Running column of SHOW SLAVE STATUS.
#
# ==== Related bugs and modifications ====
#
# BUG#10780: slave can't connect to master - IO and SQL threads running
#
# Added scenario for testing WL#2284:
# - a) Replaced 'rpl' user name to 'rpluser_with_length_32_123456789'
# i.e with increased length of 32.
# - b) Connect to master through new user name of increased length.
#
# Added few replication cases related to WL#9591
--source include/not_group_replication_plugin.inc
--source include/set_privilege_checks_user_as_system_user.inc
--let $rpl_privilege_checks_user_grant_option = 1
--source include/master-slave.inc
--echo ==== Create new replication user ====
--echo [on master]
connection master;
CREATE USER rpluser_with_length_32_123456789@127.0.0.1 IDENTIFIED BY 'rpl';
--error ER_WRONG_STRING_LENGTH
CREATE USER rpluser_with_length_32_1234567890@127.0.0.1 IDENTIFIED BY 'rpl';
GRANT REPLICATION SLAVE ON *.* TO rpluser_with_length_32_123456789@127.0.0.1;
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
source include/stop_slave.inc;
--replace_column 2 ####
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpluser_with_length_32_123456789',
SOURCE_PASSWORD='rpl', GET_SOURCE_PUBLIC_KEY = 1, SOURCE_SSL = 1;
source include/start_slave.inc;
--echo ==== Do replication as new user ====
--echo [on master]
connection master;
CREATE TABLE t1 (n INT);
INSERT INTO t1 VALUES (1);
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
SELECT * FROM t1;
--echo ==== Delete new replication user ====
--echo [on master]
connection master;
DROP USER rpluser_with_length_32_123456789@127.0.0.1;
FLUSH PRIVILEGES;
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
--echo ==== Restart slave without privileges =====
# (slave.err will contain access denied error for this START SLAVE command)
source include/stop_slave.inc;
START SLAVE;
source include/wait_for_slave_sql_to_start.inc;
--let $slave_io_errno= convert_error(ER_ACCESS_DENIED_ERROR)
source include/wait_for_slave_io_to_stop.inc;
--echo ==== Verify that Slave IO thread stopped with error ====
# 1045 = ER_ACCESS_DENIED_ERROR
--let $slave_io_errno= 1045
--source include/wait_for_slave_io_error.inc
#
# Bug #77732 REGRESSION: REPLICATION FAILS FOR INSUFFICIENT PRIVILEGES
# Proving the bug fixes. Created user's privileges - REPLICATION and SLAVE -
# alone must suffice to successful connecting even with show handler
# not compatible to 5.6.
#
--echo [on master]
--connection master
CREATE USER rpluser_plain@127.0.0.1 IDENTIFIED BY 'rpl';
GRANT REPLICATION SLAVE ON *.* TO rpluser_plain@127.0.0.1;
--echo [on slave]
--connection slave
source include/stop_slave.inc;
CHANGE REPLICATION SOURCE TO SOURCE_USER='rpluser_plain', SOURCE_PASSWORD='rpl', SOURCE_SSL = 0, GET_SOURCE_PUBLIC_KEY = 1;
source include/start_slave.inc;
--echo [on master]
--connection master
DROP USER rpluser_plain@127.0.0.1;
FLUSH PRIVILEGES;
--echo [on slave]
--connection slave
source include/stop_slave.inc;
--echo # Resetting replication user to root
--source include/stop_slave_sql.inc
--replace_column 2 ####
CHANGE REPLICATION SOURCE TO SOURCE_USER = 'root', SOURCE_PASSWORD = '', SOURCE_SSL = 0;
--let $rpl_only_running_threads= 1
--source include/rpl_reset.inc
--echo # Replication Tests related to WL#9591 and WL#11057
--echo # Check if replication takes place when slave user
--echo # created with caching_sha2_pasword
--echo [on master]
connection master;
CREATE USER u1_caching_sha2_password@127.0.0.1 IDENTIFIED WITH
'caching_sha2_password' BY 'pwd';
GRANT all ON *.* TO u1_caching_sha2_password@127.0.0.1;
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
source include/stop_slave.inc;
--echo # Set the user created with authentication plugin caching_sha2_password
--echo # as replication slave user and get RSA public key from master
--replace_column 2 ####
CHANGE REPLICATION SOURCE TO SOURCE_USER='u1_caching_sha2_password', SOURCE_PASSWORD='pwd', SOURCE_SSL=0, GET_SOURCE_PUBLIC_KEY = 1;
source include/start_slave.inc;
--echo # Do replication with the user created with
--echo # authentication plugin caching_sha2_password
--echo [on master]
connection master;
CREATE USER u2 IDENTIFIED WITH 'caching_sha2_password' BY 'pwd_u2';
--echo # Below steps ensures that password is not cached initially and
--echo # After successful login, it is cached. Hence same login statement
--echo # with which login was denied earlier is allowed now
--error 1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
--echo # On slave it is ensured that password cached on master is not replicated
--echo # and after successful login on slave, it is cached
--error 1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on master]
connection master;
FLUSH PRIVILEGES;
--echo # Ensure that affect of FLUSH PRIVILEGE is propagated and cache from slave is also cleaned
--error 1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
--error 1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu2 -ppwd_u2 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on slave]
connection slave;
source include/stop_slave.inc;
--echo # Set the user created with authentication plugin caching_sha2_password
--echo # as replication slave user and get RSA public key from master
--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
--eval CHANGE REPLICATION SOURCE TO SOURCE_USER='u1_caching_sha2_password', SOURCE_PASSWORD='pwd', SOURCE_SSL = 0, SOURCE_PUBLIC_KEY_PATH='$MYSQL_TEST_DIR/std_data/rsa_public_key.pem', GET_SOURCE_PUBLIC_KEY = 0;
source include/start_slave.inc;
--echo # Do replication with the user created with
--echo # authentication plugin caching_sha2_password
--echo [on master]
connection master;
CREATE USER u3 IDENTIFIED WITH 'caching_sha2_password' BY 'pwd_u3';
--echo # Below steps ensures that password is not cached initially and
--echo # After successful login, it is cached. Hence same login statement
--echo # with which login was denied earlier is allowed now
--error 1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
--echo # On slave it is ensured that password cached on master is not replicated
--echo # and after successful login on slave, it is cached
--error 1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 -e "SELECT USER(), CURRENT_USER()" 2>&1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on master]
connection master;
FLUSH PRIVILEGES;
--echo # Ensure that affect of FLUSH PRIVILEGE is prpoagated and cache from slave is also cleaned
--error 1
--exec $MYSQL --host=127.0.0.1 -P $MASTER_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on slave]
--source include/sync_slave_sql_with_master.inc
--error 1
--exec $MYSQL --host=127.0.0.1 -P $SLAVE_MYPORT -uu3 -ppwd_u3 --ssl-mode=DISABLED -e "SELECT USER(), CURRENT_USER()" 2>&1
--echo [on slave]
--connection slave
source include/stop_slave.inc;
--echo ==== Cleanup (Note that slave IO thread is not running) ====
# cleanup: slave io thread has stopped so we reset replication
--replace_column 2 ####
CHANGE REPLICATION SOURCE TO SOURCE_USER = 'root', SOURCE_PASSWORD = '', SOURCE_SSL = 0;
# clear Slave_IO_Errno
--let $rpl_only_running_threads= 1
--source include/rpl_reset.inc
--echo [on master]
connection master;
DROP TABLE t1;
DROP USER u1_caching_sha2_password@127.0.0.1;
DROP USER u2, u3;
--source include/rpl_end.inc
--source include/force_restart.inc
|
