Debian Codegraph @

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