Debian Codegraph @

mysql-test/suite/encryption/r/innodb_encryption-page-compression.result

raw
1 SET GLOBAL innodb_encryption_threads = 4;
2 set global innodb_compression_algorithm = 1;
3 create table innodb_normal (c1 int, b char(20)) engine=innodb;
4 show warnings;
5 Level Code Message
6 create table innodb_page_compressed1 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=1;
7 show warnings;
8 Level Code Message
9 show create table innodb_page_compressed1;
10 Table Create Table
11 innodb_page_compressed1 CREATE TABLE `innodb_page_compressed1` (
12 `c1` int(11) DEFAULT NULL,
13 `b` char(20) DEFAULT NULL
14 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=1
15 create table innodb_page_compressed2 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=2;
16 show warnings;
17 Level Code Message
18 show create table innodb_page_compressed2;
19 Table Create Table
20 innodb_page_compressed2 CREATE TABLE `innodb_page_compressed2` (
21 `c1` int(11) DEFAULT NULL,
22 `b` char(20) DEFAULT NULL
23 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=2
24 create table innodb_page_compressed3 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=3;
25 show warnings;
26 Level Code Message
27 show create table innodb_page_compressed3;
28 Table Create Table
29 innodb_page_compressed3 CREATE TABLE `innodb_page_compressed3` (
30 `c1` int(11) DEFAULT NULL,
31 `b` char(20) DEFAULT NULL
32 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=3
33 create table innodb_page_compressed4 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=4;
34 show warnings;
35 Level Code Message
36 show create table innodb_page_compressed4;
37 Table Create Table
38 innodb_page_compressed4 CREATE TABLE `innodb_page_compressed4` (
39 `c1` int(11) DEFAULT NULL,
40 `b` char(20) DEFAULT NULL
41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=4
42 create table innodb_page_compressed5 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=5;
43 show warnings;
44 Level Code Message
45 show create table innodb_page_compressed5;
46 Table Create Table
47 innodb_page_compressed5 CREATE TABLE `innodb_page_compressed5` (
48 `c1` int(11) DEFAULT NULL,
49 `b` char(20) DEFAULT NULL
50 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=5
51 create table innodb_page_compressed6 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=6;
52 show warnings;
53 Level Code Message
54 show create table innodb_page_compressed6;
55 Table Create Table
56 innodb_page_compressed6 CREATE TABLE `innodb_page_compressed6` (
57 `c1` int(11) DEFAULT NULL,
58 `b` char(20) DEFAULT NULL
59 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=6
60 create table innodb_page_compressed7 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=7;
61 show warnings;
62 Level Code Message
63 show create table innodb_page_compressed7;
64 Table Create Table
65 innodb_page_compressed7 CREATE TABLE `innodb_page_compressed7` (
66 `c1` int(11) DEFAULT NULL,
67 `b` char(20) DEFAULT NULL
68 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=7
69 create table innodb_page_compressed8 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=8;
70 show warnings;
71 Level Code Message
72 show create table innodb_page_compressed8;
73 Table Create Table
74 innodb_page_compressed8 CREATE TABLE `innodb_page_compressed8` (
75 `c1` int(11) DEFAULT NULL,
76 `b` char(20) DEFAULT NULL
77 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=8
78 create table innodb_page_compressed9 (c1 int, b char(20)) engine=innodb page_compressed=1 page_compression_level=9;
79 show warnings;
80 Level Code Message
81 show create table innodb_page_compressed9;
82 Table Create Table
83 innodb_page_compressed9 CREATE TABLE `innodb_page_compressed9` (
84 `c1` int(11) DEFAULT NULL,
85 `b` char(20) DEFAULT NULL
86 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci `page_compressed`=1 `page_compression_level`=9
87 create procedure innodb_insert_proc (repeat_count int)
88 begin
89 declare current_num int;
90 set current_num = 0;
91 while current_num < repeat_count do
92 insert into innodb_normal values(current_num,'testing..');
93 set current_num = current_num + 1;
94 end while;
95 end//
96 commit;
97 begin;
98 call innodb_insert_proc(2000);
99 insert into innodb_page_compressed1 select * from innodb_normal;
100 insert into innodb_page_compressed2 select * from innodb_normal;
101 insert into innodb_page_compressed3 select * from innodb_normal;
102 insert into innodb_page_compressed4 select * from innodb_normal;
103 insert into innodb_page_compressed5 select * from innodb_normal;
104 insert into innodb_page_compressed6 select * from innodb_normal;
105 insert into innodb_page_compressed7 select * from innodb_normal;
106 insert into innodb_page_compressed8 select * from innodb_normal;
107 insert into innodb_page_compressed9 select * from innodb_normal;
108 commit;
109 select count(*) from innodb_page_compressed1 where c1 < 500000;
110 count(*)
111 2000
112 select count(*) from innodb_page_compressed2 where c1 < 500000;
113 count(*)
114 2000
115 select count(*) from innodb_page_compressed3 where c1 < 500000;
116 count(*)
117 2000
118 select count(*) from innodb_page_compressed4 where c1 < 500000;
119 count(*)
120 2000
121 select count(*) from innodb_page_compressed5 where c1 < 500000;
122 count(*)
123 2000
124 select count(*) from innodb_page_compressed6 where c1 < 500000;
125 count(*)
126 2000
127 select count(*) from innodb_page_compressed7 where c1 < 500000;
128 count(*)
129 2000
130 select count(*) from innodb_page_compressed8 where c1 < 500000;
131 count(*)
132 2000
133 select count(*) from innodb_page_compressed9 where c1 < 500000;
134 count(*)
135 2000
136 flush tables innodb_page_compressed1, innodb_page_compressed2,
137 innodb_page_compressed3, innodb_page_compressed4,
138 innodb_page_compressed5, innodb_page_compressed6,
139 innodb_page_compressed7, innodb_page_compressed8,
140 innodb_page_compressed9 for export;
141 unlock tables;
142 # Wait until dirty pages are compressed and encrypted
143 SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_encrypted';
144 variable_value > 0
145 1
146 SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed';
147 variable_value > 0
148 1
149 # restart
150 SET GLOBAL innodb_encryption_threads = 4;
151 SET GLOBAL innodb_encrypt_tables = off;
152 update innodb_page_compressed1 set c1 = c1 + 1;
153 update innodb_page_compressed2 set c1 = c1 + 1;
154 update innodb_page_compressed3 set c1 = c1 + 1;
155 update innodb_page_compressed4 set c1 = c1 + 1;
156 update innodb_page_compressed5 set c1 = c1 + 1;
157 update innodb_page_compressed6 set c1 = c1 + 1;
158 update innodb_page_compressed7 set c1 = c1 + 1;
159 update innodb_page_compressed8 set c1 = c1 + 1;
160 update innodb_page_compressed9 set c1 = c1 + 1;
161 flush tables innodb_page_compressed1, innodb_page_compressed2,
162 innodb_page_compressed3, innodb_page_compressed4,
163 innodb_page_compressed5, innodb_page_compressed6,
164 innodb_page_compressed7, innodb_page_compressed8,
165 innodb_page_compressed9 for export;
166 unlock tables;
167 # Wait until dirty pages are compressed and encrypted 2
168 SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_decrypted';
169 variable_value > 0
170 1
171 SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_compressed';
172 variable_value > 0
173 1
174 SELECT variable_value > 0 FROM information_schema.global_status WHERE variable_name = 'innodb_num_pages_page_decompressed';
175 variable_value > 0
176 1
177 drop procedure innodb_insert_proc;
178 drop table innodb_normal;
179 drop table innodb_page_compressed1;
180 drop table innodb_page_compressed2;
181 drop table innodb_page_compressed3;
182 drop table innodb_page_compressed4;
183 drop table innodb_page_compressed5;
184 drop table innodb_page_compressed6;
185 drop table innodb_page_compressed7;
186 drop table innodb_page_compressed8;
187 drop table innodb_page_compressed9;