Debian Codegraph @

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

raw
1 ################################################################################
2 # InnoDB transparent tablespace data encryption for general shared tablespace.
3 # This test case will test
4 # - Crash during altering an encrypted tablespace
5 # - encryption='y' to encryption='n'
6 # - encryption='n' to encryption='y'
7 # - Crash during altering an encrypted tablespace (Compressed)
8 # - encryption='y' to encryption='n'
9 # - encryption='n' to encryption='y'
10 # - Crash
11 # - just before encryption processing starts
12 # - just after encryption processing finishes
13 # - Crash during master key rotation
14 # - Crash during creating an encrypted tablespace
15 # - During alter tablespace encryption, DMLs are allowed and DDLs are blocked
16 ################################################################################
17
18 --source include/big_test.inc
19 --source include/have_debug.inc
20 # --source include/no_valgrind_without_big.inc
21 # Disable in valgrind because of timeout, cf. Bug#22760145
22 --source include/not_valgrind.inc
23 # Waiting time when (re)starting the server
24 --let $explicit_default_wait_counter=10000
25
26 --disable_query_log
27 call mtr.add_suppression("ibd can't be decrypted, please confirm the keyring is loaded.");
28 call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring Is loaded.");
29 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Check keyring fail, please check the keyring is loaded.");
30 call mtr.add_suppression("\\[Warning\\] \\[MY-013674\\] \\[InnoDB\\] Decrypting a page in doublewrite file failed: space_id=");
31 call mtr.add_suppression("\\[Warning\\] \\[MY-013675\\] \\[InnoDB\\] Encryption key missing: space_id=");
32 call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Encryption information in datafile: .* can't be decrypted, please confirm that keyring is loaded.");
33 --enable_query_log
34
35 --echo #########################################################################
36 --echo # START : WITHOUT KEYRING PLUGIN
37 --echo #########################################################################
38 --echo
39 --echo #########
40 --echo # SETUP #
41 --echo #########
42
43 # Create an Unencrypted tablespace
44 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE=InnoDB ENCRYPTION="N";
45 # Create an unencrypted table in tablespace
46 CREATE TABLE t1(c1 char(100)) ENGINE=InnoDB TABLESPACE encrypt_ts;
47 # Insert few rows in table
48 --disable_query_log
49 INSERT INTO t1 VALUES ("SOME VALUES");
50 let $counter=12;
51 while ($counter>0)
52 {
53 INSERT INTO test.t1 SELECT * FROM test.t1;
54 dec $counter;
55 }
56 --enable_query_log
57
58 # Make sure ts file is updated with new records in table
59 set global innodb_buf_flush_list_now = 1;
60
61 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
62 SELECT * FROM t1 LIMIT 10;
63
64 # Try to alter tablespace to be encrypted. Should fail as keyring is not laoded.
65 --error ER_CANNOT_FIND_KEY_IN_KEYRING
66 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
67
68 --echo #############################################################
69 --echo # TEST 1 : CRASH DURING ALTER ENCRYPT A TABLESPACE.
70 --echo #############################################################
71 --echo
72 --echo #########################################################################
73 --echo # RESTART 1 : WITH KEYRING PLUGIN
74 --echo #########################################################################
75 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
76 --source include/restart_mysqld_no_echo.inc
77
78 --echo ############################################################
79 --echo # ALTER TABLESPACE 1 : Unencrypted => Encrypted #
80 --echo # (crash at page 10) #
81 --echo ############################################################
82 --echo # Set Encryption process to crash at page 10
83 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
84
85 --echo # Encrypt the tablespace. It will cause crash.
86 --source include/expect_crash.inc
87 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
88 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
89 --echo # Restart after crash
90 --source include/start_mysqld_no_echo.inc
91
92 --echo # Wait for Encryption processing to finish in background thread
93 let $wait_condition = SELECT ENCRYPTION = 'Y'
94 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
95 WHERE NAME='encrypt_ts';
96 --source include/wait_condition.inc
97
98 # Make sure ts file is updated with new records in table
99 set global innodb_buf_flush_list_now = 1;
100
101 --echo # After restart/recovery, check that Encryption was roll-forward
102 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
103 SELECT * FROM t1 LIMIT 10;
104
105 --echo #########################################################################
106 --echo # RESTART 2 : WITHOUT KEYRING PLUGIN
107 --echo #########################################################################
108 let $restart_parameters = restart:;
109 --source include/restart_mysqld_no_echo.inc
110
111 # We shouldn't be able to read t1 records as it is encrypted now and keyring
112 # plugin is not loaded.
113 --error ER_CANNOT_FIND_KEY_IN_KEYRING
114 SELECT * FROM t1 LIMIT 10;
115
116 --echo #########################################################################
117 --echo # RESTART 3 : WITH KEYRING PLUGIN
118 --echo #########################################################################
119 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
120 --source include/restart_mysqld_no_echo.inc
121
122 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
123 SELECT * FROM t1 LIMIT 10;
124
125 --echo ############################################################
126 --echo # ALTER TABLESPACE 2 : Encrypted => Unencrypted #
127 --echo # (crash at page 10) #
128 --echo ############################################################
129 --echo # Set Unencryption process to crash at page 10
130 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
131
132 --echo # Unencrypt the tablespace. It will cause crash.
133 --source include/expect_crash.inc
134 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
135 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
136 --echo # Restart after crash
137 --source include/start_mysqld_no_echo.inc
138
139 --echo # Wait for Unencryption processing to finish in background thread
140 let $wait_condition = SELECT ENCRYPTION = 'N'
141 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
142 WHERE NAME='encrypt_ts';
143 --source include/wait_condition.inc
144
145 # Make sure ts file is updated with new records in table
146 set global innodb_buf_flush_list_now = 1;
147
148 --echo # After restart/recovery, check that Unencryption was roll-forward
149 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
150 SELECT * FROM t1 LIMIT 10;
151
152 --echo #########################################################################
153 --echo # RESTART 4 : WITHOUT KEYRING PLUGIN
154 --echo #########################################################################
155 let $restart_parameters = restart:;
156 --source include/restart_mysqld_no_echo.inc
157
158 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
159 # We should be able to read t1 records even without OKV keyring plugin as it should
160 # be unencrypted now.
161 SELECT * FROM t1 LIMIT 10;
162
163 --echo ####################################################################
164 --echo # TEST 2 : CRASH DURING ALTER ENCRYPT A TABLESPACE (Compressed).
165 --echo ####################################################################
166 --echo
167 # Create an Unencrypted compressed tablespace
168 CREATE TABLESPACE compress_ts ADD DATAFILE 'compress_ts.ibd' ENGINE=InnoDB
169 ENCRYPTION="N" FILE_BLOCK_SIZE=8192;
170 # Create an unencrypted table in tablespace
171 CREATE TABLE t2(c1 char(100)) ENGINE=InnoDB TABLESPACE compress_ts
172 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
173 # Insert few rows in table
174 --disable_query_log
175 INSERT INTO t2 VALUES ("SOME VALUES");
176 let $counter=12;
177 while ($counter>0)
178 {
179 INSERT INTO test.t2 SELECT * FROM test.t1;
180 dec $counter;
181 }
182 --enable_query_log
183
184 # Make sure ts file is updated with new records in table
185 set global innodb_buf_flush_list_now = 1;
186
187 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
188 WHERE NAME='compress_ts';
189 SELECT * FROM t2 LIMIT 10;
190
191 --echo #########################################################################
192 --echo # RESTART 5 : WITH KEYRING PLUGIN
193 --echo #########################################################################
194 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
195 --source include/restart_mysqld_no_echo.inc
196
197 --echo ############################################################
198 --echo # ALTER TABLESPACE 1 : Unencrypted => Encrypted #
199 --echo # (crash at page 10) #
200 --echo ############################################################
201 --echo # Set Encryption process to crash at page 10
202 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
203
204 --echo # Encrypt the tablespace. It will cause crash.
205 --source include/expect_crash.inc
206 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
207 ALTER TABLESPACE compress_ts ENCRYPTION='Y';
208 --echo # Restart after crash
209 --source include/start_mysqld_no_echo.inc
210
211 --echo # Wait for Encryption processing to finish in background thread
212 let $wait_condition = SELECT ENCRYPTION = 'Y'
213 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
214 WHERE NAME='compress_ts';
215 --source include/wait_condition.inc
216
217 # Make sure ts file is updated with new records in table
218 set global innodb_buf_flush_list_now = 1;
219
220 --echo # After restart/recovery, check that Encryption was roll-forward
221 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
222 WHERE NAME='compress_ts';
223 SELECT * FROM t2 LIMIT 10;
224
225 --echo #########################################################################
226 --echo # RESTART 6 : WITHOUT KEYRING PLUGIN
227 --echo #########################################################################
228 let $restart_parameters = restart:;
229 --source include/restart_mysqld_no_echo.inc
230
231 # We shouldn't be able to read t2 records as it is encrypted now and keyring
232 # plugin is not loaded.
233 --error ER_CANNOT_FIND_KEY_IN_KEYRING
234 SELECT * FROM t2 LIMIT 10;
235
236 --echo #########################################################################
237 --echo # RESTART 7 WITH KEYRING PLUGIN
238 --echo #########################################################################
239 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
240 --source include/restart_mysqld_no_echo.inc
241
242 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
243 WHERE NAME='compress_ts';
244 SELECT * FROM t2 LIMIT 10;
245
246 --echo ############################################################
247 --echo # ALTER TABLESPACE 2 : Encrypted => Unencrypted #
248 --echo # (crash at page 10) #
249 --echo ############################################################
250 --echo # Set Unencryption process to crash at page 10
251 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
252
253 --echo # Unencrypt the tablespace. It will cause crash.
254 --source include/expect_crash.inc
255 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
256 ALTER TABLESPACE compress_ts ENCRYPTION='N';
257 --echo # Restart after crash
258 --source include/start_mysqld_no_echo.inc
259
260 --echo # Wait for Unencryption processing to finish in background thread
261 let $wait_condition = SELECT ENCRYPTION = 'N'
262 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
263 WHERE NAME='compress_ts';
264 --source include/wait_condition.inc
265
266 # Make sure ts file is updated with new records in table
267 set global innodb_buf_flush_list_now = 1;
268
269 --echo # After restart/recovery, check that Unencryption was roll-forward
270 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
271 WHERE NAME='compress_ts';
272 SELECT * FROM t2 LIMIT 10;
273
274 --echo #########################################################################
275 --echo # RESTART 8 : WITHOUT KEYRING PLUGIN
276 --echo #########################################################################
277 let $restart_parameters = restart:;
278 --source include/restart_mysqld_no_echo.inc
279
280 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
281 # We should be able to read t1 records even without keyring plugin as it should
282 # be unencrypted now.
283 SELECT * FROM t2 LIMIT 10;
284
285 DROP TABLE t2;
286 DROP TABLESPACE compress_ts;
287
288 --echo #############################################################
289 --echo # TEST 3 : CRASH BEFORE/AFTER ENCRYPTION PROCESSING.
290 --echo #############################################################
291 --echo
292 --echo #########################################################################
293 --echo # RESTART 9 : WITH KEYRING PLUGIN
294 --echo #########################################################################
295 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
296 --source include/restart_mysqld_no_echo.inc
297
298 # Encrypt tablespace
299 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
300 # Read rows from table
301 SELECT * FROM t1 LIMIT 10;
302
303 --echo # Set server to crash just before encryption processing starts
304 SET SESSION debug="+d,alter_encrypt_tablespace_crash_before_processing";
305
306 --echo # Unencrypt the tablespace. It will cause crash.
307 --source include/expect_crash.inc
308 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
309 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
310 --echo # Restart after crash
311 --source include/start_mysqld_no_echo.inc
312
313 --echo # Wait for Unencryption processing to finish in background thread
314 let $wait_condition = select count(*) = 0
315 from performance_schema.events_stages_current
316 where EVENT_NAME='stage/innodb/alter tablespace (encryption)';
317 --source include/wait_condition.inc
318
319 # Encrytion property of tablespace shouldn't have changed i.e. it should still
320 # be encrypted.
321 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
322
323
324 --echo # Set server to crash just after encryption processing finishes
325 SET SESSION debug="-d,alter_encrypt_tablespace_crash_before_processing";
326 SET SESSION debug="+d,alter_encrypt_tablespace_crash_after_processing";
327
328 --echo # Unencrypt the tablespace. It will cause crash.
329 --source include/expect_crash.inc
330 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
331 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
332 --echo # Restart after crash
333 --source include/start_mysqld_no_echo.inc
334
335 --echo # Wait for Unencryption processing to finish in background thread
336 let $wait_condition = select count(*) = 0
337 from performance_schema.events_stages_current
338 where EVENT_NAME='stage/innodb/alter tablespace (encryption)';
339 --source include/wait_condition.inc
340
341 # Encrytion property of tablespace should have changed i.e. it should be
342 # unencrypted now.
343 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
344
345 --echo #############################################################
346 --echo # TEST 4 : CRASH DURING KEY ROTATION.
347 --echo #############################################################
348 --echo
349 --echo #########################################################################
350 --echo # RESTART 10 : WITH KEYRING PLUGIN
351 --echo #########################################################################
352 let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
353 --source include/restart_mysqld_no_echo.inc
354
355 # Encrypt tablespace
356 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
357 # Read rows from table
358 SELECT * FROM t1 LIMIT 10;
359
360 --echo # Set server to crash while rotating encryption
361 SET SESSION debug="+d,ib_crash_during_rotation_for_encryption";
362
363 # Rotate the key. It will cause server to crash.
364 --source include/expect_crash.inc
365 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
366 ALTER INSTANCE ROTATE INNODB MASTER KEY;
367
368 --echo # Restart after crash
369 --source include/start_mysqld_no_echo.inc
370 # Read rows from table
371 SELECT * FROM t1 LIMIT 10;
372
373 # Rotate the key.
374 SET SESSION debug="-d,ib_crash_during_rotation_for_encryption";
375 ALTER INSTANCE ROTATE INNODB MASTER KEY;
376 # Read rows from table
377 SELECT * FROM t1 LIMIT 10;
378
379 --echo ###############################################################
380 --echo # TEST 5 : CRASH DURING CREATING AN ENCRYPTED TABLESPACE
381 --echo ###############################################################
382 --echo
383 --echo # Set server to crash while creating an encrypted tablespace
384 SET SESSION debug="+d,ib_crash_during_create_tablespace_for_encryption";
385
386 --echo # Try to create an encrypted tablespace. It will cause server to crash.
387 --source include/expect_crash.inc
388 --error 0,CR_SERVER_LOST,ER_INTERNAL_ERROR
389 CREATE TABLESPACE encrypt_ts_2 ADD DATAFILE 'encrypt_ts_2.ibd' ENGINE=InnoDB ENCRYPTION="Y";
390
391 --echo # Restart after crash
392 --source include/start_mysqld_no_echo.inc
393 SET SESSION debug="-d,ib_crash_during_create_tablespace_for_encryption";
394
395 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
396 WHERE NAME LIKE "%encrypt_ts%";
397
398 --echo ###############################################################
399 --echo # TEST 6 : DMLs ALLOWED AND DDLs BLOCKED TEST
400 --echo ###############################################################
401
402 # Make sure metadata locks instrumentation is enabled
403 SELECT ENABLED FROM performance_schema.setup_instruments
404 WHERE NAME='wait/lock/metadata/sql/mdl';
405
406 let $con_default_thread_id= `select THREAD_ID from performance_schema.threads
407 where PROCESSLIST_ID = connection_id()`;
408
409 SET DEBUG_SYNC = 'innodb_alter_encrypt_tablespace SIGNAL s1 WAIT_FOR s2';
410 --send ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
411
412 --echo # Session 2
413 connect(con2, localhost, root,,);
414 --connection con2
415 let $con2_thread_id= `select THREAD_ID from performance_schema.threads
416 where PROCESSLIST_ID = connection_id()`;
417 SET DEBUG_SYNC = 'now WAIT_FOR s1';
418 # Make sure MDL on tablespace is GRANTED to 'default connection' thread.
419 --disable_query_log
420 eval SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
421 FROM performance_schema.metadata_locks
422 WHERE OBJECT_NAME='encrypt_ts' AND
423 OWNER_THREAD_ID = $con_default_thread_id;
424 --enable_query_log
425 # Try to run some DMLs. Should be allowed.
426 DESCRIBE t1;
427 SHOW CREATE TABLE t1;
428 SELECT COUNT(*) FROM t1;
429 INSERT INTO t1 VALUES ("SOME VALUES");
430 SELECT COUNT(*) from t1;
431 # Try to run a DDL. Should be blocked.
432 --send ALTER TABLE t1 ADD COLUMN (c2 int);
433
434 --echo # Monitoring session
435 connect(conmon, localhost, root,,);
436 --connection conmon
437 # Make sure 'connection default' thread has MDL request GRANTED.
438 --disable_query_log
439 eval SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
440 FROM performance_schema.metadata_locks
441 WHERE OBJECT_NAME='encrypt_ts' AND
442 OWNER_THREAD_ID = $con_default_thread_id;
443 --echo # Wait for MDL request by con2 to appear in metadata_locks table.
444 let $wait_condition = SELECT LOCK_STATUS = 'PENDING'
445 FROM performance_schema.metadata_locks
446 WHERE OBJECT_NAME='t1' AND
447 OWNER_THREAD_ID = $con2_thread_id;
448 --source include/wait_condition.inc
449 # Make sure con2 thread has MDL request in PENDING state.
450 eval SELECT OBJECT_TYPE, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS
451 FROM performance_schema.metadata_locks
452 WHERE OBJECT_NAME='t1' AND
453 OWNER_THREAD_ID = $con2_thread_id;
454 --enable_query_log
455
456 # Allow 'default connection' thread to proceed
457 SET DEBUG_SYNC = 'now SIGNAL s2';
458
459 --echo # Connection default
460 --connection default
461 --reap
462
463 --echo # Connection con2
464 --connection con2
465 --reap
466
467 --echo # Connection default
468 --connection default
469 # Make sure Tablespace 'unencryption' is done.
470 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
471 WHERE NAME LIKE "%encrypt_ts%";
472 # Make sure table DDL is done.
473 DESCRIBE t1;
474 SHOW CREATE TABLE t1;
475
476 --echo ###########
477 --echo # Cleanup #
478 --echo ###########
479 --disconnect con2
480 --disconnect conmon
481
482 DROP TABLE t1;
483 DROP TABLESPACE encrypt_ts;
484 remove_file $MYSQL_TMP_DIR/mysecret_keyring;
485
486 --echo # Restarting server without keyring to restore server state
487 let $restart_parameters = restart: ;
488 --source include/restart_mysqld.inc