Debian Codegraph @

mysql-test/suite/innodb/r/tablespace_encrypt_4.result

raw
1 #########################################################################
2 # START : WITHOUT KEYRING PLUGIN
3 #########################################################################
4
5 #########
6 # SETUP #
7 #########
8 #########################################################################
9 # RESTART 1 : WITH KEYRING PLUGIN
10 #########################################################################
11 #########################################################################
12 # Non Partitioned Table #
13 #########################################################################
14
15 DROP TABLE IF EXISTS t1;
16 # Create an Encrypted and an Unencrypted tablespace
17 CREATE TABLESPACE encrypt_ts add datafile 'encrypt_ts.ibd' ENCRYPTION='Y';
18 CREATE TABLESPACE unencrypt_ts add datafile 'unencrypt_ts.ibd' ENCRYPTION='N';
19 # Create table with "encryption" option in general tablespace
20 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='y';
21 SHOW WARNINGS;
22 Level Code Message
23 DROP TABLE t1;
24 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='n';
25 ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
26 SHOW WARNINGS;
27 Level Code Message
28 Error 3825 Request to create 'unencrypted' table while using an 'encrypted' tablespace.
29 CREATE TABLE t1 (c int) TABLESPACE=innodb_system ENCRYPTION='y';
30 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
31 SHOW WARNINGS;
32 Level Code Message
33 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
34 CREATE TABLE t1 (c int) TABLESPACE=innodb_system ENCRYPTION='n';
35 SHOW WARNINGS;
36 Level Code Message
37 DROP TABLE t1;
38 #-------------------------------------------------------
39 # general [encrypted] => general [unencrypted]
40 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
41 ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=INPLACE;
42 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
43 ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=COPY;
44 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
45 # general [unencrypted] => general [encrypted]
46 DROP TABLE t1;
47 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
48 ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=INPLACE;
49 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
50 # Changing encryption type without explicit ENCRYPTION clause fails.
51 ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=COPY;
52 ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
53 ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=INPLACE;
54 ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
55 DROP TABLE t1;
56 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
57 ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=COPY;
58 #-------------------------------------------------------
59 # general [encrypted] => file-per-table [unencrypted]
60 DROP TABLE t1;
61 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
62 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
63 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
64 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
65 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
66 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=INPLACE;
67 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
68 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=COPY;
69 SHOW CREATE TABLE t1;
70 Table Create Table
71 t1 CREATE TABLE `t1` (
72 `c` int DEFAULT NULL
73 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
74 DROP TABLE t1;
75 # general [encrypted] => file-per-table [encrypted]
76 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION= 'Y';
77 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=INPLACE;
78 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=COPY;
79 SHOW CREATE TABLE t1;
80 Table Create Table
81 t1 CREATE TABLE `t1` (
82 `c` int DEFAULT NULL
83 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
84 #-------------------------------------------------------
85 # general [unencrypted] => file-per-table [unencrypted]
86 DROP TABLE t1;
87 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
88 SHOW CREATE TABLE t1;
89 Table Create Table
90 t1 CREATE TABLE `t1` (
91 `c` int DEFAULT NULL
92 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
93 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
94 SHOW CREATE TABLE t1;
95 Table Create Table
96 t1 CREATE TABLE `t1` (
97 `c` int DEFAULT NULL
98 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
99 DROP TABLE t1;
100 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
101 SHOW CREATE TABLE t1;
102 Table Create Table
103 t1 CREATE TABLE `t1` (
104 `c` int DEFAULT NULL
105 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
106 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
107 SHOW CREATE TABLE t1;
108 Table Create Table
109 t1 CREATE TABLE `t1` (
110 `c` int DEFAULT NULL
111 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
112 # general [unencrypted] => file-per-table [encrypted]
113 DROP TABLE t1;
114 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
115 SHOW CREATE TABLE t1;
116 Table Create Table
117 t1 CREATE TABLE `t1` (
118 `c` int DEFAULT NULL
119 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
120 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=INPLACE;
121 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
122 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='y', ALGORITHM=COPY;
123 SHOW CREATE TABLE t1;
124 Table Create Table
125 t1 CREATE TABLE `t1` (
126 `c` int DEFAULT NULL
127 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
128 # general [unencrypted] => file-per-table [unencrypted]
129 DROP TABLE t1;
130 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
131 SHOW CREATE TABLE t1;
132 Table Create Table
133 t1 CREATE TABLE `t1` (
134 `c` int DEFAULT NULL
135 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
136 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=INPLACE;
137 SHOW CREATE TABLE t1;
138 Table Create Table
139 t1 CREATE TABLE `t1` (
140 `c` int DEFAULT NULL
141 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
142 DROP TABLE t1;
143 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
144 SHOW CREATE TABLE t1;
145 Table Create Table
146 t1 CREATE TABLE `t1` (
147 `c` int DEFAULT NULL
148 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
149 ALTER TABLE t1 TABLESPACE=innodb_file_per_table ENCRYPTION='n', ALGORITHM=COPY;
150 SHOW CREATE TABLE t1;
151 Table Create Table
152 t1 CREATE TABLE `t1` (
153 `c` int DEFAULT NULL
154 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
155 #-------------------------------------------------------
156 # file-per-table [unencrypted] => general [encrypted]
157 DROP TABLE t1;
158 CREATE TABLE t1 (c int) ENCRYPTION='n';
159 SHOW CREATE TABLE t1;
160 Table Create Table
161 t1 CREATE TABLE `t1` (
162 `c` int DEFAULT NULL
163 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
164 ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=INPLACE;
165 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
166 SHOW CREATE TABLE t1;
167 Table Create Table
168 t1 CREATE TABLE `t1` (
169 `c` int DEFAULT NULL
170 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
171 DROP TABLE t1;
172 CREATE TABLE t1 (c int) ENCRYPTION='n';
173 SHOW CREATE TABLE t1;
174 Table Create Table
175 t1 CREATE TABLE `t1` (
176 `c` int DEFAULT NULL
177 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
178 ALTER TABLE t1 TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=COPY;
179 SHOW CREATE TABLE t1;
180 Table Create Table
181 t1 CREATE TABLE `t1` (
182 `c` int DEFAULT NULL
183 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
184 # file-per-table [encrypted] => general [encrypted]
185 DROP TABLE t1;
186 CREATE TABLE t1 (c int) ENCRYPTION='y';
187 SHOW CREATE TABLE t1;
188 Table Create Table
189 t1 CREATE TABLE `t1` (
190 `c` int DEFAULT NULL
191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
192 ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=INPLACE;
193 DROP TABLE t1;
194 CREATE TABLE t1 (c int) ENCRYPTION='y';
195 SHOW CREATE TABLE t1;
196 Table Create Table
197 t1 CREATE TABLE `t1` (
198 `c` int DEFAULT NULL
199 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
200 ALTER TABLE t1 TABLESPACE=encrypt_ts, ALGORITHM=COPY;
201 SHOW CREATE TABLE t1;
202 Table Create Table
203 t1 CREATE TABLE `t1` (
204 `c` int DEFAULT NULL
205 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='y' */
206 # file-per-table [encrypted] => general [unencrypted]
207 DROP TABLE t1;
208 CREATE TABLE t1 (c int) ENCRYPTION='y';
209 SHOW CREATE TABLE t1;
210 Table Create Table
211 t1 CREATE TABLE `t1` (
212 `c` int DEFAULT NULL
213 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
214 ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=INPLACE;
215 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
216 ALTER TABLE t1 TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=COPY;
217 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
218 # file-per-table [unencrypted] => general [unencrypted]
219 DROP TABLE t1;
220 CREATE TABLE t1 (c int) ENCRYPTION='n';
221 SHOW CREATE TABLE t1;
222 Table Create Table
223 t1 CREATE TABLE `t1` (
224 `c` int DEFAULT NULL
225 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
226 ALTER TABLE t1 TABLESPACE=unencrypt_ts, ALGORITHM=INPLACE;
227 SHOW CREATE TABLE t1;
228 Table Create Table
229 t1 CREATE TABLE `t1` (
230 `c` int DEFAULT NULL
231 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
232 DROP TABLE t1;
233 CREATE TABLE t1 (c int) ENCRYPTION='n';
234 SHOW CREATE TABLE t1;
235 Table Create Table
236 t1 CREATE TABLE `t1` (
237 `c` int DEFAULT NULL
238 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
239 ALTER TABLE t1 TABLESPACE=unencrypt_ts, ALGORITHM=COPY;
240 SHOW CREATE TABLE t1;
241 Table Create Table
242 t1 CREATE TABLE `t1` (
243 `c` int DEFAULT NULL
244 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
245 DROP TABLE t1;
246 #-------------------------------------------------------
247 # ALTER TABLE ADD COLUMN/INDEX WITH MOVING TO DIFFERENT TABLESPACE
248 CREATE TABLESPACE encrypt_ts1 add datafile 'encrypt_ts1.ibd' ENCRYPTION='Y';
249 CREATE TABLESPACE unencrypt_ts1 add datafile 'unencrypt_ts1.ibd' ENCRYPTION='N';
250 # ALGORITHM=DEFAULT
251 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
252 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts ENCRYPTION='N';
253 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
254 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=encrypt_ts1;
255 SHOW CREATE TABLE t1;
256 Table Create Table
257 t1 CREATE TABLE `t1` (
258 `c` int DEFAULT NULL,
259 `c2` char(10) DEFAULT NULL
260 ) /*!50100 TABLESPACE `encrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
261 ALTER TABLE t1 ADD INDEX (c), TABLESPACE=encrypt_ts;
262 SHOW CREATE TABLE t1;
263 Table Create Table
264 t1 CREATE TABLE `t1` (
265 `c` int DEFAULT NULL,
266 `c2` char(10) DEFAULT NULL,
267 KEY `c` (`c`)
268 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
269 DROP TABLE t1;
270 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
271 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts1;
272 SHOW CREATE TABLE t1;
273 Table Create Table
274 t1 CREATE TABLE `t1` (
275 `c` int DEFAULT NULL,
276 `c2` char(10) DEFAULT NULL
277 ) /*!50100 TABLESPACE `unencrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
278 ALTER TABLE t1 ADD INDEX (c), TABLESPACE=unencrypt_ts;
279 SHOW CREATE TABLE t1;
280 Table Create Table
281 t1 CREATE TABLE `t1` (
282 `c` int DEFAULT NULL,
283 `c2` char(10) DEFAULT NULL,
284 KEY `c` (`c`)
285 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
286 ALTER TABLE t1 ADD INDEX (c2), TABLESPACE=encrypt_ts ENCRYPTION='Y';
287 SHOW CREATE TABLE t1;
288 Table Create Table
289 t1 CREATE TABLE `t1` (
290 `c` int DEFAULT NULL,
291 `c2` char(10) DEFAULT NULL,
292 KEY `c` (`c`),
293 KEY `c2` (`c2`)
294 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
295 DROP TABLE t1;
296 # ALGORITHM=INPLACE
297 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
298 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=INPLACE;
299 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
300 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=encrypt_ts1 ENCRYPTION='Y', ALGORITHM=INPLACE;
301 SHOW CREATE TABLE t1;
302 Table Create Table
303 t1 CREATE TABLE `t1` (
304 `c` int DEFAULT NULL,
305 `c2` char(10) DEFAULT NULL
306 ) /*!50100 TABLESPACE `encrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
307 ALTER TABLE t1 ADD INDEX (c), TABLESPACE=encrypt_ts, ALGORITHM=INPLACE;
308 SHOW CREATE TABLE t1;
309 Table Create Table
310 t1 CREATE TABLE `t1` (
311 `c` int DEFAULT NULL,
312 `c2` char(10) DEFAULT NULL,
313 KEY `c` (`c`)
314 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
315 DROP TABLE t1;
316 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
317 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts1, ALGORITHM=INPLACE;
318 SHOW CREATE TABLE t1;
319 Table Create Table
320 t1 CREATE TABLE `t1` (
321 `c` int DEFAULT NULL,
322 `c2` char(10) DEFAULT NULL
323 ) /*!50100 TABLESPACE `unencrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
324 ALTER TABLE t1 ADD INDEX (c), TABLESPACE=unencrypt_ts, ALGORITHM=INPLACE;
325 SHOW CREATE TABLE t1;
326 Table Create Table
327 t1 CREATE TABLE `t1` (
328 `c` int DEFAULT NULL,
329 `c2` char(10) DEFAULT NULL,
330 KEY `c` (`c`)
331 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
332 ALTER TABLE t1 ADD INDEX (c2), TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=INPLACE;
333 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot alter encryption attribute by inplace algorithm.. Try ALGORITHM=COPY.
334 SHOW CREATE TABLE t1;
335 Table Create Table
336 t1 CREATE TABLE `t1` (
337 `c` int DEFAULT NULL,
338 `c2` char(10) DEFAULT NULL,
339 KEY `c` (`c`)
340 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
341 DROP TABLE t1;
342 # ALGORITHM=COPY
343 CREATE TABLE t1 (c int) TABLESPACE=encrypt_ts ENCRYPTION='Y';
344 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts ENCRYPTION='N', ALGORITHM=COPY;
345 ERROR HY000: Source tablespace is encrypted but target tablespace is not.
346 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=encrypt_ts1, ALGORITHM=COPY;
347 SHOW CREATE TABLE t1;
348 Table Create Table
349 t1 CREATE TABLE `t1` (
350 `c` int DEFAULT NULL,
351 `c2` char(10) DEFAULT NULL
352 ) /*!50100 TABLESPACE `encrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
353 ALTER TABLE t1 ADD INDEX (c), TABLESPACE=encrypt_ts, ALGORITHM=COPY;
354 SHOW CREATE TABLE t1;
355 Table Create Table
356 t1 CREATE TABLE `t1` (
357 `c` int DEFAULT NULL,
358 `c2` char(10) DEFAULT NULL,
359 KEY `c` (`c`)
360 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
361 DROP TABLE t1;
362 CREATE TABLE t1 (c int) TABLESPACE=unencrypt_ts;
363 ALTER TABLE t1 ADD c2 char(10), TABLESPACE=unencrypt_ts1, ALGORITHM=COPY;
364 SHOW CREATE TABLE t1;
365 Table Create Table
366 t1 CREATE TABLE `t1` (
367 `c` int DEFAULT NULL,
368 `c2` char(10) DEFAULT NULL
369 ) /*!50100 TABLESPACE `unencrypt_ts1` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
370 ALTER TABLE t1 ADD INDEX (c), TABLESPACE=unencrypt_ts, ALGORITHM=COPY;
371 SHOW CREATE TABLE t1;
372 Table Create Table
373 t1 CREATE TABLE `t1` (
374 `c` int DEFAULT NULL,
375 `c2` char(10) DEFAULT NULL,
376 KEY `c` (`c`)
377 ) /*!50100 TABLESPACE `unencrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
378 ALTER TABLE t1 ADD INDEX (c2), TABLESPACE=encrypt_ts ENCRYPTION='Y', ALGORITHM=COPY;
379 SHOW CREATE TABLE t1;
380 Table Create Table
381 t1 CREATE TABLE `t1` (
382 `c` int DEFAULT NULL,
383 `c2` char(10) DEFAULT NULL,
384 KEY `c` (`c`),
385 KEY `c2` (`c2`)
386 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
387 DROP TABLE t1;
388 #########################################################################
389 # Partitioned Table #
390 #########################################################################
391
392 DROP TABLE IF EXISTS t1;
393 # Create table without explicit tablespace name
394 CREATE TABLE t1 (id INT, name VARCHAR(50))
395 PARTITION BY RANGE(id) (
396 PARTITION p0 VALUES LESS THAN (10),
397 PARTITION p1 VALUES LESS THAN (20),
398 PARTITION p2 VALUES LESS THAN (30));
399 # Try to ALTER TABLE to have general tablespace at table level
400 ALTER TABLE t1 TABLESPACE=unencrypt_ts;
401 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
402 SHOW WARNINGS;
403 Level Code Message
404 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
405 # Alter encryption option
406 ALTER TABLE t1 ENCRYPTION='Y';
407 DROP TABLE t1;
408 # Create table with general tablespace at table level
409 CREATE TABLE t1 (id INT, name VARCHAR(50))
410 TABLESPACE=encrypt_ts ENCRYPTION='Y'
411 PARTITION BY RANGE(id) (
412 PARTITION p0 VALUES LESS THAN (10),
413 PARTITION p1 VALUES LESS THAN (20),
414 PARTITION p2 VALUES LESS THAN (30));
415 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
416 SHOW WARNINGS;
417 Level Code Message
418 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
419 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
420 # Create table with system tablespace at table level
421 CREATE TABLE t1 (id INT, name VARCHAR(50))
422 TABLESPACE=innodb_system
423 PARTITION BY RANGE(id) (
424 PARTITION p0 VALUES LESS THAN (10),
425 PARTITION p1 VALUES LESS THAN (20),
426 PARTITION p2 VALUES LESS THAN (30));
427 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
428 SHOW WARNINGS;
429 Level Code Message
430 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
431 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
432 # Create table with innodb_file_per_table tablespace at table level
433 CREATE TABLE t1 (id INT, name VARCHAR(50))
434 TABLESPACE=innodb_file_per_table
435 PARTITION BY RANGE(id) (
436 PARTITION p0 VALUES LESS THAN (10),
437 PARTITION p1 VALUES LESS THAN (20),
438 PARTITION p2 VALUES LESS THAN (30));
439 DROP TABLE t1;
440 # Create table with general tablespace at partition level
441 CREATE TABLE t1 (id INT, name VARCHAR(50)) ENCRYPTION='Y'
442 PARTITION BY RANGE(id) (
443 PARTITION p0 VALUES LESS THAN (10),
444 PARTITION p1 VALUES LESS THAN (20),
445 PARTITION p2 VALUES LESS THAN (30) TABLESPACE=encrypt_ts);
446 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
447 SHOW WARNINGS;
448 Level Code Message
449 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
450 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
451 # Create table with system tablespace at partition level
452 CREATE TABLE t1 (id INT, name VARCHAR(50))
453 PARTITION BY RANGE(id) (
454 PARTITION p0 VALUES LESS THAN (10),
455 PARTITION p1 VALUES LESS THAN (20),
456 PARTITION p2 VALUES LESS THAN (30) TABLESPACE=innodb_system);
457 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
458 SHOW WARNINGS;
459 Level Code Message
460 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
461 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
462 # Create table with innodb_file_per_table tablespace at partition level
463 CREATE TABLE t1 (id INT, name VARCHAR(50))
464 PARTITION BY RANGE(id) (
465 PARTITION p0 VALUES LESS THAN (10),
466 PARTITION p1 VALUES LESS THAN (20),
467 PARTITION p2 VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table);
468 DROP TABLE t1;
469 # Create table with ENCRYPTION='y' option
470 CREATE TABLE t1 (id INT, name VARCHAR(50))
471 ENCRYPTION='Y'
472 PARTITION BY RANGE(id) (
473 PARTITION p0 VALUES LESS THAN (10),
474 PARTITION p1 VALUES LESS THAN (20),
475 PARTITION p2 VALUES LESS THAN (30));
476 INSERT INTO t1 VALUES (5,'test');
477 INSERT INTO t1 VALUES (12,'test1');
478 INSERT INTO t1 VALUES (22,'test2');
479 # Truncate specific partition of encrypted table
480 ALTER TABLE t1 TRUNCATE PARTITION p0;
481 # Alter encryption option
482 ALTER TABLE t1 ENCRYPTION='N';
483 DROP TABLE t1;
484 # Create table with ENCRYPTION='y' option and innodb_file_per_table
485 # tablespace at table level
486 CREATE TABLE t1 (id INT, name VARCHAR(50))
487 ENCRYPTION='Y' TABLESPACE=innodb_file_per_table
488 PARTITION BY RANGE(id) (
489 PARTITION p0 VALUES LESS THAN (10),
490 PARTITION p1 VALUES LESS THAN (20),
491 PARTITION p2 VALUES LESS THAN (30));
492 # Alter encryption option
493 ALTER TABLE t1 ENCRYPTION='N';
494 # Alter table to move a partition to general tablespace.
495 ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
496 PARTITION P2 VALUES LESS THAN (30) TABLESPACE=encrypt_ts);
497 ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
498 SHOW WARNINGS;
499 Level Code Message
500 Error 3825 Request to create 'unencrypted' table while using an 'encrypted' tablespace.
501 ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
502 PARTITION P2 VALUES LESS THAN (30) TABLESPACE=unencrypt_ts);
503 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
504 SHOW WARNINGS;
505 Level Code Message
506 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
507 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
508 # Alter table to move a partition to system tablespace.
509 ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
510 PARTITION P2 VALUES LESS THAN (30) TABLESPACE=innodb_system);
511 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
512 SHOW WARNINGS;
513 Level Code Message
514 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
515 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
516 # Alter table to move a partition to file_per_table tablespace.
517 ALTER TABLE t1 REORGANIZE PARTITION P2 INTO (
518 PARTITION P2 VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table);
519 SHOW CREATE TABLE t1;
520 Table Create Table
521 t1 CREATE TABLE `t1` (
522 `id` int DEFAULT NULL,
523 `name` varchar(50) DEFAULT NULL
524 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
525 /*!50100 PARTITION BY RANGE (`id`)
526 (PARTITION p0 VALUES LESS THAN (10) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
527 PARTITION p1 VALUES LESS THAN (20) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
528 PARTITION P2 VALUES LESS THAN (30) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
529 # Alter table to add a new partition in general tablespace
530 ALTER TABLE t1 ADD PARTITION (
531 PARTITION p3 VALUES LESS THAN (40) tablespace=encrypt_ts);
532 ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
533 SHOW WARNINGS;
534 Level Code Message
535 Error 3825 Request to create 'unencrypted' table while using an 'encrypted' tablespace.
536 ALTER TABLE t1 ADD PARTITION (
537 PARTITION p3 VALUES LESS THAN (40) tablespace=unencrypt_ts);
538 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
539 SHOW WARNINGS;
540 Level Code Message
541 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
542 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
543 # Alter table to add a new partition in innodb_system tablespace
544 ALTER TABLE t1 ADD PARTITION (
545 PARTITION p3 VALUES LESS THAN (40) tablespace=innodb_system);
546 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
547 SHOW WARNINGS;
548 Level Code Message
549 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
550 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
551 # Alter table to add a new partition in innodb_file_per_table tablespace
552 ALTER TABLE t1 ADD PARTITION (
553 PARTITION p3 VALUES LESS THAN (40) tablespace=innodb_file_per_table);
554 # Alter table to add a new partition without giving tablespace
555 ALTER TABLE t1 ADD PARTITION (
556 PARTITION p4 VALUES LESS THAN (50));
557 SHOW CREATE TABLE t1;
558 Table Create Table
559 t1 CREATE TABLE `t1` (
560 `id` int DEFAULT NULL,
561 `name` varchar(50) DEFAULT NULL
562 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
563 /*!50100 PARTITION BY RANGE (`id`)
564 (PARTITION p0 VALUES LESS THAN (10) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
565 PARTITION p1 VALUES LESS THAN (20) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
566 PARTITION P2 VALUES LESS THAN (30) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
567 PARTITION p3 VALUES LESS THAN (40) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB,
568 PARTITION p4 VALUES LESS THAN (50) TABLESPACE = `innodb_file_per_table` ENGINE = InnoDB) */
569 # Alter table to add a new partition with encryption option at partition
570 # level.
571 ALTER TABLE t1 ADD PARTITION (
572 PARTITION p5 VALUES LESS THAN (60) encryption='Y');
573 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 'encryption='Y')' at line 2
574 DROP TABLE t1;
575 # Create table with ENCRYPTION='y' option and general tablespace
576 # at table level
577 CREATE TABLE t1 (id INT, name VARCHAR(50))
578 ENCRYPTION='Y' TABLESPACE=encrypt_ts
579 PARTITION BY RANGE(id) (
580 PARTITION p0 VALUES LESS THAN (10),
581 PARTITION p1 VALUES LESS THAN (20),
582 PARTITION p2 VALUES LESS THAN (30));
583 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
584 SHOW WARNINGS;
585 Level Code Message
586 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
587 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
588 # Create table with ENCRYPTION='y' option and general tablespace
589 # at partition level
590 CREATE TABLE t1 (id INT, name VARCHAR(50))
591 ENCRYPTION='Y'
592 PARTITION BY RANGE(id) (
593 PARTITION p0 VALUES LESS THAN (10),
594 PARTITION p1 VALUES LESS THAN (20),
595 PARTITION p2 VALUES LESS THAN (30) TABLESPACE=encrypt_ts);
596 ERROR HY000: InnoDB : A partitioned table is not allowed in a shared tablespace.
597 SHOW WARNINGS;
598 Level Code Message
599 Error 1478 InnoDB : A partitioned table is not allowed in a shared tablespace.
600 Error 1030 Got error 122 - 'Internal (unspecified) error in handler' from storage engine
601 ###########
602 # Cleanup #
603 ###########
604 DROP TABLE IF EXISTS t1;
605 DROP TABLESPACE encrypt_ts;
606 DROP TABLESPACE encrypt_ts1;
607 DROP TABLESPACE unencrypt_ts;
608 DROP TABLESPACE unencrypt_ts1;
609 # Restarting server without keyring to restore server state
610 # restart: