Debian Codegraph @

mysql-test/suite/component_keyring_file/r/tablespace_encrypt_1.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
9 #########################################################################
10 # Taking backup of local manifest file for MySQL server instance
11 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='Y';
12 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
13 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;
14 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
15 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
16 DROP TABLESPACE encrypt_ts;
17 #########################################################################
18 # RESTART 1 : WITH KEYRING
19 #########################################################################
20 # Restore local manifest file for MySQL server instance from backup
21 DROP TABLE IF EXISTS t1;
22 ---------------------------
23 SYSTEM TABLESPACE
24 ---------------------------
25 CREATE TABLE t1(c int) ENCRYPTION="Y" tablespace innodb_system ENGINE = InnoDB;
26 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
27 SHOW WARNINGS;
28 Level Code Message
29 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
30 SET GLOBAL innodb_file_per_table = 0;
31 SELECT @@innodb_file_per_table;
32 @@innodb_file_per_table
33 0
34 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
35 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
36 SHOW WARNINGS;
37 Level Code Message
38 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
39 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="N" ENGINE = InnoDB;
40 SHOW WARNINGS;
41 Level Code Message
42 DROP TABLE t1;
43 CREATE TABLE t1(c int) ENCRYPTION="Y" tablespace innodb_system ENGINE = InnoDB;
44 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
45 SHOW WARNINGS;
46 Level Code Message
47 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
48 CREATE TABLE t1(c int) ENCRYPTION="N" tablespace innodb_system ENGINE = InnoDB;
49 SHOW WARNINGS;
50 Level Code Message
51 DROP TABLE t1;
52 SET GLOBAL innodb_file_per_table = 1;
53 SELECT @@innodb_file_per_table;
54 @@innodb_file_per_table
55 1
56 ALTER TABLESPACE innodb_system ENCRYPTION='Y';
57 ERROR 42000: InnoDB: `innodb_system` is a reserved tablespace name.
58 ---------------------------
59 TEMPORARY TABLESPACE
60 ---------------------------
61 CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="Y";
62 ERROR HY000: ENCRYPTION clause is not valid for temporary tablespace.
63 SHOW WARNINGS;
64 Level Code Message
65 Error 3833 ENCRYPTION clause is not valid for temporary tablespace.
66 CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="N";
67 ERROR HY000: ENCRYPTION clause is not valid for temporary tablespace.
68 SHOW WARNINGS;
69 Level Code Message
70 Error 3833 ENCRYPTION clause is not valid for temporary tablespace.
71 ------------------------------------------------
72 CREATE TABLESPACE WITH INVALID ENCRYPTION OPTION
73 ------------------------------------------------
74 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='R';
75 ERROR HY000: Invalid encryption option.
76 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=y;
77 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 'y' at line 1
78 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=Y;
79 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 'Y' at line 1
80 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=n;
81 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 'n' at line 1
82 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=N;
83 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 'N' at line 1
84 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=1;
85 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 '1' at line 1
86 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=0;
87 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 '0' at line 1
88 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=null;
89 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 'null' at line 1
90 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='TRUE';
91 ERROR HY000: Invalid encryption option.
92 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=TRUE;
93 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 'TRUE' at line 1
94 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=True;
95 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 'True' at line 1
96 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='True';
97 ERROR HY000: Invalid encryption option.
98 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='true';
99 ERROR HY000: Invalid encryption option.
100 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=true;
101 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 'true' at line 1
102 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=FALSE;
103 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 'FALSE' at line 1
104 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='FALSE';
105 ERROR HY000: Invalid encryption option.
106 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=False;
107 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 'False' at line 1
108 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='False';
109 ERROR HY000: Invalid encryption option.
110 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=false;
111 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 'false' at line 1
112 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='false';
113 ERROR HY000: Invalid encryption option.
114 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=-1;
115 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 '-1' at line 1
116 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='';
117 ERROR HY000: Invalid encryption option.
118 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="";
119 ERROR HY000: Invalid encryption option.
120 ----------------------------------------------------
121 CREATE ENCRYPTED TABLESPACE IN MyISAM STORAGE ENGINE
122 ----------------------------------------------------
123 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='Y';
124 ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
125 SHOW WARNINGS;
126 Level Code Message
127 Error 1478 Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
128 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='y';
129 ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
130 SHOW WARNINGS;
131 Level Code Message
132 Error 1478 Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
133 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='N';
134 ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
135 SHOW WARNINGS;
136 Level Code Message
137 Error 1478 Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
138 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='n';
139 ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
140 SHOW WARNINGS;
141 Level Code Message
142 Error 1478 Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
143 ----------------------------------------------------
144 CREATE ENCRYPTED TABLESPACE IN MEMORY STORAGE ENGINE
145 ----------------------------------------------------
146 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='Y';
147 ERROR HY000: Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
148 SHOW WARNINGS;
149 Level Code Message
150 Error 1478 Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
151 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='y';
152 ERROR HY000: Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
153 SHOW WARNINGS;
154 Level Code Message
155 Error 1478 Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
156 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='N';
157 ERROR HY000: Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
158 SHOW WARNINGS;
159 Level Code Message
160 Error 1478 Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
161 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='n';
162 ERROR HY000: Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
163 SHOW WARNINGS;
164 Level Code Message
165 Error 1478 Table storage engine 'MEMORY' does not support the create option 'CREATE TABLESPACE'
166 ----------------------------------------------
167 CREATE TABLESPACE WITH VALID ENCRYPTION OPTION
168 ----------------------------------------------
169 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='Y';
170 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
171 NAME ENCRYPTION
172 encrypt_ts Y
173 DROP TABLESPACE encrypt_ts;
174 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='y';
175 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
176 NAME ENCRYPTION
177 encrypt_ts Y
178 DROP TABLESPACE encrypt_ts;
179 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='n';
180 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
181 NAME ENCRYPTION
182 encrypt_ts N
183 DROP TABLESPACE encrypt_ts;
184 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='N';
185 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
186 NAME ENCRYPTION
187 encrypt_ts N
188 DROP TABLESPACE encrypt_ts;
189 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="Y";
190 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
191 NAME ENCRYPTION
192 encrypt_ts Y
193 DROP TABLESPACE encrypt_ts;
194 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="y";
195 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
196 NAME ENCRYPTION
197 encrypt_ts Y
198 DROP TABLESPACE encrypt_ts;
199 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="n";
200 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
201 NAME ENCRYPTION
202 encrypt_ts N
203 DROP TABLESPACE encrypt_ts;
204 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="N";
205 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
206 NAME ENCRYPTION
207 encrypt_ts N
208 DROP TABLESPACE encrypt_ts;
209 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;
210 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
211 NAME ENCRYPTION
212 encrypt_ts N
213 -----------------------------------------------
214 ALTER TABLESPACE WITH INVALID ENCRYPTION OPTION
215 -----------------------------------------------
216 ALTER TABLESPACE encrypt_ts ENCRYPTION='R';
217 ERROR HY000: Invalid encryption option.
218 ALTER TABLESPACE encrypt_ts ENCRYPTION=1;
219 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 '1' at line 1
220 ALTER TABLESPACE encrypt_ts ENCRYPTION=0;
221 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 '0' at line 1
222 ALTER TABLESPACE encrypt_ts ENCRYPTION=TRUE;
223 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 'TRUE' at line 1
224 ALTER TABLESPACE encrypt_ts ENCRYPTION='TRUE';
225 ERROR HY000: Invalid encryption option.
226 ALTER TABLESPACE encrypt_ts ENCRYPTION=True;
227 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 'True' at line 1
228 ALTER TABLESPACE encrypt_ts ENCRYPTION='True';
229 ERROR HY000: Invalid encryption option.
230 ALTER TABLESPACE encrypt_ts ENCRYPTION=true;
231 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 'true' at line 1
232 ALTER TABLESPACE encrypt_ts ENCRYPTION='true';
233 ERROR HY000: Invalid encryption option.
234 ALTER TABLESPACE encrypt_ts ENCRYPTION=FALSE;
235 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 'FALSE' at line 1
236 ALTER TABLESPACE encrypt_ts ENCRYPTION='FALSE';
237 ERROR HY000: Invalid encryption option.
238 ALTER TABLESPACE encrypt_ts ENCRYPTION=False;
239 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 'False' at line 1
240 ALTER TABLESPACE encrypt_ts ENCRYPTION='False';
241 ERROR HY000: Invalid encryption option.
242 ALTER TABLESPACE encrypt_ts ENCRYPTION=false;
243 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 'false' at line 1
244 ALTER TABLESPACE encrypt_ts ENCRYPTION='false';
245 ERROR HY000: Invalid encryption option.
246 ALTER TABLESPACE encrypt_ts ENCRYPTION=null;
247 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 'null' at line 1
248 ALTER TABLESPACE encrypt_ts ENCRYPTION=n;
249 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 'n' at line 1
250 ALTER TABLESPACE encrypt_ts ENCRYPTION=N;
251 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 'N' at line 1
252 ALTER TABLESPACE encrypt_ts ENCRYPTION=y;
253 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 'y' at line 1
254 ALTER TABLESPACE encrypt_ts ENCRYPTION=N;
255 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 'N' at line 1
256 ALTER TABLESPACE encrypt_ts ENCRYPTION=-1;
257 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 '-1' at line 1
258 ALTER TABLESPACE encrypt_ts ENCRYPTION='';
259 ERROR HY000: Invalid encryption option.
260 ALTER TABLESPACE encrypt_ts ENCRYPTION="";
261 ERROR HY000: Invalid encryption option.
262 ---------------------------------------------
263 ALTER TABLESPACE WITH VALID ENCRYPTION OPTION
264 ---------------------------------------------
265 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
266 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
267 NAME ENCRYPTION
268 encrypt_ts N
269 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
270 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
271 NAME ENCRYPTION
272 encrypt_ts N
273 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
274 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
275 NAME ENCRYPTION
276 encrypt_ts N
277 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
278 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
279 NAME ENCRYPTION
280 encrypt_ts N
281 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
282 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
283 NAME ENCRYPTION
284 encrypt_ts N
285 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
286 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
287 NAME ENCRYPTION
288 encrypt_ts N
289 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
290 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
291 NAME ENCRYPTION
292 encrypt_ts N
293 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
294 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
295 NAME ENCRYPTION
296 encrypt_ts N
297 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
298 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
299 NAME ENCRYPTION
300 encrypt_ts Y
301 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
302 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
303 NAME ENCRYPTION
304 encrypt_ts Y
305 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
306 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
307 NAME ENCRYPTION
308 encrypt_ts Y
309 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
310 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
311 NAME ENCRYPTION
312 encrypt_ts Y
313 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
314 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
315 NAME ENCRYPTION
316 encrypt_ts Y
317 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
318 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
319 NAME ENCRYPTION
320 encrypt_ts Y
321 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
322 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
323 NAME ENCRYPTION
324 encrypt_ts Y
325 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
326 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
327 NAME ENCRYPTION
328 encrypt_ts Y
329 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
330 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
331 NAME ENCRYPTION
332 encrypt_ts Y
333 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
334 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
335 NAME ENCRYPTION
336 encrypt_ts Y
337 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
338 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
339 NAME ENCRYPTION
340 encrypt_ts N
341 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
342 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
343 NAME ENCRYPTION
344 encrypt_ts N
345 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
346 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
347 NAME ENCRYPTION
348 encrypt_ts Y
349 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
350 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
351 NAME ENCRYPTION
352 encrypt_ts Y
353 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
354 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
355 NAME ENCRYPTION
356 encrypt_ts N
357 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
358 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
359 NAME ENCRYPTION
360 encrypt_ts N
361 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
362 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
363 NAME ENCRYPTION
364 encrypt_ts Y
365 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
366 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
367 NAME ENCRYPTION
368 encrypt_ts Y
369 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
370 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
371 NAME ENCRYPTION
372 encrypt_ts N
373 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
374 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
375 NAME ENCRYPTION
376 encrypt_ts N
377 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
378 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
379 NAME ENCRYPTION
380 encrypt_ts Y
381 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
382 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
383 NAME ENCRYPTION
384 encrypt_ts Y
385 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
386 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
387 NAME ENCRYPTION
388 encrypt_ts N
389 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
390 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
391 NAME ENCRYPTION
392 encrypt_ts N
393 -----------------------------------------
394 CREATE/ALTER TABLE in FILE-PER-TABLE TABLESPACE
395 -----------------------------------------
396 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
397 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
398 NAME ENCRYPTION
399 test/t1 Y
400 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
401 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
402 test t1 InnoDB ENCRYPTION='Y'
403 SHOW CREATE TABLE test.t1;
404 Table Create Table
405 t1 CREATE TABLE `t1` (
406 `c1` int DEFAULT NULL,
407 `c2` char(20) DEFAULT NULL
408 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
409 ALTER TABLE t1 encryption="N";
410 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
411 NAME ENCRYPTION
412 test/t1 N
413 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
414 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
415 test t1 InnoDB
416 SHOW CREATE TABLE test.t1;
417 Table Create Table
418 t1 CREATE TABLE `t1` (
419 `c1` int DEFAULT NULL,
420 `c2` char(20) DEFAULT NULL
421 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
422 DROP TABLE t1;
423 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="N" ENGINE = InnoDB;
424 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
425 NAME ENCRYPTION
426 test/t1 N
427 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
428 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
429 test t1 InnoDB
430 SHOW CREATE TABLE test.t1;
431 Table Create Table
432 t1 CREATE TABLE `t1` (
433 `c1` int DEFAULT NULL,
434 `c2` char(20) DEFAULT NULL
435 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
436 ALTER TABLE t1 encryption="Y";
437 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
438 NAME ENCRYPTION
439 test/t1 Y
440 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
441 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
442 test t1 InnoDB ENCRYPTION='Y'
443 SHOW CREATE TABLE test.t1;
444 Table Create Table
445 t1 CREATE TABLE `t1` (
446 `c1` int DEFAULT NULL,
447 `c2` char(20) DEFAULT NULL
448 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
449 DROP TABLE t1;
450 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=innodb_file_per_table ENCRYPTION="Y" ENGINE = InnoDB;
451 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
452 NAME ENCRYPTION
453 test/t1 Y
454 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
455 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
456 test t1 InnoDB ENCRYPTION='Y'
457 SHOW CREATE TABLE test.t1;
458 Table Create Table
459 t1 CREATE TABLE `t1` (
460 `c1` int DEFAULT NULL,
461 `c2` char(20) DEFAULT NULL
462 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
463 ALTER TABLE t1 encryption="N";
464 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
465 NAME ENCRYPTION
466 test/t1 N
467 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
468 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
469 test t1 InnoDB
470 SHOW CREATE TABLE test.t1;
471 Table Create Table
472 t1 CREATE TABLE `t1` (
473 `c1` int DEFAULT NULL,
474 `c2` char(20) DEFAULT NULL
475 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
476 DROP TABLE t1;
477 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=innodb_file_per_table ENCRYPTION="N" ENGINE = InnoDB;
478 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
479 NAME ENCRYPTION
480 test/t1 N
481 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
482 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
483 test t1 InnoDB
484 SHOW CREATE TABLE test.t1;
485 Table Create Table
486 t1 CREATE TABLE `t1` (
487 `c1` int DEFAULT NULL,
488 `c2` char(20) DEFAULT NULL
489 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
490 ALTER TABLE t1 encryption="Y";
491 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
492 NAME ENCRYPTION
493 test/t1 Y
494 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
495 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
496 test t1 InnoDB ENCRYPTION='Y'
497 SHOW CREATE TABLE test.t1;
498 Table Create Table
499 t1 CREATE TABLE `t1` (
500 `c1` int DEFAULT NULL,
501 `c2` char(20) DEFAULT NULL
502 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
503 DROP TABLE t1;
504 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=encrypt_ts ENGINE = InnoDB;
505 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
506 NAME ENCRYPTION
507 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
508 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
509 test t1 InnoDB
510 SHOW CREATE TABLE test.t1;
511 Table Create Table
512 t1 CREATE TABLE `t1` (
513 `c1` int DEFAULT NULL,
514 `c2` char(20) DEFAULT NULL
515 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
516 DROP TABLE t1;
517 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
518 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
519 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
520 NAME ENCRYPTION
521 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
522 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
523 test t1 InnoDB ENCRYPTION='Y'
524 SHOW CREATE TABLE test.t1;
525 Table Create Table
526 t1 CREATE TABLE `t1` (
527 `c1` int DEFAULT NULL,
528 `c2` char(20) DEFAULT NULL
529 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
530 DROP TABLE t1;
531 --------------------------
532 CREATE/ALTER TABLE IN GENERAL TABLESPACE
533 --------------------------
534 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="Y" ENGINE = InnoDB;
535 DROP TABLE t1;
536 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="N" ENGINE = InnoDB;
537 ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
538 SHOW WARNINGS;
539 Level Code Message
540 Error 3825 Request to create 'unencrypted' table while using an 'encrypted' tablespace.
541 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="R" ENGINE = InnoDB;
542 ERROR HY000: Invalid encryption option.
543 SHOW WARNINGS;
544 Level Code Message
545 Error 3184 Invalid encryption option.
546 Error 1031 Table storage engine for 't1' doesn't have this option
547 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
548 ALTER TABLE t1 encryption='Y';
549 ALTER TABLE t1 encryption='N';
550 ERROR HY000: Request to create 'unencrypted' table while using an 'encrypted' tablespace.
551 ALTER TABLE t1 encryption='R';
552 ERROR HY000: Invalid encryption option.
553 DROP TABLE t1;
554 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
555 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="N" ENGINE = InnoDB;
556 DROP TABLE t1;
557 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="Y" ENGINE = InnoDB;
558 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
559 SHOW WARNINGS;
560 Level Code Message
561 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
562 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="R" ENGINE = InnoDB;
563 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
564 SHOW WARNINGS;
565 Level Code Message
566 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
567 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENGINE = InnoDB;
568 ALTER TABLE t1 encryption='Y';
569 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
570 ALTER TABLE t1 encryption='N';
571 ALTER TABLE t1 encryption='R';
572 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
573 DROP TABLE t1;
574 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
575 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
576 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
577 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
578 test t1 InnoDB ENCRYPTION='Y'
579 SHOW CREATE TABLE t1;
580 Table Create Table
581 t1 CREATE TABLE `t1` (
582 `c1` int DEFAULT NULL,
583 `c2` char(20) DEFAULT NULL
584 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
585 INSERT INTO t1 VALUES(0, "aaaaa");
586 INSERT INTO t1 VALUES(1, "bbbbb");
587 INSERT INTO t1 VALUES(2, "ccccc");
588 INSERT INTO t1 VALUES(3, "ddddd");
589 INSERT INTO t1 VALUES(4, "eeeee");
590 INSERT INTO t1 VALUES(5, "fffff");
591 INSERT INTO t1 VALUES(6, "ggggg");
592 INSERT INTO t1 VALUES(7, "hhhhh");
593 INSERT INTO t1 VALUES(8, "iiiii");
594 INSERT INTO t1 VALUES(9, "jjjjj");
595 INSERT INTO t1 select * from t1;
596 INSERT INTO t1 select * from t1;
597 INSERT INTO t1 select * from t1;
598 INSERT INTO t1 select * from t1;
599 INSERT INTO t1 select * from t1;
600 INSERT INTO t1 select * from t1;
601 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
602 c1 c2
603 0 aaaaa
604 0 aaaaa
605 0 aaaaa
606 0 aaaaa
607 0 aaaaa
608 0 aaaaa
609 0 aaaaa
610 0 aaaaa
611 0 aaaaa
612 0 aaaaa
613 #########################################################################
614 # RESTART 2 : WITHOUT KEYRING
615 #########################################################################
616 # Taking backup of local manifest file for MySQL server instance
617 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
618 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
619 #########################################################################
620 # RESTART 3 : WITH KEYRING
621 #########################################################################
622 # Restore local manifest file for MySQL server instance from backup
623 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
624 c1 c2
625 0 aaaaa
626 0 aaaaa
627 0 aaaaa
628 0 aaaaa
629 0 aaaaa
630 0 aaaaa
631 0 aaaaa
632 0 aaaaa
633 0 aaaaa
634 0 aaaaa
635 SET GLOBAL innodb_file_per_table=1;
636 DROP TABLE t1;
637 CREATE TABLE t1(c2 char(100) , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB
638 tablespace=encrypt_ts ENCRYPTION='Y';
639 ALTER TABLE t1 DROP INDEX idx1;
640 ALTER TABLE t1 ADD COLUMN c4 CHAR(20);
641 DROP TABLE t1;
642 ----------------------
643 ALGORITHM=COPY/INPLACE
644 ----------------------
645 ALTER TABLESPACE encrypt_ts ENCRYPTION="N", algorithm=copy;
646 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 'algorithm=copy' at line 1
647 ALTER TABLESPACE encrypt_ts ENCRYPTION="N", algorithm=inplace;
648 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 'algorithm=inplace' at line 1
649 DROP TABLESPACE encrypt_ts;
650 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION="N", algorithm=copy;
651 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 'algorithm=copy' at line 1
652 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION="N", algorithm=inplace;
653 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 'algorithm=inplace' at line 1
654 -----------
655 COMPRESSION
656 -----------
657 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' COMPRESSION = "ZLIB" ENCRYPTION = "Y" ENGINE = InnoDB;
658 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 'COMPRESSION = "ZLIB" ENCRYPTION = "Y" ENGINE = InnoDB' at line 1
659 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION = "Y" ENGINE = InnoDB;
660 ALTER TABLESPACE encrypt_ts COMPRESSION = "ZLIB";
661 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 'COMPRESSION = "ZLIB"' at line 1
662 DROP TABLESPACE encrypt_ts;
663 CREATE TABLESPACE encryt_ts_1k ADD DATAFILE 'encrypt_ts_1k.ibd' FILE_BLOCK_SIZE=1k ENCRYPTION='Y';
664 CREATE TABLESPACE encryt_ts_2k ADD DATAFILE 'encrypt_ts_2k.ibd' FILE_BLOCK_SIZE=2k ENCRYPTION='Y';
665 CREATE TABLESPACE encryt_ts_4k ADD DATAFILE 'encrypt_ts_4k.ibd' FILE_BLOCK_SIZE=4k ENCRYPTION='Y';
666 CREATE TABLESPACE encryt_ts_8k ADD DATAFILE 'encrypt_ts_8k.ibd' FILE_BLOCK_SIZE=8k ENCRYPTION='Y';
667 CREATE TABLESPACE encryt_ts_16k ADD DATAFILE 'encrypt_ts_16k.ibd' FILE_BLOCK_SIZE=16k ENCRYPTION='Y';
668 CREATE TABLESPACE encryt_ts_32k ADD DATAFILE 'encrypt_ts_32k.ibd' FILE_BLOCK_SIZE=32k ENCRYPTION='Y';
669 ERROR HY000: InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=16384.
670 SHOW WARNINGS;
671 Level Code Message
672 Error 1478 InnoDB: Cannot create a tablespace with FILE_BLOCK_SIZE=32768 because INNODB_PAGE_SIZE=16384.
673 Error 1528 Failed to create TABLESPACE encryt_ts_32k
674 Error 1031 Table storage engine for 'encryt_ts_32k' doesn't have this option
675 DROP tablespace encryt_ts_1k;
676 DROP tablespace encryt_ts_2k;
677 DROP tablespace encryt_ts_4k;
678 DROP tablespace encryt_ts_8k;
679 DROP tablespace encryt_ts_16k;
680 CREATE TABLESPACE encrypt_ts ENCRYPTION='Y' ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M
681 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
682 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 DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M
683 MAX_SIZE = 1' at line 1
684 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION='Y' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M
685 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
686 DROP TABLESPACE encrypt_ts;
687 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y' AUTOEXTEND_SIZE = 12M
688 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
689 DROP TABLESPACE encrypt_ts;
690 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M ENCRYPTION='Y'
691 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
692 DROP TABLESPACE encrypt_ts;
693 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
694 ENCRYPTION='Y' NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
695 DROP TABLESPACE encrypt_ts;
696 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
697 NODEGROUP = 5 ENCRYPTION='Y' WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
698 DROP TABLESPACE encrypt_ts;
699 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
700 NODEGROUP = 5 WAIT ENCRYPTION='Y' COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
701 DROP TABLESPACE encrypt_ts;
702 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
703 NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' ENCRYPTION='Y' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
704 DROP TABLESPACE encrypt_ts;
705 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
706 NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M ENCRYPTION='Y'EXTENT_SIZE = 100M;
707 DROP TABLESPACE encrypt_ts;
708 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
709 NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M ENCRYPTION='Y';
710 DROP TABLESPACE encrypt_ts;
711 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
712 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
713 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
714 NAME ENCRYPTION
715 encrypt_ts Y
716 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
717 NAME ENCRYPTION
718 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
719 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
720 test t1 InnoDB ENCRYPTION='Y'
721 SHOW CREATE TABLE test.t1;
722 Table Create Table
723 t1 CREATE TABLE `t1` (
724 `c1` int DEFAULT NULL,
725 `c2` char(20) DEFAULT NULL
726 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
727 ALTER TABLESPACE encrypt_ts RENAME TO encrypt_ts_renamed;
728 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts_renamed';
729 NAME ENCRYPTION
730 encrypt_ts_renamed Y
731 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
732 NAME ENCRYPTION
733 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
734 TABLE_SCHEMA TABLE_NAME ENGINE CREATE_OPTIONS
735 test t1 InnoDB ENCRYPTION='Y'
736 SHOW CREATE TABLE test.t1;
737 Table Create Table
738 t1 CREATE TABLE `t1` (
739 `c1` int DEFAULT NULL,
740 `c2` char(20) DEFAULT NULL
741 ) /*!50100 TABLESPACE `encrypt_ts_renamed` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
742 DROP TABLE t1;
743 DROP TABLESPACE encrypt_ts_renamed;
744 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
745 CREATE PROCEDURE populate_t1()
746 BEGIN
747 DECLARE i int DEFAULT 1;
748 START TRANSACTION;
749 WHILE (i <= 10) DO
750 INSERT INTO t1 VALUES (i,CONCAT('a', i));
751 SET i = i + 1;
752 END WHILE;
753 COMMIT;
754 END|
755 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=COMPRESSED;
756 ERROR HY000: InnoDB: Tablespace `encrypt_ts` cannot contain a COMPRESSED table
757 SHOW WARNINGS;
758 Level Code Message
759 Error 1478 InnoDB: Tablespace `encrypt_ts` cannot contain a COMPRESSED table
760 Error 1031 Table storage engine for 't1' doesn't have this option
761 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=FIXED;
762 ERROR HY000: Table storage engine for 't1' doesn't have this option
763 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=DYNAMIC;
764 DROP TABLE t1;
765 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=REDUNDANT;
766 DROP TABLE t1;
767 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=COMPACT;
768 DROP TABLE t1;
769 DROP PROCEDURE populate_t1;
770 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' KEY_BLOCK_SIZE=1;
771 ERROR HY000: InnoDB: Tablespace `encrypt_ts` cannot contain a COMPRESSED table
772 SHOW WARNINGS;
773 Level Code Message
774 Error 1478 InnoDB: Tablespace `encrypt_ts` cannot contain a COMPRESSED table
775 Error 1031 Table storage engine for 't1' doesn't have this option
776 DROP TABLESPACE encrypt_ts;
777 SET block_encryption_mode = 'aes-256-cbc';
778 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
779 DROP DATABASE IF EXISTS tde_db;
780 CREATE DATABASE tde_db;
781 CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
782 INSERT INTO tde_db.t1 VALUES(0, 'abc');
783 INSERT INTO tde_db.t1 VALUES(1, 'xyz');
784 INSERT INTO tde_db.t1 VALUES(2, null);
785 INSERT INTO tde_db.t1 VALUES(3, null);
786 SELECT * FROM tde_db.t1 LIMIT 10;
787 c1 c2
788 0 abc
789 1 xyz
790 2 NULL
791 3 NULL
792 ALTER INSTANCE ROTATE INNODB MASTER KEY;
793 SELECT * FROM tde_db.t1 LIMIT 10;
794 c1 c2
795 0 abc
796 1 xyz
797 2 NULL
798 3 NULL
799 DROP DATABASE tde_db;
800 DROP TABLESPACE encrypt_ts;
801 DROP DATABASE IF EXISTS tde_db;
802 CREATE DATABASE tde_db;
803 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION ='Y' ENGINE=InnoDB;
804 CREATE TABLE tde_db.t_encrypt(c4 JSON ,
805 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
806 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
807 c7 POINT NOT NULL SRID 0,
808 spatial INDEX idx2 (c7)
809 ) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
810 SHOW CREATE TABLE tde_db.t_encrypt;
811 Table Create Table
812 t_encrypt CREATE TABLE `t_encrypt` (
813 `c4` json DEFAULT NULL,
814 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
815 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
816 `c7` point NOT NULL /*!80003 SRID 0 */,
817 SPATIAL KEY `idx2` (`c7`)
818 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
819 INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
820 INSERT INTO tde_db.t_encrypt(c4,c7) select c4,c7 from tde_db.t_encrypt;
821 SELECT c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
822 c4 c5 ST_AsText(c7)
823 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
824 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
825 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
826 c4 c5 c6 ST_AsText(c7)
827 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
828 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
829 SHOW CREATE TABLE tde_db.t_encrypt;
830 Table Create Table
831 t_encrypt CREATE TABLE `t_encrypt` (
832 `c4` json DEFAULT NULL,
833 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
834 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
835 `c7` point NOT NULL /*!80003 SRID 0 */,
836 SPATIAL KEY `idx2` (`c7`)
837 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
838 DROP TABLE IF EXISTS tde_db.t_encrypt;
839 CREATE TABLE tde_db.t_encrypt(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 char(100), c3 BLOB , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
840 CREATE TABLE tde_db.t_encrypt1(c11 INT , c22 char(100), c33 BLOB , FULLTEXT INDEX `idx1` (c22)) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
841 SHOW CREATE TABLE tde_db.t_encrypt;
842 Table Create Table
843 t_encrypt CREATE TABLE `t_encrypt` (
844 `c1` int NOT NULL AUTO_INCREMENT,
845 `c2` char(100) DEFAULT NULL,
846 `c3` blob,
847 PRIMARY KEY (`c1`),
848 FULLTEXT KEY `idx1` (`c2`)
849 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
850 SHOW CREATE TABLE tde_db.t_encrypt1;
851 Table Create Table
852 t_encrypt1 CREATE TABLE `t_encrypt1` (
853 `c11` int DEFAULT NULL,
854 `c22` char(100) DEFAULT NULL,
855 `c33` blob,
856 FULLTEXT KEY `idx1` (`c22`)
857 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
858 CREATE TABLE tde_db.t_encrypt2 (f1 INT PRIMARY KEY, f2 CHAR(100),
859 FOREIGN KEY (f1) REFERENCES tde_db.t_encrypt(c1) ON UPDATE CASCADE) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
860 CREATE TRIGGER tde_db.trigger_encrypt_table AFTER INSERT ON tde_db.t_encrypt
861 FOR EACH ROW
862 begin
863 INSERT INTO tde_db.t_encrypt1 SET c11 = NEW.c1*-1, c22 = NEW.c2 , c33 = NEW.c3;
864 end|
865 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("transparent tablespace encryption",repeat('A', 200));
866 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("general tablespace option",repeat('A', 200));
867 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("page level encryption",repeat('A', 200));
868 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(1,"transparent tablespace encryption");
869 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
870 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
871 c1 c2 right(c3, 20)
872 1 transparent tablespace encryption AAAAAAAAAAAAAAAAAAAA
873 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
874 3 page level encryption AAAAAAAAAAAAAAAAAAAA
875 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace');
876 c1 c2 right(c3, 20)
877 1 transparent tablespace encryption AAAAAAAAAAAAAAAAAAAA
878 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
879 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace' IN BOOLEAN MODE);
880 c1 c2 right(c3, 20)
881 1 transparent tablespace encryption AAAAAAAAAAAAAAAAAAAA
882 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
883 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('+tablespace -encryption' IN BOOLEAN MODE);
884 c1 c2 right(c3, 20)
885 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
886 ALTER TABLE tde_db.t_encrypt DROP INDEX idx1;
887 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
888 c1 c2 right(c3, 20)
889 1 transparent tablespace encryption AAAAAAAAAAAAAAAAAAAA
890 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
891 3 page level encryption AAAAAAAAAAAAAAAAAAAA
892 ALTER TABLE tde_db.t_encrypt ADD COLUMN c4 CHAR(20) DEFAULT 'text';
893 SELECT c1,c2,right(c3, 20),c4 FROM tde_db.t_encrypt LIMIT 10;
894 c1 c2 right(c3, 20) c4
895 1 transparent tablespace encryption AAAAAAAAAAAAAAAAAAAA text
896 2 general tablespace option AAAAAAAAAAAAAAAAAAAA text
897 3 page level encryption AAAAAAAAAAAAAAAAAAAA text
898 CREATE VIEW tde_db.t_encrypt_view AS SELECT c1,c2 FROM tde_db.t_encrypt;
899 SELECT c2 FROM tde_db.t_encrypt_view LIMIT 10;
900 c2
901 transparent tablespace encryption
902 general tablespace option
903 page level encryption
904 SELECT A.c2,B.c2,right(B.c3,20) FROM tde_db.t_encrypt_view A , tde_db.t_encrypt B WHERE A.c2 = B.c2;
905 c2 c2 right(B.c3,20)
906 transparent tablespace encryption transparent tablespace encryption AAAAAAAAAAAAAAAAAAAA
907 general tablespace option general tablespace option AAAAAAAAAAAAAAAAAAAA
908 page level encryption page level encryption AAAAAAAAAAAAAAAAAAAA
909 DROP VIEW tde_db.t_encrypt_view;
910 SELECT c11,c22,right(c33, 20) FROM tde_db.t_encrypt1 LIMIT 10;
911 c11 c22 right(c33, 20)
912 -1 transparent tablespace encryption AAAAAAAAAAAAAAAAAAAA
913 -2 general tablespace option AAAAAAAAAAAAAAAAAAAA
914 -3 page level encryption AAAAAAAAAAAAAAAAAAAA
915 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
916 ERROR 23000: Duplicate entry '2' for key 't_encrypt2.PRIMARY'
917 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(8,"general tablespace option");
918 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`tde_db`.`t_encrypt2`, CONSTRAINT `t_encrypt2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t_encrypt` (`c1`) ON UPDATE CASCADE)
919 SELECT f1,f2 FROM tde_db.t_encrypt2;
920 f1 f2
921 1 transparent tablespace encryption
922 2 general tablespace option
923 UPDATE tde_db.t_encrypt SET c1=10 WHERE c1=1;
924 SELECT f1,f2 FROM tde_db.t_encrypt2;
925 f1 f2
926 2 general tablespace option
927 10 transparent tablespace encryption
928 DROP DATABASE tde_db;
929 DROP DATABASE IF EXISTS tde_db;
930 CREATE DATABASE tde_db;
931 USE tde_db;
932 DROP TABLE IF EXISTS tde_db.t_encrypt;
933 CREATE TABLE tde_db.t_encrypt (c2 INT NOT NULL AUTO_INCREMENT ,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,c7 POINT NOT NULL SRID 0,spatial INDEX idx2 (c7) , PRIMARY KEY (c2,c3(100))) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
934 CREATE PROCEDURE tde_db.txn_t_encrypt()
935 BEGIN
936 declare i int default 0;
937 declare rowcnt int default 0;
938 START TRANSACTION;
939 WHILE (i <= 2000) DO
940 SET i = i + 1;
941 SET rowcnt = rowcnt + 1;
942 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (CONCAT(REPEAT('a',10),REPEAT(i,10)),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
943 IF (rowcnt = 3) THEN
944 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 21, "key_b": 22, "key_c": 23 }' WHERE c2 = i-1 ;
945 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
946 SAVEPOINT A;
947 END IF;
948 IF (rowcnt = 5) THEN
949 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 41, "key_b": 42, "key_c": 43 }' WHERE c2 = i-1 ;
950 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
951 SAVEPOINT B;
952 END IF;
953 IF (rowcnt = 10) THEN
954 ROLLBACK TO SAVEPOINT A;
955 COMMIT;
956 SET rowcnt = 0;
957 START TRANSACTION;
958 END IF;
959 END WHILE;
960 COMMIT;
961 end|
962 call tde_db.txn_t_encrypt();
963 SELECT COUNT(*) FROM tde_db.t_encrypt;
964 COUNT(*)
965 401
966 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
967 c2 RIGHT(c3,20) c4
968 1 aaaaaaaaaa1111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
969 2 aaaaaaaaaa2222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
970 11 11111111111111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
971 12 12121212121212121212 {"key_a": 21, "key_b": 22, "key_c": 23}
972 21 21212121212121212121 {"key_a": 1, "key_b": 2, "key_c": 3}
973 22 22222222222222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
974 31 31313131313131313131 {"key_a": 1, "key_b": 2, "key_c": 3}
975 32 32323232323232323232 {"key_a": 21, "key_b": 22, "key_c": 23}
976 41 41414141414141414141 {"key_a": 1, "key_b": 2, "key_c": 3}
977 42 42424242424242424242 {"key_a": 21, "key_b": 22, "key_c": 23}
978 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
979 c2 RIGHT(c3,20) c4
980 501 01501501501501501501 {"key_a": 1, "key_b": 2, "key_c": 3}
981 502 02502502502502502502 {"key_a": 21, "key_b": 22, "key_c": 23}
982 511 11511511511511511511 {"key_a": 1, "key_b": 2, "key_c": 3}
983 512 12512512512512512512 {"key_a": 21, "key_b": 22, "key_c": 23}
984 521 21521521521521521521 {"key_a": 1, "key_b": 2, "key_c": 3}
985 522 22522522522522522522 {"key_a": 21, "key_b": 22, "key_c": 23}
986 531 31531531531531531531 {"key_a": 1, "key_b": 2, "key_c": 3}
987 532 32532532532532532532 {"key_a": 21, "key_b": 22, "key_c": 23}
988 541 41541541541541541541 {"key_a": 1, "key_b": 2, "key_c": 3}
989 542 42542542542542542542 {"key_a": 21, "key_b": 22, "key_c": 23}
990 551 51551551551551551551 {"key_a": 1, "key_b": 2, "key_c": 3}
991 552 52552552552552552552 {"key_a": 21, "key_b": 22, "key_c": 23}
992 561 61561561561561561561 {"key_a": 1, "key_b": 2, "key_c": 3}
993 562 62562562562562562562 {"key_a": 21, "key_b": 22, "key_c": 23}
994 571 71571571571571571571 {"key_a": 1, "key_b": 2, "key_c": 3}
995 572 72572572572572572572 {"key_a": 21, "key_b": 22, "key_c": 23}
996 581 81581581581581581581 {"key_a": 1, "key_b": 2, "key_c": 3}
997 582 82582582582582582582 {"key_a": 21, "key_b": 22, "key_c": 23}
998 591 91591591591591591591 {"key_a": 1, "key_b": 2, "key_c": 3}
999 592 92592592592592592592 {"key_a": 21, "key_b": 22, "key_c": 23}
1000 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
1001 c2 RIGHT(c3,20) c4
1002 2001 20012001200120012001 {"key_a": 1, "key_b": 2, "key_c": 3}
1003 1992 19921992199219921992 {"key_a": 21, "key_b": 22, "key_c": 23}
1004 1991 19911991199119911991 {"key_a": 1, "key_b": 2, "key_c": 3}
1005 1982 19821982198219821982 {"key_a": 21, "key_b": 22, "key_c": 23}
1006 1981 19811981198119811981 {"key_a": 1, "key_b": 2, "key_c": 3}
1007 1972 19721972197219721972 {"key_a": 21, "key_b": 22, "key_c": 23}
1008 1971 19711971197119711971 {"key_a": 1, "key_b": 2, "key_c": 3}
1009 1962 19621962196219621962 {"key_a": 21, "key_b": 22, "key_c": 23}
1010 1961 19611961196119611961 {"key_a": 1, "key_b": 2, "key_c": 3}
1011 1952 19521952195219521952 {"key_a": 21, "key_b": 22, "key_c": 23}
1012 DROP DATABASE tde_db;
1013 DROP TABLESPACE encrypt_ts;
1014 #########################################################################
1015 # RESTART 4 : WITH KEYRING AND --INNODB_DIRECTORIES
1016 #########################################################################
1017 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'MYSQL_TMP_DIR/encrypt_ts.ibd' ENCRYPTION='Y';
1018 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
1019 NAME ENCRYPTION
1020 encrypt_ts Y
1021 CREATE DATABASE tde_db;
1022 CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
1023 INSERT INTO tde_db.t1 VALUES(0, 'abc');
1024 INSERT INTO tde_db.t1 VALUES(1, 'xyz');
1025 SELECT * FROM tde_db.t1;
1026 c1 c2
1027 0 abc
1028 1 xyz
1029 # Remote tablespace listing
1030 encrypt_ts.ibd
1031 DROP TABLE tde_db.t1;
1032 DROP DATABASE tde_db;
1033 #########################################################################
1034 # Cleanup
1035 #########################################################################
1036 DROP TABLESPACE encrypt_ts;
1037 # ----------------------------------------------------------------------
1038 # Teardown
1039 # Removing manifest file for current MySQL server instance
1040 # Removing local keyring file for keyring component: component_keyring_file
1041 # Removing local configuration file for keyring component: component_keyring_file
1042 # Restarting server without the manifest file
1043 # ----------------------------------------------------------------------