Debian Codegraph @

mysql-test/suite/innodb/t/alter_algorithm.inc

raw
1 let $algorithm = $alter_algorithm;
2 let $error_code = 0;
3
4 --echo #
5 --echo # sql_mode: $sql_mode alter_algorithm: $alter_algorithm
6 --echo #
7
8 if ($algorithm == "NOCOPY") {
9 let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED, ER_ALTER_OPERATION_NOT_SUPPORTED_REASON;
10 }
11
12 if ($algorithm == "INSTANT") {
13 let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED, ER_ALTER_OPERATION_NOT_SUPPORTED_REASON;
14 }
15
16 CREATE TABLE t1(f1 INT NOT NULL,
17 f2 INT NOT NULL,
18 f3 INT NULL,
19 f4 INT as (f2) STORED,
20 f5 INT as (f3) STORED,
21 PRIMARY KEY(f1))ROW_FORMAT=COMPRESSED, ENGINE=INNODB;
22 INSERT INTO t1(f1, f2, f3) VALUES(1, 1, 1);
23
24 CREATE TABLE t2(f1 INT PRIMARY KEY, f2 INT NOT NULL,
25 f3 INT AS (f2 * f2) VIRTUAL,
26 f4 INT NOT NULL UNIQUE,
27 f5 INT NOT NULL,
28 INDEX idx(f2))ENGINE=INNODB;
29 INSERT INTO t2(f1, f2, f4, f5) VALUES(1, 2, 3, 4);
30
31 --enable_info
32 --echo # All the following cases needs table rebuild
33
34 --echo # Add and Drop primary key
35 --error $error_code
36 --eval ALTER TABLE t1 ADD COLUMN col1 INT NOT NULL,DROP PRIMARY KEY,ADD PRIMARY KEY(col1), ALGORITHM=$alter_algorithm
37
38 --echo # Make existing column NULLABLE
39 --error $error_code
40 --eval ALTER TABLE t1 MODIFY f2 INT, ALGORITHM=$alter_algorithm
41
42 --echo # Drop Stored Column
43 --error $error_code
44 --eval ALTER TABLE t1 DROP COLUMN f5, ALGORITHM=$alter_algorithm
45
46 --echo # Add base non-generated column as a last column in the compressed table
47 --error $error_code
48 --eval ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL, ALGORITHM=$alter_algorithm
49
50 --echo # Add base non-generated column but not in the last position
51 --error $error_code
52 --eval ALTER TABLE t1 ADD COLUMN f7 INT NOT NULL after f3, ALGORITHM=$alter_algorithm
53
54 --echo # Force the table to rebuild
55 --error $error_code
56 --eval ALTER TABLE t1 FORCE, ALGORITHM=$alter_algorithm
57
58 --echo # Row format changes
59 --error $error_code
60 --eval ALTER TABLE t1 ROW_FORMAT=COMPRESSED, ALGORITHM=$alter_algorithm
61
62 --echo # Engine table
63 --error $error_code
64 --eval ALTER TABLE t1 ENGINE=INNODB, ALGORITHM=$alter_algorithm
65
66 ALTER TABLE t1 FORCE, ALGORITHM=DEFAULT;
67
68 --error $error_code
69 eval ALTER TABLE t2 page_compressed=1, ALGORITHM=$alter_algorithm;
70
71 DROP TABLE t1;
72 DROP TABLE t2;
73 --disable_info
74
75 if ($algorithm == "NOCOPY") {
76 let $error_code = 0;
77 }
78
79 if ($algorithm == "INSTANT") {
80 let $error_code = ER_ALTER_OPERATION_NOT_SUPPORTED_REASON;
81 }
82
83 CREATE TABLE t1(f1 INT PRIMARY KEY, f2 INT NOT NULL,
84 f3 INT AS (f2 * f2) VIRTUAL,
85 f4 INT NOT NULL UNIQUE,
86 f5 INT NOT NULL,
87 INDEX idx(f2)) PAGE_COMPRESSED=1, ENGINE=INNODB;
88
89 CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL,
90 INDEX(f1),
91 FOREIGN KEY fidx(f1) REFERENCES t1(f1))ENGINE=INNODB;
92
93 INSERT INTO t1(f1, f2, f4, f5) VALUES(1, 2, 3, 4);
94
95 --enable_info
96 --error $error_code
97 --eval ALTER TABLE t1 ADD INDEX idx1(f4), page_compression_level=7, ALGORITHM=$alter_algorithm
98
99 --error $error_code
100 --eval ALTER TABLE t1 DROP INDEX idx, page_compression_level=5, ALGORITHM=$alter_algorithm
101
102 --error $error_code
103 --eval ALTER TABLE t1 ADD UNIQUE INDEX u1(f2), ALGORITHM=$alter_algorithm
104
105 --error $error_code
106 --eval ALTER TABLE t1 DROP INDEX f4, page_compression_level=9, ALGORITHM=$alter_algorithm
107
108 SET foreign_key_checks = 0;
109 --error $error_code
110 --eval ALTER TABLE t1 ADD FOREIGN KEY(f5) REFERENCES t2(f1), ALGORITHM=$alter_algorithm
111
112 DROP TABLE t2, t1;
113 --disable_info
114
115 CREATE TABLE t1(f1 INT NOT NULL,
116 f2 INT NOT NULL,
117 f3 INT AS (f2 * f2) VIRTUAL,
118 INDEX idx (f2))engine=innodb;
119
120 CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL,
121 f3 VARCHAR(10),
122 INDEX(f1))ENGINE=INNODB;
123
124 INSERT INTO t1(f1, f2) VALUES(1, 1);
125
126 --enable_info
127 --echo # Add column at the end of the table
128 --eval ALTER TABLE t1 ADD COLUMN f4 char(100) default 'BIG WALL', ALGORITHM=$alter_algorithm
129
130 --echo # Change virtual column expression
131 --eval ALTER TABLE t1 CHANGE f3 f3 INT AS (f2 * f2) VIRTUAL, ALGORITHM=$alter_algorithm
132
133 --echo # Add virtual column
134 --eval ALTER TABLE t1 ADD COLUMN f5 INT AS (f2) VIRTUAL, ALGORITHM=$alter_algorithm
135
136 --echo # Rename Column
137 --eval ALTER TABLE t1 CHANGE f3 vcol INT AS (f2) VIRTUAL, ALGORITHM=$alter_algorithm
138
139 --echo # Rename table
140 --eval ALTER TABLE t1 RENAME t3, ALGORITHM=$alter_algorithm
141
142 --echo # Drop Virtual Column
143 --eval ALTER TABLE t3 DROP COLUMN vcol, ALGORITHM=$alter_algorithm
144
145 --echo # Column length varies
146 --eval ALTER TABLE t2 CHANGE f3 f3 VARCHAR(20), ALGORITHM=$alter_algorithm
147
148 SET foreign_key_checks = 0;
149 --eval ALTER TABLE t3 ADD FOREIGN KEY fidx(f2) REFERENCES t2(f1), ALGORITHM=$alter_algorithm
150
151 SET foreign_key_checks = 1;
152 --eval ALTER TABLE t3 DROP FOREIGN KEY fidx, ALGORITHM=$alter_algorithm
153
154 DROP TABLE t3, t2;
155 --disable_info