Debian Codegraph @

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

raw
1
2 #############################################################
3 # TEST 1 : NORMAL ALTER ENCRYPT mysql TABLESPACE.
4 #############################################################
5
6 #########################################################################
7 # RESTART 1 : WITH KEYRING PLUGIN
8 #########################################################################
9 SET debug='+d,skip_dd_table_access_check';
10 # Initially, mysql should be unencrypted by default
11 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
12 NAME ENCRYPTION
13 mysql N
14 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
15 NAME OPTIONS
16 mysql encryption=N;
17 ALTER TABLESPACE mysql ENCRYPTION='Y';
18 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
19 NAME ENCRYPTION
20 mysql Y
21 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
22 NAME OPTIONS
23 mysql encryption=Y;
24 ALTER TABLESPACE mysql ENCRYPTION='N';
25 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
26 NAME ENCRYPTION
27 mysql N
28 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
29 NAME OPTIONS
30 mysql encryption=N;
31
32 #############################################################
33 # TEST 2 : CRASH DURING ALTER ENCRYPT mysql TABLESPACE.
34 #############################################################
35
36 ############################################################
37 # ALTER TABLESPACE 1 : Unencrypted => Encrypted #
38 # (crash at page 10) #
39 ############################################################
40 # Set Encryption process to crash at page 10
41 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
42 # Encrypt the tablespace. It will cause crash.
43 ALTER TABLESPACE mysql ENCRYPTION='Y';
44 # Restart after crash
45 SET debug='+d,skip_dd_table_access_check';
46 # Wait for Encryption processing to finish in background thread
47 set global innodb_buf_flush_list_now = 1;
48 # After restart/recovery, check that Encryption was roll-forward
49 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
50 NAME ENCRYPTION
51 mysql Y
52 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
53 NAME OPTIONS
54 mysql encryption=Y;
55 ALTER TABLESPACE mysql ENCRYPTION='Y';
56 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
57 NAME ENCRYPTION
58 mysql Y
59 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
60 NAME OPTIONS
61 mysql encryption=Y;
62 #########################################################################
63 # RESTART 2 : WITH KEYRING PLUGIN
64 #########################################################################
65 SET debug='+d,skip_dd_table_access_check';
66 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
67 NAME ENCRYPTION
68 mysql Y
69 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
70 NAME OPTIONS
71 mysql encryption=Y;
72 ############################################################
73 # ALTER TABLESPACE 2 : Encrypted => Unencrypted #
74 # (crash at page 10) #
75 ############################################################
76 # Set Unencryption process to crash at page 10
77 SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
78 # Unencrypt the tablespace. It will cause crash.
79 ALTER TABLESPACE mysql ENCRYPTION='N';
80 # Restart after crash
81 SET debug='+d,skip_dd_table_access_check';
82 # Wait for Unencryption processing to finish in background thread
83 set global innodb_buf_flush_list_now = 1;
84 # After restart/recovery, check that Unencryption was roll-forward
85 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
86 NAME ENCRYPTION
87 mysql N
88 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
89 NAME OPTIONS
90 mysql encryption=N;
91 ALTER TABLESPACE mysql ENCRYPTION='N';
92 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
93 NAME ENCRYPTION
94 mysql N
95 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
96 NAME OPTIONS
97 mysql encryption=N;
98 #########################################################################
99 # RESTART 3 : WITHOUT KEYRING PLUGIN
100 #########################################################################
101 SET debug='+d,skip_dd_table_access_check';
102 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
103 NAME ENCRYPTION
104 mysql N
105 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
106 NAME OPTIONS
107 mysql encryption=N;
108 #############################################################
109 # TEST 3 : CRASH BEFORE/AFTER ENCRYPTION PROCESSING.
110 #############################################################
111
112 #########################################################################
113 # RESTART 4 : WITH KEYRING PLUGIN
114 #########################################################################
115 SET debug='+d,skip_dd_table_access_check';
116 ALTER TABLESPACE mysql ENCRYPTION='Y';
117 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
118 NAME OPTIONS
119 mysql encryption=Y;
120 # Set server to crash just before encryption processing starts
121 SET SESSION debug="+d,alter_encrypt_tablespace_crash_before_processing";
122 # Unencrypt the tablespace. It will cause crash.
123 ALTER TABLESPACE mysql ENCRYPTION='N';
124 # Restart after crash
125 SET debug='+d,skip_dd_table_access_check';
126 # Wait for Unencryption processing to finish in background thread
127 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
128 NAME ENCRYPTION
129 mysql Y
130 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
131 NAME OPTIONS
132 mysql encryption=Y;
133 # Set server to crash just after encryption processing finishes
134 SET SESSION debug="-d,alter_encrypt_tablespace_crash_before_processing";
135 SET SESSION debug="+d,alter_encrypt_tablespace_crash_after_processing";
136 # Unencrypt the tablespace. It will cause crash.
137 ALTER TABLESPACE mysql ENCRYPTION='N';
138 # Restart after crash
139 SET debug='+d,skip_dd_table_access_check';
140 # Wait for Unencryption processing to finish in background thread
141 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
142 NAME ENCRYPTION
143 mysql N
144 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
145 NAME OPTIONS
146 mysql encryption=N;
147 #############################################################
148 # TEST 4 : CRASH DURING KEY ROTATION.
149 #############################################################
150
151 #########################################################################
152 # RESTART 5 : WITH KEYRING PLUGIN
153 #########################################################################
154 SET debug='+d,skip_dd_table_access_check';
155 ALTER TABLESPACE mysql ENCRYPTION='Y';
156 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
157 NAME OPTIONS
158 mysql encryption=Y;
159 # Set server to crash while rotating encryption
160 SET SESSION debug="+d,ib_crash_during_rotation_for_encryption";
161 ALTER INSTANCE ROTATE INNODB MASTER KEY;
162 # Restart after crash
163 SET debug='+d,skip_dd_table_access_check';
164 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
165 NAME OPTIONS
166 mysql encryption=Y;
167 SET SESSION debug="-d,ib_crash_during_rotation_for_encryption";
168 ALTER INSTANCE ROTATE INNODB MASTER KEY;
169 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
170 NAME OPTIONS
171 mysql encryption=Y;
172 #############################################################
173 # TEST 5 : PRIVILEGE CHECK.
174 #############################################################
175
176 CREATE DATABASE priv_test;
177 CREATE USER myuser@'localhost';
178 GRANT ALL ON priv_test.* TO myuser@'localhost';
179 #connection con1
180 ALTER TABLESPACE mysql ENCRYPTION='Y';
181 ERROR 42000: Access denied; you need (at least one of) the CREATE TABLESPACE privilege(s) for this operation
182 #connection default
183 GRANT CREATE TABLESPACE ON mysql.* TO myuser@'localhost';
184 ERROR HY000: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
185 GRANT CREATE TABLESPACE ON *.* TO myuser@'localhost';
186 #connection con1
187 ALTER TABLESPACE mysql ENCRYPTION='N';
188 #connection default
189 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
190 NAME OPTIONS
191 mysql encryption=N;
192 #connection con1
193 ALTER TABLESPACE mysql ENCRYPTION='Y';
194 #connection default
195 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
196 NAME OPTIONS
197 mysql encryption=Y;
198 DROP DATABASE priv_test;
199 DROP USER myuser@localhost;
200 ###########
201 # Cleanup #
202 ###########
203 ALTER TABLESPACE mysql ENCRYPTION='N';
204 SELECT NAME,OPTIONS FROM mysql.tablespaces WHERE NAME='mysql';
205 NAME OPTIONS
206 mysql encryption=N;
207 #########################################################################
208 # RESTART 6 : WITHOUT KEYRING PLUGIN
209 #########################################################################
210 # restart: