Debian Codegraph @

mysql-test/suite/innodb/r/instant_ddl_limitations.result

raw
1 #
2 # Scenario 1:
3 # Create a small table with row_format compressed and adding/dropping instant column should fail
4 #
5 CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=COMPRESSED;
6 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
7 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 5, ALGORITHM=INSTANT;
8 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
9 ALTER TABLE t1 DROP COLUMN a, ALGORITHM=INSTANT;
10 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
11 DROP TABLE t1;
12 #
13 # Scenario 2:
14 # Create a small table and adding spatial types with not null should fail
15 #
16 CREATE TABLE t1 (a INT, b INT);
17 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
18 ALTER TABLE t1 ADD COLUMN c1 POINT NOT NULL, ALGORITHM = INSTANT;
19 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
20 ALTER TABLE t1 ADD COLUMN d1 LINESTRING NOT NULL, ALGORITHM = INSTANT;
21 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
22 ALTER TABLE t1 ADD COLUMN e1 POLYGON NOT NULL, ALGORITHM = INSTANT;
23 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
24 ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT NOT NULL, ALGORITHM = INSTANT;
25 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
26 ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING NOT NULL, ALGORITHM = INSTANT;
27 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
28 ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON NOT NULL, ALGORITHM = INSTANT;
29 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
30 ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION NOT NULL, ALGORITHM = INSTANT;
31 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
32 ALTER TABLE t1 ADD COLUMN j1 GEOMETRY NOT NULL, ALGORITHM = INSTANT;
33 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
34 DROP TABLE t1;
35 #
36 # Scenario 3:
37 # Create a temporary table and adding/dropping instant column should fail
38 #
39 CREATE TEMPORARY TABLE tmp1(a INT, b INT);
40 ALTER TABLE tmp1 ADD COLUMN c int, ALGORITHM=INSTANT;
41 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.
42 ALTER TABLE tmp1 DROP COLUMN b, ALGORITHM=INSTANT;
43 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY.
44 DROP TABLE tmp1;
45 #
46 # Scenario 4:
47 # Trying to add/drop instant columns for data-dictionary tables should fail
48 #
49 ALTER TABLE mysql.plugin ADD COLUMN c1 INT, ALGORITHM=INSTANT;
50 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
51 ALTER TABLE mysql.plugin DROP COLUMN dl, ALGORITHM=INSTANT;
52 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
53 ALTER TABLE mysql.user ADD COLUMN c1 INT, ALGORITHM=INSTANT;
54 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
55 ALTER TABLE mysql.user DROP COLUMN Select_priv, ALGORITHM=INSTANT;
56 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
57 #
58 # Scenario 5:
59 # Trying to add/drop instant column to a table having FTS index
60 #
61 CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a,b));
62 INSERT INTO t1 VALUES('test1', 'test2');
63 ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INSTANT;
64 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACE.
65 ALTER TABLE t1 DROP COLUMN b, ALGORITHM=INSTANT;
66 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACE.
67 DROP TABLE t1;
68 #
69 # Scenario 6:
70 # INSTANT ADD/DROP column with key
71 #
72 CREATE TABLE t1 (c1 INT, c2 INT);
73 ALTER TABLE t1 ADD COLUMN c3 INT KEY, ALGORITHM=INSTANT;
74 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
75 DROP TABLE t1;
76 CREATE TABLE t1 (c1 INT KEY, c2 INT);
77 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT;
78 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY/INPLACE.
79 ALTER TABLE t1 DROP COLUMN c1, ADD PRIMARY KEY (c2), ALGORITHM=INSTANT;
80 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
81 DROP TABLE t1;
82 CREATE TABLE t1 (c1 INT KEY, c2 INT);
83 ALTER TABLE t1 ADD COLUMN c3 INT UNIQUE KEY, ALGORITHM=INSTANT;
84 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
85 ALTER TABLE t1 ADD COLUMN c3 INT, ADD INDEX idx (c3), ALGORITHM=INSTANT;
86 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
87 DROP TABLE t1;
88 CREATE TABLE t1 (c1 INT KEY, c2 INT, INDEX idx(c2));
89 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
90 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
91 DROP TABLE t1;
92 #
93 # Scenario 7:
94 # Maximum row versions (MAX_ROW_VERSION is 64)
95 #
96 CREATE TABLE t1 (c1 int);
97 # INSTANT ADD COLUMNS to table to make it reach the maximum versions
98 # Table TOTAL_ROW_VERSIONS should be 64 (MAX)
99 # Metadata from INFORMATION_SCHEMA.TABLES
100 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
101 test/t1 68 0 64
102 # Metadata from INFORMATION_SCHEMA.COLUMNS
103 NAME POS MTYPE PRTYPE HAS_DEFAULT
104 c1 0 6 1027 0
105 c2 1 6 1027 1
106 c3 2 6 1027 1
107 c4 3 6 1027 1
108 c5 4 6 1027 1
109 c6 5 6 1027 1
110 c7 6 6 1027 1
111 c8 7 6 1027 1
112 c9 8 6 1027 1
113 c10 9 6 1027 1
114 c11 10 6 1027 1
115 c12 11 6 1027 1
116 c13 12 6 1027 1
117 c14 13 6 1027 1
118 c15 14 6 1027 1
119 c16 15 6 1027 1
120 c17 16 6 1027 1
121 c18 17 6 1027 1
122 c19 18 6 1027 1
123 c20 19 6 1027 1
124 c21 20 6 1027 1
125 c22 21 6 1027 1
126 c23 22 6 1027 1
127 c24 23 6 1027 1
128 c25 24 6 1027 1
129 c26 25 6 1027 1
130 c27 26 6 1027 1
131 c28 27 6 1027 1
132 c29 28 6 1027 1
133 c30 29 6 1027 1
134 c31 30 6 1027 1
135 c32 31 6 1027 1
136 c33 32 6 1027 1
137 c34 33 6 1027 1
138 c35 34 6 1027 1
139 c36 35 6 1027 1
140 c37 36 6 1027 1
141 c38 37 6 1027 1
142 c39 38 6 1027 1
143 c40 39 6 1027 1
144 c41 40 6 1027 1
145 c42 41 6 1027 1
146 c43 42 6 1027 1
147 c44 43 6 1027 1
148 c45 44 6 1027 1
149 c46 45 6 1027 1
150 c47 46 6 1027 1
151 c48 47 6 1027 1
152 c49 48 6 1027 1
153 c50 49 6 1027 1
154 c51 50 6 1027 1
155 c52 51 6 1027 1
156 c53 52 6 1027 1
157 c54 53 6 1027 1
158 c55 54 6 1027 1
159 c56 55 6 1027 1
160 c57 56 6 1027 1
161 c58 57 6 1027 1
162 c59 58 6 1027 1
163 c60 59 6 1027 1
164 c61 60 6 1027 1
165 c62 61 6 1027 1
166 c63 62 6 1027 1
167 c64 63 6 1027 1
168 c65 64 6 1027 1
169 # An INSTANT ADD COLUMN should result in error now.
170 ALTER TABLE t1 ADD COLUMN c66 INT, ALGORITHM=INSTANT;
171 ERROR HY000: Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
172 # ADD COLUMN shall fall back to INPLACE if ALGORITHM not given explicitly
173 ALTER TABLE t1 ADD COLUMN c66 INT;
174 # Table would have been rebuilt
175 # Metadata from INFORMATION_SCHEMA.TABLES
176 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
177 test/t1 69 0 0
178 # Metadata from INFORMATION_SCHEMA.COLUMNS
179 NAME POS MTYPE PRTYPE HAS_DEFAULT
180 c1 0 6 1027 0
181 c2 1 6 1027 0
182 c3 2 6 1027 0
183 c4 3 6 1027 0
184 c5 4 6 1027 0
185 c6 5 6 1027 0
186 c7 6 6 1027 0
187 c8 7 6 1027 0
188 c9 8 6 1027 0
189 c10 9 6 1027 0
190 c11 10 6 1027 0
191 c12 11 6 1027 0
192 c13 12 6 1027 0
193 c14 13 6 1027 0
194 c15 14 6 1027 0
195 c16 15 6 1027 0
196 c17 16 6 1027 0
197 c18 17 6 1027 0
198 c19 18 6 1027 0
199 c20 19 6 1027 0
200 c21 20 6 1027 0
201 c22 21 6 1027 0
202 c23 22 6 1027 0
203 c24 23 6 1027 0
204 c25 24 6 1027 0
205 c26 25 6 1027 0
206 c27 26 6 1027 0
207 c28 27 6 1027 0
208 c29 28 6 1027 0
209 c30 29 6 1027 0
210 c31 30 6 1027 0
211 c32 31 6 1027 0
212 c33 32 6 1027 0
213 c34 33 6 1027 0
214 c35 34 6 1027 0
215 c36 35 6 1027 0
216 c37 36 6 1027 0
217 c38 37 6 1027 0
218 c39 38 6 1027 0
219 c40 39 6 1027 0
220 c41 40 6 1027 0
221 c42 41 6 1027 0
222 c43 42 6 1027 0
223 c44 43 6 1027 0
224 c45 44 6 1027 0
225 c46 45 6 1027 0
226 c47 46 6 1027 0
227 c48 47 6 1027 0
228 c49 48 6 1027 0
229 c50 49 6 1027 0
230 c51 50 6 1027 0
231 c52 51 6 1027 0
232 c53 52 6 1027 0
233 c54 53 6 1027 0
234 c55 54 6 1027 0
235 c56 55 6 1027 0
236 c57 56 6 1027 0
237 c58 57 6 1027 0
238 c59 58 6 1027 0
239 c60 59 6 1027 0
240 c61 60 6 1027 0
241 c62 61 6 1027 0
242 c63 62 6 1027 0
243 c64 63 6 1027 0
244 c65 64 6 1027 0
245 c66 65 6 1027 0
246 DROP TABLE t1;
247 CREATE TABLE t1 (c1 int);
248 INSERT INTO t1(c1) VALUES(1);
249 SELECT * FROM t1;
250 c1
251 1
252 # INSTANT ADD COLUMNS to table to make it reach the maximum versions
253 # An INSTANT DROP COLUMN should result in error now.
254 ALTER TABLE t1 DROP COLUMN c65, ALGORITHM=INSTANT;
255 ERROR HY000: Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
256 INSERT INTO t1(c1) VALUES(2);
257 SELECT * FROM t1;
258 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 c35 c36 c37 c38 c39 c40 c41 c42 c43 c44 c45 c46 c47 c48 c49 c50 c51 c52 c53 c54 c55 c56 c57 c58 c59 c60 c61 c62 c63 c64 c65
259 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
260 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
261 # DROP COLUMN shall fall back to INPLACE if ALGORITHM not given explicitly
262 ALTER TABLE t1 DROP COLUMN c65;
263 INSERT INTO t1(c1) VALUES(3);
264 SELECT * FROM t1;
265 c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 c15 c16 c17 c18 c19 c20 c21 c22 c23 c24 c25 c26 c27 c28 c29 c30 c31 c32 c33 c34 c35 c36 c37 c38 c39 c40 c41 c42 c43 c44 c45 c46 c47 c48 c49 c50 c51 c52 c53 c54 c55 c56 c57 c58 c59 c60 c61 c62 c63 c64
266 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
267 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
268 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
269 # Table would have been rebuilt
270 # Metadata from INFORMATION_SCHEMA.TABLES
271 NAME N_COLS INSTANT_COLS TOTAL_ROW_VERSIONS
272 test/t1 67 0 0
273 # Metadata from INFORMATION_SCHEMA.COLUMNS
274 NAME POS MTYPE PRTYPE HAS_DEFAULT
275 c1 0 6 1027 0
276 c2 1 6 1027 0
277 c3 2 6 1027 0
278 c4 3 6 1027 0
279 c5 4 6 1027 0
280 c6 5 6 1027 0
281 c7 6 6 1027 0
282 c8 7 6 1027 0
283 c9 8 6 1027 0
284 c10 9 6 1027 0
285 c11 10 6 1027 0
286 c12 11 6 1027 0
287 c13 12 6 1027 0
288 c14 13 6 1027 0
289 c15 14 6 1027 0
290 c16 15 6 1027 0
291 c17 16 6 1027 0
292 c18 17 6 1027 0
293 c19 18 6 1027 0
294 c20 19 6 1027 0
295 c21 20 6 1027 0
296 c22 21 6 1027 0
297 c23 22 6 1027 0
298 c24 23 6 1027 0
299 c25 24 6 1027 0
300 c26 25 6 1027 0
301 c27 26 6 1027 0
302 c28 27 6 1027 0
303 c29 28 6 1027 0
304 c30 29 6 1027 0
305 c31 30 6 1027 0
306 c32 31 6 1027 0
307 c33 32 6 1027 0
308 c34 33 6 1027 0
309 c35 34 6 1027 0
310 c36 35 6 1027 0
311 c37 36 6 1027 0
312 c38 37 6 1027 0
313 c39 38 6 1027 0
314 c40 39 6 1027 0
315 c41 40 6 1027 0
316 c42 41 6 1027 0
317 c43 42 6 1027 0
318 c44 43 6 1027 0
319 c45 44 6 1027 0
320 c46 45 6 1027 0
321 c47 46 6 1027 0
322 c48 47 6 1027 0
323 c49 48 6 1027 0
324 c50 49 6 1027 0
325 c51 50 6 1027 0
326 c52 51 6 1027 0
327 c53 52 6 1027 0
328 c54 53 6 1027 0
329 c55 54 6 1027 0
330 c56 55 6 1027 0
331 c57 56 6 1027 0
332 c58 57 6 1027 0
333 c59 58 6 1027 0
334 c60 59 6 1027 0
335 c61 60 6 1027 0
336 c62 61 6 1027 0
337 c63 62 6 1027 0
338 c64 63 6 1027 0
339 DROP TABLE t1;