#
# === Purpose ===
# This test script verifies that in a replication environment,
# the slave correctly parses the ALTER USER or SET PASSWORD query. 
#
# === Bug#20228478: ON REPLICATION SLAVE, ALTER USER FAILING FOR USER
#                   WITH SHA256_PASSWORD PLUGIN
# It verifies that the slave doesn't encounter an error in parsing a hash
# string which contains a single quote. The fix ensures escaping the hash
# string correctly depending on the server mode. Since this issue is sporadic,
# we have used a debug flag to deterministically generate a hash string which
# contains a single quote.
#
# === WL#11544: Current password required for SET PASSWORD
# It verifies that binlog gets the "PASSWORD REQUIRE CURRENT" only if they are
# explicitly specified in the ALTER USER statement. It also verifies that
# REPLACE clause is never replicated to the binlog

--source include/not_have_privilege_checks_user.inc
--source include/master-slave.inc
--source include/have_debug.inc

#Scenario 1:
# When NO_BACKSLASH_ESCAPES mode is not set on both master and the slave.
SET @old_sql_mode_master= @@session.sql_mode;
SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));

--connection slave
SET @old_sql_mode_slave= @@session.sql_mode;
SET @@session.sql_mode= (select replace(@@session.sql_mode,'NO_BACKSLASH_ESCAPES',''));

--connection master
SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
CREATE USER 'user1'@'localhost' IDENTIFIED WITH sha256_password BY 'auth_string1';
# Save master position
--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
# Check the binlog contents on the master
--let $binlog_start= $saved_master_pos
--let $binlog_limit= 2
if (`SELECT @@session.binlog_transaction_compression = TRUE`)
{
  --let $binlog_limit= 3
}
--source include/show_binlog_events.inc
# Check the binlog contents on the slave
--source include/sync_slave_sql_with_master.inc
# slave's GTID log events are 7 bytes larger than the same events on master
--let $binlog_start= `SELECT $saved_master_pos + 7`
--source include/show_binlog_events.inc
# Reset the flag
--connection master
SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';

# Scenario 2:
# Make sure that we are successfully able to login to the slave from a
# user whose password is changed and NO_BACKSLASH_ESCAPES mode is not
# set on both master and slave. The hash string generated here may or
# may not contain a single quote within it.
--connection master
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret1';
--source include/sync_slave_sql_with_master.inc
--connect(con1, localhost, user1,'auth_secret1',,,,SSL)
SELECT USER();
disconnect con1;

# Scenario 3:
# When NO_BACKSLASH_ESCAPES mode is set on both master and the slave.
--connection master
SET GLOBAL DEBUG= '+d,force_hash_string_with_quote';
SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
--connection slave
SET @@session.sql_mode= 'NO_BACKSLASH_ESCAPES';
--connection master
# Save master position
--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret';
# Check the binlog contents on the master
--let $binlog_start= $saved_master_pos
--let $binlog_limit= 2
if (`SELECT @@session.binlog_transaction_compression = TRUE`)
{
  --let $binlog_limit= 3
}
--source include/show_binlog_events.inc
# Check the binlog contents on the slave
--source include/sync_slave_sql_with_master.inc
# slave's GTID log events are 7 bytes larger than the same events on master
--let $binlog_start= `SELECT $saved_master_pos + 21`
--source include/show_binlog_events.inc
# Reset the flag
--connection master
SET GLOBAL DEBUG= '-d,force_hash_string_with_quote';

# Scenario 4:
# Make sure that we are successfully able to login to the slave
# after the password is changed for a user and NO_BACKSLASH_ESCAPES
# mode is set on both master and slave. This scenario will generate
# the hash string which may or may not contain a single quote.
SET PASSWORD FOR 'user1'@'localhost' = 'auth_secret2';
--source include/sync_slave_sql_with_master.inc
--connect(con1, localhost, user1,'auth_secret2',,,,SSL)
SELECT USER();
disconnect con1;

--echo #
--echo # WL#11544: Current password required for SET PASSWORD
--echo # Create users with current password require clauses.
--echo #

--connection slave
--let $saved_master_relay_pos= query_get_value('SHOW MASTER STATUS', Position, 1)

--connection master
--echo [connection master]
# Save master position
--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
CREATE USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;
ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;
ALTER USER '11544_u1'@'localhost' PASSWORD REQUIRE CURRENT;
SET PASSWORD FOR '11544_u1'@'localhost'='haha';
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe';

connect(11544_u1_con_master, localhost, 11544_u1, hehe,,,,SSL);
connection 11544_u1_con_master;
--echo #
--echo # To check that failed statements are not replicated to binlog
--echo #
--error ER_MISSING_CURRENT_PASSWORD
SET PASSWORD ='ahaha';
--error ER_MISSING_CURRENT_PASSWORD
SET PASSWORD FOR '11544_u1'@'localhost'='ahaha';
--error ER_MISSING_CURRENT_PASSWORD
ALTER USER user() IDENTIFIED BY 'ahaha';
--error ER_MISSING_CURRENT_PASSWORD
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha';
--error ER_INCORRECT_CURRENT_PASSWORD
SET PASSWORD='ahaha' REPLACE 'xyz';
--error ER_INCORRECT_CURRENT_PASSWORD
SET PASSWORD FOR '11544_u1'@'localhost'='ahaha' REPLACE 'xyz';
--error ER_INCORRECT_CURRENT_PASSWORD
ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'xyz';
--error ER_INCORRECT_CURRENT_PASSWORD
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'ahaha' REPLACE 'xyz';
--echo #
--echo # To check that REPLACE clause is not reflected in the binlog
--echo #
SET PASSWORD='ahaha' REPLACE 'hehe';
SET PASSWORD FOR '11544_u1'@'localhost'='hehe' REPLACE 'ahaha';
ALTER USER user() IDENTIFIED BY 'ahaha' REPLACE 'hehe';
ALTER USER '11544_u1'@'localhost' IDENTIFIED BY 'hehe' REPLACE 'ahaha';
--connection master
disconnect 11544_u1_con_master;

