# 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,
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,
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`,'$.key_a')) STORED,
  `c6` int(11) GENERATED ALWAYS AS (json_extract(`c4`,'$.key_b')) VIRTUAL,
  `c7` point NOT NULL,
  PRIMARY KEY (`c2`),
  SPATIAL KEY `idx2` (`c7`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 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(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)'));
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(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)'));
SELECT 1;
1
1
# Case2: insert/update/delete on encrypt in parallel during kill
# 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(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)'));
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(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)'));
SELECT 1;
1
1
# Case3: select on encrypt in parallel during kill
# In connection con1 - Running select on encrypt table
call tde_db.read_t_encrypt();
# In connection con2 - Running select on encrypt table
call tde_db.read_t_encrypt();
# In connection con3 - 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(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)'));
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(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)'));
SELECT 1;
1
1
# Case3: select and insert on encrypt in parallel during kill
# 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();
# In connection con3 - Running select on encrypt table
call tde_db.create_t_encrypt("Y",1);
# 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(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)'));
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(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)'));
SELECT 1;
1
1
# restart server without keyring
# restart: 
SET GLOBAL innodb_file_per_table=1;