Bug #76274 mysqlrpladmin switchover breaks replication if password is omitted for rpl-user
Submitted: 12 Mar 2015 5:09 Modified: 12 Jan 2017 6:16
Reporter: monty solomon Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Utilities Severity:S2 (Serious)
Version:1.5.3 OS:Linux
Assigned to: Nuno Mariz CPU Architecture:Any

[12 Mar 2015 5:09] monty solomon
Description:
mysqlrpladmin switchover breaks replication if the password is omitted for the rpl-user and the replication user does not have host-specific grants.

$ mysqlrpladmin --verbose --master=testbeaverlake --new-master=testslab switchover --demote-master --slaves=testslab,testslab2,testslab3,testslab4,testslab6 --rpl-user=Repl
# Checking privileges.
# WARNING: Errant transactions check skipped (GTID not enabled for the whole topology).
# Performing switchover from master at testbeaverlake:3306 to slave at testslab:3306.
# Checking candidate slave prerequisites.
# Checking eligibility of slave testslab:3306 for candidate.
#   Slave connected to master ... Ok
#   Slave not behind master ... Ok
#   Logging filters agree ... Ok
#   Binary logging turned on ... Ok
#   Replication user exists ... Ok
# Checking slaves configuration to master.
# When the master_info_repository variable is set to FILE, the --rpl-user option may be used only if the user specified matches what is shown in the SLAVE STATUS output unless the --force option is used.
# Creating replication user if it does not exist.
# Blocking writes on master.
# LOCK STRING: FLUSH TABLES WITH READ LOCK
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
#   Executing stop on slave testslab:3306 Ok
#   Executing stop on slave testslab2:3306 Ok
#   Executing stop on slave testslab3:3306 Ok
#   Executing stop on slave testslab4:3306 Ok
#   Executing stop on slave testslab6:3306 Ok
# UNLOCK STRING: UNLOCK TABLES
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Executing CHANGE MASTER on testslab2:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testslab3:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testslab4:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testslab6:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Executing CHANGE MASTER on testbeaverlake:3306.
# CHANGE MASTER TO MASTER_HOST = 'testslab', MASTER_USER = 'Repl', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'bin.002753', MASTER_LOG_POS = 298995
# Starting all slaves.
# Performing START on all slaves.
#   Executing start on slave testslab2:3306 Ok
#   Executing start on slave testslab3:3306 Ok
#   Executing start on slave testslab4:3306 Ok
#   Executing start on slave testslab6:3306 Ok
#   Executing start on slave testbeaverlake:3306 Ok
# Checking slaves for errors.
# testslab2:3306 status: Ok 
# testslab3:3306 status: Ok 
# testslab4:3306 status: Ok 
# testslab6:3306 status: Ok 
# testbeaverlake:3306 status: Ok 
# Switchover complete.
# Attempting to contact testslab ... Success
# Attempting to contact testslab2 ... Success
# Attempting to contact testslab3 ... Success
# Attempting to contact testslab4 ... Success
# Attempting to contact testslab6 ... Success
# Attempting to contact testbeaverlake ... Success
#
# Replication Topology Health:
+-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+
| host                  | port  | role    | state  | gtid_mode  | health                                                                                                              | version          | master_log_file  | master_log_pos  | IO_Thread   | SQL_Thread  | Secs_Behind  | Remaining_Delay  | IO_Error_Num  | IO_Error                                                                                 | SQL_Error_Num  | SQL_Error  | Trans_Behind  |
+-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+
| testslab        | 3306  | MASTER  | UP     | OFF        | OK                                                                                                                  | 5.6.21-70.1-log  | bin.002753       | 298995          |             |             |              |                  |               |                                                                                          |                |            |               |
| testbeaverlake  | 3306  | SLAVE   | UP     | OFF        | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 60  retries: 1       | 5.6.23-log       | bin.002753       | 298995          | Connecting  | Yes         | 0            | No               | 1045          | error connecting to master 'Repl@testslab:3306' - retry-time: 60  retries: 1       | 0              |            |               |
| testslab2       | 3306  | SLAVE   | UP     | OFF        | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 5.6.21-70.1-log  | bin.002753       | 298995          | Connecting  | Yes         | 0            | No               | 1045          | error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 0              |            |               |
| testslab3       | 3306  | SLAVE   | UP     | OFF        | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 5.6.21-70.1-log  | bin.002753       | 298995          | Connecting  | Yes         | 0            | No               | 1045          | error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 0              |            |               |
| testslab4       | 3306  | SLAVE   | UP     | OFF        | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 5.6.21-70.1-log  | bin.002753       | 298995          | Connecting  | Yes         | 0            | No               | 1045          | error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 0              |            |               |
| testslab6       | 3306  | SLAVE   | UP     | OFF        | IO thread is not running., error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 5.6.22-71.0-log  | bin.002753       | 298995          | Connecting  | Yes         | 0            | No               | 1045          | error connecting to master 'Repl@testslab:3306' - retry-time: 15  retries: 2       | 0              |            |               |
+-----------------------+-------+---------+--------+------------+---------------------------------------------------------------------------------------------------------------------+------------------+------------------+-----------------+-------------+-------------+--------------+------------------+---------------+------------------------------------------------------------------------------------------+----------------+------------+---------------+
# ...done.

How to repeat:
Create a set of replication servers using the replication user Repl@%

GRANT REPLICATION SLAVE ON *.* TO Repl IDENTIFIED BY 'somepassword';

CHANGE MASTER TO MASTER_HOST = 'testbeaverlake', MASTER_USER = 'Repl', MASTER_PASSWORD = 'somepassword', MASTER_LOG_FILE='bin.000004', MASTER_LOG_POS=0, MASTER_CONNECT_RETRY=15, MASTER_HEARTBEAT_PERIOD=1 ; 

Add a rpladmin user to the cluster

GRANT SUPER, GRANT OPTION, SELECT, INSERT, RELOAD, DROP, CREATE, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to rpladmin IDENTIFIED BY  'someotherpassword';

mysql_config_editor set --login-path=testbeaverlake --host=testbeaverlake --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab --host=testslab --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab1 --host=testslab1 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab2 --host=testslab2 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab3 --host=testslab3 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab4 --host=testslab4 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab5 --host=testslab5 --user=rpladmin --password --port=3306
mysql_config_editor set --login-path=testslab6 --host=testslab6 --user=rpladmin --password --port=3306

Execute a command to switch the master to one of the slaves.

mysqlrpladmin --verbose --master=testbeaverlake --new-master=testslab switchover --demote-master --slaves=testslab,testslab2,testslab3,testslab4,testslab6 --rpl-user=Repl

Suggested fix:
Do not set passwd_hash to the empty string when SELECT_RPL_USER_PASS_QUERY fails to find a match since SELECT PASSWORD('') returns the empty string which then matches the empty passwd_hash
[10 Apr 2015 22:03] monty solomon
When will someone review this bug?
[5 Sep 2015 20:54] monty solomon
What is the status of this bug?
[12 Jan 2017 6:16] monty solomon
This bug may be related to Bug#84321 and the calling of the wrong get_rpl_users() method.