Clean up some encryption tests

Instead of pointlessly waiting for a page flush to occur, take
the matter into our own hands and request an explicit flush.
Also, test with the minimum necessary amount of data (0 or 1 rows)
so that both page encryption and decryption will be exercised.

4 files changed, 101 insertions, 84 deletions

diff --git a/mysql-test/suite/encryption/r/innodb-page_encryption_compression.result b/mysql-test/suite/encryption/r/innodb-page_encryption_compression.result
index 808345ab4ca..e8e88a1e41f 100644
--- a/mysql-test/suite/encryption/r/innodb-page_encryption_compression.result
+++ b/mysql-test/suite/encryption/r/innodb-page_encryption_compression.result
@@ -41,6 +41,14 @@ 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';
+variable_value > 0
+1
 set global innodb_compression_algorithm = 1;
 alter table innodb_normal engine=innodb page_compressed=DEFAULT;
 show create table innodb_normal;
@@ -63,6 +71,16 @@ innodb_dynamic	CREATE TABLE `innodb_dynamic` (
   `c1` bigint(20) NOT NULL,
   `b` char(200) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=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';
+variable_value > 0
+1
 drop procedure innodb_insert_proc;
 drop table innodb_normal;
 drop table innodb_compact;
diff --git a/mysql-test/suite/encryption/r/innodb-page_encryption_log_encryption.result b/mysql-test/suite/encryption/r/innodb-page_encryption_log_encryption.result
index 189be75b83f..a550b6b01b3 100644
--- a/mysql-test/suite/encryption/r/innodb-page_encryption_log_encryption.result
+++ b/mysql-test/suite/encryption/r/innodb-page_encryption_log_encryption.result
@@ -37,40 +37,43 @@ innodb_redundant	CREATE TABLE `innodb_redundant` (
   `c1` bigint(20) NOT NULL,
   `b` char(200) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT `encrypted`=yes `encryption_key_id`=4
-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//
-commit;
-begin;
-call innodb_insert_proc(2000);
-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;
-commit;
-SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
-variable_value > 0
-1
-SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
-variable_value >= 0
-1
-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 variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
-variable_value > 0
-1
-SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
+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';
 variable_value > 0
 1
+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');
+variable_name
+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;
@@ -100,13 +103,16 @@ innodb_redundant	CREATE TABLE `innodb_redundant` (
   `c1` bigint(20) NOT NULL,
   `b` char(200) DEFAULT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT
-SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
-variable_value >= 0
-1
-SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
-variable_value >= 0
-1
-drop procedure innodb_insert_proc;
+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;
diff --git a/mysql-test/suite/encryption/t/innodb-page_encryption_compression.test b/mysql-test/suite/encryption/t/innodb-page_encryption_compression.test
index d902fa38508..5fe6f686576 100644
--- a/mysql-test/suite/encryption/t/innodb-page_encryption_compression.test
+++ b/mysql-test/suite/encryption/t/innodb-page_encryption_compression.test
@@ -37,8 +37,10 @@ insert into innodb_compact select * from innodb_normal;
 insert into innodb_dynamic select * from innodb_normal;
 commit;
 
-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
+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
@@ -52,8 +54,13 @@ alter table innodb_compact engine=innodb encrypted=DEFAULT encryption_key_id=DEF
 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;
-let $wait_condition= select variable_value > 0 from information_schema.global_status where variable_name = 'INNODB_NUM_PAGES_PAGE_DECOMPRESSED';
---source include/wait_condition.inc
+
+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;
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
index 5b472094653..a736c7292ad 100644
--- a/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.test
+++ b/mysql-test/suite/encryption/t/innodb-page_encryption_log_encryption.test
@@ -18,47 +18,34 @@ 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;
+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;
 
-begin;
-call innodb_insert_proc(2000);
-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;
-commit;
-
-let $wait_condition= select variable_value > 0 from information_schema.global_status where variable_name = 'INNODB_NUM_PAGES_ENCRYPTED';
---source include/wait_condition.inc
-
-# 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';
+select variable_value > 0 from information_schema.global_status
+where variable_name = 'INNODB_NUM_PAGES_ENCRYPTED';
 
 --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;
+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;
 
-let $wait_condition= select variable_value > 0 from information_schema.global_status where variable_name = 'INNODB_NUM_PAGES_ENCRYPTED';
---source include/wait_condition.inc
+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';
-SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
+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;
@@ -70,13 +57,12 @@ show create table innodb_dynamic;
 alter table innodb_redundant engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
 show create table innodb_redundant;
 
-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_encrypted';
-SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
+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 procedure innodb_insert_proc;
 drop table innodb_normal;
 drop table innodb_compact;
 drop table innodb_compressed;