MDEV-14398 When innodb_encryption_rotate_key_age=0 is set, server won't encrypt tablespacesbb-10.4-MDEV-14398-alternative

- For unloaded tablespace, all columns except space id, name
in innodb_tablespaces_encryption table will show NULL value.

5 files changed, 742 insertions, 33 deletions

diff --git a/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
index f233ed48a02..9d56ea13ec5 100644
--- a/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
+++ b/mysql-test/suite/encryption/r/innodb-key-rotation-disable.result
@@ -2,6 +2,10 @@ SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_
 NAME
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0;
 NAME
+innodb_system
+mysql/innodb_index_stats
+mysql/innodb_table_stats
+mysql/transaction_registry
 SET GLOBAL innodb_file_per_table = ON;
 set global innodb_compression_algorithm = 1;
 create database enctests;
@@ -39,10 +43,6 @@ NAME	ENCRYPTION_SCHEME	CURRENT_KEY_ID
 enctests/t7	0	1
 enctests/t8	0	1
 enctests/t9	0	1
-SET GLOBAL innodb_encrypt_tables=OFF;
-ERROR 42000: Variable 'innodb_encrypt_tables' can't be set to the value of 'OFF'
-SET GLOBAL innodb_encrypt_tables=ON;
-ERROR 42000: Variable 'innodb_encrypt_tables' can't be set to the value of 'ON'
 # t1 default on expecting NOT FOUND
 NOT FOUND /secred/ in t1.ibd
 # t2 default on expecting NOT FOUND
diff --git a/mysql-test/suite/encryption/r/innodb_lotoftables.result b/mysql-test/suite/encryption/r/innodb_lotoftables.result
index 3de38dc59ea..8ed4bd3610b 100644
--- a/mysql-test/suite/encryption/r/innodb_lotoftables.result
+++ b/mysql-test/suite/encryption/r/innodb_lotoftables.result
@@ -11,9 +11,109 @@ use innodb_encrypted_1;
 set autocommit=0;
 set autocommit=1;
 commit work;
-# should be empty
-SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'innodb_encrypted%';
+# All 100 tables are not encrypted
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'innodb_encrypted%' and MIN_KEY_VERSION = 0;
 NAME
+innodb_encrypted_1/t_100
+innodb_encrypted_1/t_99
+innodb_encrypted_1/t_98
+innodb_encrypted_1/t_97
+innodb_encrypted_1/t_96
+innodb_encrypted_1/t_95
+innodb_encrypted_1/t_94
+innodb_encrypted_1/t_93
+innodb_encrypted_1/t_92
+innodb_encrypted_1/t_91
+innodb_encrypted_1/t_90
+innodb_encrypted_1/t_89
+innodb_encrypted_1/t_88
+innodb_encrypted_1/t_87
+innodb_encrypted_1/t_86
+innodb_encrypted_1/t_85
+innodb_encrypted_1/t_84
+innodb_encrypted_1/t_83
+innodb_encrypted_1/t_82
+innodb_encrypted_1/t_81
+innodb_encrypted_1/t_80
+innodb_encrypted_1/t_79
+innodb_encrypted_1/t_78
+innodb_encrypted_1/t_77
+innodb_encrypted_1/t_76
+innodb_encrypted_1/t_75
+innodb_encrypted_1/t_74
+innodb_encrypted_1/t_73
+innodb_encrypted_1/t_72
+innodb_encrypted_1/t_71
+innodb_encrypted_1/t_70
+innodb_encrypted_1/t_69
+innodb_encrypted_1/t_68
+innodb_encrypted_1/t_67
+innodb_encrypted_1/t_66
+innodb_encrypted_1/t_65
+innodb_encrypted_1/t_64
+innodb_encrypted_1/t_63
+innodb_encrypted_1/t_62
+innodb_encrypted_1/t_61
+innodb_encrypted_1/t_60
+innodb_encrypted_1/t_59
+innodb_encrypted_1/t_58
+innodb_encrypted_1/t_57
+innodb_encrypted_1/t_56
+innodb_encrypted_1/t_55
+innodb_encrypted_1/t_54
+innodb_encrypted_1/t_53
+innodb_encrypted_1/t_52
+innodb_encrypted_1/t_51
+innodb_encrypted_1/t_50
+innodb_encrypted_1/t_49
+innodb_encrypted_1/t_48
+innodb_encrypted_1/t_47
+innodb_encrypted_1/t_46
+innodb_encrypted_1/t_45
+innodb_encrypted_1/t_44
+innodb_encrypted_1/t_43
+innodb_encrypted_1/t_42
+innodb_encrypted_1/t_41
+innodb_encrypted_1/t_40
+innodb_encrypted_1/t_39
+innodb_encrypted_1/t_38
+innodb_encrypted_1/t_37
+innodb_encrypted_1/t_36
+innodb_encrypted_1/t_35
+innodb_encrypted_1/t_34
+innodb_encrypted_1/t_33
+innodb_encrypted_1/t_32
+innodb_encrypted_1/t_31
+innodb_encrypted_1/t_30
+innodb_encrypted_1/t_29
+innodb_encrypted_1/t_28
+innodb_encrypted_1/t_27
+innodb_encrypted_1/t_26
+innodb_encrypted_1/t_25
+innodb_encrypted_1/t_24
+innodb_encrypted_1/t_23
+innodb_encrypted_1/t_22
+innodb_encrypted_1/t_21
+innodb_encrypted_1/t_20
+innodb_encrypted_1/t_19
+innodb_encrypted_1/t_18
+innodb_encrypted_1/t_17
+innodb_encrypted_1/t_16
+innodb_encrypted_1/t_15
+innodb_encrypted_1/t_14
+innodb_encrypted_1/t_13
+innodb_encrypted_1/t_12
+innodb_encrypted_1/t_11
+innodb_encrypted_1/t_10
+innodb_encrypted_1/t_9
+innodb_encrypted_1/t_8
+innodb_encrypted_1/t_7
+innodb_encrypted_1/t_6
+innodb_encrypted_1/t_5
+innodb_encrypted_1/t_4
+innodb_encrypted_1/t_3
+innodb_encrypted_1/t_2
+innodb_encrypted_1/t_1
 create database innodb_encrypted_2;
 use innodb_encrypted_2;
 set autocommit=0;
