Debian Codegraph @

mysql-test/suite/encryption/t/innodb-page_encryption.test

raw
1 -- source include/have_innodb.inc
2 -- source include/have_file_key_management_plugin.inc
3
4 # The test can take very long time with valgrind
5 --source include/not_valgrind.inc
6
7 create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb;
8 show warnings;
9 create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact encrypted=yes encryption_key_id=1;
10 show warnings;
11 create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed encrypted=yes encryption_key_id=2;
12 show warnings;
13 --error ER_CANT_CREATE_TABLE
14 create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=3;
15 show warnings;
16 create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=33;
17 show warnings;
18 create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant encrypted=yes encryption_key_id=4;
19 show warnings;
20
21 set innodb_default_encryption_key_id = 5;
22 create table innodb_defkey(c1 bigint not null, b char(200)) engine=innodb encrypted=yes;
23 show create table innodb_defkey;
24
25 show create table innodb_compact;
26 show create table innodb_compressed;
27 show create table innodb_dynamic;
28 show create table innodb_redundant;
29
30 delimiter //;
31 create procedure innodb_insert_proc (repeat_count int)
32 begin
33 declare current_num int;
34 set current_num = 0;
35 while current_num < repeat_count do
36 insert into innodb_normal values(current_num, substring(MD5(RAND()), -64));
37 set current_num = current_num + 1;
38 end while;
39 end//
40 delimiter ;//
41 commit;
42
43 set autocommit=0;
44 call innodb_insert_proc(2000);
45 commit;
46 set autocommit=1;
47
48 insert into innodb_compact select * from innodb_normal;
49 insert into innodb_compressed select * from innodb_normal;
50 insert into innodb_dynamic select * from innodb_normal;
51 insert into innodb_redundant select * from innodb_normal;
52 insert into innodb_defkey select * from innodb_normal;
53
54 update innodb_normal set c1 = c1 +1;
55 update innodb_compact set c1 = c1 + 1;
56 update innodb_compressed set c1 = c1 + 1;
57 update innodb_dynamic set c1 = c1 + 1;
58 update innodb_redundant set c1 = c1 + 1;
59 update innodb_defkey set c1 = c1 + 1;
60
61 select count(*) from innodb_compact where c1 < 1500000;
62 select count(*) from innodb_compressed where c1 < 1500000;
63 select count(*) from innodb_dynamic where c1 < 1500000;
64 select count(*) from innodb_redundant where c1 < 1500000;
65 select count(*) from innodb_defkey where c1 < 1500000;
66 select count(*) from innodb_compact t1, innodb_normal t2 where
67 t1.c1 = t2.c1 and t1.b = t2.b;
68 select count(*) from innodb_dynamic t1, innodb_normal t2 where
69 t1.c1 = t2.c1 and t1.b = t2.b;
70 select count(*) from innodb_compressed t1, innodb_normal t2 where
71 t1.c1 = t2.c1 and t1.b = t2.b;
72 select count(*) from innodb_redundant t1, innodb_normal t2 where
73 t1.c1 = t2.c1 and t1.b = t2.b;
74 select count(*) from innodb_defkey t1, innodb_normal t2 where
75 t1.c1 = t2.c1 and t1.b = t2.b;
76
77 # Note there that these variables are updated only when real I/O is done, thus they are not reliable
78 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
79 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
80
81 --source include/restart_mysqld.inc
82
83 update innodb_normal set c1 = c1 +1;
84 update innodb_compact set c1 = c1 + 1;
85 update innodb_compressed set c1 = c1 + 1;
86 update innodb_dynamic set c1 = c1 + 1;
87 update innodb_redundant set c1 = c1 + 1;
88 update innodb_defkey set c1 = c1 + 1;
89 select count(*) from innodb_compact where c1 < 1500000;
90 select count(*) from innodb_compressed where c1 < 1500000;
91 select count(*) from innodb_dynamic where c1 < 1500000;
92 select count(*) from innodb_redundant where c1 < 1500000;
93 select count(*) from innodb_defkey where c1 < 1500000;
94 select count(*) from innodb_compact t1, innodb_normal t2 where
95 t1.c1 = t2.c1 and t1.b = t2.b;
96 select count(*) from innodb_dynamic t1, innodb_normal t2 where
97 t1.c1 = t2.c1 and t1.b = t2.b;
98 select count(*) from innodb_compressed t1, innodb_normal t2 where
99 t1.c1 = t2.c1 and t1.b = t2.b;
100 select count(*) from innodb_redundant t1, innodb_normal t2 where
101 t1.c1 = t2.c1 and t1.b = t2.b;
102 select count(*) from innodb_defkey t1, innodb_normal t2 where
103 t1.c1 = t2.c1 and t1.b = t2.b;
104
105 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
106 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
107
108 alter table innodb_compact engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
109 show create table innodb_compact;
110 alter table innodb_compressed engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
111 show create table innodb_compressed;
112 alter table innodb_dynamic engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
113 show create table innodb_dynamic;
114 alter table innodb_redundant engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
115 show create table innodb_redundant;
116
117 --source include/restart_mysqld.inc
118
119 show create table innodb_compact;
120 show create table innodb_compressed;
121 show create table innodb_dynamic;
122 show create table innodb_redundant;
123 show create table innodb_defkey;
124
125 update innodb_normal set c1 = c1 +1;
126 update innodb_compact set c1 = c1 + 1;
127 update innodb_compressed set c1 = c1 + 1;
128 update innodb_dynamic set c1 = c1 + 1;
129 update innodb_redundant set c1 = c1 + 1;
130 select count(*) from innodb_compact where c1 < 1500000;
131 select count(*) from innodb_compressed where c1 < 1500000;
132 select count(*) from innodb_dynamic where c1 < 1500000;
133 select count(*) from innodb_redundant where c1 < 1500000;
134 select count(*) from innodb_compact t1, innodb_normal t2 where
135 t1.c1 = t2.c1 and t1.b = t2.b;
136 select count(*) from innodb_dynamic t1, innodb_normal t2 where
137 t1.c1 = t2.c1 and t1.b = t2.b;
138 select count(*) from innodb_compressed t1, innodb_normal t2 where
139 t1.c1 = t2.c1 and t1.b = t2.b;
140 select count(*) from innodb_redundant t1, innodb_normal t2 where
141 t1.c1 = t2.c1 and t1.b = t2.b;
142
143 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
144 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
145
146 drop procedure innodb_insert_proc;
147 drop table innodb_normal;
148 drop table innodb_compact;
149 drop table innodb_compressed;
150 drop table innodb_dynamic;
151 drop table innodb_redundant;
152 drop table innodb_defkey;