diff options
Diffstat (limited to 'mysql-test/suite/encryption/t')
146 files changed, 4883 insertions, 0 deletions
diff --git a/mysql-test/suite/encryption/t/aria_tiny.test b/mysql-test/suite/encryption/t/aria_tiny.test new file mode 100644 index 00000000..337720ee --- /dev/null +++ b/mysql-test/suite/encryption/t/aria_tiny.test @@ -0,0 +1,43 @@ +--source include/have_file_key_management_plugin.inc +--source include/have_sequence.inc + +SET @aria_encrypt= @@aria_encrypt_tables; +SET global aria_encrypt_tables=1; +# +# MDEV-8022 Assertion `rc == 0' failed in ma_encrypt on dropping an encrypted Aria table +# + +create table t1 (i int, key(i)) engine=aria; +insert into t1 values (1); +drop table t1; + +# +# MDEV-17913 Encrypted transactional Aria tables remain corrupt after crash +# recovery, automatic repair does not work +# +# We are using a simplifed version of the test here. This works thanks to +# the extended check table code that also checks if LSN's are reasonable. +# + +create table t1 (a int primary key, b int, c int, key(b),key(c)) engine=aria; +alter table t1 disable keys; +insert into t1 select seq,seq,seq from seq_1_to_100; +alter table t1 enable keys; +check table t1; +drop table t1; + +--echo # +--echo # MDEV CHECK on encrypted Aria table complains about "Wrong LSN" +--echo # + +CREATE TABLE t1 (f DATE PRIMARY KEY) ENGINE=Aria; +INSERT INTO t1 (f) VALUES ('1995-01-01'),('2000-01-01'); +DELETE FROM t1 WHERE f = '2000-01-01'; +OPTIMIZE TABLE t1; +CHECK TABLE t1 EXTENDED; +DROP TABLE t1; + +# +# Cleanup +# +set global aria_encrypt_tables=@aria_encrypt; diff --git a/mysql-test/suite/encryption/t/bulk_insert.opt b/mysql-test/suite/encryption/t/bulk_insert.opt new file mode 100644 index 00000000..95455f5f --- /dev/null +++ b/mysql-test/suite/encryption/t/bulk_insert.opt @@ -0,0 +1 @@ +--innodb_encrypt_log=1 diff --git a/mysql-test/suite/encryption/t/bulk_insert.test b/mysql-test/suite/encryption/t/bulk_insert.test new file mode 100644 index 00000000..ce7804cb --- /dev/null +++ b/mysql-test/suite/encryption/t/bulk_insert.test @@ -0,0 +1,11 @@ +--source include/have_innodb.inc +--source include/have_sequence.inc +--source include/have_file_key_management_plugin.inc + +SET UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0; +CREATE TABLE t1 ( + `id` int(10) unsigned NOT NULL, + UNIQUE KEY `id` (`id`) +) ENGINE=InnoDB; +INSERT INTO t1 SELECT seq FROM seq_1_to_65536; +DROP TABLE t1; diff --git a/mysql-test/suite/encryption/t/compressed_import_tablespace.opt b/mysql-test/suite/encryption/t/compressed_import_tablespace.opt new file mode 100644 index 00000000..a9893ed4 --- /dev/null +++ b/mysql-test/suite/encryption/t/compressed_import_tablespace.opt @@ -0,0 +1,3 @@ +--innodb-encrypt-tables=ON +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/compressed_import_tablespace.test b/mysql-test/suite/encryption/t/compressed_import_tablespace.test new file mode 100644 index 00000000..e79fdb17 --- /dev/null +++ b/mysql-test/suite/encryption/t/compressed_import_tablespace.test @@ -0,0 +1,45 @@ +-- source include/have_innodb.inc +-- source include/have_example_key_management_plugin.inc +-- source include/not_valgrind.inc +-- source include/not_embedded.inc + +let MYSQLD_DATADIR = `SELECT @@datadir`; +--let t1_IBD = $MYSQLD_DATADIR/test/t1.ibd +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED ENCRYPTED=YES; +INSERT INTO t1 VALUES(1, repeat('Nesamani', 10)); + +SELECT COUNT(*) FROM t1; +SHOW CREATE TABLE t1; + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND ROTATING_OR_FLUSHING <> 0 +--source include/wait_condition.inc + +let $restart_noprint=2; +--source include/restart_mysqld.inc +let MYSQLD_DATADIR =`SELECT @@datadir`; + +--list_files $MYSQLD_DATADIR/test +FLUSH TABLES t1 FOR EXPORT; +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF +--list_files $MYSQLD_DATADIR/test +UNLOCK TABLES; +DROP TABLE t1; + +CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED ENCRYPTED=YES; +ALTER TABLE t1 DISCARD TABLESPACE; +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +ALTER TABLE t1 IMPORT TABLESPACE; + +SELECT COUNT(*) FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/encryption/t/corrupted_during_recovery.combinations b/mysql-test/suite/encryption/t/corrupted_during_recovery.combinations new file mode 100644 index 00000000..9ac914f2 --- /dev/null +++ b/mysql-test/suite/encryption/t/corrupted_during_recovery.combinations @@ -0,0 +1,7 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 +--skip-innodb-doublewrite + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 +--skip-innodb-doublewrite diff --git a/mysql-test/suite/encryption/t/corrupted_during_recovery.test b/mysql-test/suite/encryption/t/corrupted_during_recovery.test new file mode 100644 index 00000000..e4a31a0b --- /dev/null +++ b/mysql-test/suite/encryption/t/corrupted_during_recovery.test @@ -0,0 +1,68 @@ +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc + +--disable_query_log +call mtr.add_suppression("InnoDB: Plugin initialization aborted"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error"); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed"); +call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed read of file '.*test.t1\\.ibd' page"); +call mtr.add_suppression("InnoDB: Failed to read page [123] from file '.*test.t1\\.ibd': Table is compressed or encrypted but uncompress or decrypt failed"); +call mtr.add_suppression("InnoDB: The page \\[page id: space=\\d+, page number=3\\] in file '.*test.t1\\.ibd' cannot be decrypted"); +call mtr.add_suppression("InnoDB: Table in tablespace \\d+ encrypted. However key management plugin or used key_version \\d+ is not found or used encryption algorithm or method does not match. Can't continue opening the table."); +call mtr.add_suppression("InnoDB: (Unable to apply log to|Discarding log for) corrupted page "); +--enable_query_log + +let INNODB_PAGE_SIZE=`select @@innodb_page_size`; +CREATE TABLE t1(a BIGINT PRIMARY KEY) ENGINE=InnoDB, ENCRYPTED=YES; +INSERT INTO t1 VALUES(1); +# Force a redo log checkpoint. +let $restart_noprint=2; +--source include/restart_mysqld.inc +--source ../../suite/innodb/include/no_checkpoint_start.inc +CREATE TABLE t2(a BIGINT PRIMARY KEY) ENGINE=InnoDB, ENCRYPTED=YES; +INSERT INTO t1 VALUES(2); +SET GLOBAL innodb_flush_log_at_trx_commit=1; +INSERT INTO t2 VALUES(2); + +--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1,t2; +--source ../../suite/innodb/include/no_checkpoint_end.inc + +--echo # Corrupt the pages + +perl; +my $ps = $ENV{INNODB_PAGE_SIZE}; + +my $file = "$ENV{MYSQLD_DATADIR}/test/t1.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +my $offset = ($ENV{INNODB_CHECKSUM_ALGORITHM} =~ /full_crc32/) ? 26 : 0; +seek (FILE, $ENV{INNODB_PAGE_SIZE} * 3 + $offset, SEEK_SET) or die "seek"; +print FILE "junk"; +close FILE or die "close"; + +$file = "$ENV{MYSQLD_DATADIR}/test/t2.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +binmode FILE; +# Corrupt pages 1 to 3. MLOG_INIT_FILE_PAGE2 should protect us! +# Unfortunately, we are not immune to page 0 corruption. +seek (FILE, $ps, SEEK_SET) or die "seek"; +print FILE chr(0xff) x ($ps * 3); +close FILE or die "close"; +EOF + +--source include/start_mysqld.inc +--error ER_UNKNOWN_STORAGE_ENGINE +SELECT * FROM t1; +--disable_query_log +call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=[1-9][0-9]*, page number=3\\] in file .*test.t[1].ibd looks corrupted; key_version="); +call mtr.add_suppression("Table .*t1.* is corrupted. Please drop the table and recreate."); +--enable_query_log +let $restart_parameters=--innodb_force_recovery=1 --skip-innodb-buffer-pool-load-at-startup; +--source include/restart_mysqld.inc + +--error ER_TABLE_CORRUPT +SELECT * FROM t1; +SELECT * FROM t2; +CHECK TABLE t2; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/encryption/t/create_or_replace.opt b/mysql-test/suite/encryption/t/create_or_replace.opt new file mode 100644 index 00000000..66892f34 --- /dev/null +++ b/mysql-test/suite/encryption/t/create_or_replace.opt @@ -0,0 +1 @@ +--innodb-encrypt-tables diff --git a/mysql-test/suite/encryption/t/create_or_replace.test b/mysql-test/suite/encryption/t/create_or_replace.test new file mode 100644 index 00000000..2ebd599d --- /dev/null +++ b/mysql-test/suite/encryption/t/create_or_replace.test @@ -0,0 +1,78 @@ +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc +--source include/count_sessions.inc + +SET @save_threads = @@GLOBAL.innodb_encryption_threads; + +SET default_storage_engine = InnoDB; + +# +# MDEV-8173: InnoDB; Failing assertion: crypt_data->type == 1 +# + +SET GLOBAL innodb_encryption_threads = 4; + +CREATE TABLE `table10_int_autoinc` (`col_int_key` int, pk int auto_increment, `col_int` int, key (`col_int_key` ),primary key (pk)) engine=innodb; +INSERT /*! IGNORE */ INTO table10_int_autoinc VALUES (NULL, NULL, -474021888) , (1, NULL, NULL) , (1141047296, NULL, NULL) , (NULL, NULL, NULL) , (NULL, NULL, 1) , (NULL, NULL, 9) , (0, NULL, 1225785344) , (NULL, NULL, 1574174720) , (2, NULL, NULL) , (6, NULL, 3); + +CREATE TABLE `table1_int_autoinc` (`col_int_key` int, pk int auto_increment, `col_int` int,key (`col_int_key` ), primary key (pk)) engine=innodb; + +CREATE TABLE `table0_int_autoinc` (`col_int_key` int, pk int auto_increment, `col_int` int, key (`col_int_key` ),primary key (pk)) engine=innodb; + +INSERT /*! IGNORE */ INTO table1_int_autoinc VALUES (4, NULL, NULL); +INSERT IGNORE INTO `table0_int_autoinc` ( `col_int_key` ) VALUES ( 1 ), ( 3 ), ( 4 ), ( 1 ); +INSERT IGNORE INTO `table1_int_autoinc` ( `col_int` ) VALUES ( 1 ), ( 0 ), ( 7 ), ( 9 ); +INSERT IGNORE INTO `table10_int_autoinc` ( `col_int` ) VALUES ( 6 ), ( 2 ), ( 3 ), ( 6 ); + +--connect (con1,localhost,root,,test) +--connect (con2,localhost,root,,test) + +--disable_query_log + +let $i = 100; +while ($i) +{ +connection con1; +send SET GLOBAL innodb_encrypt_tables = ON; +connection default; +CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table1_int_autoinc`; +connection con2; +send CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table10_int_autoinc`; +connection default; +send CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table0_int_autoinc`; +connection con1; +--reap; +send SET GLOBAL innodb_encrypt_tables = OFF; +connection con2; +--reap; +connection default; +--reap; +send CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table1_int_autoinc`; +connection con2; +send CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table10_int_autoinc`; +connection con1; +--reap; +send SET GLOBAL innodb_encrypt_tables = ON; +connection default; +--reap; +send CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table1_int_autoinc`; +connection con2; +--reap; +CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table10_int_autoinc`; +CREATE OR REPLACE TABLE `create_or_replace_t` AS SELECT * FROM `table0_int_autoinc`; +connection con1; +--reap; +connection default; +--reap; +dec $i; +} + +--enable_query_log +disconnect con1; +disconnect con2; +connection default; +drop table create_or_replace_t, table1_int_autoinc, table0_int_autoinc, +table10_int_autoinc; + +SET GLOBAL innodb_encryption_threads = @save_threads; +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/encryption/t/create_or_replace_big.opt b/mysql-test/suite/encryption/t/create_or_replace_big.opt new file mode 100644 index 00000000..7d3f2da7 --- /dev/null +++ b/mysql-test/suite/encryption/t/create_or_replace_big.opt @@ -0,0 +1 @@ +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/create_or_replace_big.test b/mysql-test/suite/encryption/t/create_or_replace_big.test new file mode 100644 index 00000000..133bdfa3 --- /dev/null +++ b/mysql-test/suite/encryption/t/create_or_replace_big.test @@ -0,0 +1,86 @@ +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc +--source include/not_embedded.inc +# This is needed for longer testcase timeout at least P7/P8 +--source include/big_test.inc + +# +# MDEV-8164: Server crashes in pfs_mutex_enter_func after fil_crypt_is_closing or alike +# +SET default_storage_engine = InnoDB; + +CREATE TABLE t1 (pk INT PRIMARY KEY, c VARCHAR(256)); +CREATE TABLE t2 AS SELECT * FROM t1; + +--disable_abort_on_error +--disable_warnings +--disable_query_log + +let $i = 40; +while ($i) +{ +SET GLOBAL innodb_encrypt_tables = ON; +SET GLOBAL innodb_encryption_threads = 1; +CREATE OR REPLACE TABLE t1 AS SELECT * FROM t2; +CREATE OR REPLACE TABLE t2 AS SELECT * FROM t1; +SET GLOBAL innodb_encryption_rotation_iops = 100; +SET GLOBAL innodb_encrypt_tables = OFF; +CREATE OR REPLACE TABLE t2 AS SELECT * FROM t1; +CREATE OR REPLACE TABLE t1 AS SELECT * FROM t2; +dec $i; +} + +--enable_abort_on_error +--enable_warnings +--enable_query_log + +drop table t1,t2; +SET GLOBAL innodb_encryption_threads = 0; + +# +# MDEV-8173: InnoDB; Failing assertion: crypt_data->type == 1 +# + +SET GLOBAL innodb_encryption_threads = 4; + +CREATE TABLE `table10_int_autoinc` (`col_int_key` int, pk int auto_increment, `col_int` int, key (`col_int_key` ),primary key (pk)) engine=innodb; +INSERT /*! IGNORE */ INTO table10_int_autoinc VALUES (NULL, NULL, -474021888) , (1, NULL, NULL) , (1141047296, NULL, NULL) , (NULL, NULL, NULL) , (NULL, NULL, 1) , (NULL, NULL, 9) , (0, NULL, 1225785344) , (NULL, NULL, 1574174720) , (2, NULL, NULL) , (6, NULL, 3); + +CREATE TABLE `table1_int_autoinc` (`col_int_key` int, pk int auto_increment, `col_int` int,key (`col_int_key` ), primary key (pk)) engine=innodb; + +CREATE TABLE `table0_int_autoinc` (`col_int_key` int, pk int auto_increment, `col_int` int, key (`col_int_key` ),primary key (pk)) engine=innodb; + +INSERT /*! IGNORE */ INTO table1_int_autoinc VALUES (4, NULL, NULL); +INSERT IGNORE INTO `table0_int_autoinc` ( `col_int_key` ) VALUES ( 1 ), ( 3 ), ( 4 ), ( 1 ); +INSERT IGNORE INTO `table1_int_autoinc` ( `col_int` ) VALUES ( 1 ), ( 0 ), ( 7 ), ( 9 ); +INSERT IGNORE INTO `table10_int_autoinc` ( `col_int` ) VALUES ( 6 ), ( 2 ), ( 3 ), ( 6 ); + +--echo # Wait max 10 min for key encryption threads to decrypt all spaces +let $cnt=600; +while ($cnt) +{ + let $success=`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0`; + if ($success) + { + let $cnt=0; + } + if (!$success) + { + real_sleep 1; + dec $cnt; + } +} +if (!$success) +{ + SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION; + SHOW STATUS LIKE 'innodb_encryption%'; + -- die Timeout waiting for encryption threads +} +--echo # Success! + +SET GLOBAL innodb_encryption_threads = 0; +SET GLOBAL innodb_encrypt_tables = OFF; + +DROP TABLE table0_int_autoinc, table1_int_autoinc, table10_int_autoinc; + +-- source include/restart_mysqld.inc diff --git a/mysql-test/suite/encryption/t/debug_key_management.opt b/mysql-test/suite/encryption/t/debug_key_management.opt new file mode 100644 index 00000000..5c283bbe --- /dev/null +++ b/mysql-test/suite/encryption/t/debug_key_management.opt @@ -0,0 +1,5 @@ +--innodb-encrypt-log=ON +--innodb-encryption-rotate-key-age=2 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption +--plugin-load-add=$DEBUG_KEY_MANAGEMENT_SO diff --git a/mysql-test/suite/encryption/t/debug_key_management.test b/mysql-test/suite/encryption/t/debug_key_management.test new file mode 100644 index 00000000..45a93040 --- /dev/null +++ b/mysql-test/suite/encryption/t/debug_key_management.test @@ -0,0 +1,42 @@ +-- source include/have_innodb.inc +-- source include/have_debug.inc +-- source include/innodb_undo_tablespaces.inc +-- source include/not_embedded.inc + +if (`select count(*) = 0 from information_schema.plugins + where plugin_name = 'debug_key_management' and plugin_status='active'`) +{ + --skip Needs debug_key_management +} + +create table t1(a serial) engine=innoDB; + +set global innodb_encrypt_tables=ON; +show variables like 'innodb_encrypt%'; + +--let $tables_count= `select count(*) + 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +let $wait_condition= select count(*) = $tables_count from information_schema.innodb_tablespaces_encryption where current_key_version=1; +--source include/wait_condition.inc + +select count(*) from information_schema.innodb_tablespaces_encryption where current_key_version <> 1; +set global debug_key_management_version=10; + +let $wait_condition= select count(*) = $tables_count from information_schema.innodb_tablespaces_encryption where current_key_version=10; +--source include/wait_condition.inc +select count(*) from information_schema.innodb_tablespaces_encryption where current_key_version <> 10; + +# Test redo log key rotation and crash recovery. +SET GLOBAL innodb_log_checkpoint_now = 1; +SET GLOBAL innodb_flush_log_at_trx_commit = 1; +INSERT INTO t1 VALUES(NULL); +let $shutdown_timeout = 0; +-- source include/restart_mysqld.inc + +# Note that we expect that key_version is increasing so disable encryption before reset + +set global innodb_encrypt_tables=OFF; +set global debug_key_management_version=1; + +select * from t1; + +drop table t1; diff --git a/mysql-test/suite/encryption/t/encrypt_and_grep.opt b/mysql-test/suite/encryption/t/encrypt_and_grep.opt new file mode 100644 index 00000000..c50ec719 --- /dev/null +++ b/mysql-test/suite/encryption/t/encrypt_and_grep.opt @@ -0,0 +1,7 @@ +--innodb-encrypt-tables=ON +--innodb-encrypt-log=ON +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption + + diff --git a/mysql-test/suite/encryption/t/encrypt_and_grep.test b/mysql-test/suite/encryption/t/encrypt_and_grep.test new file mode 100644 index 00000000..687f14e8 --- /dev/null +++ b/mysql-test/suite/encryption/t/encrypt_and_grep.test @@ -0,0 +1,128 @@ +-- source include/have_innodb.inc +-- source include/innodb_undo_tablespaces.inc +-- source include/have_file_key_management_plugin.inc + +# +# MDEV-8138: strange results from encrypt-and-grep test +# +--let $MYSQLD_DATADIR=`select @@datadir` +--let ib1_IBD = $MYSQLD_DATADIR/ibdata1 +--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 SEARCH_RANGE = 10000000 + +SET GLOBAL innodb_file_per_table = ON; + +create table t1 (a varchar(255)) engine=innodb encrypted=yes; +create table t2 (a varchar(255)) engine=innodb; +show warnings; +create table t3 (a varchar(255)) engine=innodb encrypted=no; + +insert t1 values (repeat('foobarsecret', 12)); +insert t2 values (repeat('tempsecret', 12)); +insert t3 values (repeat('dummysecret', 12)); + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--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 + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +FLUSH TABLES t1,t2,t3 FOR EXPORT; + +--let SEARCH_PATTERN=foobarsecret +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=tempsecret +--echo # t2 ... on expecting NOT FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=dummysecret +--echo # t3 no on expecting FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=foobarsecret +--echo # ibdata1 expecting NOT FOUND +-- let SEARCH_FILE=$ib1_IBD +-- source include/search_pattern_in_file.inc + +UNLOCK TABLES; + +--echo # Now turn off encryption and wait for threads to decrypt everything + +SET GLOBAL innodb_encrypt_tables = off; + +--echo # Wait max 10 min for key encryption threads to decrypt all spaces +--let $wait_timeout= 600 +--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` + +--let $wait_condition=SELECT COUNT(*) = $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND CURRENT_KEY_VERSION = 0; +--source include/wait_condition.inc + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +FLUSH TABLES t1,t2,t3 FOR EXPORT; + +--let SEARCH_PATTERN=foobarsecret +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=tempsecret +--echo # t2 ... default expecting FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=dummysecret +--echo # t3 no on expecting FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=foobarsecret +--echo # ibdata1 expecting NOT FOUND +-- let SEARCH_FILE=$ib1_IBD +-- source include/search_pattern_in_file.inc + +UNLOCK TABLES; + +--echo # Now turn on encryption and wait for threads to encrypt all spaces +SET GLOBAL innodb_encrypt_tables = on; + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--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 + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +FLUSH TABLES t1,t2,t3 FOR EXPORT; + +--let SEARCH_PATTERN=foobarsecret +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=tempsecret +--echo # t2 ... on expecting NOT FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=dummysecret +--echo # t3 no on expecting FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=foobarsecret +--echo # ibdata1 expecting NOT FOUND +-- let SEARCH_FILE=$ib1_IBD +-- source include/search_pattern_in_file.inc + +UNLOCK TABLES; + +drop table t1, t2, t3; diff --git a/mysql-test/suite/encryption/t/encryption_force.opt b/mysql-test/suite/encryption/t/encryption_force.opt new file mode 100644 index 00000000..c9e53287 --- /dev/null +++ b/mysql-test/suite/encryption/t/encryption_force.opt @@ -0,0 +1,2 @@ +--innodb-encrypt-tables=FORCE +--innodb-encrypt-log=ON diff --git a/mysql-test/suite/encryption/t/encryption_force.test b/mysql-test/suite/encryption/t/encryption_force.test new file mode 100644 index 00000000..3c6f0391 --- /dev/null +++ b/mysql-test/suite/encryption/t/encryption_force.test @@ -0,0 +1,39 @@ +-- source include/have_innodb.inc +-- source include/have_partition.inc +-- source include/have_example_key_management_plugin.inc + +select @@innodb_encrypt_tables; + +create table t1 (a int) engine=innodb encrypted=yes; +create table t2 (a int) engine=innodb encrypted=default; +--error ER_CANT_CREATE_TABLE +create table t3 (a int) engine=innodb encrypted=no; + +create table t4 (a int) engine=innodb encrypted=yes partition by hash(a) partitions 2; +--error ER_CANT_CREATE_TABLE +create table t5 (a int) engine=innodb encrypted=no partition by hash(a) partitions 2; + +set global innodb_encrypt_tables='ON'; +create table t3 (a int) engine=innodb encrypted=no; +set global innodb_encrypt_tables='FORCE'; + +show create table t1; +show create table t2; +show create table t3; +show create table t4; + +--error ER_ILLEGAL_HA_CREATE_OPTION +alter table t1 encrypted=no; +alter table t2 encrypted=yes; +alter table t3 encrypted=default; +--error ER_ILLEGAL_HA_CREATE_OPTION +alter table t4 encrypted=no; + +show create table t1; +show create table t2; +show create table t3; + +drop table t1; +drop table t2; +drop table t3; +drop table t4; diff --git a/mysql-test/suite/encryption/t/encryption_key_corruption.combinations b/mysql-test/suite/encryption/t/encryption_key_corruption.combinations new file mode 100644 index 00000000..979b2acd --- /dev/null +++ b/mysql-test/suite/encryption/t/encryption_key_corruption.combinations @@ -0,0 +1,4 @@ +[crc32] +innodb-checksum-algorithm=crc32 +[full_crc32] +innodb-checksum-algorithm=full_crc32 diff --git a/mysql-test/suite/encryption/t/encryption_key_corruption.opt b/mysql-test/suite/encryption/t/encryption_key_corruption.opt new file mode 100644 index 00000000..682d06d5 --- /dev/null +++ b/mysql-test/suite/encryption/t/encryption_key_corruption.opt @@ -0,0 +1 @@ +--innodb-encrypt-tables=1 diff --git a/mysql-test/suite/encryption/t/encryption_key_corruption.test b/mysql-test/suite/encryption/t/encryption_key_corruption.test new file mode 100644 index 00000000..9b4bd172 --- /dev/null +++ b/mysql-test/suite/encryption/t/encryption_key_corruption.test @@ -0,0 +1,44 @@ +--source include/have_innodb.inc +--source include/have_example_key_management_plugin.inc + +call mtr.add_suppression("InnoDB: .*: Page 0 at offset 0 looks corrupted"); +call mtr.add_suppression("Index for table 'dst' is corrupt; try to repair it"); +call mtr.add_suppression("Page for tablespace .* is index page with id .* but that index is not found from configuration file"); + +let MYSQLD_DATADIR = `SELECT @@datadir`; + + +CREATE TABLE src (pk INT PRIMARY KEY, value INT) ENGINE=INNODB; +INSERT INTO src VALUES (1, 1), (2, 2), (3, 3); + +FLUSH TABLES src FOR EXPORT; + +--copy_file $MYSQLD_DATADIR/test/src.ibd $MYSQLD_DATADIR/test/tmp.ibd +--copy_file $MYSQLD_DATADIR/test/src.cfg $MYSQLD_DATADIR/test/tmp.cfg + +perl; +use strict; +die unless open(FILE, "+<$ENV{MYSQLD_DATADIR}/test/tmp.ibd"); +binmode FILE; +die unless seek(FILE, 3 * 16384 + 26, 0); +print FILE pack("N", 0x00000000); +close(FILE); +EOF + +UNLOCK TABLES; + +DROP TABLE src; + +CREATE TABLE dst (pk INT PRIMARY KEY, value INT) ENGINE=INNODB; +ALTER TABLE dst DISCARD TABLESPACE; + +--copy_file $MYSQLD_DATADIR/test/tmp.ibd $MYSQLD_DATADIR/test/dst.ibd +--copy_file $MYSQLD_DATADIR/test/tmp.cfg $MYSQLD_DATADIR/test/dst.cfg + +--error ER_NOT_KEYFILE +ALTER TABLE dst IMPORT TABLESPACE; + +DROP TABLE dst; + +--remove_file $MYSQLD_DATADIR/test/tmp.ibd +--remove_file $MYSQLD_DATADIR/test/tmp.cfg diff --git a/mysql-test/suite/encryption/t/file_creation.opt b/mysql-test/suite/encryption/t/file_creation.opt new file mode 100644 index 00000000..7d3f2da7 --- /dev/null +++ b/mysql-test/suite/encryption/t/file_creation.opt @@ -0,0 +1 @@ +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/file_creation.test b/mysql-test/suite/encryption/t/file_creation.test new file mode 100644 index 00000000..6b012683 --- /dev/null +++ b/mysql-test/suite/encryption/t/file_creation.test @@ -0,0 +1,41 @@ +--source include/have_innodb.inc +--source include/have_example_key_management_plugin.inc +let $restart_noprint=2; +# embedded does not support restart +-- source include/not_embedded.inc + +# +# MDEV-19348 MariaBackup prepare fails with InnoDB: Database page corruption +# on disk or a failed file read +# + +SET GLOBAL innodb_encrypt_tables = ON; +SET GLOBAL innodb_encryption_threads = 1; +SET GLOBAL innodb_max_dirty_pages_pct = 99; +SHOW VARIABLES LIKE 'innodb_encrypt%'; + +CREATE TABLE t1(f1 INT NOT NULL, f2 CHAR(255), f3 CHAR(255), + f4 CHAR(255), f5 CHAR(255))ENGINE=INNODB; + +INSERT INTO t1 VALUES(1, "mysql", "mariadb", "batman", "superman"); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; + +--let $tables_count= `select count(*) + 1 from information_schema.tables where engine = 'InnoDB'` + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +OPTIMIZE TABLE t1; + +--source ../../suite/innodb/include/no_checkpoint_start.inc +ALTER TABLE t1 FORCE; +--let CLEANUP_IF_CHECKPOINT=DROP TABLE t1; +--source ../../suite/innodb/include/no_checkpoint_end.inc + +--source include/start_mysqld.inc +DROP TABLE t1; diff --git a/mysql-test/suite/encryption/t/filekeys-data-too-long.key b/mysql-test/suite/encryption/t/filekeys-data-too-long.key new file mode 100644 index 00000000..ba1624fb --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys-data-too-long.key @@ -0,0 +1,4 @@ +secretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecret +secretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecret +secretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecretsecret + diff --git a/mysql-test/suite/encryption/t/filekeys-data.enc b/mysql-test/suite/encryption/t/filekeys-data.enc Binary files differnew file mode 100644 index 00000000..a8adb2f9 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys-data.enc diff --git a/mysql-test/suite/encryption/t/filekeys-data.key b/mysql-test/suite/encryption/t/filekeys-data.key new file mode 100644 index 00000000..85fcd1fb --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys-data.key @@ -0,0 +1,2 @@ +secret + diff --git a/mysql-test/suite/encryption/t/filekeys-tooshort.enc b/mysql-test/suite/encryption/t/filekeys-tooshort.enc new file mode 100644 index 00000000..9849236c --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys-tooshort.enc @@ -0,0 +1 @@ +Salted__
\ No newline at end of file diff --git a/mysql-test/suite/encryption/t/filekeys_badtest.inc b/mysql-test/suite/encryption/t/filekeys_badtest.inc new file mode 100644 index 00000000..60ac9f0e --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_badtest.inc @@ -0,0 +1,17 @@ +-- source include/not_embedded.inc +-- source include/have_innodb.inc +-- source filekeys_plugin.inc + +--eval call mtr.add_suppression("$SEARCH_PATTERN") +call mtr.add_suppression("Plugin 'file_key_management' init function returned error"); +call mtr.add_suppression("Plugin 'file_key_management' registration.*failed"); + +--let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err +--source include/search_pattern_in_file.inc + +--error ER_CANT_CREATE_TABLE +create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; + +select plugin_status from information_schema.plugins + where plugin_name = 'file_key_management'; + diff --git a/mysql-test/suite/encryption/t/filekeys_emptyfile.opt b/mysql-test/suite/encryption/t/filekeys_emptyfile.opt new file mode 100644 index 00000000..7c5f6d05 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_emptyfile.opt @@ -0,0 +1 @@ +--loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/empty_file diff --git a/mysql-test/suite/encryption/t/filekeys_emptyfile.test b/mysql-test/suite/encryption/t/filekeys_emptyfile.test new file mode 100644 index 00000000..39f2ccf2 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_emptyfile.test @@ -0,0 +1,4 @@ +let SEARCH_PATTERN=System key id 1 is missing at; +source filekeys_badtest.inc; + +--echo # Test checks if opening an empty filekeys does not crash the server. diff --git a/mysql-test/suite/encryption/t/filekeys_encfile.opt b/mysql-test/suite/encryption/t/filekeys_encfile.opt new file mode 100644 index 00000000..651cc0f4 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile.opt @@ -0,0 +1,2 @@ +--loose-file-key-management-filekey=secret +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-data.enc diff --git a/mysql-test/suite/encryption/t/filekeys_encfile.test b/mysql-test/suite/encryption/t/filekeys_encfile.test new file mode 100644 index 00000000..a0611a38 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile.test @@ -0,0 +1 @@ +source filekeys_goodtest.inc; diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_bad.opt b/mysql-test/suite/encryption/t/filekeys_encfile_bad.opt new file mode 100644 index 00000000..6ece8c6b --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_bad.opt @@ -0,0 +1,2 @@ +--loose-file-key-management-filekey=bad +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-data.enc diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_bad.test b/mysql-test/suite/encryption/t/filekeys_encfile_bad.test new file mode 100644 index 00000000..51ab14c5 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_bad.test @@ -0,0 +1,2 @@ +let SEARCH_PATTERN=Cannot decrypt .*filekeys-data.enc. Wrong key; +source filekeys_badtest.inc; diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_badfile.opt b/mysql-test/suite/encryption/t/filekeys_encfile_badfile.opt new file mode 100644 index 00000000..374a4139 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_badfile.opt @@ -0,0 +1,2 @@ +--loose-file-key-management-filekey=FILE:bad +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-data.enc diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_badfile.test b/mysql-test/suite/encryption/t/filekeys_encfile_badfile.test new file mode 100644 index 00000000..e0b07ac6 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_badfile.test @@ -0,0 +1,2 @@ +let SEARCH_PATTERN=File 'bad' not found; +source filekeys_badtest.inc; diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_file.opt b/mysql-test/suite/encryption/t/filekeys_encfile_file.opt new file mode 100644 index 00000000..df2d3d52 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_file.opt @@ -0,0 +1,2 @@ +--loose-file-key-management-filekey=FILE:$MTR_SUITE_DIR/t/filekeys-data.key +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-data.enc diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_file.test b/mysql-test/suite/encryption/t/filekeys_encfile_file.test new file mode 100644 index 00000000..a0611a38 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_file.test @@ -0,0 +1 @@ +source filekeys_goodtest.inc; diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_no.opt b/mysql-test/suite/encryption/t/filekeys_encfile_no.opt new file mode 100644 index 00000000..68264e5c --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_no.opt @@ -0,0 +1 @@ +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-data.enc diff --git a/mysql-test/suite/encryption/t/filekeys_encfile_no.test b/mysql-test/suite/encryption/t/filekeys_encfile_no.test new file mode 100644 index 00000000..51ab14c5 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_encfile_no.test @@ -0,0 +1,2 @@ +let SEARCH_PATTERN=Cannot decrypt .*filekeys-data.enc. Wrong key; +source filekeys_badtest.inc; diff --git a/mysql-test/suite/encryption/t/filekeys_goodtest.inc b/mysql-test/suite/encryption/t/filekeys_goodtest.inc new file mode 100644 index 00000000..25206079 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_goodtest.inc @@ -0,0 +1,18 @@ +-- source include/have_innodb.inc +-- source filekeys_plugin.inc + +create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; +show create table t1; +insert t1 values (12345, repeat('1234567890', 20)); + +alter table t1 encryption_key_id=2; +show create table t1; +--error ER_ILLEGAL_HA_CREATE_OPTION +alter table t1 encryption_key_id=3; +show create table t1; +alter table t1 encryption_key_id=33; +show create table t1; +alter table t1 encryption_key_id=4; +show create table t1; + +drop table t1; diff --git a/mysql-test/suite/encryption/t/filekeys_nofile.test b/mysql-test/suite/encryption/t/filekeys_nofile.test new file mode 100644 index 00000000..46f5cd46 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_nofile.test @@ -0,0 +1,2 @@ +let SEARCH_PATTERN=file-key-management-filename is not set; +source filekeys_badtest.inc; diff --git a/mysql-test/suite/encryption/t/filekeys_plugin.inc b/mysql-test/suite/encryption/t/filekeys_plugin.inc new file mode 100644 index 00000000..a9bde3b0 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_plugin.inc @@ -0,0 +1,4 @@ +if (!$FILE_KEY_MANAGEMENT_SO) +{ + --skip Needs dynamic file_key_management plugin +} diff --git a/mysql-test/suite/encryption/t/filekeys_plugin.opt b/mysql-test/suite/encryption/t/filekeys_plugin.opt new file mode 100644 index 00000000..b42e6266 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_plugin.opt @@ -0,0 +1 @@ +--plugin-load-add=$FILE_KEY_MANAGEMENT_SO diff --git a/mysql-test/suite/encryption/t/filekeys_plugin_exists.inc b/mysql-test/suite/encryption/t/filekeys_plugin_exists.inc new file mode 100644 index 00000000..a9bde3b0 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_plugin_exists.inc @@ -0,0 +1,4 @@ +if (!$FILE_KEY_MANAGEMENT_SO) +{ + --skip Needs dynamic file_key_management plugin +} diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.enc b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.enc new file mode 100644 index 00000000..3257ff7d --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.enc @@ -0,0 +1,4 @@ +Salted__åÒ4¶À0-6„LÊÆÀ ìsK?p\õa’m8ž¸N?q œn Š<ø¹*g ¯•( •Å|F‰Š±ø/õɳ! +öœ kok6ÄðŸÙy7t67ôD#¢gæ´„¤Ê—«¤Ô£ãþiyu ²*iÅ#•Æˆ82#6à ¶›.C8ÛÏ;7þBÔ£¥ˆ‚ +0À / +üÀw¤Ú0w"xÔ±Qu04ÿðxËkj®{ÅÛÃW΢¹Ìå3CÀ5Õœ¦ ¼Âᔪ÷·¯ÑåPù$=«Ò²
\ No newline at end of file diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.key b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.key new file mode 100644 index 00000000..bba639ae --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.key @@ -0,0 +1 @@ +c9518399cbec2b5edf773e06d1b934b90ec0f46ae455b8f1e001b5629ef31a513b83e676bf654c08ba98659461410e5e040e46237a7d50b40bd9bb90576f841275506e61523e5e9a0beb7641127ed2d946395b6fee7ff5263a9019cbe71bd907bf1ac6365940fa391086830a4e6c1d2972b99505467ef31cfb46d0cb7ab8f4f1 diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.opt b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.opt new file mode 100644 index 00000000..9dee47bb --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.opt @@ -0,0 +1,3 @@ +--loose-file-key-management-filekey=FILE:$MTR_SUITE_DIR/t/filekeys_secret_openssl_rand_128bits.key +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys_secret_openssl_rand_128bits.enc + diff --git a/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.test b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.test new file mode 100644 index 00000000..60718d21 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_openssl_rand_128bits.test @@ -0,0 +1,13 @@ +-- source include/have_innodb.inc +-- source filekeys_plugin.inc + +create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; +show create table t1; +insert t1 values (12345, repeat('1234567890', 20)); + +alter table t1 encryption_key_id=2; +show create table t1; + +drop table t1; + +--echo # Test checks if opening an too large secret does not crash the server. diff --git a/mysql-test/suite/encryption/t/filekeys_secret_too_long.opt b/mysql-test/suite/encryption/t/filekeys_secret_too_long.opt new file mode 100644 index 00000000..c3f95019 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_too_long.opt @@ -0,0 +1,3 @@ +--loose-file-key-management-filekey=FILE:$MTR_SUITE_DIR/t/filekeys-data-too-long.key +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-data.enc + diff --git a/mysql-test/suite/encryption/t/filekeys_secret_too_long.test b/mysql-test/suite/encryption/t/filekeys_secret_too_long.test new file mode 100644 index 00000000..0032e94d --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_secret_too_long.test @@ -0,0 +1,4 @@ +let SEARCH_PATTERN=the filekey is too long; +source filekeys_badtest.inc; + +--echo # Test checks if opening an too large secret does not crash the server. diff --git a/mysql-test/suite/encryption/t/filekeys_syntax.opt b/mysql-test/suite/encryption/t/filekeys_syntax.opt new file mode 100644 index 00000000..4be6601b --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_syntax.opt @@ -0,0 +1 @@ +--loose-file-key-management-filename=$MYSQL_TMP_DIR/keys.txt diff --git a/mysql-test/suite/encryption/t/filekeys_syntax.test b/mysql-test/suite/encryption/t/filekeys_syntax.test new file mode 100644 index 00000000..61db6ad7 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_syntax.test @@ -0,0 +1,109 @@ +# +# first test - missing key file +# +let SEARCH_PATTERN=File '.*keys.txt' not found; +source filekeys_badtest.inc; + +# +# key id= 0 +# +write_file $MYSQL_TMP_DIR/keys.txt; +1;11111111111111111111111111111111 +0;00000000000000000000000000000000 +2;22222222222222222222222222222222 +EOF +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error 2 +install soname 'file_key_management'; +source filekeys_badtest.inc; +let SEARCH_PATTERN=Invalid key id; +source filekeys_badtest.inc; + +# +# id too big +# +remove_file $MYSQL_TMP_DIR/keys.txt; +write_file $MYSQL_TMP_DIR/keys.txt; +1;11111111111111111111111111111111 +4294967299;00000000000000000000000000000000 +2;22222222222222222222222222222222 +EOF +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error 2 +install soname 'file_key_management'; +source filekeys_badtest.inc; +let SEARCH_PATTERN=Invalid key id; +source filekeys_badtest.inc; +# +# wrong key length (not 16, 24, 23 bytes) +# +remove_file $MYSQL_TMP_DIR/keys.txt; +write_file $MYSQL_TMP_DIR/keys.txt; +1;11111111111111111111111111111111 +3;00000000000000000000000000000000111122223333 +2;22222222222222222222222222222222 +EOF +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error 2 +install soname 'file_key_management'; +source filekeys_badtest.inc; +let SEARCH_PATTERN=Invalid key; +source filekeys_badtest.inc; +# +# wrong key length (not an even number of digits) +# +remove_file $MYSQL_TMP_DIR/keys.txt; +write_file $MYSQL_TMP_DIR/keys.txt; +1;11111111111111111111111111111111 +3;0000000000000000000000000000000 +2;22222222222222222222222222222222 +EOF +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error 2 +install soname 'file_key_management'; +source filekeys_badtest.inc; +let SEARCH_PATTERN=Invalid key; +source filekeys_badtest.inc; +# +# no semicolon +# +remove_file $MYSQL_TMP_DIR/keys.txt; +write_file $MYSQL_TMP_DIR/keys.txt; +1;11111111111111111111111111111111 +3:0000000000000000000000000000000 +2;22222222222222222222222222222222 +EOF +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error 2 +install soname 'file_key_management'; +source filekeys_badtest.inc; +let SEARCH_PATTERN=Syntax error; +source filekeys_badtest.inc; +# +# another syntax error +# +remove_file $MYSQL_TMP_DIR/keys.txt; +write_file $MYSQL_TMP_DIR/keys.txt; +1;11111111111111111111111111111111 +syntax error +2;22222222222222222222222222222222 +EOF +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error 2 +install soname 'file_key_management'; +source filekeys_badtest.inc; +let SEARCH_PATTERN=Syntax error; +source filekeys_badtest.inc; +# +# no key id 1 +# +remove_file $MYSQL_TMP_DIR/keys.txt; +write_file $MYSQL_TMP_DIR/keys.txt; +3;22222222222222222222222222222222 +EOF +--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR +--error 2 +install soname 'file_key_management'; +source filekeys_badtest.inc; +let SEARCH_PATTERN=System key id 1; +source filekeys_badtest.inc; diff --git a/mysql-test/suite/encryption/t/filekeys_tooshort.opt b/mysql-test/suite/encryption/t/filekeys_tooshort.opt new file mode 100644 index 00000000..8999becc --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_tooshort.opt @@ -0,0 +1,3 @@ +--loose-file-key-management-filekey=secret +--loose-file-key-management-filename=$MTR_SUITE_DIR/t/filekeys-tooshort.enc + diff --git a/mysql-test/suite/encryption/t/filekeys_tooshort.test b/mysql-test/suite/encryption/t/filekeys_tooshort.test new file mode 100644 index 00000000..b0e89675 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_tooshort.test @@ -0,0 +1,4 @@ +let SEARCH_PATTERN=Cannot decrypt .*tooshort.enc. Not encrypted; +source filekeys_badtest.inc; + +--echo # Test checks if opening an too short filekeys does not crash the server. diff --git a/mysql-test/suite/encryption/t/filekeys_unencfile.opt b/mysql-test/suite/encryption/t/filekeys_unencfile.opt new file mode 100644 index 00000000..b7e207c0 --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_unencfile.opt @@ -0,0 +1,2 @@ +--loose-file-key-management-filekey=bad +--loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys.txt diff --git a/mysql-test/suite/encryption/t/filekeys_unencfile.test b/mysql-test/suite/encryption/t/filekeys_unencfile.test new file mode 100644 index 00000000..2567c29f --- /dev/null +++ b/mysql-test/suite/encryption/t/filekeys_unencfile.test @@ -0,0 +1,2 @@ +let SEARCH_PATTERN=Cannot decrypt .*keys.txt. Not encrypted; +source filekeys_badtest.inc; diff --git a/mysql-test/suite/encryption/t/import_4k.opt b/mysql-test/suite/encryption/t/import_4k.opt new file mode 100644 index 00000000..e5b58602 --- /dev/null +++ b/mysql-test/suite/encryption/t/import_4k.opt @@ -0,0 +1 @@ +--innodb-page-size=4k diff --git a/mysql-test/suite/encryption/t/import_4k.test b/mysql-test/suite/encryption/t/import_4k.test new file mode 100644 index 00000000..aef7c702 --- /dev/null +++ b/mysql-test/suite/encryption/t/import_4k.test @@ -0,0 +1,20 @@ +--source include/have_innodb.inc +--source include/have_sequence.inc +--source include/have_example_key_management_plugin.inc +--source include/have_debug.inc + +set @save_limit = @@GLOBAL.innodb_limit_optimistic_insert_debug; +set global innodb_limit_optimistic_insert_debug=3; +create table t1 (a INT PRIMARY KEY) engine=InnoDB ENCRYPTED=YES; +insert into t1 select * from seq_1_to_6000; +flush table t1 for export; +--let $datadir= `select @@datadir` +--copy_file $datadir/test/t1.ibd $datadir/t1.ibd +--copy_file $datadir/test/t1.cfg $datadir/t1.cfg +unlock tables; +alter table t1 discard tablespace; +--move_file $datadir/t1.ibd $datadir/test/t1.ibd +--move_file $datadir/t1.cfg $datadir/test/t1.cfg +alter table t1 import tablespace; +set global innodb_limit_optimistic_insert_debug=@save_limit; +drop table t1; diff --git a/mysql-test/suite/encryption/t/innochecksum.test b/mysql-test/suite/encryption/t/innochecksum.test new file mode 100644 index 00000000..516bc073 --- /dev/null +++ b/mysql-test/suite/encryption/t/innochecksum.test @@ -0,0 +1,303 @@ +# +# MDEV-8773: InnoDB innochecksum does not work with encrypted or page compressed tables +# + +# Don't test under embedded as we restart server +-- source include/not_embedded.inc +# Require InnoDB +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +-- source include/innodb_page_size_small.inc +-- source include/innodb_checksum_algorithm.inc +-- source include/maybe_debug.inc + +if (!$INNOCHECKSUM) { + --echo Need innochecksum binary + --die Need innochecksum binary +} + +--disable_query_log +# This may be triggered on a slow system or one that lacks native AIO. +call mtr.add_suppression("InnoDB: Trying to delete tablespace.*pending operations"); +if ($have_debug) { +SET GLOBAL DEBUG_DBUG='+d,ib_log_checkpoint_avoid_hard'; +call mtr.add_suppression("InnoDB: Crash recovery is broken due to insufficient innodb_log_file_size"); +} +--enable_query_log + +let $checksum_algorithm = `SELECT @@innodb_checksum_algorithm`; +SET GLOBAL innodb_file_per_table = ON; +# zlib +set global innodb_compression_algorithm = 1; + + +--echo # Create and populate a tables +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED ENCRYPTED=NO; +CREATE TABLE t4 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB PAGE_COMPRESSED=1; +CREATE TABLE t5 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +CREATE TABLE t6 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT) ENGINE=InnoDB; + +--disable_query_log +--let $i = 1000 +begin; +while ($i) +{ + INSERT INTO t1 (b) VALUES (REPEAT('abcdefghijklmnopqrstuvwxyz', 100)); + dec $i; +} +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t3 SELECT * FROM t1; +INSERT INTO t4 SELECT * FROM t1; +INSERT INTO t5 SELECT * FROM t1; +INSERT INTO t6 SELECT * FROM t1; +commit; +--enable_query_log + +let $MYSQLD_DATADIR=`select @@datadir`; +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 INNODB_PAGE_SIZE=`select @@innodb_page_size`; +let MYSQLD_DATADIR=`select @@datadir`; + +--source include/shutdown_mysqld.inc + +-- disable_result_log +--echo # Run innochecksum on t1, check -S does not cause crash for encrypted file +--exec $INNOCHECKSUM -S $t1_IBD + +--echo # Run innochecksum on t2 + +--exec $INNOCHECKSUM $t2_IBD + +--echo # Run innochecksum on t3 + +--exec $INNOCHECKSUM $t3_IBD + +--echo # Run innochecksum on t4 + +--exec $INNOCHECKSUM $t4_IBD + +--echo # Run innochecksum on t4 + +--exec $INNOCHECKSUM $t4_IBD + +--echo # Run innochecksum on t5 + +--exec $INNOCHECKSUM $t5_IBD + +--echo # Run innochecksum on t6 + +--exec $INNOCHECKSUM $t6_IBD + +--enable_result_log + +--echo # Backup tables before corrupting +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t1.ibd.backup +--copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t2.ibd.backup +--copy_file $MYSQLD_DATADIR/test/t3.ibd $MYSQLD_DATADIR/test/t3.ibd.backup +--copy_file $MYSQLD_DATADIR/test/t4.ibd $MYSQLD_DATADIR/test/t4.ibd.backup +--copy_file $MYSQLD_DATADIR/test/t5.ibd $MYSQLD_DATADIR/test/t5.ibd.backup +--copy_file $MYSQLD_DATADIR/test/t6.ibd $MYSQLD_DATADIR/test/t6.ibd.backup + +# +# MDEV-11939: innochecksum mistakes a file for an encrypted one +# + +--echo # Corrupt FIL_PAGE_FILE_FLUSH_LSN_OR_KEY_VERSION + +perl; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t1.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 26, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t2.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 26, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t3.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 26, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t6.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 26, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +EOF + +-- disable_result_log +--error 1 +--exec $INNOCHECKSUM $t1_IBD + +--echo # Run innochecksum on t2 + +--error 1 +--exec $INNOCHECKSUM $t2_IBD + +--echo # Run innochecksum on t3 +--echo # no encryption corrupting the field should not have effect +--exec $INNOCHECKSUM $t3_IBD + +--echo # Run innochecksum on t6 +--echo # In new checksum format, checksum calculated for whole page. +--echo # So It should affected. +let $error_code = 0; +if ($checksum_algorithm == "full_crc32") +{ + let $error_code = 1; +} + +if ($checksum_algorithm == "strict_full_crc32") +{ + let $error_code = 1; +} + +--error $error_code +--exec $INNOCHECKSUM $t6_IBD + +--enable_result_log + +--echo # Restore the original tables +--remove_file $MYSQLD_DATADIR/test/t1.ibd +--remove_file $MYSQLD_DATADIR/test/t2.ibd +--remove_file $MYSQLD_DATADIR/test/t3.ibd +--remove_file $MYSQLD_DATADIR/test/t4.ibd +--remove_file $MYSQLD_DATADIR/test/t5.ibd +--remove_file $MYSQLD_DATADIR/test/t6.ibd +--copy_file $MYSQLD_DATADIR/test/t1.ibd.backup $MYSQLD_DATADIR/test/t1.ibd +--copy_file $MYSQLD_DATADIR/test/t2.ibd.backup $MYSQLD_DATADIR/test/t2.ibd +--copy_file $MYSQLD_DATADIR/test/t3.ibd.backup $MYSQLD_DATADIR/test/t3.ibd +--copy_file $MYSQLD_DATADIR/test/t4.ibd.backup $MYSQLD_DATADIR/test/t4.ibd +--copy_file $MYSQLD_DATADIR/test/t5.ibd.backup $MYSQLD_DATADIR/test/t5.ibd +--copy_file $MYSQLD_DATADIR/test/t6.ibd.backup $MYSQLD_DATADIR/test/t6.ibd + +--echo # Corrupt FIL_PAGE_FILE_FLUSH_LSN_OR_KEY_VERSION+4 (post encryption checksum) + +perl; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t1.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 30, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t2.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 30, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t3.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 30, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t6.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 30, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +EOF + +-- disable_result_log +--error 1 +--exec $INNOCHECKSUM $t1_IBD + +--echo # Run innochecksum on t2 +--error 1 +--exec $INNOCHECKSUM $t2_IBD + +--echo # Run innochecksum on t3 +--error 1 +--exec $INNOCHECKSUM $t3_IBD + +--echo # Run innochecksum on t6 +--echo # Space ID mismatch +--error 1 +--exec $INNOCHECKSUM $t6_IBD + +--enable_result_log + +--echo # Restore the original tables +--remove_file $MYSQLD_DATADIR/test/t1.ibd +--remove_file $MYSQLD_DATADIR/test/t2.ibd +--remove_file $MYSQLD_DATADIR/test/t3.ibd +--remove_file $MYSQLD_DATADIR/test/t4.ibd +--remove_file $MYSQLD_DATADIR/test/t5.ibd +--remove_file $MYSQLD_DATADIR/test/t6.ibd +--copy_file $MYSQLD_DATADIR/test/t1.ibd.backup $MYSQLD_DATADIR/test/t1.ibd +--copy_file $MYSQLD_DATADIR/test/t2.ibd.backup $MYSQLD_DATADIR/test/t2.ibd +--copy_file $MYSQLD_DATADIR/test/t3.ibd.backup $MYSQLD_DATADIR/test/t3.ibd +--copy_file $MYSQLD_DATADIR/test/t4.ibd.backup $MYSQLD_DATADIR/test/t4.ibd +--copy_file $MYSQLD_DATADIR/test/t5.ibd.backup $MYSQLD_DATADIR/test/t5.ibd +--copy_file $MYSQLD_DATADIR/test/t6.ibd.backup $MYSQLD_DATADIR/test/t6.ibd + +--echo # Corrupt FIL_DATA+10 (data) + +perl; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t1.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 48, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t2.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 48, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t3.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 48, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t6.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 48, SEEK_SET) or die "seek"; +print FILE pack("H*", "c00lcafedeadb017"); +close FILE or die "close"; +EOF + +if (0 && $have_debug) { # these messages sometimes fail to appear +--let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err +--let SEARCH_PATTERN= InnoDB: Crash recovery is broken due to insufficient innodb_log_file_size; last checkpoint LSN=\\d+, current LSN=\\d+\\. Shutdown is in progress\\..*InnoDB: Crash recovery was broken.* +--echo # FOUND 1 is expected for both. +--source include/search_pattern_in_file.inc +--let SEARCH_PATTERN= InnoDB: Crash recovery was broken +--source include/search_pattern_in_file.inc +} + +-- disable_result_log +--error 1 +--exec $INNOCHECKSUM $t1_IBD + +--echo # Run innochecksum on t2 +--error 1 +--exec $INNOCHECKSUM $t2_IBD + +--echo # Run innochecksum on t3 +--error 1 +--exec $INNOCHECKSUM $t3_IBD + +--echo # Run innochecksum on t6 +--error 1 +--exec $INNOCHECKSUM $t6_IBD + +--enable_result_log + +--echo # Restore the original tables +--move_file $MYSQLD_DATADIR/test/t1.ibd.backup $MYSQLD_DATADIR/test/t1.ibd +--move_file $MYSQLD_DATADIR/test/t2.ibd.backup $MYSQLD_DATADIR/test/t2.ibd +--move_file $MYSQLD_DATADIR/test/t3.ibd.backup $MYSQLD_DATADIR/test/t3.ibd +--move_file $MYSQLD_DATADIR/test/t4.ibd.backup $MYSQLD_DATADIR/test/t4.ibd +--move_file $MYSQLD_DATADIR/test/t5.ibd.backup $MYSQLD_DATADIR/test/t5.ibd +--move_file $MYSQLD_DATADIR/test/t6.ibd.backup $MYSQLD_DATADIR/test/t6.ibd + +--source include/start_mysqld.inc +DROP TABLE t1, t2, t3, t4, t5, t6; diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change.combinations b/mysql-test/suite/encryption/t/innodb-bad-key-change.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change.opt b/mysql-test/suite/encryption/t/innodb-bad-key-change.opt new file mode 100644 index 00000000..d14f3bcb --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change.opt @@ -0,0 +1 @@ +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change.test b/mysql-test/suite/encryption/t/innodb-bad-key-change.test new file mode 100644 index 00000000..05a3b5f4 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change.test @@ -0,0 +1,112 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# embedded does not support restart +-- source include/not_embedded.inc + +# +# MDEV-8588: Assertion failure in file ha_innodb.cc line 21140 if at least one encrypted +# table exists and encryption service is not available. +# + +call mtr.add_suppression("Plugin 'file_key_management' init function returned error"); +call mtr.add_suppression("Plugin 'file_key_management' registration.*failed"); +call mtr.add_suppression("InnoDB: Table `test`\\.`t[12]` has an unreadable root page"); +call mtr.add_suppression("Table .*t[12].* is corrupted"); +call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t[12]\\.ibd' cannot be decrypted; key_version=1"); +call mtr.add_suppression("failed to read \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\]"); +call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=[1-9][0-9]*, page number=3\\] in file .*test.t1.ibd looks corrupted; key_version=1"); +call mtr.add_suppression("File '.*mysql-test.std_data.keysbad3\\.txt' not found"); +# for innodb_checksum_algorithm=full_crc32 only +call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); + +--echo # Start server with keys2.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +SET GLOBAL innodb_file_per_table = ON; + +CREATE TABLE t1 (c VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=2; +INSERT INTO t1 VALUES ('foobar'); +ALTER TABLE t1 ADD COLUMN c2 INT; +INSERT INTO t1 VALUES ('foobar',2); +SELECT * FROM t1; +TRUNCATE TABLE t1; +SELECT * FROM t1; +INSERT INTO t1 VALUES ('foobar',1); +INSERT INTO t1 VALUES ('foobar',2); +FLUSH TABLE WITH READ LOCK; +SELECT * FROM t1; + +--echo +--echo # Restart server with keysbad3.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keysbad3.txt +-- source include/restart_mysqld.inc + +--disable_warnings +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT * FROM t1; +--enable_warnings + +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keysbad3.txt +-- source include/restart_mysqld.inc + +--replace_regex /key_id [1-9][0-9]*/\1 / +DROP TABLE t1; + +# +# MDEV-8591: Database page corruption on disk or a failed space, Assertion failure in file buf0buf.cc +# line 2856 on querying a table using wrong default encryption key +# + +--echo # Start server with keys3.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +-- source include/restart_mysqld.inc + +SET GLOBAL innodb_default_encryption_key_id=5; +CREATE TABLE t2 (c VARCHAR(8), id int not null primary key, b int, key(b)) ENGINE=InnoDB ENCRYPTED=YES; +INSERT INTO t2 VALUES ('foobar',1,2); + +--echo +--echo # Restart server with keys2.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +--disable_warnings +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT * FROM t2; + +--error ER_TABLE_CORRUPT +SELECT * FROM t2 where id = 1; + +--error ER_TABLE_CORRUPT +SELECT * FROM t2 where b = 1; + +--error ER_TABLE_CORRUPT +INSERT INTO t2 VALUES ('tmp',3,3); + +--error ER_TABLE_CORRUPT +DELETE FROM t2 where b = 3; + +--error ER_TABLE_CORRUPT +DELETE FROM t2 where id = 3; + +--error ER_TABLE_CORRUPT +UPDATE t2 set b = b +1; + +OPTIMIZE TABLE t2; + +--error ER_TABLE_CORRUPT +ALTER TABLE t2 ADD COLUMN d INT; + +ANALYZE TABLE t2; + +--error ER_TABLE_CORRUPT +TRUNCATE TABLE t2; + +DROP TABLE t2; +--enable_warnings + +--echo +--echo # Start server with keys2.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change2.combinations b/mysql-test/suite/encryption/t/innodb-bad-key-change2.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change2.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change2.opt b/mysql-test/suite/encryption/t/innodb-bad-key-change2.opt new file mode 100644 index 00000000..d14f3bcb --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change2.opt @@ -0,0 +1 @@ +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change2.test b/mysql-test/suite/encryption/t/innodb-bad-key-change2.test new file mode 100644 index 00000000..21a9ddb2 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change2.test @@ -0,0 +1,103 @@ +--source include/have_innodb.inc +# embedded does not support restart +-- source include/not_embedded.inc +-- source filekeys_plugin_exists.inc +# +# MDEV-8750: Server crashes in page_cur_is_after_last on altering table using a wrong encryption key +# MDEV-8769: Server crash at file btr0btr.ic line 122 when defragmenting encrypted table using incorrect keys +# MDEV-8768: Server crash at file btr0btr.ic line 122 when checking encrypted table using incorrect keys +# MDEV-8727: Server/InnoDB hangs on shutdown after trying to read an encrypted table with a wrong key +# +call mtr.add_suppression("InnoDB: Table `test`\\.`t1` (has an unreadable root page|does not exist.*is trying to rename)"); +call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t1(new)?\\.ibd' cannot be decrypted; key_version=1"); +call mtr.add_suppression("InnoDB: Recovery failed to read page"); +# Suppression for builds where file_key_management plugin is linked statically +call mtr.add_suppression("Couldn't load plugins from 'file_key_management"); +call mtr.add_suppression("InnoDB: Tablespace for table \`test\`.\`t1\` is set as discarded\\."); +call mtr.add_suppression("Table .*t1.* is corrupted"); +call mtr.add_suppression("InnoDB: Cannot delete tablespace .* because it is not found in the tablespace memory cache"); +call mtr.add_suppression("InnoDB: ALTER TABLE `test`\\.`t1` DISCARD TABLESPACE failed to find tablespace"); +# for innodb_checksum_algorithm=full_crc32 only +call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +--source include/restart_mysqld.inc + +SET GLOBAL innodb_file_per_table = ON; + +CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB +ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +--source include/restart_mysqld.inc + +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT * FROM t1; +--replace_regex /key_id [1-9][0-9]*/\1 / +SHOW WARNINGS; +--error ER_TABLE_CORRUPT +ALTER TABLE t1 ENGINE=InnoDB; +--replace_regex /key_id [1-9][0-9]*/\1 / +SHOW WARNINGS; +OPTIMIZE TABLE t1; +--replace_regex /key_id [1-9][0-9]*/\1 / +SHOW WARNINGS; + +CHECK TABLE t1; +--replace_regex /key_id [1-9][0-9]*/\1 / +SHOW WARNINGS; + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +--source include/restart_mysqld.inc + +let MYSQLD_DATADIR =`SELECT @@datadir`; + +FLUSH TABLES t1 FOR EXPORT; +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF +UNLOCK TABLES; + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +--source include/restart_mysqld.inc + +--error ER_NO_SUCH_TABLE_IN_ENGINE +ALTER TABLE t1 DISCARD TABLESPACE; +# Drop table will succeed. +DROP TABLE t1; + +CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB +ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +ALTER TABLE t1 DISCARD TABLESPACE; + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +--source include/restart_mysqld.inc + +ALTER TABLE t1 DISCARD TABLESPACE; + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +ALTER TABLE t1 IMPORT TABLESPACE; +SHOW CREATE TABLE t1; + +--let $restart_parameters= --innodb-encrypt-tables --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +--source include/restart_mysqld.inc + +--error ER_ERROR_ON_RENAME +RENAME TABLE t1 TO t1new; +--error ER_TABLE_CORRUPT +ALTER TABLE t1 RENAME TO t1new; +# Drop should succeed even with incorrect keys +DROP TABLE t1; diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change3.combinations b/mysql-test/suite/encryption/t/innodb-bad-key-change3.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change3.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change3.test b/mysql-test/suite/encryption/t/innodb-bad-key-change3.test new file mode 100644 index 00000000..9c2918f3 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change3.test @@ -0,0 +1,100 @@ +--source include/have_innodb.inc +# embedded does not support restart +-- source include/not_embedded.inc +-- source include/not_valgrind.inc +# Avoid CrashReporter popup on Mac +-- source include/not_crashrep.inc +-- source filekeys_plugin_exists.inc +# +# MDEV-8772: Assertion failure in file ha_innodb.cc line 20027 when importing page compressed and encrypted tablespace using incorrect keys +# + +call mtr.add_suppression("InnoDB: Tablespace for table .* is set as discarded."); +call mtr.add_suppression("InnoDB: Cannot calculate statistics for table .* because the .ibd file is missing. Please refer to .* for how to resolve the issue."); + +--let $MYSQLD_TMPDIR = `SELECT @@tmpdir` +--let $MYSQLD_DATADIR = `SELECT @@datadir` +--let SEARCH_RANGE = 10000000 +--let t1_IBD = $MYSQLD_DATADIR/test/t1.ibd +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc + +--write_file $MYSQLTEST_VARDIR/keys1.txt +1;770A8A65DA156D24EE2A093277530142 +4;770A8A65DA156D24EE2A093277530143 +EOF + +--exec echo "restart:--innodb-encrypt-tables --innodb-stats-persistent --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQLTEST_VARDIR/keys1.txt" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc + +set global innodb_compression_algorithm = 1; + +CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(255)) ENGINE=InnoDB PAGE_COMPRESSED=1 ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +SHOW WARNINGS; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (1,'foobar'),(2,'barfoo'); +let MYSQLD_DATADIR =`SELECT @@datadir`; +FLUSH TABLE t1 FOR EXPORT; +--echo # List before copying files +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF +UNLOCK TABLES; + +ALTER TABLE t1 DISCARD TABLESPACE; + +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc + +--write_file $MYSQLTEST_VARDIR/keys2.txt +1;770A8A65DA156D24EE2A093277530142 +4;770A8A65DA156D24EE2A093277530144 +EOF + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +--exec echo "restart:--innodb-encrypt-tables --innodb-stats-persistent --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQLTEST_VARDIR/keys2.txt" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +--source include/restart_mysqld.inc + +--error ER_GET_ERRMSG +ALTER TABLE t1 IMPORT TABLESPACE; +SHOW CREATE TABLE t1; +--error ER_TABLESPACE_DISCARDED +SELECT * FROM t1; +--sleep 5 +--echo # Tablespaces should be still encrypted +-- let SEARCH_FILE=$t1_IBD +--let SEARCH_PATTERN=foobar +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc + +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc +--remove_file $MYSQLTEST_VARDIR/keys1.txt +--write_file $MYSQLTEST_VARDIR/keys1.txt +1;770A8A65DA156D24EE2A093277530142 +4;770A8A65DA156D24EE2A093277530143 +EOF + +--exec echo "restart:--innodb-encrypt-tables --innodb-stats-persistent --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQLTEST_VARDIR/keys1.txt" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc +DROP TABLE t1; + +# reset system + +--remove_file $MYSQLTEST_VARDIR/keys1.txt +--remove_file $MYSQLTEST_VARDIR/keys2.txt diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change4.combinations b/mysql-test/suite/encryption/t/innodb-bad-key-change4.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change4.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change4.opt b/mysql-test/suite/encryption/t/innodb-bad-key-change4.opt new file mode 100644 index 00000000..ce4c31a5 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change4.opt @@ -0,0 +1,5 @@ +--loose-innodb-buffer-pool-stats +--loose-innodb-buffer-page +--loose-innodb-buffer-page-lru +--innodb-defragment=1 +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-bad-key-change4.test b/mysql-test/suite/encryption/t/innodb-bad-key-change4.test new file mode 100644 index 00000000..c37eb365 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-bad-key-change4.test @@ -0,0 +1,44 @@ +--source include/have_innodb.inc +# embedded does not support restart +-- source include/not_embedded.inc +-- source filekeys_plugin_exists.inc +# +# MDEV-8769: Server crash at file btr0btr.ic line 122 when defragmenting encrypted table using incorrect keys +# MDEV-8768: Server crash at file btr0btr.ic line 122 when checking encrypted table using incorrect keys +# + +call mtr.add_suppression("InnoDB: Table `test`\\.`t1` (has an unreadable root page)"); +call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t1\\.ibd' cannot be decrypted; key_version=1"); +call mtr.add_suppression("InnoDB: Recovery failed to read page"); +# Suppression for builds where file_key_management plugin is linked statically +call mtr.add_suppression("Couldn't load plugins from 'file_key_management"); +call mtr.add_suppression("Table .*t1.* is corrupted"); +# for innodb_checksum_algorithm=full_crc32 only +call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +--source include/restart_mysqld.inc + +SET GLOBAL innodb_file_per_table = ON; + +CREATE TABLE t1 (pk INT PRIMARY KEY, f VARCHAR(8)) ENGINE=InnoDB +ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +INSERT INTO t1 VALUES (1,'foo'),(2,'bar'); + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +--source include/restart_mysqld.inc + +--replace_regex /key_id [1-9][0-9]*/\1 / +OPTIMIZE TABLE t1; +--replace_regex /key_id [1-9][0-9]*/\1 / +SHOW WARNINGS; + +--replace_regex /key_id [1-9][0-9]*/\1 / +CHECK TABLE t1; +--replace_regex /key_id [1-9][0-9]*/\1 / +SHOW WARNINGS; + +--let $restart_parameters=--plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +--source include/restart_mysqld.inc + +DROP TABLE t1; diff --git a/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test b/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test new file mode 100644 index 00000000..157fb25b --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-checksum-algorithm.test @@ -0,0 +1,83 @@ +-- source include/innodb_checksum_algorithm.inc +-- source include/innodb_page_size.inc +-- source include/have_file_key_management_plugin.inc + +SET @saved_file_per_table = @@global.innodb_file_per_table; +SET @saved_encrypt_tables = @@global.innodb_encrypt_tables; +SET @saved_encryption_threads = @@global.innodb_encryption_threads; +SET @saved_encryption_key_id = @@global.innodb_default_encryption_key_id; + +SET GLOBAL innodb_file_per_table = ON; +SET GLOBAL innodb_encrypt_tables = ON; +SET GLOBAL innodb_encryption_threads = 4; + +SET GLOBAL innodb_default_encryption_key_id=4; + +let MYSQLD_DATADIR =`SELECT @@datadir`; + +# ROW_FORMAT=COMPRESSED is unavailable with innodb_page_size=32k or 64k +let $row_format_compressed= `select case when @@global.innodb_page_size>16384 +then 'ROW_FORMAT=DYNAMIC' else 'ROW_FORMAT=COMPRESSED' end`; + +eval create table tce(a serial, b blob, index(b(10))) engine=innodb +$row_format_compressed encrypted=yes; +eval create table tc(a serial, b blob, index(b(10))) engine=innodb +$row_format_compressed encrypted=no; +eval create table te(a serial, b blob, index(b(10))) engine=innodb +encrypted=yes; +eval create table t(a serial, b blob, index(b(10))) engine=innodb +encrypted=no; +eval create table tpe(a serial, b blob, index(b(10))) engine=innodb +page_compressed=yes encrypted=yes; +eval create table tp(a serial, b blob, index(b(10))) engine=innodb +page_compressed=yes encrypted=no; + +begin; +eval insert into tce(b) values (repeat('secret',20)); +eval insert into tc(b) values (repeat('secret',20)); +eval insert into te(b) values (repeat('secret',20)); +eval insert into t(b) values (repeat('secret',20)); +eval insert into tpe(b) values (repeat('secret',20)); +eval insert into tp(b) values (repeat('secret',20)); +commit; + +eval FLUSH TABLES tce, tc, te, t, tpe, tp FOR EXPORT; +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", ("tce", "tc", "te", "t", "tpe", "tp")); +EOF +--list_files $MYSQLD_DATADIR/test +UNLOCK TABLES; + +ALTER TABLE tce DISCARD TABLESPACE; +ALTER TABLE tc DISCARD TABLESPACE; +ALTER TABLE te DISCARD TABLESPACE; +ALTER TABLE t DISCARD TABLESPACE; +ALTER TABLE tpe DISCARD TABLESPACE; +ALTER TABLE tp DISCARD TABLESPACE; + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_restore_tablespaces("test", ("tce", "tc", "te", "t", "tpe", "tp")); +EOF + +ALTER TABLE tce IMPORT TABLESPACE; +update tce set b=substr(b,1); +ALTER TABLE tc IMPORT TABLESPACE; +update tc set b=substr(b,1); +ALTER TABLE te IMPORT TABLESPACE; +update te set b=substr(b,1); +ALTER TABLE t IMPORT TABLESPACE; +update t set b=substr(b,1); +ALTER TABLE tpe IMPORT TABLESPACE; +update tpe set b=substr(b,1); +ALTER TABLE tp IMPORT TABLESPACE; +update tp set b=substr(b,1); + +CHECK TABLE tce, tc, te, t, tpe, tp; +DROP TABLE tce, tc, te, t, tpe, tp; + +SET GLOBAL innodb_file_per_table = @saved_file_per_table; +SET GLOBAL innodb_encrypt_tables = @saved_encrypt_tables; +SET GLOBAL innodb_encryption_threads = @saved_encryption_threads; +SET GLOBAL innodb_default_encryption_key_id = @saved_encryption_key_id; diff --git a/mysql-test/suite/encryption/t/innodb-compressed-blob.opt b/mysql-test/suite/encryption/t/innodb-compressed-blob.opt new file mode 100644 index 00000000..f44dcb1e --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-compressed-blob.opt @@ -0,0 +1,5 @@ +--max-allowed-packet=64K +--innodb-tablespaces-encryption +--innodb-encrypt-tables=on +--innodb-encryption-threads=4 +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-compressed-blob.test b/mysql-test/suite/encryption/t/innodb-compressed-blob.test new file mode 100644 index 00000000..12d061a8 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-compressed-blob.test @@ -0,0 +1,41 @@ +-- source include/innodb_page_size_small.inc +-- source include/have_file_key_management_plugin.inc + +# embedded does not support restart +-- source include/not_embedded.inc + +call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t[123]\\.ibd' cannot be decrypted; key_version=1"); +call mtr.add_suppression("InnoDB: Recovery failed to read page"); +call mtr.add_suppression("InnoDB: Unable to decompress ..test.t[1-3]\\.ibd\\[page id: space=[1-9][0-9]*, page number=[0-9]+\\]"); +call mtr.add_suppression("InnoDB: Table `test`\\.`t[12]` is corrupted"); + +--echo # Restart mysqld --file-key-management-filename=keys2.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +SET GLOBAL innodb_file_per_table = ON; + +set GLOBAL innodb_default_encryption_key_id=4; +create table t1(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed; +create table t2(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes; +create table t3(a int not null primary key, b blob, index(b(10))) engine=innodb row_format=compressed encrypted=no; + +insert into t1 values (1, repeat('secret',6000)); +insert into t2 values (1, repeat('secret',6000)); +insert into t3 values (1, repeat('secret',6000)); + +--echo # Restart mysqld --file-key-management-filename=keys3.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +-- source include/restart_mysqld.inc + +--error ER_NO_SUCH_TABLE_IN_ENGINE +select count(*) from t1 FORCE INDEX (b) where b like 'secret%'; +--error ER_NO_SUCH_TABLE_IN_ENGINE +select count(*) from t2 FORCE INDEX (b) where b like 'secret%'; +select count(*) from t3 FORCE INDEX (b) where b like 'secret%'; + +--echo # Restart mysqld --file-key-management-filename=keys2.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +drop table t1,t2,t3; diff --git a/mysql-test/suite/encryption/t/innodb-discard-import-change.combinations b/mysql-test/suite/encryption/t/innodb-discard-import-change.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-discard-import-change.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-discard-import-change.test b/mysql-test/suite/encryption/t/innodb-discard-import-change.test new file mode 100644 index 00000000..2cb6865e --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-discard-import-change.test @@ -0,0 +1,118 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# +# MDEV-11656: 'Data structure corruption' IMPORT TABLESPACE doesn't work for encrypted InnoDB tables if space_id changed +# + +call mtr.add_suppression("InnoDB: Table .* tablespace is set as discarded"); + +let $innodb_compression_algo = `SELECT @@innodb_compression_algorithm`; + +SET GLOBAL innodb_compression_algorithm = 1; + +create table t1(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb encrypted=yes encryption_key_id=4; +create table t2(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb encrypted=yes encryption_key_id=1; +create table t3(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes; +create table t4(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes encrypted=yes encryption_key_id=4; +create table t5(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb; + +insert into t1 values (NULL, 'verysecretmessage'); +insert into t1(b) select b from t1; +insert into t1(b) select b from t1; +insert into t1(b) select b from t1; +insert into t1(b) select b from t1; +insert into t1(b) select b from t1; +insert into t1(b) select b from t1; +insert into t1(b) select b from t1; +insert into t1(b) select b from t1; +insert into t2 select * from t1; +insert into t3 select * from t1; +insert into t4 select * from t1; +insert into t5 select * from t1; + +let MYSQLD_DATADIR =`SELECT @@datadir`; +FLUSH TABLE t1,t2,t3,t4,t5 FOR EXPORT; +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1","t2","t3","t4","t5"); +EOF +--list_files $MYSQLD_DATADIR/test +UNLOCK TABLES; + +ALTER TABLE t1 DISCARD TABLESPACE; +ALTER TABLE t2 DISCARD TABLESPACE; +ALTER TABLE t3 DISCARD TABLESPACE; +ALTER TABLE t4 DISCARD TABLESPACE; +ALTER TABLE t5 DISCARD TABLESPACE; + +# +# Now intentionally change space_id for t1,t3,t4,t5 +# +DROP TABLE t1; +DROP TABLE t3; +DROP TABLE t4; +DROP TABLE t5; + +create table t6(a int) engine=innodb; +create table t5(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb; +create table t3(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes; +create table t1(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb encrypted=yes encryption_key_id=4; +create table t4(c1 bigint not null primary key auto_increment, b char(200)) engine=innodb page_compressed=yes encrypted=yes encryption_key_id=4; + +ALTER TABLE t1 DISCARD TABLESPACE; +ALTER TABLE t3 DISCARD TABLESPACE; +ALTER TABLE t4 DISCARD TABLESPACE; +ALTER TABLE t5 DISCARD TABLESPACE; + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1","t2","t3","t4","t5"); +ib_restore_tablespaces("test", "t1","t2","t3","t4","t5"); +EOF + +ALTER TABLE t1 IMPORT TABLESPACE; +SHOW CREATE TABLE t1; +SELECT COUNT(*) FROM t1; +ALTER TABLE t2 IMPORT TABLESPACE; +SELECT COUNT(*) FROM t2; +ALTER TABLE t3 IMPORT TABLESPACE; +SELECT COUNT(*) FROM t3; +ALTER TABLE t4 IMPORT TABLESPACE; +SELECT COUNT(*) FROM t4; +ALTER TABLE t5 IMPORT TABLESPACE; +SELECT COUNT(*) FROM t5; + +# +# Verify +# +--let $MYSQLD_TMPDIR = `SELECT @@tmpdir` +--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 SEARCH_PATTERN=verysecretmessage +--echo # t1 encrypted expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc +--echo # t2 encrypted expecting NOT FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--echo # t3 page compressed expecting NOT FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--echo # t4 page compressed and encrypted expecting NOT FOUND +-- let SEARCH_FILE=$t4_IBD +-- source include/search_pattern_in_file.inc +--echo # t5 normal expecting FOUND +-- let SEARCH_FILE=$t5_IBD +-- source include/search_pattern_in_file.inc + +DROP TABLE t1,t2,t3,t4,t5,t6; + +# reset system +--disable_query_log +EVAL SET GLOBAL innodb_compression_algorithm = $innodb_compression_algo; +--enable_query_log diff --git a/mysql-test/suite/encryption/t/innodb-discard-import.combinations b/mysql-test/suite/encryption/t/innodb-discard-import.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-discard-import.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-discard-import.test b/mysql-test/suite/encryption/t/innodb-discard-import.test new file mode 100644 index 00000000..62dafec4 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-discard-import.test @@ -0,0 +1,121 @@ +-- source include/have_innodb.inc +-- source include/innodb_page_size_small.inc +-- source include/have_file_key_management_plugin.inc + +# +# MDEV-8770: Incorrect error message when importing page compressed tablespace +# + +call mtr.add_suppression("InnoDB: Tablespace for table .* is set as discarded."); +call mtr.add_suppression("InnoDB: Cannot calculate statistics for table .* because the .ibd file is missing. Please refer to .* for how to resolve the issue."); + +let $innodb_compression_algo = `SELECT @@innodb_compression_algorithm`; +SET GLOBAL innodb_compression_algorithm = 1; + +--let $MYSQLD_TMPDIR = `SELECT @@tmpdir` +--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 + +create table t1(c1 bigint not null, b char(200)) engine=innodb encrypted=yes encryption_key_id=4; +show warnings; +create table t2(c1 bigint not null, b char(200)) engine=innodb page_compressed=1 encrypted=yes encryption_key_id=4; +show warnings; +let $kbs= `select floor(@@global.innodb_page_size/1024)`; +--replace_regex / key_block_size=\d+//i +eval create table t3(c1 bigint not null, b char(200)) engine=innodb row_format=compressed encrypted=yes encryption_key_id=4 key_block_size=$kbs; +show warnings; +create table t4(c1 bigint not null, b char(200)) engine=innodb page_compressed=1; +show warnings; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into t1 values(current_num, repeat('foobar',30)); + insert into t2 values(current_num, repeat('barfoo',30)); + insert into t3 values(current_num, repeat('tmpres',30)); + insert into t4 values(current_num, repeat('mysql',30)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +set autocommit=0; +call innodb_insert_proc(2000); +commit; +set autocommit=1; + +select count(*) from t1; +select count(*) from t2; +select count(*) from t3; +select count(*) from t4; + +let MYSQLD_DATADIR =`SELECT @@datadir`; +FLUSH TABLE t1,t2,t3,t4 FOR EXPORT; +--echo # List before copying files +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1","t2","t3","t4"); +EOF +--list_files $MYSQLD_DATADIR/test + +UNLOCK TABLES; + +ALTER TABLE t1 DISCARD TABLESPACE; +ALTER TABLE t2 DISCARD TABLESPACE; +ALTER TABLE t3 DISCARD TABLESPACE; +ALTER TABLE t4 DISCARD TABLESPACE; + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1","t2","t3","t4"); +ib_restore_tablespaces("test", "t1","t2","t3","t4"); +EOF + +ALTER TABLE t1 IMPORT TABLESPACE; +SHOW CREATE TABLE t1; +SELECT COUNT(*) FROM t1; +ALTER TABLE t2 IMPORT TABLESPACE; +SHOW CREATE TABLE t2; +SELECT COUNT(*) FROM t2; +ALTER TABLE t3 IMPORT TABLESPACE; +--replace_regex / key_block_size=\d+//i +SHOW CREATE TABLE t3; +SELECT COUNT(*) FROM t3; +ALTER TABLE t4 IMPORT TABLESPACE; +SHOW CREATE TABLE t4; +SELECT COUNT(*) FROM t4; + +--echo # tables should be still either encrypted and/or compressed +--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=barfoo +--echo # t2 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=tmpres +--echo # t3 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=mysql +--echo # t4 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t4_IBD +-- source include/search_pattern_in_file.inc + +DROP PROCEDURE innodb_insert_proc; +DROP TABLE t1,t2,t3,t4; + +# reset system +--disable_query_log +eval SET GLOBAL innodb_compression_algorithm = $innodb_compression_algo; +--enable_query_log diff --git a/mysql-test/suite/encryption/t/innodb-encr-threads.test b/mysql-test/suite/encryption/t/innodb-encr-threads.test new file mode 100644 index 00000000..1a90a318 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-encr-threads.test @@ -0,0 +1,14 @@ +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc + +# +# MDEV-8159 InnoDB: Failing assertion: key_state->key_id +# +create table t1 (i int) engine=innodb; +set global innodb_encryption_threads = 1; +set global innodb_encryption_rotate_key_age = 2; +insert t1 values (1); +drop table t1; +set global innodb_encryption_threads = 0; +set global innodb_encryption_rotate_key_age = 1; + diff --git a/mysql-test/suite/encryption/t/innodb-encryption-alter.test b/mysql-test/suite/encryption/t/innodb-encryption-alter.test new file mode 100644 index 00000000..6e5d449b --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-encryption-alter.test @@ -0,0 +1,133 @@ +-- source include/have_innodb.inc +-- source include/have_debug.inc +-- source include/have_debug_sync.inc +-- source include/have_file_key_management_plugin.inc + +# +# MDEV-8817: Failing assertion: new_state->key_version != ENCRYPTION_KEY_VERSION_INVALID +# + +SET GLOBAL innodb_encrypt_tables = ON; +SET GLOBAL innodb_encryption_threads = 4; + +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=4; +DROP TABLE t1; +set @save_global = @@GLOBAL.innodb_default_encryption_key_id; +set innodb_default_encryption_key_id = 99; +set global innodb_default_encryption_key_id = 99; +set global innodb_default_encryption_key_id = @save_global; +--error 1005 +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; +SHOW WARNINGS; +--error 1005 +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES; +SHOW WARNINGS; +set innodb_default_encryption_key_id = 4; +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; +SHOW CREATE TABLE t1; +CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO ENCRYPTION_KEY_ID=1; +--error ER_ILLEGAL_HA_CREATE_OPTION +ALTER TABLE t1 ENCRYPTION_KEY_ID=99; +SHOW WARNINGS; +set innodb_default_encryption_key_id = 1; + + +--disable_warnings +--disable_query_log +let $i = 400; +while ($i) +{ +INSERT INTO t1 values(NULL, substring(MD5(RAND()), -128)); +dec $i; +} +commit; +INSERT INTO t2 select * from t1; + +--disable_abort_on_error + +--connect (con1,localhost,root,,test) +--connect (con2,localhost,root,,test) + +let $i = 50; +while ($i) +{ +connection con1; +send ALTER TABLE t1 ENCRYPTED=NO ENCRYPTION_KEY_ID=1; +connection con2; +send ALTER TABLE t1 ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +connection default; +send ALTER TABLE t2 ENCRYPTED=NO ENCRYPTION_KEY_ID=1; +connection con1; +--reap; +ALTER TABLE t1 ENCRYPTED=NO ENCRYPTION_KEY_ID=1; +connection con2; +--reap +ALTER TABLE t1 ENCRYPTED=YES ENCRYPTION_KEY_ID=4; +connection default; +--reap +ALTER TABLE t2 ENCRYPTED=YES ENCRYPTION_KEY_ID=1; +ALTER TABLE t1 ENCRYPTED=NO ENCRYPTION_KEY_ID=1; +dec $i; +} + +connection default; +--disconnect con1 +--disconnect con2 + +--enable_abort_on_error +--enable_warnings +--enable_query_log + +drop table t1,t2; + +# +# MDEV-17230: encryption_key_id from alter is ignored by encryption threads +# +--enable_warnings +SET GLOBAL innodb_encrypt_tables=OFF; +CREATE TABLE t1 (a int not null primary key) engine=innodb; +ALTER TABLE t1 ENCRYPTION_KEY_ID=4; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t2 (a int not null primary key) engine=innodb; +ALTER TABLE t2 ENCRYPTION_KEY_ID=4, ALGORITHM=COPY; +SHOW WARNINGS; +SHOW CREATE TABLE t2; + +CREATE TABLE t3 (a int not null primary key) engine=innodb ENCRYPTION_KEY_ID=4; +DROP TABLE t3; + +SET GLOBAL innodb_encrypt_tables='FORCE'; +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 (a int primary key) engine=innodb encrypted=no; +SHOW WARNINGS; + +FLUSH TABLES; + +create table t1(f1 int not null, f2 int not null)engine=innodb encrypted=yes; +insert into t1 values(1, 2), (2, 3), (4, 5), (5, 6), (7, 8); +insert into t1 select * from t1; +BEGIN; +INSERT INTO t2 VALUES (1); + +connect con1, localhost, root; +SET DEBUG_SYNC = 'row_log_table_apply2_before SIGNAL done WAIT_FOR ever'; +send alter table t1 force; + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR done'; +SET GLOBAL innodb_flush_log_at_trx_commit=1; +COMMIT; + +let $restart_parameters = --innodb_encryption_threads=2; +let $restart_noprint = 2; +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc +disconnect con1; + +select * from t1; +drop table t1,t2; diff --git a/mysql-test/suite/encryption/t/innodb-encryption-disable.opt b/mysql-test/suite/encryption/t/innodb-encryption-disable.opt new file mode 100644 index 00000000..d14f3bcb --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-encryption-disable.opt @@ -0,0 +1 @@ +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-encryption-disable.test b/mysql-test/suite/encryption/t/innodb-encryption-disable.test new file mode 100644 index 00000000..939ad2b5 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-encryption-disable.test @@ -0,0 +1,55 @@ +-- source include/have_innodb.inc +# embedded does not support restart +-- source include/not_embedded.inc +-- source filekeys_plugin_exists.inc + +# +# MDEV-9559: Server without encryption configs crashes if selecting from an implicitly encrypted table +# + +call mtr.add_suppression("InnoDB: Table `test`\\.`t[15]` (has an unreadable root page|is corrupted)"); +call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t[15]\\.ibd' cannot be decrypted\\."); +call mtr.add_suppression("InnoDB: Recovery failed to read page"); +call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=[1-9][0-9]*, page number=3\\] in file .*test.t[15].ibd looks corrupted; key_version=1"); +call mtr.add_suppression("InnoDB: Table `test`\\.`t[15]` is corrupted"); + +# Suppression for builds where file_key_management plugin is linked statically +call mtr.add_suppression("Couldn't load plugins from 'file_key_management"); + +--let $restart_parameters=--innodb-encrypt-tables=ON --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +--source include/restart_mysqld.inc + +create table t5 ( + `intcol1` int(32) DEFAULT NULL, + `intcol2` int(32) DEFAULT NULL, + `charcol1` varchar(128) DEFAULT NULL, + `charcol2` varchar(128) DEFAULT NULL, + `charcol3` varchar(128) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +insert into t5 values (1,2,'maria','db','encryption'); + +CREATE TABLE `t1` ( + `intcol1` int(32) DEFAULT NULL, + `intcol2` int(32) DEFAULT NULL, + `charcol1` varchar(128) DEFAULT NULL, + `charcol2` varchar(128) DEFAULT NULL, + `charcol3` varchar(128) DEFAULT NULL +) ENGINE=InnoDB; + +insert into t1 values (1,2,'maria','db','encryption'); +alter table t1 encrypted='yes' `encryption_key_id`=1; + +--let $restart_parameters=--innodb-encrypt-tables=OFF +--source include/restart_mysqld.inc + +--error ER_NO_SUCH_TABLE_IN_ENGINE +select * from t1; +--error ER_NO_SUCH_TABLE_IN_ENGINE +select * from t5; + +--let $restart_parameters=--innodb-encrypt-tables=ON --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +--source include/restart_mysqld.inc + +drop table t1; +drop table t5; diff --git a/mysql-test/suite/encryption/t/innodb-first-page-read.opt b/mysql-test/suite/encryption/t/innodb-first-page-read.opt new file mode 100644 index 00000000..38d69691 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-first-page-read.opt @@ -0,0 +1,5 @@ +--innodb-encrypt-tables=ON +--innodb-encrypt-log=ON +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb-force-corrupt.opt b/mysql-test/suite/encryption/t/innodb-force-corrupt.opt new file mode 100644 index 00000000..d14f3bcb --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-force-corrupt.opt @@ -0,0 +1 @@ +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-force-corrupt.test b/mysql-test/suite/encryption/t/innodb-force-corrupt.test new file mode 100644 index 00000000..51771f1e --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-force-corrupt.test @@ -0,0 +1,87 @@ +# +# MDEV-11759: Encryption code in MariaDB 10.1/10.2 causes compatibility problems +# + +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# Don't test under embedded +-- source include/not_embedded.inc + +call mtr.add_suppression("InnoDB: Table `test`\\.`t[13]` (has an unreadable root page|is corrupted)"); +call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=\\d+, page number=[36]\\] in file .*test.t[123]\\.ibd looks corrupted; key_version="); +call mtr.add_suppression("\\[ERROR\\] InnoDB: We detected index corruption in an InnoDB type table"); +call mtr.add_suppression("\\[ERROR\\] (mysqld|mariadbd).*: Index for table 't2' is corrupt; try to repair it"); + +SET GLOBAL innodb_file_per_table = ON; +set global innodb_compression_algorithm = 1; + +--echo # Create and populate tables to be corrupted +CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT,c char(200)) ENGINE=InnoDB encrypted=yes; +CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT,c char(200)) ENGINE=InnoDB row_format=compressed encrypted=yes; +CREATE TABLE t3 (a INT AUTO_INCREMENT PRIMARY KEY, b TEXT, c char(200)) ENGINE=InnoDB page_compressed=yes encrypted=yes; + +BEGIN; +INSERT INTO t1 (b,c) VALUES ('corrupt me','secret'); +--disable_query_log +--let $i = 100 +while ($i) +{ + INSERT INTO t1 (b,c) VALUES (REPEAT('abcabcabc', 100),'secretsecret'); + dec $i; +} +--enable_query_log + +INSERT INTO t1 (b,c) VALUES ('corrupt me','moresecretmoresecret'); +INSERT INTO t2 select * from t1; +INSERT INTO t3 select * from t1; +COMMIT; + +let INNODB_PAGE_SIZE=`select @@innodb_page_size`; +let MYSQLD_DATADIR=`select @@datadir`; + +--source include/shutdown_mysqld.inc + +--echo # Backup tables before corrupting +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t1.ibd.backup +--copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t2.ibd.backup +--copy_file $MYSQLD_DATADIR/test/t3.ibd $MYSQLD_DATADIR/test/t3.ibd.backup + +--echo # Corrupt tables + +perl; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t1.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 26, SEEK_SET) or die "seek"; +print FILE pack("H*", "c001cafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t2.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 26, SEEK_SET) or die "seek"; +print FILE pack("H*", "c001cafedeadb017"); +close FILE or die "close"; +open(FILE, "+<", "$ENV{MYSQLD_DATADIR}/test/t3.ibd") or die "open"; +binmode FILE; +seek(FILE, $ENV{'INNODB_PAGE_SIZE'} * 3 + 26, SEEK_SET) or die "seek"; +print FILE pack("H*", "c001cafedeadb017"); +close FILE or die "close"; +EOF + +--source include/start_mysqld.inc + +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT * FROM t1; +--error ER_GET_ERRMSG,ER_NOT_KEYFILE +SELECT * FROM t2; +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT * FROM t3; + +--source include/shutdown_mysqld.inc + +--echo # Restore the original tables +--move_file $MYSQLD_DATADIR/test/t1.ibd.backup $MYSQLD_DATADIR/test/t1.ibd +--move_file $MYSQLD_DATADIR/test/t2.ibd.backup $MYSQLD_DATADIR/test/t2.ibd +--move_file $MYSQLD_DATADIR/test/t3.ibd.backup $MYSQLD_DATADIR/test/t3.ibd + +--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 00000000..03a0028d --- /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 00000000..d360d5f8 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test @@ -0,0 +1,77 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# not embedded because of restarts +-- source include/not_embedded.inc + +let $encryption = `SELECT @@innodb_encrypt_tables`; +SET GLOBAL innodb_file_per_table = ON; +# zlib +set global innodb_compression_algorithm = 1; + +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 +--sorted_result +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 +--sorted_result +SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 and NAME LIKE 'enctests%'; + +--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 + +drop database enctests; diff --git a/mysql-test/suite/encryption/t/innodb-missing-key.opt b/mysql-test/suite/encryption/t/innodb-missing-key.opt new file mode 100644 index 00000000..6a8ee144 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-missing-key.opt @@ -0,0 +1,5 @@ +--innodb-encrypt-tables +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-missing-key.test b/mysql-test/suite/encryption/t/innodb-missing-key.test new file mode 100644 index 00000000..53fc820a --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-missing-key.test @@ -0,0 +1,65 @@ +--source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# embedded does not support restart +-- source include/not_embedded.inc + +# +# MDEV-11004: Unable to start (Segfault or os error 2) when encryption key missing +# + +call mtr.add_suppression("InnoDB: Table `test`\\.`t1` has an unreadable root page"); +call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t[123]\\.ibd' cannot be decrypted\\."); +call mtr.add_suppression("InnoDB: Recovery failed to read page"); +call mtr.add_suppression("InnoDB: Encrypted page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file .*test.t[12].ibd looks corrupted; key_version=1"); +call mtr.add_suppression("Table .*t1.* is corrupted"); + +--echo # Start server with keys2.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +CREATE TABLE t1(a int not null primary key auto_increment, b varchar(128)) engine=innodb ENCRYPTED=YES ENCRYPTION_KEY_ID=19; +CREATE TABLE t2(a int not null primary key auto_increment, b varchar(128)) engine=innodb ENCRYPTED=YES ENCRYPTION_KEY_ID=1; +CREATE TABLE t3(a int not null primary key auto_increment, b varchar(128)) engine=innodb ENCRYPTED=NO; +INSERT INTO t1(b) VALUES ('thisissecredmessage'); +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t1(b) SELECT b FROM t1; +INSERT INTO t2 SELECT * FROM t1; +INSERT INTO t3 SELECT * FROM t1; + +--echo +--echo # Restart server with keys3.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +-- source include/restart_mysqld.inc + +set global innodb_encryption_rotate_key_age = 1; +use test; +CREATE TABLE t4(a int not null primary key auto_increment, b varchar(128)) engine=innodb ENCRYPTED=YES ENCRYPTION_KEY_ID=1; +SELECT SLEEP(5); +SELECT COUNT(1) FROM t3; +SELECT COUNT(1) FROM t2; +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT COUNT(1) FROM t2,t1 where t2.a = t1.a; +--error ER_TABLE_CORRUPT +SELECT COUNT(1) FROM t1 where b = 'ab'; +--error ER_TABLE_CORRUPT +SELECT COUNT(1) FROM t1; + +--echo +--echo # Start server with keys2.txt +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +SELECT COUNT(1) FROM t1; +SELECT COUNT(1) FROM t2; +SELECT COUNT(1) FROM t3; + +DROP TABLE t1, t2, t3; diff --git a/mysql-test/suite/encryption/t/innodb-page_encryption-32k.opt b/mysql-test/suite/encryption/t/innodb-page_encryption-32k.opt new file mode 100644 index 00000000..a9021f6d --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-page_encryption-32k.opt @@ -0,0 +1,2 @@ +--default_storage_engine=InnoDB +--innodb-buffer-pool-size=24M diff --git a/mysql-test/suite/encryption/t/innodb-page_encryption-32k.test b/mysql-test/suite/encryption/t/innodb-page_encryption-32k.test new file mode 100644 index 00000000..03c9c93f --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-page_encryption-32k.test @@ -0,0 +1,82 @@ +--source include/no_valgrind_without_big.inc +--source include/not_embedded.inc +# Tests for setting innodb-page-size=32k; +--source include/have_innodb.inc +--source include/have_innodb_32k.inc +--source include/have_file_key_management_plugin.inc + +create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb; +create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact encrypted=yes encryption_key_id=1; +--error ER_CANT_CREATE_TABLE +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=3; +show warnings; +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=33; +create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant encrypted=yes encryption_key_id=4; + +show create table innodb_compact; +show create table innodb_dynamic; +show create table innodb_redundant; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into innodb_normal values(current_num, substring(MD5(RAND()), -150)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +set autocommit=0; +call innodb_insert_proc(5000); +commit; +set autocommit=1; + +insert into innodb_compact select * from innodb_normal; +insert into innodb_dynamic select * from innodb_normal; +insert into innodb_redundant select * from innodb_normal; + +update innodb_compact set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; + +--source include/restart_mysqld.inc + +update innodb_compact set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; + +alter table innodb_compact engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_compact; +alter table innodb_dynamic engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_dynamic; +alter table innodb_redundant engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_redundant; + +--source include/restart_mysqld.inc + +show create table innodb_compact; +show create table innodb_dynamic; +show create table innodb_redundant; + +update innodb_compact set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; + +drop procedure innodb_insert_proc; +drop table innodb_normal; +drop table innodb_compact; +drop table innodb_dynamic; +drop table innodb_redundant; diff --git a/mysql-test/suite/encryption/t/innodb-page_encryption.test b/mysql-test/suite/encryption/t/innodb-page_encryption.test new file mode 100644 index 00000000..d756f07a --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-page_encryption.test @@ -0,0 +1,152 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc + +# The test can take very long time with valgrind +--source include/not_valgrind.inc + +create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb; +show warnings; +create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact encrypted=yes encryption_key_id=1; +show warnings; +create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed encrypted=yes encryption_key_id=2; +show warnings; +--error ER_CANT_CREATE_TABLE +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=3; +show warnings; +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=33; +show warnings; +create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant encrypted=yes encryption_key_id=4; +show warnings; + +set innodb_default_encryption_key_id = 5; +create table innodb_defkey(c1 bigint not null, b char(200)) engine=innodb encrypted=yes; +show create table innodb_defkey; + +show create table innodb_compact; +show create table innodb_compressed; +show create table innodb_dynamic; +show create table innodb_redundant; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into innodb_normal values(current_num, substring(MD5(RAND()), -64)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +set autocommit=0; +call innodb_insert_proc(2000); +commit; +set autocommit=1; + +insert into innodb_compact select * from innodb_normal; +insert into innodb_compressed select * from innodb_normal; +insert into innodb_dynamic select * from innodb_normal; +insert into innodb_redundant select * from innodb_normal; +insert into innodb_defkey select * from innodb_normal; + +update innodb_normal set c1 = c1 +1; +update innodb_compact set c1 = c1 + 1; +update innodb_compressed set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +update innodb_defkey set c1 = c1 + 1; + +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_compressed where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; +select count(*) from innodb_defkey where c1 < 1500000; +select count(*) from innodb_compact t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_dynamic t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_compressed t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_redundant t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_defkey t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; + +# Note there that these variables are updated only when real I/O is done, thus they are not reliable +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; + +--source include/restart_mysqld.inc + +update innodb_normal set c1 = c1 +1; +update innodb_compact set c1 = c1 + 1; +update innodb_compressed set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +update innodb_defkey set c1 = c1 + 1; +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_compressed where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; +select count(*) from innodb_defkey where c1 < 1500000; +select count(*) from innodb_compact t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_dynamic t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_compressed t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_redundant t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_defkey t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; + +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; + +alter table innodb_compact engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_compact; +alter table innodb_compressed engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_compressed; +alter table innodb_dynamic engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_dynamic; +alter table innodb_redundant engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_redundant; + +--source include/restart_mysqld.inc + +show create table innodb_compact; +show create table innodb_compressed; +show create table innodb_dynamic; +show create table innodb_redundant; +show create table innodb_defkey; + +update innodb_normal set c1 = c1 +1; +update innodb_compact set c1 = c1 + 1; +update innodb_compressed set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_compressed where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; +select count(*) from innodb_compact t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_dynamic t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_compressed t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_redundant t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; + +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; + +drop procedure innodb_insert_proc; +drop table innodb_normal; +drop table innodb_compact; +drop table innodb_compressed; +drop table innodb_dynamic; +drop table innodb_redundant; +drop table innodb_defkey; diff --git a/mysql-test/suite/encryption/t/innodb-page_encryption_compression.test b/mysql-test/suite/encryption/t/innodb-page_encryption_compression.test new file mode 100644 index 00000000..5fe6f686 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-page_encryption_compression.test @@ -0,0 +1,73 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc +-- source include/have_file_key_management_plugin.inc + +let $innodb_compression_algorithm_orig=`SELECT @@innodb_compression_algorithm`; + +# zlib +set global innodb_compression_algorithm = 1; + +create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb page_compressed=1; +show warnings; +create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact encrypted=yes encryption_key_id=1 page_compressed=1; +show warnings; +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=2 page_compressed=1; +show warnings; + +show create table innodb_normal; +show create table innodb_compact; +show create table innodb_dynamic; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into innodb_normal values(current_num, substring(MD5(RAND()), -128)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +begin; +call innodb_insert_proc(2000); +insert into innodb_compact select * from innodb_normal; +insert into innodb_dynamic select * from innodb_normal; +commit; + +FLUSH TABLES innodb_compact FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_dynamic FOR EXPORT; UNLOCK TABLES; +select variable_value > 0 from information_schema.global_status +where variable_name = 'INNODB_NUM_PAGES_PAGE_COMPRESSED'; + +--let $restart_parameters=--innodb-encrypt-tables=OFF +--source include/restart_mysqld.inc + +# zlib +set global innodb_compression_algorithm = 1; + +alter table innodb_normal engine=innodb page_compressed=DEFAULT; +show create table innodb_normal; +alter table innodb_compact engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT page_compressed=DEFAULT; +show create table innodb_compact; +alter table innodb_dynamic engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT page_compressed=DEFAULT; +show create table innodb_dynamic; + +FLUSH TABLES innodb_normal FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_compact FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_dynamic FOR EXPORT; UNLOCK TABLES; + +select variable_value > 0 from information_schema.global_status +where variable_name = 'INNODB_NUM_PAGES_PAGE_DECOMPRESSED'; + +drop procedure innodb_insert_proc; +drop table innodb_normal; +drop table innodb_compact; +drop table innodb_dynamic; + +# reset system +--disable_query_log +EVAL SET GLOBAL innodb_compression_algorithm = $innodb_compression_algorithm_orig; +--enable_query_log diff --git a/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.opt b/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.opt new file mode 100644 index 00000000..d09b26a3 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.opt @@ -0,0 +1,3 @@ +--innodb-encrypt-log=ON +--innodb-encrypt-tables=ON + diff --git a/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.test b/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.test new file mode 100644 index 00000000..a736c729 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.test @@ -0,0 +1,82 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc +-- source include/have_file_key_management_plugin.inc + +create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb; +show warnings; +create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact encrypted=yes encryption_key_id=1; +show warnings; +create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed encrypted=yes encryption_key_id=2; +show warnings; +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=33; +show warnings; +create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant encrypted=yes encryption_key_id=4; +show warnings; + +show create table innodb_compact; +show create table innodb_compressed; +show create table innodb_dynamic; +show create table innodb_redundant; + +FLUSH TABLES innodb_normal FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_compact FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_compressed FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_dynamic FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_redundant FOR EXPORT; UNLOCK TABLES; + +select variable_value > 0 from information_schema.global_status +where variable_name = 'INNODB_NUM_PAGES_ENCRYPTED'; + +--source include/restart_mysqld.inc + +BEGIN; +INSERT INTO innodb_normal SET c1 = 1; +INSERT INTO innodb_compact SET c1 = 1; +INSERT INTO innodb_compressed SET c1 = 1; +INSERT INTO innodb_dynamic SET c1 = 1; +INSERT INTO innodb_redundant SET c1 = 1; +COMMIT; + +FLUSH TABLES innodb_normal FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_compact FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_compressed FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_dynamic FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_redundant FOR EXPORT; UNLOCK TABLES; + +select variable_name from information_schema.global_status +where variable_value > 0 and variable_name +IN ('INNODB_NUM_PAGES_ENCRYPTED','INNODB_NUM_PAGES_DECRYPTED'); + +SET GLOBAL innodb_encrypt_tables=OFF; +alter table innodb_compact engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_compact; +alter table innodb_compressed engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_compressed; +alter table innodb_dynamic engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_dynamic; +alter table innodb_redundant engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT; +show create table innodb_redundant; + +FLUSH TABLES innodb_normal FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_compact FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_compressed FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_dynamic FOR EXPORT; UNLOCK TABLES; +FLUSH TABLES innodb_redundant FOR EXPORT; UNLOCK TABLES; + +drop table innodb_normal; +drop table innodb_compact; +drop table innodb_compressed; +drop table innodb_dynamic; +drop table innodb_redundant; +# +# MDEV-8143: InnoDB: Database page corruption on disk or a failed file read +# +CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=InnoDB ENCRYPTION_KEY_ID=2 ENCRYPTED=YES; +INSERT INTO t1 VALUES (1),(2); + +--echo # Restarting server... +--source include/restart_mysqld.inc + +SELECT * FROM t1; + +DROP TABLE t1; diff --git a/mysql-test/suite/encryption/t/innodb-read-only.opt b/mysql-test/suite/encryption/t/innodb-read-only.opt new file mode 100644 index 00000000..acedb1a1 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-read-only.opt @@ -0,0 +1,3 @@ +--innodb-encrypt-tables=1 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb-read-only.test b/mysql-test/suite/encryption/t/innodb-read-only.test new file mode 100644 index 00000000..10ec8746 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-read-only.test @@ -0,0 +1,34 @@ +--source suite/encryption/include/have_file_key_management_plugin.inc +--source include/have_innodb.inc +--source include/not_embedded.inc + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +let $cnt=600; +while ($cnt) +{ + let $success=`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0`; + if ($success) + { + let $cnt=0; + } + if (!$success) + { + real_sleep 1; + dec $cnt; + } +} +if (!$success) +{ + SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION; + SHOW STATUS LIKE 'innodb_encryption%'; + -- die Timeout waiting for encryption threads +} +--echo # Success! + +# +# MDEV-11835: InnoDB: Failing assertion: free_slot != NULL on +# restarting server with encryption and read-only +# +--let $restart_parameters= --innodb-read-only=1 --innodb-encrypt-tables=1 +--source include/restart_mysqld.inc +--echo # All done diff --git a/mysql-test/suite/encryption/t/innodb-redo-badkey.combinations b/mysql-test/suite/encryption/t/innodb-redo-badkey.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-redo-badkey.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb-redo-badkey.opt b/mysql-test/suite/encryption/t/innodb-redo-badkey.opt new file mode 100644 index 00000000..0efdc8a7 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-redo-badkey.opt @@ -0,0 +1,6 @@ +--innodb-change-buffering=all +--innodb-encrypt-tables=on +--innodb-tablespaces-encryption +--innodb-encryption-threads=2 +--innodb-default-encryption-key-id=4 +--skip-innodb-fast-shutdown diff --git a/mysql-test/suite/encryption/t/innodb-redo-badkey.test b/mysql-test/suite/encryption/t/innodb-redo-badkey.test new file mode 100644 index 00000000..393ca4ad --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-redo-badkey.test @@ -0,0 +1,98 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# embedded does not support restart +-- source include/not_embedded.inc +# MDEV-20839 innodb-redo-badkey sporadically fails on buildbot with page dump +# We only require a debug build to avoid getting page dumps, making use of +# MDEV-19766: Disable page dump output in debug builds +-- source include/have_debug.inc + +call mtr.add_suppression("Plugin 'file_key_management'"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error."); +call mtr.add_suppression("InnoDB: The page \\[page id: space=[1-9][0-9]*, page number=[1-9][0-9]*\\] in file '.*test.t[1-4]\\.ibd' cannot be decrypted; key_version=1"); +call mtr.add_suppression("InnoDB: File '.*test/t[1234]\\.ibd' is corrupted"); +call mtr.add_suppression("InnoDB: Recovery failed to read page"); +call mtr.add_suppression("InnoDB: Unable to decompress .*.test.t[12]\\.ibd\\[page id: space=[1-9][0-9]*, page number=[0-9]+\\]"); +call mtr.add_suppression("InnoDB: Database page corruption on disk or a failed read of file '.*test.t[12]\\.ibd'"); +call mtr.add_suppression("InnoDB: Failed to read page .* from file '.*'"); +call mtr.add_suppression("InnoDB: OPT_PAGE_CHECKSUM mismatch"); +call mtr.add_suppression("InnoDB: Set innodb_force_recovery=1 to ignore corruption"); +call mtr.add_suppression("InnoDB: (Unable to apply log to|Discarding log for) corrupted page "); +call mtr.add_suppression("InnoDB: Plugin initialization aborted"); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed"); +# for innodb_checksum_algorithm=full_crc32 only +call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot decrypt \\[page id: space="); + +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--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 + +SET GLOBAL innodb_file_per_table = ON; + +create table t1(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes encryption_key_id=4; +create table t2(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb row_format=compressed; +create table t3(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb encrypted=yes encryption_key_id=4; +create table t4(a int not null primary key auto_increment, c char(250), b blob, index(b(10))) engine=innodb; + +begin; +--disable_query_log +--let $i = 20 +begin; +while ($i) +{ + insert into t1(c,b) values (repeat('secret1',20), repeat('secret2',6000)); + dec $i; +} +--enable_query_log + +insert into t2 select * from t1; +insert into t3 select * from t1; +insert into t4 select * from t1; +commit; + +--source ../../suite/innodb/include/no_checkpoint_start.inc + +# +# We test redo log page read at recv_read_page using +# incorrect keys from std_data/keys.txt. If checkpoint +# happens we will skip this test. If no checkpoint +# happens, InnoDB refuses to start as used +# encryption key is incorrect. +# +SET GLOBAL innodb_flush_log_at_trx_commit=1; +begin; +update t1 set c = repeat('secret3', 20); +update t2 set c = repeat('secret4', 20); +update t3 set c = repeat('secret4', 20); +update t4 set c = repeat('secret4', 20); +insert into t1 (c,b) values (repeat('secret5',20), repeat('secret6',6000)); +insert into t2 (c,b) values (repeat('secret7',20), repeat('secret8',6000)); +insert into t3 (c,b) values (repeat('secret9',20), repeat('secre10',6000)); +insert into t4 (c,b) values (repeat('secre11',20), repeat('secre12',6000)); +COMMIT; +let $cleanup= drop table t1,t2,t3,t4; +--let CLEANUP_IF_CHECKPOINT= $cleanup; +--source ../../suite/innodb/include/no_checkpoint_end.inc + +--error 1 +-- source include/start_mysqld.inc +--source include/kill_mysqld.inc + +# +# Now test with innodb-force-recovery=1 i.e. ignore corrupt pages +# + +-- let $restart_parameters=--innodb-force-recovery=1 +--error 1 +-- source include/start_mysqld.inc + +--source include/kill_mysqld.inc + +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/start_mysqld.inc + +drop table t1, t2,t3,t4; diff --git a/mysql-test/suite/encryption/t/innodb-redo-nokeys.opt b/mysql-test/suite/encryption/t/innodb-redo-nokeys.opt new file mode 100644 index 00000000..6190ad24 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-redo-nokeys.opt @@ -0,0 +1,2 @@ +--innodb-change-buffering=none +--innodb-default-encryption-key-id=20 diff --git a/mysql-test/suite/encryption/t/innodb-redo-nokeys.test b/mysql-test/suite/encryption/t/innodb-redo-nokeys.test new file mode 100644 index 00000000..52c12761 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-redo-nokeys.test @@ -0,0 +1,90 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# embedded does not support restart +-- source include/not_embedded.inc + +call mtr.add_suppression("mariadbd.*: File "); +call mtr.add_suppression("Plugin 'file_key_management' "); +call mtr.add_suppression("InnoDB: Recovery cannot access file"); +call mtr.add_suppression("InnoDB: Plugin initialization aborted"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error\\."); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); +call mtr.add_suppression("InnoDB: (Unable to apply log to|Discarding log for) corrupted page "); +call mtr.add_suppression("InnoDB: Cannot apply log to \\[page id: space=[1-9][0-9]*, page number=0\\] of corrupted file '.*test.t[1-5]\\.ibd'"); +call mtr.add_suppression("InnoDB: Failed to read page .* from file '.*'"); +call mtr.add_suppression("InnoDB: OPT_PAGE_CHECKSUM mismatch"); +call mtr.add_suppression("InnoDB: Missing FILE_CHECKPOINT"); +call mtr.add_suppression("InnoDB: Log scan aborted at LSN"); +call mtr.add_suppression("InnoDB: Set innodb_force_recovery=1 to ignore corruption"); +call mtr.add_suppression("InnoDB: Encryption key is not found for"); + +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +SET GLOBAL innodb_file_per_table = ON; + +create table t1(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes encryption_key_id=20; +create table t2(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb row_format=compressed encrypted=yes; +create table t3(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb encrypted=yes encryption_key_id=20; +create table t4(a int not null primary key auto_increment, c char(200), b blob, index(b(10))) engine=innodb encrypted=yes; + +begin; +--disable_query_log +--let $i = 20 +begin; +while ($i) +{ + insert into t1(c,b) values (repeat('secret1',20), repeat('secret2',6000)); + dec $i; +} +--enable_query_log + +insert into t2 select * from t1; +insert into t3 select * from t1; +insert into t4 select * from t1; +commit; + +--source ../../suite/innodb/include/no_checkpoint_start.inc +# +# We test redo log page read at recv_read_page using +# keys that are not in std_data/keys.txt. If checkpoint +# happens we will skip this test. If no checkpoint +# happens, InnoDB refuses to start as used +# encryption key is not found. +# +CREATE TABLE t5 (a VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES; +SET GLOBAL innodb_flush_log_at_trx_commit=1; +begin; +update t1 set c = repeat('secret3', 20); +update t2 set c = repeat('secret4', 20); +update t3 set c = repeat('secret4', 20); +update t4 set c = repeat('secret4', 20); +insert into t1 (c,b) values (repeat('secret5',20), repeat('secret6',6000)); +insert into t2 (c,b) values (repeat('secret7',20), repeat('secret8',6000)); +insert into t3 (c,b) values (repeat('secret9',20), repeat('secre10',6000)); +insert into t4 (c,b) values (repeat('secre11',20), repeat('secre12',6000)); +INSERT INTO t5 VALUES ('foo'),('bar'); +COMMIT; + +let $cleanup= drop table t1,t2,t3,t4,t5; +--let CLEANUP_IF_CHECKPOINT= $cleanup; +--source ../../suite/innodb/include/no_checkpoint_end.inc + +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys-not-found.txt +-- source include/start_mysqld.inc +SELECT * FROM INFORMATION_SCHEMA.ENGINES +WHERE engine = 'innodb' +AND support IN ('YES', 'DEFAULT', 'ENABLED'); + +let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err; +let SEARCH_PATTERN = \[ERROR\] InnoDB: Encryption key is not found for .*test.t1.ibd; +--source include/search_pattern_in_file.inc + +# +# In above server does start but InnoDB refuses to start +# thus we need to restart server with correct key file +# +-- let $restart_parameters=--file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +drop table t1,t2,t3,t4,t5; diff --git a/mysql-test/suite/encryption/t/innodb-remove-encryption.test b/mysql-test/suite/encryption/t/innodb-remove-encryption.test new file mode 100644 index 00000000..8ff5fd63 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-remove-encryption.test @@ -0,0 +1,63 @@ +--source include/have_innodb.inc +# Test uses restart +--source include/not_embedded.inc +--source filekeys_plugin.inc +--source include/innodb_undo_tablespaces.inc + +# +# MDEV-15566: System tablespace does not easily key rotate to unencrypted +# + +set global innodb_file_per_table=OFF; + +call mtr.add_suppression("(mysqld|mariadbd).*: file-key-management-filename is not set"); +call mtr.add_suppression("Plugin 'file_key_management' init function returned error."); +call mtr.add_suppression("Plugin 'file_key_management' registration as a ENCRYPTION failed."); +flush tables; + +create table t1(a int not null primary key, b char(200)) engine=innodb; + +--echo +--echo # Restart server with encryption +-- let $restart_parameters=--plugin-load-add=file_key_management --loose-file-key-management --loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys.txt --file-key-management-encryption-algorithm=aes_cbc --innodb-encrypt-tables=ON --innodb-encryption-threads=4 --innodb-tablespaces-encryption --innodb-encryption-rotate-key-age=15 +-- source include/restart_mysqld.inc + +--echo # Wait until encryption threads have encrypted all tablespaces + +--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) = $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 AND ROTATING_OR_FLUSHING = 0; +--source include/wait_condition.inc + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +--echo # Success! + +SELECT * from t1; + +--echo # Now turn off encryption and wait for threads to decrypt all tablespaces +SET GLOBAL innodb_encrypt_tables = off; + +--let $wait_condition=SELECT COUNT(*) = $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND ROTATING_OR_FLUSHING = 0; +--source include/wait_condition.inc + +--sorted_result +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; + +--echo # Success! + +--echo +--echo # Restart server with no encryption setup, there should be no crashes +--let $restart_parameters=--skip-file-key-management --innodb-encrypt-tables=OFF --innodb-encryption-threads=0 --innodb-tablespaces-encryption +-- source include/restart_mysqld.inc + +--sorted_result +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; + +SELECT * from t1; +DROP TABLE t1; + diff --git a/mysql-test/suite/encryption/t/innodb-spatial-index.opt b/mysql-test/suite/encryption/t/innodb-spatial-index.opt new file mode 100644 index 00000000..d1f1bb9c --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-spatial-index.opt @@ -0,0 +1,4 @@ +--innodb-encrypt-tables +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb-spatial-index.test b/mysql-test/suite/encryption/t/innodb-spatial-index.test new file mode 100644 index 00000000..2caffb14 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb-spatial-index.test @@ -0,0 +1,94 @@ +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc +--source include/innodb_checksum_algorithm.inc + +# +# MDEV-11974: MariaDB 10.2 encryption does not support spatial indexes +# + +# +# +# +let $checksum_algorithm = `SELECT @@innodb_checksum_algorithm`; +let $error_code = ER_CANT_CREATE_TABLE, ER_ILLEGAL_HA_CREATE_OPTION; +if ($checksum_algorithm == "full_crc32") +{ + let $error_code = 0; +} +if ($checksum_algorithm == "strict_full_crc32") +{ + let $error_code = 0; +} + +--error $error_code +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, +c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB +ENCRYPTED=YES; + +if (!$error_code) { +INSERT INTO t1(c, coordinate) values('mysql', ST_GeomFromText('POINT(903994614 180726515)')); +--source include/restart_mysqld.inc +INSERT INTO t1(c, coordinate) values('mariadb', ST_GeomFromText('POINT(903994614 180726515)')); +DROP TABLE t1; +} + +# +# (2) Alter table +# +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, +c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB; +--error $error_code +ALTER TABLE t1 ENCRYPTED=YES; +DROP TABLE t1; + +# +# Index creation +# +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, +c VARCHAR(256), coordinate POINT NOT NULL) +PAGE_COMPRESSED=YES, ENCRYPTED=YES ENGINE=INNODB; +# FIXME: MDEV-13851 Encrypted table refuses some form of ALGORITHM=COPY, +# but allows rebuild by FORCE +--error $error_code +ALTER TABLE t1 ADD SPATIAL INDEX b1(coordinate), ALGORITHM=COPY; +--error $error_code +ALTER TABLE t1 ADD SPATIAL INDEX b2(coordinate), FORCE, ALGORITHM=INPLACE; +--error $error_code +ALTER TABLE t1 ADD SPATIAL INDEX(coordinate); +--error $error_code +CREATE SPATIAL INDEX b3 on t1(coordinate); +DROP TABLE t1; + +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, +c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=DEFAULT ENGINE=INNODB; +CREATE SPATIAL INDEX b on t1(coordinate); +INSERT INTO t1 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); +ALTER TABLE t1 DROP INDEX b; +INSERT INTO t1 values(2, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); +ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate); +INSERT INTO t1 values(3, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); +DROP TABLE t1; +# +# (3) Default encryption should still work +# + +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, +c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB; +CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, +c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB PAGE_COMPRESSED=YES; + +INSERT INTO t1 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); +INSERT INTO t2 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); + +--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 + +--echo # Success! + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION > 0; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_freed.opt b/mysql-test/suite/encryption/t/innodb_encrypt_freed.opt new file mode 100644 index 00000000..221e3836 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_freed.opt @@ -0,0 +1,4 @@ +--innodb-encrypt-tables +--innodb-encrypt-log +--innodb-encryption-threads=1 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_freed.test b/mysql-test/suite/encryption/t/innodb_encrypt_freed.test new file mode 100644 index 00000000..7ba0b5a2 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_freed.test @@ -0,0 +1,118 @@ +--source include/have_innodb.inc +--source include/have_example_key_management_plugin.inc +--source include/have_debug.inc +--source include/not_embedded.inc +--source include/innodb_undo_tablespaces.inc + +SHOW VARIABLES LIKE 'innodb_encrypt%'; + +SET GLOBAL innodb_encrypt_tables = ON; + +CREATE TABLE t1(f1 BIGINT PRIMARY KEY, f2 int not null, + f3 int not null, index(f1), index idx_1(f2), + index(f2, f3)) ENGINE=InnoDB; +--let $tables_count= `select count(*) + 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +CREATE TABLE t2 (f1 int not null)engine=innodb; +let $restart_parameters="--debug=d,ib_log_checkpoint_avoid"; +--source include/restart_mysqld.inc + +# Stop the purge +connect(con1,localhost,root,,,); +begin; +insert into t2 values(1); + +connection default; + +--let $tables_count= `select count(*) + 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +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(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +--source include/wait_condition.inc +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; + +# Free the index `idx_1` +alter table t1 drop index idx_1; + +set global innodb_encrypt_tables = ON; + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +disconnect con1; +let $shutdown_timeout=0; +--source include/restart_mysqld.inc + +drop table t1, t2; + +# +# +CREATE TABLE t1(f1 BIGINT PRIMARY KEY, f2 int not null, + f3 int not null, index(f1), index idx_1(f2), + index(f2, f3)) ENGINE=InnoDB; +--let $tables_count= `select count(*) + 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +CREATE TABLE t2 (f1 int not null)engine=innodb; +--source include/restart_mysqld.inc + +# Stop the purge +connect(con1,localhost,root,,,); +begin; +insert into t2 values(1); + +connection default; + +--let $tables_count= `select count(*) + 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +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(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--source include/wait_condition.inc +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; + +# Free the index `idx_1` +alter table t1 drop index idx_1; + +disconnect con1; +--source include/restart_mysqld.inc + +# Stop the purge +connect(con1,localhost,root,,,); +begin; +insert into t2 values(1); + +connection default; +set global innodb_encrypt_tables = ON; + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +disconnect con1; +drop table t2, t1; diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_key_rotation_age.opt b/mysql-test/suite/encryption/t/innodb_encrypt_key_rotation_age.opt new file mode 100644 index 00000000..6fa06402 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_key_rotation_age.opt @@ -0,0 +1,2 @@ +--innodb-tablespaces-encryption +--innodb_encrypt_tables=ON diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_key_rotation_age.test b/mysql-test/suite/encryption/t/innodb_encrypt_key_rotation_age.test new file mode 100644 index 00000000..c96d33aa --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_key_rotation_age.test @@ -0,0 +1,89 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc +-- source include/have_example_key_management_plugin.inc +-- source include/innodb_undo_tablespaces.inc + +CREATE TABLE t1 (f1 INT, f2 VARCHAR(256))engine=innodb; +INSERT INTO t1 VALUES(1, 'MariaDB'), (2, 'Robot'), (3, 'Science'); +INSERT INTO t1 SELECT * FROM t1; + +CREATE TABLE t2(f1 INT, f2 VARCHAR(256))engine=innodb; +INSERT INTO t2 SELECT * FROM t1; + +CREATE TABLE t3(f1 INT, f2 VARCHAR(256))engine=innodb encrypted=yes; +INSERT INTO t3 SELECT * FROM t1; + +--echo # Restart the server with encryption + +let $restart_noprint=2; +let $restart_parameters= --innodb_encryption_threads=5 --innodb_encryption_rotate_key_age=16384; +--source include/restart_mysqld.inc + +--echo # Wait until encryption threads have encrypted all tablespaces + +--let $tables_count= `select count(*) + 1 + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +--echo # Restart the server with innodb_encryption_rotate_key_age= 0 + +let $restart_parameters= --innodb_encryption_threads=1 --innodb_encryption_rotate_key_age=0; + +--source include/restart_mysqld.inc + +create table t4 (f1 int not null)engine=innodb encrypted=NO; + +--echo # Wait until encryption threads have encrypted all tablespaces + +--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +--echo # Disable encryption when innodb_encryption_rotate_key_age is 0 +set global innodb_encrypt_tables = OFF; + +--echo # Wait until encryption threads to decrypt all unencrypted tablespaces + +--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND ROTATING_OR_FLUSHING = 0; +--source include/wait_condition.inc + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--echo # Display only encrypted create tables (t3) +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +--echo # Enable encryption when innodb_encryption_rotate_key_age is 0 +set global innodb_encrypt_tables = ON; + +--echo # Wait until encryption threads to encrypt all unencrypted tablespaces + +--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces from information_schema.tables where engine = 'InnoDB'` +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--echo # Display only unencrypted create tables (t4) +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; + +--let $restart_parameters= +-- source include/restart_mysqld.inc + +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +DROP TABLE t4, t3, t2, t1; diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_log.opt b/mysql-test/suite/encryption/t/innodb_encrypt_log.opt new file mode 100644 index 00000000..24046fe7 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_log.opt @@ -0,0 +1,6 @@ +--innodb-encrypt-log=ON +--innodb-encrypt-tables=FORCE +--plugin-load-add=$FILE_KEY_MANAGEMENT_SO +--loose-file-key-management +--loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/logkey.txt +--file-key-management-encryption-algorithm=aes_cbc diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_log.test b/mysql-test/suite/encryption/t/innodb_encrypt_log.test new file mode 100644 index 00000000..5448a606 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_log.test @@ -0,0 +1,88 @@ +-- source include/have_innodb.inc +-- source include/not_embedded.inc +-- source filekeys_plugin.inc + +--echo # +--echo # MDEV-9011: Redo log encryption does not work +--echo # + +--echo # +--echo # MDEV-9422 Encrypted redo log checksum errors +--echo # on restart after killing busy server instance +--echo # + +--let $MYSQLD_DATADIR=`select @@datadir` + +CREATE TABLE t0 ( + pk bigint auto_increment, + col_int int, + col_int_key int, + col_char char(12), + col_char_key char(12), + primary key (pk), + key (col_int_key), + key (col_char_key) +) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=1; +CREATE TEMPORARY TABLE t LIKE t0; + +INSERT INTO t VALUES +(NULL,1,1,'private','secret'),(NULL,2,2,'sacred','success'), +(NULL,3,3,'story','secure'),(NULL,4,4,'security','sacrament'); + +# Force a redo log flush at the next commit. +SET GLOBAL innodb_flush_log_at_trx_commit=1; +INSERT INTO t0 + SELECT NULL, t1.col_int, t1.col_int_key, t1.col_char, t1.col_char_key + FROM t t1, t t2, t t3, t t4, t t5; + +--source include/kill_mysqld.inc + +--let SEARCH_RANGE = 10000000 +--let SEARCH_PATTERN=private|secret|sacr(ed|ament)|success|story|secur(e|ity) + +--echo # ibdata1 expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/ibdata1 +-- source include/search_pattern_in_file.inc +--echo # t0.ibd expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t0.ibd +-- source include/search_pattern_in_file.inc +--echo # ib_logfile0 expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/ib_logfile0 +-- source include/search_pattern_in_file.inc + +--echo # Restart without redo log encryption +-- let $restart_parameters=--skip-innodb-encrypt-log +-- source include/start_mysqld.inc + +SELECT COUNT(*) FROM t0; +CHECK TABLE t0; +# Force a redo log flush at the next commit. +SET GLOBAL innodb_flush_log_at_trx_commit=1; +# If we tested with UPDATE, we would get clear-text redo log for +# encrypted undo log written with the old secret values. +INSERT INTO t0 VALUES(NULL, 5, 5, 'public', 'gossip'); + +--source include/kill_mysqld.inc + +--echo # ib_logfile0 expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/ib_logfile0 +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=(public|gossip).* +--echo # ib_logfile0 expecting FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/ib_logfile0 +-- source include/search_pattern_in_file.inc + +--let SEARCH_PATTERN=private|secret|sacr(ed|ament)|success|story|secur(e|ity)|public|gossip +--echo # ibdata1 expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/ibdata1 +-- source include/search_pattern_in_file.inc +--echo # t0.ibd expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t0.ibd +-- source include/search_pattern_in_file.inc + +--let $restart_parameters= +--source include/start_mysqld.inc + +SELECT COUNT(*) FROM t0; +CHECK TABLE t0; +DROP TABLE t0; diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_log_corruption.opt b/mysql-test/suite/encryption/t/innodb_encrypt_log_corruption.opt new file mode 100644 index 00000000..ae20d676 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_log_corruption.opt @@ -0,0 +1,6 @@ +--innodb-encrypt-log=ON +--plugin-load-add=$FILE_KEY_MANAGEMENT_SO +--loose-file-key-management +--loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/logkey.txt +--file-key-management-encryption-algorithm=aes_cbc +--innodb-log-file-size=5242880 diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_log_corruption.test b/mysql-test/suite/encryption/t/innodb_encrypt_log_corruption.test new file mode 100644 index 00000000..f1642e83 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_log_corruption.test @@ -0,0 +1,15 @@ +--let $no_cleanup=1 +--source ../../innodb/t/log_corruption.test + +SELECT COUNT(*) `1` FROM INFORMATION_SCHEMA.ENGINES WHERE engine='innodb' +AND support IN ('YES', 'DEFAULT', 'ENABLED'); +--source include/shutdown_mysqld.inc +--let SEARCH_PATTERN= InnoDB: Encrypting redo log +--source include/search_pattern_in_file.inc + +--let $restart_parameters= +--source include/start_mysqld.inc + +--list_files $bugdir +--remove_files_wildcard $bugdir +--rmdir $bugdir diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.combinations b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.combinations new file mode 100644 index 00000000..72938059 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.opt b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.opt new file mode 100644 index 00000000..70797302 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.opt @@ -0,0 +1,2 @@ +--innodb_buffer_pool_size=5M +--innodb_encrypt_temporary_tables=1 diff --git a/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.test b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.test new file mode 100644 index 00000000..d99a55b9 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encrypt_temporary_tables.test @@ -0,0 +1,24 @@ +--source include/have_sequence.inc +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc + +SELECT variable_value into @old_encrypted FROM information_schema.global_status +WHERE variable_name = 'innodb_encryption_n_temp_blocks_encrypted'; + +SELECT variable_value into @old_decrypted FROM information_schema.global_status +WHERE variable_name = 'innodb_encryption_n_temp_blocks_decrypted'; + +CREATE TEMPORARY TABLE t1(f1 CHAR(200), f2 CHAR(200)) ENGINE=InnoDB; +INSERT INTO t1 (f1,f2) SELECT '', '' FROM seq_1_to_8192; + +CREATE TEMPORARY TABLE t2(f1 CHAR(100), f2 CHAR(200), f3 CHAR(200))ENGINE=InnoDB; +INSERT INTO t2 (f1,f2,f3) SELECT '', '', '' FROM seq_1_to_8192; + +SELECT COUNT(*) FROM t1; +SELECT COUNT(*) FROM t2; + +SELECT variable_value > @old_encrypted FROM information_schema.global_status +WHERE variable_name = 'innodb_encryption_n_temp_blocks_encrypted'; + +SELECT variable_value > @old_decrypted FROM information_schema.global_status +WHERE variable_name = 'innodb_encryption_n_temp_blocks_decrypted'; diff --git a/mysql-test/suite/encryption/t/innodb_encryption-page-compression.opt b/mysql-test/suite/encryption/t/innodb_encryption-page-compression.opt new file mode 100644 index 00000000..0b7319dc --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption-page-compression.opt @@ -0,0 +1,7 @@ +--aria-encrypt-tables=ON +--encrypt-tmp-disk-tables=ON +--innodb-encrypt-tables=ON +--innodb-encrypt-log=ON +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb_encryption-page-compression.test b/mysql-test/suite/encryption/t/innodb_encryption-page-compression.test new file mode 100644 index 00000000..57c87212 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption-page-compression.test @@ -0,0 +1,146 @@ +-- source include/have_innodb.inc +-- source include/have_example_key_management_plugin.inc +-- source include/not_embedded.inc +# This test is too slow for valgrind and causes innnodb semaphores to time out +-- source include/not_valgrind.inc + +let $innodb_encrypt_tables_orig = `SELECT @@innodb_encrypt_tables`; +let $innodb_encryption_threads_orig = `SELECT @@innodb_encryption_threads`; + +SET GLOBAL innodb_encryption_threads = 4; + +# zlib +set global innodb_compression_algorithm = 1; + +create table innodb_normal (c1 int, b char(20)) engine=innodb; +show warnings; +create table innodb_page_compressed1 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=1; +show warnings; +show create table innodb_page_compressed1; +create table innodb_page_compressed2 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=2; +show warnings; +show create table innodb_page_compressed2; +create table innodb_page_compressed3 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=3; +show warnings; +show create table innodb_page_compressed3; +create table innodb_page_compressed4 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=4; +show warnings; +show create table innodb_page_compressed4; +create table innodb_page_compressed5 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=5; +show warnings; +show create table innodb_page_compressed5; +create table innodb_page_compressed6 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=6; +show warnings; +show create table innodb_page_compressed6; +create table innodb_page_compressed7 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=7; +show warnings; +show create table innodb_page_compressed7; +create table innodb_page_compressed8 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=8; +show warnings; +show create table innodb_page_compressed8; +create table innodb_page_compressed9 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=9; +show warnings; +show create table innodb_page_compressed9; +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into innodb_normal values(current_num,'testing..'); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +begin; +call innodb_insert_proc(2000); +insert into innodb_page_compressed1 select * from innodb_normal; +insert into innodb_page_compressed2 select * from innodb_normal; +insert into innodb_page_compressed3 select * from innodb_normal; +insert into innodb_page_compressed4 select * from innodb_normal; +insert into innodb_page_compressed5 select * from innodb_normal; +insert into innodb_page_compressed6 select * from innodb_normal; +insert into innodb_page_compressed7 select * from innodb_normal; +insert into innodb_page_compressed8 select * from innodb_normal; +insert into innodb_page_compressed9 select * from innodb_normal; +commit; + +select count(*) from innodb_page_compressed1 where c1 < 500000; +select count(*) from innodb_page_compressed2 where c1 < 500000; +select count(*) from innodb_page_compressed3 where c1 < 500000; +select count(*) from innodb_page_compressed4 where c1 < 500000; +select count(*) from innodb_page_compressed5 where c1 < 500000; +select count(*) from innodb_page_compressed6 where c1 < 500000; +select count(*) from innodb_page_compressed7 where c1 < 500000; +select count(*) from innodb_page_compressed8 where c1 < 500000; +select count(*) from innodb_page_compressed9 where c1 < 500000; + +flush tables innodb_page_compressed1, innodb_page_compressed2, +innodb_page_compressed3, innodb_page_compressed4, +innodb_page_compressed5, innodb_page_compressed6, +innodb_page_compressed7, innodb_page_compressed8, +innodb_page_compressed9 for export; + +unlock tables; + +--echo # Wait until dirty pages are compressed and encrypted +let $wait_condition= select variable_value > 0 from information_schema.global_status where variable_name = 'INNODB_NUM_PAGES_PAGE_COMPRESSED'; +--source include/wait_condition.inc +let $wait_condition= select variable_value > 0 from information_schema.global_status where variable_name = 'INNODB_NUM_PAGES_ENCRYPTED'; +--source include/wait_condition.inc + +SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed'; + +--source include/restart_mysqld.inc + +SET GLOBAL innodb_encryption_threads = 4; +SET GLOBAL innodb_encrypt_tables = off; + +update innodb_page_compressed1 set c1 = c1 + 1; +update innodb_page_compressed2 set c1 = c1 + 1; +update innodb_page_compressed3 set c1 = c1 + 1; +update innodb_page_compressed4 set c1 = c1 + 1; +update innodb_page_compressed5 set c1 = c1 + 1; +update innodb_page_compressed6 set c1 = c1 + 1; +update innodb_page_compressed7 set c1 = c1 + 1; +update innodb_page_compressed8 set c1 = c1 + 1; +update innodb_page_compressed9 set c1 = c1 + 1; + +flush tables innodb_page_compressed1, innodb_page_compressed2, +innodb_page_compressed3, innodb_page_compressed4, +innodb_page_compressed5, innodb_page_compressed6, +innodb_page_compressed7, innodb_page_compressed8, +innodb_page_compressed9 for export; + +unlock tables; + +--echo # Wait until dirty pages are compressed and encrypted 2 +let $wait_condition= select variable_value > 0 from information_schema.global_status where variable_name = 'INNODB_NUM_PAGES_PAGE_COMPRESSED'; +--source include/wait_condition.inc +let $wait_condition= select variable_value > 0 from information_schema.global_status where variable_name = 'INNODB_NUM_PAGES_DECRYPTED'; +--source include/wait_condition.inc + +SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; +SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed'; +SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_decompressed'; + +drop procedure innodb_insert_proc; +drop table innodb_normal; +drop table innodb_page_compressed1; +drop table innodb_page_compressed2; +drop table innodb_page_compressed3; +drop table innodb_page_compressed4; +drop table innodb_page_compressed5; +drop table innodb_page_compressed6; +drop table innodb_page_compressed7; +drop table innodb_page_compressed8; +drop table innodb_page_compressed9; + +# reset system +--disable_query_log +EVAL SET GLOBAL innodb_encrypt_tables = $innodb_encrypt_tables_orig; +EVAL SET GLOBAL innodb_encryption_threads = $innodb_encryption_threads_orig; +--enable_query_log diff --git a/mysql-test/suite/encryption/t/innodb_encryption.opt b/mysql-test/suite/encryption/t/innodb_encryption.opt new file mode 100644 index 00000000..0f3634ac --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption.opt @@ -0,0 +1,8 @@ +--aria-encrypt-tables +--encrypt-tmp-disk-tables +--innodb-encrypt-tables +--innodb-encrypt-log +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption + diff --git a/mysql-test/suite/encryption/t/innodb_encryption.test b/mysql-test/suite/encryption/t/innodb_encryption.test new file mode 100644 index 00000000..2b0b2b8d --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption.test @@ -0,0 +1,98 @@ +# +# +# +-- source include/have_innodb.inc +-- source include/have_example_key_management_plugin.inc +-- source include/innodb_undo_tablespaces.inc + +# embedded does not support restart +-- source include/not_embedded.inc + +SET @start_global_value = @@global.innodb_encryption_threads; + +SHOW VARIABLES LIKE 'innodb_encrypt%'; + +SET GLOBAL innodb_encrypt_tables = ON; + +--let $tables_count= `select count(*) + @@global.innodb_undo_tablespaces + 1 from information_schema.tables where engine = 'InnoDB'` + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; + +--echo # Success! + +--echo # Now turn off encryption and wait for threads to decrypt everything +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(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; + +--echo # Success! + +--echo # Shutdown innodb_encryption_threads +SET GLOBAL innodb_encryption_threads=0; + +--echo # Turn on encryption +--echo # since threads are off tables should remain unencrypted +SET GLOBAL innodb_encrypt_tables = on; + +--echo # Wait 15s to check that nothing gets encrypted +--let $wait_timeout= 15 +--let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; + +--echo # Success! + +--echo # Startup innodb_encryption_threads +SET GLOBAL innodb_encryption_threads=@start_global_value; + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; + +--echo # Success! +--echo # Restart mysqld --innodb_encrypt_tables=0 --innodb_encryption_threads=0 +-- let $restart_parameters=--innodb_encrypt_tables=0 --innodb_encryption_threads=0 +-- source include/restart_mysqld.inc + +SHOW VARIABLES LIKE 'innodb_encrypt%'; + +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; +--sorted_result +SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 +AND NAME NOT LIKE 'innodb_undo%' AND NAME NOT LIKE 'mysql/innodb_%_stats' AND NAME NOT LIKE 'mysql/transaction_registry'; diff --git a/mysql-test/suite/encryption/t/innodb_encryption_discard_import.opt b/mysql-test/suite/encryption/t/innodb_encryption_discard_import.opt new file mode 100644 index 00000000..9fe990f7 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_discard_import.opt @@ -0,0 +1,7 @@ +--innodb-encrypt-tables=ON +--innodb-encrypt-log=ON +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption +--innodb-max-dirty-pages-pct_lwm=0 +--innodb-max-dirty-pages-pct=0.001 diff --git a/mysql-test/suite/encryption/t/innodb_encryption_discard_import.test b/mysql-test/suite/encryption/t/innodb_encryption_discard_import.test new file mode 100644 index 00000000..22755571 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_discard_import.test @@ -0,0 +1,154 @@ +-- source include/have_innodb.inc +-- source include/have_example_key_management_plugin.inc +-- source include/not_valgrind.inc +-- source include/not_embedded.inc + +let MYSQLD_DATADIR = `SELECT @@datadir`; + +--let SEARCH_RANGE = 10000000 +--let $id = `SELECT RAND()` +--let t1_IBD = $MYSQLD_DATADIR/test/t1.ibd +--let t2_IBD = $MYSQLD_DATADIR/test/t2.ibd +--let t3_IBD = $MYSQLD_DATADIR/test/t3.ibd + +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; +CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB row_format=compressed encrypted=yes; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into t1 values (current_num,repeat('foobar',42)); + insert into t2 values (current_num,repeat('temp', 42)); + insert into t3 values (current_num,repeat('barfoo',42)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +set autocommit=0; +call innodb_insert_proc(10000); +commit; +set autocommit=1; + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND ROTATING_OR_FLUSHING <> 0 +--source include/wait_condition.inc + +# shutdown so that grep is safe +--source include/shutdown_mysqld.inc + +--echo # tablespaces should be now encrypted +--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 + +--source include/start_mysqld.inc +let MYSQLD_DATADIR =`SELECT @@datadir`; + +--list_files $MYSQLD_DATADIR/test +FLUSH TABLES t1, t2, t3 FOR EXPORT; +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1","t2","t3"); +EOF +--list_files $MYSQLD_DATADIR/test +UNLOCK TABLES; + +ALTER TABLE t1 DISCARD TABLESPACE; +ALTER TABLE t2 DISCARD TABLESPACE; +ALTER TABLE t3 DISCARD TABLESPACE; + +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1","t2","t3"); +ib_restore_tablespaces("test", "t1","t2","t3"); +EOF + +ALTER TABLE t1 IMPORT TABLESPACE; +SELECT COUNT(1) FROM t1; +ALTER TABLE t2 IMPORT TABLESPACE; +SELECT COUNT(1) FROM t2; +ALTER TABLE t3 IMPORT TABLESPACE; +SELECT COUNT(1) FROM t3; + +# shutdown so that grep is safe +--source include/shutdown_mysqld.inc + +--echo # tablespaces should remain encrypted after import +--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 +--echo # t3 ... on expecting NOT FOUND +--let SEARCH_PATTERN=barfoo +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc + +--source include/start_mysqld.inc + +ALTER TABLE t1 ENGINE InnoDB; +SHOW CREATE TABLE t1; +ALTER TABLE t2 ENGINE InnoDB; +SHOW CREATE TABLE t2; +ALTER TABLE t3 ENGINE InnoDB; +SHOW CREATE TABLE t3; + +--echo # Restarting server +-- source include/restart_mysqld.inc +--echo # Done restarting server + +--echo # Verify that tables are still usable +SELECT COUNT(1) FROM t1; +SELECT COUNT(1) FROM t2; +SELECT COUNT(1) FROM t3; + +# shutdown so that grep is safe +--source include/shutdown_mysqld.inc + +--echo # Tablespaces should be encrypted after restart +--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 +--echo # t3 ... on expecting NOT FOUND +--let SEARCH_PATTERN=barfoo +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc + +--source include/start_mysqld.inc + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND ROTATING_OR_FLUSHING <> 0 +--source include/wait_condition.inc + +--echo # Success! +--echo # Restart mysqld --innodb_encrypt_tables=0 --innodb_encryption_threads=0 +-- let $restart_parameters=--innodb_encrypt_tables=0 --innodb_encryption_threads=0 +-- source include/restart_mysqld.inc + +DROP PROCEDURE innodb_insert_proc; +DROP TABLE t1, t2, t3; diff --git a/mysql-test/suite/encryption/t/innodb_encryption_filekeys.opt b/mysql-test/suite/encryption/t/innodb_encryption_filekeys.opt new file mode 100644 index 00000000..7d3f2da7 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_filekeys.opt @@ -0,0 +1 @@ +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb_encryption_filekeys.test b/mysql-test/suite/encryption/t/innodb_encryption_filekeys.test new file mode 100644 index 00000000..03447bbc --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_filekeys.test @@ -0,0 +1,133 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc + +--disable_query_log +let $encrypt_tables = `SELECT @@innodb_encrypt_tables`; +let $threads = `SELECT @@innodb_encryption_threads`; +let $key_id = `SELECT @@innodb_default_encryption_key_id`; +--enable_query_log + +SET GLOBAL innodb_encrypt_tables = OFF; +SET GLOBAL innodb_encryption_threads = 4; + +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; +SHOW CREATE TABLE t1; +CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES; +CREATE TABLE t3 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=NO; +CREATE TABLE t4 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; + +--disable_warnings +--disable_query_log +begin; +let $i = 10; +while ($i) +{ +INSERT INTO t1 values(NULL, substring(MD5(RAND()), -128)); +dec $i; +} + +INSERT INTO t2 select * from t1; +INSERT INTO t3 select * from t1; +INSERT INTO t4 select * from t1; +commit; +--enable_warnings +--enable_query_log + +SET GLOBAL innodb_encrypt_tables = on; + +--echo # Wait max 10 min for key encryption threads to encrypt required all spaces +let $cnt=600; +while ($cnt) +{ + let $success=`SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0`; + if ($success) + { + let $cnt=0; + } + if (!$success) + { + real_sleep 1; + dec $cnt; + } +} +if (!$success) +{ + SELECT NAME,ENCRYPTION_SCHEME,MIN_KEY_VERSION, ROTATING_OR_FLUSHING FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION; + SHOW STATUS LIKE 'innodb_encryption%'; + -- die Timeout waiting for encryption threads +} +--echo # Success! + +SELECT COUNT(1) FROM t1; +SELECT COUNT(1) FROM t2; +SELECT COUNT(1) FROM t3; +SELECT COUNT(1) FROM t4; + +SET GLOBAL innodb_encrypt_tables = off; + +--echo # Wait max 10 min for key encryption threads to decrypt all required spaces +let $cnt=600; +while ($cnt) +{ + let $success=`SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0`; + if ($success) + { + let $cnt=0; + } + if (!$success) + { + real_sleep 1; + dec $cnt; + } +} +if (!$success) +{ + SELECT NAME,ENCRYPTION_SCHEME,MIN_KEY_VERSION, ROTATING_OR_FLUSHING FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION; + SHOW STATUS LIKE 'innodb_encryption%'; + -- die Timeout waiting for encryption threads +} +--echo # Success! + +SET GLOBAL innodb_encrypt_tables = ON; +set GLOBAL innodb_default_encryption_key_id=4; +CREATE TABLE t5 (pk INT PRIMARY KEY AUTO_INCREMENT, c VARCHAR(256)) ENGINE=INNODB; +SHOW CREATE TABLE t5; +INSERT INTO t5 select * from t1; + +--echo # Wait max 10 min for key encryption threads to encrypt required all spaces +let $cnt=600; +while ($cnt) +{ + let $success=`SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0`; + if ($success) + { + let $cnt=0; + } + if (!$success) + { + real_sleep 1; + dec $cnt; + } +} +if (!$success) +{ + SELECT NAME,ENCRYPTION_SCHEME,MIN_KEY_VERSION, ROTATING_OR_FLUSHING FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION; + SHOW STATUS LIKE 'innodb_encryption%'; + -- die Timeout waiting for encryption threads +} +--echo # Success! + +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; + +drop table t1,t2,t3,t4, t5; + +# reset system +--disable_query_log +EVAL SET GLOBAL innodb_encrypt_tables = $encrypt_tables; +EVAL SET GLOBAL innodb_encryption_threads = $threads; +EVAL set GLOBAL innodb_default_encryption_key_id = $key_id; +--enable_query_log diff --git a/mysql-test/suite/encryption/t/innodb_encryption_is.opt b/mysql-test/suite/encryption/t/innodb_encryption_is.opt new file mode 100644 index 00000000..26c70684 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_is.opt @@ -0,0 +1 @@ +--loose-innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb_encryption_is.test b/mysql-test/suite/encryption/t/innodb_encryption_is.test new file mode 100644 index 00000000..9e15cebb --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_is.test @@ -0,0 +1,18 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc + +CREATE TABLE t1 (c VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=1; +CREATE TABLE t2 (c VARCHAR(8)) ENGINE=InnoDB ENCRYPTED=YES ENCRYPTION_KEY_ID=2; +INSERT INTO t1 VALUES ('foobar'); +INSERT INTO t2 VALUES ('foobar'); + +# +# MDEV-9640: Add used key_id to INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION +# +--sorted_result +SELECT NAME, ENCRYPTION_SCHEME, MIN_KEY_VERSION, CURRENT_KEY_VERSION, +CURRENT_KEY_ID +FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION +WHERE NAME LIKE '%t1' OR NAME LIKE '%t2'; + +DROP TABLE t1, t2; diff --git a/mysql-test/suite/encryption/t/innodb_encryption_row_compressed.opt b/mysql-test/suite/encryption/t/innodb_encryption_row_compressed.opt new file mode 100644 index 00000000..7ebf81a0 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_row_compressed.opt @@ -0,0 +1,4 @@ +--innodb-encrypt-tables=ON +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb_encryption_row_compressed.test b/mysql-test/suite/encryption/t/innodb_encryption_row_compressed.test new file mode 100644 index 00000000..d6319164 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_row_compressed.test @@ -0,0 +1,108 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +-- source include/not_embedded.inc + +create table innodb_compressed1(c1 bigint not null primary key, d int, a varchar(20), b char(200)) engine=innodb row_format=compressed encrypted=yes; +create table innodb_compressed2(c1 bigint not null primary key, d int, a varchar(20), b char(200)) engine=innodb row_format=compressed key_block_size=1 encrypted=yes; +create table innodb_compressed3(c1 bigint not null primary key, d int, a varchar(20), b char(200)) engine=innodb row_format=compressed key_block_size=2 encrypted=yes; +create table innodb_compressed4(c1 bigint not null primary key, d int, a varchar(20), b char(200)) engine=innodb row_format=compressed key_block_size=4 encrypted=yes; + +insert into innodb_compressed1 values (1, 20, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (2, 20, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (3, 30, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (4, 30, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (5, 30, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (6, 30, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (7, 30, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (8, 20, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (9, 20, 'private', 'evenmoreprivate'); +insert into innodb_compressed1 values (10, 20, 'private', 'evenmoreprivate'); + +insert into innodb_compressed2 select * from innodb_compressed1; +insert into innodb_compressed3 select * from innodb_compressed1; +insert into innodb_compressed4 select * from innodb_compressed1; + +--source include/restart_mysqld.inc + +--let $MYSQLD_DATADIR=`select @@datadir` +--let t1_IBD = $MYSQLD_DATADIR/test/innodb_compressed1.ibd +--let t2_IBD = $MYSQLD_DATADIR/test/innodb_compressed2.ibd +--let t3_IBD = $MYSQLD_DATADIR/test/innodb_compressed3.ibd +--let t4_IBD = $MYSQLD_DATADIR/test/innodb_compressed4.ibd +--let SEARCH_RANGE = 10000000 +--let SEARCH_PATTERN=private +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc +--echo # t2 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--echo # t3 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--echo # t4 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t4_IBD +-- source include/search_pattern_in_file.inc + +select * from innodb_compressed1 where d = 20; +select * from innodb_compressed1 where d = 30; +select * from innodb_compressed2 where d = 20; +select * from innodb_compressed2 where d = 30; +select * from innodb_compressed3 where d = 20; +select * from innodb_compressed3 where d = 30; +select * from innodb_compressed4 where d = 20; +select * from innodb_compressed4 where d = 30; + +update innodb_compressed1 set d = d + 10 where d = 30; +update innodb_compressed2 set d = d + 10 where d = 30; +update innodb_compressed3 set d = d + 10 where d = 30; +update innodb_compressed4 set d = d + 10 where d = 30; + +insert into innodb_compressed1 values (20, 60, 'newprivate', 'newevenmoreprivate'); +insert into innodb_compressed2 values (20, 60, 'newprivate', 'newevenmoreprivate'); +insert into innodb_compressed3 values (20, 60, 'newprivate', 'newevenmoreprivate'); +insert into innodb_compressed4 values (20, 60, 'newprivate', 'newevenmoreprivate'); + +--let SEARCH_PATTERN=private +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc +--echo # t2 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--echo # t3 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--echo # t4 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t4_IBD +-- source include/search_pattern_in_file.inc + +--source include/restart_mysqld.inc + +select * from innodb_compressed1 where d = 40; +select * from innodb_compressed1 where d = 60; +select * from innodb_compressed2 where d = 40; +select * from innodb_compressed2 where d = 60; +select * from innodb_compressed3 where d = 40; +select * from innodb_compressed3 where d = 60; +select * from innodb_compressed4 where d = 40; +select * from innodb_compressed4 where d = 60; + +--let SEARCH_PATTERN=private +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t1_IBD +-- source include/search_pattern_in_file.inc +--echo # t2 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t2_IBD +-- source include/search_pattern_in_file.inc +--echo # t3 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t3_IBD +-- source include/search_pattern_in_file.inc +--echo # t4 yes on expecting NOT FOUND +-- let SEARCH_FILE=$t4_IBD +-- source include/search_pattern_in_file.inc + +drop table innodb_compressed1; +drop table innodb_compressed2; +drop table innodb_compressed3; +drop table innodb_compressed4; diff --git a/mysql-test/suite/encryption/t/innodb_encryption_tables.opt b/mysql-test/suite/encryption/t/innodb_encryption_tables.opt new file mode 100644 index 00000000..0b7319dc --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_tables.opt @@ -0,0 +1,7 @@ +--aria-encrypt-tables=ON +--encrypt-tmp-disk-tables=ON +--innodb-encrypt-tables=ON +--innodb-encrypt-log=ON +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb_encryption_tables.test b/mysql-test/suite/encryption/t/innodb_encryption_tables.test new file mode 100644 index 00000000..d03bc890 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_encryption_tables.test @@ -0,0 +1,99 @@ +-- source include/have_innodb.inc +-- source include/have_example_key_management_plugin.inc +-- source include/not_embedded.inc +# We can't run this test under valgrind as it 'takes forever' +-- source include/not_valgrind.inc + +create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb; +create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact; +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic; +create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed; +create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant; + +show warnings; + +show create table innodb_normal; +show create table innodb_compact; +show create table innodb_dynamic; +show create table innodb_compressed; +show create table innodb_redundant; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into innodb_normal values(current_num, substring(MD5(RAND()), -64)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +set autocommit=0; +call innodb_insert_proc(2000); +commit; +set autocommit=1; + +insert into innodb_compact select * from innodb_normal; +insert into innodb_dynamic select * from innodb_normal; +insert into innodb_compressed select * from innodb_normal; +insert into innodb_redundant select * from innodb_normal; + +update innodb_normal set c1 = c1 + 1; +update innodb_compact set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_compressed set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +select count(*) from innodb_normal; +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_compressed where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; +select count(*) from innodb_compact t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_dynamic t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_compressed t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_redundant t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; + +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_decompressed'; + +--source include/restart_mysqld.inc + +update innodb_normal set c1 = c1 + 1; +update innodb_compact set c1 = c1 + 1; +update innodb_dynamic set c1 = c1 + 1; +update innodb_compressed set c1 = c1 + 1; +update innodb_redundant set c1 = c1 + 1; +select count(*) from innodb_normal; +select count(*) from innodb_compact where c1 < 1500000; +select count(*) from innodb_dynamic where c1 < 1500000; +select count(*) from innodb_compressed where c1 < 1500000; +select count(*) from innodb_redundant where c1 < 1500000; +select count(*) from innodb_compact t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_dynamic t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_compressed t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; +select count(*) from innodb_redundant t1, innodb_normal t2 where +t1.c1 = t2.c1 and t1.b = t2.b; + +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_decompressed'; + +drop procedure innodb_insert_proc; +drop table innodb_normal; +drop table innodb_compact; +drop table innodb_dynamic; +drop table innodb_compressed; +drop table innodb_redundant; diff --git a/mysql-test/suite/encryption/t/innodb_first_page.opt b/mysql-test/suite/encryption/t/innodb_first_page.opt new file mode 100644 index 00000000..1a9099a6 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_first_page.opt @@ -0,0 +1,2 @@ +--innodb-encrypt-tables +--innodb-encrypt-log diff --git a/mysql-test/suite/encryption/t/innodb_first_page.test b/mysql-test/suite/encryption/t/innodb_first_page.test new file mode 100644 index 00000000..db4d8eb3 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_first_page.test @@ -0,0 +1,22 @@ +# +# MDEV-8021 "InnoDB: Tablespace id 4 encrypted but encryption service not available. Can't continue opening tablespace" on server restart when there are encrypted tables +# + +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc +--source include/innodb_undo_tablespaces.inc + +let $datadir=`select @@datadir`; +--source include/shutdown_mysqld.inc + +--remove_file $datadir/ib_logfile0 +--remove_file $datadir/ibdata1 +--remove_files_wildcard $datadir undo* + +--source include/start_mysqld.inc + +create table t1 (a int); + +--source include/restart_mysqld.inc + +drop table t1; diff --git a/mysql-test/suite/encryption/t/innodb_import.combinations b/mysql-test/suite/encryption/t/innodb_import.combinations new file mode 100644 index 00000000..75458949 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_import.combinations @@ -0,0 +1,7 @@ +[page_compressed] +innodb-compression-default=1 +[encryption] +innodb-encrypt-tables=1 +[page_compressed_encryption] +innodb-compression-default=1 +innodb-encrypt-tables=1 diff --git a/mysql-test/suite/encryption/t/innodb_import.opt b/mysql-test/suite/encryption/t/innodb_import.opt new file mode 100644 index 00000000..c44c611e --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_import.opt @@ -0,0 +1 @@ +--innodb-checksum-algorithm=crc32 diff --git a/mysql-test/suite/encryption/t/innodb_import.test b/mysql-test/suite/encryption/t/innodb_import.test new file mode 100644 index 00000000..2e5470c5 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_import.test @@ -0,0 +1,23 @@ +--source include/have_innodb.inc +--source include/have_example_key_management_plugin.inc +--source include/innodb_checksum_algorithm.inc +--echo # +--echo # MDEV-26131 SEGV in ha_innobase::discard_or_import_tablespace +--echo # +let $MYSQLD_DATADIR = `SELECT @@datadir`; +CREATE TABLE t1(f1 int,f2 text)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1, "InnoDB"); +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 ADD KEY idx (f2(13)); +ALTER TABLE t2 DISCARD TABLESPACE; +FLUSH TABLES t1 FOR EXPORT; +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +UNLOCK TABLES; +--error ER_INTERNAL_ERROR +ALTER TABLE t2 IMPORT TABLESPACE; + +ALTER TABLE t2 DROP KEY idx; +--replace_regex /opening '.*\/test\//opening '.\/test\// +ALTER TABLE t2 IMPORT TABLESPACE; +SELECT * FROM t2; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.opt b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.opt new file mode 100644 index 00000000..38d69691 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.opt @@ -0,0 +1,5 @@ +--innodb-encrypt-tables=ON +--innodb-encrypt-log=ON +--innodb-encryption-rotate-key-age=15 +--innodb-encryption-threads=4 +--innodb-tablespaces-encryption diff --git a/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test new file mode 100644 index 00000000..9f61bf11 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_onlinealter_encryption.test @@ -0,0 +1,135 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# test uses restart +-- source include/not_embedded.inc + +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; +CREATE TABLE t3 (id INT, a VARCHAR(255)) ENGINE=InnoDB encrypted=yes; +CREATE TABLE t4 (id INT, a VARCHAR(255)) engine=InnoDB; +CREATE TABLE t5 (id INT NOT NULL PRIMARY KEY, a TEXT(500), b VARCHAR(255), FULLTEXT(b)) ENGINE=InnoDB encrypted=yes; +CREATE TABLE t6 (id INT, a TEXT(500), b VARCHAR(255), FULLTEXT(b)) ENGINE=InnoDB; +CREATE TABLE t7 (id INT NOT NULL PRIMARY KEY, a VARCHAR(255)) ENGINE=InnoDB row_format=compressed encrypted=yes; + +delimiter //; +create procedure innodb_insert_proc (repeat_count int) +begin + declare current_num int; + set current_num = 0; + while current_num < repeat_count do + insert into t1 values (current_num,repeat('foobar',12)); + insert into t2 values (current_num,repeat('tempsecret', 12)); + insert into t3 values (current_num,repeat('barfoo',42)); + insert into t4 values (current_num,repeat('repeat',42)); + insert into t5 values (current_num,substring('A BC DEF GHIJ KLM NOPQRS TUV WXYZ 012 3456789', rand()*36+1, 100), repeat('author new',22)); + insert into t6 values (current_num,substring('A BC DEF GHIJ KLM NOPQRS TUV WXYZ 012 3456789', rand()*36+1, 100), repeat('mangled old',22)); + insert into t7 values (current_num,repeat('mysql',42)); + set current_num = current_num + 1; + end while; +end// +delimiter ;// +commit; + +set autocommit=0; +call innodb_insert_proc(1500); +commit; +set autocommit=1; + +--echo # Wait max 10 min for key encryption threads to encrypt all spaces +--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` + +--source include/shutdown_mysqld.inc + +--let SEARCH_RANGE = 10000000 +--let SEARCH_PATTERN=foobar +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=tempsecret +--echo # t2 ... on expecting NOT FOUND +-- 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=$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=$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=$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=$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=$MYSQLD_DATADIR/test/t7.ibd +-- source include/search_pattern_in_file.inc + +-- source include/start_mysqld.inc + +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; +ALTER TABLE t1 ADD KEY a(a), ADD KEY b(b); +ALTER TABLE t2 ADD KEY a(a), ADD KEY b(b); +ALTER TABLE t3 ADD COLUMN c int default 5; +ALTER TABLE t4 ADD COLUMN c int default 5; +ALTER TABLE t3 ADD KEY (a), ADD KEY c(c); +ALTER TABLE t4 ADD KEY (a), ADD KEY c(c); +ALTER TABLE t5 ADD FULLTEXT(a); +ALTER TABLE t6 ADD FULLTEXT(a); +ALTER TABLE t7 ADD KEY a(a), ADD key b(b); + +SHOW CREATE TABLE t1; +SHOW CREATE TABLE t2; +SHOW CREATE TABLE t3; +SHOW CREATE TABLE t4; +SHOW CREATE TABLE t5; +SHOW CREATE TABLE t6; +SHOW CREATE TABLE t7; + +--source include/shutdown_mysqld.inc + +--let SEARCH_PATTERN=foobar +--echo # t1 yes on expecting NOT FOUND +-- let SEARCH_FILE=$MYSQLD_DATADIR/test/t1.ibd +-- source include/search_pattern_in_file.inc +--let SEARCH_PATTERN=tempsecret +--echo # t2 ... on expecting NOT FOUND +-- 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=$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=$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=$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=$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=$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; diff --git a/mysql-test/suite/encryption/t/innodb_page_encryption_key_change.test b/mysql-test/suite/encryption/t/innodb_page_encryption_key_change.test new file mode 100644 index 00000000..acba1f60 --- /dev/null +++ b/mysql-test/suite/encryption/t/innodb_page_encryption_key_change.test @@ -0,0 +1,77 @@ +-- source include/have_innodb.inc +-- source include/have_file_key_management_plugin.inc +# embedded does not support restart +-- source include/not_embedded.inc + +-- let $restart_parameters=--loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys2.txt +-- source include/restart_mysqld.inc + +create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb; +show warnings; +create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact encrypted=yes encryption_key_id=2; +show warnings; +create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed encrypted=yes encryption_key_id=3; +show warnings; +create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=4; +show warnings; +create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant encrypted=yes encryption_key_id=5; +show warnings; + +insert into innodb_normal values (1,'test1'),(2,'foo'),(3,'bar'),(4,'mariadb'); +insert into innodb_compact select * from innodb_normal; +insert into innodb_compressed select * from innodb_normal; +insert into innodb_dynamic select * from innodb_normal; +insert into innodb_redundant select * from innodb_normal; + +# Note there that these variables are updated only when real I/O is done, thus they are not reliable +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; + +alter table innodb_compact engine=innodb encryption_key_id = 6; +alter table innodb_compressed engine=innodb encryption_key_id = 6; +alter table innodb_dynamic engine=innodb encryption_key_id = 6; +alter table innodb_redundant engine=innodb encryption_key_id = 6; + +select * from innodb_normal; +select * from innodb_compact; +select * from innodb_compressed; +select * from innodb_dynamic; +select * from innodb_redundant; + +# Note there that these variables are updated only when real I/O is done, thus they are not reliable +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; + +-- let $restart_parameters=--loose-file-key-management-filename=$MYSQL_TEST_DIR/std_data/keys3.txt +-- source include/restart_mysqld.inc + +select * from innodb_normal; +select * from innodb_compact; +select * from innodb_compressed; +select * from innodb_dynamic; +select * from innodb_redundant; + +# Note there that these variables are updated only when real I/O is done, thus they are not reliable +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; + +alter table innodb_compact engine=innodb encryption_key_id = 2; +alter table innodb_compressed engine=innodb encryption_key_id = 3; +alter table innodb_dynamic engine=innodb encryption_key_id = 4; +alter table innodb_redundant engine=innodb encryption_key_id = 5; + +select * from innodb_normal; +select * from innodb_compact; +select * from innodb_compressed; +select * from innodb_dynamic; +select * from innodb_redundant; + +# Note there that these variables are updated only when real I/O is done, thus they are not reliable +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted'; +SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted'; + +drop table innodb_normal; +drop table innodb_compact; +drop table innodb_compressed; +drop table innodb_dynamic; +drop table innodb_redundant; diff --git a/mysql-test/suite/encryption/t/instant_alter_import.test b/mysql-test/suite/encryption/t/instant_alter_import.test new file mode 100644 index 00000000..3a00c546 --- /dev/null +++ b/mysql-test/suite/encryption/t/instant_alter_import.test @@ -0,0 +1,52 @@ +--source include/have_innodb.inc +--source include/have_sequence.inc +--source include/have_file_key_management_plugin.inc + +SET GLOBAL innodb_encrypt_tables = ON; +SET GLOBAL innodb_encryption_threads = 4; + + +--let $MYSQLD_DATADIR= `SELECT @@datadir` + +CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, i2 INT, i1 INT) + ENGINE=INNODB ENCRYPTED=YES ENCRYPTION_KEY_ID=4; + +INSERT INTO t1 (i2) SELECT 4 FROM seq_1_to_1024; +ALTER TABLE t1 DROP COLUMN i2, ALGORITHM=INSTANT; + +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 DISCARD TABLESPACE; + +FLUSH TABLE t1 FOR EXPORT; + +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg + +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; + +DROP TABLE t2, t1; + + +CREATE TABLE t1 (id INT PRIMARY KEY AUTO_INCREMENT, i2 INT, i1 INT) + ENGINE=INNODB ENCRYPTED=YES ENCRYPTION_KEY_ID=4 PAGE_COMPRESSED=1; + +INSERT INTO t1 (i2) SELECT 4 FROM seq_1_to_1024; +ALTER TABLE t1 DROP COLUMN i2, ALGORITHM=INSTANT; + +CREATE TABLE t2 LIKE t1; +ALTER TABLE t2 DISCARD TABLESPACE; + +FLUSH TABLE t1 FOR EXPORT; + +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t2.ibd +--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t2.cfg + +UNLOCK TABLES; +ALTER TABLE t2 IMPORT TABLESPACE; + +DROP TABLE t2, t1; + + +SET GLOBAL innodb_encrypt_tables = OFF; +SET GLOBAL innodb_encryption_threads = 0; diff --git a/mysql-test/suite/encryption/t/key_version_rotation.opt b/mysql-test/suite/encryption/t/key_version_rotation.opt new file mode 100644 index 00000000..d7933f0f --- /dev/null +++ b/mysql-test/suite/encryption/t/key_version_rotation.opt @@ -0,0 +1,2 @@ +--innodb-tablespaces-encryption +--plugin-load-add=$DEBUG_KEY_MANAGEMENT_SO diff --git a/mysql-test/suite/encryption/t/key_version_rotation.test b/mysql-test/suite/encryption/t/key_version_rotation.test new file mode 100644 index 00000000..d36d4725 --- /dev/null +++ b/mysql-test/suite/encryption/t/key_version_rotation.test @@ -0,0 +1,41 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_sequence.inc + +create table t1(f1 int not null)engine=innodb; +create table t2(f1 int not null)engine=innodb; +insert into t1 select * from seq_1_to_100; +insert into t2 select * from seq_1_to_100; + +let $restart_parameters=--innodb_encrypt_tables=0 --innodb_encryption_threads=1 --innodb_encryption_rotate_key_age=9; +--source include/restart_mysqld.inc + +--echo # Enable encryption + +set global innodb_encrypt_tables=ON; +--let $tables_count= `select count(*) from information_schema.tables where engine = 'InnoDB'` +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc +--echo # Create a new table and it is added to rotation list +create table t3(f1 int not null)engine=innodb; +insert into t3 select * from seq_1_to_100; + +--echo # Increase the version and it should set rotation +--echo # variable for the encryption plugin + +set global debug_key_management_version=10; +select @@debug_key_management_version; +--let $tables_count= `select count(*) from information_schema.tables where engine = 'InnoDB'` +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0; +--source include/wait_condition.inc + +--echo # Decrease the key version and Disable the encryption +set global debug_key_management_version=1; +set global innodb_encrypt_tables=off; + +--let $wait_timeout= 600 +--let $wait_condition=SELECT COUNT(*) >= $tables_count FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; +--source include/wait_condition.inc +DROP TABLE t1, t2, t3; diff --git a/mysql-test/suite/encryption/t/second_plugin-12863.test b/mysql-test/suite/encryption/t/second_plugin-12863.test new file mode 100644 index 00000000..c04fccf9 --- /dev/null +++ b/mysql-test/suite/encryption/t/second_plugin-12863.test @@ -0,0 +1,16 @@ +# +# MDEV-12863 No table can be created after second encryption plugin attempted to load +# +--source include/have_innodb.inc +--source include/have_file_key_management_plugin.inc + +call mtr.add_suppression('debug.key.management'); + +--error 1123 +install soname 'debug_key_management'; + +create table t1 (a varchar(255)) engine=innodb encrypted=yes; +create table t2 (a varchar(255)) engine=innodb; +create table t3 (a varchar(255)) engine=innodb encrypted=no; + +drop table t1, t2, t3; diff --git a/mysql-test/suite/encryption/t/tempfiles.combinations b/mysql-test/suite/encryption/t/tempfiles.combinations new file mode 100644 index 00000000..7010d2c1 --- /dev/null +++ b/mysql-test/suite/encryption/t/tempfiles.combinations @@ -0,0 +1,5 @@ +[none] +binlog-checksum=NONE + +[crc32] +binlog-checksum=CRC32 diff --git a/mysql-test/suite/encryption/t/tempfiles.opt b/mysql-test/suite/encryption/t/tempfiles.opt new file mode 100644 index 00000000..2b90f474 --- /dev/null +++ b/mysql-test/suite/encryption/t/tempfiles.opt @@ -0,0 +1 @@ +--encrypt-tmp-files diff --git a/mysql-test/suite/encryption/t/tempfiles.test b/mysql-test/suite/encryption/t/tempfiles.test new file mode 100644 index 00000000..74d03f5a --- /dev/null +++ b/mysql-test/suite/encryption/t/tempfiles.test @@ -0,0 +1,85 @@ +# +# Various test cases for IO_CACHE tempfiles (file==-1) encryption +# +source include/have_file_key_management_plugin.inc; +source include/have_sequence.inc; + +# Row binlog format to fill binlog cache faster +source include/have_binlog_format_row.inc; + +source include/have_innodb.inc; + +select @@encrypt_tmp_files; + +# +# MyISAM messing around with IO_CACHE::file +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES(1),(2); +DELETE FROM t1 WHERE a=1; +OPTIMIZE TABLE t1; +CHECK TABLE t1; +DROP TABLE t1; + +# +# filesort, my_b_pread, seeks in READ_CACHE +# +create table t1 (v varchar(10), c char(10), t text, key(v), key(c), key(t(10))); +insert into t1 (v) select concat(char(ascii('a')+s2.seq),repeat(' ',s1.seq)) + from seq_0_to_9 as s1, seq_0_to_26 as s2; +update t1 set c=v, t=v; +select sql_big_result t,count(t) from t1 group by t limit 10; +drop table t1; + +reset master; +set @save_binlog_cache_size=@@global.binlog_cache_size; +set global binlog_cache_size=8192; + +connect con1, localhost, root; + +# +# Test the last half-filled block: +# first write 3 blocks, then reinit the file and write one full and one +# partial block. reading the second time must stop in the middle of the +# second block, and NOT read till EOF. +# +create table t1 (a text) engine=innodb; +start transaction; +insert t1 select repeat(seq, 1000) from seq_1_to_15; +commit; +start transaction; +insert t1 select repeat(seq, 1000) from seq_1_to_8; +commit; + +disconnect con1; +connect con2, localhost, root; + +# +# Test reinit_io_cache(WRITE_CACHE) with non-zero seek_offset: +# Start a transaction, write until the cache goes to disk, +# create a savepoint, write more blocks to disk, rollback to savepoint. +# +create table t2 (a text) engine=innodb; +start transaction; +insert t2 select repeat(seq, 1000) from seq_1_to_15; +savepoint foo; +insert t2 select repeat(seq, 1000) from seq_16_to_30; +rollback to savepoint foo; +insert t2 select repeat(seq, 1000) from seq_31_to_40; +commit; + +disconnect con2; +connection default; + +flush binary logs; + +drop table t1, t2; + +set @@global.binlog_cache_size=@save_binlog_cache_size; + +let $MYSQLD_DATADIR= `select @@datadir`; +exec $MYSQL_BINLOG $MYSQLD_DATADIR/master-bin.000001 | $MYSQL; + +select left(a, 10) from t1; +select left(a, 10) from t2; +drop table t1, t2; diff --git a/mysql-test/suite/encryption/t/tempfiles_encrypted.opt b/mysql-test/suite/encryption/t/tempfiles_encrypted.opt new file mode 100644 index 00000000..81877a8d --- /dev/null +++ b/mysql-test/suite/encryption/t/tempfiles_encrypted.opt @@ -0,0 +1 @@ +--encrypt-tmp_files=ON diff --git a/mysql-test/suite/encryption/t/tempfiles_encrypted.test b/mysql-test/suite/encryption/t/tempfiles_encrypted.test new file mode 100644 index 00000000..7628442f --- /dev/null +++ b/mysql-test/suite/encryption/t/tempfiles_encrypted.test @@ -0,0 +1,47 @@ +--echo # +--echo # Tests when the temporary files are encrypted +--echo # + +source include/have_file_key_management_plugin.inc; +source include/have_sequence.inc; +source include/have_innodb.inc; + +select @@encrypt_tmp_files; + +--source main/win.test + +--echo # +--echo # MDEV-23867: select crash in compute_window_func +--echo # + +set @save_sort_buffer_size=@@sort_buffer_size; + +set sort_buffer_size= 2000; +CREATE TABLE t1( a INT, b INT, c INT); +INSERT INTO t1 select seq, seq, seq from seq_1_to_5000; +CREATE TABLE t2( a INT, b INT, c INT); +INSERT INTO t2 SELECT a, b, ROW_NUMBER() OVER (PARTITION BY b) FROM t1; +SELECT COUNT(*), MAX(c) FROM t2; +CREATE TABLE t3( a INT, b INT, c INT); +INSERT INTO t3 SELECT a, b, SUM(a) OVER () FROM t1; +SELECT COUNT(*), MAX(c) FROM t3; +set @@sort_buffer_size=@save_sort_buffer_size; +DROP TABLE t1,t2,t3; + +--echo # end of 10.2 test + +--echo # +--echo # MDEV-22556: Incorrect result for window function when using encrypt-tmp-files=ON +--echo # + +set @save_sort_buffer_size=@@sort_buffer_size; +set sort_buffer_size= 2000; +create table t1( a DECIMAL(12,0) DEFAULT NULL, b VARCHAR(20) DEFAULT NULL, c DECIMAL(12,0) DEFAULT NULL)engine=INNODB; +insert into t1 select seq, seq, seq from seq_1_to_5000; +select count(*) from (select a, b, c, ROW_NUMBER() OVER (PARTITION BY a) FROM t1)q; + +set @@sort_buffer_size=@save_sort_buffer_size; + +drop table t1; + +--echo # End of 10.4 tests |