@@ -122,9 +222,109 @@ innodb_encrypted_2/t_96
 innodb_encrypted_2/t_97
 innodb_encrypted_2/t_98
 innodb_encrypted_2/t_99
-# should contain 0 tables
+# should contain 100 tables
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
 NAME
+innodb_encrypted_1/t_1
+innodb_encrypted_1/t_10
+innodb_encrypted_1/t_100
+innodb_encrypted_1/t_11
+innodb_encrypted_1/t_12
+innodb_encrypted_1/t_13
+innodb_encrypted_1/t_14
+innodb_encrypted_1/t_15
+innodb_encrypted_1/t_16
+innodb_encrypted_1/t_17
+innodb_encrypted_1/t_18
+innodb_encrypted_1/t_19
+innodb_encrypted_1/t_2
+innodb_encrypted_1/t_20
+innodb_encrypted_1/t_21
+innodb_encrypted_1/t_22
+innodb_encrypted_1/t_23
+innodb_encrypted_1/t_24
+innodb_encrypted_1/t_25
+innodb_encrypted_1/t_26
+innodb_encrypted_1/t_27
+innodb_encrypted_1/t_28
+innodb_encrypted_1/t_29
+innodb_encrypted_1/t_3
+innodb_encrypted_1/t_30
+innodb_encrypted_1/t_31
+innodb_encrypted_1/t_32
+innodb_encrypted_1/t_33
+innodb_encrypted_1/t_34
+innodb_encrypted_1/t_35
+innodb_encrypted_1/t_36
+innodb_encrypted_1/t_37
+innodb_encrypted_1/t_38
+innodb_encrypted_1/t_39
+innodb_encrypted_1/t_4
+innodb_encrypted_1/t_40
+innodb_encrypted_1/t_41
+innodb_encrypted_1/t_42
+innodb_encrypted_1/t_43
+innodb_encrypted_1/t_44
+innodb_encrypted_1/t_45
+innodb_encrypted_1/t_46
+innodb_encrypted_1/t_47
+innodb_encrypted_1/t_48
+innodb_encrypted_1/t_49
+innodb_encrypted_1/t_5
+innodb_encrypted_1/t_50
+innodb_encrypted_1/t_51
+innodb_encrypted_1/t_52
+innodb_encrypted_1/t_53
+innodb_encrypted_1/t_54
+innodb_encrypted_1/t_55
+innodb_encrypted_1/t_56
+innodb_encrypted_1/t_57
+innodb_encrypted_1/t_58
+innodb_encrypted_1/t_59
+innodb_encrypted_1/t_6
+innodb_encrypted_1/t_60
+innodb_encrypted_1/t_61
+innodb_encrypted_1/t_62
+innodb_encrypted_1/t_63
+innodb_encrypted_1/t_64
+innodb_encrypted_1/t_65
+innodb_encrypted_1/t_66
+innodb_encrypted_1/t_67
+innodb_encrypted_1/t_68
+innodb_encrypted_1/t_69
+innodb_encrypted_1/t_7
+innodb_encrypted_1/t_70
+innodb_encrypted_1/t_71
+innodb_encrypted_1/t_72
+innodb_encrypted_1/t_73
+innodb_encrypted_1/t_74
+innodb_encrypted_1/t_75
+innodb_encrypted_1/t_76
+innodb_encrypted_1/t_77
+innodb_encrypted_1/t_78
+innodb_encrypted_1/t_79
+innodb_encrypted_1/t_8
+innodb_encrypted_1/t_80
+innodb_encrypted_1/t_81
+innodb_encrypted_1/t_82
+innodb_encrypted_1/t_83
+innodb_encrypted_1/t_84
+innodb_encrypted_1/t_85
+innodb_encrypted_1/t_86
+innodb_encrypted_1/t_87
+innodb_encrypted_1/t_88
+innodb_encrypted_1/t_89
+innodb_encrypted_1/t_9
+innodb_encrypted_1/t_90
+innodb_encrypted_1/t_91
+innodb_encrypted_1/t_92
+innodb_encrypted_1/t_93
+innodb_encrypted_1/t_94
+innodb_encrypted_1/t_95
+innodb_encrypted_1/t_96
+innodb_encrypted_1/t_97
+innodb_encrypted_1/t_98
+innodb_encrypted_1/t_99
 create database innodb_encrypted_3;
 use innodb_encrypted_3;
 set autocommit=0;
