Debian Codegraph @

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

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