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