@@ -236,6 +436,106 @@ innodb_encrypted_2/t_99
 # should contain 100 tables
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
 NAME
+innodb_encrypted_1/t_1
+innodb_encrypted_1/t_10
+innodb_encrypted_1/t_100
+innodb_encrypted_1/t_11
+innodb_encrypted_1/t_12
+innodb_encrypted_1/t_13
+innodb_encrypted_1/t_14
+innodb_encrypted_1/t_15
+innodb_encrypted_1/t_16
+innodb_encrypted_1/t_17
+innodb_encrypted_1/t_18
+innodb_encrypted_1/t_19
+innodb_encrypted_1/t_2
+innodb_encrypted_1/t_20
+innodb_encrypted_1/t_21
+innodb_encrypted_1/t_22
+innodb_encrypted_1/t_23
+innodb_encrypted_1/t_24
+innodb_encrypted_1/t_25
+innodb_encrypted_1/t_26
+innodb_encrypted_1/t_27
+innodb_encrypted_1/t_28
+innodb_encrypted_1/t_29
+innodb_encrypted_1/t_3
+innodb_encrypted_1/t_30
+innodb_encrypted_1/t_31
+innodb_encrypted_1/t_32
+innodb_encrypted_1/t_33
+innodb_encrypted_1/t_34
+innodb_encrypted_1/t_35
+innodb_encrypted_1/t_36
+innodb_encrypted_1/t_37
+innodb_encrypted_1/t_38
+innodb_encrypted_1/t_39
+innodb_encrypted_1/t_4
+innodb_encrypted_1/t_40
+innodb_encrypted_1/t_41
+innodb_encrypted_1/t_42
+innodb_encrypted_1/t_43
+innodb_encrypted_1/t_44
+innodb_encrypted_1/t_45
+innodb_encrypted_1/t_46
+innodb_encrypted_1/t_47
+innodb_encrypted_1/t_48
+innodb_encrypted_1/t_49
+innodb_encrypted_1/t_5
+innodb_encrypted_1/t_50
+innodb_encrypted_1/t_51
+innodb_encrypted_1/t_52
+innodb_encrypted_1/t_53
+innodb_encrypted_1/t_54
+innodb_encrypted_1/t_55
+innodb_encrypted_1/t_56
+innodb_encrypted_1/t_57
+innodb_encrypted_1/t_58
+innodb_encrypted_1/t_59
+innodb_encrypted_1/t_6
+innodb_encrypted_1/t_60
+innodb_encrypted_1/t_61
+innodb_encrypted_1/t_62
+innodb_encrypted_1/t_63
+innodb_encrypted_1/t_64
+innodb_encrypted_1/t_65
+innodb_encrypted_1/t_66
+innodb_encrypted_1/t_67
+innodb_encrypted_1/t_68
+innodb_encrypted_1/t_69
+innodb_encrypted_1/t_7
+innodb_encrypted_1/t_70
+innodb_encrypted_1/t_71
+innodb_encrypted_1/t_72
+innodb_encrypted_1/t_73
+innodb_encrypted_1/t_74
+innodb_encrypted_1/t_75
+innodb_encrypted_1/t_76
+innodb_encrypted_1/t_77
+innodb_encrypted_1/t_78
+innodb_encrypted_1/t_79
+innodb_encrypted_1/t_8
+innodb_encrypted_1/t_80
+innodb_encrypted_1/t_81
+innodb_encrypted_1/t_82
+innodb_encrypted_1/t_83
+innodb_encrypted_1/t_84
+innodb_encrypted_1/t_85
+innodb_encrypted_1/t_86
+innodb_encrypted_1/t_87
+innodb_encrypted_1/t_88
+innodb_encrypted_1/t_89
+innodb_encrypted_1/t_9
+innodb_encrypted_1/t_90
+innodb_encrypted_1/t_91
+innodb_encrypted_1/t_92
+innodb_encrypted_1/t_93
+innodb_encrypted_1/t_94
+innodb_encrypted_1/t_95
+innodb_encrypted_1/t_96
+innodb_encrypted_1/t_97
+innodb_encrypted_1/t_98
+innodb_encrypted_1/t_99
 innodb_encrypted_3/t_1
 innodb_encrypted_3/t_10
 innodb_encrypted_3/t_100
@@ -441,6 +741,106 @@ innodb_encrypted_2/t_98
 innodb_encrypted_2/t_99
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
 NAME
