Debian Codegraph @

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

raw
1 # INSTANT ADD/DROP Column
2 # Test some Limitations/error conditions
3
4 # InnoDB does not support compressed tables for page sizes=32k,64k
5 --source include/have_innodb_max_16k.inc
6
7 --echo #
8 --echo # Scenario 1:
9 --echo # Create a small table with row_format compressed and adding/dropping instant column should fail
10 --echo #
11 CREATE TABLE t1 (a INT, b INT) ROW_FORMAT=COMPRESSED;
12 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
13
14 # Try to add instant column
15 --error ER_ALTER_OPERATION_NOT_SUPPORTED
16 ALTER TABLE t1 ADD COLUMN c1 INT DEFAULT 5, ALGORITHM=INSTANT;
17
18 --error ER_ALTER_OPERATION_NOT_SUPPORTED
19 ALTER TABLE t1 DROP COLUMN a, ALGORITHM=INSTANT;
20
21 DROP TABLE t1;
22
23 --echo #
24 --echo # Scenario 2:
25 --echo # Create a small table and adding spatial types with not null should fail
26 --echo #
27
28 CREATE TABLE t1 (a INT, b INT);
29
30 INSERT INTO t1 VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
31
32 --error ER_ALTER_OPERATION_NOT_SUPPORTED
33 ALTER TABLE t1 ADD COLUMN c1 POINT NOT NULL, ALGORITHM = INSTANT;
34
35 --error ER_ALTER_OPERATION_NOT_SUPPORTED
36 ALTER TABLE t1 ADD COLUMN d1 LINESTRING NOT NULL, ALGORITHM = INSTANT;
37
38 --error ER_ALTER_OPERATION_NOT_SUPPORTED
39 ALTER TABLE t1 ADD COLUMN e1 POLYGON NOT NULL, ALGORITHM = INSTANT;
40
41 --error ER_ALTER_OPERATION_NOT_SUPPORTED
42 ALTER TABLE t1 ADD COLUMN f1 MULTIPOINT NOT NULL, ALGORITHM = INSTANT;
43
44 --error ER_ALTER_OPERATION_NOT_SUPPORTED
45 ALTER TABLE t1 ADD COLUMN g1 MULTILINESTRING NOT NULL, ALGORITHM = INSTANT;
46
47 --error ER_ALTER_OPERATION_NOT_SUPPORTED
48 ALTER TABLE t1 ADD COLUMN h1 MULTIPOLYGON NOT NULL, ALGORITHM = INSTANT;
49
50 --error ER_ALTER_OPERATION_NOT_SUPPORTED
51 ALTER TABLE t1 ADD COLUMN i1 GEOMETRYCOLLECTION NOT NULL, ALGORITHM = INSTANT;
52
53 --error ER_ALTER_OPERATION_NOT_SUPPORTED
54 ALTER TABLE t1 ADD COLUMN j1 GEOMETRY NOT NULL, ALGORITHM = INSTANT;
55
56 DROP TABLE t1;
57
58
59 --echo #
60 --echo # Scenario 3:
61 --echo # Create a temporary table and adding/dropping instant column should fail
62 --echo #
63 CREATE TEMPORARY TABLE tmp1(a INT, b INT);
64
65 --error ER_ALTER_OPERATION_NOT_SUPPORTED
66 ALTER TABLE tmp1 ADD COLUMN c int, ALGORITHM=INSTANT;
67
68 --error ER_ALTER_OPERATION_NOT_SUPPORTED
69 ALTER TABLE tmp1 DROP COLUMN b, ALGORITHM=INSTANT;
70
71 DROP TABLE tmp1;
72
73 --echo #
74 --echo # Scenario 4:
75 --echo # Trying to add/drop instant columns for data-dictionary tables should fail
76 --echo #
77 --error ER_ALTER_OPERATION_NOT_SUPPORTED
78 ALTER TABLE mysql.plugin ADD COLUMN c1 INT, ALGORITHM=INSTANT;
79 --error ER_ALTER_OPERATION_NOT_SUPPORTED
80 ALTER TABLE mysql.plugin DROP COLUMN dl, ALGORITHM=INSTANT;
81
82 --error ER_ALTER_OPERATION_NOT_SUPPORTED
83 ALTER TABLE mysql.user ADD COLUMN c1 INT, ALGORITHM=INSTANT;
84 --error ER_ALTER_OPERATION_NOT_SUPPORTED
85 ALTER TABLE mysql.user DROP COLUMN Select_priv, ALGORITHM=INSTANT;
86
87 --echo #
88 --echo # Scenario 5:
89 --echo # Trying to add/drop instant column to a table having FTS index
90 --echo #
91 CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a,b));
92 INSERT INTO t1 VALUES('test1', 'test2');
93 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
94 ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INSTANT;
95
96 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
97 ALTER TABLE t1 DROP COLUMN b, ALGORITHM=INSTANT;
98
99 DROP TABLE t1;
100
101 --echo #
102 --echo # Scenario 6:
103 --echo # INSTANT ADD/DROP column with key
104 --echo #
105 CREATE TABLE t1 (c1 INT, c2 INT);
106 --error ER_ALTER_OPERATION_NOT_SUPPORTED
107 ALTER TABLE t1 ADD COLUMN c3 INT KEY, ALGORITHM=INSTANT;
108 DROP TABLE t1;
109
110 CREATE TABLE t1 (c1 INT KEY, c2 INT);
111 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
112 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT;
113 --error ER_ALTER_OPERATION_NOT_SUPPORTED
114 ALTER TABLE t1 DROP COLUMN c1, ADD PRIMARY KEY (c2), ALGORITHM=INSTANT;
115 DROP TABLE t1;
116
117 CREATE TABLE t1 (c1 INT KEY, c2 INT);
118 --error ER_ALTER_OPERATION_NOT_SUPPORTED
119 ALTER TABLE t1 ADD COLUMN c3 INT UNIQUE KEY, ALGORITHM=INSTANT;
120 --error ER_ALTER_OPERATION_NOT_SUPPORTED
121 ALTER TABLE t1 ADD COLUMN c3 INT, ADD INDEX idx (c3), ALGORITHM=INSTANT;
122 DROP TABLE t1;
123
124 CREATE TABLE t1 (c1 INT KEY, c2 INT, INDEX idx(c2));
125 --error ER_ALTER_OPERATION_NOT_SUPPORTED
126 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
127 DROP TABLE t1;
128
129 --echo #
130 --echo # Scenario 7:
131 --echo # Maximum row versions (MAX_ROW_VERSION is 64)
132 --echo #
133 CREATE TABLE t1 (c1 int);
134 --echo # INSTANT ADD COLUMNS to table to make it reach the maximum versions
135 --disable_query_log
136 let $counter=2;
137 while ($counter<=65)
138 {
139 eval ALTER TABLE t1 ADD COLUMN c$counter INT, ALGORITHM=INSTANT;
140 inc $counter;
141 }
142 --enable_query_log
143
144 --echo # Table TOTAL_ROW_VERSIONS should be 64 (MAX)
145 --let $table_name=t1
146 --source suite/innodb/include/print_instant_metadata.inc
147
148 --echo # An INSTANT ADD COLUMN should result in error now.
149 --error ER_INNODB_MAX_ROW_VERSION
150 ALTER TABLE t1 ADD COLUMN c66 INT, ALGORITHM=INSTANT;
151
152 --echo # ADD COLUMN shall fall back to INPLACE if ALGORITHM not given explicitly
153 ALTER TABLE t1 ADD COLUMN c66 INT;
154
155 --echo # Table would have been rebuilt
156 --let $table_name=t1
157 --source suite/innodb/include/print_instant_metadata.inc
158 DROP TABLE t1;
159
160 CREATE TABLE t1 (c1 int);
161 INSERT INTO t1(c1) VALUES(1);
162 SELECT * FROM t1;
163
164 --echo # INSTANT ADD COLUMNS to table to make it reach the maximum versions
165 --disable_query_log
166 let $counter=2;
167 while ($counter<=65)
168 {
169 eval ALTER TABLE t1 ADD COLUMN c$counter INT, ALGORITHM=INSTANT;
170 inc $counter;
171 }
172 --enable_query_log
173
174 --echo # An INSTANT DROP COLUMN should result in error now.
175 --error ER_INNODB_MAX_ROW_VERSION
176 ALTER TABLE t1 DROP COLUMN c65, ALGORITHM=INSTANT;
177 INSERT INTO t1(c1) VALUES(2);
178 SELECT * FROM t1;
179
180 --echo # DROP COLUMN shall fall back to INPLACE if ALGORITHM not given explicitly
181 ALTER TABLE t1 DROP COLUMN c65;
182 INSERT INTO t1(c1) VALUES(3);
183 SELECT * FROM t1;
184
185 --echo # Table would have been rebuilt
186 --let $table_name=t1
187 --source suite/innodb/include/print_instant_metadata.inc
188
189 DROP TABLE t1;