Debian Codegraph @

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

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