Debian Codegraph @

mysql-test/suite/secondary_engine/t/define.test

raw
1 # Tests for the SECONDARY_ENGINE table option.
2 # Most DDLs are allowed on tables with SECONDARY_ENGINE when
3 # the corresponding SECONDARY_ENGINE plugin is not installed.
4 # Following tests are with SECONDARY_ENGINE plugin that doesn't exist.
5
6 --echo #
7 --echo # Attempt to load table into an undefined secondary engine.
8 --echo #
9 CREATE TABLE t1 (a INT);
10 --error ER_SECONDARY_ENGINE
11 ALTER TABLE t1 SECONDARY_LOAD;
12 DROP TABLE t1;
13
14 --echo #
15 --echo # Attempt to load table into an unknown storage engine.
16 --echo #
17 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
18 # expect warning
19 ALTER TABLE t1 SECONDARY_LOAD;
20 DROP TABLE t1;
21
22 --echo #
23 --echo # Attempt to load table into an unsupported secondary storage engine.
24 --echo #
25 CREATE TABLE t1 (a INT) SECONDARY_ENGINE InnoDB;
26 --error ER_SECONDARY_ENGINE
27 ALTER TABLE t1 SECONDARY_LOAD;
28 DROP TABLE t1;
29
30 --echo #
31 --echo # Create a table with a secondary engine defined.
32 --echo #
33 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
34 SHOW CREATE TABLE t1;
35 DROP TABLE t1;
36
37 --echo #
38 --echo # Attempt to redefine the secondary engine.
39 --echo #
40 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
41 --error ER_SECONDARY_ENGINE
42 ALTER TABLE t1 SECONDARY_ENGINE ANOTHER_NONEXISTENT;
43 DROP TABLE t1;
44
45 --echo #
46 --echo # Define and undefine secondary engine (without loading in-between).
47 --echo #
48 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
49 ALTER TABLE t1 SECONDARY_ENGINE NULL;
50 SHOW CREATE TABLE t1;
51 DROP TABLE t1;
52
53 --echo #
54 --echo # Attempt to create a temporary table with a secondary engine defined.
55 --echo #
56 --error ER_SECONDARY_ENGINE
57 CREATE TEMPORARY TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
58 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
59 --error ER_SECONDARY_ENGINE
60 CREATE TEMPORARY TABLE t2 LIKE t1;
61 DROP TABLE t1;
62
63 --echo #
64 --echo # Attempt to create a table with a primary engine that does not support
65 --echo # loading tables into a secondary engine.
66 --error ER_CHECK_NOT_IMPLEMENTED
67 CREATE TABLE t1 (a INT) ENGINE MYISAM SECONDARY_ENGINE NONEXISTENT;
68
69 --echo #
70 --echo # Attempt to change primary storage engine to an engine that does not
71 --echo # support loading into a secondary engine.
72 --echo #
73 CREATE TABLE t1 (a INT) ENGINE InnoDB SECONDARY_ENGINE NONEXISTENT;
74 --error ER_SECONDARY_ENGINE
75 ALTER TABLE t1 ENGINE MYISAM;
76 SHOW CREATE TABLE t1;
77 DROP TABLE t1;
78
79 --echo #
80 --echo # Attempt to use SECONDARY_LOAD when creating table.
81 --echo #
82 --error ER_PARSE_ERROR
83 CREATE TABLE t1 (a INT) SECONDARY_LOAD;
84 --error ER_PARSE_ERROR
85 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT, SECONDARY_LOAD;
86
87 --echo #
88 --echo # Alter a table after a secondary engine has been defined.
89 --echo #
90 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
91 ALTER TABLE t1 COMMENT="Test1";
92 ALTER TABLE t1 COMMENT="Test2", ALGORITHM=INPLACE;
93 ALTER TABLE t1 COMMENT="Test3", ALGORITHM=COPY;
94 ALTER TABLE t1 ADD COLUMN (b INT);
95 ALTER TABLE t1 ADD COLUMN (c INT), ALGORITHM=INPLACE;
96 ALTER TABLE t1 ADD COLUMN (d INT), ALGORITHM=COPY;
97 SHOW CREATE TABLE t1;
98 DROP TABLE t1;
99
100 --echo #
101 --echo # Truncate a table after a secondary engine has been defined.
102 --echo #
103 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
104 TRUNCATE TABLE t1;
105 SHOW CREATE TABLE t1;
106 DROP TABLE t1;
107
108 --echo #
109 --echo # Attempt to alter table after its secondary engine has been removed.
110 --echo #
111 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
112 SHOW CREATE TABLE t1;
113 ALTER TABLE t1 SECONDARY_ENGINE NULL;
114 ALTER TABLE t1 COMMENT="Test";
115 ALTER TABLE t1 ADD COLUMN (b INT);
116 ALTER TABLE t1 SECONDARY_ENGINE NONEXISTENT;
117 SHOW CREATE TABLE t1;
118 DROP TABLE t1;
119
120 --echo #
121 --echo # Attempt to truncate table after its secondary engine is removed
122 --echo #
123 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
124 SHOW CREATE TABLE t1;
125 ALTER TABLE t1 SECONDARY_ENGINE NULL;
126 TRUNCATE TABLE t1;
127 ALTER TABLE t1 SECONDARY_ENGINE NONEXISTENT;
128 SHOW CREATE TABLE t1;
129 DROP TABLE t1;
130
131 --echo #
132 --echo # Rename a table with a secondary engine defined.
133 --echo #
134 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
135 RENAME TABLE t1 TO t2;
136 DROP TABLE t2;
137
138 --echo #
139 --echo # Attempt to use SECONDARY_LOAD in conjunction with another DDL.
140 --echo #
141 CREATE TABLE t1 (i INT);
142 --error ER_PARSE_ERROR
143 ALTER TABLE t1 SECONDARY_LOAD, ADD KEY idx(i);
144 ALTER TABLE t1 SECONDARY_ENGINE NONEXISTENT;
145 --error ER_PARSE_ERROR
146 ALTER TABLE t1 SECONDARY_LOAD, ADD KEY idx(i);
147 DROP TABLE t1;
148
149 --echo #
150 --echo # Set NOT SECONDARY on columns when creating table.
151 --echo #
152 CREATE TABLE t1 (a INT NOT SECONDARY, b INT NOT SECONDARY);
153 SHOW CREATE TABLE t1;
154 ALTER TABLE t1 MODIFY a INT;
155 SHOW CREATE TABLE t1;
156 DROP TABLE t1;
157
158 --echo #
159 --echo # Set NOT SECONDARY after create table.
160 --echo #
161 CREATE TABLE t1 (a INT, b INT);
162 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
163 ALTER TABLE t1 MODIFY b INT NOT SECONDARY;
164 SHOW CREATE TABLE t1;
165 DROP TABLE t1;
166
167 --echo #
168 --echo # It should be possible to set or remove NOT SECONDARY on a column
169 --echo # if the table is defined with a secondary engine.
170 --echo #
171 CREATE TABLE t1 (a INT NOT SECONDARY, b INT NOT SECONDARY);
172 ALTER TABLE t1 MODIFY a INT;
173 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
174 SHOW CREATE TABLE t1;
175 ALTER TABLE t1 MODIFY b INT;
176 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
177 SHOW CREATE TABLE t1;
178 ALTER TABLE t1 SECONDARY_ENGINE NULL;
179 ALTER TABLE t1 MODIFY b INT;
180 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
181 SHOW CREATE TABLE t1;
182 DROP TABLE t1;
183
184 --echo #
185 --echo # Parts of the primary key can be excluded from the secondary engine.
186 --echo #
187 CREATE TABLE t1 (a INT, b INT NOT SECONDARY, PRIMARY KEY(a, b));
188 SHOW CREATE TABLE t1;
189 DROP TABLE t1;
190
191 --echo #
192 --echo # Set and remove NOT SECONDARY in conjunction with other column options.
193 --echo #
194 CREATE TABLE t1 (a INT, b INT);
195 ALTER TABLE t1 MODIFY a INT NOT NULL NOT SECONDARY;
196 SHOW CREATE TABLE t1;
197 ALTER TABLE t1 MODIFY a INT NOT NULL;
198 SHOW CREATE TABLE t1;
199 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
200 SHOW CREATE TABLE t1;
201 ALTER TABLE t1 MODIFY a INT NOT SECONDARY, SECONDARY_ENGINE MOCK;
202 SHOW CREATE TABLE t1;
203 DROP TABLE t1;
204
205 --echo #
206 --echo # NOT SECONDARY should be preserved with CREATE TABLE as SELECT FROM.
207 --echo #
208 CREATE TABLE t1 (a INT NOT SECONDARY, b INT, c INT NOT SECONDARY) SECONDARY_ENGINE MOCK;
209 CREATE TABLE t2 AS SELECT * FROM t1;
210 SHOW CREATE TABLE t1;
211 SHOW CREATE TABLE t2;
212 DROP TABLE t1, t2;
213
214 --echo #
215 --echo # CREATE TABLE LIKE with NOT SECONDARY columns
216 --echo #
217 CREATE TABLE t1 (a INT NOT SECONDARY, b INT, c INT NOT SECONDARY) SECONDARY_ENGINE MOCK;
218 CREATE TABLE t2 LIKE t1;
219 SHOW CREATE TABLE t1;
220 SHOW CREATE TABLE t2;
221 DROP TABLE t1, t2;
222
223 --echo #
224 --echo # CREATE TABLE AS SELECT FROM with NOT SECONDARY columns
225 --echo #
226 CREATE TABLE t1 (a INT NOT SECONDARY, b INT, c INT NOT SECONDARY) SECONDARY_ENGINE MOCK;
227 CREATE TABLE t2 AS SELECT * FROM t1;
228 SHOW CREATE TABLE t1;
229 SHOW CREATE TABLE t2;
230 DROP TABLE t1, t2;