Debian Codegraph @

mysql-test/suite/component_keyring_file/r/tablespace_encrypt_2.result

raw
1 # ----------------------------------------------------------------------
2 # Setup
3 # Creating local configuration file for keyring component: component_keyring_file
4 # Creating manifest file for current MySQL server instance
5 # Re-starting mysql server with manifest file
6 # ----------------------------------------------------------------------
7 #########################################################################
8 # START : WITHOUT KEYRING
9 #########################################################################
10 # Taking backup of local manifest file for MySQL server instance
11 #########
12 # SETUP #
13 #########
14 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE=InnoDB ENCRYPTION="N";
15 CREATE TABLE t1(c1 char(100)) ENGINE=InnoDB TABLESPACE encrypt_ts;
16 set global innodb_buf_flush_list_now = 1;
17 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
18 NAME ENCRYPTION
19 encrypt_ts N
20 SELECT * FROM t1 LIMIT 10;
21 c1
22 SOME VALUES
23 SOME VALUES
24 SOME VALUES
25 SOME VALUES
26 SOME VALUES
27 SOME VALUES
28 SOME VALUES
29 SOME VALUES
30 SOME VALUES
31 SOME VALUES
32 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
33 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
34 #############################################################
35 # TEST 1 : CRASH DURING ALTER ENCRYPT A TABLESPACE.
36 #############################################################
37
38 #########################################################################
39 # RESTART 1 : WITH KEYRING
40 #########################################################################
41 # Restore local manifest file for MySQL server instance from backup
42 ############################################################
43 # ALTER TABLESPACE 1 : Unencrypted => Encrypted #
44 # (crash at page 10) #
45 ############################################################
46 # Set Encryption process to crash at page 10
47 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
48 # Encrypt the tablespace. It will cause crash.
49 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
50 # Restart after crash
51 # Wait for Encryption processing to finish in background thread
52 set global innodb_buf_flush_list_now = 1;
53 # After restart/recovery, check that Encryption was roll-forward
54 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
55 NAME ENCRYPTION
56 encrypt_ts Y
57 SELECT * FROM t1 LIMIT 10;
58 c1
59 SOME VALUES
60 SOME VALUES
61 SOME VALUES
62 SOME VALUES
63 SOME VALUES
64 SOME VALUES
65 SOME VALUES
66 SOME VALUES
67 SOME VALUES
68 SOME VALUES
69 #########################################################################
70 # RESTART 2 : WITHOUT KEYRING
71 #########################################################################
72 # Taking backup of local manifest file for MySQL server instance
73 SELECT * FROM t1 LIMIT 10;
74 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
75 #########################################################################
76 # RESTART 3 : WITH KEYRING
77 #########################################################################
78 # Restore local manifest file for MySQL server instance from backup
79 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
80 NAME ENCRYPTION
81 encrypt_ts Y
82 SELECT * FROM t1 LIMIT 10;
83 c1
84 SOME VALUES
85 SOME VALUES
86 SOME VALUES
87 SOME VALUES
88 SOME VALUES
89 SOME VALUES
90 SOME VALUES
91 SOME VALUES
92 SOME VALUES
93 SOME VALUES
94 ############################################################
95 # ALTER TABLESPACE 2 : Encrypted => Unencrypted #
96 # (crash at page 10) #
97 ############################################################
98 # Set Unencryption process to crash at page 10
99 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
100 # Unencrypt the tablespace. It will cause crash.
101 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
102 # Restart after crash
103 # Wait for Unencryption processing to finish in background thread
104 set global innodb_buf_flush_list_now = 1;
105 # After restart/recovery, check that Unencryption was roll-forward
106 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
107 NAME ENCRYPTION
108 encrypt_ts N
109 SELECT * FROM t1 LIMIT 10;
110 c1
111 SOME VALUES
112 SOME VALUES
113 SOME VALUES
114 SOME VALUES
115 SOME VALUES
116 SOME VALUES
117 SOME VALUES
118 SOME VALUES
119 SOME VALUES
120 SOME VALUES
121 #########################################################################
122 # RESTART 4 : WITHOUT KEYRING
123 #########################################################################
124 # Taking backup of local manifest file for MySQL server instance
125 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
126 NAME ENCRYPTION
127 encrypt_ts N
128 SELECT * FROM t1 LIMIT 10;
129 c1
130 SOME VALUES
131 SOME VALUES
132 SOME VALUES
133 SOME VALUES
134 SOME VALUES
135 SOME VALUES
136 SOME VALUES
137 SOME VALUES
138 SOME VALUES
139 SOME VALUES
140 ####################################################################
141 # TEST 2 : CRASH DURING ALTER ENCRYPT A TABLESPACE (Compressed).
142 ####################################################################
143
144 CREATE TABLESPACE compress_ts ADD DATAFILE 'compress_ts.ibd' ENGINE=InnoDB
145 ENCRYPTION="N" FILE_BLOCK_SIZE=8192;
146 CREATE TABLE t2(c1 char(100)) ENGINE=InnoDB TABLESPACE compress_ts
147 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
148 set global innodb_buf_flush_list_now = 1;
149 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
150 WHERE NAME='compress_ts';
151 NAME ENCRYPTION
152 compress_ts N
153 SELECT * FROM t2 LIMIT 10;
154 c1
155 SOME VALUES
156 SOME VALUES
157 SOME VALUES
158 SOME VALUES
159 SOME VALUES
160 SOME VALUES
161 SOME VALUES
162 SOME VALUES
163 SOME VALUES
164 SOME VALUES
165 #########################################################################
166 # RESTART 5 : WITH KEYRING
167 #########################################################################
168 # Restore local manifest file for MySQL server instance from backup
169 ############################################################
170 # ALTER TABLESPACE 1 : Unencrypted => Encrypted #
171 # (crash at page 10) #
172 ############################################################
173 # Set Encryption process to crash at page 10
174 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
175 # Encrypt the tablespace. It will cause crash.
176 ALTER TABLESPACE compress_ts ENCRYPTION='Y';
177 # Restart after crash
178 # Wait for Encryption processing to finish in background thread
179 set global innodb_buf_flush_list_now = 1;
180 # After restart/recovery, check that Encryption was roll-forward
181 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
182 WHERE NAME='compress_ts';
183 NAME ENCRYPTION
184 compress_ts Y
185 SELECT * FROM t2 LIMIT 10;
186 c1
187 SOME VALUES
188 SOME VALUES
189 SOME VALUES
190 SOME VALUES
191 SOME VALUES
192 SOME VALUES
193 SOME VALUES
194 SOME VALUES
195 SOME VALUES
196 SOME VALUES
197 #########################################################################
198 # RESTART 6 : WITHOUT KEYRING
199 #########################################################################
200 # Taking backup of local manifest file for MySQL server instance
201 SELECT * FROM t2 LIMIT 10;
202 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
203 #########################################################################
204 # RESTART 7 WITH KEYRING
205 #########################################################################
206 # Restore local manifest file for MySQL server instance from backup
207 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
208 WHERE NAME='compress_ts';
209 NAME ENCRYPTION
210 compress_ts Y
211 SELECT * FROM t2 LIMIT 10;
212 c1
213 SOME VALUES
214 SOME VALUES
215 SOME VALUES
216 SOME VALUES
217 SOME VALUES
218 SOME VALUES
219 SOME VALUES
220 SOME VALUES
221 SOME VALUES
222 SOME VALUES
223 ############################################################
224 # ALTER TABLESPACE 2 : Encrypted => Unencrypted #
225 # (crash at page 10) #
226 ############################################################
227 # Set Unencryption process to crash at page 10
228 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
229 # Unencrypt the tablespace. It will cause crash.
230 ALTER TABLESPACE compress_ts ENCRYPTION='N';
231 # Restart after crash
232 # Wait for Unencryption processing to finish in background thread
233 set global innodb_buf_flush_list_now = 1;
234 # After restart/recovery, check that Unencryption was roll-forward
235 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
236 WHERE NAME='compress_ts';
237 NAME ENCRYPTION
238 compress_ts N
239 SELECT * FROM t2 LIMIT 10;
240 c1
241 SOME VALUES
242 SOME VALUES
243 SOME VALUES
244 SOME VALUES
245 SOME VALUES
246 SOME VALUES
247 SOME VALUES
248 SOME VALUES
249 SOME VALUES
250 SOME VALUES
251 #########################################################################
252 # RESTART 8 : WITHOUT KEYRING
253 #########################################################################
254 # Taking backup of local manifest file for MySQL server instance
255 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
256 NAME ENCRYPTION
257 encrypt_ts N
258 SELECT * FROM t2 LIMIT 10;
259 c1
260 SOME VALUES
261 SOME VALUES
262 SOME VALUES
263 SOME VALUES
264 SOME VALUES
265 SOME VALUES
266 SOME VALUES
267 SOME VALUES
268 SOME VALUES
269 SOME VALUES
270 DROP TABLE t2;
271 DROP TABLESPACE compress_ts;
272 #############################################################
273 # TEST 3 : CRASH BEFORE/AFTER ENCRYPTION PROCESSING.
274 #############################################################
275
276 #########################################################################
277 # RESTART 9 : WITH KEYRING
278 #########################################################################
279 # Restore local manifest file for MySQL server instance from backup
280 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
281 SELECT * FROM t1 LIMIT 10;
282 c1
283 SOME VALUES
284 SOME VALUES
285 SOME VALUES
286 SOME VALUES
287 SOME VALUES
288 SOME VALUES
289 SOME VALUES
290 SOME VALUES
291 SOME VALUES
292 SOME VALUES
293 # Set server to crash just before encryption processing starts
294 SET SESSION debug="+d,alter_encrypt_tablespace_crash_before_processing";
295 # Unencrypt the tablespace. It will cause crash.
296 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
297 # Restart after crash
298 # Wait for Unencryption processing to finish in background thread
299 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
300 NAME ENCRYPTION
301 encrypt_ts Y
302 # Set server to crash just after encryption processing finishes
303 SET SESSION debug="-d,alter_encrypt_tablespace_crash_before_processing";
304 SET SESSION debug="+d,alter_encrypt_tablespace_crash_after_processing";
305 # Unencrypt the tablespace. It will cause crash.
306 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
307 # Restart after crash
308 # Wait for Unencryption processing to finish in background thread
309 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
310 NAME ENCRYPTION
311 encrypt_ts N
312 #############################################################
313 # TEST 4 : CRASH DURING KEY ROTATION.
314 #############################################################
315
316 #########################################################################
317 # RESTART 10 : WITH KEYRING
318 #########################################################################
319 ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
320 SELECT * FROM t1 LIMIT 10;
321 c1
322 SOME VALUES
323 SOME VALUES
324 SOME VALUES
325 SOME VALUES
326 SOME VALUES
327 SOME VALUES
328 SOME VALUES
329 SOME VALUES
330 SOME VALUES
331 SOME VALUES
332 # Set server to crash while rotating encryption
333 SET SESSION debug="+d,ib_crash_during_rotation_for_encryption";
334 ALTER INSTANCE ROTATE INNODB MASTER KEY;
335 # Restart after crash
336 SELECT * FROM t1 LIMIT 10;
337 c1
338 SOME VALUES
339 SOME VALUES
340 SOME VALUES
341 SOME VALUES
342 SOME VALUES
343 SOME VALUES
344 SOME VALUES
345 SOME VALUES
346 SOME VALUES
347 SOME VALUES
348 SET SESSION debug="-d,ib_crash_during_rotation_for_encryption";
349 ALTER INSTANCE ROTATE INNODB MASTER KEY;
350 SELECT * FROM t1 LIMIT 10;
351 c1
352 SOME VALUES
353 SOME VALUES
354 SOME VALUES
355 SOME VALUES
356 SOME VALUES
357 SOME VALUES
358 SOME VALUES
359 SOME VALUES
360 SOME VALUES
361 SOME VALUES
362 ###############################################################
363 # TEST 5 : CRASH DURING CREATING AN ENCRYPTED TABLESPACE
364 ###############################################################
365
366 # Set server to crash while creating an encrypted tablespace
367 SET SESSION debug="+d,ib_crash_during_create_tablespace_for_encryption";
368 # Try to create an encrypted tablespace. It will cause server to crash.
369 CREATE TABLESPACE encrypt_ts_2 ADD DATAFILE 'encrypt_ts_2.ibd' ENGINE=InnoDB ENCRYPTION="Y";
370 # Restart after crash
371 SET SESSION debug="-d,ib_crash_during_create_tablespace_for_encryption";
372 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
373 WHERE NAME LIKE "%encrypt_ts%";
374 NAME ENCRYPTION
375 encrypt_ts Y
376 ###############################################################
377 # TEST 6 : DMLs ALLOWED AND DDLs BLOCKED TEST
378 ###############################################################
379 SELECT ENABLED FROM performance_schema.setup_instruments
380 WHERE NAME='wait/lock/metadata/sql/mdl';
381 ENABLED
382 YES
383 SET DEBUG_SYNC = 'innodb_alter_encrypt_tablespace SIGNAL s1 WAIT_FOR s2';
384 ALTER TABLESPACE encrypt_ts ENCRYPTION='N';;
385 # Session 2
386 SET DEBUG_SYNC = 'now WAIT_FOR s1';
387 OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
388 TABLESPACE encrypt_ts EXCLUSIVE TRANSACTION GRANTED
389 DESCRIBE t1;
390 Field Type Null Key Default Extra
391 c1 char(100) YES NULL
392 SHOW CREATE TABLE t1;
393 Table Create Table
394 t1 CREATE TABLE `t1` (
395 `c1` char(100) DEFAULT NULL
396 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
397 SELECT COUNT(*) FROM t1;
398 COUNT(*)
399 4096
400 INSERT INTO t1 VALUES ("SOME VALUES");
401 SELECT COUNT(*) from t1;
402 COUNT(*)
403 4097
404 ALTER TABLE t1 ADD COLUMN (c2 int);;
405 # Monitoring session
406 OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
407 TABLESPACE encrypt_ts EXCLUSIVE TRANSACTION GRANTED
408 # Wait for MDL request by con2 to appear in metadata_locks table.
409 OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
410 TABLE t1 SHARED_UPGRADABLE TRANSACTION PENDING
411 SET DEBUG_SYNC = 'now SIGNAL s2';
412 # Connection default
413 # Connection con2
414 # Connection default
415 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
416 WHERE NAME LIKE "%encrypt_ts%";
417 NAME ENCRYPTION
418 encrypt_ts N
419 DESCRIBE t1;
420 Field Type Null Key Default Extra
421 c1 char(100) YES NULL
422 c2 int YES NULL
423 SHOW CREATE TABLE t1;
424 Table Create Table
425 t1 CREATE TABLE `t1` (
426 `c1` char(100) DEFAULT NULL,
427 `c2` int DEFAULT NULL
428 ) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
429 ###########
430 # Cleanup #
431 ###########
432 DROP TABLE t1;
433 DROP TABLESPACE encrypt_ts;
434 # ----------------------------------------------------------------------
435 # Teardown
436 # Removing manifest file for current MySQL server instance
437 # Removing local keyring file for keyring component: component_keyring_file
438 # Removing local configuration file for keyring component: component_keyring_file
439 # Restarting server without the manifest file
440 # ----------------------------------------------------------------------