Debian Codegraph @

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

raw
1 --source include/big_test.inc
2 --source include/not_have_innodb_4k.inc
3
4 --echo ##############################################
5 --echo # Test instant ADD COLUMN for REDUNDANT format
6 --echo ##############################################
7 let $row_format = REDUNDANT;
8 --source suite/innodb/include/instant_add_column_basic.inc
9
10 --echo ############################################
11 --echo # Test instant ADD COLUMN for DYNAMIC format
12 --echo ############################################
13 let $row_format = DYNAMIC;
14 --source suite/innodb/include/instant_add_column_basic.inc
15
16 --echo ############################################
17 --echo # Test instant ADD COLUMN for COMPACT format
18 --echo ############################################
19 let $row_format = COMPACT;
20 --source suite/innodb/include/instant_add_column_basic.inc
21
22 --echo #
23 --echo # Test cases which don't care about row format start here
24 --echo #
25
26 --echo #
27 --echo # Bug #28040201 ADDING A NEW COLUMN TO A TABLE HAVING A GENRATED COLUMN
28 --echo # CRASHES THE SERVER
29 --echo #
30
31 CREATE TABLE t1 (a TEXT GENERATED ALWAYS AS (lpad(1, 1, 1)) VIRTUAL);
32
33 ALTER TABLE t1 ADD COLUMN (b INT);
34
35 SHOW CREATE TABLE t1;
36
37 DROP TABLE t1;
38
39
40 --echo #
41 --echo # BUG#27784462 - DD::COLUMN::SE_PRIVATE_DATA[TABLE_ID] IS NOT MAINTAINED PROPERLY
42 --echo #
43
44 CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, c INT NOT NULL DEFAULT 5, e INT GENERATED ALWAYS AS ((c + 2)) VIRTUAL) PARTITION BY RANGE (a) (PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20), PARTITION p3 VALUES LESS THAN (30));
45
46 ALTER TABLE t1 REORGANIZE PARTITION p1 INTO (PARTITION p1_a VALUES LESS THAN (5), PARTITION p1_b VALUES LESS THAN (10));
47
48 ALTER TABLE t1 ADD COLUMN d INT GENERATED ALWAYS AS ((b * 2)) VIRTUAL, ALGORITHM = INSTANT;
49
50 DROP TABLE t1;
51
52
53 --echo #
54 --echo # Check adding a stored columns(along with virtual columns)
55 --echo # after the last stored column of a table with functional index
56 --echo # should be fine
57 --echo #
58
59 CREATE TABLE t1 (col1 INT, col2 INT AS (col1 + col1), INDEX (col2));
60
61 INSERT INTO t1(col1) VALUES(10), (20), (30), (40);
62
63 ALTER TABLE t1 ADD COLUMN new_col INT AFTER col1, ALGORITHM=INSTANT;
64
65 --sorted_result
66 SELECT * FROM t1;
67
68 ALTER TABLE t1 ADD COLUMN new_col_1 INT AFTER new_col, ADD COLUMN new_col_2 INT GENERATED ALWAYS AS (new_col * 3), ALGORITHM=INSTANT;
69
70 INSERT INTO t1(col1, new_col, new_col_1) VALUES(100, 200, 300);
71 --sorted_result
72 SELECT * FROM t1;
73
74 SHOW CREATE TABLE t1;
75
76 CHECK TABLE t1;
77
78 DROP TABLE t1;
79
80
81 --echo # Bug #33673961: data is incorrect after instant add column
82
83 # VALUE FOR CHECKSUM USING ALGORITHM=INSTANT SHOULD BE CONSISTENT WITH ALGORITHM=COPY FOR IDENTICAL TABLES
84
85 --let $use_copy=0
86 while($use_copy<=1){
87 CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, a char(10));
88 INSERT INTO t1 VALUES(1, 'aa');
89 INSERT INTO t1 VALUES(2, 'bb');
90 if ($use_copy == 0) {
91 ALTER TABLE t1 ADD b VARCHAR(20), ALGORITHM=INSTANT;
92 }
93 if ($use_copy == 1) {
94 ALTER TABLE t1 ADD b VARCHAR(20), ALGORITHM=COPY;
95 }
96 INSERT INTO t1 VALUES(3, 'cc', 'dd');
97 let $cs = `CHECKSUM TABLE t1`;
98 if ($use_copy == 1) {
99 if ($cs == $prev_cs) {
100 echo CHECKSUM is same;
101 }
102 }
103 DROP TABLE t1;
104
105 --let $prev_cs=$cs
106 --inc $use_copy
107 }