Debian Codegraph @

mysql-test/suite/innodb/t/tablespace_encrypt_1.test

raw
1 ####################################################################################################
2 # InnoDB transparent tablespace data encryption for general shared tablespace.
3 # This test case will test
4 # - Create/Alter general shared table spaces with
5 # - and without loading keyring plugin
6 # - valid/invalid encryption option
7 # - creating table with mix and match encryption option of tablespace
8 # - algorithm specified
9 # - compression specified
10 # - Create table with encryption Y/N when innodb_file_per_table is 0
11 # - Create temporary encrypted table
12 # - Create/Alter a table in encrypted/unencrypted tablespace with encryption='Y/N'
13 # and invalid value
14 # - Create a table in encrypted tablespace without any encryption option
15 # - Altering encryption of system/temporary tablespace
16 # - Altering tablespace with algorithm copy/inplace
17 # - CREATE/DROP FTS index on table in encrypted tablespace
18 # - Moving an encrypted table in system/temporary tablespace
19 # - Displaying encryption property of tablespace in i_s.innodb_tablespaces
20 # - Check no effect of block_encryption_mode = 'aes-256-cbc' variable on tablespace encryption
21 # - Create table in encrypted general tablespace having
22 # - numeric, text, binary, JSON data type, VIRTUAL/GENERATED column
23 # - spatial index
24 # - fulltext index
25 # - Create encrypted general tablespace with
26 # - PK-FK table
27 # - view on encrypted table
28 # - trigger on encrypted table
29 # - fulltext index
30 # - alter encrypted table
31 # - Run query on encrypted table part of user defined tablespace
32 # - from stored procedure
33 # - using transactions
34 # - Create tablespace with encryption Y/N with MyISAM/Memory storage engine
35 # - Create table in general and file_per_table tablespace and check metadata info
36 # - Parsing test of encryption parameter with respect to other tablespace parameters
37 # - Create Encrypted tablespace with different file_block_size
38 # - Renaming encrypted tablespace and checking metadata for tablespace
39 # - Create tables in encrypted tablespace using different ROW_FORMAT
40 # - Create encrypted remote tablespaces
41 ####################################################################################################
42
43 --source include/no_valgrind_without_big.inc
44 # Waiting time when (re)starting the server
45 --let $explicit_default_wait_counter=10000
46
47 --disable_query_log
48 call mtr.add_suppression("ibd can't be decrypted, please confirm that keyring is loaded.");
49 call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring is loaded.");
50 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Check keyring fail, please check the keyring is loaded.");
51 --enable_query_log
52
53 --echo #########################################################################
54 --echo # START : WITHOUT KEYRING PLUGIN
55 --echo #########################################################################
56 # Create a tablespace with encryption, should fail since keyring is not
57 # loaded.
58 --error ER_CANNOT_FIND_KEY_IN_KEYRING
59 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='Y';
60
61 # Create a tablespace without any encryption option. Should succeed.
62 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;
63
64 # Alter tablespace with encryption option. Should fail since keyring is not
65 # loaded.
66 --error ER_CANNOT_FIND_KEY_IN_KEYRING
67 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
68
69 DROP TABLESPACE encrypt_ts;
70
71 --echo #########################################################################
72 --echo # RESTART 1 : WITH KEYRING PLUGIN
73 --echo #########################################################################
74 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
75 --source include/restart_mysqld_no_echo.inc
76
77 --disable_warnings
78 DROP TABLE IF EXISTS t1;
79 --enable_warnings
80
81 --echo ---------------------------
82 --echo SYSTEM TABLESPACE
83 --echo ---------------------------
84 # Test: create table in system tablespace with encryption option.
85 # Should fail, since system tablespace is shared tablespace.
86 --error ER_INVALID_ENCRYPTION_REQUEST
87 CREATE TABLE t1(c int) ENCRYPTION="Y" tablespace innodb_system ENGINE = InnoDB;
88 SHOW WARNINGS;
89
90 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
91
92 SET GLOBAL innodb_file_per_table = 0;
93 SELECT @@innodb_file_per_table;
94 # Test: create table with encryption Y/N when innodb_file_per_table is 0.
95 # create table in system tablespace with encryption 'y' should fail, and
96 # should pass with encryption 'n'.
97 --error ER_INVALID_ENCRYPTION_REQUEST
98 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
99 SHOW WARNINGS;
100 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="N" ENGINE = InnoDB;
101 SHOW WARNINGS;
102 DROP TABLE t1;
103
104 --error ER_INVALID_ENCRYPTION_REQUEST
105 CREATE TABLE t1(c int) ENCRYPTION="Y" tablespace innodb_system ENGINE = InnoDB;
106 SHOW WARNINGS;
107 CREATE TABLE t1(c int) ENCRYPTION="N" tablespace innodb_system ENGINE = InnoDB;
108 SHOW WARNINGS;
109 DROP TABLE t1;
110
111 SET GLOBAL innodb_file_per_table = 1;
112 SELECT @@innodb_file_per_table;
113
114 --error ER_WRONG_TABLESPACE_NAME
115 ALTER TABLESPACE innodb_system ENCRYPTION='Y';
116
117 --echo ---------------------------
118 --echo TEMPORARY TABLESPACE
119 --echo ---------------------------
120 # Test: create temporary encrypted table,
121 # Should fail, since temporary table cannot use ENCRYPTION clause.
122 --error ER_CANNOT_USE_ENCRYPTION_CLAUSE
123 CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="Y";
124 SHOW WARNINGS;
125
126 --error ER_CANNOT_USE_ENCRYPTION_CLAUSE
127 CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="N";
128 SHOW WARNINGS;
129
130 --echo ------------------------------------------------
131 --echo CREATE TABLESPACE WITH INVALID ENCRYPTION OPTION
132 --echo ------------------------------------------------
133 # Create encrypted tablespace with incorrect option.
134 --error ER_INVALID_ENCRYPTION_OPTION
135 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='R';
136 --error ER_PARSE_ERROR
137 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=y;
138 --error ER_PARSE_ERROR
139 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=Y;
140 --error ER_PARSE_ERROR
141 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=n;
142 --error ER_PARSE_ERROR
143 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=N;
144 --error ER_PARSE_ERROR
145 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=1;
146 --error ER_PARSE_ERROR
147 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=0;
148 --error ER_PARSE_ERROR
149 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=null;
150 --error ER_INVALID_ENCRYPTION_OPTION
151 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='TRUE';
152 --error ER_PARSE_ERROR
153 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=TRUE;
154 --error ER_PARSE_ERROR
155 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=True;
156 --error ER_INVALID_ENCRYPTION_OPTION
157 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='True';
158 --error ER_INVALID_ENCRYPTION_OPTION
159 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='true';
160 --error ER_PARSE_ERROR
161 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=true;
162 --error ER_PARSE_ERROR
163 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=FALSE;
164 --error ER_INVALID_ENCRYPTION_OPTION
165 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='FALSE';
166 --error ER_PARSE_ERROR
167 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=False;
168 --error ER_INVALID_ENCRYPTION_OPTION
169 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='False';
170 --error ER_PARSE_ERROR
171 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=false;
172 --error ER_INVALID_ENCRYPTION_OPTION
173 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='false';
174 --error ER_PARSE_ERROR
175 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=-1;
176 --error ER_INVALID_ENCRYPTION_OPTION
177 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='';
178 --error ER_INVALID_ENCRYPTION_OPTION
179 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="";
180
181 --echo ----------------------------------------------------
182 --echo CREATE ENCRYPTED TABLESPACE IN MyISAM STORAGE ENGINE
183 --echo ----------------------------------------------------
184 # Create tablespace with encryption='Y/y/N/n' in MyISAM storage engine.
185 --error ER_ILLEGAL_HA_CREATE_OPTION
186 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='Y';
187 SHOW WARNINGS;
188 --error ER_ILLEGAL_HA_CREATE_OPTION
189 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='y';
190 SHOW WARNINGS;
191 --error ER_ILLEGAL_HA_CREATE_OPTION
192 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='N';
193 SHOW WARNINGS;
194 --error ER_ILLEGAL_HA_CREATE_OPTION
195 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='n';
196 SHOW WARNINGS;
197
198 --echo ----------------------------------------------------
199 --echo CREATE ENCRYPTED TABLESPACE IN MEMORY STORAGE ENGINE
200 --echo ----------------------------------------------------
201 # Create tablespace with encryption='Y/y/N/n' in MyISAM storage engine.
202 --error ER_ILLEGAL_HA_CREATE_OPTION
203 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='Y';
204 SHOW WARNINGS;
205 --error ER_ILLEGAL_HA_CREATE_OPTION
206 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='y';
207 SHOW WARNINGS;
208 --error ER_ILLEGAL_HA_CREATE_OPTION
209 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='N';
210 SHOW WARNINGS;
211 --error ER_ILLEGAL_HA_CREATE_OPTION
212 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='n';
213 SHOW WARNINGS;
214
215 --echo ----------------------------------------------
216 --echo CREATE TABLESPACE WITH VALID ENCRYPTION OPTION
217 --echo ----------------------------------------------
218 # Create tablespace with encryption='Y'.
219 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='Y';
220 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
221 DROP TABLESPACE encrypt_ts;
222 # Create tablespace with encryption='y'.
223 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='y';
224 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
225 DROP TABLESPACE encrypt_ts;
226 # Create tablespace with encryption='n'.
227 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='n';
228 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
229 DROP TABLESPACE encrypt_ts;
230 # Create tablespace with encryption='N'.
231 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='N';
232 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
233 DROP TABLESPACE encrypt_ts;
234 # Create tablespace with encryption="Y".
235 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="Y";
236 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
237 DROP TABLESPACE encrypt_ts;
238 # Create tablespace with encryption="y".
239 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="y";
240 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
241 DROP TABLESPACE encrypt_ts;
242 # Create tablespace with encryption="n".
243 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="n";
244 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
245 DROP TABLESPACE encrypt_ts;
246 # Create tablespace with encryption="N".
247 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="N";
248 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
249 DROP TABLESPACE encrypt_ts;
250 # Create tablespace without encryption option.
251 # By Default it should create unencrypted tablespace
252 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;
253 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
254
255 --echo -----------------------------------------------
256 --echo ALTER TABLESPACE WITH INVALID ENCRYPTION OPTION
257 --echo -----------------------------------------------
258 # Alter tablespace encryption with incorrect option.
259 --error ER_INVALID_ENCRYPTION_OPTION
260 ALTER TABLESPACE encrypt_ts ENCRYPTION='R';
261 --error ER_PARSE_ERROR
262 ALTER TABLESPACE encrypt_ts ENCRYPTION=1;
263 --error ER_PARSE_ERROR
264 ALTER TABLESPACE encrypt_ts ENCRYPTION=0;
265 --error ER_PARSE_ERROR
266 ALTER TABLESPACE encrypt_ts ENCRYPTION=TRUE;
267 --error ER_INVALID_ENCRYPTION_OPTION
268 ALTER TABLESPACE encrypt_ts ENCRYPTION='TRUE';
269 --error ER_PARSE_ERROR
270 ALTER TABLESPACE encrypt_ts ENCRYPTION=True;
271 --error ER_INVALID_ENCRYPTION_OPTION
272 ALTER TABLESPACE encrypt_ts ENCRYPTION='True';
273 --error ER_PARSE_ERROR
274 ALTER TABLESPACE encrypt_ts ENCRYPTION=true;
275 --error ER_INVALID_ENCRYPTION_OPTION
276 ALTER TABLESPACE encrypt_ts ENCRYPTION='true';
277 --error ER_PARSE_ERROR
278 ALTER TABLESPACE encrypt_ts ENCRYPTION=FALSE;
279 --error ER_INVALID_ENCRYPTION_OPTION
280 ALTER TABLESPACE encrypt_ts ENCRYPTION='FALSE';
281 --error ER_PARSE_ERROR
282 ALTER TABLESPACE encrypt_ts ENCRYPTION=False;
283 --error ER_INVALID_ENCRYPTION_OPTION
284 ALTER TABLESPACE encrypt_ts ENCRYPTION='False';
285 --error ER_PARSE_ERROR
286 ALTER TABLESPACE encrypt_ts ENCRYPTION=false;
287 --error ER_INVALID_ENCRYPTION_OPTION
288 ALTER TABLESPACE encrypt_ts ENCRYPTION='false';
289 --error ER_PARSE_ERROR
290 ALTER TABLESPACE encrypt_ts ENCRYPTION=null;
291 --error ER_PARSE_ERROR
292 ALTER TABLESPACE encrypt_ts ENCRYPTION=n;
293 --error ER_PARSE_ERROR
294 ALTER TABLESPACE encrypt_ts ENCRYPTION=N;
295 --error ER_PARSE_ERROR
296 ALTER TABLESPACE encrypt_ts ENCRYPTION=y;
297 --error ER_PARSE_ERROR
298 ALTER TABLESPACE encrypt_ts ENCRYPTION=N;
299 --error ER_PARSE_ERROR
300 ALTER TABLESPACE encrypt_ts ENCRYPTION=-1;
301 --error ER_INVALID_ENCRYPTION_OPTION
302 ALTER TABLESPACE encrypt_ts ENCRYPTION='';
303 --error ER_INVALID_ENCRYPTION_OPTION
304 ALTER TABLESPACE encrypt_ts ENCRYPTION="";
305
306
307 --echo ---------------------------------------------
308 --echo ALTER TABLESPACE WITH VALID ENCRYPTION OPTION
309 --echo ---------------------------------------------
310 # Note: Here we will be testing alter with single and double quotes.
311 # N->N.
312 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
313 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
314 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
315 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
316 # N->n.
317 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
318 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
319 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
320 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
321 # n->n.
322 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
323 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
324 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
325 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
326 # n->N.
327 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
328 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
329 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
330 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
331 # N->Y.
332 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
333 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
334 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
335 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
336 # Y->Y.
337 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
338 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
339 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
340 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
341 # Y->y.
342 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
343 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
344 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
345 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
346 # y->y.
347 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
348 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
349 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
350 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
351 # y->Y.
352 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
353 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
354 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
355 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
356 # Y->n.
357 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
358 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
359 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
360 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
361 # n->y.
362 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
363 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
364 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
365 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
366 # y->N.
367 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
368 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
369 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
370 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
371 # N->y.
372 ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
373 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
374 ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
375 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
376 # y->n.
377 ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
378 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
379 ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
380 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
381 # n->Y.
382 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
383 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
384 ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
385 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
386 # Y->N.
387 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
388 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
389 ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
390 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
391
392 --echo -----------------------------------------
393 --echo CREATE/ALTER TABLE in FILE-PER-TABLE TABLESPACE
394 --echo -----------------------------------------
395 # Create/Alter an encrypted table without specifying tablespace and check the metadata
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 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
399 SHOW CREATE TABLE test.t1;
400 ALTER TABLE t1 encryption="N";
401 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
402 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
403 SHOW CREATE TABLE test.t1;
404 DROP TABLE t1;
405 # Create/Alter an Unencrypted table without specifying tablespace and check the metadata
406 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="N" ENGINE = InnoDB;
407 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
408 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
409 SHOW CREATE TABLE test.t1;
410 ALTER TABLE t1 encryption="Y";
411 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
412 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
413 SHOW CREATE TABLE test.t1;
414 DROP TABLE t1;
415 # Create/Alter an encrypted table with specifying tablespace as innodb_file_per_table and check
416 # the metadata
417 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=innodb_file_per_table ENCRYPTION="Y" ENGINE = InnoDB;
418 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
419 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
420 SHOW CREATE TABLE test.t1;
421 ALTER TABLE t1 encryption="N";
422 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
423 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
424 SHOW CREATE TABLE test.t1;
425 DROP TABLE t1;
426 # Create/alter an Unencrypted table with specifying tablespace as innodb_file_per_table and check
427 # the metadata
428 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=innodb_file_per_table ENCRYPTION="N" ENGINE = InnoDB;
429 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
430 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
431 SHOW CREATE TABLE test.t1;
432 ALTER TABLE t1 encryption="Y";
433 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
434 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
435 SHOW CREATE TABLE test.t1;
436 DROP TABLE t1;
437 # Create a table in Unencrypted tablespace and check the table metadata
438 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=encrypt_ts ENGINE = InnoDB;
439 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
440 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
441 SHOW CREATE TABLE test.t1;
442 DROP TABLE t1;
443 # Create a table in encrypted tablespace check the table metadata
444 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
445 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
446 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
447 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
448 SHOW CREATE TABLE test.t1;
449 DROP TABLE t1;
450
451 --echo --------------------------
452 --echo CREATE/ALTER TABLE IN GENERAL TABLESPACE
453 --echo --------------------------
454 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="Y" ENGINE = InnoDB;
455 DROP TABLE t1;
456 --error ER_INVALID_ENCRYPTION_REQUEST
457 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="N" ENGINE = InnoDB;
458 SHOW WARNINGS;
459 --error ER_INVALID_ENCRYPTION_OPTION
460 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="R" ENGINE = InnoDB;
461 SHOW WARNINGS;
462
463 # Alter table in encrypted tablespace with encryption='Y/N' and invalid value
464 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
465 ALTER TABLE t1 encryption='Y';
466 --error ER_INVALID_ENCRYPTION_REQUEST
467 ALTER TABLE t1 encryption='N';
468 --error ER_INVALID_ENCRYPTION_OPTION
469 ALTER TABLE t1 encryption='R';
470 DROP TABLE t1;
471
472 # Y->N
473 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
474
475 # Create a table in unencrypted tablespace with encryption='Y/N' and invalid value
476 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="N" ENGINE = InnoDB;
477 DROP TABLE t1;
478 --error ER_INVALID_ENCRYPTION_REQUEST
479 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="Y" ENGINE = InnoDB;
480 SHOW WARNINGS;
481 --error ER_INVALID_ENCRYPTION_REQUEST
482 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="R" ENGINE = InnoDB;
483 SHOW WARNINGS;
484
485 # Alter table in unencrypted tablespace with encryption='Y/N' and invalid value
486 CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENGINE = InnoDB;
487 --error ER_INVALID_ENCRYPTION_REQUEST
488 ALTER TABLE t1 encryption='Y';
489 ALTER TABLE t1 encryption='N';
490 --error ER_INVALID_ENCRYPTION_REQUEST
491 ALTER TABLE t1 encryption='R';
492 DROP TABLE t1;
493
494 # N->Y
495 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
496
497 # Create a table in encrypted tablespace without any encryption option. It should
498 # inherit tablespace encryption i.e. should be created encrypted.
499 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
500 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
501
502 SHOW CREATE TABLE t1;
503 INSERT INTO t1 VALUES(0, "aaaaa");
504 INSERT INTO t1 VALUES(1, "bbbbb");
505 INSERT INTO t1 VALUES(2, "ccccc");
506 INSERT INTO t1 VALUES(3, "ddddd");
507 INSERT INTO t1 VALUES(4, "eeeee");
508 INSERT INTO t1 VALUES(5, "fffff");
509 INSERT INTO t1 VALUES(6, "ggggg");
510 INSERT INTO t1 VALUES(7, "hhhhh");
511 INSERT INTO t1 VALUES(8, "iiiii");
512 INSERT INTO t1 VALUES(9, "jjjjj");
513 INSERT INTO t1 select * from t1;
514 INSERT INTO t1 select * from t1;
515 INSERT INTO t1 select * from t1;
516 INSERT INTO t1 select * from t1;
517 INSERT INTO t1 select * from t1;
518 INSERT INTO t1 select * from t1;
519
520 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
521
522 --echo #########################################################################
523 --echo # RESTART 2 : WITHOUT KEYRING PLUGIN
524 --echo #########################################################################
525 let $restart_parameters = restart:;
526 -- source include/restart_mysqld_no_echo.inc
527
528 # Try to access table in encrypted tablespace
529 --error ER_CANNOT_FIND_KEY_IN_KEYRING
530 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
531
532 --echo #########################################################################
533 --echo # RESTART 3 : WITH KEYRING PLUGIN
534 --echo #########################################################################
535 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
536 --source include/restart_mysqld_no_echo.inc
537
538 # Confirm the encryption info can be retrieved properly and table can be read.
539 SELECT * FROM t1 ORDER BY c1 LIMIT 10;
540
541 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
542
543 # CREATE/DROP FTS index on table
544 DROP TABLE t1;
545 CREATE TABLE t1(c2 char(100) , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB
546 tablespace=encrypt_ts ENCRYPTION='Y';
547 ALTER TABLE t1 DROP INDEX idx1;
548 ALTER TABLE t1 ADD COLUMN c4 CHAR(20);
549 DROP TABLE t1;
550
551 # Test: CREATE/ALTER tablespace with ALGORITHM
552 # ALGORITHM syntax for ALTER tablespace is not supported. Its always INPLACE
553 --echo ----------------------
554 --echo ALGORITHM=COPY/INPLACE
555 --echo ----------------------
556 --error ER_PARSE_ERROR
557 ALTER TABLESPACE encrypt_ts ENCRYPTION="N", algorithm=copy;
558 --error ER_PARSE_ERROR
559 ALTER TABLESPACE encrypt_ts ENCRYPTION="N", algorithm=inplace;
560 DROP TABLESPACE encrypt_ts;
561 --error ER_PARSE_ERROR
562 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION="N", algorithm=copy;
563 --error ER_PARSE_ERROR
564 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION="N", algorithm=inplace;
565
566 # Test: ENCRYPTION with COMPRESSION on tablespace
567 # COMPRESSION syntax for CREATE/ALTER tablespace is not supported
568 --echo -----------
569 --echo COMPRESSION
570 --echo -----------
571 --error ER_PARSE_ERROR
572 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' COMPRESSION = "ZLIB" ENCRYPTION = "Y" ENGINE = InnoDB;
573 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION = "Y" ENGINE = InnoDB;
574 --error ER_PARSE_ERROR
575 ALTER TABLESPACE encrypt_ts COMPRESSION = "ZLIB";
576 DROP TABLESPACE encrypt_ts;
577
578 # Create ENCRYPTED tablespace with different FILE_BLOCK_SIZE
579 CREATE TABLESPACE encryt_ts_1k ADD DATAFILE 'encrypt_ts_1k.ibd' FILE_BLOCK_SIZE=1k ENCRYPTION='Y';
580 CREATE TABLESPACE encryt_ts_2k ADD DATAFILE 'encrypt_ts_2k.ibd' FILE_BLOCK_SIZE=2k ENCRYPTION='Y';
581 CREATE TABLESPACE encryt_ts_4k ADD DATAFILE 'encrypt_ts_4k.ibd' FILE_BLOCK_SIZE=4k ENCRYPTION='Y';
582 CREATE TABLESPACE encryt_ts_8k ADD DATAFILE 'encrypt_ts_8k.ibd' FILE_BLOCK_SIZE=8k ENCRYPTION='Y';
583 CREATE TABLESPACE encryt_ts_16k ADD DATAFILE 'encrypt_ts_16k.ibd' FILE_BLOCK_SIZE=16k ENCRYPTION='Y';
584 # Compressed tablespace not supported with innodb_page_size > 16K
585 --error ER_ILLEGAL_HA_CREATE_OPTION
586 CREATE TABLESPACE encryt_ts_32k ADD DATAFILE 'encrypt_ts_32k.ibd' FILE_BLOCK_SIZE=32k ENCRYPTION='Y';
587 SHOW WARNINGS;
588 DROP tablespace encryt_ts_1k;
589 DROP tablespace encryt_ts_2k;
590 DROP tablespace encryt_ts_4k;
591 DROP tablespace encryt_ts_8k;
592 DROP tablespace encryt_ts_16k;
593
594 # Parsing test of ENCRYPTION parameter with respect to other tablespace parameters
595 --error ER_PARSE_ERROR
596 CREATE TABLESPACE encrypt_ts ENCRYPTION='Y' ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M
597 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
598
599 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION='Y' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M
600 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
601 DROP TABLESPACE encrypt_ts;
602
603 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y' AUTOEXTEND_SIZE = 12M
604 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
605 DROP TABLESPACE encrypt_ts;
606
607 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M ENCRYPTION='Y'
608 MAX_SIZE = 100M NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
609 DROP TABLESPACE encrypt_ts;
610
611 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
612 ENCRYPTION='Y' NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
613 DROP TABLESPACE encrypt_ts;
614
615 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
616 NODEGROUP = 5 ENCRYPTION='Y' WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
617 DROP TABLESPACE encrypt_ts;
618
619 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
620 NODEGROUP = 5 WAIT ENCRYPTION='Y' COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
621 DROP TABLESPACE encrypt_ts;
622
623 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
624 NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' ENCRYPTION='Y' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
625 DROP TABLESPACE encrypt_ts;
626
627 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
628 NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M ENCRYPTION='Y'EXTENT_SIZE = 100M;
629 DROP TABLESPACE encrypt_ts;
630
631 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 12M MAX_SIZE = 100M
632 NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M ENCRYPTION='Y';
633 DROP TABLESPACE encrypt_ts;
634
635 # RENAME encrypted tablespace and checking metadata for tablespace
636 # and table
637 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
638 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
639 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
640 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
641 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
642 SHOW CREATE TABLE test.t1;
643
644 ALTER TABLESPACE encrypt_ts RENAME TO encrypt_ts_renamed;
645 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts_renamed';
646 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
647 SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
648 SHOW CREATE TABLE test.t1;
649
650 DROP TABLE t1;
651 DROP TABLESPACE encrypt_ts_renamed;
652
653
654 # CREATE tables in encrypted tablespace using different ROW_FORMAT
655 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
656 # Create Insert Procedure
657 DELIMITER |;
658 CREATE PROCEDURE populate_t1()
659 BEGIN
660 DECLARE i int DEFAULT 1;
661 START TRANSACTION;
662 WHILE (i <= 10) DO
663 INSERT INTO t1 VALUES (i,CONCAT('a', i));
664 SET i = i + 1;
665 END WHILE;
666 COMMIT;
667 END|
668 DELIMITER ;|
669 --error ER_ILLEGAL_HA_CREATE_OPTION
670 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=COMPRESSED;
671 SHOW WARNINGS;
672
673 --error ER_ILLEGAL_HA
674 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=FIXED;
675
676 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=DYNAMIC;
677 -- disable_query_log
678 CALL populate_t1();
679 -- enable_query_log
680 DROP TABLE t1;
681
682 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=REDUNDANT;
683 -- disable_query_log
684 CALL populate_t1();
685 -- enable_query_log
686 DROP TABLE t1;
687
688 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=COMPACT;
689 -- disable_query_log
690 CALL populate_t1();
691 -- enable_query_log
692 DROP TABLE t1;
693 DROP PROCEDURE populate_t1;
694
695 # CREATE table in encrypted tablespace using KEY_BLOCK_SIZE
696 --error ER_ILLEGAL_HA_CREATE_OPTION
697 CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' KEY_BLOCK_SIZE=1;
698 SHOW WARNINGS;
699 DROP TABLESPACE encrypt_ts;
700
701 # Check no effect of BLOCK_ENCRYPTION_MODE = 'aes-256-cbc' variable on tablespace encryption
702 SET block_encryption_mode = 'aes-256-cbc';
703 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
704 # Test encryption .
705 --disable_warnings
706 DROP DATABASE IF EXISTS tde_db;
707 CREATE DATABASE tde_db;
708 CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
709 --enable_warnings
710 #
711 INSERT INTO tde_db.t1 VALUES(0, 'abc');
712 INSERT INTO tde_db.t1 VALUES(1, 'xyz');
713 INSERT INTO tde_db.t1 VALUES(2, null);
714 INSERT INTO tde_db.t1 VALUES(3, null);
715 SELECT * FROM tde_db.t1 LIMIT 10;
716 ALTER INSTANCE ROTATE INNODB MASTER KEY;
717 SELECT * FROM tde_db.t1 LIMIT 10;
718 DROP DATABASE tde_db;
719 DROP TABLESPACE encrypt_ts;
720
721 # Encrypted general tablespace having table
722 # 1) With JSON , VIRTUAL/GENERATED column
723 # 2) With rtree ( spatial index)
724
725 --disable_warnings
726 DROP DATABASE IF EXISTS tde_db;
727 CREATE DATABASE tde_db;
728 --enable_warnings
729 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION ='Y' ENGINE=InnoDB;
730 CREATE TABLE tde_db.t_encrypt(c4 JSON ,
731 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
732 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
733 c7 POINT NOT NULL SRID 0,
734 spatial INDEX idx2 (c7)
735 ) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
736
737 SHOW CREATE TABLE tde_db.t_encrypt;
738 INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
739 INSERT INTO tde_db.t_encrypt(c4,c7) select c4,c7 from tde_db.t_encrypt;
740
741 SELECT c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
742 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
743 SHOW CREATE TABLE tde_db.t_encrypt;
744
745
746 # Encrypted general tablespace with
747 # - PK-FK table
748 # - view on encrypted table
749 # - trigger on encrypted table
750 # - fulltext index
751 # - alter encrypted table
752 --disable_warnings
753
754 DROP TABLE IF EXISTS tde_db.t_encrypt;
755 --enable_warnings
756
757 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';
758 CREATE TABLE tde_db.t_encrypt1(c11 INT , c22 char(100), c33 BLOB , FULLTEXT INDEX `idx1` (c22)) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
759 SHOW CREATE TABLE tde_db.t_encrypt;
760 SHOW CREATE TABLE tde_db.t_encrypt1;
761
762 CREATE TABLE tde_db.t_encrypt2 (f1 INT PRIMARY KEY, f2 CHAR(100),
763 FOREIGN KEY (f1) REFERENCES tde_db.t_encrypt(c1) ON UPDATE CASCADE) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
764
765
766 DELIMITER |;
767 CREATE TRIGGER tde_db.trigger_encrypt_table AFTER INSERT ON tde_db.t_encrypt
768 FOR EACH ROW
769 begin
770 INSERT INTO tde_db.t_encrypt1 SET c11 = NEW.c1*-1, c22 = NEW.c2 , c33 = NEW.c3;
771 end|
772 DELIMITER ;|
773
774 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("transparent tablespace encryption",repeat('A', 200));
775 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("general tablespace option",repeat('A', 200));
776 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("page level encryption",repeat('A', 200));
777
778
779 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(1,"transparent tablespace encryption");
780 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
781
782 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
783
784 # fulltext query
785 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace');
786 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace' IN BOOLEAN MODE);
787 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('+tablespace -encryption' IN BOOLEAN MODE);
788 ALTER TABLE tde_db.t_encrypt DROP INDEX idx1;
789 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
790 ALTER TABLE tde_db.t_encrypt ADD COLUMN c4 CHAR(20) DEFAULT 'text';
791 SELECT c1,c2,right(c3, 20),c4 FROM tde_db.t_encrypt LIMIT 10;
792
793 # check view
794 CREATE VIEW tde_db.t_encrypt_view AS SELECT c1,c2 FROM tde_db.t_encrypt;
795 SELECT c2 FROM tde_db.t_encrypt_view LIMIT 10;
796 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;
797 DROP VIEW tde_db.t_encrypt_view;
798
799 # Check triggr table
800 SELECT c11,c22,right(c33, 20) FROM tde_db.t_encrypt1 LIMIT 10;
801
802 # check PK-FK , ON UPDATE CASCADE
803 # Duplicate key
804 --ERROR ER_DUP_ENTRY
805 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
806 # No entry in PK
807 --ERROR 1452
808 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(8,"general tablespace option");
809 SELECT f1,f2 FROM tde_db.t_encrypt2;
810 UPDATE tde_db.t_encrypt SET c1=10 WHERE c1=1;
811 SELECT f1,f2 FROM tde_db.t_encrypt2;
812 DROP DATABASE tde_db;
813
814 # Run query on encrypted table part of user defined tablespace
815 # from stored procedure
816 # Using transactions
817 --disable_warnings
818 DROP DATABASE IF EXISTS tde_db;
819 CREATE DATABASE tde_db;
820 USE tde_db;
821 DROP TABLE IF EXISTS tde_db.t_encrypt;
822 #
823 --enable_warnings
824 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';
825
826 DELIMITER |;
827 CREATE PROCEDURE tde_db.txn_t_encrypt()
828 BEGIN
829 declare i int default 0;
830 declare rowcnt int default 0;
831 START TRANSACTION;
832 WHILE (i <= 2000) DO
833
834 SET i = i + 1;
835 SET rowcnt = rowcnt + 1;
836
837 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)'));
838
839
840 IF (rowcnt = 3) THEN
841 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 21, "key_b": 22, "key_c": 23 }' WHERE c2 = i-1 ;
842 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
843 SAVEPOINT A;
844 END IF;
845 IF (rowcnt = 5) THEN
846 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 41, "key_b": 42, "key_c": 43 }' WHERE c2 = i-1 ;
847 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
848 SAVEPOINT B;
849 END IF;
850 IF (rowcnt = 10) THEN
851 ROLLBACK TO SAVEPOINT A;
852 COMMIT;
853 SET rowcnt = 0;
854 START TRANSACTION;
855 END IF;
856
857
858 END WHILE;
859 COMMIT;
860 end|
861 DELIMITER ;|
862
863 call tde_db.txn_t_encrypt();
864 SELECT COUNT(*) FROM tde_db.t_encrypt;
865 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
866 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
867 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
868 DROP DATABASE tde_db;
869 DROP TABLESPACE encrypt_ts;
870
871 --echo #########################################################################
872 --echo # RESTART 4 : WITH KEYRING PLUGIN AND --INNODB_DIRECTORIES
873 --echo #########################################################################
874 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT --innodb-directories=$MYSQL_TMP_DIR;
875 --source include/restart_mysqld_no_echo.inc
876
877 # Check encrypted tablespace is getting created in remote directory(outside of datadir)
878 # and data is getting accessed
879 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
880 eval CREATE TABLESPACE encrypt_ts ADD DATAFILE '$MYSQL_TMP_DIR/encrypt_ts.ibd' ENCRYPTION='Y';
881 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
882 CREATE DATABASE tde_db;
883 CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
884 INSERT INTO tde_db.t1 VALUES(0, 'abc');
885 INSERT INTO tde_db.t1 VALUES(1, 'xyz');
886 SELECT * FROM tde_db.t1;
887 --echo # Remote tablespace listing
888 --list_files $MYSQL_TMP_DIR/ *.ibd
889
890 DROP TABLE tde_db.t1;
891 DROP DATABASE tde_db;
892
893 --echo #########################################################################
894 --echo # Cleanup
895 --echo #########################################################################
896 DROP TABLESPACE encrypt_ts;
897 remove_file $MYSQL_TMP_DIR/mysecret_keyring;
898 remove_file $MYSQL_TMP_DIR/mysecret_keyring.backup;
899
900 --echo # Restarting server without keyring to restore server state
901 let $restart_parameters = restart: ;
902 --source include/restart_mysqld.inc