+innodb_encrypted_1/t_1
+innodb_encrypted_1/t_10
+innodb_encrypted_1/t_100
+innodb_encrypted_1/t_11
+innodb_encrypted_1/t_12
+innodb_encrypted_1/t_13
+innodb_encrypted_1/t_14
+innodb_encrypted_1/t_15
+innodb_encrypted_1/t_16
+innodb_encrypted_1/t_17
+innodb_encrypted_1/t_18
+innodb_encrypted_1/t_19
+innodb_encrypted_1/t_2
+innodb_encrypted_1/t_20
+innodb_encrypted_1/t_21
+innodb_encrypted_1/t_22
+innodb_encrypted_1/t_23
+innodb_encrypted_1/t_24
+innodb_encrypted_1/t_25
+innodb_encrypted_1/t_26
+innodb_encrypted_1/t_27
+innodb_encrypted_1/t_28
+innodb_encrypted_1/t_29
+innodb_encrypted_1/t_3
+innodb_encrypted_1/t_30
+innodb_encrypted_1/t_31
+innodb_encrypted_1/t_32
+innodb_encrypted_1/t_33
+innodb_encrypted_1/t_34
+innodb_encrypted_1/t_35
+innodb_encrypted_1/t_36
+innodb_encrypted_1/t_37
+innodb_encrypted_1/t_38
+innodb_encrypted_1/t_39
+innodb_encrypted_1/t_4
+innodb_encrypted_1/t_40
+innodb_encrypted_1/t_41
+innodb_encrypted_1/t_42
+innodb_encrypted_1/t_43
+innodb_encrypted_1/t_44
+innodb_encrypted_1/t_45
+innodb_encrypted_1/t_46
+innodb_encrypted_1/t_47
+innodb_encrypted_1/t_48
+innodb_encrypted_1/t_49
+innodb_encrypted_1/t_5
+innodb_encrypted_1/t_50
+innodb_encrypted_1/t_51
+innodb_encrypted_1/t_52
+innodb_encrypted_1/t_53
+innodb_encrypted_1/t_54
+innodb_encrypted_1/t_55
+innodb_encrypted_1/t_56
+innodb_encrypted_1/t_57
+innodb_encrypted_1/t_58
+innodb_encrypted_1/t_59
+innodb_encrypted_1/t_6
+innodb_encrypted_1/t_60
+innodb_encrypted_1/t_61
+innodb_encrypted_1/t_62
+innodb_encrypted_1/t_63
+innodb_encrypted_1/t_64
+innodb_encrypted_1/t_65
+innodb_encrypted_1/t_66
+innodb_encrypted_1/t_67
+innodb_encrypted_1/t_68
+innodb_encrypted_1/t_69
+innodb_encrypted_1/t_7
+innodb_encrypted_1/t_70
+innodb_encrypted_1/t_71
+innodb_encrypted_1/t_72
+innodb_encrypted_1/t_73
+innodb_encrypted_1/t_74
+innodb_encrypted_1/t_75
+innodb_encrypted_1/t_76
+innodb_encrypted_1/t_77
+innodb_encrypted_1/t_78
+innodb_encrypted_1/t_79
+innodb_encrypted_1/t_8
+innodb_encrypted_1/t_80
+innodb_encrypted_1/t_81
+innodb_encrypted_1/t_82
+innodb_encrypted_1/t_83
+innodb_encrypted_1/t_84
+innodb_encrypted_1/t_85
+innodb_encrypted_1/t_86
+innodb_encrypted_1/t_87
+innodb_encrypted_1/t_88
+innodb_encrypted_1/t_89
+innodb_encrypted_1/t_9
+innodb_encrypted_1/t_90
+innodb_encrypted_1/t_91
+innodb_encrypted_1/t_92
+innodb_encrypted_1/t_93
+innodb_encrypted_1/t_94
+innodb_encrypted_1/t_95
+innodb_encrypted_1/t_96
+innodb_encrypted_1/t_97
+innodb_encrypted_1/t_98
+innodb_encrypted_1/t_99
 innodb_encrypted_3/t_1
 innodb_encrypted_3/t_10
 innodb_encrypted_3/t_100
@@ -647,6 +1047,106 @@ innodb_encrypted_2/t_98
 innodb_encrypted_2/t_99
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
 NAME
