201 lines
8.2 KiB
Plaintext
201 lines
8.2 KiB
Plaintext
# Starting server with keyring plugin
|
|
# restart: --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=MYSQL_TMP_DIR/mysecret_keyring --plugin-dir=KEYRING_PLUGIN_PATH
|
|
DROP DATABASE IF EXISTS tde_db;
|
|
DROP TABLE IF EXISTS tde_db. t_encrypt;
|
|
CREATE DATABASE tde_db;
|
|
USE tde_db;
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
@@innodb_file_per_table
|
|
1
|
|
CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c3 CHAR(255) Default 'No text',
|
|
c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c3 CHAR(255) Default 'No text',
|
|
c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENGINE = InnoDB;
|
|
CREATE PROCEDURE tde_db.populate_t_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
|
|
while (i <= 5000) DO
|
|
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)'));
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.populate_t_non_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
|
|
while (i <= 5000) DO
|
|
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)'));
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.update_t_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
declare ustr varchar(1000);
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
|
|
while (i <= 5000) DO
|
|
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');
|
|
PREPARE stmt FROM @sql_text;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.delete_t_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
declare ustr varchar(1000);
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1062 SET has_error = 1;
|
|
while (i <= 5000) DO
|
|
SET @sql_text = CONCAT (' DELETE FROM tde_db.t_encrypt LIMIT 1');
|
|
PREPARE stmt FROM @sql_text;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.read_t_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
while (i <= 5000) DO
|
|
SELECT * FROM (SELECT * FROM tde_db.t_encrypt ORDER BY RAND() LIMIT 1) AS A WHERE A.c2 < 0 ;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.alter_t_encrypt()
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
while (i <= 5000) DO
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
CREATE PROCEDURE tde_db.create_t_encrypt(encrypt VARCHAR(5), tcnt INT)
|
|
begin
|
|
declare i int default 1;
|
|
declare has_error int default 0;
|
|
DECLARE CONTINUE HANDLER FOR 1050 SET has_error = 1;
|
|
SET i = tcnt ;
|
|
while (i <= 5000) DO
|
|
SET @sql_text = CONCAT('CREATE TABLE ',CONCAT('tde_db.t_encrypt_',encrypt,'_',i),' (c1 INT) ENCRYPTION="',encrypt,'"' ,' ENGINE=InnoDB');
|
|
PREPARE stmt FROM @sql_text;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
set i = i + 1;
|
|
end while;
|
|
end|
|
|
SHOW CREATE TABLE tde_db.t_encrypt;
|
|
Table Create Table
|
|
t_encrypt CREATE TABLE `t_encrypt` (
|
|
`c2` int(11) NOT NULL AUTO_INCREMENT,
|
|
`c3` char(255) DEFAULT 'No text',
|
|
`c4` json DEFAULT NULL,
|
|
`c5` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_a')) STORED,
|
|
`c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,_utf8mb4'$.key_b')) VIRTUAL,
|
|
`c7` point NOT NULL /*!80003 SRID 0 */,
|
|
PRIMARY KEY (`c2`),
|
|
SPATIAL KEY `idx2` (`c7`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
|
|
# Case1: insert on encrypt and non encrypt table in parallel during kill
|
|
# In connection con1 - Running insert on encrypt table
|
|
call tde_db.populate_t_encrypt();
|
|
# In connection con2 - Running insert on encrypt table
|
|
call tde_db.populate_t_encrypt();
|
|
# In connection con3 - Running insert into non encrypt table
|
|
call tde_db.populate_t_non_encrypt();
|
|
# kill and restart the server
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
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)'));
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
|
|
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)'));
|
|
SELECT 1;
|
|
1
|
|
1
|
|
# Case2: insert/update/delete on encrypt in parallel during kill
|
|
DROP TABLE tde_db.t_encrypt;
|
|
DROP TABLE tde_db.t_non_encrypt;
|
|
CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c3 CHAR(255) Default 'No text',
|
|
c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c3 CHAR(255) Default 'No text',
|
|
c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENGINE = InnoDB;
|
|
# In connection con1 - Running insert on encrypt table
|
|
call tde_db.populate_t_encrypt();
|
|
# In connection con2 - Running update on encrypt table
|
|
call tde_db.update_t_encrypt();
|
|
# In connection con3 - Running delete on encrypt table
|
|
call tde_db.delete_t_encrypt();
|
|
# kill and restart the server
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
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)'));
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
|
|
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)'));
|
|
SELECT 1;
|
|
1
|
|
1
|
|
# Case3: select,create and insert on encrypt in parallel during kill
|
|
DROP TABLE tde_db.t_encrypt;
|
|
DROP TABLE tde_db.t_non_encrypt;
|
|
CREATE TABLE tde_db.t_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c3 CHAR(255) Default 'No text',
|
|
c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENCRYPTION="Y" ENGINE = InnoDB;
|
|
CREATE TABLE tde_db.t_non_encrypt(c2 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c3 CHAR(255) Default 'No text',
|
|
c4 JSON ,
|
|
c5 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_a')) STORED,
|
|
c6 INT GENERATED ALWAYS AS (JSON_EXTRACT(c4,'$.key_b')) VIRTUAL,
|
|
c7 POINT NOT NULL SRID 0,
|
|
spatial INDEX idx2 (c7)
|
|
) ENGINE = InnoDB;
|
|
# In connection con1 - Running insert on encrypt table
|
|
call tde_db.populate_t_encrypt();
|
|
# In connection con2 - Running select on encrypt table
|
|
call tde_db.read_t_encrypt();
|
|
# kill and restart the server
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
|
|
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)'));
|
|
SELECT c2,right(c3,20),c4,c5,c6,ST_AsText(c7) FROM tde_db.t_non_encrypt LIMIT 10;
|
|
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)'));
|
|
SELECT 1;
|
|
1
|
|
1
|
|
DROP DATABASE tde_db;
|
|
# restart server without keyring
|
|
# restart:
|
|
SET GLOBAL innodb_file_per_table=1;
|