####################################################################################################
# InnoDB transparent tablespace data encryption for general shared tablespace.
# This test case will test
#    - Create/Alter general shared table spaces with
#      - and without loading keyring plugin
#      - valid/invalid encryption option
#      - creating table with mix and match encryption option of tablespace
#      - algorithm specified
#      - compression specified
#    - Create table with encryption Y/N when innodb_file_per_table is 0
#    - Create temporary encrypted table
#    - Create/Alter a table in encrypted/unencrypted tablespace with encryption='Y/N'
#      and invalid value
#    - Create a table in encrypted tablespace without any encryption option
#    - Altering encryption of system/temporary tablespace
#    - Altering tablespace with algorithm copy/inplace
#    - CREATE/DROP FTS index on table in encrypted tablespace
#    - Moving an encrypted table in system/temporary tablespace
#    - Displaying encryption property of tablespace in i_s.innodb_tablespaces
#    - Check no effect of block_encryption_mode = 'aes-256-cbc' variable on tablespace encryption
#    - Create table in encrypted general tablespace having
#      - numeric, text, binary, JSON data type, VIRTUAL/GENERATED column
#      - spatial index
#      - fulltext index
#    - Create encrypted general tablespace with
#      - PK-FK table
#      - view on encrypted table
#      - trigger on encrypted table
#      - fulltext index
#      - alter encrypted table
#    - Run query on encrypted table part of user defined tablespace
#      - from stored procedure
#      - using transactions
#    - Create tablespace with encryption Y/N with MyISAM/Memory storage engine
#    - Create table in general and file_per_table tablespace and check metadata info
#    - Parsing test of encryption parameter with respect to other tablespace parameters
#    - Create Encrypted tablespace with different file_block_size
#    - Renaming encrypted tablespace and checking metadata for tablespace
#    - Create tables in encrypted tablespace using different  ROW_FORMAT
#    - Create encrypted remote tablespaces
####################################################################################################

--source include/no_valgrind_without_big.inc
# Waiting time when (re)starting the server
--let $explicit_default_wait_counter=10000;

--disable_query_log
call mtr.add_suppression("ibd can't be decrypted, please confirm the keyfile is match and keyring plugin is loaded.");
call mtr.add_suppression("\\[Error\\] \\[[^]]*\\] \\[[^]]*\\] Encryption can't find master key, please check the keyring plugin is loaded.");
call mtr.add_suppression("\\[ERROR\\] \\[[^]]*\\] \\[[^]]*\\] Check keyring plugin fail, please check the keyring plugin is loaded.");
--enable_query_log

--echo #########################################################################
--echo # START : WITHOUT KEYRING PLUGIN
--echo #########################################################################
# Create a tablespace with encryption, should fail since keyring is not
# loaded.
--error ER_CANNOT_FIND_KEY_IN_KEYRING
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='Y';

# Create a tablespace without any encryption option. Should succeed.
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;

# Alter tablespace with encryption option. Should fail since keyring is not
# loaded.
--error ER_CANNOT_FIND_KEY_IN_KEYRING
ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";

DROP TABLESPACE encrypt_ts;

--echo #########################################################################
--echo # RESTART 1 : WITH KEYRING PLUGIN
--echo #########################################################################
let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
--source include/restart_mysqld_no_echo.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo ---------------------------
--echo SYSTEM TABLESPACE
--echo ---------------------------
# Test: create table in system tablespace with encryption option.
# Should fail, since system tablespace is shared tablespace.
--error ER_INVALID_ENCRYPTION_REQUEST
CREATE TABLE t1(c int) ENCRYPTION="Y" tablespace innodb_system ENGINE = InnoDB;
SHOW WARNINGS;

let $innodb_file_per_table = `SELECT @@innodb_file_per_table`;

SET GLOBAL innodb_file_per_table = 0;
SELECT @@innodb_file_per_table;
# Test: create table with encryption Y/N when innodb_file_per_table is 0.
# create table in system tablespace with encryption 'y' should fail, and
# should pass with encryption 'n'.
--error ER_INVALID_ENCRYPTION_REQUEST
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
SHOW WARNINGS;
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="N" ENGINE = InnoDB;
SHOW WARNINGS;
DROP TABLE t1;

