Debian Codegraph @

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

raw
1 #############################################
2 # Test script to test INSTANT RENAME COLUMN #
3 #############################################
4
5 --disable_query_log
6 call mtr.add_suppression("\\[Warning\\].* Tablespace for table `.*`\\.`.*` is set as discarded");
7 call mtr.add_suppression("\\[Warning\\].* Tablespace for table `.*`\\.`.*` /\\* Partition `.*` \\*/ is set as discarded");
8 --enable_query_log
9
10 --echo # Scenario 1 : Rename a column
11
12 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT AS (c1 + 10) VIRTUAL);
13 INSERT INTO t1(c1, c2) VALUES (1,11);
14 INSERT INTO t1(c1, c2) VALUES (2,22);
15 INSERT INTO t1(c1, c2) VALUES (3,33);
16 INSERT INTO t1(c1, c2) VALUES (4,44);
17 INSERT INTO t1(c1, c2) VALUES (5,55);
18 SELECT * FROM t1;
19
20 ALTER TABLE t1 RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
21 SELECT * FROM t1;
22
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
28 --error ER_DEPENDENT_BY_GENERATED_COLUMN
29 ALTER TABLE tpart RENAME COLUMN c1 TO c22, ALGORITHM=INSTANT;
30
31 ALTER TABLE tpart RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
32
33 --echo # Scenario 2 : Rename an INSTANT ADD column
34 ALTER TABLE t1 ADD COLUMN c4 INT, ALGORITHM=INSTANT;
35 ALTER TABLE t1 RENAME COLUMN c4 to c44, ALGORITHM=INSTANT;
36 SELECT * FROM t1;
37
38 ALTER TABLE tpart ADD COLUMN c4 INT, ALGORITHM=INSTANT;
39 ALTER TABLE tpart RENAME COLUMN c4 TO c44, ALGORITHM=INSTANT;
40 SELECT * FROM tpart;
41
42 --echo # Scenario 3 : Rename a VIRTUAL column
43 ALTER TABLE t1 CHANGE c3 c33 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, algorithm=instant;
44 SELECT * FROM t1;
45
46 ALTER TABLE tpart CHANGE c3 c33 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, ALGORITHM=INSTANT;
47 SELECT * FROM tpart;
48
49 --echo # Scenario 4 : Rename an INSTANT ADD VIRTUAL column
50 ALTER TABLE t1 add COLUMN (c5 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL), ALGORITHM=INSTANT;
51 SELECT * FROM t1;
52 ALTER TABLE t1 change c5 c55 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, algorithm=instant;
53 SELECT * FROM t1;
54
55 ALTER TABLE tpart ADD COLUMN (c5 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL), ALGORITHM=INSTANT;
56 SELECT * FROM tpart;
57 ALTER TABLE tpart CHANGE c5 c55 INT GENERATED ALWAYS AS (c1 + 10) VIRTUAL, ALGORITHM=INSTANT;
58 SELECT * FROM tpart;
59
60 DROP TABLE t1;
61 DROP TABLE tpart;
62
63 --echo # Scenario 5 : Try to rename a column which is referenced in other table
64 --echo # Case 1: Single column foreign key without unique index
65 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX(c2));
66 CREATE TABLE t1c (c1 INT PRIMARY KEY, c2 INT,
67 CONSTRAINT t1c1 FOREIGN KEY (c2) REFERENCES t1(c2));
68
69 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
70 ALTER TABLE t1 CHANGE c2 c22 INT, algorithm=INSTANT;
71
72 SHOW CREATE TABLE t1;
73 SHOW CREATE TABLE t1c;
74
75 ALTER TABLE t1 CHANGE c2 c22 INT;
76
77 SHOW CREATE TABLE t1;
78 SHOW CREATE TABLE t1c;
79
80 DROP TABLE t1c;
81 DROP TABLE t1;
82
83 --echo # Case 2: Single column foreign key with unique index
84 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT NOT NULL, UNIQUE INDEX (c3));
85 CREATE TABLE t1c (c1 INT, c2 INT, c3 INT NOT NULL,
86 CONSTRAINT fk1 FOREIGN KEY (c3) REFERENCES t1(c3));
87
88 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
89 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL, ALGORITHM=INSTANT;
90
91 SHOW CREATE TABLE t1;
92 SHOW CREATE TABLE t1c;
93
94 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL;
95
96 SHOW CREATE TABLE t1;
97 SHOW CREATE TABLE t1c;
98
99 DROP TABLE t1c;
100 DROP TABLE t1;
101
102 --echo # Case 3: Multi column foreign key without unique index
103 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT NOT NULL, INDEX(c2, c3));
104 CREATE TABLE t1c (c1 INT, c2 INT, c3 INT NOT NULL,
105 CONSTRAINT fk1 FOREIGN KEY (c2, c3) REFERENCES t1(c2, c3));
106
107 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
108 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL, ALGORITHM=INSTANT;
109
110 SHOW CREATE TABLE t1;
111 SHOW CREATE TABLE t1c;
112
113 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL;
114
115 SHOW CREATE TABLE t1;
116 SHOW CREATE TABLE t1c;
117
118 DROP TABLE t1c;
119 DROP TABLE t1;
120
121 --echo # Case 4: Multi column foreign key with unique index
122 CREATE TABLE t1 (c1 INT, c2 INT, c3 INT NOT NULL, UNIQUE INDEX(c2, c3));
123 CREATE TABLE t1c (c1 INT, c2 INT, c3 INT NOT NULL,
124 CONSTRAINT fk1 FOREIGN KEY (c2, c3) REFERENCES t1(c2, c3));
125
126 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
127 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL, ALGORITHM=INSTANT;
128
129 SHOW CREATE TABLE t1;
130 SHOW CREATE TABLE t1c;
131
132 ALTER TABLE t1 CHANGE c3 c33 INT NOT NULL;
133
134 SHOW CREATE TABLE t1;
135 SHOW CREATE TABLE t1c;
136
137 DROP TABLE t1c;
138 DROP TABLE t1;
139
140 --echo # Scenario 6: Try to change the column name in a table with discarded tablespace
141 CREATE TABLE t1 (c1 int, c2 INT as (c1+1) VIRTUAL);
142 SELECT TABLE_ID INTO @old_tid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
143
144 ALTER TABLE t1 DISCARD TABLESPACE;
145 SELECT TABLE_ID INTO @new_tid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t1%";
146
147 SELECT (@old_tid != @new_tid) as Table_Id_Changed;
148
149 ALTER TABLE t1 RENAME COLUMN c2 to c3, algorithm=instant;
150 ALTER TABLE t1 RENAME COLUMN c3 to c2, algorithm=instant;
151
152 Drop TABLE t1;
153
154 CREATE TABLE tpart (c1 INT, c2 INT AS (c1 + 1) VIRTUAL)
155 PARTITION BY RANGE(c1) (
156 PARTITION p1 VALUES LESS THAN (10),
157 PARTITION p2 VALUES LESS THAN (100));
158
159 SELECT max(TABLE_ID) INTO @old_tpid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%tpart%";
160
161 ALTER TABLE tpart DISCARD TABLESPACE;
162 SELECT max(TABLE_ID) INTO @new_tpid FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%tpart%";
163
164 SELECT (@old_tpid != @new_tpid) AS Table_Id_Changed;
165
166 ALTER TABLE tpart RENAME COLUMN c2 TO c3, ALGORITHM=INSTANT;
167 ALTER TABLE tpart RENAME COLUMN c3 TO c2, ALGORITHM=INSTANT;
168
169 DROP TABLE tpart;
170
171 --echo # Scenario 7: Try to rename a column to an internal column name
172 CREATE TABLE t1 (c1 INT, c2 INT);
173
174 --error ER_WRONG_COLUMN_NAME
175 ALTER TABLE t1 RENAME COLUMN c1 TO DB_ROW_ID;
176
177 --error ER_WRONG_COLUMN_NAME
178 ALTER TABLE t1 RENAME COLUMN c1 TO DB_TRX_ID;
179
180 --error ER_WRONG_COLUMN_NAME
181 ALTER TABLE t1 RENAME COLUMN c1 TO DB_ROLL_PTR;
182
183 DROP TABLE t1;
184
185 --echo # Scenario 8: Rename SET and ENUM type columns
186 CREATE TABLE tenum (c1 INT, c2 ENUM('a','b'));
187 INSERT INTO tenum VALUES (1, 'a');
188 ALTER TABLE tenum RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
189 ALTER TABLE tenum CHANGE c22 c2 ENUM ('a','b','c'), ALGORITHM=INSTANT;
190 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
191 ALTER TABLE tenum CHANGE c2 c22 ENUM ('a','b'), ALGORITHM=INSTANT;
192 DROP TABLE tenum;
193
194 CREATE TABLE tset (c1 INT, c2 SET('a','b'));
195 INSERT INTO tset VALUES (1, 'a');
196 ALTER TABLE tset RENAME COLUMN c2 TO c22, ALGORITHM=INSTANT;
197 ALTER TABLE tset CHANGE c22 c2 SET ('a','b','c'), ALGORITHM=INSTANT;
198 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
199 ALTER TABLE tset CHANGE c2 c22 SET ('a','b'), ALGORITHM=INSTANT;
200 DROP TABLE tset;
201
202 --echo # Scenario 9: CHANGE column cannot use INSTANT algorithm if
203 --echo # it involves definition change
204 CREATE TABLE tchange (c1 INT, c2 INT);
205 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
206 ALTER TABLE tchange CHANGE c2 c2 DOUBLE, ALGORITHM=INSTANT;
207 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
208 ALTER TABLE tchange CHANGE c2 c2 DOUBLE, ALGORITHM=INPLACE;
209 ALTER TABLE tchange CHANGE c2 c22 DOUBLE, ALGORITHM=COPY;
210 SHOW CREATE TABLE tchange;
211 ALTER TABLE tchange CHANGE c22 c2 DOUBLE, ALGORITHM=INSTANT;
212 SHOW CREATE TABLE tchange;
213 DROP TABLE tchange;
214
215 --echo # Scenario 10: INSTANT RENAME WITH INSTANT ADD
216 # [c1, c2, c3, c4]
217 Create table t1 (c1 int KEY, c2 int, c3 int, c4 int);
218 desc t1;
219 # INSTANT ADD column with rename
220 # [c1, c2, c3, c4] => [c1, c22, c3, c4, c5]
221 alter table t1 add column c5 int , rename column c2 to c22, algorithm=instant;
222 desc t1;
223
224 # INSTANT ADD virtual column with rename
225 # Not allowed yet.
226 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
227 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;
228
229 # INSTANT ADD virtual column
230 # [c1, c22, c3, c4, c5] => [c1, c22, c3, c4, c5, v1, v2]
231 alter table t1 add column v1 int as (c1+1), add column v2 int as (c1+2) virtual, algorithm=instant;
232 desc t1;
233
234 # INSTANT DROP virtual column
235 # Drop virtual column in between. This will change the order of virtual column.
236 # Not allowed.
237 --error ER_ALTER_OPERATION_NOT_SUPPORTED
238 alter table t1 drop column v1, algorithm=instant;
239
240 # INSTANT DROP virtual column
241 # [c1, c22, c3, c4, c5, v1, v2] => [c1, c22, c3, c4, c5, v1]
242 alter table t1 drop column v2, algorithm=instant;
243 desc t1;
244
245 # INSTANT DROP virtual column with rename
246 # Not allowed.
247 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
248 alter table t1 drop column v1, rename column c22 to c222, algorithm=instant;
249
250 DROP TABLE t1;
251