stop slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; reset master; reset slave; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9; start slave; DROP TABLE IF EXISTS t1, t2, t3; ***** 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 12M ENGINE=NDB; ALTER TABLESPACE ts1 ADD DATAFILE 'datafile02.dat' INITIAL_SIZE 4M 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 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); 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`), KEY `t1_i` (`c2`,`c3`), KEY `c5` (`c5`) ) 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`), KEY `t1_i` (`c2`,`c3`), KEY `c5` (`c5`) ) 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`), KEY `t1_i` (`c2`,`c3`) ) 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`), KEY `t1_i` (`c2`,`c3`) ) 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`), KEY `t1_i` (`c2`) ) 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`), KEY `t1_i` (`c2`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 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 12M ENGINE=NDB; ALTER TABLESPACE ts2 ADD DATAFILE 'datafile04.dat' INITIAL_SIZE 12M ENGINE=NDB; DROP DATABASE IF EXISTS tpcb; Warnings: Note 1008 Can't drop database 'tpcb'; database doesn't exist CREATE DATABASE tpcb; *********** Create TPCB Tables ***************** CREATE TABLE tpcb.account (id INT, bid INT, balance DECIMAL(10,2), filler CHAR(255), PRIMARY KEY(id)) TABLESPACE ts1 STORAGE DISK ENGINE=NDB; CREATE TABLE tpcb.branch (bid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(bid)) ENGINE=NDB; CREATE TABLE tpcb.teller (tid INT, balance DECIMAL(10,2), filler VARCHAR(255), PRIMARY KEY(tid)) TABLESPACE ts2 STORAGE DISK ENGINE=NDB; 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=NDB; ********* Create Procedures and Functions ************ CREATE PROCEDURE tpcb.load() BEGIN DECLARE acct INT DEFAULT 1000; DECLARE brch INT DEFAULT 100; DECLARE tell INT DEFAULT 1000; DECLARE tmp INT DEFAULT 100; WHILE brch > 0 DO SET tmp = 100; WHILE tmp > 0 DO INSERT INTO tpcb.account VALUES (acct, brch, 0.0, "FRESH ACCOUNT"); SET acct = acct - 1; SET tmp = tmp -1; END WHILE; INSERT INTO tpcb.branch VALUES (brch, 0.0, "FRESH BRANCH"); SET brch = brch - 1; END WHILE; WHILE tell > 0 DO INSERT INTO tpcb.teller VALUES (tell, 0.0, "FRESH TELLER"); SET tell = tell - 1; END WHILE; END| CREATE FUNCTION tpcb.account_id () RETURNS INT BEGIN DECLARE num INT; DECLARE ran INT; SELECT RAND() * 10 INTO ran; IF (ran < 2) THEN SELECT RAND() * 10 INTO num; ELSEIF (ran < 4) THEN SELECT RAND() * 100 INTO num; ELSE SELECT RAND() * 1000 INTO num; END IF; IF (num < 1) THEN RETURN 1; END IF; RETURN num; END| CREATE FUNCTION tpcb.teller_id () RETURNS INT BEGIN DECLARE num INT; DECLARE ran INT; SELECT RAND() * 10 INTO ran; IF (ran < 2) THEN SELECT RAND() * 10 INTO num; ELSEIF (ran < 5) THEN SELECT RAND() * 100 INTO num; ELSE SELECT RAND() * 1000 INTO num; END IF; IF (num < 1) THEN RETURN 1; END IF; RETURN num; END| CREATE PROCEDURE tpcb.trans() BEGIN DECLARE acct INT DEFAULT 0; DECLARE brch INT DEFAULT 0; DECLARE tell INT DEFAULT 0; DECLARE bal DECIMAL(10,2) DEFAULT 0.0; DECLARE amount DECIMAL(10,2) DEFAULT 1.00; DECLARE test INT DEFAULT 0; DECLARE bbal DECIMAL(10,2) DEFAULT 0.0; DECLARE tbal DECIMAL(10,2) DEFAULT 0.0; DECLARE local_uuid VARCHAR(255); DECLARE local_user VARCHAR(255); DECLARE local_time TIMESTAMP; SELECT RAND() * 10 INTO test; SELECT tpcb.account_id() INTO acct; SELECT tpcb.teller_id() INTO tell; SELECT account.balance INTO bal FROM tpcb.account WHERE id = acct; SELECT account.bid INTO brch FROM tpcb.account WHERE id = acct; SELECT teller.balance INTO tbal FROM tpcb.teller WHERE tid = tell; SELECT branch.balance INTO bbal FROM tpcb.branch WHERE bid = brch; IF (test < 5) THEN SET bal = bal + amount; SET bbal = bbal + amount; SET tbal = tbal + amount; UPDATE tpcb.account SET balance = bal, filler = 'account updated' WHERE id = acct; UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated' WHERE bid = brch; UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated' WHERE tid = tell; ELSE SET bal = bal - amount; SET bbal = bbal - amount; SET tbal = tbal - amount; UPDATE tpcb.account SET balance = bal, filler = 'account updated' WHERE id = acct; UPDATE tpcb.branch SET balance = bbal, filler = 'branch updated' WHERE bid = brch; UPDATE tpcb.teller SET balance = tbal, filler = 'teller updated' WHERE tid = tell; END IF; SET local_uuid=UUID(); SET local_user=USER(); SET local_time= NOW(); INSERT INTO tpcb.history VALUES(NULL,acct,tell,brch,amount, local_time,local_user, local_uuid,'completed trans'); END| ****** TEST 2 test time ********************************* USE tpcb; *********** Load up the database ****************** CALL tpcb.load(); ********** Check load master and slave ************** SELECT COUNT(*) FROM account; COUNT(*) 10000 USE tpcb; SELECT COUNT(*) FROM account; COUNT(*) 10000 ******** 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(*) 1000 SELECT COUNT(*) FROM history; COUNT(*) 2000 CREATE TEMPORARY TABLE IF NOT EXISTS cluster.backup_info (id INT, backup_id INT) ENGINE = HEAP; DELETE FROM cluster.backup_info; LOAD DATA INFILE '../tmp.dat' INTO TABLE cluster.backup_info FIELDS TERMINATED BY ','; SELECT @the_backup_id:=backup_id FROM cluster.backup_info; @the_backup_id:=backup_id <the_backup_id> DROP TABLE IF EXISTS cluster.backup_info; ************ Restore the slave ************************ CREATE DATABASE tpcb; ***** Check a few slave restore values *************** USE tpcb; SELECT COUNT(*) FROM account; COUNT(*) 10000 ***** Add some more records to master ********* ***** Finsh the slave sync process ******* @the_epoch:=MAX(epoch) <the_epoch> @the_pos:=Position @the_file:=SUBSTRING_INDEX(FILE, '/', -1) <the_pos> master-bin.000001 * 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(*) 4050 ****** SLAVE ******** USE tpcb; SELECT COUNT(*) FROM history; COUNT(*) 4050 *** 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 ************