--error ER_INVALID_ENCRYPTION_REQUEST
CREATE TABLE t1(c int) ENCRYPTION="Y" tablespace innodb_system ENGINE = InnoDB;
SHOW WARNINGS;
CREATE TABLE t1(c int) ENCRYPTION="N" tablespace innodb_system ENGINE = InnoDB;
SHOW WARNINGS;
DROP TABLE t1;

SET GLOBAL innodb_file_per_table = 1;
SELECT @@innodb_file_per_table;

--error ER_WRONG_TABLESPACE_NAME
ALTER TABLESPACE innodb_system ENCRYPTION='Y';

--echo ---------------------------
--echo TEMPORARY TABLESPACE
--echo ---------------------------
# Test: create temporary encrypted table,
# Should fail, since temporary table cannot use ENCRYPTION clause.
--error ER_CANNOT_USE_ENCRYPTION_CLAUSE
CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="Y";
SHOW WARNINGS;

--error ER_CANNOT_USE_ENCRYPTION_CLAUSE
CREATE TEMPORARY TABLE t1(c int) ENCRYPTION="N";
SHOW WARNINGS;

--echo ------------------------------------------------
--echo CREATE TABLESPACE WITH INVALID ENCRYPTION OPTION
--echo ------------------------------------------------
# Create encrypted tablespace with incorrect option.
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='R';
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=y;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=Y;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=n;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=N;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=1;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=0;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=null;
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='TRUE';
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=TRUE;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=True;
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='True';
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='true';
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=true;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=FALSE;
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='FALSE';
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=False;
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='False';
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=false;
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='false';
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption=-1;
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='';
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="";

--echo ----------------------------------------------------
--echo CREATE ENCRYPTED TABLESPACE IN MyISAM STORAGE ENGINE
--echo ----------------------------------------------------
# Create tablespace with encryption='Y/y/N/n' in MyISAM storage engine.
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='Y';
SHOW WARNINGS;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='y';
SHOW WARNINGS;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='N';
SHOW WARNINGS;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=MyISAM encryption='n';
SHOW WARNINGS;

--echo ----------------------------------------------------
--echo CREATE ENCRYPTED TABLESPACE IN MEMORY STORAGE ENGINE
--echo ----------------------------------------------------
# Create tablespace with encryption='Y/y/N/n' in MyISAM storage engine.
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='Y';
SHOW WARNINGS;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='y';
SHOW WARNINGS;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='N';
SHOW WARNINGS;
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=Memory encryption='n';
SHOW WARNINGS;

--echo ----------------------------------------------
--echo CREATE TABLESPACE WITH VALID ENCRYPTION OPTION
--echo ----------------------------------------------
# Create tablespace with encryption='Y'.
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='Y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace with encryption='y'.
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace with encryption='n'.
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='n';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace with encryption='N'.
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption='N';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace with encryption="Y".
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="Y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace with encryption="y".
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace with encryption="n".
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="n";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace with encryption="N".
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB encryption="N";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
DROP TABLESPACE encrypt_ts;
# Create tablespace without encryption option.
# By Default it should create unencrypted tablespace
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' engine=INNODB;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';

--echo -----------------------------------------------
--echo ALTER TABLESPACE WITH INVALID ENCRYPTION OPTION
--echo -----------------------------------------------
# Alter tablespace encryption with incorrect option.
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='R';
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=1;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=0;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=TRUE;
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='TRUE';
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=True;
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='True';
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=true;
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='true';
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=FALSE;
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='FALSE';
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=False;
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='False';
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=false;
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='false';
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=null;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=n;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=N;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=y;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=N;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION=-1;
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION='';
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLESPACE encrypt_ts ENCRYPTION="";


--echo ---------------------------------------------
--echo ALTER TABLESPACE WITH VALID ENCRYPTION OPTION
--echo ---------------------------------------------
# Note: Here we will be testing alter with single and double quotes.
# N->N.
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# N->n.
ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# n->n.
ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# n->N.
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# N->Y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# Y->Y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# Y->y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# y->y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# y->Y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# Y->n.
ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# n->y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# y->N.
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# N->y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# y->n.
ALTER TABLESPACE encrypt_ts ENCRYPTION='n';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="n";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# n->Y.
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="Y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
# Y->N.
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
ALTER TABLESPACE encrypt_ts ENCRYPTION="N";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';

