Debian Codegraph @

mysql-test/suite/rpl/include/rpl_partition.test

raw
1 ############################################################
2 # Author: MATZ #
3 # Date: 2006-03-22 #
4 # Purpose: See if replication of partition tables work #
5 ############################################################
6
7 connection master;
8 --disable_warnings
9 DROP TABLE IF EXISTS t1;
10 --enable_query_log
11
12 --echo --- Start test 2 partition RANGE testing --
13
14 # Create table that is partitioned by range on year i.e. year(t) and
15 # replicate basice operations such at insert, update delete between 2
16 # different storage engines Alter table and ensure table is handled
17 # Correctly on the slave
18 # Note that the storage engine should not be explicit: the default
19 # storage engine is used on master and slave.
20
21 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
22 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
23 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
24 y YEAR, t DATE)
25 PARTITION BY RANGE (YEAR(t))
26 (PARTITION p0 VALUES LESS THAN (1901),
27 PARTITION p1 VALUES LESS THAN (1946),
28 PARTITION p2 VALUES LESS THAN (1966),
29 PARTITION p3 VALUES LESS THAN (1986),
30 PARTITION p4 VALUES LESS THAN (2005),
31 PARTITION p5 VALUES LESS THAN MAXVALUE);
32
33 --echo --- On master ---
34 SHOW CREATE TABLE t1;
35
36 --echo --- On slave --
37 sync_slave_with_master;
38 SHOW CREATE TABLE t1;
39
40 --source include/rpl_multi_engine3.inc
41
42 --echo --- Check that simple Alter statements are replicated correctly ---
43 ALTER TABLE t1 MODIFY vc TEXT;
44
45 --echo --- On master ---
46 SHOW CREATE TABLE t1;
47
48 --echo --- On slave ---
49 sync_slave_with_master;
50 SHOW CREATE TABLE t1;
51
52 --echo --- Perform basic operation on master ---
53 --echo --- and ensure replicated correctly ---
54 --enable_query_log
55
56 --source include/rpl_multi_engine3.inc
57
58 --echo --- End test 2 partition RANGE testing ---
59
60 DROP TABLE IF EXISTS t1;
61
62 ########################################################
63
64 --echo --- Start test 3 partition LIST testing ---
65 --echo --- Do setup ---
66 #################################################
67 # Requirment: Create table that is partitioned #
68 # by list on id i.e. (2,4). Pretend that we #
69 # missed one and alter to add. Then replicate #
70 # basice operations such at insert, update #
71 # delete between 2 different storage engines #
72 # Alter table and ensure table is handled #
73 # Correctly on the slave #
74 #################################################
75
76
77 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
78 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
79 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
80 y YEAR, t DATE)
81 PARTITION BY LIST(id)
82 (PARTITION p0 VALUES IN (2, 4),
83 PARTITION p1 VALUES IN (42, 142));
84
85 --echo --- Test 3 Alter to add partition ---
86
87 ALTER TABLE t1 ADD PARTITION (PARTITION p2 VALUES IN (412));
88
89 --echo --- Show table on master ---
90
91 SHOW CREATE TABLE t1;
92
93 --echo --- Show table on slave ---
94
95 sync_slave_with_master;
96 SHOW CREATE TABLE t1;
97
98 --echo --- Perform basic operation on master ---
99 --echo --- and ensure replicated correctly ---
100
101 --source include/rpl_multi_engine3.inc
102
103 --echo --- Check that simple Alter statements are replicated correctly ---
104
105 ALTER TABLE t1 MODIFY vc TEXT;
106
107 --echo --- Show the new improved table on the master ---
108
109 SHOW CREATE TABLE t1;
110
111 --echo --- Make sure that our tables on slave are still same engine ---
112 --echo --- and that the alter statements replicated correctly ---
113
114 sync_slave_with_master;
115 SHOW CREATE TABLE t1;
116
117 --echo --- Perform basic operation on master ---
118 --echo --- and ensure replicated correctly ---
119
120 --source include/rpl_multi_engine3.inc
121
122 --echo --- End test 3 partition LIST testing ---
123 --echo --- Do Cleanup --
124
125 DROP TABLE IF EXISTS t1;
126
127 ########################################################
128
129 --echo --- Start test 4 partition HASH testing ---
130 --echo --- Do setup ---
131 #################################################
132 # Requirment: Create table that is partitioned #
133 # by hash on year i.e. YEAR(t). Then replicate #
134 # basice operations such at insert, update #
135 # delete between 2 different storage engines #
136 # Alter table and ensure table is handled #
137 # Correctly on the slave #
138 #################################################
139
140
141 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
142 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
143 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
144 y YEAR, t DATE)
145 PARTITION BY HASH( YEAR(t) )
146 PARTITIONS 4;
147
148 --echo --- show that tables have been created correctly ---
149
150 SHOW CREATE TABLE t1;
151 sync_slave_with_master;
152 SHOW CREATE TABLE t1;
153
154 --echo --- Perform basic operation on master ---
155 --echo --- and ensure replicated correctly ---
156
157 --source include/rpl_multi_engine3.inc
158
159 --echo --- Check that simple Alter statements are replicated correctly ---
160
161 ALTER TABLE t1 MODIFY vc TEXT;
162
163 --echo --- Show the new improved table on the master ---
164
165 SHOW CREATE TABLE t1;
166
167 --echo --- Make sure that our tables on slave are still same engine ---
168 --echo --- and that the alter statements replicated correctly ---
169
170 sync_slave_with_master;
171 SHOW CREATE TABLE t1;
172
173 --echo --- Perform basic operation on master ---
174 --echo --- and ensure replicated correctly ---
175
176 --source include/rpl_multi_engine3.inc
177
178 --echo --- End test 4 partition HASH testing ---
179 --echo --- Do Cleanup --
180
181 DROP TABLE IF EXISTS t1;
182
183 ########################################################
184
185 --echo --- Start test 5 partition by key testing ---
186 --echo --- Create Table Section ---
187
188 #################################################
189 # Requirment: Create table that is partitioned #
190 # by key on id with 4 parts. Then replicate #
191 # basice operations such at insert, update #
192 # delete between 2 different storage engines #
193 # Alter table and ensure table is handled #
194 # Correctly on the slave #
195 #################################################
196
197 CREATE TABLE t1 (id MEDIUMINT NOT NULL, b1 BIT(8), vc VARCHAR(255),
198 bc CHAR(255), d DECIMAL(10,4) DEFAULT 0,
199 f FLOAT DEFAULT 0, total BIGINT UNSIGNED,
200 y YEAR, t DATE,PRIMARY KEY(id))
201 PARTITION BY KEY()
202 PARTITIONS 4;
203
204 --echo --- Show tables on master ---
205
206 SHOW CREATE TABLE t1;
207
208 --echo --- Show tables on slave ---
209
210 sync_slave_with_master;
211 SHOW CREATE TABLE t1;
212
213 --echo --- Perform basic operation on master ---
214 --echo --- and ensure replicated correctly ---
215
216 --source include/rpl_multi_engine3.inc
217
218 # Okay lets see how it holds up to table changes
219 --echo --- Check that simple Alter statements are replicated correctly ---
220
221 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id, total);
222
223 --echo --- Show the new improved table on the master ---
224
225 SHOW CREATE TABLE t1;
226
227 --echo --- Make sure that our tables on slave are still right type ---
228 --echo --- and that the alter statements replicated correctly ---
229
230 sync_slave_with_master;
231 SHOW CREATE TABLE t1;
232
233 --echo --- Perform basic operation on master ---
234 --echo --- and ensure replicated correctly ---
235
236 --source include/rpl_multi_engine3.inc
237
238 --echo --- Check that simple Alter statements are replicated correctly ---
239
240 ALTER TABLE t1 MODIFY vc TEXT;
241
242 --echo --- Show the new improved table on the master ---
243
244 SHOW CREATE TABLE t1;
245
246 --echo --- Make sure that our tables on slave are still same engine ---
247 --echo --- and that the alter statements replicated correctly ---
248
249 sync_slave_with_master;
250 SHOW CREATE TABLE t1;
251
252 --echo --- Perform basic operation on master ---
253 --echo --- and ensure replicated correctly ---
254
255 --source include/rpl_multi_engine3.inc
256
257 --echo --- End test 5 key partition testing ---
258 --echo --- Do Cleanup ---
259
260 DROP TABLE IF EXISTS t1;
261
262 # End of 5.1 test case