Debian Codegraph @

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

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