Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/innodb_row_format.inc
3 --source include/maybe_debug.inc
4
5 -- echo #
6 -- echo # MDEV-15563: Instant ROW_FORMAT=REDUNDANT column type change&extension
7 -- echo # (reverted in MDEV-18627)
8 -- echo #
9
10 # Use character-set-server in test db
11 create database best;
12 use best;
13
14 set default_storage_engine=innodb;
15 set @bigval= repeat('0123456789', 30);
16
17 delimiter ~~;
18 create procedure check_table(table_name varchar(255))
19 begin
20 select table_id into @table_id
21 from information_schema.innodb_sys_tables
22 where name = concat('best/', table_name);
23 select name, mtype, hex(prtype) as prtype, len
24 from information_schema.innodb_sys_columns
25 where table_id = @table_id;
26 end~~
27 delimiter ;~~
28
29
30 --echo # VARCHAR -> CHAR, VARBINARY -> BINARY conversion
31 set @bigval= repeat('0123456789', 20);
32
33 create table t (a varchar(300));
34 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
35 alter table t modify a char(255), algorithm=instant;
36 alter table t modify a char(255), algorithm=copy;
37
38 create or replace table t (a varchar(200));
39 insert into t values (@bigval);
40 insert into t values ('z');
41 if ($have_debug) {
42 --disable_query_log
43 # This should not be reachable.
44 set @save_debug= @@SESSION.debug_dbug;
45 set debug_dbug= '+d,ib_instant_error';
46 --enable_query_log
47 }
48 --enable_info
49 alter table t modify a char(200);
50 --disable_info
51 select count(a) from t where a = @bigval;
52 select a, length(a) from t where a = 'z';
53
54 check table t extended;
55 call check_table('t');
56
57 --echo # CHAR enlargement
58 --enable_info
59 alter table t modify a char(220);
60 --disable_info
61 select count(a) from t where a = @bigval;
62 select a, length(a) from t where a = 'z';
63
64 check table t extended;
65 call check_table('t');
66
67 --enable_info
68 ALTER TABLE t CHANGE COLUMN a a CHAR(230) BINARY;
69 ALTER TABLE t ADD COLUMN b INT FIRST;
70 ALTER TABLE t DROP b;
71 --disable_info
72
73 check table t extended;
74 call check_table('t');
75
76 --echo # Convert from VARCHAR to a bigger CHAR
77 --enable_info
78 alter table t modify a varchar(200);
79 alter table t modify a char(255);
80 --disable_info
81 select count(a) from t where a = @bigval;
82 select a, length(a) from t where a = 'z';
83
84 select * from t;
85 check table t extended;
86 call check_table('t');
87
88 --echo # BINARY/VARBINARY test
89 create or replace table t (a varbinary(300));
90 insert into t values(NULL);
91 --enable_info
92 alter table t modify a binary(255);
93 --disable_info
94
95 create or replace table t (a varbinary(200));
96 insert into t values (@bigval);
97 insert into t values ('z');
98 --enable_info
99 alter table t modify a binary(200);
100 --disable_info
101 select count(a) from t where a = @bigval;
102 select length(a) from t where left(a, 1) = 'z';
103
104 check table t extended;
105 call check_table('t');
106
107 --echo # BINARY enlargement
108 --enable_info
109 alter table t modify a binary(220);
110 --disable_info
111
112 check table t extended;
113 call check_table('t');
114
115 --echo # Convert from VARBINARY to a bigger BINARY
116 --enable_info
117 alter table t modify a varbinary(220);
118 alter table t modify a binary(255);
119 --disable_info
120 select count(a) from t where a = @bigval;
121 select a, length(a) from t where a = 'z';
122
123 select * from t;
124 check table t extended;
125 call check_table('t');
126
127
128 --echo # Integer conversions
129 create or replace table t (x tinyint);
130 insert into t values (127);
131 --enable_info
132 alter table t modify x smallint;
133 --disable_info
134 select * from t;
135 check table t extended;
136 call check_table('t');
137
138 update t set x= 32767;
139 --enable_info
140 alter table t modify x mediumint;
141 --disable_info
142 select * from t;
143 check table t extended;
144 call check_table('t');
145
146 update t set x= 8388607;
147 --enable_info
148 alter table t modify x int;
149 --disable_info
150 select * from t;
151 check table t extended;
152 call check_table('t');
153
154 update t set x= 2147483647;
155 --enable_info
156 alter table t modify x bigint;
157 --disable_info
158 select * from t;
159 check table t extended;
160 call check_table('t');
161
162 if ($have_debug) {
163 --disable_query_log
164 # This should not be reachable.
165 set debug_dbug= @save_debug;
166 --enable_query_log
167 }
168
169 --echo # Check IMPORT TABLESPACE
170 --let $MYSQLD_DATADIR= `select @@datadir`
171 create or replace table t2 (x int);
172 alter table t2 discard tablespace;
173
174 create or replace table t1 (x tinyint);
175 insert into t1 set x= 42;
176 alter table t1 modify x int;
177 flush tables t1 for export;
178 --move_file $MYSQLD_DATADIR/best/t1.cfg $MYSQLD_DATADIR/best/t2.cfg
179 --copy_file $MYSQLD_DATADIR/best/t1.ibd $MYSQLD_DATADIR/best/t2.ibd
180 unlock tables;
181
182 alter table t2 import tablespace;
183
184 select * from t2;
185 check table t2 extended;
186 call check_table('t2');
187
188 --echo # Check innobase_col_to_mysql() len < flen
189 create or replace table t1 (x mediumint);
190 insert into t1 values (1);
191 insert into t1 values (1);
192 --enable_info
193 alter table t1 add column y int first, modify x int;
194 --error ER_DUP_ENTRY
195 alter table t1 add column z int first, add primary key (x);
196 --disable_info
197
198 --echo # Check assertion in wrong instant operation
199 create or replace table t1 (a varchar(26) not null) default character set utf8mb4;
200 insert into t1 values ('abcdef'), (repeat('x',26));
201 --enable_info
202 alter ignore table t1 modify a varchar(25) not null;
203 --disable_info
204 select * from t1;
205
206 --echo # Check row_mysql_store_col_in_innobase_format()
207 create or replace table t1(x int primary key, a varchar(20));
208 insert into t1 (x) values (1);
209 update t1 set a= 'foo' where x = 2;
210
211 --echo #
212 --echo # MDEV-18124 PK on inplace-enlarged type fails
213 --echo #
214 create or replace table t1 (x int, y int);
215 insert into t1 (x, y) values (11, 22);
216 --enable_info
217 alter table t1 modify x bigint;
218 alter table t1 add primary key (x);
219 --disable_info
220 select * from t1;
221 check table t1;
222
223 create or replace table t1 (a varchar(10), y int);
224 insert into t1 (a, y) values ("0123456789", 33);
225 --enable_info
226 alter table t1 modify a char(15);
227 alter table t1 add primary key (a);
228 --disable_info
229 select * from t1;
230 check table t1;
231
232 create or replace table t1 (x int primary key, y int);
233 insert into t1 (x, y) values (44, 55);
234 --enable_info
235 alter table t1 modify x bigint;
236 --disable_info
237 select * from t1;
238 check table t1;
239
240 create or replace table t1 (x int primary key, y int);
241 insert into t1 values (66, 77);
242 --enable_info
243 alter table t1 add column z int;
244 alter table t1 drop column y;
245 --disable_info
246 select * from t1;
247 check table t1;
248
249 create or replace table t1 (x integer, a varchar(20));
250 --enable_info
251 insert into t1 (x, a) values (73, 'a');
252 alter table t1 add index idx3 (a);
253 alter table t1 modify a char(20);
254 --disable_info
255 select * from t1;
256 check table t1;
257
258 drop database best;
259
260 --echo #
261 --echo # MDEV-33214 Table is getting rebuild with
262 --echo # ALTER TABLE ADD COLUMN
263 --echo #
264 use test;
265 CREATE TABLE t1(f1 INT, f2 VARCHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
266 INSERT INTO t1 VALUES(1,'abc'),(2,'def');
267 ALTER TABLE t1 ADD (f3 VARCHAR(5000), f4 VARCHAR(20)), ALGORITHM=instant;
268 ALTER TABLE t1 ADD f5 TEXT, ALGORITHM=INSTANT;
269 DROP TABLE t1;
270
271 --echo # End of 10.4 tests