183 lines
6.9 KiB
Plaintext
183 lines
6.9 KiB
Plaintext
#
|
|
# === 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/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
|
|
--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
|
|
--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';
|
|
|
|
#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
|
|
|
|
--connection master
|
|
--source suite/xengine/include/check_xengine_log_error.inc
|