+innodb_encrypted_1/t_1
+innodb_encrypted_1/t_10
+innodb_encrypted_1/t_100
+innodb_encrypted_1/t_11
+innodb_encrypted_1/t_12
+innodb_encrypted_1/t_13
+innodb_encrypted_1/t_14
+innodb_encrypted_1/t_15
+innodb_encrypted_1/t_16
+innodb_encrypted_1/t_17
+innodb_encrypted_1/t_18
+innodb_encrypted_1/t_19
+innodb_encrypted_1/t_2
+innodb_encrypted_1/t_20
+innodb_encrypted_1/t_21
+innodb_encrypted_1/t_22
+innodb_encrypted_1/t_23
+innodb_encrypted_1/t_24
+innodb_encrypted_1/t_25
+innodb_encrypted_1/t_26
+innodb_encrypted_1/t_27
+innodb_encrypted_1/t_28
+innodb_encrypted_1/t_29
+innodb_encrypted_1/t_3
+innodb_encrypted_1/t_30
+innodb_encrypted_1/t_31
+innodb_encrypted_1/t_32
+innodb_encrypted_1/t_33
+innodb_encrypted_1/t_34
+innodb_encrypted_1/t_35
+innodb_encrypted_1/t_36
+innodb_encrypted_1/t_37
+innodb_encrypted_1/t_38
+innodb_encrypted_1/t_39
+innodb_encrypted_1/t_4
+innodb_encrypted_1/t_40
+innodb_encrypted_1/t_41
+innodb_encrypted_1/t_42
+innodb_encrypted_1/t_43
+innodb_encrypted_1/t_44
+innodb_encrypted_1/t_45
+innodb_encrypted_1/t_46
+innodb_encrypted_1/t_47
+innodb_encrypted_1/t_48
+innodb_encrypted_1/t_49
+innodb_encrypted_1/t_5
+innodb_encrypted_1/t_50
+innodb_encrypted_1/t_51
+innodb_encrypted_1/t_52
+innodb_encrypted_1/t_53
+innodb_encrypted_1/t_54
+innodb_encrypted_1/t_55
+innodb_encrypted_1/t_56
+innodb_encrypted_1/t_57
+innodb_encrypted_1/t_58
+innodb_encrypted_1/t_59
+innodb_encrypted_1/t_6
+innodb_encrypted_1/t_60
+innodb_encrypted_1/t_61
+innodb_encrypted_1/t_62
+innodb_encrypted_1/t_63
+innodb_encrypted_1/t_64
+innodb_encrypted_1/t_65
+innodb_encrypted_1/t_66
+innodb_encrypted_1/t_67
+innodb_encrypted_1/t_68
+innodb_encrypted_1/t_69
+innodb_encrypted_1/t_7
+innodb_encrypted_1/t_70
+innodb_encrypted_1/t_71
+innodb_encrypted_1/t_72
+innodb_encrypted_1/t_73
+innodb_encrypted_1/t_74
+innodb_encrypted_1/t_75
+innodb_encrypted_1/t_76
+innodb_encrypted_1/t_77
+innodb_encrypted_1/t_78
+innodb_encrypted_1/t_79
+innodb_encrypted_1/t_8
+innodb_encrypted_1/t_80
+innodb_encrypted_1/t_81
+innodb_encrypted_1/t_82
+innodb_encrypted_1/t_83
+innodb_encrypted_1/t_84
+innodb_encrypted_1/t_85
+innodb_encrypted_1/t_86
+innodb_encrypted_1/t_87
+innodb_encrypted_1/t_88
+innodb_encrypted_1/t_89
+innodb_encrypted_1/t_9
+innodb_encrypted_1/t_90
+innodb_encrypted_1/t_91
+innodb_encrypted_1/t_92
+innodb_encrypted_1/t_93
+innodb_encrypted_1/t_94
+innodb_encrypted_1/t_95
+innodb_encrypted_1/t_96
+innodb_encrypted_1/t_97
+innodb_encrypted_1/t_98
+innodb_encrypted_1/t_99
 innodb_encrypted_3/t_1
 innodb_encrypted_3/t_10
 innodb_encrypted_3/t_100
@@ -755,6 +1255,106 @@ use innodb_encrypted_2;
 use innodb_encrypted_3;
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
 NAME
