Debian Codegraph @

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

raw
1 ################################################################################
2 # InnoDB transparent tablespace data encryption for mysql.tablespace.
3 # For mysql.tablespace, this test will test
4 # 1 - Alter mysql.tablespace with
5 # - and without loading keyring plugin
6 # - valid/invalid encryption option
7 # -ENCRYPTION='n' to ENCRYPTION='y'
8 # 2 - Actual encryption taking place or not
9 # 3 - Create/Alter table using mysql.tablespace
10 # 4 - Alter table part of innodb_file_per_table/general/innodb_system/mysql ts to mysql ts
11 # 5 - Create view using mysql.tablespace
12 # 6 - Delete/truncate/drop DD table part of mysql tablespace
13 # 7 - Changing encryption attribute of tables in mysql tablespace
14 # 8 - Restart with same keyring plugin option and access mysql tables
15 # 9 - Restart without keyring plugin option, Install plugin explicitly and alter encryption to Y
16 # 10 - Monitor progress of encryption in performance_schema table
17 # 11 - Rename/drop mysql tablespace
18 # 12 - Create tablespace with name as mysql
19 ################################################################################
20
21 --source include/big_test.inc
22 --source include/have_debug.inc
23 # Disable in valgrind because of timeout, cf. Bug#22760145
24 --source include/not_valgrind.inc
25 --disable_query_log
26 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Check keyring fail, please check the keyring is loaded.");
27 call mtr.add_suppression("\\[ERROR\\].*Plugin keyring_file reported: 'keyring_file initialization failure.");
28 call mtr.add_suppression("\\[ERROR\\].*Plugin keyring_file reported: 'File .*keyring' not found .*");
29 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring is loaded.");
30 --enable_query_log
31
32 --echo #########
33 --echo # SETUP #
34 --echo #########
35 --echo
36
37 let datadir=`SELECT @@datadir`;
38 let search_pattern=mysql.plugin;
39 let ts_name=mysql.ibd;
40 --echo #########################################################################
41 --echo # START : WITHOUT KEYRING PLUGIN
42 --echo #########################################################################
43 # Alter mysql.tablespace with encryption, should fail since keyring is not
44 # loaded.
45
46 --error ER_CANNOT_FIND_KEY_IN_KEYRING
47 ALTER TABLESPACE mysql ENCRYPTION='Y';
48
49 --error ER_CANNOT_FIND_KEY_IN_KEYRING
50 ALTER TABLESPACE mysql ENCRYPTION='N';
51
52 --echo #########################################################################
53 --echo # RESTART 1 : WITH KEYRING PLUGIN
54 --echo #########################################################################
55 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysql_ts_keyring $KEYRING_PLUGIN_OPT;
56 --source include/restart_mysqld_no_echo.inc
57
58 --echo --------------------------------------------------
59 --echo By Default, mysql tablespace should be unencrypted
60 --echo --------------------------------------------------
61 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
62 # mysql.ibd file should be actually unencrypted
63 --source include/if_encrypted.inc
64
65 ALTER TABLESPACE mysql ENCRYPTION='Y';
66 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
67 # mysql.ibd file should be actually encrypted
68 --source include/if_encrypted.inc
69
70 --echo -----------------------------------------------------
71 --echo ALTER mysql TABLESPACE WITH INVALID ENCRYPTION OPTION
72 --echo -----------------------------------------------------
73 --error ER_INVALID_ENCRYPTION_OPTION
74 ALTER TABLESPACE mysql ENCRYPTION='R';
75 --error ER_INVALID_ENCRYPTION_OPTION
76 ALTER TABLESPACE mysql ENCRYPTION='TRUE';
77 --error ER_INVALID_ENCRYPTION_OPTION
78 ALTER TABLESPACE mysql ENCRYPTION='True';
79 --error ER_INVALID_ENCRYPTION_OPTION
80 ALTER TABLESPACE mysql ENCRYPTION='true';
81 --error ER_PARSE_ERROR
82 ALTER TABLESPACE mysql ENCRYPTION=TRUE;
83 --error ER_PARSE_ERROR
84 ALTER TABLESPACE mysql ENCRYPTION=True;
85 --error ER_PARSE_ERROR
86 ALTER TABLESPACE mysql ENCRYPTION=true;
87 --error ER_INVALID_ENCRYPTION_OPTION
88 ALTER TABLESPACE mysql ENCRYPTION='FALSE';
89 --error ER_INVALID_ENCRYPTION_OPTION
90 ALTER TABLESPACE mysql ENCRYPTION='False';
91 --error ER_INVALID_ENCRYPTION_OPTION
92 ALTER TABLESPACE mysql ENCRYPTION='false';
93 --error ER_PARSE_ERROR
94 ALTER TABLESPACE mysql ENCRYPTION=FALSE;
95 --error ER_PARSE_ERROR
96 ALTER TABLESPACE mysql ENCRYPTION=False;
97 --error ER_PARSE_ERROR
98 ALTER TABLESPACE mysql ENCRYPTION=false;
99 --error ER_PARSE_ERROR
100 ALTER TABLESPACE mysql ENCRYPTION=0;
101 --error ER_PARSE_ERROR
102 ALTER TABLESPACE mysql ENCRYPTION=1;
103 --error ER_PARSE_ERROR
104 ALTER TABLESPACE mysql ENCRYPTION=null;
105 --error ER_PARSE_ERROR
106 ALTER TABLESPACE mysql ENCRYPTION=-1;
107 --error ER_PARSE_ERROR
108 ALTER TABLESPACE mysql ENCRYPTION=n;
109 --error ER_PARSE_ERROR
110 ALTER TABLESPACE mysql ENCRYPTION=N;
111 --error ER_PARSE_ERROR
112 ALTER TABLESPACE mysql ENCRYPTION=y;
113 --error ER_PARSE_ERROR
114 ALTER TABLESPACE mysql ENCRYPTION=Y;
115 --error ER_INVALID_ENCRYPTION_OPTION
116 ALTER TABLESPACE mysql ENCRYPTION='1';
117 --error ER_INVALID_ENCRYPTION_OPTION
118 ALTER TABLESPACE mysql ENCRYPTION='1True';
119 --error ER_INVALID_ENCRYPTION_OPTION
120 ALTER TABLESPACE mysql ENCRYPTION='@';
121 --error ER_INVALID_ENCRYPTION_OPTION
122 ALTER TABLESPACE mysql ENCRYPTION='null';
123 --error ER_INVALID_ENCRYPTION_OPTION
124 ALTER TABLESPACE mysql ENCRYPTION='';
125 --error ER_INVALID_ENCRYPTION_OPTION
126 ALTER TABLESPACE mysql ENCRYPTION="";
127
128 --echo ----------------------------------------------------
129 --echo ALTER MYSQL TABLESPACE WITH VALID ENCRYPTION OPTION
130 --echo ----------------------------------------------------
131 # ALTER mysql TABLESPACE WITH ENCRYPTION='Y'.
132 ALTER TABLESPACE mysql ENCRYPTION='Y';
133 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
134
135 # ALTER mysql TABLESPACE WITH ENCRYPTION='y'.
136 ALTER TABLESPACE mysql ENCRYPTION='y';
137 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
138
139 # ALTER mysql TABLESPACE WITH ENCRYPTION='n'.
140 ALTER TABLESPACE mysql ENCRYPTION='n';
141 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
142
143 # ALTER mysql TABLESPACE WITH ENCRYPTION='N'.
144 ALTER TABLESPACE mysql ENCRYPTION='N';
145 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
146
147 #Below SQL uses double quotes
148 # ALTER mysql TABLESPACE WITH ENCRYPTION="Y".
149 ALTER TABLESPACE mysql ENCRYPTION="Y";
150 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
151
152 # ALTER mysql TABLESPACE WITH ENCRYPTION="y".
153 ALTER TABLESPACE mysql ENCRYPTION="y";
154 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
155
156 # ALTER mysql TABLESPACE WITH ENCRYPTION="n".
157 ALTER TABLESPACE mysql ENCRYPTION="n";
158 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
159
160 # ALTER mysql TABLESPACE WITH ENCRYPTION="N".
161 ALTER TABLESPACE mysql ENCRYPTION="N";
162 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
163
164
165 --echo -----------------------------------------------------
166 --echo Create/Alter table using mysql.tablespace
167 --echo -----------------------------------------------------
168 # This should fail as mysql is a reserved tablespace
169 --error ER_RESERVED_TABLESPACE_NAME
170 CREATE TABLE t1(i int) TABLESPACE mysql;
171 --error ER_RESERVED_TABLESPACE_NAME
172 CREATE TEMPORARY TABLE t1(i int) TABLESPACE mysql;
173
174 # Alter table part of innodb_file_per_table ts to mysql ts
175 CREATE TABLE t1(i int);
176 --error ER_RESERVED_TABLESPACE_NAME
177 ALTER TABLE t1 TABLESPACE mysql;
178 DROP TABLE t1;
179
180 # Alter table part of general ts to mysql ts
181 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;
182 CREATE TABLE t1(i int) TABLESPACE encrypt_ts;
183 --error ER_RESERVED_TABLESPACE_NAME
184 ALTER TABLE t1 TABLESPACE mysql;
185 DROP TABLE t1;
186
187 # Alter table part of innodb_system to mysql ts
188 CREATE TABLE t1(i int) TABLESPACE innodb_system;
189 --error ER_RESERVED_TABLESPACE_NAME
190 ALTER TABLE t1 TABLESPACE mysql;
191 DROP TABLE t1;
192
193 # Alter table part of mysql ts to mysql ts
194 # This should be successful
195 ALTER TABLE mysql.component TABLESPACE mysql;
196
197 # Alter temporary table to mysql ts
198 CREATE TEMPORARY TABLE t1(i int);
199 --error ER_RESERVED_TABLESPACE_NAME
200 ALTER TABLE t1 TABLESPACE mysql;
201 DROP TABLE t1;
202
203 --echo -----------------------------------------------------
204 --echo Create view using mysql.tablespace
205 --echo -----------------------------------------------------
206 # This should throw parse error as view does not support tablespace attribute
207 CREATE TABLE t1(i int);
208 INSERT INTO t1 VALUES(1);
209 INSERT INTO t1 SELECT * FROM t1;
210 INSERT INTO t1 SELECT * FROM t1;
211 INSERT INTO t1 SELECT * FROM t1;
212 INSERT INTO t1 SELECT * FROM t1;
213 --error ER_PARSE_ERROR
214 CREATE VIEW v1 AS SELECT * FROM t1 TABLESPACE mysql;
215 DROP TABLE t1;
216
217 --echo ------------------------------------------------------------
218 --echo Alter DD table part of mysql.tablespace to other tablespaces
219 --echo ------------------------------------------------------------
220 # This should fail as access to DD table is not allowed
221 --error ER_NO_SYSTEM_TABLE_ACCESS
222 ALTER TABLE mysql.events TABLESPACE innodb_file_per_table ENCRYPTION='Y';
223 --error ER_NO_SYSTEM_TABLE_ACCESS
224 ALTER TABLE mysql.events TABLESPACE innodb_file_per_table;
225 --error ER_NO_SYSTEM_TABLE_ACCESS
226 ALTER TABLE mysql.events TABLESPACE innodb_temporary ENCRYPTION='Y';
227 --error ER_NO_SYSTEM_TABLE_ACCESS
228 ALTER TABLE mysql.events TABLESPACE innodb_temporary;
229 --error ER_NO_SYSTEM_TABLE_ACCESS
230 ALTER TABLE mysql.events TABLESPACE innodb_system ENCRYPTION='Y';
231 --error ER_NO_SYSTEM_TABLE_ACCESS
232 ALTER TABLE mysql.events TABLESPACE innodb_system;
233 --error ER_NO_SYSTEM_TABLE_ACCESS
234 ALTER TABLE mysql.events TABLESPACE encrypt_ts ENCRYPTION='Y';
235 --error ER_NO_SYSTEM_TABLE_ACCESS
236 ALTER TABLE mysql.events TABLESPACE encrypt_ts;
237 DROP TABLESPACE encrypt_ts;
238
239 --echo -------------------------------------------------------
240 --echo Delete/truncate/drop DD table part of mysql tablespace
241 --echo -------------------------------------------------------
242 # Access should be denied
243 --error ER_NO_SYSTEM_TABLE_ACCESS
244 DELETE FROM mysql.events;
245 --error ER_NO_SYSTEM_TABLE_ACCESS
246 TRUNCATE TABLE mysql.events;
247 --error ER_NO_SYSTEM_TABLE_ACCESS
248 DROP TABLE mysql.events;
249
250 --echo --------------------------------------------------
251 --echo Alter encryption of table part of mysql tablespace
252 --echo --------------------------------------------------
253 # Changing encryption at table level should not be permitted
254 --error ER_INVALID_ENCRYPTION_REQUEST
255 ALTER TABLE mysql.component ENCRYPTION='Y';
256 --error ER_INVALID_ENCRYPTION_REQUEST
257 ALTER TABLE mysql.component ENCRYPTION='y';
258 ALTER TABLE mysql.component ENCRYPTION='N';
259 ALTER TABLE mysql.component ENCRYPTION='n';
260
261 --echo ---------------------------------------------------------------------
262 --echo Metadata for a table in mysql ts should not show encryption attribute
263 --echo ---------------------------------------------------------------------
264 SHOW CREATE TABLE mysql.plugin;
265
266
267 --echo -------------------
268 --echo Drop mysql database
269 --echo -------------------
270 --error ER_NO_SYSTEM_SCHEMA_ACCESS
271 DROP DATABASE mysql;
272
273 --echo -----------------------------------------------------
274 --echo Other DDL operation not allowed on 'mysql' tablespace
275 --echo -----------------------------------------------------
276 --error ER_WRONG_TABLESPACE_NAME
277 CREATE TABLESPACE mysql ADD DATAFILE 'mysql.ibd' ENGINE=INNODB;
278
279 --error ER_WRONG_TABLESPACE_NAME
280 DROP TABLESPACE mysql;
281
282 --error ER_WRONG_TABLESPACE_NAME
283 ALTER TABLESPACE mysql RENAME TO xyz;
284
285 --error ER_TABLESPACE_ENGINE_MISMATCH
286 ALTER TABLESPACE mysql ENGINE=myisam;
287
288 --error ER_TABLESPACE_ENGINE_MISMATCH
289 ALTER TABLESPACE mysql ENGINE=memory;
290
291 --echo #########################################################################
292 --echo # Restart with same keyring plugin option
293 --echo # - tables in mysql ts should be accessible
294 --echo #########################################################################
295 ALTER TABLESPACE mysql ENCRYPTION='Y';
296 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysql_ts_keyring $KEYRING_PLUGIN_OPT;
297 --source include/restart_mysqld_no_echo.inc
298 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
299 SELECT help_keyword_id FROM mysql.help_keyword ORDER BY help_keyword_id LIMIT 2;
300
301
302 ALTER TABLESPACE mysql ENCRYPTION='N';
303 --echo #########################################################################
304 --echo # Restart without keyring plugin option
305 --echo # - Install plugin explicitly and alter encryption to Y
306 --echo #########################################################################
307 let $restart_parameters = restart: ;
308 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
309 --source include/restart_mysqld.inc
310 --disable_warnings
311 --replace_regex /\.dll/.so/
312 eval INSTALL PLUGIN keyring_file SONAME '$KEYRING_PLUGIN';
313 --enable_warnings
314 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
315 eval SET @@global.keyring_file_data='$MYSQL_TMP_DIR/mysql_ts_keyring';
316 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
317 SELECT @@global.keyring_file_data;
318 ALTER TABLESPACE mysql ENCRYPTION='Y';
319 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
320 --replace_regex /\.dll/.so/
321 UNINSTALL PLUGIN keyring_file;
322 --error ER_CANNOT_FIND_KEY_IN_KEYRING
323 ALTER INSTANCE ROTATE INNODB MASTER KEY;
324
325 --echo #########################################################################
326 --echo # Restart with keyring plugin
327 --echo # - monitor progress of encryption in performance_schema table
328 --echo #########################################################################
329 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysql_ts_keyring $KEYRING_PLUGIN_OPT;
330 --source include/restart_mysqld_no_echo.inc
331 ALTER TABLESPACE mysql ENCRYPTION='N';
332 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
333 --echo # Set Encryption process to wait after page 5 so that we can monitor
334 --echo # progress in performance_schema table
335 SET DEBUG_SYNC = 'alter_encrypt_tablespace_wait_after_page5 SIGNAL s1 WAIT_FOR s2';
336
337 let $con_default_thread_id= `select THREAD_ID from performance_schema.threads
338 where PROCESSLIST_ID = connection_id()`;
339
340 --send
341 ALTER TABLESPACE mysql ENCRYPTION='Y';
342 --echo # Monitoring connection
343 connect(con1, localhost, root,,);
344 --connection con1
345 SET DEBUG_SYNC = 'now WAIT_FOR s1';
346
347 --echo # Wait for Encryption progress monitoring to appear in PFS table
348 let $wait_condition = SELECT COUNT(*) = 1
349 FROM performance_schema.events_stages_current
350 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
351 --source include/wait_condition.inc
352
353 --echo # Wait for some progress to appear in PFS table
354 let $wait_condition = SELECT WORK_COMPLETED > 0
355 FROM performance_schema.events_stages_current
356 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
357 --source include/wait_condition.inc
358
359 # Verify the expected progress seen in PFS table
360 select WORK_COMPLETED
361 FROM performance_schema.events_stages_current
362 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)';
363
364 # Let the encryption processing continue
365 SET DEBUG_SYNC = 'now SIGNAL s2';
366
367 --echo # Default connection
368 --connection default
369 --reap
370
371 --echo # Once done, select count from PFS tables
372 SELECT COUNT(*)
373 FROM performance_schema.events_stages_current
374 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
375
376 SELECT COUNT(*)
377 FROM performance_schema.events_stages_history
378 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
379
380 SELECT COUNT(*)
381 FROM performance_schema.events_stages_history_long
382 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
383
384 SELECT COUNT(*)
385 FROM performance_schema.events_stages_summary_global_by_event_name
386 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
387 COUNT_STAR>0;
388
389 --disable_query_log
390 eval SELECT COUNT(*)
391 FROM performance_schema.events_stages_summary_by_thread_by_event_name
392 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
393 THREAD_ID=$con_default_thread_id;
394 --enable_query_log
395
396 SELECT COUNT(*)
397 FROM performance_schema.events_stages_summary_by_user_by_event_name
398 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
399 COUNT_STAR>0;
400
401 SELECT COUNT(*)
402 FROM performance_schema.events_stages_summary_by_host_by_event_name
403 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
404 COUNT_STAR>0;
405
406 SELECT COUNT(*)
407 FROM performance_schema.events_stages_summary_by_account_by_event_name
408 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
409 COUNT_STAR>0;
410
411 --echo # Check that Encryption done successfully.
412 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
413 WHERE NAME='mysql';
414 SELECT help_keyword_id FROM mysql.help_keyword ORDER BY help_keyword_id LIMIT 2;
415
416 --echo ###########
417 --echo # Cleanup #
418 --echo ###########
419 --disconnect con1
420 # Unencrypt tablespace
421 ALTER TABLESPACE mysql ENCRYPTION='N';
422 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
423
424 remove_file $MYSQL_TMP_DIR/mydummy_key;
425 remove_file $MYSQL_TMP_DIR/mysql_ts_keyring;
426 remove_file $MYSQLTEST_VARDIR/tmpfile.txt;
427
428 --echo #########################################################################
429 --echo # RESTART : WITHOUT KEYRING PLUGIN
430 --echo #########################################################################
431 let $restart_parameters = restart: ;
432 --source include/restart_mysqld.inc
433