Debian Codegraph @

mysql-test/suite/ndb_rpl/t/ndb_rpl_dd_partitions.test

raw
1 #######################################
2 # Author: JBM #
3 # Date: 2006-03-09 #
4 # Purpose: To test the replication of #
5 # Cluster Disk Data using partitions #
6 #######################################
7
8 --source include/have_ndb.inc
9 --source include/have_binlog_format_mixed_or_row.inc
10 --source suite/ndb_rpl/ndb_master-slave.inc
11
12 # Start by creating a logfile group
13 ##################################
14
15 CREATE LOGFILE GROUP lg1
16 ADD UNDOFILE 'undofile.dat'
17 INITIAL_SIZE 16M
18 UNDO_BUFFER_SIZE = 1M
19 ENGINE=NDB;
20
21 ALTER LOGFILE GROUP lg1
22 ADD UNDOFILE 'undofile02.dat'
23 INITIAL_SIZE = 4M
24 ENGINE=NDB;
25
26 ###################################################
27 # Create a tablespace connected to the logfile group
28 ###################################################
29
30 CREATE TABLESPACE ts1
31 ADD DATAFILE 'datafile.dat'
32 USE LOGFILE GROUP lg1
33 INITIAL_SIZE 12M
34 ENGINE NDB;
35
36 ALTER TABLESPACE ts1
37 ADD DATAFILE 'datafile02.dat'
38 INITIAL_SIZE = 4M;
39
40 #################################################################
41
42 --echo --- Start test 2 partition RANGE testing --
43 --echo --- Do setup --
44
45
46 #################################################
47 # Requirment: Create table that is partitioned #
48 # by range on year i.e. year(t) and replicate #
49 # basice operations such at insert, update #
50 # delete between 2 different storage engines #
51 # Alter table and ensure table is handled #
52 # Correctly on the slave #
53 #################################################
54
55 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
56 bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
57 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
58 y YEAR, t DATE, primary key(t))
59 TABLESPACE ts1 STORAGE DISK
60 ENGINE=NDB
61 PARTITION BY RANGE (YEAR(t))
62 (PARTITION p0 VALUES LESS THAN (1901),
63 PARTITION p1 VALUES LESS THAN (1946),
64 PARTITION p2 VALUES LESS THAN (1966),
65 PARTITION p3 VALUES LESS THAN (1986),
66 PARTITION p4 VALUES LESS THAN (2005),
67 PARTITION p5 VALUES LESS THAN MAXVALUE);
68
69 --echo --- Show table on master ---
70
71 SHOW CREATE TABLE t1;
72
73 --echo --- Show table on slave --
74
75 sync_slave_with_master;
76 SHOW CREATE TABLE t1;
77
78 --echo --- Perform basic operation on master ---
79 --echo --- and ensure replicated correctly ---
80
81 --source include/rpl_multi_engine3.inc
82
83 --echo --- Check that simple Alter statements are replicated correctly ---
84
85 ALTER TABLE t1 MODIFY vc VARCHAR(255);
86
87 --echo --- Show the new improved table on the master ---
88
89 SHOW CREATE TABLE t1;
90
91 --echo --- Make sure that our tables on slave are still same engine ---
92 --echo --- and that the alter statements replicated correctly ---
93
94 sync_slave_with_master;
95 SHOW CREATE TABLE t1;
96
97 --echo --- Perform basic operation on master ---
98 --echo --- and ensure replicated correctly ---
99 --enable_query_log
100
101 --source include/rpl_multi_engine3.inc
102
103 --echo --- End test 2 partition RANGE testing ---
104 --echo --- Do Cleanup ---
105
106 DROP TABLE IF EXISTS t1;
107
108 ########################################################
109
110 --echo --- Start test 3 partition LIST testing ---
111 --echo --- Do setup ---
112 #################################################
113
114
115 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
116 bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
117 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
118 y YEAR, t DATE, primary key(id))
119 TABLESPACE ts1 STORAGE DISK
120 ENGINE=NDB
121 PARTITION BY LIST(id)
122 (PARTITION p0 VALUES IN (2, 4),
123 PARTITION p1 VALUES IN (42, 142));
124
125 --echo --- Test 3 Alter to add partition ---
126
127 ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
128
129 --echo --- Show table on master ---
130
131 SHOW CREATE TABLE t1;
132
133 --echo --- Show table on slave ---
134
135 sync_slave_with_master;
136 SHOW CREATE TABLE t1;
137
138 --echo --- Perform basic operation on master ---
139 --echo --- and ensure replicated correctly ---
140
141 --source include/rpl_multi_engine3.inc
142
143 --echo --- Check that simple Alter statements are replicated correctly ---
144
145 ALTER TABLE t1 MODIFY vc VARCHAR(255);
146
147 --echo --- Show the new improved table on the master ---
148
149 SHOW CREATE TABLE t1;
150
151 --echo --- Make sure that our tables on slave are still same engine ---
152 --echo --- and that the alter statements replicated correctly ---
153
154 sync_slave_with_master;
155 SHOW CREATE TABLE t1;
156
157 --echo --- Perform basic operation on master ---
158 --echo --- and ensure replicated correctly ---
159
160 --source include/rpl_multi_engine3.inc
161
162 --echo --- End test 3 partition LIST testing ---
163 --echo --- Do Cleanup --
164
165 DROP TABLE IF EXISTS t1;
166
167 ########################################################
168
169 --echo --- Start test 4 partition HASH testing ---
170 --echo --- Do setup ---
171 #################################################
172
173
174 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
175 bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
176 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
177 y YEAR, t DATE, primary key(t))
178 TABLESPACE ts1 STORAGE DISK
179 ENGINE=NDB
180 PARTITION BY HASH( YEAR(t) )
181 PARTITIONS 4;
182
183 --echo --- show that tables have been created correctly ---
184
185 SHOW CREATE TABLE t1;
186 sync_slave_with_master;
187 SHOW CREATE TABLE t1;
188
189 --echo --- Perform basic operation on master ---
190 --echo --- and ensure replicated correctly ---
191
192 --source include/rpl_multi_engine3.inc
193
194 --echo --- Check that simple Alter statements are replicated correctly ---
195
196 ALTER TABLE t1 MODIFY vc VARCHAR(255);
197
198 --echo --- Show the new improved table on the master ---
199
200 SHOW CREATE TABLE t1;
201
202 --echo --- Make sure that our tables on slave are still same engine ---
203 --echo --- and that the alter statements replicated correctly ---
204
205 sync_slave_with_master;
206 SHOW CREATE TABLE t1;
207
208 --echo --- Perform basic operation on master ---
209 --echo --- and ensure replicated correctly ---
210
211 --source include/rpl_multi_engine3.inc
212
213 --echo --- End test 4 partition HASH testing ---
214 --echo --- Do Cleanup --
215
216 DROP TABLE IF EXISTS t1;
217
218 ########################################################
219
220 --echo --- Start test 5 partition by key testing ---
221 --echo --- Create Table Section ---
222
223 #################################################
224
225 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(63),
226 bc CHAR(63), d DECIMAL(10,4) DEFAULT 0,
227 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
228 y YEAR, t DATE,PRIMARY KEY(id))
229 TABLESPACE ts1 STORAGE DISK
230 ENGINE=NDB
231 PARTITION BY KEY()
232 PARTITIONS 4;
233
234 --echo --- Show that tables on master are ndbcluster tables ---
235
236 SHOW CREATE TABLE t1;
237
238 --echo --- Show that tables on slave ---
239
240 sync_slave_with_master;
241 SHOW CREATE TABLE t1;
242
243 --echo --- Perform basic operation on master ---
244 --echo --- and ensure replicated correctly ---
245
246 --source include/rpl_multi_engine3.inc
247
248 # Okay lets see how it holds up to table changes
249 --echo --- Check that simple Alter statements are replicated correctly ---
250
251 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
252
253 --echo --- Show the new improved table on the master ---
254
255 SHOW CREATE TABLE t1;
256
257 --echo --- Make sure that our tables on slave are still right type ---
258 --echo --- and that the alter statements replicated correctly ---
259
260 sync_slave_with_master;
261 SHOW CREATE TABLE t1;
262
263 --echo --- Perform basic operation on master ---
264 --echo --- and ensure replicated correctly ---
265
266 --source include/rpl_multi_engine3.inc
267
268 --echo --- Check that simple Alter statements are replicated correctly ---
269
270 ALTER TABLE t1 MODIFY vc VARCHAR(255);
271
272 --echo --- Show the new improved table on the master ---
273
274 SHOW CREATE TABLE t1;
275
276 --echo --- Make sure that our tables on slave are still same engine ---
277 --echo --- and that the alter statements replicated correctly ---
278
279 sync_slave_with_master;
280 SHOW CREATE TABLE t1;
281
282 --echo --- Perform basic operation on master ---
283 --echo --- and ensure replicated correctly ---
284
285 --source include/rpl_multi_engine3.inc
286
287 --echo --- End test 5 key partition testing ---
288 --echo --- Do Cleanup ---
289
290 DROP TABLE IF EXISTS t1;
291 alter tablespace ts1
292 drop datafile 'datafile.dat';
293 alter tablespace ts1
294 drop datafile 'datafile02.dat';
295 DROP TABLESPACE ts1;
296 DROP LOGFILE GROUP lg1 ENGINE=NDB;
297 --sync_slave_with_master
298
299 # End of 5.1 test case
300 --source include/rpl_end.inc