Debian Codegraph @

mysql-test/suite/component_keyring_file/r/table_encrypt_3.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 # Taking backup of local manifest file for MySQL server instance
8 # Create encrypt table before loading keyring
9 SET GLOBAL innodb_file_per_table = 1;
10 SELECT @@innodb_file_per_table;
11 @@innodb_file_per_table
12 1
13 DROP TABLE IF EXISTS t_encrypt;
14 CREATE TABLE t_encrypt(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
15 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
16 ALTER INSTANCE ROTATE INNODB MASTER KEY;
17 ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring is loaded and initialized successfully.
18 # Starting server with keyring
19 # Restore local manifest file for MySQL server instance from backup
20 DROP DATABASE IF EXISTS tde_db;
21 DROP TABLE IF EXISTS tde_db.t_encrypt;
22 CREATE DATABASE tde_db;
23 SET GLOBAL innodb_file_per_table = 1;
24 SELECT @@innodb_file_per_table;
25 @@innodb_file_per_table
26 1
27 CREATE TABLE tde_db.t_encrypt(c4 JSON ,
28 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
29 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
30 c7 POINT NOT NULL SRID 0,
31 spatial INDEX idx2 (c7)
32 ) ENCRYPTION="Y" ENGINE = InnoDB;
33 SHOW CREATE TABLE tde_db.t_encrypt;
34 Table Create Table
35 t_encrypt CREATE TABLE `t_encrypt` (
36 `c4` json DEFAULT NULL,
37 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
38 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
39 `c7` point NOT NULL /*!80003 SRID 0 */,
40 SPATIAL KEY `idx2` (`c7`)
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
42 INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
43 INSERT INTO tde_db.t_encrypt(c4,c7) select c4,c7 from tde_db.t_encrypt;
44 SELECT c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
45 c4 c5 ST_AsText(c7)
46 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
47 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
48 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
49 c4 c5 c6 ST_AsText(c7)
50 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
51 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
52 SHOW CREATE TABLE tde_db.t_encrypt;
53 Table Create Table
54 t_encrypt CREATE TABLE `t_encrypt` (
55 `c4` json DEFAULT NULL,
56 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
57 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
58 `c7` point NOT NULL /*!80003 SRID 0 */,
59 SPATIAL KEY `idx2` (`c7`)
60 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
61 # In connection 1
62 LOCK TABLES tde_db.t_encrypt WRITE;
63 # In connection default
64 SHOW OPEN TABLES LIKE 't_encrypt';
65 Database Table In_use Name_locked
66 tde_db t_encrypt 1 0
67 "ALTER INSTANCE.." do not conflict with "LOCK TABLE .." COMMAND
68 ALTER INSTANCE ROTATE INNODB MASTER KEY;
69 # In connection 1
70 INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
71 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
72 c4 c5 c6 ST_AsText(c7)
73 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
74 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
75 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
76 UNLOCK TABLES;
77 # In connection default
78 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
79 c4 c5 c6 ST_AsText(c7)
80 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
81 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
82 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
83 SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
84 c4 c5 c6 ST_AsText(c7)
85 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
86 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
87 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
88 DROP DATABASE tde_db;
89 SET GLOBAL innodb_file_per_table=1;
90 DROP DATABASE IF EXISTS tde_db;
91 CREATE DATABASE tde_db;
92 USE tde_db;
93 DROP TABLE IF EXISTS tde_db.t_encrypt;
94 DROP TABLE IF EXISTS tde_db.t_encrypt_1;
95 SET GLOBAL innodb_file_per_table = 1;
96 SELECT @@innodb_file_per_table;
97 @@innodb_file_per_table
98 1
99 CREATE TABLE tde_db.t_encrypt(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
100 CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20)) ENCRYPTION="Yes" ENGINE = InnoDB;
101 ERROR HY000: Invalid encryption option.
102 CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20)) ENCRYPTION="y" ENGINE = InnoDB;
103 DROP TABLE tde_db.t_encrypt_1;
104 CREATE TABLE tde_db.t_encrypt_1(c1 INT, c2 char(20),c3 BLOB) ENGINE = InnoDB;
105 SHOW CREATE TABLE tde_db.t_encrypt;
106 Table Create Table
107 t_encrypt CREATE TABLE `t_encrypt` (
108 `c1` int DEFAULT NULL,
109 `c2` char(20) DEFAULT NULL,
110 `c3` blob
111 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
112 INSERT INTO tde_db.t_encrypt VALUES(0, "aaaaa",repeat('A', 20000));
113 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
114 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
115 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
116 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
117 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
118 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
119 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
120 c1 c2 right(c3, 20)
121 0 aaaaa AAAAAAAAAAAAAAAAAAAA
122 0 aaaaa AAAAAAAAAAAAAAAAAAAA
123 0 aaaaa AAAAAAAAAAAAAAAAAAAA
124 0 aaaaa AAAAAAAAAAAAAAAAAAAA
125 0 aaaaa AAAAAAAAAAAAAAAAAAAA
126 0 aaaaa AAAAAAAAAAAAAAAAAAAA
127 0 aaaaa AAAAAAAAAAAAAAAAAAAA
128 0 aaaaa AAAAAAAAAAAAAAAAAAAA
129 0 aaaaa AAAAAAAAAAAAAAAAAAAA
130 0 aaaaa AAAAAAAAAAAAAAAAAAAA
131 #Insert into non encrypted table
132 INSERT INTO tde_db.t_encrypt_1 SELECT * FROM tde_db.t_encrypt;
133 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt_1 LIMIT 10;
134 c1 c2 right(c3, 20)
135 0 aaaaa AAAAAAAAAAAAAAAAAAAA
136 0 aaaaa AAAAAAAAAAAAAAAAAAAA
137 0 aaaaa AAAAAAAAAAAAAAAAAAAA
138 0 aaaaa AAAAAAAAAAAAAAAAAAAA
139 0 aaaaa AAAAAAAAAAAAAAAAAAAA
140 0 aaaaa AAAAAAAAAAAAAAAAAAAA
141 0 aaaaa AAAAAAAAAAAAAAAAAAAA
142 0 aaaaa AAAAAAAAAAAAAAAAAAAA
143 0 aaaaa AAAAAAAAAAAAAAAAAAAA
144 0 aaaaa AAAAAAAAAAAAAAAAAAAA
145 SELECT COUNT(*) FROM tde_db.t_encrypt_1;
146 COUNT(*)
147 64
148 # Starting server with keyring restart with keying
149 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
150 c1 c2 right(c3, 20)
151 0 aaaaa AAAAAAAAAAAAAAAAAAAA
152 0 aaaaa AAAAAAAAAAAAAAAAAAAA
153 0 aaaaa AAAAAAAAAAAAAAAAAAAA
154 0 aaaaa AAAAAAAAAAAAAAAAAAAA
155 0 aaaaa AAAAAAAAAAAAAAAAAAAA
156 0 aaaaa AAAAAAAAAAAAAAAAAAAA
157 0 aaaaa AAAAAAAAAAAAAAAAAAAA
158 0 aaaaa AAAAAAAAAAAAAAAAAAAA
159 0 aaaaa AAAAAAAAAAAAAAAAAAAA
160 0 aaaaa AAAAAAAAAAAAAAAAAAAA
161 SELECT COUNT(*) FROM tde_db.t_encrypt;
162 COUNT(*)
163 64
164 #check non encrypted table
165 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt_1 LIMIT 10;
166 c1 c2 right(c3, 20)
167 0 aaaaa AAAAAAAAAAAAAAAAAAAA
168 0 aaaaa AAAAAAAAAAAAAAAAAAAA
169 0 aaaaa AAAAAAAAAAAAAAAAAAAA
170 0 aaaaa AAAAAAAAAAAAAAAAAAAA
171 0 aaaaa AAAAAAAAAAAAAAAAAAAA
172 0 aaaaa AAAAAAAAAAAAAAAAAAAA
173 0 aaaaa AAAAAAAAAAAAAAAAAAAA
174 0 aaaaa AAAAAAAAAAAAAAAAAAAA
175 0 aaaaa AAAAAAAAAAAAAAAAAAAA
176 0 aaaaa AAAAAAAAAAAAAAAAAAAA
177 SELECT COUNT(*) FROM tde_db.t_encrypt_1;
178 COUNT(*)
179 64
180 DROP TABLE tde_db.t_encrypt;
181 DROP TABLE tde_db.t_encrypt_1;
182 SET GLOBAL innodb_file_per_table=1;
183 DROP DATABASE IF EXISTS tde_db;
184 DROP TABLE IF EXISTS tde_db.t_encrypt;
185 CREATE DATABASE tde_db;
186 USE tde_db;
187 # File per table is set 0. Encryption not possible.
188 SET GLOBAL innodb_file_per_table = 0;
189 SELECT @@innodb_file_per_table;
190 @@innodb_file_per_table
191 0
192 CREATE TABLE tde_db.t_encrypt(c4 JSON ,
193 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
194 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
195 c7 POINT NOT NULL SRID 0,
196 spatial INDEX idx2 (c7)
197 ) ENCRYPTION="Y" ENGINE = InnoDB;
198 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
199 SHOW WARNINGS;
200 Level Code Message
201 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
202 SET GLOBAL innodb_file_per_table = 1;
203 SELECT @@innodb_file_per_table;
204 @@innodb_file_per_table
205 1
206 CREATE TABLESPACE s_alt1 ADD DATAFILE 's_alt1.ibd';
207 CREATE TABLE tde_db.t_encrypt (a int, b text) ENCRYPTION="Y" TABLESPACE=`s_alt1` ENGINE=InnoDB;
208 ERROR HY000: Request to create 'encrypted' table while using an 'unencrypted' tablespace.
209 SHOW WARNINGS;
210 Level Code Message
211 Error 3825 Request to create 'encrypted' table while using an 'unencrypted' tablespace.
212 DROP TABLESPACE s_alt1;
213 CREATE TEMPORARY TABLE tde_db.t_encrypt (a int, b text) ENCRYPTION="Y" ENGINE=InnoDB;
214 ERROR HY000: ENCRYPTION clause is not valid for temporary tablespace.
215 SHOW WARNINGS;
216 Level Code Message
217 Error 3833 ENCRYPTION clause is not valid for temporary tablespace.
218 CREATE TABLE tde_db.t_encrypt_myisam (a int, b text) ENCRYPTION="Y" ENGINE=MyISAM;
219 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
220 CREATE PROCEDURE tde_db.row_format_t_encrypt(row_form VARCHAR(1000))
221 begin
222 declare i int default 1;
223 declare has_error int default 0;
224 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
225 DROP TABLE IF EXISTS tde_db.t_encrypt;
226 SET @sql_text = CONCAT('CREATE TABLE tde_db.t_encrypt ('," c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,c7 POINT NOT NULL SRID 0,spatial INDEX idx2 (c7) ) ", ' ENCRYPTION="Y" ', row_form ,' ENGINE=InnoDB');
227 PREPARE stmt FROM @sql_text;
228 EXECUTE stmt;
229 DEALLOCATE PREPARE stmt;
230 SHOW CREATE TABLE tde_db.t_encrypt;
231 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (REPEAT('a',200),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
232 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
233 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
234 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
235 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
236 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
237 SELECT COUNT(*) FROM tde_db.t_encrypt;
238 SELECT c2,c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
239 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
240 DELETE FROM tde_db.t_encrypt WHERE c2 > 10;
241 UPDATE tde_db.t_encrypt SET c2 = 100 WHERE c2=1;
242 SELECT c2,c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
243 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
244 SHOW CREATE TABLE tde_db.t_encrypt;
245 end|
246 call tde_db.row_format_t_encrypt(" ROW_FORMAT=DYNAMIC ");
247 Table Create Table
248 t_encrypt CREATE TABLE `t_encrypt` (
249 `c2` int NOT NULL AUTO_INCREMENT,
250 `c3` varchar(255) DEFAULT NULL,
251 `c4` json DEFAULT NULL,
252 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
253 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
254 `c7` point NOT NULL /*!80003 SRID 0 */,
255 PRIMARY KEY (`c2`),
256 SPATIAL KEY `idx2` (`c7`)
257 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ENCRYPTION='Y'
258 COUNT(*)
259 32
260 c2 c4 c5 ST_AsText(c7)
261 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
262 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
263 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
264 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
265 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
266 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
267 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
268 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
269 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
270 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
271 c2 c4 c5 c6 ST_AsText(c7)
272 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
273 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
274 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
275 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
276 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
277 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
278 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
279 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
280 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
281 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
282 c2 c4 c5 ST_AsText(c7)
283 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
284 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
285 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
286 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
287 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
288 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
289 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
290 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
291 c2 c4 c5 c6 ST_AsText(c7)
292 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
293 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
294 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
295 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
296 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
297 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
298 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
299 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
300 Table Create Table
301 t_encrypt CREATE TABLE `t_encrypt` (
302 `c2` int NOT NULL AUTO_INCREMENT,
303 `c3` varchar(255) DEFAULT NULL,
304 `c4` json DEFAULT NULL,
305 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
306 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
307 `c7` point NOT NULL /*!80003 SRID 0 */,
308 PRIMARY KEY (`c2`),
309 SPATIAL KEY `idx2` (`c7`)
310 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC ENCRYPTION='Y'
311 # restart with keying
312 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
313 c2 c4 c5 c6 ST_AsText(c7)
314 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
315 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
316 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
317 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
318 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
319 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
320 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
321 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
322 SELECT COUNT(*) FROM tde_db.t_encrypt;
323 COUNT(*)
324 8
325 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
326 SELECT COUNT(*) FROM tde_db.t_encrypt;
327 COUNT(*)
328 16
329 call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPACT ");
330 Table Create Table
331 t_encrypt CREATE TABLE `t_encrypt` (
332 `c2` int NOT NULL AUTO_INCREMENT,
333 `c3` varchar(255) DEFAULT NULL,
334 `c4` json DEFAULT NULL,
335 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
336 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
337 `c7` point NOT NULL /*!80003 SRID 0 */,
338 PRIMARY KEY (`c2`),
339 SPATIAL KEY `idx2` (`c7`)
340 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT ENCRYPTION='Y'
341 COUNT(*)
342 32
343 c2 c4 c5 ST_AsText(c7)
344 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
345 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
346 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
347 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
348 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
349 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
350 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
351 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
352 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
353 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
354 c2 c4 c5 c6 ST_AsText(c7)
355 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
356 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
357 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
358 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
359 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
360 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
361 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
362 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
363 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
364 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
365 c2 c4 c5 ST_AsText(c7)
366 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
367 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
368 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
369 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
370 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
371 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
372 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
373 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
374 c2 c4 c5 c6 ST_AsText(c7)
375 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
376 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
377 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
378 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
379 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
380 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
381 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
382 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
383 Table Create Table
384 t_encrypt CREATE TABLE `t_encrypt` (
385 `c2` int NOT NULL AUTO_INCREMENT,
386 `c3` varchar(255) DEFAULT NULL,
387 `c4` json DEFAULT NULL,
388 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
389 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
390 `c7` point NOT NULL /*!80003 SRID 0 */,
391 PRIMARY KEY (`c2`),
392 SPATIAL KEY `idx2` (`c7`)
393 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT ENCRYPTION='Y'
394 # restart with keying
395 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
396 c2 c4 c5 c6 ST_AsText(c7)
397 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
398 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
399 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
400 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
401 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
402 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
403 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
404 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
405 SELECT COUNT(*) FROM tde_db.t_encrypt;
406 COUNT(*)
407 8
408 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
409 SELECT COUNT(*) FROM tde_db.t_encrypt;
410 COUNT(*)
411 16
412 call tde_db.row_format_t_encrypt(" ROW_FORMAT=REDUNDANT ");
413 Table Create Table
414 t_encrypt CREATE TABLE `t_encrypt` (
415 `c2` int NOT NULL AUTO_INCREMENT,
416 `c3` varchar(255) DEFAULT NULL,
417 `c4` json DEFAULT NULL,
418 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
419 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
420 `c7` point NOT NULL /*!80003 SRID 0 */,
421 PRIMARY KEY (`c2`),
422 SPATIAL KEY `idx2` (`c7`)
423 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ENCRYPTION='Y'
424 COUNT(*)
425 32
426 c2 c4 c5 ST_AsText(c7)
427 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
428 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
429 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
430 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
431 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
432 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
433 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
434 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
435 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
436 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
437 c2 c4 c5 c6 ST_AsText(c7)
438 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
439 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
440 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
441 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
442 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
443 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
444 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
445 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
446 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
447 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
448 c2 c4 c5 ST_AsText(c7)
449 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
450 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
451 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
452 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
453 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
454 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
455 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
456 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
457 c2 c4 c5 c6 ST_AsText(c7)
458 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
459 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
460 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
461 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
462 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
463 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
464 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
465 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
466 Table Create Table
467 t_encrypt CREATE TABLE `t_encrypt` (
468 `c2` int NOT NULL AUTO_INCREMENT,
469 `c3` varchar(255) DEFAULT NULL,
470 `c4` json DEFAULT NULL,
471 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
472 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
473 `c7` point NOT NULL /*!80003 SRID 0 */,
474 PRIMARY KEY (`c2`),
475 SPATIAL KEY `idx2` (`c7`)
476 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ENCRYPTION='Y'
477 # restart with keying
478 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
479 c2 c4 c5 c6 ST_AsText(c7)
480 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
481 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
482 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
483 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
484 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
485 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
486 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
487 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
488 SELECT COUNT(*) FROM tde_db.t_encrypt;
489 COUNT(*)
490 8
491 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
492 SELECT COUNT(*) FROM tde_db.t_encrypt;
493 COUNT(*)
494 16
495 call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED " );
496 Table Create Table
497 t_encrypt CREATE TABLE `t_encrypt` (
498 `c2` int NOT NULL AUTO_INCREMENT,
499 `c3` varchar(255) DEFAULT NULL,
500 `c4` json DEFAULT NULL,
501 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
502 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
503 `c7` point NOT NULL /*!80003 SRID 0 */,
504 PRIMARY KEY (`c2`),
505 SPATIAL KEY `idx2` (`c7`)
506 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED ENCRYPTION='Y'
507 COUNT(*)
508 32
509 c2 c4 c5 ST_AsText(c7)
510 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
511 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
512 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
513 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
514 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
515 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
516 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
517 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
518 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
519 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
520 c2 c4 c5 c6 ST_AsText(c7)
521 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
522 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
523 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
524 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
525 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
526 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
527 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
528 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
529 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
530 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
531 c2 c4 c5 ST_AsText(c7)
532 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
533 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
534 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
535 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
536 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
537 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
538 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
539 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
540 c2 c4 c5 c6 ST_AsText(c7)
541 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
542 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
543 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
544 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
545 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
546 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
547 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
548 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
549 Table Create Table
550 t_encrypt CREATE TABLE `t_encrypt` (
551 `c2` int NOT NULL AUTO_INCREMENT,
552 `c3` varchar(255) DEFAULT NULL,
553 `c4` json DEFAULT NULL,
554 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
555 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
556 `c7` point NOT NULL /*!80003 SRID 0 */,
557 PRIMARY KEY (`c2`),
558 SPATIAL KEY `idx2` (`c7`)
559 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED ENCRYPTION='Y'
560 # restart with keying
561 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
562 c2 c4 c5 c6 ST_AsText(c7)
563 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
564 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
565 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
566 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
567 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
568 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
569 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
570 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
571 SELECT COUNT(*) FROM tde_db.t_encrypt;
572 COUNT(*)
573 8
574 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
575 SELECT COUNT(*) FROM tde_db.t_encrypt;
576 COUNT(*)
577 16
578 call tde_db.row_format_t_encrypt(" ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 ");
579 Table Create Table
580 t_encrypt CREATE TABLE `t_encrypt` (
581 `c2` int NOT NULL AUTO_INCREMENT,
582 `c3` varchar(255) DEFAULT NULL,
583 `c4` json DEFAULT NULL,
584 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
585 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
586 `c7` point NOT NULL /*!80003 SRID 0 */,
587 PRIMARY KEY (`c2`),
588 SPATIAL KEY `idx2` (`c7`)
589 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 ENCRYPTION='Y'
590 COUNT(*)
591 32
592 c2 c4 c5 ST_AsText(c7)
593 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
594 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
595 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
596 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
597 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
598 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
599 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
600 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
601 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
602 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
603 c2 c4 c5 c6 ST_AsText(c7)
604 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
605 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
606 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
607 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
608 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
609 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
610 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
611 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
612 13 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
613 14 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
614 c2 c4 c5 ST_AsText(c7)
615 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
616 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
617 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
618 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
619 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
620 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
621 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
622 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
623 c2 c4 c5 c6 ST_AsText(c7)
624 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
625 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
626 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
627 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
628 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
629 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
630 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
631 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
632 Table Create Table
633 t_encrypt CREATE TABLE `t_encrypt` (
634 `c2` int NOT NULL AUTO_INCREMENT,
635 `c3` varchar(255) DEFAULT NULL,
636 `c4` json DEFAULT NULL,
637 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
638 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
639 `c7` point NOT NULL /*!80003 SRID 0 */,
640 PRIMARY KEY (`c2`),
641 SPATIAL KEY `idx2` (`c7`)
642 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 ENCRYPTION='Y'
643 # restart with keying
644 SELECT c2,c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
645 c2 c4 c5 c6 ST_AsText(c7)
646 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
647 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
648 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
649 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
650 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
651 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
652 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
653 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
654 SELECT COUNT(*) FROM tde_db.t_encrypt;
655 COUNT(*)
656 8
657 INSERT INTO tde_db.t_encrypt(c3,c4,c7) SELECT c3,c4,c7 FROM tde_db.t_encrypt;
658 SELECT COUNT(*) FROM tde_db.t_encrypt;
659 COUNT(*)
660 16
661 # Create partition table
662 DROP TABLE tde_db.t_encrypt;
663 CREATE TABLE tde_db.t_encrypt (c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL ) ENCRYPTION="Y" ENGINE=InnoDB PARTITION BY RANGE (c2) (PARTITION p1 VALUES LESS THAN (4),PARTITION p2 VALUES LESS THAN (8),PARTITION p3 VALUES LESS THAN (1000)) ;
664 SHOW CREATE TABLE tde_db.t_encrypt;
665 Table Create Table
666 t_encrypt CREATE TABLE `t_encrypt` (
667 `c2` int NOT NULL AUTO_INCREMENT,
668 `c3` varchar(255) DEFAULT NULL,
669 `c4` json DEFAULT NULL,
670 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
671 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
672 PRIMARY KEY (`c2`)
673 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
674 /*!50100 PARTITION BY RANGE (`c2`)
675 (PARTITION p1 VALUES LESS THAN (4) ENGINE = InnoDB,
676 PARTITION p2 VALUES LESS THAN (8) ENGINE = InnoDB,
677 PARTITION p3 VALUES LESS THAN (1000) ENGINE = InnoDB) */
678 INSERT INTO tde_db.t_encrypt(c3,c4) VALUES (REPEAT('a',200),'{ "key_a": 1, "key_b": 2, "key_c": 3 }');
679 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
680 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
681 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
682 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
683 INSERT INTO tde_db.t_encrypt(c3,c4) SELECT c3,c4 FROM tde_db.t_encrypt;
684 SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
685 c2 c4 c5
686 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1
687 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1
688 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1
689 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1
690 5 {"key_a": 1, "key_b": 2, "key_c": 3} 1
691 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1
692 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1
693 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1
694 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1
695 10 {"key_a": 1, "key_b": 2, "key_c": 3} 1
696 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
697 c2 c4 c5 c6
698 1 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
699 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
700 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
701 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
702 5 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
703 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
704 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
705 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
706 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
707 10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
708 DELETE FROM tde_db.t_encrypt WHERE c2 > 10;
709 UPDATE tde_db.t_encrypt SET c2 = 100 WHERE c2=1;
710 SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
711 c2 c4 c5
712 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1
713 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1
714 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1
715 5 {"key_a": 1, "key_b": 2, "key_c": 3} 1
716 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1
717 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1
718 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1
719 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1
720 10 {"key_a": 1, "key_b": 2, "key_c": 3} 1
721 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1
722 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
723 c2 c4 c5 c6
724 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
725 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
726 4 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
727 5 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
728 6 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
729 7 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
730 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
731 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
732 10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
733 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
734 ALTER TABLE tde_db.t_encrypt TRUNCATE PARTITION p2;
735 SELECT c2,c4,c5 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
736 c2 c4 c5
737 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1
738 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1
739 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1
740 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1
741 10 {"key_a": 1, "key_b": 2, "key_c": 3} 1
742 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1
743 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
744 c2 c4 c5 c6
745 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
746 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
747 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
748 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
749 10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
750 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
751 SHOW CREATE TABLE tde_db.t_encrypt;
752 Table Create Table
753 t_encrypt CREATE TABLE `t_encrypt` (
754 `c2` int NOT NULL AUTO_INCREMENT,
755 `c3` varchar(255) DEFAULT NULL,
756 `c4` json DEFAULT NULL,
757 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
758 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
759 PRIMARY KEY (`c2`)
760 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
761 /*!50100 PARTITION BY RANGE (`c2`)
762 (PARTITION p1 VALUES LESS THAN (4) ENGINE = InnoDB,
763 PARTITION p2 VALUES LESS THAN (8) ENGINE = InnoDB,
764 PARTITION p3 VALUES LESS THAN (1000) ENGINE = InnoDB) */
765 # restart with keying
766 SELECT c2,c4,c5,c6 FROM tde_db.t_encrypt ORDER BY c2 LIMIT 10;
767 c2 c4 c5 c6
768 2 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
769 3 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
770 8 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
771 9 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
772 10 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
773 100 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2
774 DROP TABLE tde_db.t_encrypt;
775 DROP DATABASE tde_db;
776 SET GLOBAL innodb_file_per_table=1;
777 DROP DATABASE IF EXISTS tde_db;
778 DROP TABLE IF EXISTS tde_db. t_encrypt;
779 CREATE DATABASE tde_db;
780 USE tde_db;
781 SET GLOBAL innodb_file_per_table = 1;
782 SELECT @@innodb_file_per_table;
783 @@innodb_file_per_table
784 1
785 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL PRIMARY KEY,
786 c3 CHAR(255) Default 'No text',
787 c4 JSON ,
788 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
789 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
790 c7 POINT NOT NULL SRID 0,
791 spatial INDEX idx2 (c7)
792 ) ENCRYPTION="Y" ENGINE = InnoDB;
793 CREATE PROCEDURE tde_db.populate_t_encrypt()
794 begin
795 declare i int default 1;
796 declare has_error int default 0;
797 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
798 while (i <= 2000) DO
799 insert into tde_db.t_encrypt(c2,c3,c4,c7) VALUES(i,CONCAT(REPEAT('a',200),LPAD(CAST(i AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
800 set i = i + 1;
801 end while;
802 end|
803 CREATE PROCEDURE tde_db.populate_t_encrypt_small()
804 begin
805 declare i int default 1;
806 declare has_error int default 0;
807 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
808 while (i <= 500) DO
809 insert into tde_db.t_encrypt(c2,c3,c4,c7) VALUES(i,CONCAT(REPEAT('a',200),LPAD(CAST(i AS CHAR),4,'0')),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
810 set i = i + 1;
811 end while;
812 end|
813 CREATE PROCEDURE tde_db.read_t_encrypt()
814 begin
815 declare i int default 1;
816 while (i <= 30) DO
817 SELECT * FROM (SELECT * FROM tde_db.t_encrypt ORDER BY RAND() LIMIT 1) AS A WHERE A.c2 < 0 ;
818 set i = i + 1;
819 end while;
820 end|
821 CREATE PROCEDURE tde_db.rotate_master_key()
822 begin
823 declare i int default 1;
824 declare has_error int default 0;
825 while (i <= 500) DO
826 ALTER INSTANCE ROTATE INNODB MASTER KEY;
827 set i = i + 1;
828 end while;
829 end|
830 CREATE PROCEDURE tde_db.create_encrypt_table(encrypt VARCHAR(5))
831 begin
832 declare i int default 1;
833 declare has_error int default 0;
834 while (i <= 50) DO
835 SET @sql_text = CONCAT('CREATE TABLE ',CONCAT('tde_db.t_encrypt_',encrypt,'_',i),' (c1 INT) ENCRYPTION="',encrypt,'"' ,' ENGINE=InnoDB');
836 PREPARE stmt FROM @sql_text;
837 EXECUTE stmt;
838 DEALLOCATE PREPARE stmt;
839 set i = i + 1;
840 end while;
841 end|
842 SHOW CREATE TABLE tde_db.t_encrypt;
843 Table Create Table
844 t_encrypt CREATE TABLE `t_encrypt` (
845 `c2` int NOT NULL,
846 `c3` char(255) DEFAULT 'No text',
847 `c4` json DEFAULT NULL,
848 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
849 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
850 `c7` point NOT NULL /*!80003 SRID 0 */,
851 PRIMARY KEY (`c2`),
852 SPATIAL KEY `idx2` (`c7`)
853 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
854 # In connection con1 - Running insert
855 call tde_db.populate_t_encrypt();
856 # In connection con2 - Running insert
857 call tde_db.populate_t_encrypt_small();
858 # In connection con3 : Running select
859 call tde_db.read_t_encrypt();
860 # In connection con4 : Running select
861 call tde_db.read_t_encrypt();
862 # In connection con5 - Running "alter instance"
863 call tde_db.rotate_master_key();
864 # In connection con6 - Running "create table"
865 call tde_db.create_encrypt_table("Y");
866 # In connection con7 - Running "create table"
867 call tde_db.create_encrypt_table("N");
868 # In connection con1
869 # In connection con2
870 # In connection con3
871 c2 c3 c4 c5 c6 c7
872 c2 c3 c4 c5 c6 c7
873 c2 c3 c4 c5 c6 c7
874 c2 c3 c4 c5 c6 c7
875 c2 c3 c4 c5 c6 c7
876 c2 c3 c4 c5 c6 c7
877 c2 c3 c4 c5 c6 c7
878 c2 c3 c4 c5 c6 c7
879 c2 c3 c4 c5 c6 c7
880 c2 c3 c4 c5 c6 c7
881 c2 c3 c4 c5 c6 c7
882 c2 c3 c4 c5 c6 c7
883 c2 c3 c4 c5 c6 c7
884 c2 c3 c4 c5 c6 c7
885 c2 c3 c4 c5 c6 c7
886 c2 c3 c4 c5 c6 c7
887 c2 c3 c4 c5 c6 c7
888 c2 c3 c4 c5 c6 c7
889 c2 c3 c4 c5 c6 c7
890 c2 c3 c4 c5 c6 c7
891 c2 c3 c4 c5 c6 c7
892 c2 c3 c4 c5 c6 c7
893 c2 c3 c4 c5 c6 c7
894 c2 c3 c4 c5 c6 c7
895 c2 c3 c4 c5 c6 c7
896 c2 c3 c4 c5 c6 c7
897 c2 c3 c4 c5 c6 c7
898 c2 c3 c4 c5 c6 c7
899 c2 c3 c4 c5 c6 c7
900 c2 c3 c4 c5 c6 c7
901 # In connection con4
902 c2 c3 c4 c5 c6 c7
903 c2 c3 c4 c5 c6 c7
904 c2 c3 c4 c5 c6 c7
905 c2 c3 c4 c5 c6 c7
906 c2 c3 c4 c5 c6 c7
907 c2 c3 c4 c5 c6 c7
908 c2 c3 c4 c5 c6 c7
909 c2 c3 c4 c5 c6 c7
910 c2 c3 c4 c5 c6 c7
911 c2 c3 c4 c5 c6 c7
912 c2 c3 c4 c5 c6 c7
913 c2 c3 c4 c5 c6 c7
914 c2 c3 c4 c5 c6 c7
915 c2 c3 c4 c5 c6 c7
916 c2 c3 c4 c5 c6 c7
917 c2 c3 c4 c5 c6 c7
918 c2 c3 c4 c5 c6 c7
919 c2 c3 c4 c5 c6 c7
920 c2 c3 c4 c5 c6 c7
921 c2 c3 c4 c5 c6 c7
922 c2 c3 c4 c5 c6 c7
923 c2 c3 c4 c5 c6 c7
924 c2 c3 c4 c5 c6 c7
925 c2 c3 c4 c5 c6 c7
926 c2 c3 c4 c5 c6 c7
927 c2 c3 c4 c5 c6 c7
928 c2 c3 c4 c5 c6 c7
929 c2 c3 c4 c5 c6 c7
930 c2 c3 c4 c5 c6 c7
931 c2 c3 c4 c5 c6 c7
932 # In connection con5
933 # In connection con6
934 # In connection con7
935 USE tde_db;
936 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
937 c2 right(c3,20) c4 c5 ST_AsText(c7)
938 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
939 2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
940 3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
941 4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
942 5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
943 6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
944 7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
945 8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
946 9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
947 10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
948 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
949 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
950 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
951 2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
952 3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
953 4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
954 5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
955 6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
956 7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
957 8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
958 9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
959 10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
960 SELECT COUNT(*) FROM tde_db.t_encrypt;
961 COUNT(*)
962 2000
963 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
964 c2 right(c3,20) c4 c5 ST_AsText(c7)
965 200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
966 400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
967 600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
968 800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
969 1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
970 1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
971 1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
972 1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
973 1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
974 2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
975 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
976 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
977 200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
978 400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
979 600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
980 800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
981 1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
982 1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
983 1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
984 1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
985 1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
986 2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
987 # restart with keying
988 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
989 c2 right(c3,20) c4 c5 ST_AsText(c7)
990 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
991 2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
992 3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
993 4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
994 5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
995 6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
996 7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
997 8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
998 9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
999 10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1000 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
1001 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
1002 1 aaaaaaaaaaaaaaaa0001 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1003 2 aaaaaaaaaaaaaaaa0002 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1004 3 aaaaaaaaaaaaaaaa0003 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1005 4 aaaaaaaaaaaaaaaa0004 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1006 5 aaaaaaaaaaaaaaaa0005 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1007 6 aaaaaaaaaaaaaaaa0006 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1008 7 aaaaaaaaaaaaaaaa0007 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1009 8 aaaaaaaaaaaaaaaa0008 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1010 9 aaaaaaaaaaaaaaaa0009 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1011 10 aaaaaaaaaaaaaaaa0010 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1012 SELECT COUNT(*) FROM tde_db.t_encrypt;
1013 COUNT(*)
1014 2000
1015 SELECT c2,right(c3,20),c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
1016 c2 right(c3,20) c4 c5 ST_AsText(c7)
1017 200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1018 400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1019 600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1020 800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1021 1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1022 1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1023 1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1024 1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1025 1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1026 2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 POINT(383293632 1754448)
1027 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt WHERE c2%200 = 0;
1028 c2 right(c3,20) c4 c5 c6 ST_AsText(c7)
1029 200 aaaaaaaaaaaaaaaa0200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1030 400 aaaaaaaaaaaaaaaa0400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1031 600 aaaaaaaaaaaaaaaa0600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1032 800 aaaaaaaaaaaaaaaa0800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1033 1000 aaaaaaaaaaaaaaaa1000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1034 1200 aaaaaaaaaaaaaaaa1200 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1035 1400 aaaaaaaaaaaaaaaa1400 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1036 1600 aaaaaaaaaaaaaaaa1600 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1037 1800 aaaaaaaaaaaaaaaa1800 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1038 2000 aaaaaaaaaaaaaaaa2000 {"key_a": 1, "key_b": 2, "key_c": 3} 1 2 POINT(383293632 1754448)
1039 DROP DATABASE tde_db;
1040 SET GLOBAL innodb_file_per_table=1;
1041 DROP DATABASE IF EXISTS tde_db;
1042 CREATE DATABASE tde_db;
1043 USE tde_db;
1044 DROP TABLE IF EXISTS tde_db.t_encrypt;
1045 SET GLOBAL innodb_file_per_table = 1;
1046 SELECT @@innodb_file_per_table;
1047 @@innodb_file_per_table
1048 1
1049 CREATE TABLE tde_db.t_encrypt(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
1050 SHOW CREATE TABLE tde_db.t_encrypt;
1051 Table Create Table
1052 t_encrypt CREATE TABLE `t_encrypt` (
1053 `c1` int DEFAULT NULL,
1054 `c2` char(20) DEFAULT NULL,
1055 `c3` blob
1056 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1057 INSERT INTO tde_db.t_encrypt VALUES(0, "aaaaa",repeat('A', 20000));
1058 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
1059 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
1060 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
1061 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
1062 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
1063 INSERT INTO tde_db.t_encrypt select * from tde_db.t_encrypt;
1064 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1065 c1 c2 right(c3, 20)
1066 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1067 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1068 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1069 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1070 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1071 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1072 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1073 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1074 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1075 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1076 CREATE USER encryptprivuser@localhost IDENTIFIED BY 'auth';
1077 GRANT ALL PRIVILEGES ON *.* to encryptprivuser@localhost;
1078 FLUSH PRIVILEGES;
1079 CREATE USER encryptnonprivuser@localhost IDENTIFIED BY 'noauth';
1080 GRANT SELECT ON *.* to encryptnonprivuser@localhost;
1081 FLUSH PRIVILEGES;
1082 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1083 c1 c2 right(c3, 20)
1084 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1085 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1086 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1087 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1088 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1089 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1090 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1091 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1092 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1093 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1094 # In connection 1
1095 SELECT CURRENT_USER();
1096 CURRENT_USER()
1097 encryptprivuser@localhost
1098 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1099 c1 c2 right(c3, 20)
1100 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1101 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1102 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1103 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1104 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1105 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1106 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1107 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1108 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1109 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1110 ALTER INSTANCE ROTATE INNODB MASTER KEY;
1111 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1112 c1 c2 right(c3, 20)
1113 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1114 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1115 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1116 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1117 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1118 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1119 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1120 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1121 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1122 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1123 # In connection 2
1124 SELECT CURRENT_USER();
1125 CURRENT_USER()
1126 encryptnonprivuser@localhost
1127 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1128 c1 c2 right(c3, 20)
1129 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1130 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1131 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1132 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1133 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1134 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1135 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1136 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1137 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1138 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1139 ALTER INSTANCE ROTATE INNODB MASTER KEY;
1140 ERROR 42000: Access denied; you need (at least one of) the SUPER or ENCRYPTION_KEY_ADMIN privilege(s) for this operation
1141 CREATE TABLE tde_db.t_encrypt_np(c1 INT, c2 char(20), c3 BLOB) ENCRYPTION="Y" ENGINE = InnoDB;
1142 ERROR 42000: CREATE command denied to user 'encryptnonprivuser'@'localhost' for table 't_encrypt_np'
1143 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1144 c1 c2 right(c3, 20)
1145 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1146 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1147 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1148 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1149 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1150 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1151 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1152 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1153 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1154 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1155 # In connection default
1156 # Starting server with keyring
1157 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1158 c1 c2 right(c3, 20)
1159 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1160 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1161 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1162 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1163 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1164 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1165 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1166 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1167 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1168 0 aaaaa AAAAAAAAAAAAAAAAAAAA
1169 DROP USER encryptnonprivuser@localhost;
1170 DROP USER encryptprivuser@localhost;
1171 DROP TABLE tde_db.t_encrypt;
1172 SET GLOBAL innodb_file_per_table=1;
1173 DROP DATABASE IF EXISTS tde_db;
1174 CREATE DATABASE tde_db;
1175 USE tde_db;
1176 DROP TABLE IF EXISTS tde_db.t_encrypt;
1177 SET GLOBAL innodb_file_per_table = 1;
1178 SELECT @@innodb_file_per_table;
1179 @@innodb_file_per_table
1180 1
1181 CREATE TABLE tde_db.t_encrypt(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 char(100), c3 BLOB , FULLTEXT INDEX `idx1` (c2)) ENCRYPTION="Y" ENGINE = InnoDB;
1182 CREATE TABLE tde_db.t_encrypt1(c11 INT , c22 char(100), c33 BLOB , FULLTEXT INDEX `idx1` (c22)) ENCRYPTION="Y" ENGINE = InnoDB;
1183 SHOW CREATE TABLE tde_db.t_encrypt;
1184 Table Create Table
1185 t_encrypt CREATE TABLE `t_encrypt` (
1186 `c1` int NOT NULL AUTO_INCREMENT,
1187 `c2` char(100) DEFAULT NULL,
1188 `c3` blob,
1189 PRIMARY KEY (`c1`),
1190 FULLTEXT KEY `idx1` (`c2`)
1191 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1192 CREATE TABLE tde_db.t_encrypt2 (f1 INT PRIMARY KEY, f2 CHAR(100),
1193 FOREIGN KEY (f1) REFERENCES tde_db.t_encrypt(c1) ON UPDATE CASCADE) ENCRYPTION="Y" ENGINE=InnoDB;
1194 CREATE TRIGGER tde_db.trigger_encrypt_table AFTER INSERT ON tde_db.t_encrypt
1195 FOR EACH ROW
1196 begin
1197 INSERT INTO tde_db.t_encrypt1 SET c11 = NEW.c1*-1, c22 = NEW.c2 , c33 = NEW.c3;
1198 end|
1199 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("transparanet tablespace encryption",repeat('A', 200));
1200 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("general tablespace option",repeat('A', 200));
1201 INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("page level encryption",repeat('A', 200));
1202 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(1,"transparanet tablespace encryption");
1203 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
1204 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1205 c1 c2 right(c3, 20)
1206 1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
1207 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
1208 3 page level encryption AAAAAAAAAAAAAAAAAAAA
1209 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace');
1210 c1 c2 right(c3, 20)
1211 1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
1212 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
1213 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace' IN BOOLEAN MODE);
1214 c1 c2 right(c3, 20)
1215 1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
1216 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
1217 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('+tablespace -encryption' IN BOOLEAN MODE);
1218 c1 c2 right(c3, 20)
1219 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
1220 ALTER TABLE tde_db.t_encrypt DROP INDEX idx1;
1221 SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
1222 c1 c2 right(c3, 20)
1223 1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
1224 2 general tablespace option AAAAAAAAAAAAAAAAAAAA
1225 3 page level encryption AAAAAAAAAAAAAAAAAAAA
1226 ALTER TABLE tde_db.t_encrypt ADD COLUMN c4 CHAR(20) DEFAULT 'text';
1227 SELECT c1,c2,right(c3, 20),c4 FROM tde_db.t_encrypt LIMIT 10;
1228 c1 c2 right(c3, 20) c4
1229 1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA text
1230 2 general tablespace option AAAAAAAAAAAAAAAAAAAA text
1231 3 page level encryption AAAAAAAAAAAAAAAAAAAA text
1232 CREATE VIEW tde_db.t_encrypt_view AS SELECT c1,c2 FROM tde_db.t_encrypt;
1233 SELECT c2 FROM tde_db.t_encrypt_view LIMIT 10;
1234 c2
1235 transparanet tablespace encryption
1236 general tablespace option
1237 page level encryption
1238 SELECT A.c2,B.c2,right(B.c3,20) FROM tde_db.t_encrypt_view A , tde_db.t_encrypt B WHERE A.c2 = B.c2;
1239 c2 c2 right(B.c3,20)
1240 transparanet tablespace encryption transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
1241 general tablespace option general tablespace option AAAAAAAAAAAAAAAAAAAA
1242 page level encryption page level encryption AAAAAAAAAAAAAAAAAAAA
1243 DROP VIEW tde_db.t_encrypt_view;
1244 SELECT c11,c22,right(c33, 20) FROM tde_db.t_encrypt1 LIMIT 10;
1245 c11 c22 right(c33, 20)
1246 -1 transparanet tablespace encryption AAAAAAAAAAAAAAAAAAAA
1247 -2 general tablespace option AAAAAAAAAAAAAAAAAAAA
1248 -3 page level encryption AAAAAAAAAAAAAAAAAAAA
1249 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
1250 ERROR 23000: Duplicate entry '2' for key 't_encrypt2.PRIMARY'
1251 INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(8,"general tablespace option");
1252 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`tde_db`.`t_encrypt2`, CONSTRAINT `t_encrypt2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t_encrypt` (`c1`) ON UPDATE CASCADE)
1253 SELECT f1,f2 FROM tde_db.t_encrypt2;
1254 f1 f2
1255 1 transparanet tablespace encryption
1256 2 general tablespace option
1257 UPDATE tde_db.t_encrypt SET c1=10 WHERE c1=1;
1258 SELECT f1,f2 FROM tde_db.t_encrypt2;
1259 f1 f2
1260 2 general tablespace option
1261 10 transparanet tablespace encryption
1262 DROP DATABASE tde_db;
1263 SET GLOBAL innodb_file_per_table=1;
1264 DROP DATABASE IF EXISTS tde_db;
1265 CREATE DATABASE tde_db;
1266 USE tde_db;
1267 DROP TABLE IF EXISTS tde_db.t_encrypt;
1268 SET GLOBAL innodb_file_per_table = 1;
1269 SELECT @@innodb_file_per_table;
1270 @@innodb_file_per_table
1271 1
1272 CREATE TABLE tde_db.t_encrypt (c2 INT NOT NULL AUTO_INCREMENT ,c3 VARCHAR(255), c4 JSON ,c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,c7 POINT NOT NULL SRID 0,spatial INDEX idx2 (c7) , PRIMARY KEY (c2,c3(100))) ENCRYPTION="Y" ENGINE=InnoDB;
1273 CREATE PROCEDURE tde_db.txn_t_encrypt()
1274 BEGIN
1275 declare i int default 0;
1276 declare rowcnt int default 0;
1277 START TRANSACTION;
1278 WHILE (i <= 2000) DO
1279 SET i = i + 1;
1280 SET rowcnt = rowcnt + 1;
1281 INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (CONCAT(REPEAT('a',10),REPEAT(i,10)),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
1282 IF (rowcnt = 3) THEN
1283 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 21, "key_b": 22, "key_c": 23 }' WHERE c2 = i-1 ;
1284 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
1285 SAVEPOINT A;
1286 END IF;
1287 IF (rowcnt = 5) THEN
1288 UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 41, "key_b": 42, "key_c": 43 }' WHERE c2 = i-1 ;
1289 DELETE FROM tde_db.t_encrypt WHERE c2 = i;
1290 SAVEPOINT B;
1291 END IF;
1292 IF (rowcnt = 10) THEN
1293 ROLLBACK TO SAVEPOINT A;
1294 COMMIT;
1295 SET rowcnt = 0;
1296 START TRANSACTION;
1297 END IF;
1298 END WHILE;
1299 COMMIT;
1300 end|
1301 call tde_db.txn_t_encrypt();
1302 SELECT COUNT(*) FROM tde_db.t_encrypt;
1303 COUNT(*)
1304 401
1305 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
1306 c2 RIGHT(c3,20) c4
1307 1 aaaaaaaaaa1111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
1308 2 aaaaaaaaaa2222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
1309 11 11111111111111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
1310 12 12121212121212121212 {"key_a": 21, "key_b": 22, "key_c": 23}
1311 21 21212121212121212121 {"key_a": 1, "key_b": 2, "key_c": 3}
1312 22 22222222222222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
1313 31 31313131313131313131 {"key_a": 1, "key_b": 2, "key_c": 3}
1314 32 32323232323232323232 {"key_a": 21, "key_b": 22, "key_c": 23}
1315 41 41414141414141414141 {"key_a": 1, "key_b": 2, "key_c": 3}
1316 42 42424242424242424242 {"key_a": 21, "key_b": 22, "key_c": 23}
1317 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
1318 c2 RIGHT(c3,20) c4
1319 501 01501501501501501501 {"key_a": 1, "key_b": 2, "key_c": 3}
1320 502 02502502502502502502 {"key_a": 21, "key_b": 22, "key_c": 23}
1321 511 11511511511511511511 {"key_a": 1, "key_b": 2, "key_c": 3}
1322 512 12512512512512512512 {"key_a": 21, "key_b": 22, "key_c": 23}
1323 521 21521521521521521521 {"key_a": 1, "key_b": 2, "key_c": 3}
1324 522 22522522522522522522 {"key_a": 21, "key_b": 22, "key_c": 23}
1325 531 31531531531531531531 {"key_a": 1, "key_b": 2, "key_c": 3}
1326 532 32532532532532532532 {"key_a": 21, "key_b": 22, "key_c": 23}
1327 541 41541541541541541541 {"key_a": 1, "key_b": 2, "key_c": 3}
1328 542 42542542542542542542 {"key_a": 21, "key_b": 22, "key_c": 23}
1329 551 51551551551551551551 {"key_a": 1, "key_b": 2, "key_c": 3}
1330 552 52552552552552552552 {"key_a": 21, "key_b": 22, "key_c": 23}
1331 561 61561561561561561561 {"key_a": 1, "key_b": 2, "key_c": 3}
1332 562 62562562562562562562 {"key_a": 21, "key_b": 22, "key_c": 23}
1333 571 71571571571571571571 {"key_a": 1, "key_b": 2, "key_c": 3}
1334 572 72572572572572572572 {"key_a": 21, "key_b": 22, "key_c": 23}
1335 581 81581581581581581581 {"key_a": 1, "key_b": 2, "key_c": 3}
1336 582 82582582582582582582 {"key_a": 21, "key_b": 22, "key_c": 23}
1337 591 91591591591591591591 {"key_a": 1, "key_b": 2, "key_c": 3}
1338 592 92592592592592592592 {"key_a": 21, "key_b": 22, "key_c": 23}
1339 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
1340 c2 RIGHT(c3,20) c4
1341 2001 20012001200120012001 {"key_a": 1, "key_b": 2, "key_c": 3}
1342 1992 19921992199219921992 {"key_a": 21, "key_b": 22, "key_c": 23}
1343 1991 19911991199119911991 {"key_a": 1, "key_b": 2, "key_c": 3}
1344 1982 19821982198219821982 {"key_a": 21, "key_b": 22, "key_c": 23}
1345 1981 19811981198119811981 {"key_a": 1, "key_b": 2, "key_c": 3}
1346 1972 19721972197219721972 {"key_a": 21, "key_b": 22, "key_c": 23}
1347 1971 19711971197119711971 {"key_a": 1, "key_b": 2, "key_c": 3}
1348 1962 19621962196219621962 {"key_a": 21, "key_b": 22, "key_c": 23}
1349 1961 19611961196119611961 {"key_a": 1, "key_b": 2, "key_c": 3}
1350 1952 19521952195219521952 {"key_a": 21, "key_b": 22, "key_c": 23}
1351 # Starting server with keyring
1352 SELECT COUNT(*) FROM tde_db.t_encrypt;
1353 COUNT(*)
1354 401
1355 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
1356 c2 RIGHT(c3,20) c4
1357 1 aaaaaaaaaa1111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
1358 2 aaaaaaaaaa2222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
1359 11 11111111111111111111 {"key_a": 1, "key_b": 2, "key_c": 3}
1360 12 12121212121212121212 {"key_a": 21, "key_b": 22, "key_c": 23}
1361 21 21212121212121212121 {"key_a": 1, "key_b": 2, "key_c": 3}
1362 22 22222222222222222222 {"key_a": 21, "key_b": 22, "key_c": 23}
1363 31 31313131313131313131 {"key_a": 1, "key_b": 2, "key_c": 3}
1364 32 32323232323232323232 {"key_a": 21, "key_b": 22, "key_c": 23}
1365 41 41414141414141414141 {"key_a": 1, "key_b": 2, "key_c": 3}
1366 42 42424242424242424242 {"key_a": 21, "key_b": 22, "key_c": 23}
1367 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
1368 c2 RIGHT(c3,20) c4
1369 501 01501501501501501501 {"key_a": 1, "key_b": 2, "key_c": 3}
1370 502 02502502502502502502 {"key_a": 21, "key_b": 22, "key_c": 23}
1371 511 11511511511511511511 {"key_a": 1, "key_b": 2, "key_c": 3}
1372 512 12512512512512512512 {"key_a": 21, "key_b": 22, "key_c": 23}
1373 521 21521521521521521521 {"key_a": 1, "key_b": 2, "key_c": 3}
1374 522 22522522522522522522 {"key_a": 21, "key_b": 22, "key_c": 23}
1375 531 31531531531531531531 {"key_a": 1, "key_b": 2, "key_c": 3}
1376 532 32532532532532532532 {"key_a": 21, "key_b": 22, "key_c": 23}
1377 541 41541541541541541541 {"key_a": 1, "key_b": 2, "key_c": 3}
1378 542 42542542542542542542 {"key_a": 21, "key_b": 22, "key_c": 23}
1379 551 51551551551551551551 {"key_a": 1, "key_b": 2, "key_c": 3}
1380 552 52552552552552552552 {"key_a": 21, "key_b": 22, "key_c": 23}
1381 561 61561561561561561561 {"key_a": 1, "key_b": 2, "key_c": 3}
1382 562 62562562562562562562 {"key_a": 21, "key_b": 22, "key_c": 23}
1383 571 71571571571571571571 {"key_a": 1, "key_b": 2, "key_c": 3}
1384 572 72572572572572572572 {"key_a": 21, "key_b": 22, "key_c": 23}
1385 581 81581581581581581581 {"key_a": 1, "key_b": 2, "key_c": 3}
1386 582 82582582582582582582 {"key_a": 21, "key_b": 22, "key_c": 23}
1387 591 91591591591591591591 {"key_a": 1, "key_b": 2, "key_c": 3}
1388 592 92592592592592592592 {"key_a": 21, "key_b": 22, "key_c": 23}
1389 SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
1390 c2 RIGHT(c3,20) c4
1391 2001 20012001200120012001 {"key_a": 1, "key_b": 2, "key_c": 3}
1392 1992 19921992199219921992 {"key_a": 21, "key_b": 22, "key_c": 23}
1393 1991 19911991199119911991 {"key_a": 1, "key_b": 2, "key_c": 3}
1394 1982 19821982198219821982 {"key_a": 21, "key_b": 22, "key_c": 23}
1395 1981 19811981198119811981 {"key_a": 1, "key_b": 2, "key_c": 3}
1396 1972 19721972197219721972 {"key_a": 21, "key_b": 22, "key_c": 23}
1397 1971 19711971197119711971 {"key_a": 1, "key_b": 2, "key_c": 3}
1398 1962 19621962196219621962 {"key_a": 21, "key_b": 22, "key_c": 23}
1399 1961 19611961196119611961 {"key_a": 1, "key_b": 2, "key_c": 3}
1400 1952 19521952195219521952 {"key_a": 21, "key_b": 22, "key_c": 23}
1401 DROP DATABASE tde_db;
1402 # Global privilege ENCRYPTION_KEY_ADMIN can replace super.
1403 USE test;
1404 CREATE USER encryption_admin@localhost IDENTIFIED BY 'foo';
1405 GRANT ENCRYPTION_KEY_ADMIN, CREATE ON *.* TO encryption_admin@localhost;
1406 CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
1407 ALTER INSTANCE ROTATE INNODB MASTER KEY;
1408 REVOKE ENCRYPTION_KEY_ADMIN ON *.* FROM encryption_admin@localhost;
1409 ALTER INSTANCE ROTATE INNODB MASTER KEY;
1410 ERROR 42000: Access denied; you need (at least one of) the SUPER or ENCRYPTION_KEY_ADMIN privilege(s) for this operation
1411 DROP USER encryption_admin@localhost;
1412 DROP TABLE t1;
1413 SET GLOBAL innodb_file_per_table=1;
1414 # ----------------------------------------------------------------------
1415 # Teardown
1416 # Removing manifest file for current MySQL server instance
1417 # Removing local keyring file for keyring component: component_keyring_file
1418 # Removing local configuration file for keyring component: component_keyring_file
1419 # Restarting server without the manifest file
1420 # ----------------------------------------------------------------------