--echo # Check the binlog contents on the master
--let $binlog_start= $saved_master_pos
--let $binlog_limit= 100
--let $mask_user_password_events=1
--source include/show_binlog_events.inc
--let $mask_user_password_events=0

--echo # Check the binlog contents on the slave
--source include/sync_slave_sql_with_master.inc
--echo [connection slave]
--let $binlog_start= $saved_master_relay_pos
--let $binlog_limit= 100
--let $mask_user_password_events=1
--source include/show_binlog_events.inc
--let $mask_user_password_events=0

--echo # Check that we are successfully able to login to the slave from
--echo # the user whose password is changed on master
connect(11544_u1_con_slave, localhost, 11544_u1, hehe,,,,SSL);
SELECT USER();
--connection master
--echo [connection master]
DROP USER '11544_u1'@'localhost';


#
# WL11772 CREATE USER WITH RANDOM PASSWORD
#
#
--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)

--connection master
--echo * Create a new user with random password
--let $sql= "CREATE USER u1@localhost IDENTIFIED BY RANDOM PASSWORD"
--let $passwd= query_get_value( $sql, "generated password", 1)
--source include/sync_slave_sql_with_master.inc
--connect(slave_con1, localhost, u1, $passwd, test, $SLAVE_MYPORT,,SSL)
--echo [connection slave]
SELECT CURRENT_USER();
--connection master
--echo [connection master]
--disconnect slave_con1
--echo * Set random password using SET PASSWORD
--let $sql= "SET PASSWORD FOR u1@localhost TO RANDOM"
--let $passwd= query_get_value($sql, "generated password", 1)
--source include/sync_slave_sql_with_master.inc
--connect(slave_con1, localhost, u1, $passwd, test, $SLAVE_MYPORT,,SSL)
--echo [connection slave]
SELECT CURRENT_USER();
--connection master
--echo [connection master]
--disconnect slave_con1
--echo * Set random password using ALTER USER
--let $sql= "ALTER USER u1@localhost IDENTIFIED BY RANDOM PASSWORD"
--let $passwd= query_get_value($sql, "generated password", 1)
--source include/sync_slave_sql_with_master.inc
--connect(slave_con1, localhost, u1, $passwd, test, $SLAVE_MYPORT,, SSL)
--echo [connection slave]
SELECT CURRENT_USER();
--connection master
--echo [connection master]
--disconnect slave_con1
DROP USER u1@localhost;

--echo * Check the binlog contents on the master
--let $binlog_start= $saved_master_pos
--let $binlog_limit= 100
--let $mask_user_password_events=1
--source include/show_binlog_events.inc
--let $mask_user_password_events=0


#
# WL13562 ARBITRARY JSON ATTRIBUTES
#
--let $saved_master_pos= query_get_value('SHOW MASTER STATUS', Position, 1)
--connection master
--echo * Create a new user with an arbitrary attribute
CREATE USER u1@localhost IDENTIFIED BY 'foo' ATTRIBUTE '{"trackingId": "12345"}';
CREATE USER u2@localhost COMMENT 'This is account is used by my private LAMP project';
--source include/sync_slave_sql_with_master.inc
--connect(slave_con1, localhost, root,, test, $SLAVE_MYPORT,,SSL)
--echo [connection slave]
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1' OR USER = 'u2';
--connection master
--echo [connection master]
--disconnect slave_con1
ALTER USER u1@localhost COMMENT 'TODO: Delete this user';
--source include/sync_slave_sql_with_master.inc
--connect(slave_con1, localhost, root,, test, $SLAVE_MYPORT,,SSL)
--echo [connection slave]
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u1';
--let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.comment") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u1'`
--let $assert_text = User JSON comment updated successfully
--let $assert_cond = $comment = "TODO: Delete this user"
--source include/assert.inc
--let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.trackingId") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u1'`
--let $assert_text = User JSON attribute updated successfully
--let $assert_cond = $comment = "12345"
--source include/assert.inc

--connection master
--echo [connection master]
--disconnect slave_con1
ALTER USER u2@localhost ATTRIBUTE '{ "code": "red" }';
ALTER USER u2@localhost COMMENT 'Change of mind';
--source include/sync_slave_sql_with_master.inc
--connect(slave_con1, localhost, root,, test, $SLAVE_MYPORT,, SSL)
--echo [connection slave]
SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER = 'u2';
--let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.comment") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u2'`
--let $assert_text = User JSON comment updated successfully
--let $assert_cond = $comment = "Change of mind"
--source include/assert.inc

--let $comment = `SELECT JSON_EXTRACT(ATTRIBUTE, "$.code") FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE USER= 'u2'`
--let $assert_text = User JSON attribute updated successfully
--let $assert_cond = $comment = "red"
--source include/assert.inc

--connection master
--echo [connection master]
--disconnect slave_con1
DROP USER u1@localhost;
DROP USER u2@localhost;

--echo * Check the binlog contents on the master
--let $binlog_start= $saved_master_pos
--let $binlog_limit= 100
--let $mask_user_password_events=1
--source include/show_binlog_events.inc
--let $mask_user_password_events=0

#Restore the old sql_mode at the master and the slave
--connection slave
SET @@session.sql_mode= @old_sql_mode_slave;
--connection master
SET @@session.sql_mode= @old_sql_mode_master;

#cleanup
DROP USER 'user1'@'localhost';
#--let $rpl_debug= 1
--source include/rpl_end.inc