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 4 5# This test makes no sense with the embedded server 6--source include/not_embedded.inc 7 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 11 12# Save the initial number of concurrent sessions 13--source include/count_sessions.inc 14 15SET global secure_auth=0; 16 17#connect (con1,localhost,root,,""); 18#show tables; 19connect (con1,localhost,root,,mysql); 20show tables; 21connect (con2,localhost,root,,test); 22show tables; 23 24--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 25--error ER_ACCESS_DENIED_ERROR 26connect (fail_con,localhost,root,z,test2); 27--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 28--error ER_ACCESS_DENIED_ERROR 29connect (fail_con,localhost,root,z,); 30 31connection default; 32disconnect con1; 33disconnect con2; 34 35grant ALL on *.* to test@localhost identified by "gambling"; 36grant ALL on *.* to test@127.0.0.1 identified by "gambling"; 37 38# Now check this user with different databases 39#connect (con1,localhost,test,gambling,""); 40#show tables; 41connect (con3,localhost,test,gambling,mysql); 42show tables; 43connect (con4,localhost,test,gambling,test); 44show tables; 45 46connection default; 47disconnect con3; 48disconnect con4; 49 50--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 51--error ER_ACCESS_DENIED_ERROR 52connect (fail_con,localhost,test,,test2); 53--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 54--error ER_ACCESS_DENIED_ERROR 55# Need to protect "" within '' so it's interpreted literally 56connect (fail_con,localhost,test,,'""'); 57--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 58--error ER_ACCESS_DENIED_ERROR 59connect (fail_con,localhost,test,zorro,test2); 60--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 61--error ER_ACCESS_DENIED_ERROR 62connect (fail_con,localhost,test,zorro,); 63 64# check if old password version also works 65source include/switch_to_mysql_user.inc; 66update mysql.user set plugin="", authentication_string="", password=old_password("gambling2") where user=_binary"test"; 67flush privileges; 68show grants for test@localhost; 69update mysql.user set plugin='mysql_old_password' where user='test'; 70flush privileges; 71show grants for test@localhost; 72 73connect (con10,localhost,test,gambling2,); 74connect (con5,localhost,test,gambling2,mysql); 75set password=""; 76--error ER_PASSWD_LENGTH 77set password='gambling3'; 78set password=old_password('gambling3'); 79show tables; 80connect (con6,localhost,test,gambling3,test); 81show tables; 82 83connection default; 84disconnect con10; 85disconnect con5; 86disconnect con6; 87 88--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 89--error ER_ACCESS_DENIED_ERROR 90connect (fail_con,localhost,test,,test2); 91--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 92--error ER_ACCESS_DENIED_ERROR 93connect (fail_con,localhost,test,,); 94--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 95--error ER_ACCESS_DENIED_ERROR 96connect (fail_con,localhost,test,zorro,test2); 97--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 98--error ER_ACCESS_DENIED_ERROR 99connect (fail_con,localhost,test,zorro,); 100 101source include/switch_to_mysql_global_priv.inc; 102# remove user 'test' so that other tests which may use 'test' 103# do not depend on this test. 104delete from mysql.user where user=_binary"test"; 105 106# 107# Bug#12517 Clear user variables and replication events before 108# closing temp tables in thread cleanup. 109connect (con7,localhost,root,,test); 110let $connection_id= `select connection_id()`; 111create table t1 (id integer not null auto_increment primary key); 112create temporary table t2(id integer not null auto_increment primary key); 113set @id := 1; 114delete from t1 where id like @id; 115connection default; 116disconnect con7; 117# Wait till the session con7 is disconnected 118let $wait_condition = 119 SELECT COUNT(*) = 0 120 FROM information_schema.processlist 121 WHERE id = '$connection_id'; 122--source include/wait_condition.inc 123drop table t1; 124 125--echo # ------------------------------------------------------------------ 126--echo # -- End of 4.1 tests 127--echo # ------------------------------------------------------------------ 128 129########################################################################### 130 131--echo 132--echo # -- Bug#33507: Event scheduler creates more threads than max_connections 133--echo # -- which results in user lockout. 134 135--echo 136CREATE USER mysqltest_u1@localhost; 137GRANT USAGE ON *.* TO mysqltest_u1@localhost; 138 139# NOTE: if the test case fails sporadically due to spurious connections, 140# consider disabling all users. 141 142--echo 143let $saved_max_connections = `SELECT @@global.max_connections`; 144SET GLOBAL max_connections = 10; 145SET GLOBAL event_scheduler = ON; 146 147connect (tmp_con1,localhost,mysqltest_u1,,); 148connect (tmp_con2,localhost,mysqltest_u1,,); 149connect (tmp_con3,localhost,mysqltest_u1,,); 150connect (tmp_con4,localhost,mysqltest_u1,,); 151connect (tmp_con5,localhost,mysqltest_u1,,); 152connect (tmp_con6,localhost,mysqltest_u1,,); 153connect (tmp_con7,localhost,mysqltest_u1,,); 154connection default; 155 156--echo 157--echo # -- Waiting for Event Scheduler to start... 158let $wait_condition = 159 SELECT COUNT(*) = 1 160 FROM information_schema.processlist 161 WHERE user = 'event_scheduler'; 162--source include/wait_condition.inc 163 164--echo 165--echo # -- Disconnecting default connection... 166--disconnect default 167 168--echo 169--echo # -- Check that we allow exactly three user connections, no matter how 170--echo # -- many threads are running. 171 172--echo 173--echo # -- Connecting (1)... 174let $con_name = con_1; 175let $con_user_name = mysqltest_u1; 176--source include/connect2.inc 177 178--echo 179--echo # -- Connecting (2)... 180let $con_name = con_2; 181let $con_user_name = mysqltest_u1; 182--source include/connect2.inc 183 184--echo 185--echo # -- Connecting (3)... 186let $con_name = con_3; 187let $con_user_name = mysqltest_u1; 188--source include/connect2.inc 189 190--echo 191--echo # -- Connecting (4) [should fail]... 192let $con_name = con_4; 193let $con_user_name = mysqltest_u1; 194let $wait_timeout = 5; 195--source include/connect2.inc 196 197--echo 198--echo # -- Check that we allow one extra SUPER-user connection. 199 200--echo 201--echo # -- Connecting super (1)... 202let $con_name = con_super_1; 203let $con_user_name = root; 204--source include/connect2.inc 205 206--echo 207--echo # -- Connecting super (2) [should fail]... 208let $con_name = con_super_2; 209let $con_user_name = root; 210let $wait_timeout = 5; 211--source include/connect2.inc 212 213--echo 214--echo # -- Ensure that we have Event Scheduler thread, 3 ordinary user 215--echo # -- connections and one extra super-user connection. 216SELECT user FROM information_schema.processlist ORDER BY id; 217 218--echo 219--echo # -- Resetting variables... 220--replace_result $saved_max_connections #max_connections# 221--eval SET GLOBAL max_connections = $saved_max_connections 222 223--echo 224--echo # -- Stopping Event Scheduler... 225SET GLOBAL event_scheduler = OFF; 226 227--echo 228--echo # -- Waiting for Event Scheduler to stop... 229let $wait_condition = 230 SELECT COUNT(*) = 0 231 FROM information_schema.processlist 232 WHERE user = 'event_scheduler'; 233--source include/wait_condition.inc 234 235--echo 236--echo # -- That's it. Closing connections... 237--disconnect con_1 238--disconnect con_2 239--disconnect con_3 240--disconnect con_super_1 241--disconnect tmp_con1 242--disconnect tmp_con2 243--disconnect tmp_con3 244--disconnect tmp_con4 245--disconnect tmp_con5 246--disconnect tmp_con6 247--disconnect tmp_con7 248 249--echo 250--echo # -- Restoring default connection... 251--connect (default,localhost,root,,test) 252 253--echo 254--echo # -- Waiting for connections to close... 255let $count_sessions=1; 256--source include/wait_until_count_sessions.inc 257 258--echo 259DROP USER mysqltest_u1@localhost; 260 261--echo 262--echo # -- End of Bug#33507. 263--echo 264 265########################################################################### 266 267--echo # -- Bug#35074: max_used_connections is not correct. 268--echo 269 270FLUSH STATUS; 271 272--echo 273SHOW STATUS LIKE 'max_used_connections'; 274 275--echo 276--echo # -- Starting Event Scheduler... 277SET GLOBAL event_scheduler = ON; 278 279--echo # -- Waiting for Event Scheduler to start... 280--source include/running_event_scheduler.inc 281 282# NOTE: We should use a new connection here instead of reconnect in order to 283# avoid races (we can not for sure when the connection being disconnected is 284# actually disconnected on the server). 285 286--echo 287--echo # -- Opening a new connection to check max_used_connections... 288--connect (con_1,localhost,root) 289 290--echo 291--echo # -- Check that max_used_connections hasn't changed. 292SHOW STATUS LIKE 'max_used_connections'; 293 294--echo 295--echo # -- Closing new connection... 296--disconnect con_1 297--connection default 298 299--echo 300--echo # -- Stopping Event Scheduler... 301SET GLOBAL event_scheduler = OFF; 302 303--echo # -- Waiting for Event Scheduler to stop... 304--source include/no_running_events.inc 305 306--echo 307--echo # -- End of Bug#35074. 308--echo 309 310# Test connections to the extra port. 311 312connect(extracon,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,); 313SELECT 'Connection on extra port ok'; 314 315connect(extracon2,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,); 316SELECT 'Connection on extra port 2 ok'; 317 318--disable_abort_on_error 319--disable_result_log 320--disable_query_log 321connect(extracon3,127.0.0.1,root,,test,$MASTER_EXTRA_PORT,); 322--enable_query_log 323--enable_result_log 324--enable_abort_on_error 325let $error = $mysql_errno; 326if (!$error) 327{ 328 --echo # -- Error: managed to establish more than --extra-max-connections + 1 connections 329} 330if ($error) 331{ 332 --echo # -- Success: more than --extra-max-connections + 1 normal connections not possible 333} 334 335########################################################################### 336 337--echo # 338--echo # -- Bug#49752: 2469.126.2 unintentionally breaks authentication 339--echo # against MySQL 5.1 server 340--echo # 341 342GRANT ALL ON test.* TO 'O1234567890123456789012345678901234567890123456789012345678901234567890123456789'@'localhost' IDENTIFIED BY 'test123'; 343 344FLUSH PRIVILEGES; 345 346connect (con1,localhost,O1234567890123456789012345678901234567890123456789012345678901234567890123456789x,test123,test); 347disconnect con1; 348 349connection default; 350 351DROP USER 'O1234567890123456789012345678901234567890123456789012345678901234567890123456789'@'localhost'; 352 353FLUSH PRIVILEGES; 354 355--echo # 356--echo # -- End of Bug#49752 357--echo # 358 359--echo # ------------------------------------------------------------------ 360--echo # -- End of 5.1 tests 361--echo # ------------------------------------------------------------------ 362 363--disconnect extracon 364--disconnect extracon2 365--connection default 366 367# 368# A couple of plugin tests - for builtin plugins only 369# 370CREATE USER mysqltest_up1 IDENTIFIED VIA mysql_native_password using '*E8D46CE25265E545D225A8A6F1BAF642FEBEE5CB'; 371CREATE USER mysqltest_up2 IDENTIFIED VIA mysql_old_password using '09301740536db389'; 372 373--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 374--error ER_ACCESS_DENIED_ERROR 375connect(pcon1,localhost,mysqltest_up1,foo,,$MASTER_MYPORT,); 376connect(pcon2,localhost,mysqltest_up1,bar,,$MASTER_MYPORT,); 377select user(), current_user(); 378disconnect pcon2; 379 380--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 381--error ER_ACCESS_DENIED_ERROR 382connect(pcon3,localhost,mysqltest_up2,newpw,,$MASTER_MYPORT,); 383connect(pcon4,localhost,mysqltest_up2,oldpw,,$MASTER_MYPORT,); 384select user(), current_user(); 385disconnect pcon4; 386 387# 388# lp:683112 Maria 5.2 incorrectly reports "(using password: NO)" 389# even when password is specified 390# 391# test "access denied" error for nonexisting user with and without a password 392# 393--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 394--error ER_ACCESS_DENIED_ERROR 395connect(pcon5,localhost,mysqltest_nouser,newpw,,$MASTER_MYPORT,); 396--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT 397--error ER_ACCESS_DENIED_ERROR 398connect(pcon5,localhost,mysqltest_nouser,,,$MASTER_MYPORT,); 399 400connection default; 401 402# 403# MDEV-6253 MySQL Users Break when Migrating from MySQL 5.1 to MariaDB 10.0.10 404# 405# cannot connect when password is set and plugin=mysql_native_password 406# 407source include/switch_to_mysql_user.inc; 408update mysql.user set plugin='mysql_native_password' where user = 'mysqltest_up1'; 409update mysql.user set plugin='mysql_old_password' where user = 'mysqltest_up2'; 410select user, password, plugin, authentication_string from mysql.user 411 where user like 'mysqltest_up_'; 412flush privileges; 413 414connect(pcon6,localhost,mysqltest_up1,bar,,$MASTER_MYPORT,); 415select user(), current_user(); 416disconnect pcon6; 417 418connect(pcon7,localhost,mysqltest_up2,oldpw,,$MASTER_MYPORT,); 419select user(), current_user(); 420disconnect pcon7; 421connection default; 422 423source include/switch_to_mysql_global_priv.inc; 424 425DROP USER mysqltest_up1@'%'; 426DROP USER mysqltest_up2@'%'; 427 428# Wait till all disconnects are completed 429--source include/wait_until_count_sessions.inc 430 431--echo # 432--echo # BUG#1010351: New "via" keyword in 5.2+ can't be used as identifier anymore 433--echo # 434create table t1 (via int); 435alter table t1 add key(via); 436drop table t1; 437 438create table t1 (col1 int); 439alter table t1 add via int not null; 440drop table t1; 441 442delimiter |; 443create procedure p1(x int) 444foo: loop 445 if x = 0 then 446 leave foo; 447 end if; 448 select 'test'; 449 set x = x-1; 450end loop foo| 451 452delimiter ;| 453call p1(2); 454drop procedure p1; 455 456SET global secure_auth=default; 457 458--echo # 459--echo # MDEV-19282: Log more specific warning with log_warnings=2 if 460--echo # connection is aborted prior to authentication 461--echo # MDEV-19277: Add status variable that gets incremented if 462--echo # connection is aborted prior to authentication 463--echo # 464 465flush status; 466SHOW GLOBAL STATUS LIKE 'Aborted_connects%'; 467 468SET GLOBAL log_warnings=2; 469--let SEARCH_FILE=$MYSQLTEST_VARDIR/log/mysqld.1.err 470--let SEARCH_PATTERN= This connection closed normally without authentication 471--source include/search_pattern_in_file.inc 472 473--perl 474use Socket; 475socket(SOCK, PF_INET, SOCK_STREAM, getprotobyname("tcp")) 476 or die "Can't create a tcp socket!\n"; 477connect(SOCK, pack_sockaddr_in($ENV{MASTER_MYPORT}, inet_aton("localhost"))) 478 or die "Can't connect to port $ENV{MASTER_MYPORT}!\n"; 479exit(0); 480EOF 481--echo # let tcp to detect disconnect 482select sleep(1); 483 484SHOW GLOBAL STATUS LIKE 'Aborted_connects%'; 485 486--let SEARCH_PATTERN= This connection closed normally without authentication 487--source include/search_pattern_in_file.inc 488 489SET @save_connect_timeout= @@connect_timeout; 490SET GLOBAL connect_timeout=2; 491 492--perl 493use Socket; 494socket(SOCK, PF_INET, SOCK_STREAM, getprotobyname("tcp")) 495 or die "Can't create a tcp socket!\n"; 496connect(SOCK, pack_sockaddr_in($ENV{MASTER_MYPORT}, inet_aton("localhost"))) 497 or die "Can't connect to port $ENV{MASTER_MYPORT}!\n"; 498sleep 3; 499exit(0); 500EOF 501 502SHOW GLOBAL STATUS LIKE 'Aborted_connects%'; 503 504--let SEARCH_PATTERN= This connection closed normally without authentication 505--source include/search_pattern_in_file.inc 506 507SET GLOBAL log_warnings=default; 508SET GLOBAL connect_timeout= @save_connect_timeout; 509 510--echo # End of 10.4 tests 511