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