2
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
5
drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
7
DROP TABLE IF EXISTS t1, t2, t3;
8
***** Test 1 RPL of CDD and Alter *****
9
***** Test 1 setup *****
10
CREATE LOGFILE GROUP lg1
11
ADD UNDOFILE 'undofile.dat'
15
ALTER LOGFILE GROUP lg1
16
ADD UNDOFILE 'undofile02.dat'
20
ADD DATAFILE 'datafile.dat'
25
ADD DATAFILE 'datafile02.dat'
29
(c1 INT NOT NULL PRIMARY KEY,
32
TABLESPACE ts1 STORAGE DISK
34
***** insert some data *****
35
***** Select from Master *****
36
SELECT * FROM t1 ORDER BY c1 LIMIT 5;
43
***** Select from Slave *****
44
SELECT * FROM t1 ORDER BY c1 LIMIT 5;
51
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
undofile.dat UNDO LOG NULL lg1
56
undofile02.dat UNDO LOG NULL lg1
57
**** Do First Set of ALTERs in the master table ****
58
CREATE INDEX t1_i ON t1(c2, c3);
59
CREATE UNIQUE INDEX t1_i2 ON t1(c2);
60
ALTER TABLE t1 ADD c4 TIMESTAMP;
61
ALTER TABLE t1 ADD c5 DOUBLE;
62
ALTER TABLE t1 ADD INDEX (c5);
65
t1 CREATE TABLE `t1` (
66
`c1` int(11) NOT NULL,
67
`c2` int(11) NOT NULL,
68
`c3` int(11) NOT NULL,
69
`c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
70
`c5` double DEFAULT NULL,
72
UNIQUE KEY `t1_i2` (`c2`),
73
KEY `t1_i` (`c2`,`c3`),
75
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
76
**** Show first set of ALTERs on SLAVE ****
79
t1 CREATE TABLE `t1` (
80
`c1` int(11) NOT NULL,
81
`c2` int(11) NOT NULL,
82
`c3` int(11) NOT NULL,
83
`c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
84
`c5` double DEFAULT NULL,
86
UNIQUE KEY `t1_i2` (`c2`),
87
KEY `t1_i` (`c2`,`c3`),
89
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
90
**** Second set of alters test 1 ****
91
ALTER TABLE t1 RENAME t2;
92
ALTER TABLE t2 DROP INDEX c5;
93
CREATE TABLE t1(c1 INT)ENGINE=NDB;
94
INSERT INTO t1 VALUES(1);
96
ALTER TABLE t2 RENAME t1;
97
**** Show second set of ALTERs on MASTER ****
100
t1 CREATE TABLE `t1` (
101
`c1` int(11) NOT NULL,
102
`c2` int(11) NOT NULL,
103
`c3` int(11) NOT NULL,
104
`c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
105
`c5` double DEFAULT NULL,
107
UNIQUE KEY `t1_i2` (`c2`),
108
KEY `t1_i` (`c2`,`c3`)
109
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
110
**** Show second set of ALTERs on SLAVE ****
111
SHOW CREATE TABLE t1;
113
t1 CREATE TABLE `t1` (
114
`c1` int(11) NOT NULL,
115
`c2` int(11) NOT NULL,
116
`c3` int(11) NOT NULL,
117
`c4` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
118
`c5` double DEFAULT NULL,
120
UNIQUE KEY `t1_i2` (`c2`),
121
KEY `t1_i` (`c2`,`c3`)
122
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
123
**** Third and last set of alters for test1 ****
124
ALTER TABLE t1 CHANGE c1 c1 DOUBLE;
125
ALTER TABLE t1 CHANGE c2 c2 DECIMAL(10,2);
126
ALTER TABLE t1 DROP COLUMN c3;
127
ALTER TABLE t1 CHANGE c4 c4 TEXT CHARACTER SET utf8;
128
ALTER TABLE t1 CHANGE c4 c4 BLOB;
129
ALTER TABLE t1 CHANGE c4 c3 BLOB;
131
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
132
UPDATE t1 SET c3=@b1 where c1 = 1;
133
UPDATE t1 SET c3=@b1 where c1 = 2;
134
**** Show last set of ALTERs on MASTER ****
135
SHOW CREATE TABLE t1;
137
t1 CREATE TABLE `t1` (
138
`c1` double NOT NULL DEFAULT '0',
139
`c2` decimal(10,2) DEFAULT NULL,
141
`c5` double DEFAULT NULL,
143
UNIQUE KEY `t1_i2` (`c2`),
145
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
146
SELECT * FROM t1 ORDER BY c1 LIMIT 5;
148
1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
149
2 4.00 b1b1b1b1b1b1b1b1b1b1 NULL
150
3 6.00 0000-00-00 00:00:00 NULL
151
4 8.00 0000-00-00 00:00:00 NULL
152
5 10.00 0000-00-00 00:00:00 NULL
153
**** Show last set of ALTERs on SLAVE ****
154
SHOW CREATE TABLE t1;
156
t1 CREATE TABLE `t1` (
157
`c1` double NOT NULL DEFAULT '0',
158
`c2` decimal(10,2) DEFAULT NULL,
160
`c5` double DEFAULT NULL,
162
UNIQUE KEY `t1_i2` (`c2`),
164
) /*!50100 TABLESPACE ts1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
165
SELECT * FROM t1 ORDER BY c1 LIMIT 5;
167
1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
168
2 4.00 b1b1b1b1b1b1b1b1b1b1 NULL
169
3 6.00 0000-00-00 00:00:00 NULL
170
4 8.00 0000-00-00 00:00:00 NULL
171
5 10.00 0000-00-00 00:00:00 NULL
172
SELECT * FROM t1 where c1 = 1;
174
1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
180
******** Create additional TABLESPACE test 2 **************
181
CREATE TABLESPACE ts2
182
ADD DATAFILE 'datafile03.dat'
183
USE LOGFILE GROUP lg1
187
ADD DATAFILE 'datafile04.dat'
190
DROP DATABASE IF EXISTS tpcb;
191
CREATE DATABASE tpcb;
193
CREATE TABLE tpcb.account
194
(id INT, bid INT, balance DECIMAL(10,2),
195
filler CHAR(255), PRIMARY KEY(id))
196
TABLESPACE ts2 STORAGE DISK
199
CREATE TABLE tpcb.branch
200
(bid INT, balance DECIMAL(10,2), filler VARCHAR(255),
201
PRIMARY KEY(bid))TABLESPACE ts2 STORAGE DISK
204
CREATE TABLE tpcb.teller
205
(tid INT, balance DECIMAL(10,2), filler VARCHAR(255),
206
PRIMARY KEY(tid)) TABLESPACE ts2 STORAGE DISK
209
CREATE TABLE tpcb.history
210
(id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT,
211
tid INT, bid INT, amount DECIMAL(10,2),
212
tdate DATETIME, teller CHAR(20), uuidf LONGBLOB,
213
filler CHAR(80),PRIMARY KEY (id))
214
TABLESPACE ts2 STORAGE DISK
217
--- Create stored procedures & functions ---
220
*** Stored Procedures Created ***
222
****** TEST 2 test time *********************************
224
*********** Load up the database ******************
226
********** Check load master and slave **************
227
SELECT COUNT(*) FROM account;
231
SELECT COUNT(*) FROM account;
234
******** Run in some transactions ***************
235
***** Time to try slave sync ***********
236
**** Must make sure slave is clean *****
239
DROP PROCEDURE IF EXISTS tpcb.load;
240
DROP PROCEDURE IF EXISTS tpcb.trans;
241
DROP TABLE IF EXISTS tpcb.account;
242
DROP TABLE IF EXISTS tpcb.teller;
243
DROP TABLE IF EXISTS tpcb.branch;
244
DROP TABLE IF EXISTS tpcb.history;
247
DROP DATAFILE 'datafile.dat'
250
DROP DATAFILE 'datafile02.dat'
252
DROP TABLESPACE ts1 ENGINE=NDB;
254
DROP DATAFILE 'datafile03.dat'
257
DROP DATAFILE 'datafile04.dat'
259
DROP TABLESPACE ts2 ENGINE=NDB;
260
DROP LOGFILE GROUP lg1 ENGINE=NDB;
261
********** Take a backup of the Master *************
262
SELECT COUNT(*) FROM history;
265
SELECT COUNT(*) FROM history;
268
CREATE TEMPORARY TABLE test.backup_info (id INT, backup_id INT) ENGINE = HEAP;
269
LOAD DATA INFILE 'DUMP_FILE' INTO TABLE test.backup_info FIELDS TERMINATED BY ',';
270
DROP TABLE test.backup_info;
271
************ Restore the slave ************************
272
CREATE DATABASE tpcb;
273
***** Check a few slave restore values ***************
275
SELECT COUNT(*) FROM account;
278
***** Add some more records to master *********
279
***** Finsh the slave sync process *******
280
@the_epoch:=MAX(epoch)
282
@the_pos:=Position @the_file:=SUBSTRING_INDEX(FILE, '/', -1)
283
<the_pos> master-bin.000001
287
**** We should be ready to continue on *************
288
****** Let's make sure we match *******
291
SELECT COUNT(*) FROM history;
294
****** SLAVE ********
296
SELECT COUNT(*) FROM history;
299
*** DUMP MASTER & SLAVE FOR COMPARE ********
300
*************** TEST 2 CLEANUP SECTION ********************
301
DROP PROCEDURE IF EXISTS tpcb.load;
302
DROP PROCEDURE IF EXISTS tpcb.trans;
303
DROP TABLE tpcb.account;
304
DROP TABLE tpcb.teller;
305
DROP TABLE tpcb.branch;
306
DROP TABLE tpcb.history;
309
DROP DATAFILE 'datafile.dat'
312
DROP DATAFILE 'datafile02.dat'
314
DROP TABLESPACE ts1 ENGINE=NDB;
316
DROP DATAFILE 'datafile03.dat'
319
DROP DATAFILE 'datafile04.dat'
321
DROP TABLESPACE ts2 ENGINE=NDB;
322
DROP LOGFILE GROUP lg1 ENGINE=NDB;
323
****** Do dumps compare ************