Debian Codegraph @

mysql-test/suite/innodb/r/import_compress_encrypt.result

raw
1 #
2 # Bug#31313533 : IMPORT FAILS FOR ENCRYPT AND COMPRESSION ENABLED TDE TABLES
3 #
4 # SETUP
5 # RESTART WITH KEYRING PLUGIN
6
7 # Test 1 : Check that EXPORT and IMPORT is working fine on same FS
8
9 CREATE TABLE t1(c1 int NOT NULL AUTO_INCREMENT,
10 c2 varchar(65000) DEFAULT NULL,
11 c3 varchar(255) GENERATED ALWAYS AS (substr(c2,2,100)) STORED,
12 c4 varchar(255) GENERATED ALWAYS AS (substr(c2,10,200)) VIRTUAL,
13 b bit(64) DEFAULT NULL,
14 p_c1 bigint DEFAULT NULL,
15 PRIMARY KEY (c1)) ENGINE=Innodb AUTO_INCREMENT=50001 DEFAULT CHARSET=latin1 COMPRESSION='zlib' ENCRYPTION='Y';
16 SELECT c1, SUBSTRING(c2, 10, 10), SUBSTRING(c3, 10, 10), SUBSTRING(c4, 10, 10), b FROM t1 ORDER BY c1 limit 10;
17 c1 SUBSTRING(c2, 10, 10) SUBSTRING(c3, 10, 10) SUBSTRING(c4, 10, 10) b
18 50001 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
19
20 50002 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
21
22 50003 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
23
24 50004 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
25
26 50005 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
27
28 50006 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
29
30 50007 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
31
32 50008 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
33
34 50009 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
35
36 50010 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
37
38 # Flush tables for export
39 FLUSH TABLES t1 FOR EXPORT;
40 # Copy .cfp .cfg .ibd file to temp
41 UNLOCK TABLES;
42 DROP TABLE t1;
43 CREATE TABLE t1(c1 int NOT NULL AUTO_INCREMENT,
44 c2 varchar(65000) DEFAULT NULL,
45 c3 varchar(255) GENERATED ALWAYS AS (substr(c2,2,100)) STORED,
46 c4 varchar(255) GENERATED ALWAYS AS (substr(c2,10,200)) VIRTUAL,
47 b bit(64) DEFAULT NULL,
48 p_c1 bigint DEFAULT NULL,
49 PRIMARY KEY (c1)) ENGINE=Innodb AUTO_INCREMENT=50001 DEFAULT CHARSET=latin1 COMPRESSION='zlib' ENCRYPTION='Y';
50 ALTER TABLE t1 DISCARD TABLESPACE;
51 # Copy .cfp/.cfg and .ibd files from temp to datadir
52 # Start import
53 ALTER TABLE t1 IMPORT TABLESPACE;
54 SELECT c1, SUBSTRING(c2, 10, 10), SUBSTRING(c3, 10, 10), SUBSTRING(c4, 10, 10), b FROM t1 ORDER BY c1 limit 10;
55 c1 SUBSTRING(c2, 10, 10) SUBSTRING(c3, 10, 10) SUBSTRING(c4, 10, 10) b
56 50001 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
57
58 50002 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
59
60 50003 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
61
62 50004 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
63
64 50005 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
65
66 50006 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
67
68 50007 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
69
70 50008 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
71
72 50009 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
73
74 50010 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
75
76 # Cleanup
77 DROP TABLE t1;
78
79 # Test 2 : Check that EXPORT and IMPORT is working fine on different FS
80
81 # Copy and unzip the dir having cfg/cfg/ibd file from a different FS Block Size
82 CREATE TABLE t1(c1 int NOT NULL AUTO_INCREMENT,
83 c2 varchar(65000) DEFAULT NULL,
84 c3 varchar(255) GENERATED ALWAYS AS (substr(c2,2,100)) STORED,
85 c4 varchar(255) GENERATED ALWAYS AS (substr(c2,10,200)) VIRTUAL,
86 b bit(64) DEFAULT NULL,
87 p_c1 bigint DEFAULT NULL,
88 PRIMARY KEY (c1)) ENGINE=Innodb AUTO_INCREMENT=50001 DEFAULT CHARSET=latin1 COMPRESSION='zlib' ENCRYPTION='Y';
89 ALTER TABLE t1 DISCARD TABLESPACE;
90 # Copy .cfp/.cfg and .ibd files from temp to datadir
91 # Start import
92 ALTER TABLE t1 IMPORT TABLESPACE;
93 SELECT c1, SUBSTRING(c2, 10, 10), SUBSTRING(c3, 10, 10), SUBSTRING(c4, 10, 10), b FROM t1 ORDER BY c1 limit 10;
94 c1 SUBSTRING(c2, 10, 10) SUBSTRING(c3, 10, 10) SUBSTRING(c4, 10, 10) b
95 50001 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
96
97 50002 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
98
99 50003 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
100
101 50004 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
102
103 50005 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
104
105 50006 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
106
107 50007 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
108
109 50008 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
110
111 50009 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
112
113 50010 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
114
115 # Cleanup
116 DROP TABLE t1;
117 # Remove copied files
118
119 # Test 3 : Check that IMPORT works fine from previous version on same FS
120
121 # Copy and unzip the dir having cfg/cfg/ibd file from previous version.
122 CREATE TABLE t1(c1 int NOT NULL AUTO_INCREMENT,
123 c2 varchar(65000) DEFAULT NULL,
124 c3 varchar(255) GENERATED ALWAYS AS (substr(c2,2,100)) STORED,
125 c4 varchar(255) GENERATED ALWAYS AS (substr(c2,10,200)) VIRTUAL,
126 b bit(64) DEFAULT NULL,
127 p_c1 bigint DEFAULT NULL,
128 PRIMARY KEY (c1)) ENGINE=Innodb AUTO_INCREMENT=50001 DEFAULT CHARSET=latin1 COMPRESSION='zlib' ENCRYPTION='Y';
129 ALTER TABLE t1 DISCARD TABLESPACE;
130 # Copy .cfp/.cfg and .ibd files from temp to datadir
131 # Start import
132 ALTER TABLE t1 IMPORT TABLESPACE;
133 SELECT c1, SUBSTRING(c2, 10, 10), SUBSTRING(c3, 10, 10), SUBSTRING(c4, 10, 10), b FROM t1 ORDER BY c1 limit 10;
134 c1 SUBSTRING(c2, 10, 10) SUBSTRING(c3, 10, 10) SUBSTRING(c4, 10, 10) b
135 50001 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
136
137 50002 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
138
139 50003 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
140
141 50004 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
142
143 50005 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
144
145 50006 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
146
147 50007 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
148
149 50008 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
150
151 50009 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
152
153 50010 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
154
155 # Cleanup
156 DROP TABLE t1;
157 # Remove copied files
158
159 # Test 4 : check table from old version are read correctly after upgrade
160
161 # Stop the running the server
162 # Copy and unzip the datadir.
163 # Restart the server against the unzipped datadir
164 # restart: --datadir=DATADIR --innodb-page-size=16k --early-plugin-load=keyring_file=keyring_file.so --loose-keyring_file_data=DATADIR/keyring KEYRING_PLUGIN_OPT
165 # Verify tables after upgrade
166 USE test;
167 SHOW CREATE TABLE t1;
168 Table Create Table
169 t1 CREATE TABLE `t1` (
170 `c1` int NOT NULL AUTO_INCREMENT,
171 `c2` varchar(65000) DEFAULT NULL,
172 `c3` varchar(255) GENERATED ALWAYS AS (substr(`c2`,2,100)) STORED,
173 `c4` varchar(255) GENERATED ALWAYS AS (substr(`c2`,10,200)) VIRTUAL,
174 `b` bit(64) DEFAULT NULL,
175 `p_c1` bigint DEFAULT NULL,
176 PRIMARY KEY (`c1`)
177 ) ENGINE=InnoDB AUTO_INCREMENT=56000 DEFAULT CHARSET=latin1 COMPRESSION='zlib' ENCRYPTION='Y'
178 SELECT c1, SUBSTRING(c2, 10, 10), SUBSTRING(c3, 10, 10), SUBSTRING(c4, 10, 10), b FROM t1 ORDER BY c1 limit 10;
179 c1 SUBSTRING(c2, 10, 10) SUBSTRING(c3, 10, 10) SUBSTRING(c4, 10, 10) b
180 50001 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
181
182 50002 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
183
184 50003 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
185
186 50004 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
187
188 50005 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
189
190 50006 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
191
192 50007 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
193
194 50008 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
195
196 50009 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
197
198 50010 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
199
200 # Run some DML statements
201 INSERT INTO t1(c2,b,p_c1) VALUES(AES_ENCRYPT("5dd0786a3c7adf503202b8e234a95cbe643bc752028e1792ca8e6ef86c69582eb1cc478670c8e5d6c0133d1b4e5c98017aeb893f9db5f1bff397bc9e0f5fde48","abc"),10,100);
202 DELETE FROM t1 WHERE c1=50001;
203 # Verify results
204 SELECT c1, SUBSTRING(c2, 10, 10), SUBSTRING(c3, 10, 10), SUBSTRING(c4, 10, 10), b FROM t1 ORDER BY c1 limit 10;
205 c1 SUBSTRING(c2, 10, 10) SUBSTRING(c3, 10, 10) SUBSTRING(c4, 10, 10) b
206 50002 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
207
208 50003 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
209
210 50004 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
211
212 50005 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
213
214 50006 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
215
216 50007 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
217
218 50008 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
219
220 50009 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
221
222 50010 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
223
224 50011 lŠÜ>Ç!uÊw lŠÜ>Ç!uÊwÎ wÎñûÊ[þs
225
226 # Cleanup
227 DROP TABLE t1;
228 # Shutdown server
229 # Remove copied files
230 # Restarting server without keyring to restore server state
231 # restart