Edit File: connect.result
drop table if exists t1,t2; show tables; Tables_in_mysql columns_priv db event func general_log help_category help_keyword help_relation help_topic innodb_index_stats innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers slave_master_info slave_relay_log_info slave_worker_info slow_log tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user show tables; Tables_in_test connect(localhost,root,z,test2,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES) connect(localhost,root,z,test,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'root'@'localhost' (using password: YES) grant ALL on *.* to test@localhost identified by "gambling"; grant ALL on *.* to test@127.0.0.1 identified by "gambling"; show tables; Tables_in_mysql columns_priv db event func general_log help_category help_keyword help_relation help_topic innodb_index_stats innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers slave_master_info slave_relay_log_info slave_worker_info slow_log tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user show tables; Tables_in_test connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) connect(localhost,test,,"",MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES) connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES) SET GLOBAL secure_auth = OFF; Warnings: Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead update mysql.user set password=old_password("gambling2"), plugin="mysql_old_password" where user=_binary"test"; Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead flush privileges; set password=""; set password='gambling3'; ERROR HY000: Password hash should be a 16-digit hexadecimal number set password=old_password('gambling3'); Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead show tables; Tables_in_mysql columns_priv db event func general_log help_category help_keyword help_relation help_topic innodb_index_stats innodb_table_stats ndb_binlog_index plugin proc procs_priv proxies_priv servers slave_master_info slave_relay_log_info slave_worker_info slow_log tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user show tables; Tables_in_test connect(localhost,test,,test2,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) connect(localhost,test,,test,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: NO) connect(localhost,test,zorro,test2,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES) connect(localhost,test,zorro,test,MASTER_PORT,MASTER_SOCKET); ERROR 28000: Access denied for user 'test'@'localhost' (using password: YES) delete from mysql.user where user=_binary"test"; flush privileges; SET GLOBAL secure_auth = DEFAULT; create table t1 (id integer not null auto_increment primary key); create temporary table t2(id integer not null auto_increment primary key); set @id := 1; delete from t1 where id like @id; drop table t1; # ------------------------------------------------------------------ # -- End of 4.1 tests # ------------------------------------------------------------------ # -- Bug#33507: Event scheduler creates more threads than max_connections # -- which results in user lockout. GRANT USAGE ON *.* TO mysqltest_u1@localhost; SET GLOBAL max_connections = 3; SET GLOBAL event_scheduler = ON; # -- Waiting for Event Scheduler to start... # -- Disconnecting default connection... # -- Check that we allow exactly three user connections, no matter how # -- many threads are running. # -- Connecting (1)... # -- Establishing connection 'con_1' (user: mysqltest_u1)... # -- Connection 'con_1' has been established. # -- Connecting (2)... # -- Establishing connection 'con_2' (user: mysqltest_u1)... # -- Connection 'con_2' has been established. # -- Connecting (3)... # -- Establishing connection 'con_3' (user: mysqltest_u1)... # -- Connection 'con_3' has been established. # -- Connecting (4) [should fail]... # -- Establishing connection 'con_4' (user: mysqltest_u1)... # -- Error: can not establish connection 'con_4'. # -- Check that we allow one extra SUPER-user connection. # -- Connecting super (1)... # -- Establishing connection 'con_super_1' (user: root)... # -- Connection 'con_super_1' has been established. # -- Connecting super (2) [should fail]... # -- Establishing connection 'con_super_2' (user: root)... # -- Error: can not establish connection 'con_super_2'. # -- Ensure that we have Event Scheduler thread, 3 ordinary user # -- connections and one extra super-user connection. SELECT user FROM information_schema.processlist ORDER BY id; user event_scheduler mysqltest_u1 mysqltest_u1 mysqltest_u1 root # -- Resetting variables... SET GLOBAL max_connections = 151; # -- Stopping Event Scheduler... SET GLOBAL event_scheduler = OFF; # -- Waiting for Event Scheduler to stop... # -- That's it. Closing connections... # -- Restoring default connection... # -- Waiting for connections to close... DROP USER mysqltest_u1@localhost; # -- End of Bug#33507. # -- Bug#35074: max_used_connections is not correct. FLUSH STATUS; SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 1 # -- Starting Event Scheduler... SET GLOBAL event_scheduler = ON; # -- Waiting for Event Scheduler to start... # -- Opening a new connection to check max_used_connections... # -- Check that max_used_connections hasn't changed. SHOW STATUS LIKE 'max_used_connections'; Variable_name Value Max_used_connections 2 # -- Closing new connection... # -- Stopping Event Scheduler... SET GLOBAL event_scheduler = OFF; # -- Waiting for Event Scheduler to stop... # -- End of Bug#35074. # ------------------------------------------------------------------ # -- End of 5.1 tests # ------------------------------------------------------------------ # # Bug #13586336 : SRVGEN - HIBISCUS: ISSUE DEPRECATION WARNING FOR # LEGACY AUTHENTICATION METHOD # #Expect 1 SELECT @@global.secure_auth; @@global.secure_auth 1 SET old_passwords=1; #Expect deprecation warning CREATE USER old_pwd@localhost IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD FOR old_pwd@localhost= OLD_PASSWORD('aha'); Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead #Expect deprecation warning SET PASSWORD FOR old_pwd@localhost = OLD_PASSWORD('aha2'); Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead #Expect deprecation warning GRANT SELECT on test.* TO old_gpwd@localhost IDENTIFIED BY 'aha3'; ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function. #Expect deprecation warning GRANT SELECT on test.* TO old_gpwd@localhost IDENTIFIED WITH 'mysql_old_password'; SET PASSWORD FOR old_gpwd@localhost= OLD_PASSWORD('aha4'); Warnings: Warning 1287 'OLD_PASSWORD' is deprecated and will be removed in a future release. Please use PASSWORD instead Warning 1287 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead #Expected normal flush, no warnings FLUSH PRIVILEGES; DROP USER old_pwd@localhost, old_gpwd@localhost; SET old_passwords=default; # # WL#2392: Change Password at next login # CREATE USER must_change@localhost IDENTIFIED BY 'aha'; SELECT password_expired FROM mysql.user WHERE user='must_change' and host = 'localhost'; password_expired N SELECT USER(); USER() must_change@localhost CREATE TABLE t1 (A INT); CREATE PROCEDURE TEST_t1(new_a INT) INSERT INTO t1 VALUES (new_a); CREATE FUNCTION last_t1() RETURNS INT RETURN (SELECT MAX(A) FROM t1); # Initialize the table CALL test_t1(1); UPDATE mysql.user SET password_expired='Y' WHERE user='must_change' and host = 'localhost'; # without FLUSH the field has no effect # must not throw an error SELECT USER(); USER() must_change@localhost # must not throw an error SELECT last_t1(); last_t1() 1 # must not throw an error CALL test_t1(last_t1() + 1); FLUSH PRIVILEGES; # existing connections continue as before even after flush # must not throw an error SELECT USER(); USER() must_change@localhost # must not throw an error SELECT last_t1(); last_t1() 2 # must not throw an error CALL test_t1(last_t1() + 1); # new connections are blocked until a password reset SELECT USER(); ERROR HY000: You must SET PASSWORD before executing this statement SELECT last_t1(); ERROR HY000: You must SET PASSWORD before executing this statement CALL test_t1(last_t1() + 1); ERROR HY000: You must SET PASSWORD before executing this statement # setting a password unlocks it SET PASSWORD = PASSWORD('aha2'); # must not throw an error SELECT USER(); USER() must_change@localhost # must not throw an error SELECT last_t1(); last_t1() 3 # must not throw an error CALL test_t1(last_t1() + 1); # check if SET PASSWORD resets the column SELECT password_expired FROM mysql.user WHERE user='must_change' and host = 'localhost'; password_expired N UPDATE mysql.user SET password_expired='Y' WHERE user='must_change' and host = 'localhost'; FLUSH PRIVILEGES; SELECT USER(); ERROR HY000: You must SET PASSWORD before executing this statement SELECT last_t1(); ERROR HY000: You must SET PASSWORD before executing this statement CALL test_t1(last_t1() + 1); ERROR HY000: You must SET PASSWORD before executing this statement # setting a password with a user name is ok if it exactly matches the # authenticated user. SET PASSWORD FOR must_change@localhost = PASSWORD('aha3'); SELECT USER(); USER() must_change@localhost SELECT last_t1(); last_t1() 4 CALL test_t1(last_t1() + 1); # setting a password for the current user works SET PASSWORD FOR CURRENT_USER() = PASSWORD('aha3'); SELECT USER(); USER() must_change@localhost SELECT last_t1(); last_t1() 5 CALL test_t1(last_t1() + 1); # testing the ALTER USER command # try a single user ALTER USER must_change@localhost PASSWORD EXPIRE; SELECT password_expired FROM mysql.user WHERE user='must_change' and host = 'localhost'; password_expired Y SELECT USER(); ERROR HY000: You must SET PASSWORD before executing this statement SET PASSWORD = PASSWORD('aha4'); # try a valid+invalid user combo ALTER USER invalid_user@localhost PASSWORD EXPIRE, must_change@localhost PASSWORD EXPIRE; ERROR HY000: Operation ALTER USER failed for 'invalid_user'@'localhost' SELECT password_expired FROM mysql.user WHERE user='must_change' and host = 'localhost'; password_expired Y SELECT USER(); ERROR HY000: You must SET PASSWORD before executing this statement SET PASSWORD = PASSWORD('aha5'); SELECT USER(); USER() must_change@localhost # Password change must be persistent when reconnecting SELECT USER(); USER() must_change@localhost # cleanup DROP PROCEDURE test_t1; DROP FUNCTION last_t1; DROP TABLE t1; DROP USER must_change@localhost; # # WL#6587: Protocol support for password expiration # CREATE USER wl6587@localhost IDENTIFIED BY 'wl6587'; ALTER USER wl6587@localhost PASSWORD EXPIRE; # non-interactive mysql should fail Warning: Using a password on the command line interface can be insecure. ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords. # mysqladmin non-password should fail Warning: Using a password on the command line interface can be insecure. mysqladmin: connect to server at 'localhost' failed error: 'Your password has expired. To log in you must change it using a client that supports expired passwords.' # mysqladmin password should work Warning: Using a password on the command line interface can be insecure. DROP USER wl6587@localhost; # ------------------------------------------------------------------ # -- End of 5.6 tests # ------------------------------------------------------------------