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