Debian Codegraph @

mysql-test/suite/parts/r/partition_exchange_innodb.result

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