Debian Codegraph @

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

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