+innodb_encrypted_1/t_1
+innodb_encrypted_1/t_10
+innodb_encrypted_1/t_100
+innodb_encrypted_1/t_11
+innodb_encrypted_1/t_12
+innodb_encrypted_1/t_13
+innodb_encrypted_1/t_14
+innodb_encrypted_1/t_15
+innodb_encrypted_1/t_16
+innodb_encrypted_1/t_17
+innodb_encrypted_1/t_18
+innodb_encrypted_1/t_19
+innodb_encrypted_1/t_2
+innodb_encrypted_1/t_20
+innodb_encrypted_1/t_21
+innodb_encrypted_1/t_22
+innodb_encrypted_1/t_23
+innodb_encrypted_1/t_24
+innodb_encrypted_1/t_25
+innodb_encrypted_1/t_26
+innodb_encrypted_1/t_27
+innodb_encrypted_1/t_28
+innodb_encrypted_1/t_29
+innodb_encrypted_1/t_3
+innodb_encrypted_1/t_30
+innodb_encrypted_1/t_31
+innodb_encrypted_1/t_32
+innodb_encrypted_1/t_33
+innodb_encrypted_1/t_34
+innodb_encrypted_1/t_35
+innodb_encrypted_1/t_36
+innodb_encrypted_1/t_37
+innodb_encrypted_1/t_38
+innodb_encrypted_1/t_39
+innodb_encrypted_1/t_4
+innodb_encrypted_1/t_40
+innodb_encrypted_1/t_41
+innodb_encrypted_1/t_42
+innodb_encrypted_1/t_43
+innodb_encrypted_1/t_44
+innodb_encrypted_1/t_45
+innodb_encrypted_1/t_46
+innodb_encrypted_1/t_47
+innodb_encrypted_1/t_48
+innodb_encrypted_1/t_49
+innodb_encrypted_1/t_5
+innodb_encrypted_1/t_50
+innodb_encrypted_1/t_51
+innodb_encrypted_1/t_52
+innodb_encrypted_1/t_53
+innodb_encrypted_1/t_54
+innodb_encrypted_1/t_55
+innodb_encrypted_1/t_56
+innodb_encrypted_1/t_57
+innodb_encrypted_1/t_58
+innodb_encrypted_1/t_59
+innodb_encrypted_1/t_6
+innodb_encrypted_1/t_60
+innodb_encrypted_1/t_61
+innodb_encrypted_1/t_62
+innodb_encrypted_1/t_63
+innodb_encrypted_1/t_64
+innodb_encrypted_1/t_65
+innodb_encrypted_1/t_66
+innodb_encrypted_1/t_67
+innodb_encrypted_1/t_68
+innodb_encrypted_1/t_69
+innodb_encrypted_1/t_7
+innodb_encrypted_1/t_70
+innodb_encrypted_1/t_71
+innodb_encrypted_1/t_72
+innodb_encrypted_1/t_73
+innodb_encrypted_1/t_74
+innodb_encrypted_1/t_75
+innodb_encrypted_1/t_76
+innodb_encrypted_1/t_77
+innodb_encrypted_1/t_78
+innodb_encrypted_1/t_79
+innodb_encrypted_1/t_8
+innodb_encrypted_1/t_80
+innodb_encrypted_1/t_81
+innodb_encrypted_1/t_82
+innodb_encrypted_1/t_83
+innodb_encrypted_1/t_84
+innodb_encrypted_1/t_85
+innodb_encrypted_1/t_86
+innodb_encrypted_1/t_87
+innodb_encrypted_1/t_88
+innodb_encrypted_1/t_89
+innodb_encrypted_1/t_9
+innodb_encrypted_1/t_90
+innodb_encrypted_1/t_91
+innodb_encrypted_1/t_92
+innodb_encrypted_1/t_93
+innodb_encrypted_1/t_94
+innodb_encrypted_1/t_95
+innodb_encrypted_1/t_96
+innodb_encrypted_1/t_97
+innodb_encrypted_1/t_98
+innodb_encrypted_1/t_99
 innodb_encrypted_3/t_1
 innodb_encrypted_3/t_10
 innodb_encrypted_3/t_100
@@ -1063,6 +1663,106 @@ innodb_encrypted_2/t_98
 innodb_encrypted_2/t_99
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
 NAME
+innodb_encrypted_1/t_1
+innodb_encrypted_1/t_10
+innodb_encrypted_1/t_100
+innodb_encrypted_1/t_11
+innodb_encrypted_1/t_12
+innodb_encrypted_1/t_13
+innodb_encrypted_1/t_14
+innodb_encrypted_1/t_15
+innodb_encrypted_1/t_16
+innodb_encrypted_1/t_17
+innodb_encrypted_1/t_18
+innodb_encrypted_1/t_19
+innodb_encrypted_1/t_2
+innodb_encrypted_1/t_20
+innodb_encrypted_1/t_21
+innodb_encrypted_1/t_22
+innodb_encrypted_1/t_23
+innodb_encrypted_1/t_24
+innodb_encrypted_1/t_25
+innodb_encrypted_1/t_26
+innodb_encrypted_1/t_27
+innodb_encrypted_1/t_28
+innodb_encrypted_1/t_29
+innodb_encrypted_1/t_3
+innodb_encrypted_1/t_30
+innodb_encrypted_1/t_31
+innodb_encrypted_1/t_32
+innodb_encrypted_1/t_33
+innodb_encrypted_1/t_34
+innodb_encrypted_1/t_35
+innodb_encrypted_1/t_36
+innodb_encrypted_1/t_37
+innodb_encrypted_1/t_38
+innodb_encrypted_1/t_39
+innodb_encrypted_1/t_4
+innodb_encrypted_1/t_40
+innodb_encrypted_1/t_41
+innodb_encrypted_1/t_42
+innodb_encrypted_1/t_43
+innodb_encrypted_1/t_44
+innodb_encrypted_1/t_45
+innodb_encrypted_1/t_46
+innodb_encrypted_1/t_47
+innodb_encrypted_1/t_48
+innodb_encrypted_1/t_49
+innodb_encrypted_1/t_5
+innodb_encrypted_1/t_50
+innodb_encrypted_1/t_51
+innodb_encrypted_1/t_52
+innodb_encrypted_1/t_53
+innodb_encrypted_1/t_54
+innodb_encrypted_1/t_55
+innodb_encrypted_1/t_56
+innodb_encrypted_1/t_57
+innodb_encrypted_1/t_58
+innodb_encrypted_1/t_59
+innodb_encrypted_1/t_6
+innodb_encrypted_1/t_60
+innodb_encrypted_1/t_61
+innodb_encrypted_1/t_62
+innodb_encrypted_1/t_63
+innodb_encrypted_1/t_64
+innodb_encrypted_1/t_65
+innodb_encrypted_1/t_66
+innodb_encrypted_1/t_67
+innodb_encrypted_1/t_68
+innodb_encrypted_1/t_69
+innodb_encrypted_1/t_7
+innodb_encrypted_1/t_70
+innodb_encrypted_1/t_71
+innodb_encrypted_1/t_72
+innodb_encrypted_1/t_73
+innodb_encrypted_1/t_74
+innodb_encrypted_1/t_75
+innodb_encrypted_1/t_76
+innodb_encrypted_1/t_77
+innodb_encrypted_1/t_78
+innodb_encrypted_1/t_79
+innodb_encrypted_1/t_8
+innodb_encrypted_1/t_80
+innodb_encrypted_1/t_81
+innodb_encrypted_1/t_82
+innodb_encrypted_1/t_83
+innodb_encrypted_1/t_84
+innodb_encrypted_1/t_85
+innodb_encrypted_1/t_86
+innodb_encrypted_1/t_87
+innodb_encrypted_1/t_88
+innodb_encrypted_1/t_89
+innodb_encrypted_1/t_9
+innodb_encrypted_1/t_90
+innodb_encrypted_1/t_91
+innodb_encrypted_1/t_92
+innodb_encrypted_1/t_93
+innodb_encrypted_1/t_94
+innodb_encrypted_1/t_95
+innodb_encrypted_1/t_96
+innodb_encrypted_1/t_97
+innodb_encrypted_1/t_98
+innodb_encrypted_1/t_99
 innodb_encrypted_3/t_1
 innodb_encrypted_3/t_10
 innodb_encrypted_3/t_100
