1include/master-slave.inc 2[connection master] 3***** Test 1 RPL of CDD and Alter ***** 4***** Test 1 setup ***** 5CREATE LOGFILE GROUP lg1 6ADD UNDOFILE 'undofile.dat' 7INITIAL_SIZE 16M 8UNDO_BUFFER_SIZE = 1M 9ENGINE=NDB; 10ALTER LOGFILE GROUP lg1 11ADD UNDOFILE 'undofile02.dat' 12INITIAL_SIZE 4M 13ENGINE=NDB; 14CREATE TABLESPACE ts1 15ADD DATAFILE 'datafile.dat' 16USE LOGFILE GROUP lg1 17INITIAL_SIZE 24M 18ENGINE=NDB; 19ALTER TABLESPACE ts1 20ADD DATAFILE 'datafile02.dat' 21INITIAL_SIZE 8M 22ENGINE=NDB; 23CREATE TABLE t1 24(c1 INT NOT NULL PRIMARY KEY, 25c2 INT NOT NULL, 26c3 INT NOT NULL) 27TABLESPACE ts1 STORAGE DISK 28ENGINE=NDB; 29***** insert some data ***** 30***** Select from Master ***** 31SELECT * FROM t1 ORDER BY c1 LIMIT 5; 32c1 c2 c3 331 2 4 342 4 5 353 6 6 364 8 7 375 10 8 38***** Select from Slave ***** 39SELECT * FROM t1 ORDER BY c1 LIMIT 5; 40c1 c2 c3 411 2 4 422 4 5 433 6 6 444 8 7 455 10 8 46FILE_NAME FILE_TYPE TABLESPACE_NAME LOGFILE_GROUP_NAME 47NULL TABLESPACE ts1 lg1 48NULL UNDO LOG NULL lg1 49datafile.dat DATAFILE ts1 lg1 50datafile02.dat DATAFILE ts1 lg1 51undofile.dat UNDO LOG NULL lg1 52undofile02.dat UNDO LOG NULL lg1 53**** Do First Set of ALTERs in the master table **** 54CREATE INDEX t1_i ON t1(c2, c3); 55CREATE UNIQUE INDEX t1_i2 ON t1(c2); 56ALTER TABLE t1 ADD c4 TIMESTAMP; 57ALTER TABLE t1 ADD c5 DOUBLE; 58ALTER TABLE t1 ADD INDEX (c5); 59SHOW CREATE TABLE t1; 60Table Create Table 61t1 CREATE TABLE `t1` ( 62 `c1` int(11) NOT NULL, 63 `c2` int(11) NOT NULL, 64 `c3` int(11) NOT NULL, 65 `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 66 `c5` double DEFAULT NULL, 67 PRIMARY KEY (`c1`), 68 UNIQUE KEY `t1_i2` (`c2`), 69 KEY `t1_i` (`c2`,`c3`), 70 KEY `c5` (`c5`) 71) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 72**** Show first set of ALTERs on SLAVE **** 73SHOW CREATE TABLE t1; 74Table Create Table 75t1 CREATE TABLE `t1` ( 76 `c1` int(11) NOT NULL, 77 `c2` int(11) NOT NULL, 78 `c3` int(11) NOT NULL, 79 `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 80 `c5` double DEFAULT NULL, 81 PRIMARY KEY (`c1`), 82 UNIQUE KEY `t1_i2` (`c2`), 83 KEY `t1_i` (`c2`,`c3`), 84 KEY `c5` (`c5`) 85) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 86**** Second set of alters test 1 **** 87ALTER TABLE t1 RENAME t2; 88ALTER TABLE t2 DROP INDEX c5; 89CREATE TABLE t1(c1 INT)ENGINE=NDB; 90INSERT INTO t1 VALUES(1); 91DROP TABLE t1; 92ALTER TABLE t2 RENAME t1; 93**** Show second set of ALTERs on MASTER **** 94SHOW CREATE TABLE t1; 95Table Create Table 96t1 CREATE TABLE `t1` ( 97 `c1` int(11) NOT NULL, 98 `c2` int(11) NOT NULL, 99 `c3` int(11) NOT NULL, 100 `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 101 `c5` double DEFAULT NULL, 102 PRIMARY KEY (`c1`), 103 UNIQUE KEY `t1_i2` (`c2`), 104 KEY `t1_i` (`c2`,`c3`) 105) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 106**** Show second set of ALTERs on SLAVE **** 107SHOW CREATE TABLE t1; 108Table Create Table 109t1 CREATE TABLE `t1` ( 110 `c1` int(11) NOT NULL, 111 `c2` int(11) NOT NULL, 112 `c3` int(11) NOT NULL, 113 `c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 114 `c5` double DEFAULT NULL, 115 PRIMARY KEY (`c1`), 116 UNIQUE KEY `t1_i2` (`c2`), 117 KEY `t1_i` (`c2`,`c3`) 118) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 119**** Third and last set of alters for test1 **** 120ALTER TABLE t1 CHANGE c1 c1 DOUBLE; 121ALTER TABLE t1 CHANGE c2 c2 DECIMAL(10,2); 122ALTER TABLE t1 DROP COLUMN c3; 123ALTER TABLE t1 CHANGE c4 c4 TEXT CHARACTER SET utf8; 124ALTER TABLE t1 CHANGE c4 c4 BLOB; 125ALTER TABLE t1 CHANGE c4 c3 BLOB; 126set @b1 = 'b1'; 127set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); 128UPDATE t1 SET c3=@b1 where c1 = 1; 129UPDATE t1 SET c3=@b1 where c1 = 2; 130**** Show last set of ALTERs on MASTER **** 131SHOW CREATE TABLE t1; 132Table Create Table 133t1 CREATE TABLE `t1` ( 134 `c1` double NOT NULL DEFAULT '0', 135 `c2` decimal(10,2) DEFAULT NULL, 136 `c3` blob, 137 `c5` double DEFAULT NULL, 138 PRIMARY KEY (`c1`), 139 UNIQUE KEY `t1_i2` (`c2`), 140 KEY `t1_i` (`c2`) 141) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 142SELECT * FROM t1 ORDER BY c1 LIMIT 5; 143c1 c2 c3 c5 1441 2.00 b1b1b1b1b1b1b1b1b1b1 NULL 1452 4.00 b1b1b1b1b1b1b1b1b1b1 NULL 1463 6.00 0000-00-00 00:00:00 NULL 1474 8.00 0000-00-00 00:00:00 NULL 1485 10.00 0000-00-00 00:00:00 NULL 149**** Show last set of ALTERs on SLAVE **** 150SHOW CREATE TABLE t1; 151Table Create Table 152t1 CREATE TABLE `t1` ( 153 `c1` double NOT NULL DEFAULT '0', 154 `c2` decimal(10,2) DEFAULT NULL, 155 `c3` blob, 156 `c5` double DEFAULT NULL, 157 PRIMARY KEY (`c1`), 158 UNIQUE KEY `t1_i2` (`c2`), 159 KEY `t1_i` (`c2`) 160) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1 161SELECT * FROM t1 ORDER BY c1 LIMIT 5; 162c1 c2 c3 c5 1631 2.00 b1b1b1b1b1b1b1b1b1b1 NULL 1642 4.00 b1b1b1b1b1b1b1b1b1b1 NULL 1653 6.00 0000-00-00 00:00:00 NULL 1664 8.00 0000-00-00 00:00:00 NULL 1675 10.00 0000-00-00 00:00:00 NULL 168SELECT * FROM t1 where c1 = 1; 169c1 c2 c3 c5 1701 2.00 b1b1b1b1b1b1b1b1b1b1 NULL 171DROP TABLE t1; 172STOP SLAVE; 173RESET SLAVE; 174RESET MASTER; 175START SLAVE; 176******** Create additional TABLESPACE test 2 ************** 177CREATE TABLESPACE ts2 178ADD DATAFILE 'datafile03.dat' 179USE LOGFILE GROUP lg1 180INITIAL_SIZE 28M 181ENGINE=NDB; 182ALTER TABLESPACE ts2 183ADD DATAFILE 'datafile04.dat' 184INITIAL_SIZE 18M 185ENGINE=NDB; 186DROP DATABASE IF EXISTS tpcb; 187CREATE DATABASE tpcb; 188 189CREATE TABLE tpcb.account 190(id INT, bid INT, balance DECIMAL(10,2), 191filler CHAR(255), PRIMARY KEY(id)) 192TABLESPACE ts2 STORAGE DISK 193ENGINE=NDBCLUSTER; 194 195CREATE TABLE tpcb.branch 196(bid INT, balance DECIMAL(10,2), filler VARCHAR(255), 197PRIMARY KEY(bid))TABLESPACE ts2 STORAGE DISK 198ENGINE=NDBCLUSTER; 199 200CREATE TABLE tpcb.teller 201(tid INT, balance DECIMAL(10,2), filler VARCHAR(255), 202PRIMARY KEY(tid)) TABLESPACE ts2 STORAGE DISK 203ENGINE=NDBCLUSTER; 204 205CREATE TABLE tpcb.history 206(id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT, 207tid INT, bid INT, amount DECIMAL(10,2), 208tdate DATETIME, teller CHAR(20), uuidf LONGBLOB, 209filler CHAR(80),PRIMARY KEY (id)) 210TABLESPACE ts2 STORAGE DISK 211ENGINE=NDBCLUSTER; 212 213--- Create stored procedures & functions --- 214 215 216*** Stored Procedures Created *** 217 218****** TEST 2 test time ********************************* 219USE tpcb; 220*********** Load up the database ****************** 221CALL tpcb.load(); 222********** Check load master and slave ************** 223SELECT COUNT(*) FROM account; 224COUNT(*) 2251000 226USE tpcb; 227SELECT COUNT(*) FROM account; 228COUNT(*) 2291000 230******** Run in some transactions *************** 231***** Time to try slave sync *********** 232**** Must make sure slave is clean ***** 233STOP SLAVE; 234RESET SLAVE; 235DROP PROCEDURE IF EXISTS tpcb.load; 236DROP PROCEDURE IF EXISTS tpcb.trans; 237DROP TABLE IF EXISTS tpcb.account; 238DROP TABLE IF EXISTS tpcb.teller; 239DROP TABLE IF EXISTS tpcb.branch; 240DROP TABLE IF EXISTS tpcb.history; 241DROP DATABASE tpcb; 242ALTER TABLESPACE ts1 243DROP DATAFILE 'datafile.dat' 244ENGINE=NDB; 245ALTER TABLESPACE ts1 246DROP DATAFILE 'datafile02.dat' 247ENGINE=NDB; 248DROP TABLESPACE ts1 ENGINE=NDB; 249ALTER TABLESPACE ts2 250DROP DATAFILE 'datafile03.dat' 251ENGINE=NDB; 252ALTER TABLESPACE ts2 253DROP DATAFILE 'datafile04.dat' 254ENGINE=NDB; 255DROP TABLESPACE ts2 ENGINE=NDB; 256DROP LOGFILE GROUP lg1 ENGINE=NDB; 257********** Take a backup of the Master ************* 258SELECT COUNT(*) FROM history; 259COUNT(*) 260100 261SELECT COUNT(*) FROM history; 262COUNT(*) 263200 264************ Restore the slave ************************ 265CREATE DATABASE tpcb; 266***** Check a few slave restore values *************** 267USE tpcb; 268SELECT COUNT(*) FROM account; 269COUNT(*) 2701000 271***** Add some more records to master ********* 272***** Finsh the slave sync process ******* 273@the_epoch:=MAX(epoch) 274<the_epoch> 275@the_pos:=Position @the_file:=SUBSTRING_INDEX(REPLACE(FILE,'\\','/'), '/', -1) 276<the_pos> master-bin.000001 277CHANGE MASTER TO 278master_log_file = 'master-bin.000001', 279master_log_pos = <the_pos> ; 280* 4. * 281* 5. * 282START SLAVE; 283**** We should be ready to continue on ************* 284****** Let's make sure we match ******* 285***** MASTER ******* 286USE tpcb; 287SELECT COUNT(*) FROM history; 288COUNT(*) 289400 290****** SLAVE ******** 291USE tpcb; 292SELECT COUNT(*) FROM history; 293COUNT(*) 294400 295*** DUMP MASTER & SLAVE FOR COMPARE ******** 296*************** TEST 2 CLEANUP SECTION ******************** 297DROP PROCEDURE IF EXISTS tpcb.load; 298DROP PROCEDURE IF EXISTS tpcb.trans; 299DROP TABLE tpcb.account; 300DROP TABLE tpcb.teller; 301DROP TABLE tpcb.branch; 302DROP TABLE tpcb.history; 303DROP DATABASE tpcb; 304ALTER TABLESPACE ts1 305DROP DATAFILE 'datafile.dat' 306ENGINE=NDB; 307ALTER TABLESPACE ts1 308DROP DATAFILE 'datafile02.dat' 309ENGINE=NDB; 310DROP TABLESPACE ts1 ENGINE=NDB; 311ALTER TABLESPACE ts2 312DROP DATAFILE 'datafile03.dat' 313ENGINE=NDB; 314ALTER TABLESPACE ts2 315DROP DATAFILE 'datafile04.dat' 316ENGINE=NDB; 317DROP TABLESPACE ts2 ENGINE=NDB; 318DROP LOGFILE GROUP lg1 ENGINE=NDB; 319****** Do dumps compare ************ 320include/rpl_end.inc 321