rpl_ndb_dd_advance.result 8.87 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
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
52
NULL	UNDO LOG	NULL	lg1
53
datafile.dat	DATAFILE	ts1	lg1
54
datafile02.dat	DATAFILE	ts1	lg1
55 56
undofile.dat	UNDO LOG	NULL	lg1
undofile02.dat	UNDO LOG	NULL	lg1
57 58
**** Do First Set of ALTERs in the master table ****
CREATE INDEX t1_i ON t1(c2, c3);
59
CREATE UNIQUE INDEX t1_i2 ON t1(c2);
60 61 62 63 64 65 66 67 68 69 70 71
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`),
72
  UNIQUE KEY `t1_i2` (`c2`),
73 74
  KEY `t1_i` (`c2`,`c3`),
  KEY `c5` (`c5`)
75
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
76 77 78 79 80 81 82 83 84 85
**** 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`),
86
  UNIQUE KEY `t1_i2` (`c2`),
87 88
  KEY `t1_i` (`c2`,`c3`),
  KEY `c5` (`c5`)
89
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106
**** 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`),
107
  UNIQUE KEY `t1_i2` (`c2`),
108
  KEY `t1_i` (`c2`,`c3`)
109
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
110 111 112 113 114 115 116 117 118 119
**** 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`),
120
  UNIQUE KEY `t1_i2` (`c2`),
121
  KEY `t1_i` (`c2`,`c3`)
122
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142
**** 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`),
143
  UNIQUE KEY `t1_i2` (`c2`),
144
  KEY `t1_i` (`c2`)
145
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161
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`),
162
  UNIQUE KEY `t1_i2` (`c2`),
163
  KEY `t1_i` (`c2`)
164
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
165 166 167 168 169 170 171
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
172 173 174 175 176 177 178 179 180 181 182 183
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
184
INITIAL_SIZE 10M
185 186 187
ENGINE=NDB;
ALTER TABLESPACE ts2
ADD DATAFILE 'datafile04.dat'
188
INITIAL_SIZE 5M
189 190 191
ENGINE=NDB;
DROP DATABASE IF EXISTS tpcb;
CREATE DATABASE tpcb;
192 193

CREATE TABLE tpcb.account
194 195
(id INT, bid INT, balance DECIMAL(10,2),
filler CHAR(255), PRIMARY KEY(id))
196 197 198 199
TABLESPACE ts2 STORAGE DISK
ENGINE=NDBCLUSTER;

CREATE TABLE tpcb.branch
200
(bid INT, balance DECIMAL(10,2), filler VARCHAR(255),
201 202 203 204
PRIMARY KEY(bid))TABLESPACE ts2 STORAGE DISK
ENGINE=NDBCLUSTER;

CREATE TABLE tpcb.teller
205
(tid INT, balance DECIMAL(10,2), filler VARCHAR(255),
206 207 208 209
PRIMARY KEY(tid)) TABLESPACE ts2 STORAGE DISK
ENGINE=NDBCLUSTER;

CREATE TABLE tpcb.history
210 211 212 213 214
(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
215 216 217 218 219 220 221
ENGINE=NDBCLUSTER;

--- Create stored procedures & functions ---


*** Stored Procedures Created ***

222 223 224 225 226 227 228
****** TEST 2 test time *********************************
USE tpcb;
*********** Load up the database ******************
CALL tpcb.load();
********** Check load master and slave **************
SELECT COUNT(*) FROM account;
COUNT(*)
229
1000
230 231 232
USE tpcb;
SELECT COUNT(*) FROM account;
COUNT(*)
233
1000
234 235 236 237 238
******** Run in some transactions ***************
***** Time to try slave sync ***********
**** Must make sure slave is clean *****
STOP SLAVE;
RESET SLAVE;
239 240 241 242 243 244
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;
245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263
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(*)
264
100
265 266
SELECT COUNT(*) FROM history;
COUNT(*)
267
200
268 269 270 271
CREATE TEMPORARY TABLE IF NOT EXISTS mysql.backup_info (id INT, backup_id INT) ENGINE = HEAP;
DELETE FROM mysql.backup_info;
LOAD DATA INFILE '../tmp.dat' INTO TABLE mysql.backup_info FIELDS TERMINATED BY ',';
SELECT @the_backup_id:=backup_id FROM mysql.backup_info;
272 273
@the_backup_id:=backup_id
<the_backup_id>
274
DROP TABLE IF EXISTS mysql.backup_info;
275 276 277 278 279 280
************ Restore the slave ************************
CREATE DATABASE tpcb;
***** Check a few slave restore values ***************
USE tpcb;
SELECT COUNT(*) FROM account;
COUNT(*)
281
1000
282 283 284 285 286 287 288 289 290 291 292 293 294 295 296
***** 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(*)
297
400
298 299 300 301
****** SLAVE ********
USE tpcb;
SELECT COUNT(*) FROM history;
COUNT(*)
302
400
303 304
*** DUMP MASTER & SLAVE FOR COMPARE ********
*************** TEST 2 CLEANUP SECTION ********************
305 306
DROP PROCEDURE IF EXISTS tpcb.load;
DROP PROCEDURE IF EXISTS tpcb.trans;
307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327
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 ************