Debian Codegraph @

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