Debian Codegraph @

mysql-test/suite/secondary_engine/r/define.result

raw
1 #
2 # Attempt to load table into an undefined secondary engine.
3 #
4 CREATE TABLE t1 (a INT);
5 ALTER TABLE t1 SECONDARY_LOAD;
6 ERROR HY000: Secondary engine operation failed. No secondary engine defined.
7 DROP TABLE t1;
8 #
9 # Attempt to load table into an unknown storage engine.
10 #
11 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
12 ALTER TABLE t1 SECONDARY_LOAD;
13 Warnings:
14 Warning 1286 Unknown storage engine 'NONEXISTENT'
15 DROP TABLE t1;
16 #
17 # Attempt to load table into an unsupported secondary storage engine.
18 #
19 CREATE TABLE t1 (a INT) SECONDARY_ENGINE InnoDB;
20 ALTER TABLE t1 SECONDARY_LOAD;
21 ERROR HY000: Secondary engine operation failed. Unsupported secondary storage engine.
22 DROP TABLE t1;
23 #
24 # Create a table with a secondary engine defined.
25 #
26 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
27 SHOW CREATE TABLE t1;
28 Table Create Table
29 t1 CREATE TABLE `t1` (
30 `a` int DEFAULT NULL
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=NONEXISTENT
32 DROP TABLE t1;
33 #
34 # Attempt to redefine the secondary engine.
35 #
36 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
37 ALTER TABLE t1 SECONDARY_ENGINE ANOTHER_NONEXISTENT;
38 ERROR HY000: Secondary engine operation failed. Table already has a secondary engine defined.
39 DROP TABLE t1;
40 #
41 # Define and undefine secondary engine (without loading in-between).
42 #
43 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
44 ALTER TABLE t1 SECONDARY_ENGINE NULL;
45 SHOW CREATE TABLE t1;
46 Table Create Table
47 t1 CREATE TABLE `t1` (
48 `a` int DEFAULT NULL
49 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
50 DROP TABLE t1;
51 #
52 # Attempt to create a temporary table with a secondary engine defined.
53 #
54 CREATE TEMPORARY TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
55 ERROR HY000: Secondary engine operation failed. Temporary tables not supported.
56 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
57 CREATE TEMPORARY TABLE t2 LIKE t1;
58 ERROR HY000: Secondary engine operation failed. Temporary tables not supported.
59 DROP TABLE t1;
60 #
61 # Attempt to create a table with a primary engine that does not support
62 # loading tables into a secondary engine.
63 CREATE TABLE t1 (a INT) ENGINE MYISAM SECONDARY_ENGINE NONEXISTENT;
64 ERROR 42000: The storage engine for the table doesn't support SECONDARY_ENGINE
65 #
66 # Attempt to change primary storage engine to an engine that does not
67 # support loading into a secondary engine.
68 #
69 CREATE TABLE t1 (a INT) ENGINE InnoDB SECONDARY_ENGINE NONEXISTENT;
70 ALTER TABLE t1 ENGINE MYISAM;
71 ERROR HY000: Secondary engine operation failed. Cannot change the primary engine of a table with a defined secondary engine.
72 SHOW CREATE TABLE t1;
73 Table Create Table
74 t1 CREATE TABLE `t1` (
75 `a` int DEFAULT NULL
76 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=NONEXISTENT
77 DROP TABLE t1;
78 #
79 # Attempt to use SECONDARY_LOAD when creating table.
80 #
81 CREATE TABLE t1 (a INT) SECONDARY_LOAD;
82 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SECONDARY_LOAD' at line 1
83 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT, SECONDARY_LOAD;
84 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SECONDARY_LOAD' at line 1
85 #
86 # Alter a table after a secondary engine has been defined.
87 #
88 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
89 ALTER TABLE t1 COMMENT="Test1";
90 ALTER TABLE t1 COMMENT="Test2", ALGORITHM=INPLACE;
91 ALTER TABLE t1 COMMENT="Test3", ALGORITHM=COPY;
92 ALTER TABLE t1 ADD COLUMN (b INT);
93 ALTER TABLE t1 ADD COLUMN (c INT), ALGORITHM=INPLACE;
94 ALTER TABLE t1 ADD COLUMN (d INT), ALGORITHM=COPY;
95 SHOW CREATE TABLE t1;
96 Table Create Table
97 t1 CREATE TABLE `t1` (
98 `a` int DEFAULT NULL,
99 `b` int DEFAULT NULL,
100 `c` int DEFAULT NULL,
101 `d` int DEFAULT NULL
102 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Test3' SECONDARY_ENGINE=NONEXISTENT
103 DROP TABLE t1;
104 #
105 # Truncate a table after a secondary engine has been defined.
106 #
107 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
108 TRUNCATE TABLE t1;
109 SHOW CREATE TABLE t1;
110 Table Create Table
111 t1 CREATE TABLE `t1` (
112 `a` int DEFAULT NULL
113 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=NONEXISTENT
114 DROP TABLE t1;
115 #
116 # Attempt to alter table after its secondary engine has been removed.
117 #
118 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
119 SHOW CREATE TABLE t1;
120 Table Create Table
121 t1 CREATE TABLE `t1` (
122 `a` int DEFAULT NULL
123 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=NONEXISTENT
124 ALTER TABLE t1 SECONDARY_ENGINE NULL;
125 ALTER TABLE t1 COMMENT="Test";
126 ALTER TABLE t1 ADD COLUMN (b INT);
127 ALTER TABLE t1 SECONDARY_ENGINE NONEXISTENT;
128 SHOW CREATE TABLE t1;
129 Table Create Table
130 t1 CREATE TABLE `t1` (
131 `a` int DEFAULT NULL,
132 `b` int DEFAULT NULL
133 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Test' SECONDARY_ENGINE=NONEXISTENT
134 DROP TABLE t1;
135 #
136 # Attempt to truncate table after its secondary engine is removed
137 #
138 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
139 SHOW CREATE TABLE t1;
140 Table Create Table
141 t1 CREATE TABLE `t1` (
142 `a` int DEFAULT NULL
143 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=NONEXISTENT
144 ALTER TABLE t1 SECONDARY_ENGINE NULL;
145 TRUNCATE TABLE t1;
146 ALTER TABLE t1 SECONDARY_ENGINE NONEXISTENT;
147 SHOW CREATE TABLE t1;
148 Table Create Table
149 t1 CREATE TABLE `t1` (
150 `a` int DEFAULT NULL
151 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=NONEXISTENT
152 DROP TABLE t1;
153 #
154 # Rename a table with a secondary engine defined.
155 #
156 CREATE TABLE t1 (a INT) SECONDARY_ENGINE NONEXISTENT;
157 RENAME TABLE t1 TO t2;
158 DROP TABLE t2;
159 #
160 # Attempt to use SECONDARY_LOAD in conjunction with another DDL.
161 #
162 CREATE TABLE t1 (i INT);
163 ALTER TABLE t1 SECONDARY_LOAD, ADD KEY idx(i);
164 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', ADD KEY idx(i)' at line 1
165 ALTER TABLE t1 SECONDARY_ENGINE NONEXISTENT;
166 ALTER TABLE t1 SECONDARY_LOAD, ADD KEY idx(i);
167 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', ADD KEY idx(i)' at line 1
168 DROP TABLE t1;
169 #
170 # Set NOT SECONDARY on columns when creating table.
171 #
172 CREATE TABLE t1 (a INT NOT SECONDARY, b INT NOT SECONDARY);
173 SHOW CREATE TABLE t1;
174 Table Create Table
175 t1 CREATE TABLE `t1` (
176 `a` int NOT SECONDARY DEFAULT NULL,
177 `b` int NOT SECONDARY DEFAULT NULL
178 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
179 ALTER TABLE t1 MODIFY a INT;
180 SHOW CREATE TABLE t1;
181 Table Create Table
182 t1 CREATE TABLE `t1` (
183 `a` int DEFAULT NULL,
184 `b` int NOT SECONDARY DEFAULT NULL
185 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
186 DROP TABLE t1;
187 #
188 # Set NOT SECONDARY after create table.
189 #
190 CREATE TABLE t1 (a INT, b INT);
191 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
192 ALTER TABLE t1 MODIFY b INT NOT SECONDARY;
193 SHOW CREATE TABLE t1;
194 Table Create Table
195 t1 CREATE TABLE `t1` (
196 `a` int NOT SECONDARY DEFAULT NULL,
197 `b` int NOT SECONDARY DEFAULT NULL
198 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
199 DROP TABLE t1;
200 #
201 # It should be possible to set or remove NOT SECONDARY on a column
202 # if the table is defined with a secondary engine.
203 #
204 CREATE TABLE t1 (a INT NOT SECONDARY, b INT NOT SECONDARY);
205 ALTER TABLE t1 MODIFY a INT;
206 ALTER TABLE t1 SECONDARY_ENGINE MOCK;
207 SHOW CREATE TABLE t1;
208 Table Create Table
209 t1 CREATE TABLE `t1` (
210 `a` int DEFAULT NULL,
211 `b` int NOT SECONDARY DEFAULT NULL
212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=MOCK
213 ALTER TABLE t1 MODIFY b INT;
214 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
215 SHOW CREATE TABLE t1;
216 Table Create Table
217 t1 CREATE TABLE `t1` (
218 `a` int NOT SECONDARY DEFAULT NULL,
219 `b` int DEFAULT NULL
220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=MOCK
221 ALTER TABLE t1 SECONDARY_ENGINE NULL;
222 ALTER TABLE t1 MODIFY b INT;
223 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
224 SHOW CREATE TABLE t1;
225 Table Create Table
226 t1 CREATE TABLE `t1` (
227 `a` int NOT SECONDARY DEFAULT NULL,
228 `b` int DEFAULT NULL
229 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
230 DROP TABLE t1;
231 #
232 # Parts of the primary key can be excluded from the secondary engine.
233 #
234 CREATE TABLE t1 (a INT, b INT NOT SECONDARY, PRIMARY KEY(a, b));
235 SHOW CREATE TABLE t1;
236 Table Create Table
237 t1 CREATE TABLE `t1` (
238 `a` int NOT NULL,
239 `b` int NOT NULL NOT SECONDARY,
240 PRIMARY KEY (`a`,`b`)
241 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
242 DROP TABLE t1;
243 #
244 # Set and remove NOT SECONDARY in conjunction with other column options.
245 #
246 CREATE TABLE t1 (a INT, b INT);
247 ALTER TABLE t1 MODIFY a INT NOT NULL NOT SECONDARY;
248 SHOW CREATE TABLE t1;
249 Table Create Table
250 t1 CREATE TABLE `t1` (
251 `a` int NOT NULL NOT SECONDARY,
252 `b` int DEFAULT NULL
253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
254 ALTER TABLE t1 MODIFY a INT NOT NULL;
255 SHOW CREATE TABLE t1;
256 Table Create Table
257 t1 CREATE TABLE `t1` (
258 `a` int NOT NULL,
259 `b` int DEFAULT NULL
260 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
261 ALTER TABLE t1 MODIFY a INT NOT SECONDARY;
262 SHOW CREATE TABLE t1;
263 Table Create Table
264 t1 CREATE TABLE `t1` (
265 `a` int NOT SECONDARY DEFAULT NULL,
266 `b` int DEFAULT NULL
267 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
268 ALTER TABLE t1 MODIFY a INT NOT SECONDARY, SECONDARY_ENGINE MOCK;
269 SHOW CREATE TABLE t1;
270 Table Create Table
271 t1 CREATE TABLE `t1` (
272 `a` int NOT SECONDARY DEFAULT NULL,
273 `b` int DEFAULT NULL
274 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=MOCK
275 DROP TABLE t1;
276 #
277 # NOT SECONDARY should be preserved with CREATE TABLE as SELECT FROM.
278 #
279 CREATE TABLE t1 (a INT NOT SECONDARY, b INT, c INT NOT SECONDARY) SECONDARY_ENGINE MOCK;
280 CREATE TABLE t2 AS SELECT * FROM t1;
281 SHOW CREATE TABLE t1;
282 Table Create Table
283 t1 CREATE TABLE `t1` (
284 `a` int NOT SECONDARY DEFAULT NULL,
285 `b` int DEFAULT NULL,
286 `c` int NOT SECONDARY DEFAULT NULL
287 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=MOCK
288 SHOW CREATE TABLE t2;
289 Table Create Table
290 t2 CREATE TABLE `t2` (
291 `a` int NOT SECONDARY DEFAULT NULL,
292 `b` int DEFAULT NULL,
293 `c` int NOT SECONDARY DEFAULT NULL
294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
295 DROP TABLE t1, t2;
296 #
297 # CREATE TABLE LIKE with NOT SECONDARY columns
298 #
299 CREATE TABLE t1 (a INT NOT SECONDARY, b INT, c INT NOT SECONDARY) SECONDARY_ENGINE MOCK;
300 CREATE TABLE t2 LIKE t1;
301 SHOW CREATE TABLE t1;
302 Table Create Table
303 t1 CREATE TABLE `t1` (
304 `a` int NOT SECONDARY DEFAULT NULL,
305 `b` int DEFAULT NULL,
306 `c` int NOT SECONDARY DEFAULT NULL
307 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=MOCK
308 SHOW CREATE TABLE t2;
309 Table Create Table
310 t2 CREATE TABLE `t2` (
311 `a` int NOT SECONDARY DEFAULT NULL,
312 `b` int DEFAULT NULL,
313 `c` int NOT SECONDARY DEFAULT NULL
314 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=MOCK
315 DROP TABLE t1, t2;
316 #
317 # CREATE TABLE AS SELECT FROM with NOT SECONDARY columns
318 #
319 CREATE TABLE t1 (a INT NOT SECONDARY, b INT, c INT NOT SECONDARY) SECONDARY_ENGINE MOCK;
320 CREATE TABLE t2 AS SELECT * FROM t1;
321 SHOW CREATE TABLE t1;
322 Table Create Table
323 t1 CREATE TABLE `t1` (
324 `a` int NOT SECONDARY DEFAULT NULL,
325 `b` int DEFAULT NULL,
326 `c` int NOT SECONDARY DEFAULT NULL
327 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SECONDARY_ENGINE=MOCK
328 SHOW CREATE TABLE t2;
329 Table Create Table
330 t2 CREATE TABLE `t2` (
331 `a` int NOT SECONDARY DEFAULT NULL,
332 `b` int DEFAULT NULL,
333 `c` int NOT SECONDARY DEFAULT NULL
334 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
335 DROP TABLE t1, t2;