Debian Codegraph @

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

raw
1 # Scenario 1 : Rename a column
2 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT AS (c1 + 10) VIRTUAL);
3 INSERT INTO t1(c1, c2) VALUES (1,11);
4 INSERT INTO t1(c1, c2) VALUES (2,22);
5 INSERT INTO t1(c1, c2) VALUES (3,33);
6 INSERT INTO t1(c1, c2) VALUES (4,44);
7 INSERT INTO t1(c1, c2) VALUES (5,55);
8 SELECT * FROM t1;
9 c1 c2 c3
10 1 11 11
11 2 22 12
12 3 33 13
13 4 44 14
14 5 55 15
15 ALTER TABLE t1 RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
16 SELECT * FROM t1;
17 c1 c22 c3
18 1 11 11
19 2 22 12
20 3 33 13
21 4 44 14
22 5 55 15
23 CREATE TABLE tpart (c1 INT, c2 INT, c3 INT AS (c1 + 10) VIRTUAL)
24 PARTITION BY RANGE (c1) (
25 PARTITION tpart1 VALUES LESS THAN (10),
26 PARTITION tpart2 VALUES LESS THAN (100));
27 ALTER TABLE tpart RENAME COLUMN c1 TO c22, ALGORITHM=INSTANT;
28 ERROR HY000: Column 'c1' has a generated column dependency.
29 ALTER TABLE tpart RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
30 # Scenario 2 : Rename an INSTANT ADD column
31 ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT;
32 ALTER TABLE t1 RENAME COLUMN c4 to c44, ALGORITHM=INSTANT;
33 SELECT * FROM t1;
34 c1 c22 c3 c44
35 1 11 11 NULL
36 2 22 12 NULL
37 3 33 13 NULL
38 4 44 14 NULL
39 5 55 15 NULL
40 ALTER TABLE tpart ADD COLUMN c4 INT, ALGORITHM=INSTANT;
41 ALTER TABLE tpart RENAME COLUMN c4 TO c44, ALGORITHM=INSTANT;
42 SELECT * FROM tpart;
43 c1 c22 c3 c44
44 # Scenario 3 : Rename a VIRTUAL column
45 ALTER TABLE t1 CHANGE c3 c33 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, algorithm=instant;
46 SELECT * FROM t1;
47 c1 c22 c33 c44
48 1 11 11 NULL
49 2 22 12 NULL
50 3 33 13 NULL
51 4 44 14 NULL
52 5 55 15 NULL
53 ALTER TABLE tpart CHANGE c3 c33 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, ALGORITHM=INSTANT;
54 SELECT * FROM tpart;
55 c1 c22 c33 c44
56 # Scenario 4 : Rename an INSTANT ADD VIRTUAL column
57 ALTER TABLE t1 add COLUMN (c5 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL), ALGORITHM=INSTANT;
58 SELECT * FROM t1;
59 c1 c22 c33 c44 c5
60 1 11 11 NULL 11
61 2 22 12 NULL 12
62 3 33 13 NULL 13
63 4 44 14 NULL 14
64 5 55 15 NULL 15
65 ALTER TABLE t1 change c5 c55 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, algorithm=instant;
66 SELECT * FROM t1;
67 c1 c22 c33 c44 c55
68 1 11 11 NULL 11
69 2 22 12 NULL 12
70 3 33 13 NULL 13
71 4 44 14 NULL 14
72 5 55 15 NULL 15
73 ALTER TABLE tpart ADD COLUMN (c5 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL), ALGORITHM=INSTANT;
74 SELECT * FROM tpart;
75 c1 c22 c33 c44 c5
76 ALTER TABLE tpart CHANGE c5 c55 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, ALGORITHM=INSTANT;
77 SELECT * FROM tpart;
78 c1 c22 c33 c44 c55
79 DROP TABLE t1;
80 DROP TABLE tpart;
81 # Scenario 5 : Try to rename a column which is referenced in other table
82 # Case 1: Single column foreign key without unique index
83 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2));
84 CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT,
85 CONSTRAINT t1c1 FOREIGN KEY (c2) REFERENCES t1(c2));
86 ALTER TABLE t1 CHANGE c2 c22 INT, algorithm=INSTANT;
87 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
88 SHOW CREATE TABLE t1;
89 Table Create Table
90 t1 CREATE TABLE `t1` (
91 `c1` int NOT NULL,
92 `c2` int DEFAULT NULL,
93 `c3` int DEFAULT NULL,
94 PRIMARY KEY (`c1`),
95 KEY `c2` (`c2`)
96 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
97 SHOW CREATE TABLE t1c;
98 Table Create Table
99 t1c CREATE TABLE `t1c` (
100 `c1` int NOT NULL,
101 `c2` int DEFAULT NULL,
102 PRIMARY KEY (`c1`),
103 KEY `t1c1` (`c2`),
104 CONSTRAINT `t1c1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c2`)
105 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
106 ALTER TABLE t1 CHANGE c2 c22 INT;
107 SHOW CREATE TABLE t1;
108 Table Create Table
109 t1 CREATE TABLE `t1` (
110 `c1` int NOT NULL,
111 `c22` int DEFAULT NULL,
112 `c3` int DEFAULT NULL,
113 PRIMARY KEY (`c1`),
114 KEY `c2` (`c22`)
115 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
116 SHOW CREATE TABLE t1c;
117 Table Create Table
118 t1c CREATE TABLE `t1c` (
119 `c1` int NOT NULL,
120 `c2` int DEFAULT NULL,
121 PRIMARY KEY (`c1`),
122 KEY `t1c1` (`c2`),
123 CONSTRAINT `t1c1` FOREIGN KEY (`c2`) REFERENCES `t1` (`c22`)
124 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
125 DROP TABLE t1c;
126 DROP TABLE t1;
127 # Case 2: Single column foreign key with unique index
128 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT NOT NULL, UNIQUE INDEX (c3));
129 CREATE TABLE t1c (c1 INT, c2 INT, c3 INT NOT NULL,
130 CONSTRAINT fk1 FOREIGN KEY (c3) REFERENCES t1(c3));
131 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL, ALGORITHM=INSTANT;
132 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
133 SHOW CREATE TABLE t1;
134 Table Create Table
135 t1 CREATE TABLE `t1` (
136 `c1` int DEFAULT NULL,
137 `c2` int DEFAULT NULL,
138 `c3` int NOT NULL,
139 UNIQUE KEY `c3` (`c3`)
140 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
141 SHOW CREATE TABLE t1c;
142 Table Create Table
143 t1c CREATE TABLE `t1c` (
144 `c1` int DEFAULT NULL,
145 `c2` int DEFAULT NULL,
146 `c3` int NOT NULL,
147 KEY `fk1` (`c3`),
148 CONSTRAINT `fk1` FOREIGN KEY (`c3`) REFERENCES `t1` (`c3`)
149 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
150 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL;
151 SHOW CREATE TABLE t1;
152 Table Create Table
153 t1 CREATE TABLE `t1` (
154 `c1` int DEFAULT NULL,
155 `c2` int DEFAULT NULL,
156 `c33` int NOT NULL,
157 UNIQUE KEY `c3` (`c33`)
158 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
159 SHOW CREATE TABLE t1c;
160 Table Create Table
161 t1c CREATE TABLE `t1c` (
162 `c1` int DEFAULT NULL,
163 `c2` int DEFAULT NULL,
164 `c3` int NOT NULL,
165 KEY `fk1` (`c3`),
166 CONSTRAINT `fk1` FOREIGN KEY (`c3`) REFERENCES `t1` (`c33`)
167 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
168 DROP TABLE t1c;
169 DROP TABLE t1;
170 # Case 3: Multi column foreign key without unique index
171 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT NOT NULL, INDEX(c2, c3));
172 CREATE TABLE t1c (c1 INT, c2 INT, c3 INT NOT NULL,
173 CONSTRAINT fk1 FOREIGN KEY (c2, c3) REFERENCES t1(c2, c3));
174 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL, ALGORITHM=INSTANT;
175 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
176 SHOW CREATE TABLE t1;
177 Table Create Table
178 t1 CREATE TABLE `t1` (
179 `c1` int DEFAULT NULL,
180 `c2` int DEFAULT NULL,
181 `c3` int NOT NULL,
182 KEY `c2` (`c2`,`c3`)
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
184 SHOW CREATE TABLE t1c;
185 Table Create Table
186 t1c CREATE TABLE `t1c` (
187 `c1` int DEFAULT NULL,
188 `c2` int DEFAULT NULL,
189 `c3` int NOT NULL,
190 KEY `fk1` (`c2`,`c3`),
191 CONSTRAINT `fk1` FOREIGN KEY (`c2`, `c3`) REFERENCES `t1` (`c2`, `c3`)
192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
193 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL;
194 SHOW CREATE TABLE t1;
195 Table Create Table
196 t1 CREATE TABLE `t1` (
197 `c1` int DEFAULT NULL,
198 `c2` int DEFAULT NULL,
199 `c33` int NOT NULL,
200 KEY `c2` (`c2`,`c33`)
201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
202 SHOW CREATE TABLE t1c;
203 Table Create Table
204 t1c CREATE TABLE `t1c` (
205 `c1` int DEFAULT NULL,
206 `c2` int DEFAULT NULL,
207 `c3` int NOT NULL,
208 KEY `fk1` (`c2`,`c3`),
209 CONSTRAINT `fk1` FOREIGN KEY (`c2`, `c3`) REFERENCES `t1` (`c2`, `c33`)
210 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
211 DROP TABLE t1c;
212 DROP TABLE t1;
213 # Case 4: Multi column foreign key with unique index
214 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT NOT NULL, UNIQUE INDEX(c2, c3));
215 CREATE TABLE t1c (c1 INT, c2 INT, c3 INT NOT NULL,
216 CONSTRAINT fk1 FOREIGN KEY (c2, c3) REFERENCES t1(c2, c3));
217 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL, ALGORITHM=INSTANT;
218 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Columns participating in a foreign key are renamed. Try ALGORITHM=INPLACE.
219 SHOW CREATE TABLE t1;
220 Table Create Table
221 t1 CREATE TABLE `t1` (
222 `c1` int DEFAULT NULL,
223 `c2` int DEFAULT NULL,
224 `c3` int NOT NULL,
225 UNIQUE KEY `c2` (`c2`,`c3`)
226 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
227 SHOW CREATE TABLE t1c;
228 Table Create Table
229 t1c CREATE TABLE `t1c` (
230 `c1` int DEFAULT NULL,
231 `c2` int DEFAULT NULL,
232 `c3` int NOT NULL,
233 KEY `fk1` (`c2`,`c3`),
234 CONSTRAINT `fk1` FOREIGN KEY (`c2`, `c3`) REFERENCES `t1` (`c2`, `c3`)
235 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
236 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL;
237 SHOW CREATE TABLE t1;
238 Table Create Table
239 t1 CREATE TABLE `t1` (
240 `c1` int DEFAULT NULL,
241 `c2` int DEFAULT NULL,
242 `c33` int NOT NULL,
243 UNIQUE KEY `c2` (`c2`,`c33`)
244 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
245 SHOW CREATE TABLE t1c;
246 Table Create Table
247 t1c CREATE TABLE `t1c` (
248 `c1` int DEFAULT NULL,
249 `c2` int DEFAULT NULL,
250 `c3` int NOT NULL,
251 KEY `fk1` (`c2`,`c3`),
252 CONSTRAINT `fk1` FOREIGN KEY (`c2`, `c3`) REFERENCES `t1` (`c2`, `c33`)
253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
254 DROP TABLE t1c;
255 DROP TABLE t1;
256 # Scenario 6: Try to change the column name in a table with discarded tablespace
257 CREATE TABLE t1 (c1 int, c2 INT as (c1+1) VIRTUAL);
258 SELECT TABLE_ID INTO @old_tid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
259 ALTER TABLE t1 DISCARD TABLESPACE;
260 SELECT TABLE_ID INTO @new_tid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
261 SELECT (@old_tid != @new_tid) as Table_Id_Changed;
262 Table_Id_Changed
263 1
264 ALTER TABLE t1 RENAME COLUMN c2 to c3, algorithm=instant;
265 Warnings:
266 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
267 ALTER TABLE t1 RENAME COLUMN c3 to c2, algorithm=instant;
268 Warnings:
269 Warning 1814 InnoDB: Tablespace has been discarded for table 't1'
270 Drop TABLE t1;
271 CREATE TABLE tpart (c1 INT, c2 INT AS (c1 + 1) VIRTUAL)
272 PARTITION BY RANGE(c1) (
273 PARTITION p1 VALUES LESS THAN (10),
274 PARTITION p2 VALUES LESS THAN (100));
275 SELECT max(TABLE_ID) INTO @old_tpid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%tpart%";
276 ALTER TABLE tpart DISCARD TABLESPACE;
277 SELECT max(TABLE_ID) INTO @new_tpid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%tpart%";
278 SELECT (@old_tpid != @new_tpid) AS Table_Id_Changed;
279 Table_Id_Changed
280 1
281 ALTER TABLE tpart RENAME COLUMN c2 TO c3, ALGORITHM=INSTANT;
282 Warnings:
283 Warning 1814 InnoDB: Tablespace has been discarded for table 'tpart'
284 Warning 1814 InnoDB: Tablespace has been discarded for table 'tpart'
285 ALTER TABLE tpart RENAME COLUMN c3 TO c2, ALGORITHM=INSTANT;
286 Warnings:
287 Warning 1814 InnoDB: Tablespace has been discarded for table 'tpart'
288 Warning 1814 InnoDB: Tablespace has been discarded for table 'tpart'
289 DROP TABLE tpart;
290 # Scenario 7: Try to rename a column to an internal column name
291 CREATE TABLE t1 (c1 INT, c2 INT);
292 ALTER TABLE t1 RENAME COLUMN c1 TO DB_ROW_ID;
293 ERROR 42000: Incorrect column name 'DB_ROW_ID'
294 ALTER TABLE t1 RENAME COLUMN c1 TO DB_TRX_ID;
295 ERROR 42000: Incorrect column name 'DB_TRX_ID'
296 ALTER TABLE t1 RENAME COLUMN c1 TO DB_ROLL_PTR;
297 ERROR 42000: Incorrect column name 'DB_ROLL_PTR'
298 DROP TABLE t1;
299 # Scenario 8: Rename SET and ENUM type columns
300 CREATE TABLE tenum (c1 INT, c2 ENUM('a','b'));
301 INSERT INTO tenum VALUES (1, 'a');
302 ALTER TABLE tenum RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
303 ALTER TABLE tenum CHANGE c22 c2 ENUM ('a','b','c'), ALGORITHM=INSTANT;
304 ALTER TABLE tenum CHANGE c2 c22 ENUM ('a','b'), ALGORITHM=INSTANT;
305 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
306 DROP TABLE tenum;
307 CREATE TABLE tset (c1 INT, c2 SET('a','b'));
308 INSERT INTO tset VALUES (1, 'a');
309 ALTER TABLE tset RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
310 ALTER TABLE tset CHANGE c22 c2 SET ('a','b','c'), ALGORITHM=INSTANT;
311 ALTER TABLE tset CHANGE c2 c22 SET ('a','b'), ALGORITHM=INSTANT;
312 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
313 DROP TABLE tset;
314 # Scenario 9: CHANGE column cannot use INSTANT algorithm if
315 # it involves definition change
316 CREATE TABLE tchange (c1 INT, c2 INT);
317 ALTER TABLE tchange CHANGE c2 c2 DOUBLE, ALGORITHM=INSTANT;
318 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Need to rebuild the table to change column type. Try ALGORITHM=COPY/INPLACE.
319 ALTER TABLE tchange CHANGE c2 c2 DOUBLE, ALGORITHM=INPLACE;
320 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
321 ALTER TABLE tchange CHANGE c2 c22 DOUBLE, ALGORITHM=COPY;
322 SHOW CREATE TABLE tchange;
323 Table Create Table
324 tchange CREATE TABLE `tchange` (
325 `c1` int DEFAULT NULL,
326 `c22` double DEFAULT NULL
327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
328 ALTER TABLE tchange CHANGE c22 c2 DOUBLE, ALGORITHM=INSTANT;
329 SHOW CREATE TABLE tchange;
330 Table Create Table
331 tchange CREATE TABLE `tchange` (
332 `c1` int DEFAULT NULL,
333 `c2` double DEFAULT NULL
334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
335 DROP TABLE tchange;
336 # Scenario 10: INSTANT RENAME WITH INSTANT ADD
337 Create table t1 (c1 int KEY, c2 int, c3 int, c4 int);
338 desc t1;
339 Field Type Null Key Default Extra
340 c1 int NO PRI NULL
341 c2 int YES NULL
342 c3 int YES NULL
343 c4 int YES NULL
344 alter table t1 add column c5 int , rename column c2 to c22, algorithm=instant;
345 desc t1;
346 Field Type Null Key Default Extra
347 c1 int NO PRI NULL
348 c22 int YES NULL
349 c3 int YES NULL
350 c4 int YES NULL
351 c5 int YES NULL
352 alter table t1 add column v1 int as (c1+1), add column v2 int as (c1+2) virtual, rename column c3 to c33, algorithm=instant;
353 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE.
354 alter table t1 add column v1 int as (c1+1), add column v2 int as (c1+2) virtual, algorithm=instant;
355 desc t1;
356 Field Type Null Key Default Extra
357 c1 int NO PRI NULL
358 c22 int YES NULL
359 c3 int YES NULL
360 c4 int YES NULL
361 c5 int YES NULL
362 v1 int YES NULL VIRTUAL GENERATED
363 v2 int YES NULL VIRTUAL GENERATED
364 alter table t1 drop column v1, algorithm=instant;
365 ERROR 0A000: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.
366 alter table t1 drop column v2, algorithm=instant;
367 desc t1;
368 Field Type Null Key Default Extra
369 c1 int NO PRI NULL
370 c22 int YES NULL
371 c3 int YES NULL
372 c4 int YES NULL
373 c5 int YES NULL
374 v1 int YES NULL VIRTUAL GENERATED
375 alter table t1 drop column v1, rename column c22 to c222, algorithm=instant;
376 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY/INPLACE.
377 DROP TABLE t1;