Debian Codegraph @

mysql-test/suite/ndb_rpl/r/ndb_rpl_dd_advance.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 ***** Test 1 RPL of CDD and Alter *****
7 ***** Test 1 setup *****
8 CREATE LOGFILE GROUP lg1
9 ADD UNDOFILE 'undofile.dat'
10 INITIAL_SIZE 16M
11 UNDO_BUFFER_SIZE = 1M
12 ENGINE=NDB;
13 ALTER LOGFILE GROUP lg1
14 ADD UNDOFILE 'undofile02.dat'
15 INITIAL_SIZE 4M
16 ENGINE=NDB;
17 CREATE TABLESPACE ts1
18 ADD DATAFILE 'datafile.dat'
19 USE LOGFILE GROUP lg1
20 INITIAL_SIZE 24M
21 EXTENT_SIZE 256K
22 ENGINE=NDB;
23 ALTER TABLESPACE ts1
24 ADD DATAFILE 'datafile02.dat'
25 INITIAL_SIZE 12M;
26 CREATE TABLE t1
27 (c1 INT NOT NULL PRIMARY KEY,
28 c2 INT NOT NULL,
29 c3 INT NOT NULL)
30 TABLESPACE ts1 STORAGE DISK
31 ENGINE=NDB;
32 ***** insert some data *****
33 ***** Select from Master *****
34 SELECT * FROM t1 ORDER BY c1 LIMIT 5;
35 c1 c2 c3
36 1 2 4
37 2 4 5
38 3 6 6
39 4 8 7
40 5 10 8
41 ***** Select from Slave *****
42 SELECT * FROM t1 ORDER BY c1 LIMIT 5;
43 c1 c2 c3
44 1 2 4
45 2 4 5
46 3 6 6
47 4 8 7
48 5 10 8
49 FILE_NAME FILE_TYPE TABLESPACE_NAME LOGFILE_GROUP_NAME
50 ./datafile.dat DATAFILE ts1 lg1
51 ./datafile02.dat DATAFILE ts1 lg1
52 ./undofile.dat UNDO LOG lg1 lg1
53 ./undofile02.dat UNDO LOG lg1 lg1
54 **** Do First Set of ALTERs in the master table ****
55 CREATE INDEX t1_i ON t1(c2, c3);
56 CREATE UNIQUE INDEX t1_i2 ON t1(c2);
57 ALTER TABLE t1 ADD c4 TIMESTAMP;
58 ALTER TABLE t1 ADD c5 DOUBLE;
59 ALTER TABLE t1 ADD INDEX (c5);
60 SHOW CREATE TABLE t1;
61 Table Create Table
62 t1 CREATE TABLE `t1` (
63 `c1` int NOT NULL,
64 `c2` int NOT NULL,
65 `c3` int NOT NULL,
66 `c4` timestamp NULL DEFAULT NULL,
67 `c5` double DEFAULT NULL,
68 PRIMARY KEY (`c1`),
69 UNIQUE KEY `t1_i2` (`c2`),
70 KEY `t1_i` (`c2`,`c3`),
71 KEY `c5` (`c5`)
72 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
73 **** Show first set of ALTERs on SLAVE ****
74 SHOW CREATE TABLE t1;
75 Table Create Table
76 t1 CREATE TABLE `t1` (
77 `c1` int NOT NULL,
78 `c2` int NOT NULL,
79 `c3` int NOT NULL,
80 `c4` timestamp NULL DEFAULT NULL,
81 `c5` double DEFAULT NULL,
82 PRIMARY KEY (`c1`),
83 UNIQUE KEY `t1_i2` (`c2`),
84 KEY `t1_i` (`c2`,`c3`),
85 KEY `c5` (`c5`)
86 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
87 **** Second set of alters test 1 ****
88 ALTER TABLE t1 RENAME t2;
89 ALTER TABLE t2 DROP INDEX c5;
90 CREATE TABLE t1(c1 INT)ENGINE=NDB;
91 INSERT INTO t1 VALUES(1);
92 DROP TABLE t1;
93 ALTER TABLE t2 RENAME t1;
94 **** Show second set of ALTERs on MASTER ****
95 SHOW CREATE TABLE t1;
96 Table Create Table
97 t1 CREATE TABLE `t1` (
98 `c1` int NOT NULL,
99 `c2` int NOT NULL,
100 `c3` int NOT NULL,
101 `c4` timestamp NULL DEFAULT NULL,
102 `c5` double DEFAULT NULL,
103 PRIMARY KEY (`c1`),
104 UNIQUE KEY `t1_i2` (`c2`),
105 KEY `t1_i` (`c2`,`c3`)
106 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
107 **** Show second set of ALTERs on SLAVE ****
108 SHOW CREATE TABLE t1;
109 Table Create Table
110 t1 CREATE TABLE `t1` (
111 `c1` int NOT NULL,
112 `c2` int NOT NULL,
113 `c3` int NOT NULL,
114 `c4` timestamp NULL DEFAULT NULL,
115 `c5` double DEFAULT NULL,
116 PRIMARY KEY (`c1`),
117 UNIQUE KEY `t1_i2` (`c2`),
118 KEY `t1_i` (`c2`,`c3`)
119 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
120 **** Third and last set of alters for test1 ****
121 ALTER TABLE t1 CHANGE c1 c1 DOUBLE;
122 ALTER TABLE t1 CHANGE c2 c2 DECIMAL(10,2);
123 ALTER TABLE t1 DROP COLUMN c3;
124 ALTER TABLE t1 CHANGE c4 c4 TEXT CHARACTER SET utf8mb4;
125 ALTER TABLE t1 CHANGE c4 c4 BLOB;
126 ALTER TABLE t1 CHANGE c4 c3 BLOB;
127 set @b1 = 'b1';
128 set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
129 UPDATE t1 SET c3=@b1 where c1 = 1;
130 UPDATE t1 SET c3=@b1 where c1 = 2;
131 **** Show last set of ALTERs on MASTER ****
132 SHOW CREATE TABLE t1;
133 Table Create Table
134 t1 CREATE TABLE `t1` (
135 `c1` double NOT NULL,
136 `c2` decimal(10,2) DEFAULT NULL,
137 `c3` blob,
138 `c5` double DEFAULT NULL,
139 PRIMARY KEY (`c1`),
140 UNIQUE KEY `t1_i2` (`c2`),
141 KEY `t1_i` (`c2`)
142 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
143 SELECT * FROM t1 ORDER BY c1 LIMIT 5;
144 c1 c2 c3 c5
145 1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
146 2 4.00 b1b1b1b1b1b1b1b1b1b1 NULL
147 3 6.00 NULL NULL
148 4 8.00 NULL NULL
149 5 10.00 NULL NULL
150 **** Show last set of ALTERs on SLAVE ****
151 SHOW CREATE TABLE t1;
152 Table Create Table
153 t1 CREATE TABLE `t1` (
154 `c1` double NOT NULL,
155 `c2` decimal(10,2) DEFAULT NULL,
156 `c3` blob,
157 `c5` double DEFAULT NULL,
158 PRIMARY KEY (`c1`),
159 UNIQUE KEY `t1_i2` (`c2`),
160 KEY `t1_i` (`c2`)
161 ) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
162 SELECT * FROM t1 ORDER BY c1 LIMIT 5;
163 c1 c2 c3 c5
164 1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
165 2 4.00 b1b1b1b1b1b1b1b1b1b1 NULL
166 3 6.00 NULL NULL
167 4 8.00 NULL NULL
168 5 10.00 NULL NULL
169 SELECT * FROM t1 where c1 = 1;
170 c1 c2 c3 c5
171 1 2.00 b1b1b1b1b1b1b1b1b1b1 NULL
172 DROP TABLE t1;
173 STOP SLAVE;
174 Warnings:
175 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
176 RESET SLAVE;
177 Warnings:
178 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
179 RESET MASTER;
180 START SLAVE;
181 Warnings:
182 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
183 ******** Create additional TABLESPACE test 2 **************
184 CREATE TABLESPACE ts2
185 ADD DATAFILE 'datafile03.dat'
186 USE LOGFILE GROUP lg1
187 INITIAL_SIZE 28M
188 EXTENT_SIZE 256K
189 ENGINE=NDB;
190 ALTER TABLESPACE ts2
191 ADD DATAFILE 'datafile04.dat'
192 INITIAL_SIZE 18M;
193 DROP DATABASE IF EXISTS tpcb;
194 CREATE DATABASE tpcb;
195
196 CREATE TABLE tpcb.account
197 (id INT, bid INT, balance DECIMAL(10,2),
198 filler CHAR(255), PRIMARY KEY(id))
199 TABLESPACE ts2 STORAGE DISK
200 ENGINE=NDBCLUSTER;
201
202 CREATE TABLE tpcb.branch
203 (bid INT, balance DECIMAL(10,2), filler VARCHAR(255),
204 PRIMARY KEY(bid))TABLESPACE ts2 STORAGE DISK
205 ENGINE=NDBCLUSTER;
206
207 CREATE TABLE tpcb.teller
208 (tid INT, balance DECIMAL(10,2), filler VARCHAR(255),
209 PRIMARY KEY(tid)) TABLESPACE ts2 STORAGE DISK
210 ENGINE=NDBCLUSTER;
211
212 CREATE TABLE tpcb.history
213 (id MEDIUMINT NOT NULL AUTO_INCREMENT,aid INT,
214 tid INT, bid INT, amount DECIMAL(10,2),
215 tdate DATETIME, teller CHAR(20), uuidf LONGBLOB,
216 filler CHAR(80),PRIMARY KEY (id))
217 TABLESPACE ts2 STORAGE DISK
218 ENGINE=NDBCLUSTER;
219
220 --- Create stored procedures & functions ---
221
222
223 *** Stored Procedures Created ***
224
225 ****** TEST 2 test time *********************************
226 USE tpcb;
227 *********** Load up the database ******************
228 CALL tpcb.load();
229 ********** Check load master and slave **************
230 SELECT COUNT(*) FROM account;
231 COUNT(*)
232 1000
233 USE tpcb;
234 SELECT COUNT(*) FROM account;
235 COUNT(*)
236 1000
237 ******** Run in some transactions ***************
238 ***** Time to try slave sync ***********
239 **** Must make sure slave is clean *****
240 USE tpcb;
241 SELECT COUNT(*) FROM account;
242 COUNT(*)
243 1000
244 STOP SLAVE;
245 Warnings:
246 Warning 1287 'STOP SLAVE' is deprecated and will be removed in a future release. Please use STOP REPLICA instead
247 RESET SLAVE;
248 Warnings:
249 Warning 1287 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead
250 DROP PROCEDURE IF EXISTS tpcb.load;
251 DROP PROCEDURE IF EXISTS tpcb.trans;
252 DROP TABLE IF EXISTS tpcb.account;
253 DROP TABLE IF EXISTS tpcb.teller;
254 DROP TABLE IF EXISTS tpcb.branch;
255 DROP TABLE IF EXISTS tpcb.history;
256 DROP DATABASE tpcb;
257 ALTER TABLESPACE ts1
258 DROP DATAFILE 'datafile.dat';
259 ALTER TABLESPACE ts1
260 DROP DATAFILE 'datafile02.dat';
261 DROP TABLESPACE ts1;
262 ALTER TABLESPACE ts2
263 DROP DATAFILE 'datafile03.dat';
264 ALTER TABLESPACE ts2
265 DROP DATAFILE 'datafile04.dat';
266 DROP TABLESPACE ts2;
267 DROP LOGFILE GROUP lg1 ENGINE=NDB;
268 ********** Take a backup of the Master *************
269 SELECT COUNT(*) FROM history;
270 COUNT(*)
271 100
272 SELECT COUNT(*) FROM history;
273 COUNT(*)
274 200
275 ************ Restore the slave ************************
276 CREATE DATABASE tpcb;
277 SET GLOBAL ndb_metadata_sync = true;
278 ***** Check a few slave restore values ***************
279 USE tpcb;
280 SELECT COUNT(*) FROM account;
281 COUNT(*)
282 1000
283 ***** Add some more records to master *********
284 ***** Finsh the slave sync process *******
285 @the_epoch
286 <the_epoch>
287 @the_pos @the_file
288 <the_pos> master-bin.000001
289 CHANGE REPLICATION SOURCE TO
290 SOURCE_LOG_FILE = 'master-bin.000001',
291 SOURCE_LOG_POS = <the_pos> ;
292 * 4. *
293 * 5. *
294 START SLAVE;
295 Warnings:
296 Warning 1287 'START SLAVE' is deprecated and will be removed in a future release. Please use START REPLICA instead
297 **** We should be ready to continue on *************
298 ****** Let's make sure we match *******
299 ***** MASTER *******
300 USE tpcb;
301 SELECT COUNT(*) FROM history;
302 COUNT(*)
303 400
304 ****** SLAVE ********
305 USE tpcb;
306 SELECT COUNT(*) FROM history;
307 COUNT(*)
308 400
309 *** DUMP MASTER & SLAVE FOR COMPARE ********
310 *************** TEST 2 CLEANUP SECTION ********************
311 DROP PROCEDURE IF EXISTS tpcb.load;
312 DROP PROCEDURE IF EXISTS tpcb.trans;
313 DROP TABLE tpcb.account;
314 DROP TABLE tpcb.teller;
315 DROP TABLE tpcb.branch;
316 DROP TABLE tpcb.history;
317 DROP DATABASE tpcb;
318 ALTER TABLESPACE ts1
319 DROP DATAFILE 'datafile.dat';
320 ALTER TABLESPACE ts1
321 DROP DATAFILE 'datafile02.dat';
322 DROP TABLESPACE ts1;
323 ALTER TABLESPACE ts2
324 DROP DATAFILE 'datafile03.dat';
325 ALTER TABLESPACE ts2
326 DROP DATAFILE 'datafile04.dat';
327 DROP TABLESPACE ts2;
328 DROP LOGFILE GROUP lg1 ENGINE=NDB;
329 ****** Do dumps compare ************
330 include/rpl_end.inc