Debian Codegraph @

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

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