summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
authorJan Lindström <jan.lindstrom@mariadb.com>2017-03-14 12:56:01 +0200
committerJan Lindström <jan.lindstrom@mariadb.com>2017-03-14 16:23:10 +0200
commit50eb40a2a8aa3af6cc271f6028f4d6d74301d030 (patch)
treedd194385ba0e8fc0302d17c7a682d3ae3d65b19f /mysql-test
parenta2f34809e55c492af9a23d43840133f01528df7b (diff)
downloadmariadb-git-50eb40a2a8aa3af6cc271f6028f4d6d74301d030.tar.gz
MDEV-11738: Mariadb uses 100% of several of my 8 cpus doing nothing
MDEV-11581: Mariadb starts InnoDB encryption threads when key has not changed or data scrubbing turned off Background: Key rotation is based on background threads (innodb-encryption-threads) periodically going through all tablespaces on fil_system. For each tablespace current used key version is compared to max key age (innodb-encryption-rotate-key-age). This process naturally takes CPU. Similarly, in same time need for scrubbing is investigated. Currently, key rotation is fully supported on Amazon AWS key management plugin only but InnoDB does not have knowledge what key management plugin is used. This patch re-purposes innodb-encryption-rotate-key-age=0 to disable key rotation and background data scrubbing. All new tables are added to special list for key rotation and key rotation is based on sending a event to background encryption threads instead of using periodic checking (i.e. timeout). fil0fil.cc: Added functions fil_space_acquire_low() to acquire a tablespace when it could be dropped concurrently. This function is used from fil_space_acquire() or fil_space_acquire_silent() that will not print any messages if we try to acquire space that does not exist. fil_space_release() to release a acquired tablespace. fil_space_next() to iterate tablespaces in fil_system using fil_space_acquire() and fil_space_release(). Similarly, fil_space_keyrotation_next() to iterate new list fil_system->rotation_list where new tables. are added if key rotation is disabled. Removed unnecessary functions fil_get_first_space_safe() fil_get_next_space_safe() fil_node_open_file(): After page 0 is read read also crypt_info if it is not yet read. btr_scrub_lock_dict_func() buf_page_check_corrupt() buf_page_encrypt_before_write() buf_merge_or_delete_for_page() lock_print_info_all_transactions() row_fts_psort_info_init() row_truncate_table_for_mysql() row_drop_table_for_mysql() Use fil_space_acquire()/release() to access fil_space_t. buf_page_decrypt_after_read(): Use fil_space_get_crypt_data() because at this point we might not yet have read page 0. fil0crypt.cc/fil0fil.h: Lot of changes. Pass fil_space_t* directly to functions needing it and store fil_space_t* to rotation state. Use fil_space_acquire()/release() when iterating tablespaces and removed unnecessary is_closing from fil_crypt_t. Use fil_space_t::is_stopping() to detect when access to tablespace should be stopped. Removed unnecessary fil_space_get_crypt_data(). fil_space_create(): Inform key rotation that there could be something to do if key rotation is disabled and new table with encryption enabled is created. Remove unnecessary functions fil_get_first_space_safe() and fil_get_next_space_safe(). fil_space_acquire() and fil_space_release() are used instead. Moved fil_space_get_crypt_data() and fil_space_set_crypt_data() to fil0crypt.cc. fsp_header_init(): Acquire fil_space_t*, write crypt_data and release space. check_table_options() Renamed FIL_SPACE_ENCRYPTION_* TO FIL_ENCRYPTION_* i_s.cc: Added ROTATING_OR_FLUSHING field to information_schema.innodb_tablespace_encryption to show current status of key rotation.
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/suite/encryption/r/debug_key_management.result4
-rw-r--r--mysql-test/suite/encryption/r/encrypt_and_grep.result35
-rw-r--r--mysql-test/suite/encryption/r/innodb-key-rotation-disable.result66
-rw-r--r--mysql-test/suite/encryption/r/innodb_encryption.result1
-rw-r--r--mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result41
-rw-r--r--mysql-test/suite/encryption/t/encrypt_and_grep.opt3
-rw-r--r--mysql-test/suite/encryption/t/encrypt_and_grep.test29
-rw-r--r--mysql-test/suite/encryption/t/innodb-key-rotation-disable.opt5
-rw-r--r--mysql-test/suite/encryption/t/innodb-key-rotation-disable.test102
-rw-r--r--mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test116
-rw-r--r--mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result2
-rw-r--r--mysql-test/suite/sys_vars/r/sysvars_innodb.result2
12 files changed, 271 insertions, 135 deletions
diff --git a/mysql-test/suite/encryption/r/debug_key_management.result b/mysql-test/suite/encryption/r/debug_key_management.result
index 8793e6ba363..70840fdeeac 100644
--- a/mysql-test/suite/encryption/r/debug_key_management.result
+++ b/mysql-test/suite/encryption/r/debug_key_management.result
@@ -8,13 +8,13 @@ innodb_encryption_rotation_iops 100
innodb_encryption_threads 4
select space,name,current_key_version from information_schema.innodb_tablespaces_encryption order by space;
space name current_key_version
-0 NULL 1
+0 ./ibdata1 1
1 mysql/innodb_table_stats 1
2 mysql/innodb_index_stats 1
set global debug_key_management_version=10;
select space,name,current_key_version from information_schema.innodb_tablespaces_encryption order by space;
space name current_key_version
-0 NULL 10
+0 ./ibdata1 10
1 mysql/innodb_table_stats 10
2 mysql/innodb_index_stats 10
set global innodb_encrypt_tables=OFF;
diff --git a/mysql-test/suite/encryption/r/encrypt_and_grep.result b/mysql-test/suite/encryption/r/encrypt_and_grep.result
index f95e70bf19e..e08ac349c68 100644
--- a/mysql-test/suite/encryption/r/encrypt_and_grep.result
+++ b/mysql-test/suite/encryption/r/encrypt_and_grep.result
@@ -6,6 +6,16 @@ insert t1 values (repeat('foobar', 42));
insert t2 values (repeat('temp', 42));
insert t3 values (repeat('dummy', 42));
# Wait max 10 min for key encryption threads to encrypt all spaces
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+NAME
+test/t3
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+NAME
+mysql/innodb_table_stats
+mysql/innodb_index_stats
+test/t1
+test/t2
+./ibdata1
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
@@ -15,13 +25,23 @@ FOUND /dummy/ in t3.ibd
# ibdata1 expecting NOT FOUND
NOT FOUND /foobar/ in ibdata1
# Now turn off encryption and wait for threads to decrypt everything
-SET GLOBAL innodb_encryption_threads = 4;
+SET GLOBAL innodb_encryption_threads = 1;
SET GLOBAL innodb_encrypt_tables = off;
# Wait max 10 min for key encryption threads to decrypt all spaces
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+NAME
+mysql/innodb_table_stats
+mysql/innodb_index_stats
+test/t2
+test/t3
+./ibdata1
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+NAME
+test/t1
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting FOUND
-FOUND /temp/ in t2.ibd
+NOT FOUND /temp/ in t2.ibd
# t3 no on expecting FOUND
FOUND /dummy/ in t3.ibd
# ibdata1 expecting NOT FOUND
@@ -30,6 +50,16 @@ NOT FOUND /foobar/ in ibdata1
SET GLOBAL innodb_encryption_threads = 4;
SET GLOBAL innodb_encrypt_tables = on;
# Wait max 10 min for key encryption threads to encrypt all spaces
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+NAME
+test/t3
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+NAME
+mysql/innodb_table_stats
+mysql/innodb_index_stats
+test/t1
+test/t2
+./ibdata1
# t1 yes on expecting NOT FOUND
NOT FOUND /foobar/ in t1.ibd
# t2 ... on expecting NOT FOUND
@@ -38,5 +68,4 @@ NOT FOUND /temp/ in t2.ibd
FOUND /dummy/ in t3.ibd
# ibdata1 expecting NOT FOUND
NOT FOUND /foobar/ in ibdata1
-# TODO: add shutdown + grep tests
drop table t1, t2, t3;
diff --git a/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
new file mode 100644
index 00000000000..eeb338cad61
--- /dev/null
+++ b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
@@ -0,0 +1,66 @@
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+NAME
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+NAME
+mysql/innodb_table_stats
+mysql/innodb_index_stats
+./ibdata1
+create database enctests;
+use enctests;
+create table t1(a int not null primary key, b char(200)) engine=innodb;
+create table t2(a int not null primary key, b char(200)) engine=innodb row_format=compressed;
+create table t3(a int not null primary key, b char(200)) engine=innodb page_compressed=yes;
+create table t4(a int not null primary key, b char(200)) engine=innodb encrypted=yes;
+create table t5(a int not null primary key, b char(200)) engine=innodb encrypted=yes row_format=compressed;
+create table t6(a int not null primary key, b char(200)) engine=innodb encrypted=yes page_compressed=yes;
+create table t7(a int not null primary key, b char(200)) engine=innodb encrypted=no;
+create table t8(a int not null primary key, b char(200)) engine=innodb encrypted=no row_format=compressed;
+create table t9(a int not null primary key, b char(200)) engine=innodb encrypted=no page_compressed=yes;
+insert into t1 values (1, 'secredmessage');
+insert into t2 values (1, 'secredmessage');
+insert into t3 values (1, 'secredmessagecompressedaaaaaaaaabbbbbbbbbbbbbbccccccccccccccc');
+insert into t4 values (1, 'secredmessage');
+insert into t5 values (1, 'secredmessage');
+insert into t6 values (1, 'secredmessagecompressedaaaaaaaaabbbbbbbbbbbbbbccccccccccccccc');
+insert into t7 values (1, 'publicmessage');
+insert into t8 values (1, 'publicmessage');
+insert into t9 values (1, 'pugliccompressedaaaaaaaaabbbbbbbbbbbbbbccccccccccccccc');
+# should list tables t1-t6
+SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 AND NAME LIKE 'enctests%';
+NAME ENCRYPTION_SCHEME CURRENT_KEY_ID
+enctests/t1 1 1
+enctests/t2 1 1
+enctests/t3 1 1
+enctests/t4 1 1
+enctests/t5 1 1
+enctests/t6 1 1
+# should list tables t7-t9
+SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 and NAME LIKE 'enctests%';
+NAME ENCRYPTION_SCHEME CURRENT_KEY_ID
+enctests/t7 0 1
+enctests/t8 0 1
+enctests/t9 0 1
+SET GLOBAL innodb_encrypt_tables=OFF;
+ERROR 42000: Variable 'innodb_encrypt_tables' can't be set to the value of 'OFF'
+SET GLOBAL innodb_encrypt_tables=ON;
+ERROR 42000: Variable 'innodb_encrypt_tables' can't be set to the value of 'ON'
+# t1 default on expecting NOT FOUND
+NOT FOUND /secred/ in t1.ibd
+# t2 default on expecting NOT FOUND
+NOT FOUND /secred/ in t2.ibd
+# t3 default on expecting NOT FOUND
+NOT FOUND /secred/ in t3.ibd
+# t4 on expecting NOT FOUND
+NOT FOUND /secred/ in t4.ibd
+# t5 on expecting NOT FOUND
+NOT FOUND /secred/ in t5.ibd
+# t6 on expecting NOT FOUND
+NOT FOUND /secred/ in t6.ibd
+# t7 off expecting FOUND
+FOUND /public/ in t7.ibd
+# t8 row compressed expecting NOT FOUND
+FOUND /public/ in t8.ibd
+# t9 page compressed expecting NOT FOUND
+NOT FOUND /public/ in t9.ibd
+use test;
+drop database enctests;
diff --git a/mysql-test/suite/encryption/r/innodb_encryption.result b/mysql-test/suite/encryption/r/innodb_encryption.result
index 9b762bbba11..26c77499d25 100644
--- a/mysql-test/suite/encryption/r/innodb_encryption.result
+++ b/mysql-test/suite/encryption/r/innodb_encryption.result
@@ -17,6 +17,7 @@ CURRENT_KEY_VERSION int(11) unsigned NO 0
KEY_ROTATION_PAGE_NUMBER bigint(21) unsigned YES NULL
KEY_ROTATION_MAX_PAGE_NUMBER bigint(21) unsigned YES NULL
CURRENT_KEY_ID int(11) unsigned NO 0
+ROTATING_OR_FLUSHING int(1) unsigned NO 0
# Wait max 5 min for key encryption threads to encrypt one space
# Success!
# Wait max 10 min for key encryption threads to encrypt all space
diff --git a/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result b/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result
index a3a3ab3fed6..a86f762af0a 100644
--- a/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result
+++ b/mysql-test/suite/encryption/r/innodb_onlinealter_encryption.result
@@ -24,7 +24,7 @@ end while;
end//
commit;
set autocommit=0;
-call innodb_insert_proc(15000);
+call innodb_insert_proc(1500);
commit;
set autocommit=1;
# Wait max 10 min for key encryption threads to encrypt all spaces
@@ -42,6 +42,8 @@ NOT FOUND /author/ in t5.ibd
NOT FOUND /mangled/ in t6.ibd
# t7 ... on expecting NOT FOUND
NOT FOUND /mysql/ in t7.ibd
+SET GLOBAL innodb_file_format = `Barracuda`;
+SET GLOBAL innodb_file_per_table = ON;
ALTER TABLE t1 ADD COLUMN b int default 2;
ALTER TABLE t2 ADD COLUMN b int default 2;
ALTER TABLE t7 ADD COLUMN b int default 2;
@@ -135,42 +137,5 @@ NOT FOUND /author/ in t5.ibd
NOT FOUND /mangled/ in t6.ibd
# t7 ... on expecting NOT FOUND
NOT FOUND /mysql/ in t7.ibd
-# Restarting server
-# Done restarting server
-select count(1) from t1;
-count(1)
-15000
-select count(1) from t2;
-count(1)
-15000
-select count(1) from t3;
-count(1)
-15000
-select count(1) from t4;
-count(1)
-15000
-select count(1) from t5;
-count(1)
-15000
-select count(1) from t6;
-count(1)
-15000
-select count(1) from t7;
-count(1)
-15000
-# t1 yes on expecting NOT FOUND
-NOT FOUND /foobar/ in t1.ibd
-# t2 ... on expecting NOT FOUND
-NOT FOUND /temp/ in t2.ibd
-# t3 ... on expecting NOT FOUND
-NOT FOUND /barfoo/ in t3.ibd
-# t4 ... on expecting NOT FOUND
-NOT FOUND /repeat/ in t4.ibd
-# t5 ... on expecting NOT FOUND
-NOT FOUND /author/ in t5.ibd
-# t6 ... on expecting NOT FOUND
-NOT FOUND /mangled/ in t6.ibd
-# t7 ... on expecting NOT FOUND
-NOT FOUND /mysql/ in t7.ibd
DROP PROCEDURE innodb_insert_proc;
DROP TABLE t1, t2, t3, t4, t5, t6, t7;
diff --git a/mysql-test/suite/encryption/t/encrypt_and_grep.opt b/mysql-test/suite/encryption/t/encrypt_and_grep.opt
index bcff011eb82..5c9aaf65b06 100644
--- a/mysql-test/suite/encryption/t/encrypt_and_grep.opt
+++ b/mysql-test/suite/encryption/t/encrypt_and_grep.opt
@@ -1,8 +1,7 @@
--innodb-encrypt-tables=ON
--innodb-encrypt-log=ON
--innodb-encryption-rotate-key-age=15
---innodb-encryption-threads=4
+--innodb-encryption-threads=1
--innodb-tablespaces-encryption
---innodb-max-dirty-pages-pct=0.001
diff --git a/mysql-test/suite/encryption/t/encrypt_and_grep.test b/mysql-test/suite/encryption/t/encrypt_and_grep.test
index 2a5fcbcebf8..fd54fc74f0a 100644
--- a/mysql-test/suite/encryption/t/encrypt_and_grep.test
+++ b/mysql-test/suite/encryption/t/encrypt_and_grep.test
@@ -1,5 +1,5 @@
-- source include/have_innodb.inc
--- source include/have_example_key_management_plugin.inc
+-- source include/have_file_key_management_plugin.inc
# embedded does not support restart
-- source include/not_embedded.inc
@@ -30,7 +30,10 @@ insert t3 values (repeat('dummy', 42));
--let $wait_condition=SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0
--source include/wait_condition.inc
---sleep 5
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+
+--source include/shutdown_mysqld.inc
--let SEARCH_PATTERN=foobar
--echo # t1 yes on expecting NOT FOUND
@@ -49,15 +52,21 @@ insert t3 values (repeat('dummy', 42));
-- let SEARCH_FILE=$ib1_IBD
-- source include/search_pattern_in_file.inc
+-- source include/start_mysqld.inc
+
--echo # Now turn off encryption and wait for threads to decrypt everything
-SET GLOBAL innodb_encryption_threads = 4;
+SET GLOBAL innodb_encryption_threads = 1;
SET GLOBAL innodb_encrypt_tables = off;
--echo # Wait max 10 min for key encryption threads to decrypt all spaces
--let $wait_timeout= 600
---let $wait_condition=SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+--let $wait_condition=SELECT COUNT(*) = 5 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND KEY_ROTATION_PAGE_NUMBER IS NULL;
--source include/wait_condition.inc
---sleep 5
+
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+
+--source include/shutdown_mysqld.inc
--let SEARCH_PATTERN=foobar
--echo # t1 yes on expecting NOT FOUND
@@ -76,6 +85,8 @@ SET GLOBAL innodb_encrypt_tables = off;
-- let SEARCH_FILE=$ib1_IBD
-- source include/search_pattern_in_file.inc
+-- source include/start_mysqld.inc
+
--echo # Now turn on encryption and wait for threads to encrypt all spaces
SET GLOBAL innodb_encryption_threads = 4;
SET GLOBAL innodb_encrypt_tables = on;
@@ -84,7 +95,11 @@ SET GLOBAL innodb_encrypt_tables = on;
--let $wait_timeout= 600
--let $wait_condition=SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
--source include/wait_condition.inc
---sleep 5
+
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+
+--source include/shutdown_mysqld.inc
--let SEARCH_PATTERN=foobar
--echo # t1 yes on expecting NOT FOUND
@@ -103,6 +118,6 @@ SET GLOBAL innodb_encrypt_tables = on;
-- let SEARCH_FILE=$ib1_IBD
-- source include/search_pattern_in_file.inc
---echo # TODO: add shutdown + grep tests
+-- source include/start_mysqld.inc
drop table t1, t2, t3;
diff --git a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.opt b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.opt
new file mode 100644
index 00000000000..03a0028d371
--- /dev/null
+++ b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.opt
@@ -0,0 +1,5 @@
+--innodb-encrypt-tables
+--innodb-encrypt-log
+--innodb-encryption-rotate-key-age=0
+--innodb-encryption-threads=4
+--innodb-tablespaces-encryption
diff --git a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
new file mode 100644
index 00000000000..fdbd6c8da7c
--- /dev/null
+++ b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
@@ -0,0 +1,102 @@
+-- source include/have_innodb.inc
+-- source include/have_file_key_management_plugin.inc
+# not embedded because of restarts
+-- source include/not_embedded.inc
+
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0;
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
+
+--disable_query_log
+--disable_warnings
+let $innodb_compression_algorithm_orig=`SELECT @@innodb_compression_algorithm`;
+let $innodb_file_format_orig = `SELECT @@innodb_file_format`;
+let $innodb_file_per_table_orig = `SELECT @@innodb_file_per_table`;
+let $encryption = `SELECT @@innodb_encrypt_tables`;
+SET GLOBAL innodb_file_format = `Barracuda`;
+SET GLOBAL innodb_file_per_table = ON;
+# zlib
+set global innodb_compression_algorithm = 1;
+--enable_warnings
+--enable_query_log
+
+create database enctests;
+use enctests;
+create table t1(a int not null primary key, b char(200)) engine=innodb;
+create table t2(a int not null primary key, b char(200)) engine=innodb row_format=compressed;
+create table t3(a int not null primary key, b char(200)) engine=innodb page_compressed=yes;
+create table t4(a int not null primary key, b char(200)) engine=innodb encrypted=yes;
+create table t5(a int not null primary key, b char(200)) engine=innodb encrypted=yes row_format=compressed;
+create table t6(a int not null primary key, b char(200)) engine=innodb encrypted=yes page_compressed=yes;
+create table t7(a int not null primary key, b char(200)) engine=innodb encrypted=no;
+create table t8(a int not null primary key, b char(200)) engine=innodb encrypted=no row_format=compressed;
+create table t9(a int not null primary key, b char(200)) engine=innodb encrypted=no page_compressed=yes;
+
+insert into t1 values (1, 'secredmessage');
+insert into t2 values (1, 'secredmessage');
+insert into t3 values (1, 'secredmessagecompressedaaaaaaaaabbbbbbbbbbbbbbccccccccccccccc');
+insert into t4 values (1, 'secredmessage');
+insert into t5 values (1, 'secredmessage');
+insert into t6 values (1, 'secredmessagecompressedaaaaaaaaabbbbbbbbbbbbbbccccccccccccccc');
+insert into t7 values (1, 'publicmessage');
+insert into t8 values (1, 'publicmessage');
+insert into t9 values (1, 'pugliccompressedaaaaaaaaabbbbbbbbbbbbbbccccccccccccccc');
+
+--echo # should list tables t1-t6
+SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 AND NAME LIKE 'enctests%';
+--echo # should list tables t7-t9
+SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 and NAME LIKE 'enctests%';
+
+--error ER_WRONG_VALUE_FOR_VAR
+SET GLOBAL innodb_encrypt_tables=OFF;
+--error ER_WRONG_VALUE_FOR_VAR
+SET GLOBAL innodb_encrypt_tables=ON;
+
+--let $MYSQLD_DATADIR=`select @@datadir`
+
+-- source include/shutdown_mysqld.inc
+
+--let SEARCH_RANGE = 10000000
+--let SEARCH_PATTERN=secred
+--echo # t1 default on expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t1.ibd
+-- source include/search_pattern_in_file.inc
+--echo # t2 default on expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t2.ibd
+-- source include/search_pattern_in_file.inc
+--echo # t3 default on expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t3.ibd
+-- source include/search_pattern_in_file.inc
+--echo # t4 on expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t4.ibd
+-- source include/search_pattern_in_file.inc
+--echo # t5 on expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t5.ibd
+-- source include/search_pattern_in_file.inc
+--echo # t6 on expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t6.ibd
+-- source include/search_pattern_in_file.inc
+--let SEARCH_PATTERN=public
+--echo # t7 off expecting FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t7.ibd
+-- source include/search_pattern_in_file.inc
+--echo # t8 row compressed expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t8.ibd
+-- source include/search_pattern_in_file.inc
+--echo # t9 page compressed expecting NOT FOUND
+-- let SEARCH_FILE=$MYSQLD_DATADIR/enctests/t9.ibd
+-- source include/search_pattern_in_file.inc
+
+-- source include/start_mysqld.inc
+
+use test;
+drop database enctests;
+# reset system
+
+--disable_query_log
+--disable_warnings
+EVAL SET GLOBAL innodb_compression_algorithm = $innodb_compression_algorithm_orig;
+EVAL SET GLOBAL innodb_file_per_table = $innodb_file_per_table_orig;
+EVAL SET GLOBAL innodb_file_format = $innodb_file_format_orig;
+set global innodb_compression_algorithm = DEFAULT;
+--enable_warnings
+--enable_query_log
diff --git a/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test
index 40beb10bcdb..2dfbb068c36 100644
--- a/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test
+++ b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test
@@ -1,26 +1,12 @@
-- source include/have_innodb.inc
--- source include/have_example_key_management_plugin.inc
--- source include/not_valgrind.inc
+-- source include/have_file_key_management_plugin.inc
+# test uses restart
-- source include/not_embedded.inc
--- source include/not_windows.inc
-
---disable_query_log
-let $innodb_file_format_orig = `SELECT @@innodb_file_format`;
-let $innodb_file_per_table_orig = `SELECT @@innodb_file_per_table`;
---enable_query_log
+--disable_warnings
SET GLOBAL innodb_file_format = `Barracuda`;
SET GLOBAL innodb_file_per_table = ON;
-
---let $MYSQLD_DATADIR=`select @@datadir`
---let SEARCH_RANGE = 10000000
---let t1_IBD = $MYSQLD_DATADIR/test/t1.ibd
---let t2_IBD = $MYSQLD_DATADIR/test/t2.ibd
---let t3_IBD = $MYSQLD_DATADIR/test/t3.ibd
---let t4_IBD = $MYSQLD_DATADIR/test/t4.ibd
---let t5_IBD = $MYSQLD_DATADIR/test/t5.ibd
---let t6_IBD = $MYSQLD_DATADIR/test/t6.ibd
---let t7_IBD = $MYSQLD_DATADIR/test/t7.ibd
+--enable_warnings
CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB encrypted=yes;
CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB;
@@ -50,7 +36,7 @@ delimiter ;//
commit;
set autocommit=0;
-call innodb_insert_proc(15000);
+call innodb_insert_proc(1500);
commit;
set autocommit=1;
@@ -58,37 +44,48 @@ set autocommit=1;
--let $wait_timeout= 600
--let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0
--source include/wait_condition.inc
+--let $MYSQLD_DATADIR=`select @@datadir`
---sleep 10
+--source include/shutdown_mysqld.inc
+--source include/wait_until_disconnected.inc
+
+--let SEARCH_RANGE = 10000000
--let SEARCH_PATTERN=foobar
--echo # t1 yes on expecting NOT FOUND
--- let SEARCH_FILE=$t1_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=temp
--echo # t2 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t2_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t2.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=barfoo
--echo # t3 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t3_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t3.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=repeat
--echo # t4 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t4_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t4.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=author
--echo # t5 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t5_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t5.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=mangled
--echo # t6 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t6_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t6.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=mysql
--echo # t7 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t7_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t7.ibd
-- source include/search_pattern_in_file.inc
+-- source include/start_mysqld.inc
+
+--disable_warnings
+SET GLOBAL innodb_file_format = `Barracuda`;
+SET GLOBAL innodb_file_per_table = ON;
+--enable_warnings
+
ALTER TABLE t1 ADD COLUMN b int default 2;
ALTER TABLE t2 ADD COLUMN b int default 2;
ALTER TABLE t7 ADD COLUMN b int default 2;
@@ -110,82 +107,39 @@ SHOW CREATE TABLE t5;
SHOW CREATE TABLE t6;
SHOW CREATE TABLE t7;
---sleep 10
---let SEARCH_PATTERN=foobar
---echo # t1 yes on expecting NOT FOUND
--- let SEARCH_FILE=$t1_IBD
--- source include/search_pattern_in_file.inc
---let SEARCH_PATTERN=temp
---echo # t2 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t2_IBD
--- source include/search_pattern_in_file.inc
---let SEARCH_PATTERN=barfoo
---echo # t3 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t3_IBD
--- source include/search_pattern_in_file.inc
---let SEARCH_PATTERN=repeat
---echo # t4 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t4_IBD
--- source include/search_pattern_in_file.inc
---let SEARCH_PATTERN=author
---echo # t5 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t5_IBD
--- source include/search_pattern_in_file.inc
---let SEARCH_PATTERN=mangled
---echo # t6 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t6_IBD
--- source include/search_pattern_in_file.inc
---let SEARCH_PATTERN=mysql
---echo # t7 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t7_IBD
--- source include/search_pattern_in_file.inc
-
---echo # Restarting server
--- source include/restart_mysqld.inc
---echo # Done restarting server
-
-select count(1) from t1;
-select count(1) from t2;
-select count(1) from t3;
-select count(1) from t4;
-select count(1) from t5;
-select count(1) from t6;
-select count(1) from t7;
+--source include/shutdown_mysqld.inc
+--source include/wait_until_disconnected.inc
--let SEARCH_PATTERN=foobar
--echo # t1 yes on expecting NOT FOUND
--- let SEARCH_FILE=$t1_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=temp
--echo # t2 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t2_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t2.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=barfoo
--echo # t3 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t3_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t3.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=repeat
--echo # t4 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t4_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t4.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=author
--echo # t5 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t5_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t5.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=mangled
--echo # t6 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t6_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t6.ibd
-- source include/search_pattern_in_file.inc
--let SEARCH_PATTERN=mysql
--echo # t7 ... on expecting NOT FOUND
--- let SEARCH_FILE=$t7_IBD
+-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t7.ibd
-- source include/search_pattern_in_file.inc
+-- source include/start_mysqld.inc
+
DROP PROCEDURE innodb_insert_proc;
DROP TABLE t1, t2, t3, t4, t5, t6, t7;
-
-# reset system
---disable_query_log
-EVAL SET GLOBAL innodb_file_per_table = $innodb_file_per_table_orig;
-EVAL SET GLOBAL innodb_file_format = $innodb_file_format_orig;
---enable_query_log
diff --git a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result
index 7568dc9e369..0a1f0391250 100644
--- a/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result
+++ b/mysql-test/suite/innodb/r/innodb_skip_innodb_is_tables.result
@@ -377,7 +377,7 @@ space_id page_id start_lsn end_lsn
Warnings:
Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_changed_pages but the InnoDB storage engine is not installed
select * from information_schema.innodb_tablespaces_encryption;
-SPACE NAME ENCRYPTION_SCHEME KEYSERVER_REQUESTS MIN_KEY_VERSION CURRENT_KEY_VERSION KEY_ROTATION_PAGE_NUMBER KEY_ROTATION_MAX_PAGE_NUMBER CURRENT_KEY_ID
+SPACE NAME ENCRYPTION_SCHEME KEYSERVER_REQUESTS MIN_KEY_VERSION CURRENT_KEY_VERSION KEY_ROTATION_PAGE_NUMBER KEY_ROTATION_MAX_PAGE_NUMBER CURRENT_KEY_ID ROTATING_OR_FLUSHING
Warnings:
Warning 1012 InnoDB: SELECTing from INFORMATION_SCHEMA.innodb_tablespaces_encryption but the InnoDB storage engine is not installed
select * from information_schema.innodb_tablespaces_scrubbing;
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index 863ec73790b..981780c086f 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -782,7 +782,7 @@ GLOBAL_VALUE_ORIGIN COMPILE-TIME
DEFAULT_VALUE 1
VARIABLE_SCOPE GLOBAL
VARIABLE_TYPE INT UNSIGNED
-VARIABLE_COMMENT Key rotation - re-encrypt in background all pages that were encrypted with a key that many (or more) versions behind
+VARIABLE_COMMENT Key rotation - re-encrypt in background all pages that were encrypted with a key that many (or more) versions behind. Value 0 indicates that key rotation is disabled.
NUMERIC_MIN_VALUE 0
NUMERIC_MAX_VALUE 4294967295
NUMERIC_BLOCK_SIZE 0