Debian Codegraph @

mysql-test/suite/parts/inc/partition_exchange.inc

raw
1 # Include file to test PARTITION EXCHANGE usable with different engines
2 # Written by [email protected]
3
4 --echo # Test with AUTO_INCREMENT
5 eval CREATE TABLE tp
6 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
7 b varchar(24))
8 ENGINE = $engine
9 PARTITION BY HASH (a) PARTITIONS 4;
10 CREATE TABLE t LIKE tp;
11 ALTER TABLE t REMOVE PARTITIONING;
12 SHOW CREATE TABLE tp;
13 SHOW CREATE TABLE t;
14 INSERT INTO tp (b) VALUES ("One"), ("Two"), ("Three"), ("Four"), ("Five"),
15 ("Six"), ("Seven"), ("Eight"), ("Nine"), ("Ten"), ("Eleven"), ("Twelwe");
16 if ($no_update)
17 {
18 # Archive handles auto inc by only allowing increasing values,
19 # so this must be inserted before a higher value is inserted.
20 INSERT INTO tp VALUES (41, "One hundred one");
21 }
22 INSERT INTO tp VALUES (97, "Ninety seven");
23 if (!$no_delete)
24 {
25 INSERT INTO tp VALUES (111, "One hundred eleven");
26 }
27 if (!$no_update)
28 {
29 INSERT INTO tp VALUES (101, "One hundred one");
30 }
31 SET INSERT_ID = 13;
32 INSERT INTO t (b) VALUES ("Thirteen");
33 if ($no_update)
34 {
35 SET INSERT_ID = 17;
36 INSERT INTO t (b) VALUES ("Twenty five");
37 }
38 SET INSERT_ID = 21;
39 INSERT INTO t (b) VALUES ("Twenty one");
40 if (!$no_update)
41 {
42 SET INSERT_ID = 25;
43 INSERT INTO t (b) VALUES ("Twenty five");
44 }
45 if (!$no_delete)
46 {
47 SET INSERT_ID = 55;
48 INSERT INTO t (b) VALUES ("Fifty five");
49 DELETE FROM tp WHERE a = 111;
50 DELETE FROM t WHERE a = 55;
51 }
52 if (!$no_update)
53 {
54 UPDATE tp SET a = 41 WHERE a = 101;
55 UPDATE t SET a = 17 WHERE a = 25;
56 }
57 -- disable_query_log
58 -- disable_result_log
59 ANALYZE TABLE tp;
60 ANALYZE TABLE t;
61 -- enable_result_log
62 -- enable_query_log
63 --sorted_result
64 SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 'tp';
65 SELECT IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 't';
66 ALTER TABLE tp EXCHANGE PARTITION p1 WITH TABLE t;
67 -- disable_query_log
68 -- disable_result_log
69 ANALYZE TABLE tp;
70 -- enable_result_log
71 -- enable_query_log
72 --sorted_result
73 SELECT PARTITION_NAME, IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 'tp';
74 SELECT IF(TABLE_ROWS, 'YES', 'NO') AS HAVE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='test' AND TABLE_NAME = 't';
75 SELECT * FROM tp ORDER BY a;
76 SELECT * FROM t ORDER BY a;
77 SHOW CREATE TABLE tp;
78 SHOW CREATE TABLE t;
79 DROP TABLE tp, t;
80
81 # set a DEBUG_SYNC after open table and before verifying is done
82 # Test that one can read from the table but not write, test that one can both
83 # read and write to the partition
84 # Verify that one must close the other instances of the table before the rename
85 # takes place (i.e. no need for LOCK_open)
86 # Test that it is not possible to drop, create, truncate either the tables or
87 # partition
88 # Test that it waits for ongoing transactions
89
90 if ($no_keys)
91 {
92 eval CREATE TABLE t
93 (a INT,
94 b VARCHAR(55))
95 ENGINE = $engine;
96 eval CREATE TABLE tp
97 (a INT,
98 b VARCHAR(55))
99 ENGINE = $engine
100 PARTITION BY RANGE (a)
101 (PARTITION p0 VALUES LESS THAN (100),
102 PARTITION p1 VALUES LESS THAN MAXVALUE);
103 eval CREATE TABLE tsp
104 (a INT,
105 b VARCHAR(55))
106 ENGINE = $engine
107 PARTITION BY RANGE (a)
108 SUBPARTITION BY HASH(a)
109 (PARTITION p0 VALUES LESS THAN (100)
110 (SUBPARTITION sp0,
111 SUBPARTITION sp1),
112 PARTITION p1 VALUES LESS THAN MAXVALUE
113 (SUBPARTITION sp2,
114 SUBPARTITION sp3));
115 }
116 if (!$no_keys)
117 {
118 eval CREATE TABLE t
119 (a INT,
120 b VARCHAR(55),
121 PRIMARY KEY (a))
122 ENGINE = $engine;
123 eval CREATE TABLE tp
124 (a INT,
125 b VARCHAR(55),
126 PRIMARY KEY (a))
127 ENGINE = $engine
128 PARTITION BY RANGE (a)
129 (PARTITION p0 VALUES LESS THAN (100),
130 PARTITION p1 VALUES LESS THAN MAXVALUE);
131 eval CREATE TABLE tsp
132 (a INT,
133 b VARCHAR(55),
134 PRIMARY KEY (a))
135 ENGINE = $engine
136 PARTITION BY RANGE (a)
137 SUBPARTITION BY HASH(a)
138 (PARTITION p0 VALUES LESS THAN (100)
139 (SUBPARTITION sp0,
140 SUBPARTITION sp1),
141 PARTITION p1 VALUES LESS THAN MAXVALUE
142 (SUBPARTITION sp2,
143 SUBPARTITION sp3));
144 }
145
146 INSERT INTO t VALUES (1, "First value"), (3, "Three"), (5, "Five"), (99, "End of values");
147 INSERT INTO tp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164");
148 INSERT INTO tsp VALUES (2, "First value"), (10, "Ten"), (50, "Fifty"), (200, "Two hundred, end of values"), (61, "Sixty one"), (62, "Sixty two"), (63, "Sixty three"), (64, "Sixty four"), (161, "161"), (162, "162"), (163, "163"), (164, "164");
149 --sorted_result
150 SELECT * FROM t;
151 --sorted_result
152 SELECT * FROM tp;
153
154 --echo # Start by testing read/write locking
155 SET AUTOCOMMIT = 1;
156
157 connect(con1, localhost, root,,);
158 --echo # con1
159 SET DEBUG_SYNC= 'swap_partition_after_compare_tables SIGNAL swap_in_progress WAIT_FOR goto_verification';
160 SET DEBUG_SYNC= 'swap_partition_first_row_read SIGNAL swap_in_progress WAIT_FOR goto_wait';
161 SET DEBUG_SYNC= 'swap_partition_after_wait SIGNAL swap_in_progress WAIT_FOR goto_exchange';
162 SET DEBUG_SYNC= 'swap_partition_before_exchange SIGNAL swap_in_progress WAIT_FOR test_done';
163 send ALTER TABLE tp EXCHANGE PARTITION p0 WITH TABLE t;
164
165 connection default;
166 --echo # con default
167 SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
168 --echo # select from t and select/update/delete/insert from tp should work
169 SELECT * FROM t WHERE a = 99;
170 SELECT * FROM tp WHERE a = 61;
171 --echo # any write (update/delete/insert) into t or tp should fail
172 SET SESSION lock_wait_timeout=1;
173 if (!$no_update)
174 {
175 --error ER_LOCK_WAIT_TIMEOUT
176 UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63;
177 }
178 --error ER_LOCK_WAIT_TIMEOUT
179 INSERT INTO tp VALUES (63, "Sixty three, new"), (59, "To be deleted");
180 if (!$no_delete)
181 {
182 --error ER_LOCK_WAIT_TIMEOUT
183 DELETE FROM tp WHERE a = 59;
184 }
185 if (!$no_update)
186 {
187 --error ER_LOCK_WAIT_TIMEOUT
188 UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
189 }
190 --error ER_LOCK_WAIT_TIMEOUT
191 INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
192 if (!$no_delete)
193 {
194 --error ER_LOCK_WAIT_TIMEOUT
195 DELETE FROM t WHERE a = 3;
196 }
197 --error ER_LOCK_WAIT_TIMEOUT
198 eval ALTER TABLE t ENGINE = $engine;
199 --error ER_LOCK_WAIT_TIMEOUT
200 eval ALTER TABLE tp ENGINE = $engine;
201 SHOW CREATE TABLE t;
202 SHOW CREATE TABLE tp;
203 SET DEBUG_SYNC= 'now SIGNAL goto_verification';
204 SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
205 --echo # select from t and select/update/delete/insert from tp should work
206 SELECT * FROM t WHERE a = 99;
207 SELECT * FROM tp WHERE a = 61;
208 if (!$no_update)
209 {
210 --error ER_LOCK_WAIT_TIMEOUT
211 UPDATE tp SET a = 43, b = concat("Fifty three, was ", b) WHERE a = 63;
212 }
213 --error ER_LOCK_WAIT_TIMEOUT
214 INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted");
215 if (!$no_delete)
216 {
217 --error ER_LOCK_WAIT_TIMEOUT
218 DELETE FROM tp WHERE a = 59;
219 }
220 --echo # any write (update/delete/insert) into t should fail
221 if (!$no_update)
222 {
223 --error ER_LOCK_WAIT_TIMEOUT
224 UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
225 }
226 --error ER_LOCK_WAIT_TIMEOUT
227 INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
228 if (!$no_delete)
229 {
230 --error ER_LOCK_WAIT_TIMEOUT
231 DELETE FROM t WHERE a = 3;
232 }
233 --error ER_LOCK_WAIT_TIMEOUT
234 eval ALTER TABLE t ENGINE = $engine;
235 --error ER_LOCK_WAIT_TIMEOUT
236 eval ALTER TABLE tp ENGINE = $engine;
237 SHOW CREATE TABLE t;
238 SHOW CREATE TABLE tp;
239 SET DEBUG_SYNC= 'now SIGNAL goto_wait';
240 SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
241 --echo # Both tables should now be under exclusive lock, even SHOW should fail
242 --error ER_LOCK_WAIT_TIMEOUT
243 SELECT * FROM t WHERE a = 99;
244 --error ER_LOCK_WAIT_TIMEOUT
245 SELECT * FROM tp WHERE a = 61;
246 if (!$no_update)
247 {
248 --error ER_LOCK_WAIT_TIMEOUT
249 UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63;
250 }
251 --error ER_LOCK_WAIT_TIMEOUT
252 INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted");
253 if (!$no_delete)
254 {
255 --error ER_LOCK_WAIT_TIMEOUT
256 DELETE FROM tp WHERE a = 59;
257 }
258 if (!$no_update)
259 {
260 --error ER_LOCK_WAIT_TIMEOUT
261 UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
262 }
263 --error ER_LOCK_WAIT_TIMEOUT
264 INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
265 if (!$no_delete)
266 {
267 --error ER_LOCK_WAIT_TIMEOUT
268 DELETE FROM t WHERE a = 3;
269 }
270 --error ER_LOCK_WAIT_TIMEOUT
271 SHOW CREATE TABLE t;
272 --error ER_LOCK_WAIT_TIMEOUT
273 SHOW CREATE TABLE tp;
274 --error ER_LOCK_WAIT_TIMEOUT
275 eval ALTER TABLE t ENGINE = $engine;
276 --error ER_LOCK_WAIT_TIMEOUT
277 eval ALTER TABLE tp ENGINE = $engine;
278 SET DEBUG_SYNC= 'now SIGNAL goto_exchange';
279 SET DEBUG_SYNC= 'now WAIT_FOR swap_in_progress';
280 --echo # Both tables should now be under exclusive lock
281 --error ER_LOCK_WAIT_TIMEOUT
282 SELECT * FROM t WHERE a = 99;
283 --error ER_LOCK_WAIT_TIMEOUT
284 SELECT * FROM tp WHERE a = 61;
285 if (!$no_update)
286 {
287 --error ER_LOCK_WAIT_TIMEOUT
288 UPDATE tp SET a = 53, b = concat("Fifty three, was ", b) WHERE a = 63;
289 }
290 --error ER_LOCK_WAIT_TIMEOUT
291 INSERT INTO tp VALUES (63, "Sixty three, new 2"), (59, "To be deleted");
292 if (!$no_delete)
293 {
294 --error ER_LOCK_WAIT_TIMEOUT
295 DELETE FROM tp WHERE a = 59;
296 }
297 if (!$no_update)
298 {
299 --error ER_LOCK_WAIT_TIMEOUT
300 UPDATE t SET a = 53, b = "Fifty three, was three" WHERE a = 3;
301 }
302 --error ER_LOCK_WAIT_TIMEOUT
303 INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
304 if (!$no_delete)
305 {
306 --error ER_LOCK_WAIT_TIMEOUT
307 DELETE FROM t WHERE a = 3;
308 }
309 --error ER_LOCK_WAIT_TIMEOUT
310 eval ALTER TABLE t ENGINE = $engine;
311 --error ER_LOCK_WAIT_TIMEOUT
312 eval ALTER TABLE tp ENGINE = $engine;
313 --error ER_LOCK_WAIT_TIMEOUT
314 SHOW CREATE TABLE t;
315 --error ER_LOCK_WAIT_TIMEOUT
316 SHOW CREATE TABLE tp;
317
318 SET DEBUG_SYNC= 'now SIGNAL test_done';
319
320 connection con1;
321 --echo # con1
322 --reap
323 connection default;
324 --echo # con default
325 --echo # Tables should now be as normal
326 SHOW CREATE TABLE t;
327 SHOW CREATE TABLE tp;
328 SELECT * FROM tp WHERE a = 99;
329 SELECT * FROM t WHERE a = 61;
330 if (!$no_update)
331 {
332 UPDATE t SET a = 53, b = "Fifty three, was sixty three" WHERE a = 63;
333 }
334 INSERT INTO t VALUES (63, "Sixty three, new"), (59, "To be deleted");
335 if (!$no_delete)
336 {
337 DELETE FROM t WHERE a = 59;
338 }
339 if (!$no_update)
340 {
341 UPDATE tp SET a = 53, b = "Fifty three, was three" WHERE a = 3;
342 }
343 INSERT INTO tp VALUES (63, "Sixty three, new"), (59, "To be deleted");
344 if (!$no_delete)
345 {
346 DELETE FROM tp WHERE a = 3;
347 }
348 eval ALTER TABLE t ENGINE = $engine;
349 eval ALTER TABLE tp ENGINE = $engine;
350
351 disconnect con1;
352 connection default;
353 --echo # con default
354 SET DEBUG_SYNC= 'RESET';
355 SHOW CREATE TABLE t;
356 SHOW CREATE TABLE tp;
357 --sorted_result
358 SELECT * FROM t;
359 --sorted_result
360 SELECT * FROM tp;
361
362 DROP TABLE t, tp, tsp;
363
364