421 lines
13 KiB
Plaintext
421 lines
13 KiB
Plaintext
#########################################################################
|
|
# START : WITHOUT KEYRING PLUGIN
|
|
#########################################################################
|
|
|
|
#########
|
|
# SETUP #
|
|
#########
|
|
CREATE TABLESPACE encrypt_ts ADD DATAFILE 'encrypt_ts.ibd' ENGINE=InnoDB ENCRYPTION="N";
|
|
CREATE TABLE t1(c1 char(100)) ENGINE=InnoDB TABLESPACE encrypt_ts;
|
|
set global innodb_buf_flush_list_now = 1;
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts N
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
|
|
ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
|
|
#############################################################
|
|
# TEST 1 : CRASH DURING ALTER ENCRYPT A TABLESPACE.
|
|
#############################################################
|
|
|
|
#########################################################################
|
|
# RESTART 1 : WITH KEYRING PLUGIN
|
|
#########################################################################
|
|
############################################################
|
|
# ALTER TABLESPACE 1 : Unencrypted => Encrypted #
|
|
# (crash at page 10) #
|
|
############################################################
|
|
# Set Encryption process to crash at page 10
|
|
SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
|
|
# Encrypt the tablespace. It will cause crash.
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
|
|
# Restart after crash
|
|
# Wait for Encryption processing to finish in background thread
|
|
set global innodb_buf_flush_list_now = 1;
|
|
# After restart/recovery, check that Encryption was roll-forward
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts Y
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
#########################################################################
|
|
# RESTART 2 : WITHOUT KEYRING PLUGIN
|
|
#########################################################################
|
|
SELECT * FROM t1 LIMIT 10;
|
|
ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
|
|
#########################################################################
|
|
# RESTART 3 : WITH KEYRING PLUGIN
|
|
#########################################################################
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts Y
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
############################################################
|
|
# ALTER TABLESPACE 2 : Encrypted => Unencrypted #
|
|
# (crash at page 10) #
|
|
############################################################
|
|
# Set Unencryption process to crash at page 10
|
|
SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
|
|
# Unencrypt the tablespace. It will cause crash.
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
|
|
# Restart after crash
|
|
# Wait for Unencryption processing to finish in background thread
|
|
set global innodb_buf_flush_list_now = 1;
|
|
# After restart/recovery, check that Unencryption was roll-forward
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts N
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
#########################################################################
|
|
# RESTART 4 : WITHOUT KEYRING PLUGIN
|
|
#########################################################################
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts N
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
####################################################################
|
|
# TEST 2 : CRASH DURING ALTER ENCRYPT A TABLESPACE (Compressed).
|
|
####################################################################
|
|
|
|
CREATE TABLESPACE compress_ts ADD DATAFILE 'compress_ts.ibd' ENGINE=InnoDB
|
|
ENCRYPTION="N" FILE_BLOCK_SIZE=8192;
|
|
CREATE TABLE t2(c1 char(100)) ENGINE=InnoDB TABLESPACE compress_ts
|
|
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
|
|
set global innodb_buf_flush_list_now = 1;
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME='compress_ts';
|
|
NAME ENCRYPTION
|
|
compress_ts N
|
|
SELECT * FROM t2 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
#########################################################################
|
|
# RESTART 5 : WITH KEYRING PLUGIN
|
|
#########################################################################
|
|
############################################################
|
|
# ALTER TABLESPACE 1 : Unencrypted => Encrypted #
|
|
# (crash at page 10) #
|
|
############################################################
|
|
# Set Encryption process to crash at page 10
|
|
SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
|
|
# Encrypt the tablespace. It will cause crash.
|
|
ALTER TABLESPACE compress_ts ENCRYPTION='Y';
|
|
# Restart after crash
|
|
# Wait for Encryption processing to finish in background thread
|
|
set global innodb_buf_flush_list_now = 1;
|
|
# After restart/recovery, check that Encryption was roll-forward
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME='compress_ts';
|
|
NAME ENCRYPTION
|
|
compress_ts Y
|
|
SELECT * FROM t2 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
#########################################################################
|
|
# RESTART 6 : WITHOUT KEYRING PLUGIN
|
|
#########################################################################
|
|
SELECT * FROM t2 LIMIT 10;
|
|
ERROR HY000: Can't find master key from keyring, please check in the server log if a keyring plugin is loaded and initialized successfully.
|
|
#########################################################################
|
|
# RESTART 7 WITH KEYRING PLUGIN
|
|
#########################################################################
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME='compress_ts';
|
|
NAME ENCRYPTION
|
|
compress_ts Y
|
|
SELECT * FROM t2 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
############################################################
|
|
# ALTER TABLESPACE 2 : Encrypted => Unencrypted #
|
|
# (crash at page 10) #
|
|
############################################################
|
|
# Set Unencryption process to crash at page 10
|
|
SET SESSION debug= '+d,alter_encrypt_tablespace_page_10';
|
|
# Unencrypt the tablespace. It will cause crash.
|
|
ALTER TABLESPACE compress_ts ENCRYPTION='N';
|
|
# Restart after crash
|
|
# Wait for Unencryption processing to finish in background thread
|
|
set global innodb_buf_flush_list_now = 1;
|
|
# After restart/recovery, check that Unencryption was roll-forward
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME='compress_ts';
|
|
NAME ENCRYPTION
|
|
compress_ts N
|
|
SELECT * FROM t2 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
#########################################################################
|
|
# RESTART 8 : WITHOUT KEYRING PLUGIN
|
|
#########################################################################
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts N
|
|
SELECT * FROM t2 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
DROP TABLE t2;
|
|
DROP TABLESPACE compress_ts;
|
|
#############################################################
|
|
# TEST 3 : CRASH BEFORE/AFTER ENCRYPTION PROCESSING.
|
|
#############################################################
|
|
|
|
#########################################################################
|
|
# RESTART 9 : WITH KEYRING PLUGIN
|
|
#########################################################################
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
# Set server to crash just before encryption processing starts
|
|
SET SESSION debug="+d,alter_encrypt_tablespace_crash_before_processing";
|
|
# Unencrypt the tablespace. It will cause crash.
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
|
|
# Restart after crash
|
|
# Wait for Unencryption processing to finish in background thread
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts Y
|
|
# Set server to crash just after encryption processing finishes
|
|
SET SESSION debug="-d,alter_encrypt_tablespace_crash_before_processing";
|
|
SET SESSION debug="+d,alter_encrypt_tablespace_crash_after_processing";
|
|
# Unencrypt the tablespace. It will cause crash.
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';
|
|
# Restart after crash
|
|
# Wait for Unencryption processing to finish in background thread
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME='encrypt_ts';
|
|
NAME ENCRYPTION
|
|
encrypt_ts N
|
|
#############################################################
|
|
# TEST 4 : CRASH DURING KEY ROTATION.
|
|
#############################################################
|
|
|
|
#########################################################################
|
|
# RESTART 10 : WITH KEYRING PLUGIN
|
|
#########################################################################
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='Y';
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
# Set server to crash while rotating encryption
|
|
SET SESSION debug="+d,ib_crash_during_rotation_for_encryption";
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
# Restart after crash
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SET SESSION debug="-d,ib_crash_during_rotation_for_encryption";
|
|
ALTER INSTANCE ROTATE INNODB MASTER KEY;
|
|
SELECT * FROM t1 LIMIT 10;
|
|
c1
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
SOME VALUES
|
|
###############################################################
|
|
# TEST 5 : CRASH DURING CREATING AN ENCRYPTED TABLESPACE
|
|
###############################################################
|
|
|
|
# Set server to crash while creating an encrypted tablespace
|
|
SET SESSION debug="+d,ib_crash_during_create_tablespace_for_encryption";
|
|
# Try to create an encrypted tablespace. It will cause server to crash.
|
|
CREATE TABLESPACE encrypt_ts_2 ADD DATAFILE 'encrypt_ts_2.ibd' ENGINE=InnoDB ENCRYPTION="Y";
|
|
# Restart after crash
|
|
SET SESSION debug="-d,ib_crash_during_create_tablespace_for_encryption";
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE "%encrypt_ts%";
|
|
NAME ENCRYPTION
|
|
encrypt_ts Y
|
|
###############################################################
|
|
# TEST 6 : DMLs ALLOWED AND DDLs BLOCKED TEST
|
|
###############################################################
|
|
SELECT ENABLED FROM performance_schema.setup_instruments
|
|
WHERE NAME='wait/lock/metadata/sql/mdl';
|
|
ENABLED
|
|
YES
|
|
SET DEBUG_SYNC = 'innodb_alter_encrypt_tablespace SIGNAL s1 WAIT_FOR s2';
|
|
ALTER TABLESPACE encrypt_ts ENCRYPTION='N';;
|
|
# Session 2
|
|
SET DEBUG_SYNC = 'now WAIT_FOR s1';
|
|
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
|
|
TABLESPACE encrypt_ts EXCLUSIVE TRANSACTION GRANTED
|
|
DESCRIBE t1;
|
|
Field Type Null Key Default Extra
|
|
c1 char(100) YES NULL
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` char(100) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!80016 ENCRYPTION='Y' */
|
|
SELECT COUNT(*) FROM t1;
|
|
COUNT(*)
|
|
4096
|
|
INSERT INTO t1 VALUES ("SOME VALUES");
|
|
SELECT COUNT(*) from t1;
|
|
COUNT(*)
|
|
4097
|
|
ALTER TABLE t1 ADD COLUMN (c2 int);;
|
|
# Monitoring session
|
|
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
|
|
TABLESPACE encrypt_ts EXCLUSIVE TRANSACTION GRANTED
|
|
# Wait for MDL request by con2 to appear in metadata_locks table.
|
|
OBJECT_TYPE OBJECT_NAME LOCK_TYPE LOCK_DURATION LOCK_STATUS
|
|
TABLE t1 SHARED_UPGRADABLE TRANSACTION PENDING
|
|
SET DEBUG_SYNC = 'now SIGNAL s2';
|
|
# Connection default
|
|
# Connection con2
|
|
# Connection default
|
|
SELECT NAME, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
|
|
WHERE NAME LIKE "%encrypt_ts%";
|
|
NAME ENCRYPTION
|
|
encrypt_ts N
|
|
DESCRIBE t1;
|
|
Field Type Null Key Default Extra
|
|
c1 char(100) YES NULL
|
|
c2 int(11) YES NULL
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`c1` char(100) DEFAULT NULL,
|
|
`c2` int(11) DEFAULT NULL
|
|
) /*!50100 TABLESPACE `encrypt_ts` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
###########
|
|
# Cleanup #
|
|
###########
|
|
DROP TABLE t1;
|
|
DROP TABLESPACE encrypt_ts;
|
|
# Restarting server without keyring to restore server state
|
|
# restart:
|