--echo -----------------------------------------
--echo CREATE/ALTER TABLE in FILE-PER-TABLE TABLESPACE
--echo -----------------------------------------
# Create/Alter an encrypted table without specifying tablespace and check the metadata
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
ALTER TABLE t1 encryption="N";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
DROP TABLE t1;
# Create/Alter an Unencrypted table without specifying tablespace and check the metadata
CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="N" ENGINE = InnoDB;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
ALTER TABLE t1 encryption="Y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
DROP TABLE t1;
# Create/Alter an encrypted table with specifying tablespace as innodb_file_per_table and check
# the metadata
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=innodb_file_per_table ENCRYPTION="Y" ENGINE = InnoDB;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
ALTER TABLE t1 encryption="N";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
DROP TABLE t1;
# Create/alter an Unencrypted table with specifying tablespace as innodb_file_per_table and check
# the metadata
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=innodb_file_per_table ENCRYPTION="N" ENGINE = InnoDB;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
ALTER TABLE t1 encryption="Y";
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
DROP TABLE t1;
# Create a table in Unencrypted tablespace and check the table metadata
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=encrypt_ts ENGINE = InnoDB;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
DROP TABLE t1;
# Create a table in encrypted tablespace  check the table metadata
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE=encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;
DROP TABLE t1;

--echo --------------------------
--echo CREATE/ALTER TABLE IN GENERAL TABLESPACE
--echo --------------------------
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="Y" ENGINE = InnoDB;
DROP TABLE t1;
--error ER_INVALID_ENCRYPTION_REQUEST
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="N" ENGINE = InnoDB;
SHOW WARNINGS;
--error ER_INVALID_ENCRYPTION_OPTION
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="R" ENGINE = InnoDB;
SHOW WARNINGS;

# Alter table in encrypted tablespace with encryption='Y/N' and invalid value
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION='Y' ENGINE = InnoDB;
ALTER TABLE t1 encryption='Y';
--error ER_INVALID_ENCRYPTION_REQUEST
ALTER TABLE t1 encryption='N';
--error ER_INVALID_ENCRYPTION_OPTION
ALTER TABLE t1 encryption='R';
DROP TABLE t1;

# Y->N
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';

# Create a table in unencrypted tablespace with encryption='Y/N' and invalid value
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="N" ENGINE = InnoDB;
DROP TABLE t1;
--error ER_INVALID_ENCRYPTION_REQUEST
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="Y" ENGINE = InnoDB;
SHOW WARNINGS;
--error ER_INVALID_ENCRYPTION_REQUEST
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENCRYPTION="R" ENGINE = InnoDB;
SHOW WARNINGS;

# Alter table in unencrypted tablespace with encryption='Y/N' and invalid value
CREATE TABLE t1(c1 INT, c2 char(20)) TABLESPACE encrypt_ts ENGINE = InnoDB;
--error ER_INVALID_ENCRYPTION_REQUEST
ALTER TABLE t1 encryption='Y';
ALTER TABLE t1 encryption='N';
--error ER_INVALID_ENCRYPTION_REQUEST
ALTER TABLE t1 encryption='R';
DROP TABLE t1;

# N->Y
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';

# Create a table in encrypted tablespace without any encryption option. It should
# inherit tablespace encryption i.e. should be created encrypted.
CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";

SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES(0, "aaaaa");
INSERT INTO t1 VALUES(1, "bbbbb");
INSERT INTO t1 VALUES(2, "ccccc");
INSERT INTO t1 VALUES(3, "ddddd");
INSERT INTO t1 VALUES(4, "eeeee");
INSERT INTO t1 VALUES(5, "fffff");
INSERT INTO t1 VALUES(6, "ggggg");
INSERT INTO t1 VALUES(7, "hhhhh");
INSERT INTO t1 VALUES(8, "iiiii");
INSERT INTO t1 VALUES(9, "jjjjj");
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;
INSERT INTO t1 select * from t1;

