Debian Codegraph @

mysql-test/suite/component_keyring_file/r/tablespace_encrypt_6.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 # RESTART 1 : WITH KEYRING
9 #########################################################################
10 #########
11 # SETUP #
12 #########
13 CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION ='Y' ENGINE=InnoDB;
14 CREATE TABLESPACE encrypt_ts1 ADD DATAFILE 'encrypt_ts1.ibd' ENCRYPTION ='N' ENGINE=InnoDB;
15 DROP DATABASE IF EXISTS tde_db;
16 DROP TABLE IF EXISTS tde_db. t_encrypt;
17 CREATE DATABASE tde_db;
18 USE tde_db;
19 CREATE PROCEDURE tde_db.init_setup()
20 begin
21 /* Create table in encrypted tablespace */
22 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
23 c3 CHAR(255) Default 'No text',
24 c4 JSON ,
25 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
26 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
27 c7 POINT NOT NULL SRID 0,
28 spatial INDEX idx2 (c7)
29 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
30 /* Create table in non-encrypted tablesapce */
31 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
32 c3 CHAR(255) Default 'No text',
33 c4 JSON ,
34 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
35 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
36 c7 POINT NOT NULL SRID 0,
37 spatial INDEX idx2 (c7)
38 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
39 /* insert into encrypted table */
40 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES(CONCAT(REPEAT('a',200),LPAD(CAST(1 AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
41 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
42 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
43 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
44 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
45 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
46 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
47 SELECT '/* select tde_db.t_encrypt */';
48 SELECT COUNT(*) FROM tde_db.t_encrypt;
49 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
50 /* insert into non encrypted table */
51 INSERT INTO tde_db.t_non_encrypt(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
52 SELECT '/* select tde_db.t_non_encrypt */';
53 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
54 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
55 ALTER INSTANCE ROTATE INNODB MASTER KEY;
56 CREATE TABLE tde_db.t_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
57 c3 CHAR(255) Default 'No text',
58 c4 JSON ,
59 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
60 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
61 c7 POINT NOT NULL SRID 0,
62 spatial INDEX idx2 (c7)
63 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
64 CREATE TABLE tde_db.t_non_encrypt_2(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
65 c3 CHAR(255) Default 'No text',
66 c4 JSON ,
67 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
68 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
69 c7 POINT NOT NULL SRID 0,
70 spatial INDEX idx2 (c7)
71 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
72 /* insert into encrypted table 2 */
73 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
74 SELECT '/* select tde_db.t_encrypt_2 */';
75 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
76 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
77 /* insert into NON encrypted table 2 */
78 INSERT INTO tde_db.t_non_encrypt_2(c2,c3,c4,c7) SELECT c2,c3,c4,c7 FROM tde_db.t_encrypt;
79 SELECT '/* select tde_db.t_non_encrypt_2 */';
80 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
81 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
82 end|
83 #-----------------------------------------------------------------------
84 # Initialize tables
85 call tde_db.init_setup();
86 /* select tde_db.t_encrypt */
87 /* select tde_db.t_encrypt */
88 COUNT(*)
89 64
90 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
91 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
92 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
93 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
94 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
95 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
96 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
97 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
98 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
99 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
100 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
101 /* select tde_db.t_non_encrypt */
102 /* select tde_db.t_non_encrypt */
103 COUNT(*)
104 64
105 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
106 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
107 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
108 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
109 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
110 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
111 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
112 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
113 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
114 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
115 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
116 /* select tde_db.t_encrypt_2 */
117 /* select tde_db.t_encrypt_2 */
118 COUNT(*)
119 64
120 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
121 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
122 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
123 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
124 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
125 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
126 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
127 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
128 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
129 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
130 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
131 /* select tde_db.t_non_encrypt_2 */
132 /* select tde_db.t_non_encrypt_2 */
133 COUNT(*)
134 64
135 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
136 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
137 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
138 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
139 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
140 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
141 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
142 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
143 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
144 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
145 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
146 SELECT status_key, status_value FROM
147 performance_schema.keyring_component_status
148 WHERE status_key LIKE '%name%' OR status_key LIKE '%status%';
149 status_key status_value
150 Component_name component_keyring_file
151 Implementation_name component_keyring_file
152 Component_status Active
153 # Taking backup of local keyring file for keyring component: component_keyring_file
154 ALTER INSTANCE RELOAD KEYRING;
155 # Select non encrypted tables : Pass
156 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
157 COUNT(*)
158 64
159 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
160 COUNT(*)
161 64
162 # Select encrypted tables : No Error (-master key is cached)
163 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
164 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
165 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
166 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
167 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
168 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
169 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
170 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
171 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
172 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
173 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
174 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
175 SELECT c2 ,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
176 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
177 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
178 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
179 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
180 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
181 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
182 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
183 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
184 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
185 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
186 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
187 SELECT COUNT(*) FROM tde_db.t_encrypt;
188 COUNT(*)
189 64
190 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
191 COUNT(*)
192 64
193 # Error on "ALTER INSTANCE ..."
194 ALTER INSTANCE ROTATE INNODB MASTER KEY;
195 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
196 # new table creation in encrypted tablespace is allowed after uninstall
197 CREATE TABLE tde_db.t_encrypt_3(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
198 c3 CHAR(255) Default 'No text',
199 c4 JSON ,
200 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
201 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
202 c7 POINT NOT NULL SRID 0,
203 spatial INDEX idx2 (c7)
204 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
205 # new table in non-encrypted tablespace is allowed after uninstall
206 CREATE TABLE tde_db.t_non_encrypt_3(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
207 c3 CHAR(255) Default 'No text',
208 c4 JSON ,
209 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
210 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
211 c7 POINT NOT NULL SRID 0,
212 spatial INDEX idx2 (c7)
213 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
214 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_3 ;
215 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_3;
216 #########################################################################
217 # Test 1 : Restart with same keyring option
218 # - all tables accessible
219 #########################################################################
220 # Restoring local keyring file from backup for keyring component: component_keyring_file
221 ALTER INSTANCE RELOAD KEYRING;
222 SELECT status_key, status_value FROM
223 performance_schema.keyring_component_status
224 WHERE status_key LIKE '%name%' OR status_key LIKE '%status%';
225 status_key status_value
226 Component_name component_keyring_file
227 Implementation_name component_keyring_file
228 Component_status Active
229 # Initialize tables
230 call tde_db.init_setup();
231 /* select tde_db.t_encrypt */
232 /* select tde_db.t_encrypt */
233 COUNT(*)
234 64
235 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
236 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
237 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
238 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
239 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
240 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
241 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
242 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
243 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
244 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
245 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
246 /* select tde_db.t_non_encrypt */
247 /* select tde_db.t_non_encrypt */
248 COUNT(*)
249 64
250 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
251 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
252 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
253 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
254 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
255 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
256 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
257 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
258 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
259 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
260 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
261 /* select tde_db.t_encrypt_2 */
262 /* select tde_db.t_encrypt_2 */
263 COUNT(*)
264 64
265 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
266 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
267 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
268 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
269 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
270 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
271 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
272 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
273 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
274 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
275 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
276 /* select tde_db.t_non_encrypt_2 */
277 /* select tde_db.t_non_encrypt_2 */
278 COUNT(*)
279 64
280 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
281 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
282 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
283 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
284 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
285 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
286 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
287 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
288 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
289 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
290 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
291 # restart with same keyring option
292 # All tables must be accessible
293 SELECT COUNT(*) FROM tde_db.t_encrypt;
294 COUNT(*)
295 64
296 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
297 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
298 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
299 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
300 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
301 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
302 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
303 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
304 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
305 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
306 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
307 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
308 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
309 COUNT(*)
310 64
311 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
312 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
313 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
314 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
315 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
316 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
317 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
318 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
319 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
320 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
321 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
322 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
323 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
324 COUNT(*)
325 64
326 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
327 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
328 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
329 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
330 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
331 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
332 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
333 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
334 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
335 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
336 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
337 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
338 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
339 COUNT(*)
340 64
341 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
342 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
343 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
344 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
345 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
346 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
347 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
348 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
349 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
350 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
351 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
352 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
353 # Insert into old encrypted tables
354 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
355 COUNT(*)
356 64
357 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
358 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
359 COUNT(*)
360 128
361 # Insert into old non encrypted tables
362 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
363 COUNT(*)
364 64
365 INSERT INTO tde_db.t_non_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
366 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
367 COUNT(*)
368 128
369 # Update into old encrypted tables
370 UPDATE tde_db.t_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
371 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
372 COUNT(*)
373 1
374 # Update into old non encrypted tables
375 UPDATE tde_db.t_non_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
376 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
377 COUNT(*)
378 1
379 # Delete from old encrypted tables
380 DELETE FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
381 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
382 COUNT(*)
383 0
384 # Delete from old non encrypted tables
385 DELETE FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
386 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
387 COUNT(*)
388 0
389 # Create new tables
390 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
391 c3 CHAR(255) Default 'No text',
392 c4 JSON ,
393 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
394 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
395 c7 POINT NOT NULL SRID 0,
396 spatial INDEX idx2 (c7)
397 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
398 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
399 c3 CHAR(255) Default 'No text',
400 c4 JSON ,
401 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
402 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
403 c7 POINT NOT NULL SRID 0,
404 spatial INDEX idx2 (c7)
405 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
406 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
407 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
408 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
409 COUNT(*)
410 64
411 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_4 LIMIT 10;
412 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
413 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
414 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
415 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
416 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
417 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
418 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
419 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
420 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
421 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
422 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
423 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
424 COUNT(*)
425 64
426 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_4 LIMIT 10;
427 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
428 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
429 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
430 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
431 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
432 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
433 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
434 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
435 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
436 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
437 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
438 ALTER INSTANCE ROTATE INNODB MASTER KEY;
439 SELECT COUNT(*) FROM tde_db.t_encrypt;
440 COUNT(*)
441 64
442 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
443 COUNT(*)
444 64
445 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
446 COUNT(*)
447 127
448 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
449 COUNT(*)
450 127
451 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
452 COUNT(*)
453 64
454 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
455 COUNT(*)
456 64
457 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_4;
458 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_4;
459 #########################################################################
460 # Test 2 : Restart without keyring option
461 # - old encrypted tables accessible
462 # - unencryption tables are accessible
463 #########################################################################
464 SELECT status_key, status_value FROM
465 performance_schema.keyring_component_status
466 WHERE status_key LIKE '%name%' OR status_key LIKE '%status%';
467 status_key status_value
468 Component_name component_keyring_file
469 Implementation_name component_keyring_file
470 Component_status Active
471 # Initialize tables
472 call tde_db.init_setup();
473 /* select tde_db.t_encrypt */
474 /* select tde_db.t_encrypt */
475 COUNT(*)
476 64
477 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
478 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
479 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
480 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
481 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
482 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
483 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
484 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
485 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
486 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
487 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
488 /* select tde_db.t_non_encrypt */
489 /* select tde_db.t_non_encrypt */
490 COUNT(*)
491 64
492 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
493 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
494 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
495 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
496 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
497 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
498 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
499 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
500 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
501 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
502 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
503 /* select tde_db.t_encrypt_2 */
504 /* select tde_db.t_encrypt_2 */
505 COUNT(*)
506 64
507 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
508 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
509 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
510 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
511 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
512 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
513 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
514 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
515 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
516 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
517 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
518 /* select tde_db.t_non_encrypt_2 */
519 /* select tde_db.t_non_encrypt_2 */
520 COUNT(*)
521 64
522 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
523 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
524 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
525 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
526 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
527 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
528 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
529 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
530 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
531 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
532 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
533 # restart without keyring
534 # Taking backup of local manifest file for MySQL server instance
535 # Encrypted tables must not be accessible
536 SELECT COUNT(*) FROM tde_db.t_encrypt;
537 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
538 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
539 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
540 # Unencrypted tables must be accessible
541 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
542 COUNT(*)
543 64
544 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
545 COUNT(*)
546 64
547 # Creating table in encrypted tablespace must not be possible as tablespace
548 # would be missing
549 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
550 c3 CHAR(255) Default 'No text',
551 c4 JSON ,
552 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
553 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
554 c7 POINT NOT NULL SRID 0,
555 spatial INDEX idx2 (c7)
556 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
557 ERROR HY000: InnoDB: A general tablespace named `encrypt_ts` cannot be found.
558 # Creating table in unencrypted tablespace must be possible
559 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
560 c3 CHAR(255) Default 'No text',
561 c4 JSON ,
562 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
563 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
564 c7 POINT NOT NULL SRID 0,
565 spatial INDEX idx2 (c7)
566 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
567 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
568 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
569 COUNT(*)
570 64
571 ALTER INSTANCE ROTATE INNODB MASTER KEY;
572 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
573 # Encrypted tables must not be accessible
574 SELECT COUNT(*) FROM tde_db.t_encrypt;
575 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
576 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
577 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
578 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2;
579 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 ,tde_db.t_non_encrypt_4;
580 #########################################################################
581 # Test 3 : Restart and reload keyring using command
582 # - All tables must be accessible
583 #########################################################################
584 # Restore local manifest file for MySQL server instance from backup
585 SELECT status_key, status_value FROM
586 performance_schema.keyring_component_status
587 WHERE status_key LIKE '%name%' OR status_key LIKE '%status%';
588 status_key status_value
589 Component_name component_keyring_file
590 Implementation_name component_keyring_file
591 Component_status Active
592 # Initalize tables
593 call tde_db.init_setup();
594 /* select tde_db.t_encrypt */
595 /* select tde_db.t_encrypt */
596 COUNT(*)
597 64
598 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
599 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
600 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
601 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
602 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
603 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
604 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
605 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
606 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
607 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
608 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
609 /* select tde_db.t_non_encrypt */
610 /* select tde_db.t_non_encrypt */
611 COUNT(*)
612 64
613 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
614 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
615 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
616 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
617 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
618 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
619 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
620 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
621 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
622 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
623 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
624 /* select tde_db.t_encrypt_2 */
625 /* select tde_db.t_encrypt_2 */
626 COUNT(*)
627 64
628 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
629 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
630 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
631 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
632 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
633 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
634 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
635 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
636 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
637 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
638 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
639 /* select tde_db.t_non_encrypt_2 */
640 /* select tde_db.t_non_encrypt_2 */
641 COUNT(*)
642 64
643 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
644 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
645 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
646 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
647 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
648 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
649 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
650 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
651 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
652 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
653 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
654 # Taking backup of local keyring file for keyring component: component_keyring_file
655 # restart without keyring
656 # Restoring local keyring file from backup for keyring component: component_keyring_file
657 ALTER INSTANCE RELOAD KEYRING;
658 # All tables must be accessible
659 SELECT COUNT(*) FROM tde_db.t_encrypt;
660 COUNT(*)
661 64
662 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
663 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
664 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
665 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
666 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
667 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
668 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
669 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
670 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
671 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
672 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
673 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
674 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
675 COUNT(*)
676 64
677 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
678 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
679 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
680 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
681 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
682 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
683 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
684 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
685 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
686 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
687 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
688 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
689 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
690 COUNT(*)
691 64
692 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_2 LIMIT 10;
693 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
694 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
695 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
696 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
697 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
698 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
699 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
700 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
701 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
702 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
703 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
704 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
705 COUNT(*)
706 64
707 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_2 LIMIT 10;
708 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
709 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
710 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
711 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
712 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
713 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
714 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
715 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
716 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
717 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
718 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
719 # Insert into old encrypted tables
720 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
721 COUNT(*)
722 64
723 INSERT INTO tde_db.t_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
724 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
725 COUNT(*)
726 128
727 # Insert into old non encrypted tables
728 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
729 COUNT(*)
730 64
731 INSERT INTO tde_db.t_non_encrypt_2(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
732 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
733 COUNT(*)
734 128
735 # Update into old encrypted tables
736 UPDATE tde_db.t_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
737 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
738 COUNT(*)
739 1
740 # Update into old non encrypted tables
741 UPDATE tde_db.t_non_encrypt_2 SET c2 = 1000 WHERE c2 = 1;
742 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
743 COUNT(*)
744 1
745 # Delete from old encrypted tables
746 DELETE FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
747 SELECT COUNT(*) FROM tde_db.t_encrypt_2 WHERE c2 = 1000 ;
748 COUNT(*)
749 0
750 # Delete from old non encrypted tables
751 DELETE FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
752 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2 WHERE c2 = 1000 ;
753 COUNT(*)
754 0
755 # Create new tables
756 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
757 c3 CHAR(255) Default 'No text',
758 c4 JSON ,
759 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
760 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
761 c7 POINT NOT NULL SRID 0,
762 spatial INDEX idx2 (c7)
763 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
764 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
765 c3 CHAR(255) Default 'No text',
766 c4 JSON ,
767 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
768 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
769 c7 POINT NOT NULL SRID 0,
770 spatial INDEX idx2 (c7)
771 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
772 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
773 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
774 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
775 COUNT(*)
776 64
777 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt_4 LIMIT 10;
778 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
779 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
780 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
781 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
782 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
783 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
784 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
785 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
786 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
787 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
788 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
789 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
790 COUNT(*)
791 64
792 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt_4 LIMIT 10;
793 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
794 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
795 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
796 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
797 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
798 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
799 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
800 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
801 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
802 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
803 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
804 ALTER INSTANCE ROTATE INNODB MASTER KEY;
805 SELECT COUNT(*) FROM tde_db.t_encrypt;
806 COUNT(*)
807 64
808 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
809 COUNT(*)
810 64
811 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
812 COUNT(*)
813 127
814 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
815 COUNT(*)
816 127
817 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
818 COUNT(*)
819 64
820 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
821 COUNT(*)
822 64
823 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 , tde_db.t_encrypt_4;
824 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 , tde_db.t_non_encrypt_4;
825 # Taking backup of local keyring file for keyring component: component_keyring_file
826 ALTER INSTANCE RELOAD KEYRING;
827 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
828 c3 CHAR(255) Default 'No text',
829 c4 JSON ,
830 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
831 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
832 c7 POINT NOT NULL SRID 0,
833 spatial INDEX idx2 (c7)
834 ) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
835 ALTER INSTANCE ROTATE INNODB MASTER KEY;
836 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
837 DROP TABLE tde_db.t_encrypt_4;
838 # Restoring local keyring file from backup for keyring component: component_keyring_file
839 ALTER INSTANCE RELOAD KEYRING;
840 #########################################################################
841 # Test 4 : Restart with new keyring_data_file
842 # - Old encrypted tables not accessible
843 # - Non encrypted tables accessible
844 # - Creation of new tables in encrypted/unencrypted tablespace
845 # is also posible
846 #########################################################################
847 # init tables
848 call tde_db.init_setup();
849 /* select tde_db.t_encrypt */
850 /* select tde_db.t_encrypt */
851 COUNT(*)
852 64
853 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
854 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
855 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
856 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
857 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
858 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
859 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
860 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
861 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
862 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
863 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
864 /* select tde_db.t_non_encrypt */
865 /* select tde_db.t_non_encrypt */
866 COUNT(*)
867 64
868 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
869 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
870 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
871 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
872 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
873 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
874 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
875 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
876 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
877 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
878 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
879 /* select tde_db.t_encrypt_2 */
880 /* select tde_db.t_encrypt_2 */
881 COUNT(*)
882 64
883 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
884 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
885 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
886 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
887 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
888 5 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
889 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
890 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
891 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
892 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
893 10 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
894 /* select tde_db.t_non_encrypt_2 */
895 /* select tde_db.t_non_encrypt_2 */
896 COUNT(*)
897 64
898 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
899 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
900 2 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
901 3 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
902 4 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
903 6 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
904 7 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
905 8 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
906 9 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
907 13 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
908 14 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
909 # Taking backup of local keyring file for keyring component: component_keyring_file
910 # restart with with different keyring file
911 # old encrypted tables not accessible
912 SELECT COUNT(*) FROM tde_db.t_encrypt;
913 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
914 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
915 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
916 # NON encrypted tables accessible
917 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
918 COUNT(*)
919 64
920 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
921 COUNT(*)
922 64
923 # new encrypted tablespace possible
924 CREATE TABLESPACE encrypt_ts2 ADD DATAFILE 'encrypt_ts2.ibd' ENCRYPTION ='Y' ENGINE=InnoDB;
925 # new table in new encrypted tablespace possible
926 CREATE TABLE tde_db.t_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
927 c3 CHAR(255) Default 'No text',
928 c4 JSON ,
929 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
930 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
931 c7 POINT NOT NULL SRID 0,
932 spatial INDEX idx2 (c7)
933 ) TABLESPACE encrypt_ts2 ENCRYPTION='Y' ENGINE = InnoDB;
934 # new table in non encrypted tablespace possible
935 CREATE TABLE tde_db.t_non_encrypt_4(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
936 c3 CHAR(255) Default 'No text',
937 c4 JSON ,
938 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
939 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
940 c7 POINT NOT NULL SRID 0,
941 spatial INDEX idx2 (c7)
942 ) TABLESPACE=encrypt_ts1 ENGINE = InnoDB;
943 INSERT INTO tde_db.t_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
944 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
945 COUNT(*)
946 64
947 INSERT INTO tde_db.t_non_encrypt_4(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_non_encrypt;
948 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
949 COUNT(*)
950 64
951 ALTER INSTANCE ROTATE INNODB MASTER KEY;
952 # Old encrypted tables in encrypted tablespace must not be accessible
953 SELECT COUNT(*) FROM tde_db.t_encrypt;
954 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
955 SELECT COUNT(*) FROM tde_db.t_encrypt_2;
956 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
957 # Old unencrypted table must be accessible
958 SELECT COUNT(*) FROM tde_db.t_non_encrypt;
959 COUNT(*)
960 64
961 SELECT COUNT(*) FROM tde_db.t_non_encrypt_2;
962 COUNT(*)
963 64
964 # New encrypted tables must be accessible
965 SELECT COUNT(*) FROM tde_db.t_encrypt_4;
966 COUNT(*)
967 64
968 # New unencrypted tables must be accessible
969 SELECT COUNT(*) FROM tde_db.t_non_encrypt_4;
970 COUNT(*)
971 64
972 ###########
973 # CLEANUP #
974 ###########
975 DROP TABLE tde_db.t_encrypt , tde_db.t_encrypt_2 ,tde_db.t_encrypt_4;
976 DROP TABLE tde_db.t_non_encrypt , tde_db.t_non_encrypt_2 ,tde_db.t_non_encrypt_4;
977 DROP DATABASE tde_db;
978 DROP TABLESPACE encrypt_ts;
979 DROP TABLESPACE encrypt_ts1;
980 DROP TABLESPACE encrypt_ts2;
981 # Restoring local keyring file from backup for keyring component: component_keyring_file
982 # ----------------------------------------------------------------------
983 # Teardown
984 # Removing manifest file for current MySQL server instance
985 # Removing local keyring file for keyring component: component_keyring_file
986 # Removing local configuration file for keyring component: component_keyring_file
987 # Restarting server without the manifest file
988 # ----------------------------------------------------------------------