2

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.

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 ;
  • Yes. That works – nwarriorch Aug 23 '17 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.