On MySQL Cluster Replication

Erik Hoekstra, from Daisycon, has pointed out this problem related to Replication in general and with a specific example on MySQL Cluster and Replication in 5.1.

In the manual for 5.1 there's an entry about scripting the failover for MySQL Cluster Replication.

In this part of the manual they speak about fetching the needed variables, like the filename and position, and place them into the CHANGE MASTER TO statement.

The example is here:

CHANGE MASTER TO
MASTER_LOG_FILE='@file',
MASTER_LOG_POS=@pos;

I'm now trying to do the following:
On a slave I've created a Federated tables, 1 pointing to the current master, and 1
to the stand-in master, should the current master fail.

Federated table 1, let's say F1, is just a simple table on the current master with only one record.

Federated table 2, let's say F2, is the ndb_binlog_index table in the mysql database on the stand-in master.

I wrote a procedure which checks if F1 exists and if not (current master down) it selects the needed vars from F2. I would like to place these variables into a CHANGE MASTER TO statement so the slave can start and pickup at the right epoch, but it seems that the CHANGE MASTER TO statement doesn't accept variables.

DELIMITER //

CREATE PROCEDURE check_master()

BEGIN
  DECLARE master_dead BOOLEAN DEFAULT false;
  DECLARE latest BIGINT(20) DEFAULT 0;
  DECLARE fil VARCHAR(255);
  DECLARE pos BIGINT(20) DEFAULT 0;

  DECLARE pointer CURSOR FOR
  SELECT `id` FROM `replication`.`master_alive`;
  DECLARE CONTINUE HANDLER FOR
          SQLSTATE 'HY000' SET master_dead = true;

  OPEN pointer;
  IF( master_dead ) THEN

    STOP SLAVE;

    SELECT @latest:=MAX(`epoch`)
      FROM `mysql`.`ndb_apply_status`;

    SELECT @file:=SUBSTRING_INDEX(File, '/', -1), @pos:=Position
      FROM `replication`.`new_master_binlog_index`
     WHERE `epoch` > @latest
     ORDER BY `epoch` ASC LIMIT 1;

    CHANGE MASTER TO
           MASTER_HOST='192.168.0.1',
           MASTER_PORT=3306,
           MASTER_USER='replica',
           MASTER_PASSWORD='mypasswd',
           MASTER_LOG_FILE='@file',
           MASTER_LOG_POS=@pos;
    START SLAVE;
    ALTER EVENT `check_master_alive` DISABLE;
  END IF;
  CLOSE pointer;
END;
//
DELIMITER ;

CREATE EVENT check_master_alive
    ON SCHEDULE EVERY 5 SECOND
       ENABLE DO CALL check_master();

This is the error message:
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 'CHANGE MASTER TO MASTER_HOST='10.0.33.104', MASTER_PORT=3306, MASTER_USER='repli' at line 15

I also tried the procedure with SELECT .. INTO but it doesn't seem to work at all.


I have submitted a bug - #28270 - and our tech support is working on it.

In the meantime, Erik has found a workaround to the problem, so he can implement the script. The workaround is based on the PREPARE/EXECUTE statement:

CREATE PROCEDURE check_master()
BEGIN
  DECLARE master_dead BOOLEAN DEFAULT false;
  DECLARE pointer CURSOR FOR

  SELECT `ip` FROM `replication`.`master_alive`;

  DECLARE CONTINUE HANDLER FOR
          SQLSTATE 'HY000' SET master_dead = true;

  OPEN pointer;
  IF( master_dead ) THEN

    STOP SLAVE;

    SELECT @latest:=MAX(`epoch`)
      FROM `mysql`.`ndb_apply_status`;

    SELECT @file:=SUBSTRING_INDEX(File, '/', -1), @pos:=Position
      FROM `replication`.`new_master_binlog_index`
     WHERE `epoch` > @latest
     ORDER BY `epoch` ASC LIMIT 1;

    SET @new_master_command = CONCAT( 'CHANGE MASTER TO
                 MASTER_HOST=\'192.168.0.1\',
                 MASTER_PORT=3306,
                 MASTER_USER=\'replication_user\',
                 MASTER_PASSWORD=\'mypasswd\',                  MASTER_LOG_FILE=\'',@file,'\',
                 MASTER_LOG_POS=',@pos,';' );

    PREPARE nmc FROM @new_master_command;
    EXECUTE nmc;
    DEALLOCATE PREPARE nmc;

    START SLAVE;

    ALTER EVENT `check_master_alive` DISABLE;
  END IF;
END;

In this way, the server accepts the CHANGE MASTER command.

Thanks to Erik for this great workaround, in the meantime we will check that the bug will be fixed.