Debian Codegraph @

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

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