Debian Codegraph @

mysql-test/suite/component_keyring_file/r/table_encrypt_kill.result

raw
1 # ----------------------------------------------------------------------
2 # Setup
3 # Creating local configuration file for keyring component: component_keyring_file
4 # Creating manifest file for current MySQL server instance
5 # Re-starting mysql server with manifest file
6 # ----------------------------------------------------------------------
7 DROP DATABASE IF EXISTS tde_db;
8 DROP TABLE IF EXISTS tde_db. t_encrypt;
9 CREATE DATABASE tde_db;
10 USE tde_db;
11 SET GLOBAL innodb_file_per_table = 1;
12 SELECT @@innodb_file_per_table;
13 @@innodb_file_per_table
14 1
15 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
16 c3 CHAR(255) Default 'No text',
17 c4 JSON,
18 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
19 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
20 c7 POINT NOT NULL SRID 0,
21 spatial INDEX idx2 (c7)
22 ) ENCRYPTION="Y" ENGINE = InnoDB;
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 CREATE PROCEDURE tde_db.populate_t_encrypt()
32 begin
33 declare i int default 1;
34 declare has_error int default 0;
35 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
36 while (i <= 5000) DO
37 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)'));
38 set i = i + 1;
39 end while;
40 end|
41 CREATE PROCEDURE tde_db.populate_t_non_encrypt()
42 begin
43 declare i int default 1;
44 declare has_error int default 0;
45 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
46 while (i <= 5000) DO
47 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)'));
48 set i = i + 1;
49 end while;
50 end|
51 CREATE PROCEDURE tde_db.update_t_encrypt()
52 begin
53 declare i int default 1;
54 declare ustr varchar(1000);
55 declare has_error int default 0;
56 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
57 while (i <= 5000) DO
58 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');
59 PREPARE stmt FROM @sql_text;
60 EXECUTE stmt;
61 DEALLOCATE PREPARE stmt;
62 set i = i + 1;
63 end while;
64 end|
65 CREATE PROCEDURE tde_db.delete_t_encrypt()
66 begin
67 declare i int default 1;
68 declare ustr varchar(1000);
69 declare has_error int default 0;
70 DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
71 while (i <= 5000) DO
72 SET @sql_text = CONCAT (' DELETE FROM tde_db.t_encrypt LIMIT 1');
73 PREPARE stmt FROM @sql_text;
74 EXECUTE stmt;
75 DEALLOCATE PREPARE stmt;
76 set i = i + 1;
77 end while;
78 end|
79 CREATE PROCEDURE tde_db.read_t_encrypt()
80 begin
81 declare i int default 1;
82 while (i <= 5000) DO
83 SELECT * FROM (SELECT * FROM tde_db.t_encrypt ORDER BY RAND() LIMIT 1) AS A WHERE A.c2 < 0 ;
84 set i = i + 1;
85 end while;
86 end|
87 CREATE PROCEDURE tde_db.alter_t_encrypt()
88 begin
89 declare i int default 1;
90 declare has_error int default 0;
91 while (i <= 5000) DO
92 ALTER INSTANCE ROTATE INNODB MASTER KEY;
93 set i = i + 1;
94 end while;
95 end|
96 CREATE PROCEDURE tde_db.create_t_encrypt(encrypt VARCHAR(5), tcnt INT)
97 begin
98 declare i int default 1;
99 declare has_error int default 0;
100 DECLARE CONTINUE HANDLER FOR 1050 SET has_error = 1;
101 SET i = tcnt ;
102 while (i <= 5000) DO
103 SET @sql_text = CONCAT('CREATE TABLE ',CONCAT('tde_db.t_encrypt_',encrypt,'_',i),' (c1 INT) ENCRYPTION="',encrypt,'"',' ENGINE=InnoDB');
104 PREPARE stmt FROM @sql_text;
105 EXECUTE stmt;
106 DEALLOCATE PREPARE stmt;
107 set i = i + 1;
108 end while;
109 end|
110 SHOW CREATE TABLE tde_db.t_encrypt;
111 Table Create Table
112 t_encrypt CREATE TABLE `t_encrypt` (
113 `c2` int NOT NULL AUTO_INCREMENT,
114 `c3` char(255) DEFAULT 'No text',
115 `c4` json DEFAULT NULL,
116 `c5` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
117 `c6` int GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
118 `c7` point NOT NULL /*!80003 SRID 0 */,
119 PRIMARY KEY (`c2`),
120 SPATIAL KEY `idx2` (`c7`)
121 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
122 # Case1: insert on encrypt and non encrypt table in parallel during kill
123 # In connection con1 - Running insert on encrypt table
124 call tde_db.populate_t_encrypt();
125 # In connection con2 - Running insert on encrypt table
126 call tde_db.populate_t_encrypt();
127 # In connection con3 - Running insert into non encrypt table
128 call tde_db.populate_t_non_encrypt();
129 # kill and restart the server
130 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
131 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)'));
132 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
133 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)'));
134 SELECT 1;
135 1
136 1
137 # Case2: insert/update/delete on encrypt in parallel during kill
138 DROP TABLE tde_db.t_encrypt;
139 DROP TABLE tde_db.t_non_encrypt;
140 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
141 c3 CHAR(255) Default 'No text',
142 c4 JSON,
143 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
144 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
145 c7 POINT NOT NULL SRID 0,
146 spatial INDEX idx2 (c7)
147 ) ENCRYPTION="Y" ENGINE = InnoDB;
148 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
149 c3 CHAR(255) Default 'No text',
150 c4 JSON,
151 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
152 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
153 c7 POINT NOT NULL SRID 0,
154 spatial INDEX idx2 (c7)
155 ) ENGINE = InnoDB;
156 # In connection con1 - Running insert on encrypt table
157 call tde_db.populate_t_encrypt();
158 # In connection con2 - Running update on encrypt table
159 call tde_db.update_t_encrypt();
160 # In connection con3 - Running delete on encrypt table
161 call tde_db.delete_t_encrypt();
162 # kill and restart the server
163 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
164 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)'));
165 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
166 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)'));
167 SELECT 1;
168 1
169 1
170 # Case3: select,create and insert on encrypt in parallel during kill
171 DROP TABLE tde_db.t_encrypt;
172 DROP TABLE tde_db.t_non_encrypt;
173 CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
174 c3 CHAR(255) Default 'No text',
175 c4 JSON,
176 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
177 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
178 c7 POINT NOT NULL SRID 0,
179 spatial INDEX idx2 (c7)
180 ) ENCRYPTION="Y" ENGINE = InnoDB;
181 CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
182 c3 CHAR(255) Default 'No text',
183 c4 JSON,
184 c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
185 c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
186 c7 POINT NOT NULL SRID 0,
187 spatial INDEX idx2 (c7)
188 ) ENGINE = InnoDB;
189 # In connection con1 - Running insert on encrypt table
190 call tde_db.populate_t_encrypt();
191 # In connection con2 - Running select on encrypt table
192 call tde_db.read_t_encrypt();
193 # kill and restart the server
194 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
195 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)'));
196 SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
197 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)'));
198 SELECT 1;
199 1
200 1
201 DROP DATABASE tde_db;
202 SET GLOBAL innodb_file_per_table=1;
203 # ----------------------------------------------------------------------
204 # Teardown
205 # Removing manifest file for current MySQL server instance
206 # Removing local keyring file for keyring component: component_keyring_file
207 # Removing local configuration file for keyring component: component_keyring_file
208 # Restarting server without the manifest file
209 # ----------------------------------------------------------------------