Debian Codegraph @

mysql-test/suite/innodb/include/instant_ddl_misc.inc

raw
1 ##########################################################################
2 # Test script to test miscellaneous for table having INSTANT
3 # ADD/DROP columns.
4 #
5 # This test is to be run in debug mode only and with debug flag
6 ##########################################################################
7
8 --source include/have_debug.inc
9 --source include/not_valgrind.inc
10 let $is_debug = 1;
11
12 SET SESSION debug= '+d,show_dropped_column';
13
14 --echo # Scenario 1
15 create table t1 (c1 char(10), c2 char(10));
16 --let $table_name=t1
17 --source suite/innodb/include/print_instant_metadata.inc
18 insert into t1 values ("r1c1", "r1c2");
19 select * from t1;
20 alter table t1 add column c3 char(10) default "def_c3" after c1;
21 --source suite/innodb/include/print_instant_metadata.inc
22 insert into t1 values ("r2c1", "r2c3", "r2c2");
23 select * from t1;
24 drop table t1;
25
26 --echo # Scenario 2
27 create table t1 (c1 char(10), c2 char(10), c3 char(10));
28 insert into t1 values ("r1c1", "r1c2", "r1c3");
29 alter table t1 drop column c2;
30 --let $table_name=t1
31 --source suite/innodb/include/print_instant_metadata.inc
32 select * from t1;
33 drop table t1;
34
35 --echo # Scenario 3
36 create table t1 (c1 char(10), c2 char(10));
37 insert into t1 values ("r1c1", "r1c2");
38 select * from t1;
39 alter table t1 add column c3 char(10) default "def_c3" after c1, algorithm=INSTANT;
40 insert into t1 values ("r2c1", "r2c3", "r2c2");
41 select * from t1;
42 alter table t1 drop column c2, algorithm=INSTANT;
43 select * from t1;
44 drop table t1;
45
46 --echo # Scenario 4
47 create table t1 (c1 char(10), c2 char(10), c3 char(10));
48 insert into t1 values ("r1c1", "r1c2", "r1c3");
49 select * from t1;
50 alter table t1 drop column c2;
51 select * from t1;
52 drop table t1;
53
54 --echo # Scenario 5
55 create table t1 (c1 char(10), c2 char(10));
56 insert into t1 values ("r1c1", "r1c2");
57 alter table t1 add column c3 char(10) default "def_c3" after c1;
58 select * from t1;
59 insert into t1 value ("r2c1", "r2c3", "r2c2");
60 alter table t1 drop column c2;
61 select * from t1;
62 drop table t1;
63
64 --echo # Scenario 6
65 create table t1 (c1 char(10), c2 char(10));
66 insert into t1 values ("r1c1", "r1c2");
67 alter table t1 add column c2 char(10) default "def_c3", drop column c2;
68 select * from t1;
69 insert into t1 value ("r2c1", "r2c2");
70 select * from t1;
71 drop table t1;
72
73 --echo # Scenario 7
74 create table t1 (c1 char(10), c2 char(10));
75 insert into t1 values ("r1c1", "r1c2");
76 alter table t1 add column c3 int after c1;
77 select * from t1;
78 insert into t1 value ("r2c1", 3, "r2c2");
79 select * from t1;
80 alter table t1 drop column c2;
81 select * from t1;
82 drop table t1;
83
84 --echo # Scenario 8
85 create table t1 (c1 char(10), c2 char(10), c3 char(10));
86 insert into t1 values ("r1c1", NULL, "r1c3");
87 select * from t1;
88 alter table t1 drop column c2;
89 --let $table_name=t1
90 --source suite/innodb/include/print_instant_metadata.inc
91 select * from t1;
92 drop table t1;
93
94 --echo # Scenario 9
95 --eval CREATE TABLE t1 (C1 char(10), C2 char(10), C3 char(10)) ROW_FORMAT=$row_format
96 INSERT INTO t1 values ("r1c1", "r1c2", "r1c3");
97 alter table t1 drop column c1;
98 select * from t1;
99 alter table t1 drop column c3;
100 select * from t1;
101 drop table t1;
102
103 --echo # Scenario 10
104 create table t1 (c1 char(10) KEY, c2 char(10), key sec_idx(c2));
105 insert into t1 values ("r1c1", "r1c2");
106 select * from t1;
107 alter table t1 add column c3 char(10) default "def_c3";
108 select * from t1;
109 insert into t1 values ("r2c1", "r2c2", "r3c3");
110 drop table t1;
111
112 --echo # Scenario 11
113 --eval CREATE TABLE t1(c2 char(100) , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB ROW_FORMAT=$row_format
114 ALTER TABLE t1 DROP INDEX idx1;
115 DROP TABLE t1;
116
117 --echo # Scenario 12
118 --eval CREATE TABLE t1(c2 char(100)) ROW_FORMAT=$row_format
119 ALTER TABLE t1 ADD FULLTEXT INDEX `ft_idx` (c2);
120 DROP TABLE t1;
121
122 --echo # Scenario 13
123 --eval CREATE TABLE t1(a INT) ROW_FORMAT=$row_format
124 INSERT INTO t1 VALUES(1);
125 ALTER TABLE t1 add COLUMN (f INT generated always as (a+1) virtual, g INT );
126 ALTER TABLE t1 add COLUMN (h INT generated always as (a+1) virtual), add INDEX idx (h), algorithm=inplace;
127 DROP TABLE t1;
128
129 --echo # Scenario 14
130 --eval CREATE TABLE t1 (col1 int(11) DEFAULT NULL, col2 int(11) DEFAULT NULL, col3 int(11) DEFAULT NULL, col4 int(11) DEFAULT NULL, col5 int(11) GENERATED ALWAYS AS (col4 * col2) VIRTUAL, col6 int(11) GENERATED ALWAYS AS (col2 % col4) VIRTUAL, col7 int(11) GENERATED ALWAYS AS (col5 / col6) VIRTUAL, col8 int(11) GENERATED ALWAYS AS (col5 + col5) VIRTUAL, col9 text, extra int(11) DEFAULT NULL) ROW_FORMAT=$row_format
131
132 ALTER TABLE t1 DROP COLUMN col7;
133 ALTER TABLE t1 DROP COLUMN col6, DROP COLUMN col9;
134 DROP TABLE t1;
135
136 --echo # Scenario 15
137 create table t1(a text, b text, fulltext(a,b));
138 insert into t1 values('text1', 'text2'); insert into t1 values('test1', 'test2');
139 alter table t1 add fulltext(b);
140 drop table t1;
141
142 --echo # Scenario 16
143 --eval CREATE TABLE t1 (a serial key, b text) ROW_FORMAT=$row_format
144 INSERT INTO t1(b) VALUES(repeat("a short string - ",5));
145 INSERT INTO t1(b) VALUES(repeat("a long string - ",50));
146 INSERT INTO t1(b) SELECT b FROM t1;
147 ALTER TABLE t1 ADD COLUMN (c int), algorithm=INSTANT;
148 --error 1845
149 ALTER TABLE t1 ADD INDEX c (c), algorithm=INSTANT;
150 ALTER TABLE t1 ADD INDEX c (c), algorithm=INPLACE;
151 DROP TABLE t1;
152
153 --echo # Scenario 17
154 --eval CREATE TABLE t1 (c1 char(20), c2 char(20), c3 char(20), c4 char(20)) ROW_FORMAT=$row_format
155 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
156 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
157 ALTER TABLE test.t1 ADD c6 char(20), DROP COLUMN c3;
158 DROP TABLE t1;
159
160 --echo # Scenario 18
161 --eval CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT DEFAULT 1, ct TEXT, INDEX(c2)) ROW_FORMAT=$row_format
162 ALTER TABLE t1 DISCARD TABLESPACE;
163 ALTER TABLE t1 ADD FULLTEXT INDEX (ct), ALGORITHM=INPLACE;
164 ALTER TABLE t1 CHANGE c2 c2 INT AFTER c1, ALGORITHM=INPLACE;
165 DROP TABLE t1;
166
167 --echo # Scenario 19
168 --eval CREATE TABLE t1 (a INT, b INT, c INT, d INT) ENGINE=InnoDB ROW_FORMAT=$row_format
169 ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 FIRST;
170 ALTER TABLE t1 CHANGE aa e INT;
171 INSERT INTO t1 VALUES (1,1,1,1,1);
172 ALTER TABLE t1 ADD COLUMN a INT DEFAULT 1 FIRST, DROP COLUMN a, CHANGE b b INT;
173 ALTER TABLE t1 ADD COLUMN aa INT DEFAULT 1 AFTER a, DROP COLUMN b, CHANGE c c INT;
174 ALTER TABLE t1 CHANGE aa b INT;
175 DROP TABLE t1;
176
177 --echo # Scenario 20
178 --eval CREATE TABLE t1 (a varchar(512) NOT NULL, b varchar(512) NOT NULL, FULLTEXT KEY fts_idx (a)) ROW_FORMAT=$row_format
179 ALTER TABLE t1 DROP KEY fts_idx;
180 ALTER TABLE t1 ADD COLUMN b_hash char(64) AS(b) VIRTUAL NOT NULL;
181 DROP TABLE t1;
182
183 --echo # Scenario 21
184 Delimiter |;
185 CREATE PROCEDURE test.fill_table (IN start INT) BEGIN SET @idx =start; WHILE (@idx > 0) DO INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa"); SET @idx = @idx - 1; END WHILE; END|
186 Delimiter ;|
187 --eval CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=$row_format
188 ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT;
189 call fill_table(400);
190 DROP PROCEDURE test.fill_table;
191 DROP TABLE t1;
192
193 --echo # Scenario 22
194 --eval CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ROW_FORMAT=$row_format
195 ALTER TABLE t1 ADD COLUMN v VARCHAR(255) DEFAULT "default_value", ALGORITHM=INSTANT;
196 SET GLOBAL innodb_limit_optimistic_insert_debug=2;
197 INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa");
198 INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa");
199 INSERT INTO test.t1 (v) VALUES("aaaaaaaaaaaaa");
200 SELECT * FROM t1;
201 SET GLOBAL innodb_limit_optimistic_insert_debug=0;
202 DROP TABLE t1;
203
204 --echo # Scenario 23
205 create table t1 (c1 char(10), c2 char(10));
206 --let $table_name=t1
207 --source suite/innodb/include/print_instant_metadata.inc
208 insert into t1 values ("r1c1", "r1c2");
209 select * from t1;
210 alter table t1 add column c3 char(10) default "def_c3" after c1;
211 --source suite/innodb/include/print_instant_metadata.inc
212 insert into t1 values ("r2c1", "r2c3", "r2c2");
213 select * from t1;
214 alter table t1 add column c3 char(10) default "newnewnew", drop column c3;
215 select * from t1;
216 DROP TABLE t1;
217
218 --echo # Scenario 24
219 --eval CREATE TABLE t1 (c1 char(10)) ROW_FORMAT=$row_format
220 ALTER TABLE t1 ADD COLUMN c2 INT;
221 ALTER TABLE t1 DROP COLUMN c2;
222 INSERT INTO t1 (c1) VALUES("aaaa");
223 select * from t1;
224 check table t1;
225 DROP TABLE t1;
226
227 --echo # Scenario 25
228 --eval CREATE TABLE t1 (c1 int, c2 char(10)) ROW_FORMAT=$row_format PARTITION BY HASH (c1) PARTITIONS 2
229 alter table t1 drop column c2;
230 DROP TABLE t1;
231
232 --echo # Scenario 26
233 --eval CREATE TABLE t1 (c1 char(10) KEY, c2 char(10)) ROW_FORMAT=$row_format
234 INSERT INTO t1 values ("row1_c1", "row1_c2");
235 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "default_c3", ALGORITHM=INSTANT;
236 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
237 ALTER TABLE t1 ADD COLUMN c4 char(20) DEFAULT NULL AFTER c1, ALGORITHM=INSTANT;
238 INSERT INTO t1 values ("row3_c1", "row3_c4", "row3_c2", "row3_c3");
239 ALTER TABLE t1 force;
240 SELECT * FROM t1;
241 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
242 ALTER TABLE t1 ADD COLUMN c5 INT after c2, ALGORITHM=INSTANT;
243 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
244 DROP TABLE t1;
245
246 --echo # Scenario 27
247 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
248 ALTER TABLE t1 ADD COLUMN c INT NOT NULL;
249 ALTER TABLE t1 ADD COLUMN d VARCHAR(100);
250 INSERT INTO t1 VALUES(0, 5, 20, 'Hello world');
251 DROP TABLE t1;
252
253 --echo # Scenario 28
254 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
255 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
256 SELECT * from t1;
257 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 100, ADD COLUMN d INT GENERATED ALWAYS AS ((c * 2 + b)) VIRTUAL, ADD COLUMN e VARCHAR(100) DEFAULT 'Hello world';
258 SELECT * from t1;
259 UPDATE t1 SET c = 200 WHERE a > 3;
260 SELECT * from t1;
261 DROP TABLE t1;
262
263 --echo # Scenario 29
264 --eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT) ROW_FORMAT=$row_format;
265 INSERT INTO t1 VALUES(0, 1);
266 SELECT * from t1;
267 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT;
268 SELECT * from t1;
269 ALTER TABLE t1 ADD COLUMN c4 CHAR(100), ALGORITHM=INSTANT;
270 SELECT * from t1;
271 UPDATE t1 SET c3 = 2;
272 SELECT * from t1;
273 DROP TABLE t1;
274
275 --echo # Scenario 30
276 --eval CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format;
277 INSERT INTO t1 VALUES("r1c1", "r1c2");
278 SELECT * from t1;
279 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT;
280 SELECT * from t1;
281 ALTER TABLE t1 ADD COLUMN c4 char(100), ALGORITHM=INSTANT;
282 SELECT * from t1;
283 UPDATE t1 SET c3 = 2;
284 SELECT * from t1;
285 DROP TABLE t1;
286
287 --echo # Scenario 31
288 --eval CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
289 INSERT INTO t1 VALUES("r1c1", "r1c2");
290 ALTER TABLE t1 ADD COLUMN c3 char(10) default "def_c3";
291 SELECT * from t1;
292 UPDATE t1 SET c3 = "new_c3";
293 SELECT * from t1;
294 UPDATE t1 SET c3 = "new2_c3";
295 SELECT * from t1;
296 DROP TABLE t1;
297
298 --echo # Scenario 32
299 --eval CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b)) ROW_FORMAT=$row_format
300 INSERT INTO t1 VALUES(0, 1), (1, 2), (2, 3), (3, 4);
301 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
302 CHECK TABLE t1;
303 SELECT * FROM t1;
304 UPDATE t1 SET c = b WHERE b <= 2;
305 CHECK TABLE t1;
306 SELECT * FROM t1;
307 DROP TABLE t1;
308
309 --echo # Scenario 33
310 --eval CREATE TABLE t1(c1 longtext) ROW_FORMAT=$row_format
311 INSERT INTO t1 VALUES (repeat('x', 8098));
312 SELECT SUBSTRING(c1, 1000, 32) FROM t1;
313 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT;
314 SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1;
315 BEGIN;
316 UPDATE t1 SET c2 = 0;
317 SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1;
318 ROLLBACK;
319 SELECT SUBSTRING(c1, 1000, 32), c2 FROM t1;
320 DROP TABLE t1;
321
322 --echo # Scenario 34
323 --eval CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
324 INSERT INTO t1 VALUES("r1c1", "r1c2");
325 SELECT * FROM t1;
326 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT;
327 SELECT * FROM t1;
328 SET GLOBAL innodb_log_checkpoint_now = ON;
329 START TRANSACTION;
330 INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3");
331 SELECT * FROM t1;
332 --source include/expect_crash.inc
333 SET SESSION DEBUG="+d,crash_commit_after";
334 --error 2013
335 COMMIT;
336 --source include/start_mysqld.inc
337 CHECK TABLE t1;
338 SELECT * FROM t1;
339 INSERT INTO t1 VALUES("r3c1", "r3c2", "r3c3");
340 START TRANSACTION;
341 UPDATE t1 SET c2 = "temp";
342 SELECT * FROM t1;
343 --source include/expect_crash.inc
344 SET SESSION DEBUG="+d,crash_commit_after";
345 --error 2013
346 COMMIT;
347 --source include/start_mysqld.inc
348 CHECK TABLE t1;
349 SELECT * FROM t1;
350 DROP TABLE t1;
351
352 --echo # Scenario 35
353 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format
354 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5);
355 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 10, algorithm=INSTANT;
356 CHECK TABLE t1;
357 START TRANSACTION;
358 INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20);
359 SELECT * FROM t1;
360 --source include/expect_crash.inc
361 SET SESSION DEBUG="+d,crash_commit_after";
362 --error 2013
363 COMMIT;
364 --source include/start_mysqld.inc
365 CHECK TABLE t1;
366 SELECT * FROM t1;
367 INSERT INTO t1 VALUES(0, 6, 20), (0, 7, 20);
368 START TRANSACTION;
369 UPDATE t1 SET b = 10;
370 SELECT * FROM t1;
371 --source include/expect_crash.inc
372 SET SESSION DEBUG="+d,crash_commit_after";
373 --error 2013
374 COMMIT;
375 --source include/start_mysqld.inc
376 CHECK TABLE t1;
377 SELECT * FROM t1;
378 DROP TABLE t1;
379
380 --echo # Scenario 36
381 --eval CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
382 INSERT INTO t1 VALUES("r1c1", "r1c2");
383 SELECT * FROM t1;
384 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT;
385 INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3");
386 SELECT * FROM t1;
387 START TRANSACTION;
388 UPDATE t1 SET c2 = "temp";
389 SELECT * FROM t1;
390 --source include/expect_crash.inc
391 SET SESSION DEBUG="+d,crash_commit_after";
392 --error 2013
393 COMMIT;
394 --source include/start_mysqld.inc
395 CHECK TABLE t1;
396 SELECT * FROM t1;
397 DROP TABLE t1;
398
399 --echo # Scenario 37
400 --eval CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
401 INSERT INTO t1 VALUES("r1c1", "r1c2");
402 SELECT * FROM t1;
403 ALTER TABLE t1 ADD COLUMN c3 char(10) DEFAULT "c3_def", ALGORITHM=INSTANT;
404 INSERT INTO t1 VALUES("r2c1", "r2c2", "r2c3");
405 SELECT * FROM t1;
406 SET GLOBAL innodb_log_checkpoint_now = ON;
407 START TRANSACTION;
408 UPDATE t1 SET c3 = "temp";
409 SELECT * FROM t1;
410 --source include/expect_crash.inc
411 SET SESSION DEBUG="+d,crash_commit_after";
412 --error 2013
413 COMMIT;
414 --source include/start_mysqld.inc
415 CHECK TABLE t1;
416 SELECT * FROM t1;
417 DROP TABLE t1;
418
419 --echo # Scenario 38
420 --eval CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=$row_format PARTITION BY RANGE(col1 * 2) ( PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256) , PARTITION p2 VALUES LESS THAN (384) , PARTITION p3 VALUES LESS THAN MAXVALUE);
421 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
422 ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT;
423 CHECK TABLE t1;
424 SELECT count(*) AS `Expect 4` FROM information_schema.innodb_tables WHERE instant_cols != 0;
425 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
426 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
427 CHECK TABLE t1;
428 ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
429 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
430 CHECK TABLE t1;
431 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
432 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
433 CHECK TABLE t1;
434 ALTER TABLE t1 ADD KEY idx3(col3);
435 SELECT count(*) AS `Expect 3` FROM information_schema.innodb_tables WHERE instant_cols != 0;
436 CHECK TABLE t1;
437 SELECT * FROM t1;
438 DROP TABLE t1;
439
440 --echo # Scenario 39
441 --eval CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=$row_format PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);
442 INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3");
443 SELECT * FROM t1;
444 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%";
445 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4";
446 SELECT * FROM t1;
447 CHECK TABLE t1;
448 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%";
449 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100));
450 SELECT * FROM t1;
451 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%";
452 CHECK TABLE t1;
453 ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
454 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%";
455 CHECK TABLE t1;
456 --error 1520
457 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
458 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%";
459 CHECK TABLE t1;
460 ALTER TABLE t1 ADD KEY idx3(c3);
461 SELECT NAME, N_COLS, TOTAL_ROW_VERSIONS, INITIAL_COLUMN_COUNTS, CURRENT_COLUMN_COUNTS, TOTAL_COLUMN_COUNTS FROM information_schema.innodb_tables WHERE name like "%t1%";
462 CHECK TABLE t1;
463 SELECT * FROM t1;
464 DROP TABLE t1;
465
466 --echo # Scenario 40
467 --eval CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=$row_format PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);
468 INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3");
469 SELECT * FROM t1;
470 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4";
471 CHECK TABLE t1;
472 SELECT * FROM t1;
473 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100));
474 CHECK TABLE t1;
475 SELECT * FROM t1;
476 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4);
477 CHECK TABLE t1;
478 SELECT * FROM t1;
479 --error 1520
480 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
481 CHECK TABLE t1;
482 SELECT * FROM t1;
483 ALTER TABLE t1 ADD KEY idx3(c3);
484 CHECK TABLE t1;
485 SELECT * FROM t1;
486 DROP TABLE t1;
487
488 --echo # Scenario 41
489 --eval CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=$row_format PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (128), PARTITION p1 VALUES LESS THAN (256), PARTITION p2 VALUES LESS THAN (384), PARTITION p3 VALUES LESS THAN MAXVALUE);
490 INSERT INTO t1 VALUES(1, 2, 3, 'abcdefg'), (100, 200, 300, 'qwerty'), (200, 300, 400, 'asdfg');
491 ALTER TABLE t1 ALGORITHM DEFAULT, ADD COLUMN col5 VARCHAR(500) default "def", ADD COLUMN col6 TEXT;
492 CHECK TABLE t1;
493 SELECT * FROM t1;
494 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (64), PARTITION p0_b VALUES LESS THAN (128));
495 CHECK TABLE t1;
496 SELECT * FROM t1;
497 ALTER TABLE t1 ALGORITHM DEFAULT, ADD KEY idx4(col4(10));
498 CHECK TABLE t1;
499 SELECT * FROM t1;
500 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
501 CHECK TABLE t1;
502 SELECT * FROM t1;
503 ALTER TABLE t1 ADD KEY idx3(col3);
504 CHECK TABLE t1;
505 SELECT * FROM t1;
506 DROP TABLE t1;
507
508 --echo # Scenario 42
509 --eval CREATE TABLE t1 (c1 INT, c2 char(10), c3 char(10)) ROW_FORMAT=$row_format PARTITION BY RANGE(c1) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200) , PARTITION p2 VALUES LESS THAN MAXVALUE);
510 INSERT INTO t1 VALUES(1, "r1c2", "r1c3"), (100, "r2c2", "r2c3"), (200, "r3c2", "r3c3");
511 SELECT * FROM t1;
512 ALTER TABLE t1 ALGORITHM INSTANT, ADD COLUMN col4 CHAR(10) DEFAULT "def_4";
513 CHECK TABLE t1;
514 SELECT * FROM t1;
515 ALTER TABLE t1 ALGORITHM INSTANT, DROP COLUMN c2;
516 CHECK TABLE t1;
517 SELECT * FROM t1;
518 ALTER TABLE t1 ALGORITHM INPLACE, REORGANIZE PARTITION p0 INTO (PARTITION p0_a VALUES LESS THAN (50), PARTITION p0_b VALUES LESS THAN (100));
519 CHECK TABLE t1;
520 SELECT * FROM t1;
521 ALTER TABLE t1 ALGORITHM INPLACE, ADD KEY idx4(col4);
522 CHECK TABLE t1;
523 SELECT * FROM t1;
524 --error 1520
525 ALTER TABLE t1 ALGORITHM DEFAULT, LOCK EXCLUSIVE, REORGANIZE PARTITION p0_a, p0_b INTO (PARTITION p0 VALUES LESS THAN (128) TABLESPACE innodb_file_per_table);
526 CHECK TABLE t1;
527 SELECT * FROM t1;
528 ALTER TABLE t1 ADD KEY idx3(c3);
529 CHECK TABLE t1;
530 SELECT * FROM t1;
531 DROP TABLE t1;
532
533 --echo # Scenario 43
534 --eval CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY HASH(a) PARTITIONS 3;
535 INSERT INTO t1 VALUES(0, 1), (0, 2), (0, 3), (0, 4), (0, 5), (0, 6), (0, 7), (0, 8);
536 SELECT * FROM t1;
537 ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 5;
538 CHECK TABLE t1;
539 SELECT * FROM t1;
540 ALTER TABLE t1 ADD PARTITION PARTITIONS 10;
541 CHECK TABLE t1;
542 SELECT * FROM t1;
543 ALTER TABLE t1 ADD KEY(b);
544 CHECK TABLE t1;
545 SELECT * FROM t1;
546 DROP TABLE t1;
547
548 --echo # Scenario 44
549 --eval CREATE TABLE t1 (a BLOB, b BLOB, c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, h VARCHAR(10) DEFAULT NULL) ROW_FORMAT=$row_format
550 INSERT INTO t1 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk");
551 CREATE INDEX idx ON t1(c(100));
552 SELECT length(c) FROM t1;
553 START TRANSACTION;
554 INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
555 ROLLBACK;
556 INSERT INTO t1 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
557 START TRANSACTION;
558 UPDATE t1 SET a = REPEAT('m', 16000) WHERE a like "aaa%";
559 ROLLBACK;
560 DROP TABLE t1;
561
562 --echo # Scenario 45
563 --eval CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10), j INT, m INT GENERATED ALWAYS AS(b + j), n VARCHAR(10), p VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), INDEX idx1(c), INDEX idx2 (m), INDEX idx3(p)) ROW_FORMAT=$row_format
564 INSERT INTO t1 VALUES(11, 22, DEFAULT, "AAA", 8, DEFAULT, "XXX", DEFAULT);
565 INSERT INTO t1 VALUES(1, 2, DEFAULT, "uuu", 9, DEFAULT, "uu", DEFAULT);
566 INSERT INTO t1 VALUES(3, 4, DEFAULT, "uooo", 1, DEFAULT, "umm", DEFAULT);
567 ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;
568 ALTER TABLE t1 DROP COLUMN p, ADD COLUMN s VARCHAR(20) GENERATED ALWAYS AS(CONCAT(n, h)), algorithm=inplace;
569 DROP TABLE t1;
570
571 --echo # Scenario 46
572 connect (conn1, localhost, root,,);
573 connection default;
574 --eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT) ROW_FORMAT=$row_format
575 INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2), (3, 3, 3), (6, 6, 6);
576 ALTER TABLE t1 ADD COLUMN d INT DEFAULT 10;
577 SELECT * FROM t1;
578 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done';
579 --send ALTER TABLE t1 DROP COLUMN c, algorithm=inplace;
580 connection conn1;
581 SET DEBUG_SYNC = 'now WAIT_FOR altered';
582 INSERT INTO t1(a, b, c) VALUES(7, 7, 7);
583 UPDATE t1 SET a = 10 WHERE a = 1;
584 DELETE FROM t1 WHERE a = 2;
585 UPDATE t1 SET c = 10 WHERE a = 3;
586 UPDATE t1 SET d = 20 WHERE a = 5;
587 UPDATE t1 SET a = 8, d = 20 WHERE a = 6;
588 SET DEBUG_SYNC = 'now SIGNAL dmls_done';
589 disconnect conn1;
590 connection default;
591 reap;
592 SELECT * FROM t1;
593 DROP TABLE t1;
594
595 --echo # Scenario 47
596 --eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20))
597 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
598 SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1;
599 ALTER TABLE t1 REORGANIZE PARTITION p2 INTO (PARTITION p21 VALUES LESS THAN(15), PARTITION p22 VALUES LESS THAN(20));
600 SELECT NAME, POS, MTYPE, PRTYPE, LEN, HAS_DEFAULT, DEFAULT_VALUE, VERSION_ADDED, VERSION_DROPPED, PHYSICAL_POS FROM information_schema.innodb_columns WHERE has_default = 1;
601 DROP TABLE t1;
602
603 --echo # Scenario 48
604 --eval CREATE TABLE t1 (c1 integer PRIMARY KEY AUTO_INCREMENT, c2 integer NULL UNIQUE) ROW_FORMAT=$row_format
605 ALTER TABLE t1 ADD COLUMN c3 int;
606 ALTER TABLE t1 DROP COLUMN c3;
607 ALTER TABLE t1 ADD COLUMN c4 int DEFAULT 1;
608 ALTER TABLE t1 CHANGE COLUMN c4 c4_new int NULL DEFAULT 1;
609 ALTER TABLE t1 CHANGE COLUMN c4_new c4_new int NULL DEFAULT 1;
610 DROP TABLE t1;
611
612 --echo # Scenario 49
613 --eval CREATE TABLE t1 (c1 char(20) KEY, c2 enum('a', 'b', 'c')) ROW_FORMAT=$row_format
614 INSERT INTO t1 values ("row1_c1", "a");
615 SELECT * from t1;
616 ALTER TABLE t1 ADD COLUMN c3 enum('x', 'y', 'z'), ALGORITHM=INSTANT;
617 SELECT * from t1;
618 INSERT INTO t1 values ("row2_c2", "b", "y");
619 SELECT * from t1;
620 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
621 SELECT * from t1;
622 DROP TABLE t1;
623
624 --echo # Scenario 50
625 --eval CREATE TABLE t1 (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
626 ALTER TABLE t1 DROP COLUMN c2;
627 truncate table t1;
628 truncate table t1;
629 truncate table t1;
630 DROP TABLE t1;
631
632 --echo # Scenario 51
633 --eval CREATE TABLE t1 (c1 int KEY, c2 int) ROW_FORMAT=$row_format PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20), PARTITION p2 VALUES LESS THAN (30))
634 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
635 ALTER TABLE t1 DROP PARTITION p1;
636 DROP TABLE t1;
637
638 --echo # Scenario 52
639 --eval CREATE TABLE t1(c1 int, c2 int, c3 int, c4 DOUBLE GENERATED ALWAYS AS (c1+c2) STORED, c5 DOUBLE GENERATED ALWAYS AS (c1-c2) VIRTUAL) ROW_FORMAT=$row_format;
640 INSERT INTO t1(c1,c2,c3) VALUES (1,1,1);
641 SELECT * FROM t1;
642 --error ER_DEPENDENT_BY_GENERATED_COLUMN
643 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
644 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
645 SELECT * FROM t1;
646 ALTER TABLE t1 DROP COLUMN c4, ALGORITHM=INSTANT;
647 SELECT * FROM t1;
648 ALTER TABLE t1 ADD COLUMN c6 DOUBLE GENERATED ALWAYS AS (2*c1+c2) VIRTUAL, ALGORITHM=INSTANT;
649 SELECT * FROM t1;
650 --error ER_ALTER_OPERATION_NOT_SUPPORTED
651 ALTER TABLE t1 ADD COLUMN c7 DOUBLE GENERATED ALWAYS AS (c1+2*c2) STORED, ALGORITHM=INSTANT;
652 SELECT * FROM t1;
653 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
654 ALTER TABLE t1 ADD COLUMN c8 DOUBLE GENERATED ALWAYS AS (c1+2*c2) VIRTUAL FIRST, ALGORITHM=INSTANT;
655 SELECT * FROM t1;
656 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT;
657 SELECT * FROM t1;
658 ALTER TABLE t1 DROP column c3, add column c10 int as (c1+c2) virtual, ALGORITHM=INSTANT;
659 SELECT * FROM t1;
660 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT;
661 SELECT * FROM t1;
662 ALTER TABLE t1 DROP column c3, drop column c6, ALGORITHM=INSTANT;
663 SELECT * FROM t1;
664 ALTER TABLE t1 add column c3 int after c2, ALGORITHM=INSTANT;
665 SELECT * FROM t1;
666 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
667 ALTER TABLE t1 DROP column c3, add column c11 int as (c1+c2) virtual first, ALGORITHM=INSTANT;
668 SELECT * FROM t1;
669 DROP TABLE t1;
670
671 --echo # Scenario 53
672 --echo # Table with functional index.
673 --eval CREATE TABLE t1 (id int NOT NULL AUTO_INCREMENT KEY, c1 varchar(10) DEFAULT NULL, c2 varchar(10) DEFAULT NULL, INDEX idx((concat(c1, "c1")))) ROW_FORMAT=$row_format;
674 INSERT INTO t1(c1, c2) values("abc", "def");
675 SELECT * FROM t1;
676 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
677 ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual after id, ALGORITHM=INSTANT;
678 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
679 ALTER TABLE t1 DROP COLUMN c2, ADD COLUMN c3 varchar(10) as (c1) virtual, ALGORITHM=INSTANT;
680 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
681 SELECT * FROM t1;
682 DROP TABLE t1;
683
684 --echo # Scenarion 54
685 --eval CREATE TABLE t1 (c1 TEXT, c2 INT, PRIMARY KEY (c1(1))) ROW_FORMAT=$row_format;
686 SELECT * FROM t1;
687 REPLACE INTO t1(c1) VALUES ('');
688 REPLACE INTO t1(c1) VALUES ('');
689 SELECT * From t1;
690 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
691 REPLACE INTO t1(c1) VALUES ('');
692 DROP TABLE t1;
693
694 --echo # Scenario 55
695 --eval CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT) ROW_FORMAT=$row_format PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (10), PARTITION p1 VALUES LESS THAN (20))
696
697 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT;
698 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
699
700 ALTER TABLE t1 ADD COLUMN c2 INT, ALGORITHM=INSTANT;
701 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
702
703 ALTER TABLE t1 ADD PARTITION (PARTITION p5 VALUES LESS THAN (60));
704
705 ALTER TABLE t1 ADD COLUMN c3 INT, ALGORITHM=INSTANT;
706 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
707
708 ALTER TABLE t1 REORGANIZE PARTITION p5 INTO
709 (
710 PARTITION p51 VALUES LESS THAN (50),
711 PARTITION p52 VALUES LESS THAN (60)
712 );
713
714 DROP TABLE t1;
715
716 --echo # Scenario 56
717 --eval CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT, PRIMARY KEY(c1, c4(10))) ROW_FORMAT=$row_format;
718 INSERT INTO t1 VALUES (1, 2, 3, "abcdefghijklmnop");
719 SELECT * FROM t1;
720
721 ALTER TABLE t1 ADD COLUMN c5 INT, ALGORITHM=INSTANT;
722 SELECT * FROM t1;
723
724 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
725 SELECT * FROM t1;
726
727 DROP TABLE t1;
728
729 --echo # Scenario 57
730 --eval CREATE TABLE t1 (c1 CHAR(20), c2 CHAR(20), PRIMARY KEY PK(c1(4))) ROW_FORMAT=$row_format;
731 INSERT INTO t1 VALUES ("r1c1", "r1c2");
732 SELECT * FROM t1;
733 ALTER TABLE t1 ADD COLUMN c3 char(20) DEFAULT "c3_def", ALGORITHM=INSTANT;
734 INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3");
735 SELECT * FROM t1;
736 DROP TABLE t1;
737
738 --echo # Scenario 58
739 --echo # Validating record in table with INSTANT ADD/DROP columns
740 eval CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=$row_format;
741 INSERT INTO t1 VALUES ("r1c1", "r1c2");
742 ALTER TABLE t1 ADD COLUMN c3 CHAR(10), ALGORITHM=INSTANT;
743 INSERT INTO t1 VALUES ("r2c1", "r2c2", "r2c3");
744 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT;
745 INSERT INTO t1 (c2) VALUES ("r3c2");
746 ALTER TABLE t1 ADD COLUMN c4 CHAR(10), ALGORITHM=INSTANT;
747 INSERT INTO t1 (c2) VALUES ("r4c2");
748 SELECT * From t1;
749 DROP TABLE t1;
750
751 eval CREATE TABLE t1 (id INT KEY, c2 CHAR(10)) ROW_FORMAT=$row_format PARTITION BY RANGE( id ) (PARTITION P0 VALUES LESS THAN (40), PARTITION P1 VALUES LESS THAN (50));
752 INSERT INTO t1 VALUES(1,"r1c2");
753 INSERT INTO t1 VALUES(2,"r2c2");
754 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
755 ALTER TABLE t1 ADD COLUMN c2 CHAR(10), ALGORITHM=INSTANT;
756 ALTER TABLE t1 REORGANIZE PARTITION P0, P1 INTO (PARTITION P1 VALUES LESS THAN (50));
757 SELECT * FROM t1;
758 DROP TABLE t1;
759
760 --echo # Scenario 59
761 --echo # A scenario when #fields were not updated correctly in REDUNDANT record for not-inplace update.
762 eval CREATE TABLE t1 (c1 VARCHAR(20), c2 CHAR(20)) ROW_FORMAT=$row_format;
763 INSERT INTO t1 VALUES ("r1c1", "r1c2");
764 SELECT * FROM t1;
765 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
766 UPDATE t1 SET c1="r1c11111111111";
767 SELECT * FROM t1;
768 DROP TABLE t1;
769
770 --echo # Scenario 60
771 --echo # When a new partition is added in a table with instant columns and virtual column,
772 --echo # during inheriting INSTANT metadat for new partitions, virtual columns were not skipped.
773 eval CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 TEXT) ROW_FORMAT=$row_format PARTITION BY RANGE(col1 * 2) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300));
774
775 ALTER TABLE t1 ADD COLUMN col5 INT, ADD COLUMN col6 INT GENERATED ALWAYS AS (col5 + 1) VIRTUAL, ALGORITHM=INSTANT;
776 ALTER TABLE t1 DROP COLUMN col2, algorithm=INSTANT;
777
778 ALTER TABLE t1 REORGANIZE PARTITION p2 INTO(PARTITION p21 VALUES LESS THAN (250), PARTITION p22 VALUES LESS THAN (300));
779 DROP TABLE t1;
780
781 --echo # Scenario 61
782 --echo # When a table is truncated, it's instant metadata is reset. It shall
783 --echo # also reset instant metadata of columns.
784 eval CREATE TABLE t1 (c1 VARCHAR(1008)) ROW_FORMAT=$row_format;
785
786 ALTER TABLE t1 ADD COLUMN c2 INT;
787 TRUNCATE TABLE t1;
788
789 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),1);
790 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),2);
791 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),3);
792 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),4);
793 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),5);
794 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),6);
795 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),7);
796 INSERT INTO t1 (c1, c2) VALUES (CONCAT(REPEAT('z', 1008)),8);
797
798 ALTER TABLE t1 ADD COLUMN c3 VARCHAR(500);
799 UPDATE t1 SET c3 = CONCAT(REPEAT('c', 500));
800
801 DROP TABLE t1;
802
803 --echo # Scenario 62
804 --echo # While updating lob columns on a rebuilt table which had INSTANT cols
805 eval CREATE TABLE t1 (c1 CHAR(10)) ROW_FORMAT=$row_format;
806 INSERT INTO t1 VALUES ("r1c1");
807 SELECT * FROM t1;
808
809 ALTER TABLE t1 ADD COLUMN c2 INT;
810 ALTER TABLE t1 DROP COLUMN c2;
811
812 ALTER TABLE t1 ADD COLUMN c3 TEXT;
813 UPDATE t1 SET c3 = CONCAT(REPEAT('z', 8204));
814
815 ALTER TABLE t1 ADD PRIMARY KEY (c3(10)), ADD FULLTEXT KEY idx (c3);
816 SELECT * FROM t1;
817
818 DROP TABLE t1;
819
820 --echo # Scenario 63
821 --echo # Truncate of a partition/partition table
822 eval CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT) ROW_FORMAT=$row_format PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20));
823 --let $table_name=t1
824 --source suite/innodb/include/print_instant_part_metadata.inc
825
826 INSERT INTO t1 values (5, 50), (15, 150);
827 SELECT * FROM t1 PARTITION (p1);
828 SELECT * FROM t1 PARTITION (p2);
829
830 ALTER TABLE t1 ADD COLUMN c INT DEFAULT 100;
831 --source suite/innodb/include/print_instant_part_metadata.inc
832
833 ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN(30));
834 INSERT INTO t1 (a, b) values (25, 250);
835
836 SELECT * FROM t1 PARTITION (p1);
837 SELECT * FROM t1 PARTITION (p2);
838 SELECT * FROM t1 PARTITION (p3);
839
840 alter table t1 drop column b, algorithm=instant;
841 --source suite/innodb/include/print_instant_part_metadata.inc
842 SELECT * FROM t1;
843
844 ALTER TABLE t1 TRUNCATE PARTITION p1;
845 --source suite/innodb/include/print_instant_part_metadata.inc
846 SELECT * FROM t1;
847
848 ALTER TABLE t1 TRUNCATE PARTITION p3;
849 --source suite/innodb/include/print_instant_part_metadata.inc
850 SELECT * FROM t1;
851
852 TRUNCATE TABLE t1;
853 --source suite/innodb/include/print_instant_part_metadata.inc
854 SELECT * FROM t1;
855
856 TRUNCATE TABLE t1;
857 ALTER TABLE t1 TRUNCATE PARTITION p1;
858 ALTER TABLE t1 TRUNCATE PARTITION p3;
859 --source suite/innodb/include/print_instant_part_metadata.inc
860 SELECT * FROM t1;
861
862 DROP TABLE t1;
863
864 --echo # Scenario 64
865 --echo # When nullbitmap size on physical record changes after INSTANT ADD columns.
866 eval CREATE TABLE t1 (c0 char(9) KEY, c1 char (10), c2 char(20), c3 char(30), c4 char(40), c5 char(50), c6 char(60), c7 char(70), c8 char(80)) ROW_FORMAT=$row_format;
867
868 SET GLOBAL innodb_limit_optimistic_insert_debug=2;
869
870 Insert into t1 values(
871 REPEAT("a", 8),
872 REPEAT("1", 9),
873 REPEAT("2", 19),
874 REPEAT("3", 29),
875 REPEAT("4", 39),
876 REPEAT("5", 49),
877 REPEAT("6", 59),
878 REPEAT("7", 69),
879 REPEAT("8", 79));
880
881 Insert into t1 values(
882 REPEAT("b", 8),
883 REPEAT("1", 9),
884 REPEAT("2", 19),
885 REPEAT("3", 29),
886 REPEAT("4", 39),
887 REPEAT("5", 49),
888 REPEAT("6", 59),
889 REPEAT("7", 69),
890 REPEAT("8", 79));
891
892 Alter table t1 add column c9 char(90);
893
894 Insert into t1 values(
895 REPEAT("c", 8),
896 REPEAT("1", 9),
897 REPEAT("2", 19),
898 REPEAT("3", 29),
899 REPEAT("4", 39),
900 REPEAT("5", 49),
901 REPEAT("6", 59),
902 REPEAT("7", 69),
903 REPEAT("8", 79),
904 REPEAT("9", 89));
905
906 SET GLOBAL innodb_limit_optimistic_insert_debug=0;
907
908 DROP table t1;
909
910 --echo # Scenario 65
911 --echo # Truncate all partition shall reset INSTANT Metadata
912 eval CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, c4 TEXT) ROW_FORMAT=$row_format PARTITION BY RANGE(c1) (PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (200), PARTITION p2 VALUES LESS THAN (300), PARTITION p3 VALUES LESS THAN MAXVALUE);
913
914 INSERT INTO t1 VALUES (90, 1, 1, "1"), (190, 1, 1, "2"), (290, 1, 1, "3"), (390, 1, 1, "3");
915
916 Alter table t1 add column c5 int, algorithm=INSTANT;
917 Alter table t1 drop column c3, algorithm=INSTANT;
918 --let $table_name=t1
919 --source suite/innodb/include/print_instant_part_metadata.inc
920
921 ALTER TABLE t1 TRUNCATE partition p0, p1, p2, p3;
922 --source suite/innodb/include/print_instant_part_metadata.inc
923
924 DROP TABLE t1;
925
926 --echo # Scenario 66
927 --echo # Purge crash
928 --eval create table t1 (c0 INT KEY, c1 varchar(10), c2 varchar(10)) ROW_FORMAT=$row_format
929
930 alter table t1 add column c4 char(10), algorithm=instant;
931 alter table t1 add index idx (c4);
932
933 insert into t1 values (1, "r1c1", "r1c2", "c4");
934 SET GLOBAL innodb_purge_stop_now=ON;
935 Update t1 set c2="r122222", c4="c44";
936
937 alter table t1 drop column c2, algorithm=instant;
938
939 SET GLOBAL innodb_purge_run_now=ON;
940 --source include/wait_innodb_all_purged.inc
941
942 DROP TABLE t1;
943
944 --echo # Scenario 66
945 --echo # Inplace update is failing
946 --echo # INSTANT ADD and INPLACE UPDATE
947 eval CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=$row_format;
948
949 Insert into t1 values ("r1c1", "r1c2", "r1c3");
950 Select * from t1;
951
952 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT;
953 Select * from t1;
954
955 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3");
956 Select * from t1;
957
958 --echo # Disable flush to make sure REDO is applied after restart
959 --disable_query_log
960 SET GLOBAL innodb_log_checkpoint_now = ON;
961 SET GLOBAL innodb_page_cleaner_disabled_debug = 1;
962 SET GLOBAL innodb_dict_stats_disabled_debug = 1;
963 SET GLOBAL innodb_master_thread_disabled_debug = 1;
964 --enable_query_log
965
966 Update t1 set c2="r112" where c2="r1c2";
967 Update t1 set c2="r212" where c2="r2c2";
968 Select * from t1;
969
970 --echo # Kill the running server
971 --source include/kill_mysqld.inc
972
973 --echo # Start the server
974 --source include/start_mysqld_no_echo.inc
975
976 Select * from t1;
977
978 DROP TABLE t1;
979
980 --echo # INSTANT DROP and INPLACE UPDATE
981 eval CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=$row_format;
982
983 Insert into t1 values ("r1c1", "r1c2", "r1c3");
984 Select * from t1;
985
986 Alter table t1 drop column c1, algorithm=instant;
987 Insert into t1 values ("r2c2", "r2c3");
988 Select * from t1;
989
990
991 SET GLOBAL innodb_log_checkpoint_now = ON;
992 SET GLOBAL innodb_page_cleaner_disabled_debug = 1;
993 SET GLOBAL innodb_dict_stats_disabled_debug = 1;
994 SET GLOBAL innodb_master_thread_disabled_debug = 1;
995
996 Update t1 set c2="r112" where c2="r1c2";
997 Update t1 set c2="r212" where c2="r2c2";
998 Select * from t1;
999
1000 --echo # Kill the running server
1001 --source include/kill_mysqld.inc
1002
1003 --echo # Start the server
1004 --source include/start_mysqld_no_echo.inc
1005
1006 Select * from t1;
1007
1008 DROP TABLE t1;
1009
1010 --echo # INSTANT ADD and DROP and INPLACE UPDATE
1011 eval CREATE TABLE t1 (c1 char(10), c2 char(10), c3 char(10)) ROW_FORMAT=$row_format;
1012
1013 Insert into t1 values ("r1c1", "r1c2", "r1c3");
1014 Select * from t1;
1015
1016 Alter table t1 add column c4 char(10) after c1, ALGORITHM=INSTANT;
1017 Select * from t1;
1018 Insert into t1 values ("r2c1", "r2c4", "r2c2", "r2c3");
1019 Select * from t1;
1020
1021 Alter table t1 drop column c1, algorithm=instant;
1022 Select * from t1;
1023 Insert into t1 values ("r3c4", "r3c2", "r3c3");
1024 Select * from t1;
1025
1026 SET GLOBAL innodb_log_checkpoint_now = ON;
1027 SET GLOBAL innodb_page_cleaner_disabled_debug = 1;
1028 SET GLOBAL innodb_dict_stats_disabled_debug = 1;
1029 SET GLOBAL innodb_master_thread_disabled_debug = 1;
1030
1031 Update t1 set c2="r112" where c2="r1c2";
1032 Update t1 set c2="r212" where c2="r2c2";
1033 Update t1 set c2="r312" where c2="r3c2";
1034 Select * from t1;
1035
1036 --echo # Kill the running server
1037 --source include/kill_mysqld.inc
1038
1039 --echo # Start the server
1040 --source include/start_mysqld_no_echo.inc
1041
1042 Select * from t1;
1043
1044 DROP TABLE t1;
1045
1046 --echo ###########
1047 --echo # CLEANUP #
1048 --echo ###########