

============================================================================================================
Checking the global and session value of old_passwords when server default plugin is mysql_native_password.
=============================================================================================================

select @@Global.old_passwords;
@@Global.old_passwords
0
0 Expected
select @@session.old_passwords;
@@session.old_passwords
0
0 Expected
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
set @@session.old_passwords=2;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
set @@session.old_passwords=1;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
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
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
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
set @@global.secure_auth=0;
set @@global.secure_auth=0;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
1
0 for GLOBAL and 1 for SESSION Expected
set @@global.secure_auth=1;
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password';
set @@session.old_passwords=1;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
set @@session.old_passwords=2;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
set @@session.old_passwords=0;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
set @@global.secure_auth=0;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
set @@global.secure_auth=1;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
0
0 for GLOBAL and 0 for SESSION Expected
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
set @@session.old_passwords=1;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
set @@session.old_passwords=0;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
set @@session.old_passwords=2;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
set @@global.secure_auth=0;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
set @@global.secure_auth=1;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
0
@@session.old_passwords
2
0 for GLOBAL and 2 for SESSION Expected
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';


============================================================================================================
Checking the global and session value of old_passwords when server default plugin is sha256_password.
=============================================================================================================

# Restart server with default-authentication-plugin=sha256_password;
select @@Global.old_passwords;
@@Global.old_passwords
2
2 Expected
select @@session.old_passwords;
@@session.old_passwords
0
0 Expected
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_old_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_old_password';
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
set @@session.old_passwords=2;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 16-digit hexadecimal number
set @@session.old_passwords=1;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
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
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
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
set @@global.secure_auth=0;
set @@global.secure_auth=0;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
1
2 for GLOBAL and 1 for SESSION Expected
set @@global.secure_auth=1;
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
**** connecting Tanjotuser1 with server
**** connecting Tanjotuser2 with server
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'mysql_native_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'mysql_native_password';
set @@session.old_passwords=1;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
set @@session.old_passwords=2;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: Password hash should be a 41-digit hexadecimal number
set @@session.old_passwords=0;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
set @@global.secure_auth=0;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
set @@global.secure_auth=1;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
0
2 for GLOBAL and 0 for SESSION Expected
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
CREATE USER 'Tanjotuser1'@'localhost' IDENTIFIED WITH 'sha256_password';
CREATE USER 'Tanjotuser2'@'127.0.0.1' IDENTIFIED WITH 'sha256_password';
set @@session.old_passwords=1;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
set @@session.old_passwords=0;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
ERROR HY000: The password hash doesn't have the expected format. Check if the correct password algorithm is being used with the PASSWORD() function.
set @@session.old_passwords=2;
SET PASSWORD FOR 'Tanjotuser1'@'localhost'=PASSWORD('abc');
SET PASSWORD FOR 'Tanjotuser2'@'127.0.0.1'=PASSWORD('abc');
set @@global.secure_auth=0;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
set @@global.secure_auth=1;
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser1 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
**** connecting Tanjotuser2 with server 
@@Global.old_passwords
2
@@session.old_passwords
2
2 for GLOBAL and 2 for SESSION Expected
DROP USER 'Tanjotuser1'@'localhost';
DROP USER 'Tanjotuser2'@'127.0.0.1';
set @@global.old_passwords=default;
set @@session.old_passwords=default;
set @@global.secure_auth=default;
