1 # This test is to check various cases of connections
2 # with right and wrong password, with and without database
3 # Unfortunately the check is incomplete as we can't connect without database
5 # This test makes no sense with the embedded server
6 --source include/not_embedded.inc
8 # check that CSV engine was compiled in, as the test relies on the presence
9 # of the log tables (which are CSV-based). By connect mysql; show tables;
10 --source include/have_csv.inc
12 # Save the initial number of concurrent sessions
13 --source include/count_sessions.inc
17 drop table if exists t1,t2;
20 #connect (con1,localhost,root,,"");
22 connect (con1,localhost,root,,mysql);
24 connect (con2,localhost,root,,test);
27 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
28 --error ER_ACCESS_DENIED_ERROR
29 connect (fail_con,localhost,root,z,test2);
30 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
31 --error ER_ACCESS_DENIED_ERROR
32 connect (fail_con,localhost,root,z,);
38 grant ALL on *.* to test@localhost identified by "gambling";
39 grant ALL on *.* to test@127.0.0.1 identified by "gambling";
41 # Now check this user with different databases
42 #connect (con1,localhost,test,gambling,"");
44 connect (con3,localhost,test,gambling,mysql);
46 connect (con4,localhost,test,gambling,test);
53 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
54 --error ER_ACCESS_DENIED_ERROR
55 connect (fail_con,localhost,test,,test2);
56 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
57 --error ER_ACCESS_DENIED_ERROR
58 # Need to protect "" within '' so it's interpreted literally
59 connect (fail_con,localhost,test,,'""');
60 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
61 --error ER_ACCESS_DENIED_ERROR
62 connect (fail_con,localhost,test,zorro,test2);
63 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
64 --error ER_ACCESS_DENIED_ERROR
65 connect (fail_con,localhost,test,zorro,);
67 # check if old password version also works
68 update mysql.user set password=old_password("gambling2") where user=_binary"test";
71 connect (con10,localhost,test,gambling2,);
72 connect (con5,localhost,test,gambling2,mysql);
75 --error ER_PASSWD_LENGTH
76 set password='gambling3';
77 set password=old_password('gambling3');
79 connect (con6,localhost,test,gambling3,test);
87 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
88 --error ER_ACCESS_DENIED_ERROR
89 connect (fail_con,localhost,test,,test2);
90 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
91 --error ER_ACCESS_DENIED_ERROR
92 connect (fail_con,localhost,test,,);
93 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
94 --error ER_ACCESS_DENIED_ERROR
95 connect (fail_con,localhost,test,zorro,test2);
96 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
97 --error ER_ACCESS_DENIED_ERROR
98 connect (fail_con,localhost,test,zorro,);
101 # remove user 'test' so that other tests which may use 'test'
102 # do not depend on this test.
103 delete from mysql.user where user=_binary"test";
107 # Bug#12517 Clear user variables and replication events before
108 # closing temp tables in thread cleanup.
109 connect (con7,localhost,root,,test);
111 let $connection_id= `select connection_id()`;
112 create table t1 (id integer not null auto_increment primary key);
113 create temporary table t2(id integer not null auto_increment primary key);
115 delete from t1 where id like @id;
118 # Wait till the session con7 is disconnected
119 let $wait_condition =
121 FROM information_schema.processlist
122 WHERE id = '$connection_id';
123 --source include/wait_condition.inc
126 --echo # ------------------------------------------------------------------
127 --echo # -- End of 4.1 tests
128 --echo # ------------------------------------------------------------------
130 ###########################################################################
133 --echo # -- Bug#33507: Event scheduler creates more threads than max_connections
134 --echo # -- which results in user lockout.
137 GRANT USAGE ON *.* TO mysqltest_u1@localhost;
139 # NOTE: if the test case fails sporadically due to spurious connections,
140 # consider disabling all users.
143 let $saved_max_connections = `SELECT @@global.max_connections`;
144 SET GLOBAL max_connections = 3;
145 SET GLOBAL event_scheduler = ON;
148 --echo # -- Waiting for Event Scheduler to start...
149 let $wait_condition =
151 FROM information_schema.processlist
152 WHERE user = 'event_scheduler';
153 --source include/wait_condition.inc
156 --echo # -- Disconnecting default connection...
160 --echo # -- Check that we allow exactly three user connections, no matter how
161 --echo # -- many threads are running.
164 --echo # -- Connecting (1)...
165 let $con_name = con_1;
166 let $con_user_name = mysqltest_u1;
167 --source include/connect2.inc
170 --echo # -- Connecting (2)...
171 let $con_name = con_2;
172 let $con_user_name = mysqltest_u1;
173 --source include/connect2.inc
176 --echo # -- Connecting (3)...
177 let $con_name = con_3;
178 let $con_user_name = mysqltest_u1;
179 --source include/connect2.inc
182 --echo # -- Connecting (4) [should fail]...
183 let $con_name = con_4;
184 let $con_user_name = mysqltest_u1;
185 let $wait_timeout = 5;
186 --source include/connect2.inc
189 --echo # -- Check that we allow one extra SUPER-user connection.
192 --echo # -- Connecting super (1)...
193 let $con_name = con_super_1;
194 let $con_user_name = root;
195 --source include/connect2.inc
198 --echo # -- Connecting super (2) [should fail]...
199 let $con_name = con_super_2;
200 let $con_user_name = root;
201 let $wait_timeout = 5;
202 --source include/connect2.inc
205 --echo # -- Ensure that we have Event Scheduler thread, 3 ordinary user
206 --echo # -- connections and one extra super-user connection.
207 SELECT user FROM information_schema.processlist ORDER BY id;
210 --echo # -- Resetting variables...
211 --eval SET GLOBAL max_connections = $saved_max_connections
214 --echo # -- Stopping Event Scheduler...
215 SET GLOBAL event_scheduler = OFF;
218 --echo # -- Waiting for Event Scheduler to stop...
219 let $wait_condition =
221 FROM information_schema.processlist
222 WHERE user = 'event_scheduler';
223 --source include/wait_condition.inc
226 --echo # -- That's it. Closing connections...
230 --disconnect con_super_1
233 --echo # -- Restoring default connection...
234 --connect (default,localhost,root,,test)
237 --echo # -- Waiting for connections to close...
238 let $wait_condition =
240 FROM information_schema.processlist
242 --source include/wait_condition.inc
245 DROP USER mysqltest_u1@localhost;
248 --echo # -- End of Bug#33507.
251 ###########################################################################
253 --echo # -- Bug#35074: max_used_connections is not correct.
259 SHOW STATUS LIKE 'max_used_connections';
262 --echo # -- Starting Event Scheduler...
263 SET GLOBAL event_scheduler = ON;
265 --echo # -- Waiting for Event Scheduler to start...
266 --source include/running_event_scheduler.inc
268 # NOTE: We should use a new connection here instead of reconnect in order to
269 # avoid races (we can not for sure when the connection being disconnected is
270 # actually disconnected on the server).
273 --echo # -- Opening a new connection to check max_used_connections...
274 --connect (con_1,localhost,root)
277 --echo # -- Check that max_used_connections hasn't changed.
278 SHOW STATUS LIKE 'max_used_connections';
281 --echo # -- Closing new connection...
286 --echo # -- Stopping Event Scheduler...
287 SET GLOBAL event_scheduler = OFF;
289 --echo # -- Waiting for Event Scheduler to stop...
290 --source include/no_running_events.inc
293 --echo # -- End of Bug#35074.
297 ###########################################################################
300 --echo # -- Bug#49752: 2469.126.2 unintentionally breaks authentication
301 --echo # against MySQL 5.1 server
304 GRANT ALL ON test.* TO 'Azundris12345678'@'localhost' IDENTIFIED BY 'test123';
308 --replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT
309 connect (con1,localhost,Azundris123456789,test123,test);
314 DROP USER 'Azundris12345678'@'localhost';
319 --echo # -- End of Bug#49752
324 --echo # ------------------------------------------------------------------
325 --echo # -- End of 5.1 tests
326 --echo # ------------------------------------------------------------------
328 # Wait till all disconnects are completed
329 --source include/wait_until_count_sessions.inc