SELECT * FROM t1 ORDER BY c1 LIMIT 10;

--echo #########################################################################
--echo # RESTART 2 : WITHOUT KEYRING PLUGIN
--echo #########################################################################
let $restart_parameters = restart:;
-- source include/restart_mysqld_no_echo.inc

# Try to access table in encrypted tablespace
--error ER_CANNOT_FIND_KEY_IN_KEYRING
SELECT * FROM t1 ORDER BY c1 LIMIT 10;

--echo #########################################################################
--echo # RESTART 3 : WITH KEYRING PLUGIN
--echo #########################################################################
let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT;
--source include/restart_mysqld_no_echo.inc

# Confirm the encryption info can be retrieved properly and table can be read.
SELECT * FROM t1 ORDER BY c1 LIMIT 10;

eval SET GLOBAL innodb_file_per_table=$innodb_file_per_table;

# CREATE/DROP FTS index on table
DROP TABLE t1;
CREATE TABLE t1(c2 char(100) , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB
  tablespace=encrypt_ts ENCRYPTION='Y';
ALTER TABLE t1 DROP INDEX idx1;
ALTER TABLE t1 ADD COLUMN c4 CHAR(20);
DROP TABLE t1;

# Test: CREATE/ALTER tablespace with ALGORITHM
# ALGORITHM syntax for ALTER tablespace is not supported. Its always INPLACE
--echo ----------------------
--echo ALGORITHM=COPY/INPLACE
--echo ----------------------
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION="N", algorithm=copy;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts ENCRYPTION="N", algorithm=inplace;
DROP TABLESPACE encrypt_ts;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION="N", algorithm=copy;
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION="N", algorithm=inplace;

# Test: ENCRYPTION with COMPRESSION on tablespace
# COMPRESSION syntax for CREATE/ALTER tablespace is not supported
--echo -----------
--echo COMPRESSION
--echo -----------
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' COMPRESSION = "ZLIB" ENCRYPTION = "Y" ENGINE = InnoDB;
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION = "Y" ENGINE = InnoDB;
--error ER_PARSE_ERROR
ALTER TABLESPACE encrypt_ts COMPRESSION = "ZLIB";
DROP TABLESPACE encrypt_ts;

# Create ENCRYPTED tablespace with different FILE_BLOCK_SIZE
CREATE TABLESPACE encryt_ts_1k ADD DATAFILE 'encrypt_ts_1k.ibd' FILE_BLOCK_SIZE=1k ENCRYPTION='Y';
CREATE TABLESPACE encryt_ts_2k ADD DATAFILE 'encrypt_ts_2k.ibd' FILE_BLOCK_SIZE=2k ENCRYPTION='Y';
CREATE TABLESPACE encryt_ts_4k ADD DATAFILE 'encrypt_ts_4k.ibd' FILE_BLOCK_SIZE=4k ENCRYPTION='Y';
CREATE TABLESPACE encryt_ts_8k ADD DATAFILE 'encrypt_ts_8k.ibd' FILE_BLOCK_SIZE=8k ENCRYPTION='Y';
CREATE TABLESPACE encryt_ts_16k ADD DATAFILE 'encrypt_ts_16k.ibd' FILE_BLOCK_SIZE=16k ENCRYPTION='Y';
# Compressed tablespace not supported with innodb_page_size > 16K
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLESPACE encryt_ts_32k ADD DATAFILE 'encrypt_ts_32k.ibd' FILE_BLOCK_SIZE=32k ENCRYPTION='Y';
SHOW WARNINGS;
DROP tablespace encryt_ts_1k;
DROP tablespace encryt_ts_2k;
DROP tablespace encryt_ts_4k;
DROP tablespace encryt_ts_8k;
DROP tablespace encryt_ts_16k;

# Parsing test of ENCRYPTION parameter with respect to other tablespace parameters
--error ER_PARSE_ERROR
CREATE TABLESPACE encrypt_ts  ENCRYPTION='Y' ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M
MAX_SIZE = 100M NODEGROUP = 5 WAIT  COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION='Y' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M
MAX_SIZE = 100M NODEGROUP = 5 WAIT  COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y' AUTOEXTEND_SIZE = 10M
MAX_SIZE = 100M NODEGROUP = 5 WAIT  COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M ENCRYPTION='Y'
MAX_SIZE = 100M NODEGROUP = 5 WAIT  COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M  MAX_SIZE = 100M
ENCRYPTION='Y' NODEGROUP = 5 WAIT  COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M  MAX_SIZE = 100M
NODEGROUP = 5 ENCRYPTION='Y' WAIT  COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M  MAX_SIZE = 100M
NODEGROUP = 5 WAIT ENCRYPTION='Y' COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M  MAX_SIZE = 100M
NODEGROUP = 5 WAIT  COMMENT = 'TABLESPACE ENCRYPTION' ENCRYPTION='Y' INITIAL_SIZE = 100M EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M  MAX_SIZE = 100M
NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M ENCRYPTION='Y'EXTENT_SIZE = 100M;
DROP TABLESPACE encrypt_ts;

CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB AUTOEXTEND_SIZE = 10M  MAX_SIZE = 100M
NODEGROUP = 5 WAIT COMMENT = 'TABLESPACE ENCRYPTION' INITIAL_SIZE = 100M EXTENT_SIZE = 100M ENCRYPTION='Y';
DROP TABLESPACE encrypt_ts;

# RENAME encrypted tablespace and checking metadata for tablespace
# and table
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;

ALTER TABLESPACE encrypt_ts RENAME TO encrypt_ts_renamed;
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts_renamed';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='test/t1';
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME="t1";
SHOW CREATE TABLE test.t1;

DROP TABLE t1;
DROP TABLESPACE encrypt_ts_renamed;


# CREATE tables in encrypted tablespace using different  ROW_FORMAT
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
# Create Insert Procedure
DELIMITER |;
CREATE PROCEDURE populate_t1()
BEGIN
        DECLARE i int DEFAULT 1;
        START TRANSACTION;
        WHILE (i <= 10) DO
                INSERT INTO t1 VALUES (i,CONCAT('a', i));
                SET i = i + 1;
        END WHILE;
        COMMIT;
END|
DELIMITER ;|
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=COMPRESSED;
SHOW WARNINGS;

--error ER_ILLEGAL_HA
CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=FIXED;

CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=DYNAMIC;
-- disable_query_log
CALL populate_t1();
-- enable_query_log
DROP TABLE t1;

CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=REDUNDANT;
-- disable_query_log
CALL populate_t1();
-- enable_query_log
DROP TABLE t1;

CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' ROW_FORMAT=COMPACT;
-- disable_query_log
CALL populate_t1();
-- enable_query_log
DROP TABLE t1;
DROP PROCEDURE populate_t1;

# CREATE table in encrypted tablespace using KEY_BLOCK_SIZE
--error ER_ILLEGAL_HA_CREATE_OPTION
CREATE TABLE t1(c1 INT, c2 char(20)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y' KEY_BLOCK_SIZE=1;
SHOW WARNINGS;
DROP TABLESPACE encrypt_ts;

# Check no effect of BLOCK_ENCRYPTION_MODE = 'aes-256-cbc' variable on tablespace encryption
SET block_encryption_mode = 'aes-256-cbc';
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE = InnoDB ENCRYPTION='Y';
# Test encryption .
--disable_warnings
DROP DATABASE IF EXISTS tde_db;
CREATE DATABASE tde_db;
CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
--enable_warnings
#
INSERT INTO tde_db.t1 VALUES(0, 'abc');
INSERT INTO tde_db.t1 VALUES(1, 'xyz');
INSERT INTO tde_db.t1 VALUES(2, null);
INSERT INTO tde_db.t1 VALUES(3, null);
SELECT * FROM tde_db.t1 LIMIT 10;
ALTER INSTANCE ROTATE INNODB MASTER KEY;
SELECT * FROM tde_db.t1 LIMIT 10;
DROP DATABASE tde_db;
DROP TABLESPACE encrypt_ts;

# Encrypted general tablespace having table
# 1) With JSON , VIRTUAL/GENERATED column
# 2) With rtree ( spatial index)

--disable_warnings
DROP DATABASE IF EXISTS tde_db;
CREATE DATABASE tde_db;
--enable_warnings
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENCRYPTION ='Y' ENGINE=InnoDB;
CREATE TABLE tde_db.t_encrypt(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 TABLESPACE=encrypt_ts ENCRYPTION='Y';

SHOW CREATE TABLE tde_db.t_encrypt;
INSERT INTO tde_db.t_encrypt(c4,c7) VALUES('{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));
INSERT INTO tde_db.t_encrypt(c4,c7) select c4,c7 from tde_db.t_encrypt;

SELECT c4,c5,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
SELECT c4,c5,c6,ST_AsText(c7) FROM tde_db.t_encrypt LIMIT 10;
SHOW CREATE TABLE tde_db.t_encrypt;


#  Encrypted general tablespace with
#  - PK-FK table
#  - view on encrypted table
#  - trigger on encrypted table
#  - fulltext index
#  - alter encrypted table
--disable_warnings

DROP TABLE IF EXISTS tde_db.t_encrypt;
--enable_warnings

CREATE TABLE tde_db.t_encrypt(c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 char(100), c3 BLOB , FULLTEXT INDEX `idx1` (c2)) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
CREATE TABLE tde_db.t_encrypt1(c11 INT , c22 char(100), c33 BLOB , FULLTEXT INDEX `idx1` (c22)) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';
SHOW CREATE TABLE tde_db.t_encrypt;
SHOW CREATE TABLE tde_db.t_encrypt1;

CREATE TABLE tde_db.t_encrypt2 (f1 INT PRIMARY KEY, f2 CHAR(100),
   FOREIGN KEY (f1) REFERENCES tde_db.t_encrypt(c1) ON UPDATE CASCADE) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';


DELIMITER |;
CREATE TRIGGER tde_db.trigger_encrypt_table AFTER INSERT ON tde_db.t_encrypt
FOR EACH ROW
begin
        INSERT INTO tde_db.t_encrypt1 SET c11 = NEW.c1*-1,  c22 = NEW.c2 , c33 = NEW.c3;
end|
DELIMITER ;|

INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("transparent tablespace encryption",repeat('A', 200));
INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("general tablespace option",repeat('A', 200));
INSERT INTO tde_db.t_encrypt(c2,c3) VALUES("page level encryption",repeat('A', 200));


INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(1,"transparent tablespace encryption");
INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");

SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;

# fulltext query
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace');
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('tablespace' IN BOOLEAN MODE);
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt WHERE MATCH c2 AGAINST ('+tablespace -encryption' IN BOOLEAN MODE);
ALTER TABLE tde_db.t_encrypt DROP INDEX idx1;
SELECT c1,c2,right(c3, 20) FROM tde_db.t_encrypt LIMIT 10;
ALTER TABLE tde_db.t_encrypt ADD COLUMN c4 CHAR(20) DEFAULT 'text';
SELECT c1,c2,right(c3, 20),c4 FROM tde_db.t_encrypt LIMIT 10;

# check view
CREATE VIEW tde_db.t_encrypt_view AS SELECT c1,c2 FROM tde_db.t_encrypt;
SELECT c2 FROM tde_db.t_encrypt_view LIMIT 10;
SELECT A.c2,B.c2,right(B.c3,20) FROM tde_db.t_encrypt_view A , tde_db.t_encrypt B WHERE A.c2 = B.c2;
DROP VIEW tde_db.t_encrypt_view;

# Check triggr table
SELECT c11,c22,right(c33, 20) FROM tde_db.t_encrypt1 LIMIT 10;

# check PK-FK , ON UPDATE CASCADE
# Duplicate key
--ERROR ER_DUP_ENTRY
INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(2,"general tablespace option");
# No entry in PK
--ERROR 1452
INSERT INTO tde_db.t_encrypt2(f1,f2) VALUES(8,"general tablespace option");
SELECT f1,f2 FROM tde_db.t_encrypt2;
UPDATE tde_db.t_encrypt SET c1=10 WHERE c1=1;
SELECT f1,f2 FROM tde_db.t_encrypt2;
DROP DATABASE tde_db;

# Run query on encrypted table part of user defined tablespace
# from stored procedure
# Using transactions
--disable_warnings
DROP DATABASE IF EXISTS tde_db;
CREATE DATABASE tde_db;
USE tde_db;
DROP TABLE IF EXISTS tde_db.t_encrypt;
#
--enable_warnings
CREATE TABLE tde_db.t_encrypt  (c2 INT NOT NULL AUTO_INCREMENT ,c3 VARCHAR(255), 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) , PRIMARY KEY (c2,c3(100))) ENGINE=InnoDB TABLESPACE=encrypt_ts ENCRYPTION='Y';

DELIMITER |;
CREATE PROCEDURE tde_db.txn_t_encrypt()
BEGIN
        declare i int default 0;
        declare rowcnt int default 0;
        START TRANSACTION;
        WHILE (i <= 2000) DO

           SET i = i + 1;
           SET rowcnt = rowcnt + 1;

           INSERT INTO tde_db.t_encrypt(c3,c4,c7) VALUES (CONCAT(REPEAT('a',10),REPEAT(i,10)),'{ "key_a": 1, "key_b": 2, "key_c": 3 }',ST_GeomFromText('POINT(383293632 1754448)'));


           IF (rowcnt = 3) THEN
              UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 21, "key_b": 22, "key_c": 23 }' WHERE c2 = i-1 ;
              DELETE FROM tde_db.t_encrypt WHERE c2 = i;
              SAVEPOINT A;
           END IF;
           IF (rowcnt = 5) THEN
              UPDATE tde_db.t_encrypt SET c4 = '{ "key_a": 41, "key_b": 42, "key_c": 43 }' WHERE c2 = i-1 ;
              DELETE FROM tde_db.t_encrypt WHERE c2 = i;
              SAVEPOINT B;
           END IF;
           IF (rowcnt = 10) THEN
              ROLLBACK TO SAVEPOINT A;
              COMMIT;
              SET rowcnt = 0;
              START TRANSACTION;
           END IF;


        END WHILE;
        COMMIT;
end|
DELIMITER ;|

call tde_db.txn_t_encrypt();
SELECT COUNT(*) FROM tde_db.t_encrypt;
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt LIMIT 10;
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt WHERE c2 > 500 AND c2 < 600;
SELECT c2,RIGHT(c3,20),c4 FROM tde_db.t_encrypt ORDER BY c2 DESC LIMIT 10;
DROP DATABASE tde_db;
DROP TABLESPACE encrypt_ts;

--echo #########################################################################
--echo # RESTART 4 : WITH KEYRING PLUGIN AND --INNODB_DIRECTORIES
--echo #########################################################################
let $restart_parameters = restart: --early-plugin-load=keyring_file=$KEYRING_PLUGIN --loose-keyring_file_data=$MYSQL_TMP_DIR/mysecret_keyring $KEYRING_PLUGIN_OPT --innodb-directories=$MYSQL_TMP_DIR;
--source include/restart_mysqld_no_echo.inc

# Check encrypted tablespace is getting created in remote directory(outside of datadir)
# and data is getting accessed
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLESPACE encrypt_ts ADD DATAFILE '$MYSQL_TMP_DIR/encrypt_ts.ibd' ENCRYPTION='Y';
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES where NAME='encrypt_ts';
CREATE DATABASE tde_db;
CREATE TABLE tde_db.t1(c1 INT PRIMARY KEY, c2 char(50)) ENGINE = InnoDB TABLESPACE encrypt_ts ENCRYPTION='Y';
INSERT INTO tde_db.t1 VALUES(0, 'abc');
INSERT INTO tde_db.t1 VALUES(1, 'xyz');
SELECT * FROM tde_db.t1;
--echo # Remote tablespace listing
--list_files $MYSQL_TMP_DIR/ *.ibd

DROP TABLE tde_db.t1;
DROP DATABASE tde_db;

--echo #########################################################################
--echo # Cleanup
--echo #########################################################################
DROP TABLESPACE encrypt_ts;
remove_file $MYSQL_TMP_DIR/mysecret_keyring;

--echo # Restarting server without keyring to restore server state
let $restart_parameters = restart: ;
--source include/restart_mysqld.inc