diff --git a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
index 3167cee4b4b..dffabaf97f1 100644
--- a/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
+++ b/mysql-test/suite/encryption/t/innodb-key-rotation-disable.test
@@ -38,11 +38,6 @@ SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABL
 --echo # should list tables t7-t9
 SELECT NAME,ENCRYPTION_SCHEME,CURRENT_KEY_ID FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 and NAME LIKE 'enctests%';
 
---error ER_WRONG_VALUE_FOR_VAR
-SET GLOBAL innodb_encrypt_tables=OFF;
---error ER_WRONG_VALUE_FOR_VAR
-SET GLOBAL innodb_encrypt_tables=ON;
-
 --let $MYSQLD_DATADIR=`select @@datadir`
 
 -- source include/shutdown_mysqld.inc
diff --git a/mysql-test/suite/encryption/t/innodb_lotoftables.test b/mysql-test/suite/encryption/t/innodb_lotoftables.test
index 413fc8685f2..1b818bc0b84 100644
--- a/mysql-test/suite/encryption/t/innodb_lotoftables.test
+++ b/mysql-test/suite/encryption/t/innodb_lotoftables.test
@@ -46,8 +46,8 @@ commit work;
 #
 # Verify
 #
---echo # should be empty
-SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'innodb_encrypted%';
+--echo # All 100 tables are not encrypted
+SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE NAME LIKE 'innodb_encrypted%' and MIN_KEY_VERSION = 0;
 
 #
 # This will create 100 tables that are encrypted always
@@ -81,7 +81,7 @@ set autocommit=1;
 #
 --echo # should contain 100 tables
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION <> 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
---echo # should contain 0 tables
+--echo # should contain 100 tables
 SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0 AND NAME LIKE 'innodb_encrypted%' ORDER BY NAME;
 
 #
diff --git a/storage/innobase/handler/i_s.cc b/storage/innobase/handler/i_s.cc
index 52f1ace7e37..948e5f2fab7 100644
--- a/storage/innobase/handler/i_s.cc
+++ b/storage/innobase/handler/i_s.cc
@@ -8425,7 +8425,7 @@ static ST_FIELD_INFO	innodb_tablespaces_encryption_fields_info[] =
 	 STRUCT_FLD(field_length,	MY_INT32_NUM_DECIMAL_DIGITS),
 	 STRUCT_FLD(field_type,		MYSQL_TYPE_LONG),
 	 STRUCT_FLD(value,		0),
-	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED),
+	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED | MY_I_S_MAYBE_NULL),
 	 STRUCT_FLD(old_name,		""),
 	 STRUCT_FLD(open_method,	SKIP_OPEN_TABLE)},
 
@@ -8434,7 +8434,7 @@ static ST_FIELD_INFO	innodb_tablespaces_encryption_fields_info[] =
 	 STRUCT_FLD(field_length,	MY_INT32_NUM_DECIMAL_DIGITS),
 	 STRUCT_FLD(field_type,		MYSQL_TYPE_LONG),
 	 STRUCT_FLD(value,		0),
-	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED),
+	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED | MY_I_S_MAYBE_NULL),
 	 STRUCT_FLD(old_name,		""),
 	 STRUCT_FLD(open_method,	SKIP_OPEN_TABLE)},
 
@@ -8443,7 +8443,7 @@ static ST_FIELD_INFO	innodb_tablespaces_encryption_fields_info[] =
 	 STRUCT_FLD(field_length,	MY_INT32_NUM_DECIMAL_DIGITS),
 	 STRUCT_FLD(field_type,		MYSQL_TYPE_LONG),
 	 STRUCT_FLD(value,		0),
