Debian Codegraph @

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

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