Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_2multi_eng.inc

raw
1 #######################################
2 # Author: JBM #
3 # Date: 2006-02-23 #
4 # Purpose: See if replication between #
5 # NDB -> MyISAM and InnoDB works. #
6 # and if #
7 # MyISAM and InnoDB -> NDB works. #
8 #######################################
9 # By JBM #
10 # Date 2006-02-28 #
11 # Change: Implemented review comments #
12 #######################################
13
14 ###
15 # The MyISAM engine does not support native partitioning
16 # Set flag to allow testcases which use native partitioning
17 # to be skipped if either slave or master does not support feature
18 connection slave;
19 let $supports_native_partitioning = 1;
20 if (`select @@default_storage_engine = "MyISAM"`)
21 {
22 let $supports_native_partitioning = 0;
23 }
24
25 connection master;
26 if (`select @@default_storage_engine = "MyISAM"`)
27 {
28 let $supports_native_partitioning = 0;
29 }
30 echo supports_native_partitioning: $supports_native_partitioning;
31
32 ###
33
34 connection master;
35
36 --echo --- Start test 1 Basic testing ---
37 --echo --- Create Table Section ---
38
39 #################################################
40 # Requirment: Create basic table, replicate #
41 # basice operations such at insert, update #
42 # delete between 2 different storage engines #
43 # Alter table and ensure table is handled #
44 # Correctly on the slave #
45 #################################################
46
47 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
48 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
49 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
50 y YEAR, t DATE,PRIMARY KEY(id));
51
52 --echo --- Show table on master ---
53
54 SHOW CREATE TABLE t1;
55
56 --echo --- Show table on slave ---
57
58 sync_slave_with_master;
59 SHOW CREATE TABLE t1;
60
61 --echo --- Perform basic operation on master ---
62 --echo --- and ensure replicated correctly ---
63
64 --source include/rpl_multi_engine3.inc
65
66 # Okay lets see how it holds up to table changes
67 --echo --- Check that simple Alter statements are replicated correctly --
68
69 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
70 ALTER TABLE t1 MODIFY vc TEXT;
71
72 --echo --- Show the new improved table on the master ---
73
74 SHOW CREATE TABLE t1;
75
76 --echo --- Make sure that our tables on slave are still same engine ---
77 --echo --- and that the alter statements replicated correctly ---
78
79 sync_slave_with_master;
80 SHOW CREATE TABLE t1;
81
82 --echo --- Perform basic operation on master ---
83 --echo --- and ensure replicated correctly ---
84
85 --source include/rpl_multi_engine3.inc
86
87 --echo --- End test 1 Basic testing ---
88 --echo --- Do Cleanup --
89
90 DROP TABLE IF EXISTS t1;
91
92 #################################################################
93
94 if ($supports_native_partitioning)
95 {
96
97 --echo --- Start test 2 partition RANGE testing --
98 --echo --- Do setup --
99
100
101 #################################################
102 # Requirment: Create table that is partitioned #
103 # by range on year i.e. year(t) and replicate #
104 # basice operations such at insert, update #
105 # delete between 2 different storage engines #
106 # Alter table and ensure table is handled #
107 # Correctly on the slave #
108 #################################################
109
110 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
111 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
112 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
113 y YEAR, t DATE, primary key(t))
114 PARTITION BY RANGE (YEAR(t))
115 (PARTITION p0 VALUES LESS THAN (1901),
116 PARTITION p1 VALUES LESS THAN (1946),
117 PARTITION p2 VALUES LESS THAN (1966),
118 PARTITION p3 VALUES LESS THAN (1986),
119 PARTITION p4 VALUES LESS THAN (2005),
120 PARTITION p5 VALUES LESS THAN MAXVALUE);
121
122 --echo --- Show table on master ---
123
124 SHOW CREATE TABLE t1;
125
126 --echo --- Show table on slave --
127
128 sync_slave_with_master;
129 SHOW CREATE TABLE t1;
130
131 --echo --- Perform basic operation on master ---
132 --echo --- and ensure replicated correctly ---
133
134 --source include/rpl_multi_engine3.inc
135
136 --echo --- Check that simple Alter statements are replicated correctly ---
137
138 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(t, id);
139 ALTER TABLE t1 MODIFY vc TEXT;
140
141 --echo --- Show the new improved table on the master ---
142
143 SHOW CREATE TABLE t1;
144
145 --echo --- Make sure that our tables on slave are still same engine ---
146 --echo --- and that the alter statements replicated correctly ---
147
148 sync_slave_with_master;
149 SHOW CREATE TABLE t1;
150
151 --echo --- Perform basic operation on master ---
152 --echo --- and ensure replicated correctly ---
153 --enable_query_log
154
155 --source include/rpl_multi_engine3.inc
156
157 --echo --- End test 2 partition RANGE testing ---
158 --echo --- Do Cleanup ---
159
160 DROP TABLE t1;
161
162 }
163
164 ########################################################
165
166 if ($supports_native_partitioning)
167 {
168
169 --echo --- Start test 3 partition LIST testing ---
170 --echo --- Do setup ---
171 #################################################
172 # Requirment: Create table that is partitioned #
173 # by list on id i.e. (2,4). Pretend that we #
174 # missed one and alter to add. Then replicate #
175 # basice operations such at insert, update #
176 # delete between 2 different storage engines #
177 # Alter table and ensure table is handled #
178 # Correctly on the slave #
179 #################################################
180
181
182 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
183 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
184 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
185 y YEAR, t DATE, primary key(id))
186 PARTITION BY LIST(id)
187 (PARTITION p0 VALUES IN (2, 4),
188 PARTITION p1 VALUES IN (42, 142));
189
190 --echo --- Test 3 Alter to add partition ---
191
192 ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
193
194 --echo --- Show table on master ---
195
196 SHOW CREATE TABLE t1;
197
198 --echo --- Show table on slave ---
199
200 sync_slave_with_master;
201 SHOW CREATE TABLE t1;
202
203 --echo --- Perform basic operation on master ---
204 --echo --- and ensure replicated correctly ---
205
206 --source include/rpl_multi_engine3.inc
207
208 --echo --- Check that simple Alter statements are replicated correctly ---
209
210 ALTER TABLE t1 MODIFY vc TEXT;
211
212 --echo --- Show the new improved table on the master ---
213
214 SHOW CREATE TABLE t1;
215
216 --echo --- Make sure that our tables on slave are still same engine ---
217 --echo --- and that the alter statements replicated correctly ---
218
219 sync_slave_with_master;
220 SHOW CREATE TABLE t1;
221
222 --echo --- Perform basic operation on master ---
223 --echo --- and ensure replicated correctly ---
224
225 --source include/rpl_multi_engine3.inc
226
227 --echo --- End test 3 partition LIST testing ---
228 --echo --- Do Cleanup --
229
230 DROP TABLE t1;
231
232 }
233
234 ########################################################
235
236 if ($supports_native_partitioning)
237 {
238
239 --echo --- Start test 4 partition HASH testing ---
240 --echo --- Do setup ---
241 #################################################
242 # Requirment: Create table that is partitioned #
243 # by hash on year i.e. YEAR(t). Then replicate #
244 # basice operations such at insert, update #
245 # delete between 2 different storage engines #
246 # Alter table and ensure table is handled #
247 # Correctly on the slave #
248 #################################################
249
250
251 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
252 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
253 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
254 y YEAR, t DATE, primary key(t))
255 PARTITION BY HASH( YEAR(t) )
256 PARTITIONS 4;
257
258 --echo --- show that tables have been created correctly ---
259
260 SHOW CREATE TABLE t1;
261 sync_slave_with_master;
262 SHOW CREATE TABLE t1;
263
264 --echo --- Perform basic operation on master ---
265 --echo --- and ensure replicated correctly ---
266
267 --source include/rpl_multi_engine3.inc
268
269 --echo --- Check that simple Alter statements are replicated correctly ---
270
271 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(t,id);
272 ALTER TABLE t1 MODIFY vc TEXT;
273
274 --echo --- Show the new improved table on the master ---
275
276 SHOW CREATE TABLE t1;
277
278 --echo --- Make sure that our tables on slave are still same engine ---
279 --echo --- and that the alter statements replicated correctly ---
280
281 sync_slave_with_master;
282 SHOW CREATE TABLE t1;
283
284 --echo --- Perform basic operation on master ---
285 --echo --- and ensure replicated correctly ---
286
287 --source include/rpl_multi_engine3.inc
288
289 --echo --- End test 4 partition HASH testing ---
290 --echo --- Do Cleanup --
291
292 DROP TABLE t1;
293
294 }
295
296 ########################################################
297
298 if ($supports_native_partitioning)
299 {
300
301 --echo --- Start test 5 partition by key testing ---
302 --echo --- Create Table Section ---
303
304 #################################################
305 # Requirment: Create table that is partitioned #
306 # by key on id with 4 parts. Then replicate #
307 # basice operations such at insert, update #
308 # delete between 2 different storage engines #
309 # Alter table and ensure table is handled #
310 # Correctly on the slave #
311 #################################################
312
313 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
314 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
315 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
316 y YEAR, t DATE,PRIMARY KEY(id))
317 PARTITION BY KEY()
318 PARTITIONS 4;
319
320 --echo --- Show that tables on master are ndbcluster tables ---
321
322 SHOW CREATE TABLE t1;
323
324 --echo --- Show that tables on slave ---
325
326 sync_slave_with_master;
327 SHOW CREATE TABLE t1;
328
329 --echo --- Perform basic operation on master ---
330 --echo --- and ensure replicated correctly ---
331
332 --source include/rpl_multi_engine3.inc
333
334 # Okay lets see how it holds up to table changes
335 --echo --- Check that simple Alter statements are replicated correctly ---
336
337 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
338
339 --echo --- Show the new improved table on the master ---
340
341 SHOW CREATE TABLE t1;
342
343 --echo --- Make sure that our tables on slave are still right type ---
344 --echo --- and that the alter statements replicated correctly ---
345
346 sync_slave_with_master;
347 SHOW CREATE TABLE t1;
348
349 --echo --- Perform basic operation on master ---
350 --echo --- and ensure replicated correctly ---
351
352 --source include/rpl_multi_engine3.inc
353
354 --echo --- Check that simple Alter statements are replicated correctly ---
355
356 ALTER TABLE t1 MODIFY vc TEXT;
357
358 --echo --- Show the new improved table on the master ---
359
360 SHOW CREATE TABLE t1;
361
362 --echo --- Make sure that our tables on slave are still same engine ---
363 --echo --- and that the alter statements replicated correctly ---
364
365 sync_slave_with_master;
366 SHOW CREATE TABLE t1;
367
368 --echo --- Perform basic operation on master ---
369 --echo --- and ensure replicated correctly ---
370
371 --source include/rpl_multi_engine3.inc
372
373 --echo --- End test 5 key partition testing ---
374 --echo --- Do Cleanup ---
375
376 DROP TABLE t1;
377 sync_slave_with_master;
378
379 }