-	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED),
+	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED | MY_I_S_MAYBE_NULL),
 	 STRUCT_FLD(old_name,		""),
 	 STRUCT_FLD(open_method,	SKIP_OPEN_TABLE)},
 
@@ -8452,7 +8452,7 @@ static ST_FIELD_INFO	innodb_tablespaces_encryption_fields_info[] =
 	 STRUCT_FLD(field_length,	MY_INT32_NUM_DECIMAL_DIGITS),
 	 STRUCT_FLD(field_type,		MYSQL_TYPE_LONG),
 	 STRUCT_FLD(value,		0),
-	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED),
+	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED | MY_I_S_MAYBE_NULL),
 	 STRUCT_FLD(old_name,		""),
 	 STRUCT_FLD(open_method,	SKIP_OPEN_TABLE)},
 
@@ -8479,7 +8479,7 @@ static ST_FIELD_INFO	innodb_tablespaces_encryption_fields_info[] =
 	 STRUCT_FLD(field_length,	MY_INT32_NUM_DECIMAL_DIGITS),
 	 STRUCT_FLD(field_type,		MYSQL_TYPE_LONG),
 	 STRUCT_FLD(value,		0),
-	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED),
+	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED | MY_I_S_MAYBE_NULL),
 	 STRUCT_FLD(old_name,		""),
 	 STRUCT_FLD(open_method,	SKIP_OPEN_TABLE)},
 
@@ -8488,7 +8488,7 @@ static ST_FIELD_INFO	innodb_tablespaces_encryption_fields_info[] =
 	 STRUCT_FLD(field_length,	1),
 	 STRUCT_FLD(field_type,		MYSQL_TYPE_LONG),
 	 STRUCT_FLD(value,		0),
-	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED),
+	 STRUCT_FLD(field_flags,	MY_I_S_UNSIGNED | MY_I_S_MAYBE_NULL),
 	 STRUCT_FLD(old_name,		""),
 	 STRUCT_FLD(open_method,	SKIP_OPEN_TABLE)},
 
@@ -8516,19 +8516,34 @@ i_s_dict_fill_tablespaces_encryption(
 
 	fields = table_to_fill->field;
 
-	fil_space_crypt_get_status(space, &status);
-
-	/* If tablespace id does not match, we did not find
-	encryption information for this tablespace. */
-	if (!space->crypt_data || space->id != status.space) {
-		goto skip;
-	}
-
 	OK(fields[TABLESPACES_ENCRYPTION_SPACE]->store(space->id, true));
 
 	OK(field_store_string(fields[TABLESPACES_ENCRYPTION_NAME],
 			      space->name));
 
+	if (space->size == 0 && space->crypt_data == NULL) {
+		fields[TABLESPACES_ENCRYPTION_ENCRYPTION_SCHEME]->set_null();
+		fields[TABLESPACES_ENCRYPTION_MIN_KEY_VERSION]->set_null();
+		fields[TABLESPACES_ENCRYPTION_KEYSERVER_REQUESTS]->set_null();
+		fields[TABLESPACES_ENCRYPTION_CURRENT_KEY_VERSION]->set_null();
+		fields[TABLESPACES_ENCRYPTION_CURRENT_KEY_ID]->set_null();
+		fields[TABLESPACES_ENCRYPTION_ROTATING_OR_FLUSHING]->set_null();
+		fields[TABLESPACES_ENCRYPTION_KEY_ROTATION_PAGE_NUMBER]
+			->set_null();
+		fields[TABLESPACES_ENCRYPTION_KEY_ROTATION_MAX_PAGE_NUMBER]
+			->set_null();
+		goto fill;
+	}
+
+	fil_space_crypt_get_status(space, &status);
+
+	fields[TABLESPACES_ENCRYPTION_ENCRYPTION_SCHEME]->set_notnull();
+	fields[TABLESPACES_ENCRYPTION_MIN_KEY_VERSION]->set_notnull();
+	fields[TABLESPACES_ENCRYPTION_KEYSERVER_REQUESTS]->set_notnull();
+	fields[TABLESPACES_ENCRYPTION_CURRENT_KEY_VERSION]->set_notnull();
+	fields[TABLESPACES_ENCRYPTION_CURRENT_KEY_ID]->set_notnull();
+	fields[TABLESPACES_ENCRYPTION_ROTATING_OR_FLUSHING]->set_notnull();
+
 	OK(fields[TABLESPACES_ENCRYPTION_ENCRYPTION_SCHEME]->store(
 		   status.scheme, true));
 	OK(fields[TABLESPACES_ENCRYPTION_KEYSERVER_REQUESTS]->store(
@@ -8556,11 +8571,10 @@ i_s_dict_fill_tablespaces_encryption(
 			->set_null();
 	}
 
-	OK(schema_table_store_record(thd, table_to_fill));
-
-skip:
-	DBUG_RETURN(0);
+fill:
+	DBUG_RETURN(schema_table_store_record(thd, table_to_fill));
 }
+
 /*******************************************************************//**
 Function to populate INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION table.
 Loop through each record in TABLESPACES_ENCRYPTION, and extract the column