MySQL: cannot run change master to inside mysql

Question

I am trying to create a stored procedure that can run change master to command.

This is what I was able to come up with.

DELIMITER //
CREATE PROCEDURE changeMasterTo(IN ip char(20),IN port int,IN user 
char(30),IN password char(30))
LANGUAGE SQL
COMMENT 'changes master to'
ThisStoredProcedure:BEGIN
CHANGE MASTER TO MASTER_HOST=ip, 
MASTER_PORT=port,
MASTER_USER=user,
MASTER_PASSWORD=password,
MASTER_AUTO_POSITION=1,
MASTER_SSL=1;
END;
//

But it fails with the following error.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

'ip, MASTER_PORT=port, MASTER_USER=user, MASTER_PASSWORD=password, MASTER_AUTO_P'

at line 5

I am not sure what the issue is, any help will be appreciated.

Answer 1

The host, user, and password values are supposed to be enclosed in single quotes.

You are also better off executing it as Dynamic SQL

DELIMITER //
DROP PROCEDURE IF EXISTS changeMasterTo //
CREATE PROCEDUREchangeMasterTo(IN ip char(20),IN port int,IN user 
char(30),IN password char(30))
LANGUAGE SQL
COMMENT 'changes master to'
ThisStoredProcedure:BEGIN
    SET @sql = 'STOP SLAVE';
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

    SET @sql = CONCAT('CHANGE MASTER TO MASTER_HOST=',QUOTE(ip)); 
    SET @sql = CONCAT(@sql,',MASTER_PORT=',port);
    SET @sql = CONCAT(@sql,',MASTER_USER=',QUOTE(user));
    SET @sql = CONCAT(@sql,',MASTER_PASSWORD=',QUOTE(password));
    SET @sql = CONCAT(@sql,',MASTER_AUTO_POSITION=1,MASTER_SSL=1');
    PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
END;
//
DELIMITER ;