Debian Codegraph @

mysql-test/suite/innodb/t/table_encrypt_kill.test

raw
1 #------------------------------------------------------------------------------
2 # InnoDB transparent tablespace data encryption
3 # This test case will test basic recovery.
4 # Test does following
5 # - runs command in parallel on encrypt table
6 # - server is killed
7 # - server restarted with same options
8 #------------------------------------------------------------------------------
9
10 --source include/no_valgrind_without_big.inc
11 --source include/big_test.inc
12 # Avoid CrashReporter popup on Mac
13 --source include/not_crashrep.inc
14
15 #suppress warning
16 -- disable_query_log
17 call mtr.add_suppression("does not exist in the InnoDB internal data dictionary though MySQL is trying to drop it");
18 call mtr.add_suppression("Can't generate new master key for tablespace encryption, please check the keyring plugin is loaded.");
19 call mtr.add_suppression("Operating system error number");
20 call mtr.add_suppression("The error means the system cannot find the path specified");
21 call mtr.add_suppression("If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them");
22 call mtr.add_suppression("Could not find a valid tablespace file");
23 call mtr.add_suppression("Ignoring tablespace");
24 call mtr.add_suppression("Failed to find tablespace for table");
25 call mtr.add_suppression("Cannot create keyring directory");
26 call mtr.add_suppression("\\[Warning\\] .* Please refer to .* for how to resolve the issue");
27 call mtr.add_suppression("\\[Warning\\] .* Database page corruption or a failed file read of page");
28 call mtr.add_suppression("\\[Warning\\] .* Cannot calculate statistics for table");
29 call mtr.add_suppression("\\[ERROR\\] .* Encryption can't find master key, please check the keyring plugin is loaded.");
30 call mtr.add_suppression("\\[ERROR\\] .* Plugin keyring_file reported: 'Could not create keyring directory");
31 call mtr.add_suppression("\\[ERROR\\] .* Table tde_db/t_encrypt.* in the InnoDB data dictionary has tablespace id .*, but tablespace with that id or name does not exist");
32 -- enable_query_log
33
34 let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;
35
36 --echo # Starting server with keyring plugin
37 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
38 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
39 --replace_regex /\.dll/.so/
40 --source include/restart_mysqld.inc
41
42
43
44 #------------------------------------------------------------------------------
45 # Initial setup
46
47 --disable_warnings
48 DROP DATABASE IF EXISTS tde_db;
49 DROP TABLE IF EXISTS tde_db. t_encrypt;
50 CREATE DATABASE tde_db;
51 USE tde_db;
52 --enable_warnings
53
54 SET GLOBAL innodb_file_per_table = 1;
55 SELECT @@innodb_file_per_table;
56
57 # Create a table with encryption
58 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
59 c3 CHAR(255) Default 'No text',
60 c4 JSON,
61 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
62 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
63 c7 POINT NOT NULL SRID 0,
64 spatial INDEX idx2 (c7)
65 ) ENCRYPTION="Y" ENGINE = InnoDB;
66
67 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
68 c3 CHAR(255) Default 'No text',
69 c4 JSON,
70 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
71 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
72 c7 POINT NOT NULL SRID 0,
73 spatial INDEX idx2 (c7)
74 ) ENGINE = InnoDB;
75
76
77 DELIMITER |;
78 CREATE PROCEDURE tde_db.populate_t_encrypt()
79 begin
80 declare i int default 1;
81 declare has_error int default 0;
82 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
83 while (i <= 5000) DO
84 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)'));
85 set i = i + 1;
86 end while;
87 end|
88 CREATE PROCEDURE tde_db.populate_t_non_encrypt()
89 begin
90 declare i int default 1;
91 declare has_error int default 0;
92 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
93 while (i <= 5000) DO
94 insert into tde_db.t_non_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)'));
95 set i = i + 1;
96 end while;
97 end|
98 CREATE PROCEDURE tde_db.update_t_encrypt()
99 begin
100 declare i int default 1;
101 declare ustr varchar(1000);
102 declare has_error int default 0;
103 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
104 while (i <= 5000) DO
105 SET @sql_text = CONCAT (' UPDATE tde_db.t_encrypt SET c3 =', 'CONCAT(REPEAT(a,200),LPAD(CAST(',i, 'AS CHAR),4,0) ORDER BY RAND() LIMIT 1');
106 PREPARE stmt FROM @sql_text;
107 EXECUTE stmt;
108 DEALLOCATE PREPARE stmt;
109 set i = i + 1;
110 end while;
111 end|
112
113 CREATE PROCEDURE tde_db.delete_t_encrypt()
114 begin
115 declare i int default 1;
116 declare ustr varchar(1000);
117 declare has_error int default 0;
118 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
119 while (i <= 5000) DO
120 SET @sql_text = CONCAT (' DELETE FROM tde_db.t_encrypt LIMIT 1');
121 PREPARE stmt FROM @sql_text;
122 EXECUTE stmt;
123 DEALLOCATE PREPARE stmt;
124 set i = i + 1;
125 end while;
126 end|
127
128
129 CREATE PROCEDURE tde_db.read_t_encrypt()
130 begin
131 declare i int default 1;
132 while (i <= 5000) DO
133 SELECT * FROM (SELECT * FROM tde_db.t_encrypt ORDER BY RAND() LIMIT 1) AS A WHERE A.c2 < 0 ;
134 set i = i + 1;
135 end while;
136 end|
137 CREATE PROCEDURE tde_db.alter_t_encrypt()
138 begin
139 declare i int default 1;
140 declare has_error int default 0;
141 while (i <= 5000) DO
142 ALTER INSTANCE ROTATE INNODB MASTER KEY;
143 set i = i + 1;
144 end while;
145 end|
146 CREATE PROCEDURE tde_db.create_t_encrypt(encrypt VARCHAR(5), tcnt INT)
147 begin
148 declare i int default 1;
149 declare has_error int default 0;
150 DECLARE CONTINUE HANDLER FOR 1050 SET has_error = 1;
151 SET i = tcnt ;
152 while (i <= 5000) DO
153 SET @sql_text = CONCAT('CREATE TABLE ',CONCAT('tde_db.t_encrypt_',encrypt,'_',i),' (c1 INT) ENCRYPTION="',encrypt,'"',' ENGINE=InnoDB');
154 PREPARE stmt FROM @sql_text;
155 EXECUTE stmt;
156 DEALLOCATE PREPARE stmt;
157 set i = i + 1;
158 end while;
159 end|
160 DELIMITER ;|
161
162
163 SHOW CREATE TABLE tde_db.t_encrypt;
164
165 --echo # Case1: insert on encrypt and non encrypt table in parallel during kill
166 --echo # In connection con1 - Running insert on encrypt table
167 connect (con1,localhost,root,,);
168 send call tde_db.populate_t_encrypt();
169 --echo # In connection con2 - Running insert on encrypt table
170 connect (con2,localhost,root,,);
171 send call tde_db.populate_t_encrypt();
172 --echo # In connection con3 - Running insert into non encrypt table
173 connect (con3,localhost,root,,);
174 send call tde_db.populate_t_non_encrypt();
175
176
177 --connection default
178 --echo # kill and restart the server
179 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
180 --sleep 3
181 --let $_server_id= `SELECT @@server_id`
182 --let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect
183 --exec echo "$restart_parameters" > $_expect_file_name
184 --shutdown_server 0
185 --source include/wait_until_disconnected.inc
186 --enable_reconnect
187 --source include/wait_until_connected_again.inc
188 --disable_reconnect
189
190 --disconnect con1
191 --disconnect con2
192 --disconnect con3
193 --connection default
194 --disable_result_log
195 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
196 INSERT INTO tde_db.t_encrypt(c2,c3,c4,c7) VALUES(10000,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)'));
197 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
198 INSERT INTO tde_db.t_non_encrypt(c2,c3,c4,c7) VALUES(10000,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)'));
199 --enable_result_log
200 SELECT 1;
201
202
203 --echo # Case2: insert/update/delete on encrypt in parallel during kill
204 DROP TABLE tde_db.t_encrypt;
205 DROP TABLE tde_db.t_non_encrypt;
206 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
207 c3 CHAR(255) Default 'No text',
208 c4 JSON,
209 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
210 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
211 c7 POINT NOT NULL SRID 0,
212 spatial INDEX idx2 (c7)
213 ) ENCRYPTION="Y" ENGINE = InnoDB;
214
215 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
216 c3 CHAR(255) Default 'No text',
217 c4 JSON,
218 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
219 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
220 c7 POINT NOT NULL SRID 0,
221 spatial INDEX idx2 (c7)
222 ) ENGINE = InnoDB;
223
224 --echo # In connection con1 - Running insert on encrypt table
225 connect (con1,localhost,root,,);
226 send call tde_db.populate_t_encrypt();
227 --echo # In connection con2 - Running update on encrypt table
228 connect (con2,localhost,root,,);
229 send call tde_db.update_t_encrypt();
230 --echo # In connection con3 - Running delete on encrypt table
231 connect (con3,localhost,root,,);
232 send call tde_db.delete_t_encrypt();
233
234
235 --connection default
236 --echo # kill and restart the server
237 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
238 --sleep 3
239
240 --let $_server_id= `SELECT @@server_id`
241 --let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect
242 --exec echo "$restart_parameters" > $_expect_file_name
243 --shutdown_server 0
244 --source include/wait_until_disconnected.inc
245 --enable_reconnect
246 --source include/wait_until_connected_again.inc
247 --disable_reconnect
248
249
250 --disconnect con1
251 --disconnect con2
252 --disconnect con3
253 --connection default
254 --disable_result_log
255 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
256 INSERT INTO tde_db.t_encrypt(c2,c3,c4,c7) VALUES(10000,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)'));
257 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
258 INSERT INTO tde_db.t_non_encrypt(c2,c3,c4,c7) VALUES(10000,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)'));
259 --enable_result_log
260 SELECT 1;
261
262 --echo # Case3: select,create and insert on encrypt in parallel during kill
263 DROP TABLE tde_db.t_encrypt;
264 DROP TABLE tde_db.t_non_encrypt;
265 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
266 c3 CHAR(255) Default 'No text',
267 c4 JSON,
268 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
269 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
270 c7 POINT NOT NULL SRID 0,
271 spatial INDEX idx2 (c7)
272 ) ENCRYPTION="Y" ENGINE = InnoDB;
273
274 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
275 c3 CHAR(255) Default 'No text',
276 c4 JSON,
277 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
278 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
279 c7 POINT NOT NULL SRID 0,
280 spatial INDEX idx2 (c7)
281 ) ENGINE = InnoDB;
282
283 --echo # In connection con1 - Running insert on encrypt table
284 connect (con1,localhost,root,,);
285 send call tde_db.populate_t_encrypt();
286 --echo # In connection con2 - Running select on encrypt table
287 connect (con2,localhost,root,,);
288 send call tde_db.read_t_encrypt();
289
290
291 --connection default
292 --echo # kill and restart the server
293
294 let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
295 --sleep 1
296 --let $_server_id= `SELECT @@server_id`
297 --let $_expect_file_name= $MYSQLTEST_VARDIR/tmp/mysqld.$_server_id.expect
298 --replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR $KEYRING_PLUGIN_OPT --plugin-dir=KEYRING_PLUGIN_PATH $KEYRING_PLUGIN keyring_file.so
299 --exec echo "$restart_parameters" > $_expect_file_name
300 --shutdown_server 0
301 --source include/wait_until_disconnected.inc
302 --enable_reconnect
303 --source include/wait_until_connected_again.inc
304 --disable_reconnect
305
306
307 --disconnect con1
308 --disconnect con2
309 --connection default
310 --disable_result_log
311 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
312 INSERT INTO tde_db.t_encrypt(c2,c3,c4,c7) VALUES(10000,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)'));
313 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
314 INSERT INTO tde_db.t_non_encrypt(c2,c3,c4,c7) VALUES(10000,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)'));
315 --enable_result_log
316 SELECT 1;
317
318
319
320 #--echo # Case5: insert,create,alter on encrypt in parallel during kill
321 #--echo # In connection con1 - Running insert on encrypt table
322 #connect (con1,localhost,root,,);
323 #send call tde_db.populate_t_encrypt();
324 #--echo # In connection con2 - Running alter instance
325 #connect (con2,localhost,root,,);
326 #send call tde_db.alter_t_encrypt();
327 #--echo # In connection con3 - Running create on encrypt table
328 #connect (con3,localhost,root,,);
329 #send call tde_db.create_t_encrypt("Y",500);
330
331 #--connection default
332 #--echo # kill and restart the server
333 #let $restart_parameters = restart: --early-plugin-load="keyring_file=$KEYRING_PLUGIN" --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT ;
334 #--sleep 3
335 #--source include/kill_and_restart_mysqld.inc
336 #--disconnect con1
337 #--disconnect con2
338 #--disconnect con3
339 #--connection default
340 #--disable_result_log
341 #SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
342 #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)'));
343 #SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
344 #INSERT INTO tde_db.t_non_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)'));
345 #--enable_result_log
346 #SELECT 1;
347
348 DROP DATABASE tde_db;
349
350 # Cleanup
351 --echo # restart server without keyring
352 let $restart_parameters = restart: ;
353 --source include/restart_mysqld.inc
354 eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;
355 --remove_file $MYSQL_TMP_DIR/mysecret_keyring