Debian Codegraph @

mysql-test/suite/innodb/t/alter_varchar_change.test

raw
1 --source include/have_innodb.inc
2 --source include/test_db_charset_latin1.inc
3
4 DELIMITER |;
5 CREATE PROCEDURE get_index_id(IN tbl_id INT, IN idx_name char(100), OUT idx_id INT)
6 BEGIN
7 SELECT index_id into idx_id FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE
8 NAME=idx_name and TABLE_ID=tbl_id;
9 END|
10
11 CREATE PROCEDURE get_table_id(IN tbl_name char(100), OUT tbl_id INT)
12 BEGIN
13 SELECT table_id into tbl_id FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE
14 NAME = tbl_name;
15 END|
16
17 DELIMITER ;|
18
19 SET @tbl_id = 0;
20 SET @tbl1_id = 0;
21 SET @idx_id = 0;
22 SET @idx1_id = 0;
23
24 # Table should avoid rebuild for the following varchar change.
25
26 CREATE TABLE t1(f1 INT NOT NULL,
27 f2 VARCHAR(100) PRIMARY KEY)ENGINE=InnoDB;
28
29 CALL get_table_id("test/t1", @tbl_id);
30 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ALGORITHM=INSTANT;
31 CALL get_table_id("test/t1", @tbl1_id);
32
33 SELECT @tbl1_id = @tbl_id;
34 SHOW CREATE TABLE t1;
35 DROP TABLE t1;
36
37 # Index should avoid rebuild
38 CREATE TABLE t1(f1 INT NOT NULL,
39 f2 VARCHAR(100),
40 INDEX idx(f2))ENGINE=InnoDB;
41
42 CALL get_table_id("test/t1", @tbl_id);
43 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ALGORITHM=INSTANT;
44 CALL get_table_id("test/t1", @tbl1_id);
45
46 SELECT @tbl1_id = @tbl_id;
47 SHOW CREATE TABLE t1;
48 DROP TABLE t1;
49
50 CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100), f3 VARCHAR(100),
51 INDEX idx(f2, f3), index idx1(f3, f2))ENGINE=InnoDB;
52
53 CALL get_table_id("test/t1", @tbl_id);
54 CALL get_index_id(@tbl_id, "idx", @idx_id);
55 ALTER TABLE t1 MODIFY f2 VARCHAR(200), MODIFY f3 VARCHAR(150), ALGORITHM=INSTANT;
56 CALL get_table_id("test/t1", @tbl1_id);
57 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
58
59 SELECT @tbl1_id = @tbl_id;
60 SELECT @idx1_id = @idx_id;
61 SHOW CREATE TABLE t1;
62 DROP TABLE t1;
63
64 CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100),
65 INDEX idx(f2(40)))ENGINE=InnoDB;
66
67 CALL get_table_id("test/t1", @tbl_id);
68 CALL get_index_id(@tbl_id, "idx", @idx_id);
69 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ALGORITHM=INSTANT;
70 CALL get_table_id("test/t1", @tbl1_id);
71 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
72
73 SELECT @tbl1_id = @tbl_id;
74 SELECT @idx1_id = @idx_id;
75
76 SHOW CREATE TABLE t1;
77 DROP TABLE t1;
78
79 CREATE TABLE t1(f1 INT NOT NULL,
80 f2 VARCHAR(100), FULLTEXT idx(f2))ENGINE=InnoDB;
81
82 CALL get_table_id("test/t1", @tbl_id);
83 CALL get_index_id(@tbl_id, "idx", @idx_id);
84 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ALGORITHM=INSTANT;
85 CALL get_table_id("test/t1", @tbl1_id);
86 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
87
88 SELECT @tbl1_id = @tbl_id;
89 SELECT @idx1_id = @idx_id;
90 SHOW CREATE TABLE t1;
91 DROP TABLE t1;
92
93 CREATE TABLE t1(f1 INT NOT NULL,
94 f2 VARCHAR(100),
95 f3 VARCHAR(50) as (f2) VIRTUAL,
96 INDEX idx(f3))ENGINE=InnoDB;
97
98 INSERT INTO t1(f1, f2) VALUES(1, repeat('a', 40));
99
100 CALL get_table_id("test/t1", @tbl_id);
101 CALL get_index_id(@tbl_id, "idx", @idx_id);
102 ALTER TABLE t1 MODIFY f2 VARCHAR(100), ALGORITHM=INSTANT;
103 CALL get_table_id("test/t1", @tbl1_id);
104 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
105
106 SELECT @tbl1_id = @tbl_id;
107 SELECT @idx1_id = @idx_id;
108 SHOW CREATE TABLE t1;
109 DROP TABLE t1;
110
111 CREATE TABLE t1(f1 INT NOT NULL,
112 f2 VARCHAR(100),
113 INDEX idx(f2(10)),
114 INDEX idx1(f1))ENGINE=InnoDB;
115
116 CALL get_table_id("test/t1", @tbl_id);
117 CALL get_index_id(@tbl_id, "idx", @idx_id);
118 ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx1;
119 CALL get_table_id("test/t1", @tbl1_id);
120 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
121
122 SELECT @tbl1_id = @tbl_id;
123 SELECT @idx1_id = @idx_id;
124 SHOW CREATE TABLE t1;
125 DROP TABLE t1;
126
127 CREATE TABLE t1(f1 INT NOT NULL,
128 f2 VARCHAR(100),
129 INDEX idx(f2(10)))ENGINE=InnoDB;
130
131 CALL get_table_id("test/t1", @tbl_id);
132 CALL get_index_id(@tbl_id, "idx", @idx_id);
133 ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(10)), ALGORITHM=INSTANT;
134 CALL get_table_id("test/t1", @tbl1_id);
135 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
136
137 SELECT @tbl1_id = @tbl_id;
138 SELECT @idx1_id = @idx_id;
139 SHOW CREATE TABLE t1;
140 DROP TABLE t1;
141
142 CREATE TABLE t1(f1 INT NOT NULL,
143 f2 VARCHAR(100),
144 INDEX idx(f2(10)))ENGINE=InnoDB;
145
146 CALL get_table_id("test/t1", @tbl_id);
147 CALL get_index_id(@tbl_id, "idx", @idx_id);
148 ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(50));
149 CALL get_table_id("test/t1", @tbl1_id);
150 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
151
152 SELECT @tbl1_id = @tbl_id;
153 SELECT @idx1_id = @idx_id;
154 SHOW CREATE TABLE t1;
155 DROP TABLE t1;
156
157 # Newly added index should built
158
159 CREATE TABLE t1(f1 INT NOT NULL,
160 f2 VARCHAR(100),
161 INDEX idx(f2(100)))ENGINE=InnoDB;
162
163 CALL get_table_id("test/t1", @tbl_id);
164 CALL get_index_id(@tbl_id, "idx", @idx_id);
165 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD INDEX idx1(f1);
166 CALL get_table_id("test/t1", @tbl1_id);
167 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
168
169 SELECT @tbl1_id = @tbl_id;
170 SELECT @idx1_id = @idx_id;
171 SHOW CREATE TABLE t1;
172 DROP TABLE t1;
173
174 CREATE TABLE t1(f1 INT NOT NULL,
175 f2 VARCHAR(100),
176 INDEX idx(f2(10)))ENGINE=InnoDB;
177
178 CALL get_table_id("test/t1", @tbl_id);
179 CALL get_index_id(@tbl_id, "idx", @idx_id);
180 ALTER TABLE t1 MODIFY f2 VARCHAR(200), DROP INDEX idx, ADD INDEX idx(f2(6));
181 CALL get_table_id("test/t1", @tbl1_id);
182 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
183
184 SELECT @tbl1_id = @tbl_id;
185 SELECT @idx1_id = @idx_id;
186 SHOW CREATE TABLE t1;
187 DROP TABLE t1;
188
189 # Table should rebuild
190
191 CREATE TABLE t1(f1 INT NOT NULL,
192 f2 VARCHAR(100),
193 INDEX idx(f2))ENGINE=InnoDB;
194
195 CALL get_table_id("test/t1", @tbl_id);
196 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD COLUMN f3 INT, ALGORITHM=INSTANT;
197 CALL get_table_id("test/t1", @tbl1_id);
198
199 SELECT @tbl1_id = @tbl_id;
200 SHOW CREATE TABLE t1;
201 DROP TABLE t1;
202
203 CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100) PRIMARY KEY)ENGINE=InnoDB;
204
205 CALL get_table_id("test/t1", @tbl_id);
206 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD COLUMN f3 INT, ALGORITHM=INSTANT;
207 CALL get_table_id("test/t1", @tbl1_id);
208
209 SELECT @tbl1_id = @tbl_id;
210 SHOW CREATE TABLE t1;
211 DROP TABLE t1;
212
213 CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100))ENGINE=INNODB;
214
215 CALL get_table_id("test/t1", @tbl_id);
216 ALTER TABLE t1 MODIFY f2 VARCHAR(200), ADD FULLTEXT idx(f2);
217 CALL get_table_id("test/t1", @tbl1_id);
218
219 SELECT @tbl1_id = @tbl_id;
220 SHOW CREATE TABLE t1;
221 DROP TABLE t1;
222
223 CREATE TABLE t1(f1 INT NOT NULL,
224 f2 CHAR(100) PRIMARY KEY)ENGINE=InnoDB;
225
226 CALL get_table_id("test/t1", @tbl_id);
227 ALTER TABLE t1 MODIFY f2 CHAR(200);
228 CALL get_table_id("test/t1", @tbl1_id);
229
230 SELECT @tbl1_id = @tbl_id;
231 SHOW CREATE TABLE t1;
232 DROP TABLE t1;
233
234 CREATE TABLE t1(f1 INT NOT NULL,
235 f2 VARCHAR(100),
236 INDEX idx(f2(10)),
237 INDEX idx1(f1))ENGINE=InnoDB;
238
239 CALL get_table_id("test/t1", @tbl_id);
240 ALTER TABLE t1 MODIFY f2 VARCHAR(50);
241 CALL get_table_id("test/t1", @tbl1_id);
242
243 SELECT @tbl1_id = @tbl_id;
244 SHOW CREATE TABLE t1;
245 DROP TABLE t1;
246
247 CREATE TABLE t1(f1 INT NOT NULL,
248 f2 VARCHAR(100),
249 INDEX idx(f2(10)),
250 INDEX idx1(f1))ENGINE=InnoDB;
251
252 CALL get_table_id("test/t1", @tbl_id);
253 ALTER TABLE t1 MODIFY f2 VARCHAR(5), DROP INDEX idx1;
254 CALL get_table_id("test/t1", @tbl1_id);
255
256 SELECT @tbl1_id = @tbl_id;
257 SHOW CREATE TABLE t1;
258 DROP TABLE t1;
259
260 CREATE TABLE t1(f1 INT NOT NULL,
261 f2 VARCHAR(100), FULLTEXT idx(f2))ENGINE=InnoDB;
262
263 CALL get_table_id("test/t1", @tbl_id);
264 CALL get_index_id(@tbl_id, "idx", @idx_id);
265 ALTER TABLE t1 MODIFY f2 VARCHAR(50);
266 CALL get_table_id("test/t1", @tbl1_id);
267 CALL get_index_id(@tbl1_id, "idx", @idx1_id);
268
269 SELECT @tbl1_id = @tbl_id;
270 SELECT @idx1_id = @idx_id;
271 SHOW CREATE TABLE t1;
272 DROP TABLE t1;
273
274 CREATE TABLE t1(f1 INT NOT NULL,
275 f2 VARCHAR(100),
276 INDEX idx(f2))ENGINE=InnoDB;
277
278 CALL get_table_id("test/t1", @tbl_id);
279 ALTER TABLE t1 MODIFY f2 CHAR(200);
280 CALL get_table_id("test/t1", @tbl1_id);
281
282 SELECT @tbl1_id = @tbl_id;
283 SHOW CREATE TABLE t1;
284 DROP TABLE t1;
285
286 CREATE TABLE t1(f1 INT NOT NULL,
287 f2 VARCHAR(100),
288 INDEX idx(f2(40)))ENGINE=InnoDB;
289
290 CALL get_table_id("test/t1", @tbl_id);
291 ALTER TABLE t1 MODIFY f2 TEXT;
292 CALL get_table_id("test/t1", @tbl1_id);
293
294 SELECT @tbl1_id = @tbl_id;
295 SHOW CREATE TABLE t1;
296 DROP TABLE t1;
297
298 CREATE TABLE t1(f1 INT NOT NULL,
299 f2 VARCHAR(100),
300 INDEX idx(f2(40)))ENGINE=InnoDB;
301
302 CALL get_table_id("test/t1", @tbl_id);
303 ALTER TABLE t1 MODIFY f2 VARCHAR(300);
304 CALL get_table_id("test/t1", @tbl1_id);
305
306 SELECT @tbl1_id = @tbl_id;
307 SHOW CREATE TABLE t1;
308 DROP TABLE t1;
309
310 CREATE TABLE t1(f1 INT NOT NULL,
311 f2 VARCHAR(128),
312 INDEX idx(f2(40)))ENGINE=InnoDB;
313
314 CALL get_table_id("test/t1", @tbl_id);
315 ALTER TABLE t1 MODIFY f2 VARCHAR(300);
316 CALL get_table_id("test/t1", @tbl1_id);
317
318 SELECT @tbl1_id = @tbl_id;
319 SHOW CREATE TABLE t1;
320 DROP TABLE t1;
321
322 CREATE TABLE t1(f1 INT NOT NULL,
323 f2 VARCHAR(128),
324 INDEX idx(f2(40)))ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
325
326 CALL get_table_id("test/t1", @tbl_id);
327 ALTER TABLE t1 MODIFY f2 VARCHAR(300);
328 CALL get_table_id("test/t1", @tbl1_id);
329
330 SELECT @tbl1_id = @tbl_id;
331 SHOW CREATE TABLE t1;
332 DROP TABLE t1;
333
334 CREATE TABLE t1(f1 INT NOT NULL,
335 f2 VARCHAR(100),
336 INDEX idx(f2(40)))ENGINE=InnoDB;
337
338 CALL get_table_id("test/t1", @tbl_id);
339 ALTER TABLE t1 MODIFY f2 VARCHAR(200) CHARACTER SET UTF16;
340 CALL get_table_id("test/t1", @tbl1_id);
341
342 SELECT @tbl1_id = @tbl_id;
343 SHOW CREATE TABLE t1;
344 DROP TABLE t1;
345 # Show error when virtual varchar column got changed
346
347 CREATE TABLE t1(f1 INT NOT NULL,
348 f2 VARCHAR(100),
349 f3 VARCHAR(50) as (f2) VIRTUAL,
350 INDEX idx(f3))ENGINE=InnoDB;
351
352 --echo # If varchar virtual column extension is allowed in the future then
353 --echo # InnoDB must rebuild the index
354
355 --error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
356 ALTER TABLE t1 MODIFY f3 VARCHAR(100);
357 SHOW CREATE TABLE t1;
358 DROP TABLE t1;
359
360 DROP PROCEDURE get_index_id;
361 DROP PROCEDURE get_table_id;
362
363 # LEN must increase here
364 create table t (a varchar(100)) engine=innodb;
365 select sc.name, sc.pos, sc.mtype, sc.prtype, sc.len
366 from information_schema.innodb_sys_columns sc
367 inner join information_schema.innodb_sys_tables st
368 on sc.table_id=st.table_id where st.name='test/t' and sc.name='a';
369 alter table t modify a varchar(110), algorithm=inplace;
370 select sc.name, sc.pos, sc.mtype, sc.prtype, sc.len
371 from information_schema.innodb_sys_columns sc
372 inner join information_schema.innodb_sys_tables st
373 on sc.table_id=st.table_id where st.name='test/t' and sc.name='a';
374 drop table t;
375
376 --echo # End of 10.2 tests
377
378 --source include/test_db_charset_restore.inc