Debian Codegraph @

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

raw
1 #
2 # MDEV-15563: Instant ROW_FORMAT=REDUNDANT column type change&extension
3 # (reverted in MDEV-18627)
4 #
5 create database best;
6 use best;
7 set default_storage_engine=innodb;
8 set @bigval= repeat('0123456789', 30);
9 create procedure check_table(table_name varchar(255))
10 begin
11 select table_id into @table_id
12 from information_schema.innodb_sys_tables
13 where name = concat('best/', table_name);
14 select name, mtype, hex(prtype) as prtype, len
15 from information_schema.innodb_sys_columns
16 where table_id = @table_id;
17 end~~
18 # VARCHAR -> CHAR, VARBINARY -> BINARY conversion
19 set @bigval= repeat('0123456789', 20);
20 create table t (a varchar(300));
21 alter table t modify a char(255), algorithm=instant;
22 ERROR 0A000: ALGORITHM=INSTANT is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
23 alter table t modify a char(255), algorithm=copy;
24 create or replace table t (a varchar(200));
25 insert into t values (@bigval);
26 insert into t values ('z');
27 alter table t modify a char(200);
28 affected rows: 2
29 info: Records: 2 Duplicates: 0 Warnings: 0
30 select count(a) from t where a = @bigval;
31 count(a)
32 1
33 select a, length(a) from t where a = 'z';
34 a length(a)
35 z 1
36 check table t extended;
37 Table Op Msg_type Msg_text
38 best.t check status OK
39 call check_table('t');
40 name mtype prtype len
41 a 2 800FE 200
42 # CHAR enlargement
43 alter table t modify a char(220);
44 affected rows: 2
45 info: Records: 2 Duplicates: 0 Warnings: 0
46 select count(a) from t where a = @bigval;
47 count(a)
48 1
49 select a, length(a) from t where a = 'z';
50 a length(a)
51 z 1
52 check table t extended;
53 Table Op Msg_type Msg_text
54 best.t check status OK
55 call check_table('t');
56 name mtype prtype len
57 a 2 800FE 220
58 ALTER TABLE t CHANGE COLUMN a a CHAR(230) BINARY;
59 affected rows: 2
60 info: Records: 2 Duplicates: 0 Warnings: 0
61 ALTER TABLE t ADD COLUMN b INT FIRST;
62 affected rows: 0
63 info: Records: 0 Duplicates: 0 Warnings: 0
64 ALTER TABLE t DROP b;
65 affected rows: 0
66 info: Records: 0 Duplicates: 0 Warnings: 0
67 check table t extended;
68 Table Op Msg_type Msg_text
69 best.t check status OK
70 call check_table('t');
71 name mtype prtype len
72 a 13 2F00FE 230
73 # Convert from VARCHAR to a bigger CHAR
74 alter table t modify a varchar(200);
75 affected rows: 2
76 info: Records: 2 Duplicates: 0 Warnings: 0
77 alter table t modify a char(255);
78 affected rows: 2
79 info: Records: 2 Duplicates: 0 Warnings: 0
80 select count(a) from t where a = @bigval;
81 count(a)
82 1
83 select a, length(a) from t where a = 'z';
84 a length(a)
85 z 1
86 select * from t;
87 a
88 01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
89 z
90 check table t extended;
91 Table Op Msg_type Msg_text
92 best.t check status OK
93 call check_table('t');
94 name mtype prtype len
95 a 2 800FE 255
96 # BINARY/VARBINARY test
97 create or replace table t (a varbinary(300));
98 insert into t values(NULL);
99 alter table t modify a binary(255);
100 affected rows: 1
101 info: Records: 1 Duplicates: 0 Warnings: 0
102 create or replace table t (a varbinary(200));
103 insert into t values (@bigval);
104 insert into t values ('z');
105 alter table t modify a binary(200);
106 affected rows: 2
107 info: Records: 2 Duplicates: 0 Warnings: 0
108 select count(a) from t where a = @bigval;
109 count(a)
110 1
111 select length(a) from t where left(a, 1) = 'z';
112 length(a)
113 200
114 check table t extended;
115 Table Op Msg_type Msg_text
116 best.t check status OK
117 call check_table('t');
118 name mtype prtype len
119 a 3 3F04FE 200
120 # BINARY enlargement
121 alter table t modify a binary(220);
122 affected rows: 2
123 info: Records: 2 Duplicates: 0 Warnings: 0
124 check table t extended;
125 Table Op Msg_type Msg_text
126 best.t check status OK
127 call check_table('t');
128 name mtype prtype len
129 a 3 3F04FE 220
130 # Convert from VARBINARY to a bigger BINARY
131 alter table t modify a varbinary(220);
132 affected rows: 2
133 info: Records: 2 Duplicates: 0 Warnings: 0
134 alter table t modify a binary(255);
135 affected rows: 2
136 info: Records: 2 Duplicates: 0 Warnings: 0
137 select count(a) from t where a = @bigval;
138 count(a)
139 0
140 select a, length(a) from t where a = 'z';
141 a length(a)
142 select * from t;
143 a
144 01234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
145 z
146 check table t extended;
147 Table Op Msg_type Msg_text
148 best.t check status OK
149 call check_table('t');
150 name mtype prtype len
151 a 3 3F04FE 255
152 # Integer conversions
153 create or replace table t (x tinyint);
154 insert into t values (127);
155 alter table t modify x smallint;
156 affected rows: 1
157 info: Records: 1 Duplicates: 0 Warnings: 0
158 select * from t;
159 x
160 127
161 check table t extended;
162 Table Op Msg_type Msg_text
163 best.t check status OK
164 call check_table('t');
165 name mtype prtype len
166 x 6 402 2
167 update t set x= 32767;
168 alter table t modify x mediumint;
169 affected rows: 1
170 info: Records: 1 Duplicates: 0 Warnings: 0
171 select * from t;
172 x
173 32767
174 check table t extended;
175 Table Op Msg_type Msg_text
176 best.t check status OK
177 call check_table('t');
178 name mtype prtype len
179 x 6 409 3
180 update t set x= 8388607;
181 alter table t modify x int;
182 affected rows: 1
183 info: Records: 1 Duplicates: 0 Warnings: 0
184 select * from t;
185 x
186 8388607
187 check table t extended;
188 Table Op Msg_type Msg_text
189 best.t check status OK
190 call check_table('t');
191 name mtype prtype len
192 x 6 403 4
193 update t set x= 2147483647;
194 alter table t modify x bigint;
195 affected rows: 1
196 info: Records: 1 Duplicates: 0 Warnings: 0
197 select * from t;
198 x
199 2147483647
200 check table t extended;
201 Table Op Msg_type Msg_text
202 best.t check status OK
203 call check_table('t');
204 name mtype prtype len
205 x 6 408 8
206 # Check IMPORT TABLESPACE
207 create or replace table t2 (x int);
208 alter table t2 discard tablespace;
209 create or replace table t1 (x tinyint);
210 insert into t1 set x= 42;
211 alter table t1 modify x int;
212 flush tables t1 for export;
213 unlock tables;
214 alter table t2 import tablespace;
215 select * from t2;
216 x
217 42
218 check table t2 extended;
219 Table Op Msg_type Msg_text
220 best.t2 check status OK
221 call check_table('t2');
222 name mtype prtype len
223 x 6 403 4
224 # Check innobase_col_to_mysql() len < flen
225 create or replace table t1 (x mediumint);
226 insert into t1 values (1);
227 insert into t1 values (1);
228 alter table t1 add column y int first, modify x int;
229 affected rows: 2
230 info: Records: 2 Duplicates: 0 Warnings: 0
231 alter table t1 add column z int first, add primary key (x);
232 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
233 # Check assertion in wrong instant operation
234 create or replace table t1 (a varchar(26) not null) default character set utf8mb4;
235 insert into t1 values ('abcdef'), (repeat('x',26));
236 alter ignore table t1 modify a varchar(25) not null;
237 affected rows: 2
238 info: Records: 2 Duplicates: 0 Warnings: 1
239 Warnings:
240 Warning 1265 Data truncated for column 'a' at row 2
241 select * from t1;
242 a
243 abcdef
244 xxxxxxxxxxxxxxxxxxxxxxxxx
245 # Check row_mysql_store_col_in_innobase_format()
246 create or replace table t1(x int primary key, a varchar(20));
247 insert into t1 (x) values (1);
248 update t1 set a= 'foo' where x = 2;
249 #
250 # MDEV-18124 PK on inplace-enlarged type fails
251 #
252 create or replace table t1 (x int, y int);
253 insert into t1 (x, y) values (11, 22);
254 alter table t1 modify x bigint;
255 affected rows: 1
256 info: Records: 1 Duplicates: 0 Warnings: 0
257 alter table t1 add primary key (x);
258 affected rows: 0
259 info: Records: 0 Duplicates: 0 Warnings: 0
260 select * from t1;
261 x y
262 11 22
263 check table t1;
264 Table Op Msg_type Msg_text
265 best.t1 check status OK
266 create or replace table t1 (a varchar(10), y int);
267 insert into t1 (a, y) values ("0123456789", 33);
268 alter table t1 modify a char(15);
269 affected rows: 1
270 info: Records: 1 Duplicates: 0 Warnings: 0
271 alter table t1 add primary key (a);
272 affected rows: 0
273 info: Records: 0 Duplicates: 0 Warnings: 0
274 select * from t1;
275 a y
276 0123456789 33
277 check table t1;
278 Table Op Msg_type Msg_text
279 best.t1 check status OK
280 create or replace table t1 (x int primary key, y int);
281 insert into t1 (x, y) values (44, 55);
282 alter table t1 modify x bigint;
283 affected rows: 1
284 info: Records: 1 Duplicates: 0 Warnings: 0
285 select * from t1;
286 x y
287 44 55
288 check table t1;
289 Table Op Msg_type Msg_text
290 best.t1 check status OK
291 create or replace table t1 (x int primary key, y int);
292 insert into t1 values (66, 77);
293 alter table t1 add column z int;
294 affected rows: 0
295 info: Records: 0 Duplicates: 0 Warnings: 0
296 alter table t1 drop column y;
297 affected rows: 0
298 info: Records: 0 Duplicates: 0 Warnings: 0
299 select * from t1;
300 x z
301 66 NULL
302 check table t1;
303 Table Op Msg_type Msg_text
304 best.t1 check status OK
305 create or replace table t1 (x integer, a varchar(20));
306 insert into t1 (x, a) values (73, 'a');
307 affected rows: 1
308 alter table t1 add index idx3 (a);
309 affected rows: 0
310 info: Records: 0 Duplicates: 0 Warnings: 0
311 alter table t1 modify a char(20);
312 affected rows: 1
313 info: Records: 1 Duplicates: 0 Warnings: 0
314 select * from t1;
315 x a
316 73 a
317 check table t1;
318 Table Op Msg_type Msg_text
319 best.t1 check status OK
320 drop database best;
321 #
322 # MDEV-33214 Table is getting rebuild with
323 # ALTER TABLE ADD COLUMN
324 #
325 use test;
326 CREATE TABLE t1(f1 INT, f2 VARCHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
327 INSERT INTO t1 VALUES(1,'abc'),(2,'def');
328 ALTER TABLE t1 ADD (f3 VARCHAR(5000), f4 VARCHAR(20)), ALGORITHM=instant;
329 ALTER TABLE t1 ADD f5 TEXT, ALGORITHM=INSTANT;
330 DROP TABLE t1;
331 # End of 10.4 tests