Debian Codegraph @

mysql-test/suite/component_keyring_file/r/mysql_ts_alter_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 # SETUP #
9 #########
10
11 # Taking backup of local manifest file for MySQL server instance
12 #########################################################################
13 # START : WITHOUT KEYRING
14 #########################################################################
15 ALTER TABLESPACE mysql ENCRYPTION='Y';
16 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
17 ALTER TABLESPACE mysql ENCRYPTION='N';
18 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
19 ALTER INSTANCE ROTATE INNODB MASTER KEY;
20 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
21 #########################################################################
22 # RESTART 1 : WITH KEYRING
23 #########################################################################
24 # Restore local manifest file for MySQL server instance from backup
25 --------------------------------------------------
26 By Default, mysql tablespace should be unencrypted
27 --------------------------------------------------
28 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
29 NAME ENCRYPTION
30 mysql N
31 # Print result
32 table space is Encrypted.
33 ALTER TABLESPACE mysql ENCRYPTION='Y';
34 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
35 NAME ENCRYPTION
36 mysql Y
37 # Print result
38 table space is Encrypted.
39 -----------------------------------------------------
40 ALTER mysql TABLESPACE WITH INVALID ENCRYPTION OPTION
41 -----------------------------------------------------
42 ALTER TABLESPACE mysql ENCRYPTION='R';
43 ERROR HY000: Invalid encryption option.
44 ALTER TABLESPACE mysql ENCRYPTION='TRUE';
45 ERROR HY000: Invalid encryption option.
46 ALTER TABLESPACE mysql ENCRYPTION='True';
47 ERROR HY000: Invalid encryption option.
48 ALTER TABLESPACE mysql ENCRYPTION='true';
49 ERROR HY000: Invalid encryption option.
50 ALTER TABLESPACE mysql ENCRYPTION=TRUE;
51 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRUE' at line 1
52 ALTER TABLESPACE mysql ENCRYPTION=True;
53 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'True' at line 1
54 ALTER TABLESPACE mysql ENCRYPTION=true;
55 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'true' at line 1
56 ALTER TABLESPACE mysql ENCRYPTION='FALSE';
57 ERROR HY000: Invalid encryption option.
58 ALTER TABLESPACE mysql ENCRYPTION='False';
59 ERROR HY000: Invalid encryption option.
60 ALTER TABLESPACE mysql ENCRYPTION='false';
61 ERROR HY000: Invalid encryption option.
62 ALTER TABLESPACE mysql ENCRYPTION=FALSE;
63 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FALSE' at line 1
64 ALTER TABLESPACE mysql ENCRYPTION=False;
65 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'False' at line 1
66 ALTER TABLESPACE mysql ENCRYPTION=false;
67 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'false' at line 1
68 ALTER TABLESPACE mysql ENCRYPTION=0;
69 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1
70 ALTER TABLESPACE mysql ENCRYPTION=1;
71 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1
72 ALTER TABLESPACE mysql ENCRYPTION=null;
73 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 1
74 ALTER TABLESPACE mysql ENCRYPTION=-1;
75 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1
76 ALTER TABLESPACE mysql ENCRYPTION=n;
77 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'n' at line 1
78 ALTER TABLESPACE mysql ENCRYPTION=N;
79 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'N' at line 1
80 ALTER TABLESPACE mysql ENCRYPTION=y;
81 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'y' at line 1
82 ALTER TABLESPACE mysql ENCRYPTION=Y;
83 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Y' at line 1
84 ALTER TABLESPACE mysql ENCRYPTION='1';
85 ERROR HY000: Invalid encryption option.
86 ALTER TABLESPACE mysql ENCRYPTION='1True';
87 ERROR HY000: Invalid encryption option.
88 ALTER TABLESPACE mysql ENCRYPTION='@';
89 ERROR HY000: Invalid encryption option.
90 ALTER TABLESPACE mysql ENCRYPTION='null';
91 ERROR HY000: Invalid encryption option.
92 ALTER TABLESPACE mysql ENCRYPTION='';
93 ERROR HY000: Invalid encryption option.
94 ALTER TABLESPACE mysql ENCRYPTION="";
95 ERROR HY000: Invalid encryption option.
96 ----------------------------------------------------
97 ALTER MYSQL TABLESPACE WITH VALID ENCRYPTION OPTION
98 ----------------------------------------------------
99 ALTER TABLESPACE mysql ENCRYPTION='Y';
100 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
101 NAME ENCRYPTION
102 mysql Y
103 ALTER TABLESPACE mysql ENCRYPTION='y';
104 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
105 NAME ENCRYPTION
106 mysql Y
107 ALTER TABLESPACE mysql ENCRYPTION='n';
108 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
109 NAME ENCRYPTION
110 mysql N
111 ALTER TABLESPACE mysql ENCRYPTION='N';
112 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
113 NAME ENCRYPTION
114 mysql N
115 ALTER TABLESPACE mysql ENCRYPTION="Y";
116 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
117 NAME ENCRYPTION
118 mysql Y
119 ALTER TABLESPACE mysql ENCRYPTION="y";
120 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
121 NAME ENCRYPTION
122 mysql Y
123 ALTER TABLESPACE mysql ENCRYPTION="n";
124 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
125 NAME ENCRYPTION
126 mysql N
127 ALTER TABLESPACE mysql ENCRYPTION="N";
128 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
129 NAME ENCRYPTION
130 mysql N
131 -----------------------------------------------------
132 Create/Alter table using mysql.tablespace
133 -----------------------------------------------------
134 CREATE TABLE t1(i int) TABLESPACE mysql;
135 ERROR HY000: The table 't1' may not be created in the reserved tablespace 'mysql'.
136 CREATE TEMPORARY TABLE t1(i int) TABLESPACE mysql;
137 ERROR HY000: The table 't1' may not be created in the reserved tablespace 'mysql'.
138 CREATE TABLE t1(i int);
139 ALTER TABLE t1 TABLESPACE mysql;
140 ERROR HY000: The table 't1' may not be created in the reserved tablespace 'mysql'.
141 DROP TABLE t1;
142 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;
143 CREATE TABLE t1(i int) TABLESPACE encrypt_ts;
144 ALTER TABLE t1 TABLESPACE mysql;
145 ERROR HY000: The table 't1' may not be created in the reserved tablespace 'mysql'.
146 DROP TABLE t1;
147 CREATE TABLE t1(i int) TABLESPACE innodb_system;
148 ALTER TABLE t1 TABLESPACE mysql;
149 ERROR HY000: The table 't1' may not be created in the reserved tablespace 'mysql'.
150 DROP TABLE t1;
151 ALTER TABLE mysql.component TABLESPACE mysql;
152 CREATE TEMPORARY TABLE t1(i int);
153 ALTER TABLE t1 TABLESPACE mysql;
154 ERROR HY000: The table 't1' may not be created in the reserved tablespace 'mysql'.
155 DROP TABLE t1;
156 -----------------------------------------------------
157 Create view using mysql.tablespace
158 -----------------------------------------------------
159 CREATE TABLE t1(i int);
160 INSERT INTO t1 VALUES(1);
161 INSERT INTO t1 SELECT * FROM t1;
162 INSERT INTO t1 SELECT * FROM t1;
163 INSERT INTO t1 SELECT * FROM t1;
164 INSERT INTO t1 SELECT * FROM t1;
165 CREATE VIEW v1 AS SELECT * FROM t1 TABLESPACE mysql;
166 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql' at line 1
167 DROP TABLE t1;
168 ------------------------------------------------------------
169 Alter DD table part of mysql.tablespace to other tablespaces
170 ------------------------------------------------------------
171 ALTER TABLE mysql.events TABLESPACE innodb_file_per_table ENCRYPTION='Y';
172 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
173 ALTER TABLE mysql.events TABLESPACE innodb_file_per_table;
174 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
175 ALTER TABLE mysql.events TABLESPACE innodb_temporary ENCRYPTION='Y';
176 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
177 ALTER TABLE mysql.events TABLESPACE innodb_temporary;
178 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
179 ALTER TABLE mysql.events TABLESPACE innodb_system ENCRYPTION='Y';
180 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
181 ALTER TABLE mysql.events TABLESPACE innodb_system;
182 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
183 ALTER TABLE mysql.events TABLESPACE encrypt_ts ENCRYPTION='Y';
184 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
185 ALTER TABLE mysql.events TABLESPACE encrypt_ts;
186 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
187 DROP TABLESPACE encrypt_ts;
188 -------------------------------------------------------
189 Delete/truncate/drop DD table part of mysql tablespace
190 -------------------------------------------------------
191 DELETE FROM mysql.events;
192 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
193 TRUNCATE TABLE mysql.events;
194 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
195 DROP TABLE mysql.events;
196 ERROR HY000: Access to data dictionary table 'mysql.events' is rejected.
197 --------------------------------------------------
198 Alter encryption of table part of mysql tablespace
199 --------------------------------------------------
200 ALTER TABLE mysql.component ENCRYPTION='Y';
201 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
202 ALTER TABLE mysql.component ENCRYPTION='y';
203 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
204 ALTER TABLE mysql.component ENCRYPTION='N';
205 ALTER TABLE mysql.component ENCRYPTION='n';
206 ---------------------------------------------------------------------
207 Metadata for a table in mysql ts should not show encryption attribute
208 ---------------------------------------------------------------------
209 SHOW CREATE TABLE mysql.plugin;
210 Table Create Table
211 plugin CREATE TABLE `plugin` (
212 `name` varchar(64) NOT NULL DEFAULT '',
213 `dl` varchar(128) NOT NULL DEFAULT '',
214 PRIMARY KEY (`name`)
215 ) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='MySQL plugins'
216 -------------------
217 Drop mysql database
218 -------------------
219 DROP DATABASE mysql;
220 ERROR HY000: Access to system schema 'mysql' is rejected.
221 -----------------------------------------------------
222 Other DDL operation not allowed on 'mysql' tablespace
223 -----------------------------------------------------
224 CREATE TABLESPACE mysql ADD DATAFILE 'mysql.ibd' ENGINE=INNODB;
225 ERROR 42000: InnoDB: `mysql` is a reserved tablespace name.
226 DROP TABLESPACE mysql;
227 ERROR 42000: InnoDB: `mysql` is a reserved tablespace name.
228 ALTER TABLESPACE mysql RENAME TO xyz;
229 ERROR 42000: InnoDB: `mysql` is a reserved tablespace name.
230 ALTER TABLESPACE mysql ENGINE=myisam;
231 ERROR HY000: Engine 'myisam' does not match stored engine 'InnoDB' for tablespace 'mysql'
232 ALTER TABLESPACE mysql ENGINE=memory;
233 ERROR HY000: Engine 'memory' does not match stored engine 'InnoDB' for tablespace 'mysql'
234 #########################################################################
235 # Restart with same keyring option
236 # - tables in mysql ts should be accessible
237 #########################################################################
238 ALTER TABLESPACE mysql ENCRYPTION='Y';
239 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
240 NAME ENCRYPTION
241 mysql Y
242 SELECT help_keyword_id FROM mysql.help_keyword ORDER BY help_keyword_id LIMIT 2;
243 help_keyword_id
244 0
245 1
246 ALTER TABLESPACE mysql ENCRYPTION='N';
247 ALTER TABLESPACE mysql ENCRYPTION='Y';
248 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
249 NAME ENCRYPTION
250 mysql Y
251 ALTER INSTANCE ROTATE INNODB MASTER KEY;
252 ALTER TABLESPACE mysql ENCRYPTION='N';
253 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='mysql';
254 NAME ENCRYPTION
255 mysql N
256 # Set Encryption process to wait after page 5 so that we can monitor
257 # progress in performance_schema table
258 SET DEBUG_SYNC = 'alter_encrypt_tablespace_wait_after_page5 SIGNAL s1 WAIT_FOR s2';
259 ALTER TABLESPACE mysql ENCRYPTION='Y';
260 # Monitoring connection
261 SET DEBUG_SYNC = 'now WAIT_FOR s1';
262 # Wait for Encryption progress monitoring to appear in PFS table
263 # Wait for some progress to appear in PFS table
264 select WORK_COMPLETED
265 FROM performance_schema.events_stages_current
266 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)';
267 WORK_COMPLETED
268 5
269 SET DEBUG_SYNC = 'now SIGNAL s2';
270 # Default connection
271 # Once done, select count from PFS tables
272 SELECT COUNT(*)
273 FROM performance_schema.events_stages_current
274 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
275 COUNT(*)
276 0
277 SELECT COUNT(*)
278 FROM performance_schema.events_stages_history
279 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
280 COUNT(*)
281 0
282 SELECT COUNT(*)
283 FROM performance_schema.events_stages_history_long
284 WHERE EVENT_NAME='stage/innodb/alter tablespace (encryption)';
285 COUNT(*)
286 3
287 SELECT COUNT(*)
288 FROM performance_schema.events_stages_summary_global_by_event_name
289 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
290 COUNT_STAR>0;
291 COUNT(*)
292 1
293 COUNT(*)
294 1
295 SELECT COUNT(*)
296 FROM performance_schema.events_stages_summary_by_user_by_event_name
297 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
298 COUNT_STAR>0;
299 COUNT(*)
300 1
301 SELECT COUNT(*)
302 FROM performance_schema.events_stages_summary_by_host_by_event_name
303 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
304 COUNT_STAR>0;
305 COUNT(*)
306 1
307 SELECT COUNT(*)
308 FROM performance_schema.events_stages_summary_by_account_by_event_name
309 WHERE EVENT_NAME = 'stage/innodb/alter tablespace (encryption)' AND
310 COUNT_STAR>0;
311 COUNT(*)
312 1
313 # Check that Encryption done successfully.
314 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
315 WHERE NAME='mysql';
316 NAME ENCRYPTION
317 mysql Y
318 SELECT help_keyword_id FROM mysql.help_keyword ORDER BY help_keyword_id LIMIT 2;
319 help_keyword_id
320 0
321 1
322 ###########
323 # Cleanup #
324 ###########
325 ALTER TABLESPACE mysql ENCRYPTION='N';
326 SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='mysql';
327 NAME ENCRYPTION
328 mysql N
329 # ----------------------------------------------------------------------
330 # Teardown
331 # Removing manifest file for current MySQL server instance
332 # Removing local keyring file for keyring component: component_keyring_file
333 # Removing local configuration file for keyring component: component_keyring_file
334 # Restarting server without the manifest file
335 # ----------------------------------------------------------------------