Debian Codegraph @

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