include/master-slave.inc [connection master] ***** Test 1 RPL of CDD and Alter ***** ***** Test 1 setup ***** CREATE LOGFILE GROUP lg1 ADD UNDOFILE 'undofile.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE = 1M ENGINE=NDB; ALTER LOGFILE GROUP lg1 ADD UNDOFILE 'undofile02.dat' INITIAL_SIZE 4M ENGINE=NDB; CREATE TABLESPACE ts1 ADD DATAFILE 'datafile.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 24M ENGINE=NDB; ALTER TABLESPACE ts1 ADD DATAFILE 'datafile02.dat' INITIAL_SIZE 8M ENGINE=NDB; CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY, c2 INT NOT NULL, c3 INT NOT NULL) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; ***** insert some data ***** ***** Select from Master ***** SELECT * FROM t1 ORDER BY c1 LIMIT 5; c1 c2 c3 1 2 4 2 4 5 3 6 6 4 8 7 5 10 8 ***** Select from Slave ***** SELECT * FROM t1 ORDER BY c1 LIMIT 5; c1 c2 c3 1 2 4 2 4 5 3 6 6 4 8 7 5 10 8 FILE_NAME FILE_TYPE TABLESPACE_NAME LOGFILE_GROUP_NAME NULL TABLESPACE ts1 lg1 NULL UNDO LOG NULL lg1 datafile.dat DATAFILE ts1 lg1 datafile02.dat DATAFILE ts1 lg1 undofile.dat UNDO LOG NULL lg1 undofile02.dat UNDO LOG NULL lg1 **** Do First Set of ALTERs in the master table **** CREATE INDEX t1_i ON t1(c2, c3); CREATE UNIQUE INDEX t1_i2 ON t1(c2); ALTER TABLE t1 ADD c4 TIMESTAMP; ALTER TABLE t1 ADD c5 DOUBLE; ALTER TABLE t1 ADD INDEX (c5); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` int(11) NOT NULL, `c3` int(11) NOT NULL, `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`), KEY `c5` (`c5`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 **** Show first set of ALTERs on SLAVE **** SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` int(11) NOT NULL, `c3` int(11) NOT NULL, `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`), KEY `c5` (`c5`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 **** Second set of alters test 1 **** ALTER TABLE t1 RENAME t2; ALTER TABLE t2 DROP INDEX c5; CREATE TABLE t1(c1 INT)ENGINE=NDB; INSERT INTO t1 VALUES(1); DROP TABLE t1; ALTER TABLE t2 RENAME t1; **** Show second set of ALTERs on MASTER **** SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` int(11) NOT NULL, `c3` int(11) NOT NULL, `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 **** Show second set of ALTERs on SLAVE **** SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` int(11) NOT NULL, `c3` int(11) NOT NULL, `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`,`c3`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 **** Third and last set of alters for test1 **** ALTER TABLE t1 CHANGE c1 c1 DOUBLE; ALTER TABLE t1 CHANGE c2 c2 DECIMAL(10,2); ALTER TABLE t1 DROP COLUMN c3; ALTER TABLE t1 CHANGE c4 c4 TEXT CHARACTER SET utf8; ALTER TABLE t1 CHANGE c4 c4 BLOB; ALTER TABLE t1 CHANGE c4 c3 BLOB; set @b1 = 'b1'; set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); UPDATE t1 SET c3=@b1 where c1 = 1; UPDATE t1 SET c3=@b1 where c1 = 2; **** Show last set of ALTERs on MASTER **** SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` double NOT NULL DEFAULT '0', `c2` decimal(10,2) DEFAULT NULL, `c3` blob, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c1 LIMIT 5; c1 c2 c3 c5 1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL 2 4.00 b1b1b1b1b1b1b1b1b1b1 NULL 3 6.00 0000-00-00 00:00:00 NULL 4 8.00 0000-00-00 00:00:00 NULL 5 10.00 0000-00-00 00:00:00 NULL **** Show last set of ALTERs on SLAVE **** SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` double NOT NULL DEFAULT '0', `c2` decimal(10,2) DEFAULT NULL, `c3` blob, `c5` double DEFAULT NULL, PRIMARY KEY (`c1`), UNIQUE KEY `t1_i2` (`c2`), KEY `t1_i` (`c2`) ) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 SELECT * FROM t1 ORDER BY c1 LIMIT 5; c1 c2 c3 c5 1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL 2 4.00 b1b1b1b1b1b1b1b1b1b1 NULL 3 6.00 0000-00-00 00:00:00 NULL 4 8.00 0000-00-00 00:00:00 NULL 5 10.00 0000-00-00 00:00:00 NULL SELECT * FROM t1 where c1 = 1; c1 c2 c3 c5 1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL DROP TABLE t1; STOP SLAVE; RESET SLAVE; RESET MASTER; START SLAVE; ******** Create additional TABLESPACE test 2 ************** CREATE TABLESPACE ts2 ADD DATAFILE 'datafile03.dat' USE LOGFILE GROUP lg1 INITIAL_SIZE 28M ENGINE=NDB; ALTER TABLESPACE ts2 ADD DATAFILE 'datafile04.dat' INITIAL_SIZE 18M ENGINE=NDB; DROP DATABASE IF EXISTS tpcb; CREATE DATABASE tpcb; CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), filler CHAR(255), PRIMARY KEY(id)) TABLESPACE ts2 STORAGE DISK ENGINE=NDBCLUSTER; CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(bid))TABLESPACE ts2 STORAGE DISK ENGINE=NDBCLUSTER; CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(tid)) TABLESPACE ts2 STORAGE DISK ENGINE=NDBCLUSTER; CREATE TABLE tpcb.history (id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT, tid INT, bid INT, amount DECIMAL(10,2), tdate DATETIME, teller CHAR(20), uuidf LONGBLOB, filler CHAR(80),PRIMARY KEY (id)) TABLESPACE ts2 STORAGE DISK ENGINE=NDBCLUSTER; --- Create stored procedures & functions --- *** Stored Procedures Created *** ****** TEST 2 test time ********************************* USE tpcb; *********** Load up the database ****************** CALL tpcb.load(); ********** Check load master and slave ************** SELECT COUNT(*) FROM account; COUNT(*) 1000 USE tpcb; SELECT COUNT(*) FROM account; COUNT(*) 1000 ******** Run in some transactions *************** ***** Time to try slave sync *********** **** Must make sure slave is clean ***** STOP SLAVE; RESET SLAVE; DROP PROCEDURE IF EXISTS tpcb.load; DROP PROCEDURE IF EXISTS tpcb.trans; DROP TABLE IF EXISTS tpcb.account; DROP TABLE IF EXISTS tpcb.teller; DROP TABLE IF EXISTS tpcb.branch; DROP TABLE IF EXISTS tpcb.history; DROP DATABASE tpcb; ALTER TABLESPACE ts1 DROP DATAFILE 'datafile.dat' ENGINE=NDB; ALTER TABLESPACE ts1 DROP DATAFILE 'datafile02.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; ALTER TABLESPACE ts2 DROP DATAFILE 'datafile03.dat' ENGINE=NDB; ALTER TABLESPACE ts2 DROP DATAFILE 'datafile04.dat' ENGINE=NDB; DROP TABLESPACE ts2 ENGINE=NDB; DROP LOGFILE GROUP lg1 ENGINE=NDB; ********** Take a backup of the Master ************* SELECT COUNT(*) FROM history; COUNT(*) 100 SELECT COUNT(*) FROM history; COUNT(*) 200 ************ Restore the slave ************************ CREATE DATABASE tpcb; ***** Check a few slave restore values *************** USE tpcb; SELECT COUNT(*) FROM account; COUNT(*) 1000 ***** Add some more records to master ********* ***** Finsh the slave sync process ******* @the_epoch:=MAX(epoch) @the_pos:=Position @the_file:=SUBSTRING_INDEX(REPLACE(FILE,'\\','/'), '/', -1) master-bin.000001 CHANGE MASTER TO master_log_file = 'master-bin.000001', master_log_pos = ; * 4. * * 5. * START SLAVE; **** We should be ready to continue on ************* ****** Let's make sure we match ******* ***** MASTER ******* USE tpcb; SELECT COUNT(*) FROM history; COUNT(*) 400 ****** SLAVE ******** USE tpcb; SELECT COUNT(*) FROM history; COUNT(*) 400 *** DUMP MASTER & SLAVE FOR COMPARE ******** *************** TEST 2 CLEANUP SECTION ******************** DROP PROCEDURE IF EXISTS tpcb.load; DROP PROCEDURE IF EXISTS tpcb.trans; DROP TABLE tpcb.account; DROP TABLE tpcb.teller; DROP TABLE tpcb.branch; DROP TABLE tpcb.history; DROP DATABASE tpcb; ALTER TABLESPACE ts1 DROP DATAFILE 'datafile.dat' ENGINE=NDB; ALTER TABLESPACE ts1 DROP DATAFILE 'datafile02.dat' ENGINE=NDB; DROP TABLESPACE ts1 ENGINE=NDB; ALTER TABLESPACE ts2 DROP DATAFILE 'datafile03.dat' ENGINE=NDB; ALTER TABLESPACE ts2 DROP DATAFILE 'datafile04.dat' ENGINE=NDB; DROP TABLESPACE ts2 ENGINE=NDB; DROP LOGFILE GROUP lg1 ENGINE=NDB; ****** Do dumps compare ************ include/rpl_end.inc