Debian Codegraph @

mysql-test/suite/encryption/r/innodb-page_encryption.result

raw
1 create table innodb_normal(c1 bigint not null, b char(200)) engine=innodb;
2 show warnings;
3 Level Code Message
4 create table innodb_compact(c1 bigint not null, b char(200)) engine=innodb row_format=compact encrypted=yes encryption_key_id=1;
5 show warnings;
6 Level Code Message
7 create table innodb_compressed(c1 bigint not null, b char(200)) engine=innodb row_format=compressed encrypted=yes encryption_key_id=2;
8 show warnings;
9 Level Code Message
10 create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=3;
11 ERROR HY000: Can't create table `test`.`innodb_dynamic` (errno: 140 "Wrong create options")
12 show warnings;
13 Level Code Message
14 Warning 140 InnoDB: ENCRYPTION_KEY_ID 3 not available
15 Error 1005 Can't create table `test`.`innodb_dynamic` (errno: 140 "Wrong create options")
16 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
17 create table innodb_dynamic(c1 bigint not null, b char(200)) engine=innodb row_format=dynamic encrypted=yes encryption_key_id=33;
18 show warnings;
19 Level Code Message
20 create table innodb_redundant(c1 bigint not null, b char(200)) engine=innodb row_format=redundant encrypted=yes encryption_key_id=4;
21 show warnings;
22 Level Code Message
23 set innodb_default_encryption_key_id = 5;
24 create table innodb_defkey(c1 bigint not null, b char(200)) engine=innodb encrypted=yes;
25 show create table innodb_defkey;
26 Table Create Table
27 innodb_defkey CREATE TABLE `innodb_defkey` (
28 `c1` bigint(20) NOT NULL,
29 `b` char(200) DEFAULT NULL
30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `encrypted`=yes `ENCRYPTION_KEY_ID`=5
31 show create table innodb_compact;
32 Table Create Table
33 innodb_compact CREATE TABLE `innodb_compact` (
34 `c1` bigint(20) NOT NULL,
35 `b` char(200) DEFAULT NULL
36 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPACT `encrypted`=yes `encryption_key_id`=1
37 show create table innodb_compressed;
38 Table Create Table
39 innodb_compressed CREATE TABLE `innodb_compressed` (
40 `c1` bigint(20) NOT NULL,
41 `b` char(200) DEFAULT NULL
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED `encrypted`=yes `encryption_key_id`=2
43 show create table innodb_dynamic;
44 Table Create Table
45 innodb_dynamic CREATE TABLE `innodb_dynamic` (
46 `c1` bigint(20) NOT NULL,
47 `b` char(200) DEFAULT NULL
48 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC `encrypted`=yes `encryption_key_id`=33
49 show create table innodb_redundant;
50 Table Create Table
51 innodb_redundant CREATE TABLE `innodb_redundant` (
52 `c1` bigint(20) NOT NULL,
53 `b` char(200) DEFAULT NULL
54 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=REDUNDANT `encrypted`=yes `encryption_key_id`=4
55 create procedure innodb_insert_proc (repeat_count int)
56 begin
57 declare current_num int;
58 set current_num = 0;
59 while current_num < repeat_count do
60 insert into innodb_normal values(current_num, substring(MD5(RAND()), -64));
61 set current_num = current_num + 1;
62 end while;
63 end//
64 commit;
65 set autocommit=0;
66 call innodb_insert_proc(2000);
67 commit;
68 set autocommit=1;
69 insert into innodb_compact select * from innodb_normal;
70 insert into innodb_compressed select * from innodb_normal;
71 insert into innodb_dynamic select * from innodb_normal;
72 insert into innodb_redundant select * from innodb_normal;
73 insert into innodb_defkey select * from innodb_normal;
74 update innodb_normal set c1 = c1 +1;
75 update innodb_compact set c1 = c1 + 1;
76 update innodb_compressed set c1 = c1 + 1;
77 update innodb_dynamic set c1 = c1 + 1;
78 update innodb_redundant set c1 = c1 + 1;
79 update innodb_defkey set c1 = c1 + 1;
80 select count(*) from innodb_compact where c1 < 1500000;
81 count(*)
82 2000
83 select count(*) from innodb_compressed where c1 < 1500000;
84 count(*)
85 2000
86 select count(*) from innodb_dynamic where c1 < 1500000;
87 count(*)
88 2000
89 select count(*) from innodb_redundant where c1 < 1500000;
90 count(*)
91 2000
92 select count(*) from innodb_defkey where c1 < 1500000;
93 count(*)
94 2000
95 select count(*) from innodb_compact t1, innodb_normal t2 where
96 t1.c1 = t2.c1 and t1.b = t2.b;
97 count(*)
98 2000
99 select count(*) from innodb_dynamic t1, innodb_normal t2 where
100 t1.c1 = t2.c1 and t1.b = t2.b;
101 count(*)
102 2000
103 select count(*) from innodb_compressed t1, innodb_normal t2 where
104 t1.c1 = t2.c1 and t1.b = t2.b;
105 count(*)
106 2000
107 select count(*) from innodb_redundant t1, innodb_normal t2 where
108 t1.c1 = t2.c1 and t1.b = t2.b;
109 count(*)
110 2000
111 select count(*) from innodb_defkey t1, innodb_normal t2 where
112 t1.c1 = t2.c1 and t1.b = t2.b;
113 count(*)
114 2000
115 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
116 variable_value >= 0
117 1
118 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
119 variable_value >= 0
120 1
121 # restart
122 update innodb_normal set c1 = c1 +1;
123 update innodb_compact set c1 = c1 + 1;
124 update innodb_compressed set c1 = c1 + 1;
125 update innodb_dynamic set c1 = c1 + 1;
126 update innodb_redundant set c1 = c1 + 1;
127 update innodb_defkey set c1 = c1 + 1;
128 select count(*) from innodb_compact where c1 < 1500000;
129 count(*)
130 2000
131 select count(*) from innodb_compressed where c1 < 1500000;
132 count(*)
133 2000
134 select count(*) from innodb_dynamic where c1 < 1500000;
135 count(*)
136 2000
137 select count(*) from innodb_redundant where c1 < 1500000;
138 count(*)
139 2000
140 select count(*) from innodb_defkey where c1 < 1500000;
141 count(*)
142 2000
143 select count(*) from innodb_compact t1, innodb_normal t2 where
144 t1.c1 = t2.c1 and t1.b = t2.b;
145 count(*)
146 2000
147 select count(*) from innodb_dynamic t1, innodb_normal t2 where
148 t1.c1 = t2.c1 and t1.b = t2.b;
149 count(*)
150 2000
151 select count(*) from innodb_compressed t1, innodb_normal t2 where
152 t1.c1 = t2.c1 and t1.b = t2.b;
153 count(*)
154 2000
155 select count(*) from innodb_redundant t1, innodb_normal t2 where
156 t1.c1 = t2.c1 and t1.b = t2.b;
157 count(*)
158 2000
159 select count(*) from innodb_defkey t1, innodb_normal t2 where
160 t1.c1 = t2.c1 and t1.b = t2.b;
161 count(*)
162 2000
163 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
164 variable_value >= 0
165 1
166 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
167 variable_value >= 0
168 1
169 alter table innodb_compact engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
170 show create table innodb_compact;
171 Table Create Table
172 innodb_compact CREATE TABLE `innodb_compact` (
173 `c1` bigint(20) NOT NULL,
174 `b` char(200) DEFAULT NULL
175 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPACT
176 alter table innodb_compressed engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
177 show create table innodb_compressed;
178 Table Create Table
179 innodb_compressed CREATE TABLE `innodb_compressed` (
180 `c1` bigint(20) NOT NULL,
181 `b` char(200) DEFAULT NULL
182 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED
183 alter table innodb_dynamic engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
184 show create table innodb_dynamic;
185 Table Create Table
186 innodb_dynamic CREATE TABLE `innodb_dynamic` (
187 `c1` bigint(20) NOT NULL,
188 `b` char(200) DEFAULT NULL
189 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
190 alter table innodb_redundant engine=innodb encrypted=DEFAULT encryption_key_id=DEFAULT;
191 show create table innodb_redundant;
192 Table Create Table
193 innodb_redundant CREATE TABLE `innodb_redundant` (
194 `c1` bigint(20) NOT NULL,
195 `b` char(200) DEFAULT NULL
196 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=REDUNDANT
197 # restart
198 show create table innodb_compact;
199 Table Create Table
200 innodb_compact CREATE TABLE `innodb_compact` (
201 `c1` bigint(20) NOT NULL,
202 `b` char(200) DEFAULT NULL
203 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPACT
204 show create table innodb_compressed;
205 Table Create Table
206 innodb_compressed CREATE TABLE `innodb_compressed` (
207 `c1` bigint(20) NOT NULL,
208 `b` char(200) DEFAULT NULL
209 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=COMPRESSED
210 show create table innodb_dynamic;
211 Table Create Table
212 innodb_dynamic CREATE TABLE `innodb_dynamic` (
213 `c1` bigint(20) NOT NULL,
214 `b` char(200) DEFAULT NULL
215 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=DYNAMIC
216 show create table innodb_redundant;
217 Table Create Table
218 innodb_redundant CREATE TABLE `innodb_redundant` (
219 `c1` bigint(20) NOT NULL,
220 `b` char(200) DEFAULT NULL
221 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci ROW_FORMAT=REDUNDANT
222 show create table innodb_defkey;
223 Table Create Table
224 innodb_defkey CREATE TABLE `innodb_defkey` (
225 `c1` bigint(20) NOT NULL,
226 `b` char(200) DEFAULT NULL
227 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `encrypted`=yes `ENCRYPTION_KEY_ID`=5
228 update innodb_normal set c1 = c1 +1;
229 update innodb_compact set c1 = c1 + 1;
230 update innodb_compressed set c1 = c1 + 1;
231 update innodb_dynamic set c1 = c1 + 1;
232 update innodb_redundant set c1 = c1 + 1;
233 select count(*) from innodb_compact where c1 < 1500000;
234 count(*)
235 2000
236 select count(*) from innodb_compressed where c1 < 1500000;
237 count(*)
238 2000
239 select count(*) from innodb_dynamic where c1 < 1500000;
240 count(*)
241 2000
242 select count(*) from innodb_redundant where c1 < 1500000;
243 count(*)
244 2000
245 select count(*) from innodb_compact t1, innodb_normal t2 where
246 t1.c1 = t2.c1 and t1.b = t2.b;
247 count(*)
248 2000
249 select count(*) from innodb_dynamic t1, innodb_normal t2 where
250 t1.c1 = t2.c1 and t1.b = t2.b;
251 count(*)
252 2000
253 select count(*) from innodb_compressed t1, innodb_normal t2 where
254 t1.c1 = t2.c1 and t1.b = t2.b;
255 count(*)
256 2000
257 select count(*) from innodb_redundant t1, innodb_normal t2 where
258 t1.c1 = t2.c1 and t1.b = t2.b;
259 count(*)
260 2000
261 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
262 variable_value >= 0
263 1
264 SELECT variable_value >= 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
265 variable_value >= 0
266 1
267 drop procedure innodb_insert_proc;
268 drop table innodb_normal;
269 drop table innodb_compact;
270 drop table innodb_compressed;
271 drop table innodb_dynamic;
272 drop table innodb_redundant;
273 drop table innodb_defkey;