MySQL – Cannot Run CHANGE MASTER TO Inside MySQL

MySQLmysql-5.7replicationstored-procedures

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.

Best Answer

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 ;
Related Question