Debian Codegraph @

mysql-test/suite/innodb_zip/t/page_size.test

raw
1 --source include/innodb_page_size_small.inc
2 --source include/innodb_checksum_algorithm.inc
3 --source include/test_db_charset_latin1.inc
4
5 SET default_storage_engine=InnoDB;
6
7 --disable_query_log
8 let $MYSQLD_DATADIR = `select @@datadir`;
9 let $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
10
11 call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is");
12 --enable_query_log
13
14 --echo # Test 1) Show the page size from Information Schema
15 SELECT variable_value FROM information_schema.global_status
16 WHERE LOWER(variable_name) = 'innodb_page_size';
17
18 --echo # Test 3) Query some information_shema tables that are dependent upon
19 --echo # the page size.
20 # Show the metadata for tables in schema 'mysql'.
21 # Pulled from innodb-system-table-view.test
22 # The IDs of mysql.innodb_table_stats and mysql.innodb_index_stats are
23 # unpredictable. They depend on whether mtr has created the database for
24 # this test from scratch or is using a previously created database where
25 # those tables have been dropped and recreated. Since we cannot force mtr
26 # to use a freshly created database for this test we do not return the
27 # table or index IDs. We can return the space IS of mysql schema tables
28 # since they are created consistently during bootstrap.
29 SELECT t.name table_name, t.n_cols, t.flag table_flags,
30 i.name index_name, i.page_no root_page, i.type,
31 i.n_fields, i.merge_threshold
32 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t,
33 INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
34 WHERE t.table_id = i.table_id
35 AND t.name LIKE 'mysql%'
36 ORDER BY t.name, i.index_id;
37
38 CREATE TABLE t1 (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
39 CREATE TABLE t2 (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
40 CREATE TABLE t3 (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
41 CREATE TABLE t4 (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
42
43 # Show the metadata for tables in schema 'test'.
44 # Do not return the space ID since this tablespace may have existed before
45 # this test runs. The root page number of each index should be consistent
46 # within a file-per-table tablespace.
47 SELECT t.name table_name, t.n_cols, t.flag table_flags,
48 i.name index_name, i.page_no root_page, i.type,
49 i.n_fields, i.merge_threshold
50 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t,
51 INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
52 WHERE t.table_id = i.table_id
53 AND t.name LIKE 'test%'
54 ORDER BY t.name, i.name;
55 --source suite/innodb/include/show_i_s_tablespaces.inc
56 DROP TABLE t1, t2, t3, t4;
57
58 --echo # Test 4) The maximum row size is dependent upon the page size.
59 --echo # Each row format has its own amount of overhead that
60 --echo # varies depending on number of fields and other overhead.
61
62 SET SESSION innodb_strict_mode = ON;
63
64 if ($INNODB_PAGE_SIZE == 4096) {
65 # Redundant table; 1927 bytes with 10 CHAR fields
66 CREATE TABLE t1 (
67 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
68 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(127)
69 ) ROW_FORMAT=redundant;
70 DROP TABLE t1;
71 --replace_regex /> [0-9]*/> max_row_size/
72 --error ER_TOO_BIG_ROWSIZE
73 CREATE TABLE t1 (
74 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
75 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(128)
76 ) ROW_FORMAT=redundant;
77
78 # Compact table; 1955 bytes with 10 CHAR fields
79 CREATE TABLE t1 (
80 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
81 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(155)
82 ) ROW_FORMAT=compact;
83 DROP TABLE t1;
84 --replace_regex /> [0-9]*/> max_row_size/
85 --error ER_TOO_BIG_ROWSIZE
86 CREATE TABLE t1 (
87 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
88 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(156)
89 ) ROW_FORMAT=compact;
90
91 # Compressed table
92 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
93 CREATE TABLE t1 (
94 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
95 c06 char(200), c07 char(200), c08 char(176)
96 ) ROW_FORMAT=compressed;
97 DROP TABLE t1;
98 --replace_regex /> [0-9]*/> max_row_size/
99 --error ER_TOO_BIG_ROWSIZE
100 CREATE TABLE t1 (
101 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
102 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(79)
103 ) ROW_FORMAT=compressed;
104
105 # Dynamic table; 1955 bytes with 10 CHAR fields
106 CREATE TABLE t1 (
107 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
108 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(155)
109 ) ROW_FORMAT=dynamic;
110 DROP TABLE t1;
111 --replace_regex /> [0-9]*/> max_row_size/
112 --error ER_TOO_BIG_ROWSIZE
113 CREATE TABLE t1 (
114 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
115 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(156)
116 ) ROW_FORMAT=dynamic;
117
118 #
119 # Test the maximum key length
120 # Moved from innodb-index.test since each page size has its own max key length.
121 # Max Key Length is 1173 for 4k pages.
122 #
123 # InnoDB assumes 3 bytes for each UTF8 character.
124 #
125 CREATE TABLE t1 (a varchar(98) character set utf8,
126 b varchar(98) character set utf8,
127 c varchar(98) character set utf8,
128 d varchar(97) character set utf8,
129 PRIMARY KEY (a,b,c,d))
130 ENGINE=innodb;
131 DROP TABLE t1;
132 --error ER_TOO_LONG_KEY
133 CREATE TABLE t1 (a varchar(98) character set utf8,
134 b varchar(98) character set utf8,
135 c varchar(98) character set utf8,
136 d varchar(98) character set utf8,
137 PRIMARY KEY (a,b,c,d))
138 ENGINE=innodb;
139 CREATE TABLE t1 (a varchar(98) character set utf8,
140 b varchar(98) character set utf8,
141 c varchar(98) character set utf8,
142 d varchar(98) character set utf8,
143 e varchar(97) character set utf8,
144 PRIMARY KEY (a), KEY (b,c,d,e))
145 ENGINE=innodb;
146 DROP TABLE t1;
147 --error ER_TOO_LONG_KEY
148 CREATE TABLE t1 (a varchar(98) character set utf8,
149 b varchar(98) character set utf8,
150 c varchar(98) character set utf8,
151 d varchar(98) character set utf8,
152 e varchar(98) character set utf8,
153 PRIMARY KEY (a), KEY (b,c,d,e))
154 ENGINE=innodb;
155 }
156 if ($INNODB_PAGE_SIZE == 8192) {
157 # Redundant table; 3955 bytes with 20 CHAR fields
158 CREATE TABLE t1 (
159 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
160 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
161 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
162 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(155)
163 ) ROW_FORMAT=redundant;
164 DROP TABLE t1;
165 --replace_regex /> [0-9]*/> max_row_size/
166 --error ER_TOO_BIG_ROWSIZE
167 CREATE TABLE t1 (
168 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
169 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
170 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
171 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(156)
172 ) ROW_FORMAT=redundant;
173
174 # Compact table; 4002 bytes with 20 CHAR fields
175 CREATE TABLE t1 (
176 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
177 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
178 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
179 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(202)
180 ) ROW_FORMAT=compact;
181 DROP TABLE t1;
182 --replace_regex /> [0-9]*/> max_row_size/
183 --error ER_TOO_BIG_ROWSIZE
184 CREATE TABLE t1 (
185 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
186 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
187 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
188 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(203)
189 ) ROW_FORMAT=compact;
190
191 # Compressed table
192 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
193 CREATE TABLE t1 (
194 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
195 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
196 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
197 c16 char(200), c17 char(200), c18 char(182)
198 ) ROW_FORMAT=compressed;
199 DROP TABLE t1;
200 --replace_regex /> [0-9]*/> max_row_size/
201 --error ER_TOO_BIG_ROWSIZE
202 CREATE TABLE t1 (
203 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
204 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
205 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
206 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(106)
207 ) ROW_FORMAT=compressed;
208
209 # Dynamic table; 4002 bytes with 20 CHAR fields
210 CREATE TABLE t1 (
211 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
212 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
213 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
214 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(202)
215 ) ROW_FORMAT=dynamic;
216 DROP TABLE t1;
217 --replace_regex /> [0-9]*/> max_row_size/
218 --error ER_TOO_BIG_ROWSIZE
219 CREATE TABLE t1 (
220 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
221 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
222 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
223 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(203)
224 ) ROW_FORMAT=dynamic;
225
226 #
227 # Test the maximum key length
228 # Moved from innodb-index.test since each page size has its own max key length.
229 # Max Key Length is 1536 for 8k pages.
230 #
231 # InnoDB assumes 3 bytes for each UTF8 character.
232 #
233 CREATE TABLE t1 (a varchar(128) character set utf8,
234 b varchar(128) character set utf8,
235 c varchar(128) character set utf8,
236 d varchar(128) character set utf8,
237 PRIMARY KEY (a,b,c,d))
238 ENGINE=innodb;
239 DROP TABLE t1;
240 --error ER_TOO_LONG_KEY
241 CREATE TABLE t1 (a varchar(128) character set utf8,
242 b varchar(128) character set utf8,
243 c varchar(128) character set utf8,
244 d varchar(129) character set utf8,
245 PRIMARY KEY (a,b,c,d))
246 ENGINE=innodb;
247 CREATE TABLE t1 (a varchar(128) character set utf8,
248 b varchar(128) character set utf8,
249 c varchar(128) character set utf8,
250 d varchar(128) character set utf8,
251 e varchar(128) character set utf8,
252 PRIMARY KEY (a), KEY (b,c,d,e))
253 ENGINE=innodb;
254 DROP TABLE t1;
255 --error ER_TOO_LONG_KEY
256 CREATE TABLE t1 (a varchar(128) character set utf8,
257 b varchar(128) character set utf8,
258 c varchar(128) character set utf8,
259 d varchar(128) character set utf8,
260 e varchar(129) character set utf8,
261 PRIMARY KEY (a), KEY (b,c,d,e))
262 ENGINE=innodb;
263 }
264 if ($INNODB_PAGE_SIZE == 16384) {
265 # Redundant table; 8011 bytes with 40 char fields
266 CREATE TABLE t1 (
267 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
268 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
269 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
270 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
271 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
272 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
273 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
274 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(211)
275 ) ROW_FORMAT=redundant;
276 DROP TABLE t1;
277 --error ER_TOO_BIG_ROWSIZE
278 CREATE TABLE t1 (
279 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
280 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
281 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
282 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
283 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
284 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
285 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
286 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(212)
287 ) ROW_FORMAT=redundant;
288
289 # Compact table; 8096 bytes with 40 CHAR fields
290 CREATE TABLE t1 (
291 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
292 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
293 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
294 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
295 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
296 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
297 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
298 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
299 ) ROW_FORMAT=compact;
300 DROP TABLE t1;
301 --error ER_TOO_BIG_ROWSIZE
302 CREATE TABLE t1 (
303 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
304 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
305 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
306 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
307 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
308 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
309 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
310 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
311 ) ROW_FORMAT=compact;
312
313 # Compressed table
314 # MDEV-27634 stricter compressBound() limit for the s390x DFLTCC instruction
315 CREATE TABLE t1 (
316 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
317 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
318 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
319 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
320 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
321 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
322 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
323 c36 char(200), c37 char(200), c38 char(196)
324 ) ROW_FORMAT=compressed;
325 DROP TABLE t1;
326 --error ER_TOO_BIG_ROWSIZE
327 CREATE TABLE t1 (
328 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
329 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
330 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
331 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
332 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
333 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
334 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
335 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(160)
336 ) ROW_FORMAT=compressed;
337
338 # Dynamic table; 8096 bytes with 40 CHAR fields
339 CREATE TABLE t1 (
340 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
341 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
342 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
343 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
344 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
345 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
346 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
347 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
348 ) ROW_FORMAT=dynamic;
349 DROP TABLE t1;
350 --error ER_TOO_BIG_ROWSIZE
351 CREATE TABLE t1 (
352 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
353 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
354 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
355 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
356 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
357 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
358 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
359 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
360 ) ROW_FORMAT=dynamic;
361
362 #
363 # Test the maximum key length
364 # Moved from innodb-index.test since each page size has its own max key length.
365 # Max Key Length is 3072 for 16k pages.
366 # Max key Part length is 767
367 # InnoDB assumes 3 bytes for each UTF8 character.
368 #
369 CREATE TABLE t1 (a varchar(255) character set utf8,
370 b varchar(255) character set utf8,
371 c varchar(255) character set utf8,
372 d varchar(255) character set utf8,
373 e varchar(4) character set utf8,
374 PRIMARY KEY (a,b,c,d,e))
375 ENGINE=innodb;
376 DROP TABLE t1;
377 --error ER_TOO_LONG_KEY
378 CREATE TABLE t1 (a varchar(255) character set utf8,
379 b varchar(255) character set utf8,
380 c varchar(255) character set utf8,
381 d varchar(255) character set utf8,
382 e varchar(5) character set utf8,
383 PRIMARY KEY (a,b,c,d,e))
384 ENGINE=innodb;
385 CREATE TABLE t1 (a varchar(255) character set utf8,
386 b varchar(255) character set utf8,
387 c varchar(255) character set utf8,
388 d varchar(255) character set utf8,
389 e varchar(255) character set utf8,
390 f varchar(4) character set utf8,
391 PRIMARY KEY (a), KEY (b,c,d,e,f))
392 ENGINE=innodb;
393 DROP TABLE t1;
394 --error ER_TOO_LONG_KEY
395 CREATE TABLE t1 (a varchar(255) character set utf8,
396 b varchar(255) character set utf8,
397 c varchar(255) character set utf8,
398 d varchar(255) character set utf8,
399 e varchar(255) character set utf8,
400 f varchar(5) character set utf8,
401 PRIMARY KEY (a), KEY (b,c,d,e,f))
402 ENGINE=innodb;
403 }
404
405 --echo # Test 5) KEY_BLOCK_SIZE validation
406
407 if ($INNODB_PAGE_SIZE == 4096) {
408 --error ER_CANT_CREATE_TABLE
409 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
410 SHOW WARNINGS;
411
412 --error ER_CANT_CREATE_TABLE
413 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
414 SHOW WARNINGS;
415
416 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED;
417 }
418 if ($INNODB_PAGE_SIZE == 8192) {
419 --error ER_CANT_CREATE_TABLE
420 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
421 SHOW WARNINGS;
422
423 CREATE TABLE t1 ( i INT ) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;
424 SHOW WARNINGS;
425 SELECT table_name, row_format, create_options
426 FROM information_schema.tables WHERE table_name = 't1';
427 }
428 if ($INNODB_PAGE_SIZE == 16384) {
429 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
430 SHOW WARNINGS;
431 SELECT table_name, row_format, create_options
432 FROM information_schema.tables WHERE table_name = 't1';
433
434 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
435 SHOW WARNINGS;
436 SELECT table_name, row_format, create_options
437 FROM information_schema.tables WHERE table_name = 't1';
438 }
439 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
440 SHOW WARNINGS;
441 SELECT table_name, row_format, create_options
442 FROM information_schema.tables WHERE table_name = 't1';
443
444 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
445 SHOW WARNINGS;
446 SELECT table_name, row_format, create_options
447 FROM information_schema.tables WHERE table_name = 't1';
448
449 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
450 SHOW WARNINGS;
451 SELECT table_name, row_format, create_options
452 FROM information_schema.tables WHERE table_name = 't1';
453
454 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
455 SHOW WARNINGS;
456 SELECT table_name, row_format, create_options
457 FROM information_schema.tables WHERE table_name = 't1';
458 DROP TABLE t1;
459
460 SET SESSION innodb_strict_mode = OFF;
461
462 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
463 SHOW WARNINGS;
464 SELECT table_name, row_format, create_options
465 FROM information_schema.tables WHERE table_name = 't1';
466
467 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
468 SHOW WARNINGS;
469 SELECT table_name, row_format, create_options
470 FROM information_schema.tables WHERE table_name = 't1';
471
472 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
473 SHOW WARNINGS;
474 SELECT table_name, row_format, create_options
475 FROM information_schema.tables WHERE table_name = 't1';
476
477 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
478 SHOW WARNINGS;
479 SELECT table_name, row_format, create_options
480 FROM information_schema.tables WHERE table_name = 't1';
481
482 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
483 SHOW WARNINGS;
484 SELECT table_name, row_format, create_options
485 FROM information_schema.tables WHERE table_name = 't1';
486
487 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
488 SHOW WARNINGS;
489 SELECT table_name, row_format, create_options
490 FROM information_schema.tables WHERE table_name = 't1';
491 DROP TABLE t1;
492
493
494 --echo # Test 6) KEY_BLOCK_SIZE with innodb_file_per_table=OFF
495 # Moved from innodb-zip.test
496 SET SESSION innodb_strict_mode = ON;
497 SET GLOBAL innodb_file_per_table = OFF;
498 SHOW VARIABLES LIKE 'innodb_file_per_table';
499 --error ER_CANT_CREATE_TABLE
500 CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
501 SHOW WARNINGS;
502 --error ER_CANT_CREATE_TABLE
503 CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
504 SHOW WARNINGS;
505 SET GLOBAL innodb_file_per_table = ON;
506
507 if ($INNODB_PAGE_SIZE == 16384) {
508 --echo # Test 7) This series of tests were moved from innodb-index to here
509 --echo # because the second alter table t1 assumes a 16k page size.
510 --echo # Moving the test allows the rest of innodb-index to be run on all
511 --echo # page sizes. The previously disabled portions of this test were
512 --echo # moved as well.
513
514 CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8;
515 CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb;
516
517 INSERT INTO t3 VALUES (22),(44),(33),(55),(66);
518
519 INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'),
520 ('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
521
522 CREATE TABLE t1(a int, b blob, c text, d text NOT NULL)
523 ENGINE=innodb DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
524
525 INSERT INTO t1
526 SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3 order by a, d;
527 DROP TABLE t2, t3;
528 SELECT COUNT(*) FROM t1 WHERE a=44;
529 SELECT a,
530 LENGTH(b),b=LEFT(REPEAT(d,100*a),65535),LENGTH(c),c=REPEAT(d,20*a),d FROM t1
531 ORDER BY 1, 2, 3, 4, 5, 6;
532 # in-place alter table should trigger ER_PRIMARY_CANT_HAVE_NULL
533 --error ER_DUP_ENTRY
534 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
535 DELETE FROM t1 WHERE d='null';
536 --error ER_DUP_ENTRY
537 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
538 DELETE FROM t1 WHERE a%2;
539 CHECK TABLE t1;
540 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
541 # And adding a PRIMARY KEY will also add NOT NULL implicitly!
542 ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));
543 SELECT COUNT(*) FROM t1 WHERE a=44;
544 SELECT a,
545 LENGTH(b), b=LEFT(REPEAT(d,100*a), 65535),LENGTH(c), c=REPEAT(d,20*a), d FROM t1;
546 SHOW CREATE TABLE t1;
547 CHECK TABLE t1;
548 --replace_column 9 #
549 EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';
550
551 # The following tests are disabled because of the introduced timeouts for
552 # metadata locks at the MySQL level as part of the fix for
553 # Bug#45225 Locking: hang if drop table with no timeout
554 # The following commands now play with MySQL metadata locks instead of
555 # InnoDB locks
556 # start disabled45225_1
557 ##
558 ## Test locking
559 ##
560 #
561 #CREATE TABLE t2(a int, b varchar(255), PRIMARY KEY(a,b)) ENGINE=innodb;
562 #INSERT INTO t2 SELECT a,LEFT(b,255) FROM t1;
563 #DROP TABLE t1;
564 #RENAME TABLE t2 to t1;
565 #
566 #connect (a,localhost,root,,);
567 #connect (b,localhost,root,,);
568 #connection a;
569 #SET innodb_lock_wait_timeout=1;
570 #begin;
571 ## Obtain an IX lock on the table
572 #SELECT a FROM t1 limit 1 FOR UPDATE;
573 #connection b;
574 #SET innodb_lock_wait_timeout=1;
575 ## This would require an S lock on the table, conflicting with the IX lock.
576 #--error ER_LOCK_WAIT_TIMEOUT
577 #CREATE INDEX t1ba ON t1 (b,a);
578 #connection a;
579 #commit;
580 #begin;
581 ## Obtain an IS lock on the table
582 #SELECT a FROM t1 limit 1 lock in share mode;
583 #connection b;
584 ## This will require an S lock on the table. No conflict with the IS lock.
585 #CREATE INDEX t1ba ON t1 (b,a);
586 ## This would require an X lock on the table, conflicting with the IS lock.
587 #--error ER_LOCK_WAIT_TIMEOUT
588 #DROP INDEX t1ba ON t1;
589 #connection a;
590 #commit;
591 #EXPLAIN SELECT a FROM t1 ORDER BY b;
592 #--send
593 #SELECT a,sleep(2+a/100) FROM t1 ORDER BY b limit 3;
594 #
595 ## The following DROP INDEX will succeed, altough the SELECT above has
596 ## opened a read view. However, during the execution of the SELECT,
597 ## MySQL should hold a table lock that should block the execution
598 ## of the DROP INDEX below.
599 #
600 #connection b;
601 #SELECT sleep(1);
602 #DROP INDEX t1ba ON t1;
603 #
604 ## After the index was dropped, subsequent SELECTs will use the same
605 ## read view, but they should not be accessing the dropped index any more.
606 #
607 #connection a;
608 #reap;
609 #EXPLAIN SELECT a FROM t1 ORDER BY b;
610 #SELECT a FROM t1 ORDER BY b limit 3;
611 #commit;
612 #
613 #connection default;
614 #disconnect a;
615 #disconnect b;
616 #
617 # end disabled45225_1
618 DROP TABLE t1;
619 }
620
621 --echo # Test 8) Test creating a table that could lead to undo log overflow.
622 CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,
623 h blob,i blob,j blob,k blob,l blob,m blob,n blob,
624 o blob,p blob,q blob,r blob,s blob,t blob,u blob)
625 ENGINE=InnoDB ROW_FORMAT=dynamic;
626 SET @a = repeat('a', 767);
627 SET @b = repeat('b', 767);
628 SET @c = repeat('c', 767);
629 SET @d = repeat('d', 767);
630 SET @e = repeat('e', 767);
631
632 # With no indexes defined, we can update all columns to max key part length.
633 INSERT INTO t1 VALUES (@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a,@a);
634 UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b,
635 k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b;
636
637 # With this many indexes defined, we can still update all fields.
638 CREATE INDEX t1a ON t1 (a(767));
639 if ($INNODB_PAGE_SIZE == 16384) {
640 CREATE INDEX t1b ON t1 (b(767));
641 CREATE INDEX t1c ON t1 (c(767));
642 CREATE INDEX t1d ON t1 (d(767));
643 CREATE INDEX t1e ON t1 (e(767));
644 }
645 UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c,
646 k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c;
647
648 # Add one more index and the UNDO record becomes too big to update all columns.
649 # But a single transaction can update the columns in separate statements.
650 # because the UNDO records will be smaller.
651 CREATE INDEX t1f ON t1 (f(767));
652 BEGIN;
653 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d,f=@d,g=@d,h=@d,i=@d,j=@d,
654 k=@d,l=@d,m=@d,n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
655 ROLLBACK;
656 BEGIN;
657 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d;
658 UPDATE t1 SET f=@d,g=@d,h=@d,i=@d,j=@d,k=@d,l=@d,m=@d,
659 n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
660 COMMIT;
661
662 # More indexes can still be added and a single field can still be updated
663 CREATE INDEX t1g ON t1 (g(767));
664 UPDATE t1 SET g=@e;
665 if ($INNODB_PAGE_SIZE == 16384) {
666 CREATE INDEX t1h ON t1 (h(767));
667 UPDATE t1 SET h=@e;
668 CREATE INDEX t1i ON t1 (i(767));
669 UPDATE t1 SET i=@e;
670 CREATE INDEX t1j ON t1 (j(767));
671 UPDATE t1 SET j=@e;
672 CREATE INDEX t1k ON t1 (k(767));
673 UPDATE t1 SET k=@e;
674 CREATE INDEX t1l ON t1 (l(767));
675 UPDATE t1 SET l=@e;
676 CREATE INDEX t1m ON t1 (m(767));
677 UPDATE t1 SET m=@e;
678 CREATE INDEX t1n ON t1 (n(767));
679 UPDATE t1 SET n=@e;
680 CREATE INDEX t1o ON t1 (o(767));
681 UPDATE t1 SET o=@e;
682 CREATE INDEX t1p ON t1 (p(767));
683 UPDATE t1 SET p=@e;
684 CREATE INDEX t1q ON t1 (q(767));
685 UPDATE t1 SET q=@e;
686 CREATE INDEX t1r ON t1 (r(767));
687 UPDATE t1 SET r=@e;
688 CREATE INDEX t1s ON t1 (s(767));
689 UPDATE t1 SET s=@e;
690 }
691
692 # Add one more index and we cannot update a column to its defined index length.
693 # This is a problem. It means that the DDL is allowed to create a table
694 # that CANNOT be updated. See bug#12953735.
695 CREATE INDEX t1t ON t1 (t(767));
696 BEGIN;
697 UPDATE t1 SET t=@e;
698 ROLLBACK;
699
700 CREATE INDEX t1u ON t1 (u(767));
701 if ($INNODB_PAGE_SIZE == 4096)
702 {
703 CREATE INDEX t1ut ON t1 (u(767));
704 CREATE INDEX t1st ON t1 (s(767));
705 }
706 if ($INNODB_PAGE_SIZE != 4096)
707 {
708 CREATE INDEX t1ut ON t1 (u(767), t(767));
709 CREATE INDEX t1st ON t1 (s(767), t(767));
710 }
711
712 SHOW CREATE TABLE t1;
713 DROP TABLE t1;
714
715 --echo # Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE
716 # InnoDB cannot know that this undo record would be too big for the undo
717 # page. Too much of text field is stored in the clustered record in this
718 # DYNAMIC row formatted record.
719
720 CREATE TABLE bug12547647(
721 a int NOT NULL, b blob NOT NULL, c text,
722 PRIMARY KEY (b(10), a), INDEX (c(767)), INDEX(b(767))
723 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
724 INSERT INTO bug12547647 VALUES (5,REPEAT('khdfo5AlOq',1900),REPEAT('g',7751));
725 COMMIT;
726 # The following used to cause a hang while doing infinite undo log allocation.
727 BEGIN;
728 UPDATE bug12547647 SET c = REPEAT('b',16928);
729 SHOW WARNINGS;
730 ROLLBACK;
731 DROP TABLE bug12547647;
732
733 # make sure the largest possible key entry can be added to the insert buffer.
734 # Make enough records so that the root page is not a leaf page.
735 SET SESSION innodb_strict_mode = OFF;
736 CREATE TABLE t1(
737 pk01 varchar(48), pk02 varchar(48), pk03 varchar(48), pk04 varchar(48),
738 pk05 varchar(48), pk06 varchar(48), pk07 varchar(48), pk08 varchar(48),
739 pk09 varchar(48), pk10 varchar(48), pk11 varchar(48), pk12 varchar(48),
740 pk13 varchar(48), pk14 varchar(48), pk15 varchar(48), pk16 varchar(48),
741 sk01 varchar(48), sk02 varchar(48), sk03 varchar(48), sk04 varchar(48),
742 sk05 varchar(48), sk06 varchar(48), sk07 varchar(48), sk08 varchar(48),
743 sk09 varchar(48), sk10 varchar(48), sk11 varchar(48), sk12 varchar(48),
744 sk13 varchar(48), sk14 varchar(48), sk15 varchar(48), sk16 varchar(48),
745 PRIMARY KEY pk(pk01,pk02,pk03,pk04,pk05,pk06,pk07,pk08,
746 pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16),
747 KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08,
748 sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16))
749 ROW_FORMAT=Redundant ENGINE=InnoDB;
750 SET @r = repeat('a', 48);
751 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
752 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
753 SET @r = repeat('b', 48);
754 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
755 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
756 SET @r = repeat('c', 48);
757 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
758 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
759 SET @r = repeat('d', 48);
760 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
761 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
762 SET @r = repeat('e', 48);
763 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
764 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
765 DELETE from t1;
766 DROP TABLE t1;
767
768 # Compressed tables do not compress parent pages. So the whole uncompressed
769 # secondary tuple including the primary key must be able to fit in half the
770 # compressed page size. This record length is enforced at index creation.
771 # So the only way to get an ibuf tuple too big is to make the KEY_BLOCK_SIZE
772 # the same as the page size.
773 CREATE TABLE t1(
774 pk01 varchar(48), pk02 varchar(48), pk03 varchar(48), pk04 varchar(48),
775 pk05 varchar(48), pk06 varchar(48), pk07 varchar(48), pk08 varchar(48),
776 pk09 varchar(48), pk10 varchar(48), pk11 varchar(48), pk12 varchar(48),
777 pk13 varchar(48), pk14 varchar(48), pk15 varchar(48), pk16 varchar(48),
778 sk01 varchar(48), sk02 varchar(48), sk03 varchar(48), sk04 varchar(48),
779 sk05 varchar(48), sk06 varchar(48), sk07 varchar(48), sk08 varchar(48),
780 sk09 varchar(48), sk10 varchar(48), sk11 varchar(48), sk12 varchar(48),
781 sk13 varchar(48), sk14 varchar(48), sk15 varchar(48), sk16 varchar(48),
782 PRIMARY KEY pk(pk01,pk02,pk03,pk04,pk05,pk06,pk07,pk08,
783 pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16),
784 KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08,
785 sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16))
786 ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 ENGINE=InnoDB;
787 SET @r = repeat('a', 48);
788 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
789 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
790 SET @r = repeat('b', 48);
791 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
792 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
793 SET @r = repeat('c', 48);
794 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
795 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
796 SET @r = repeat('d', 48);
797 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
798 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
799 SET @r = repeat('e', 48);
800 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
801 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
802 DELETE from t1;
803 DROP TABLE t1;
804
805 # The following should fail in non-strict mode too.
806 # (The fix of Bug #50945 only affects REDUNDANT and COMPACT tables.)
807 if ($INNODB_PAGE_SIZE == 4096)
808 {
809 CREATE TABLE t1(
810 c text NOT NULL, d text NOT NULL,
811 PRIMARY KEY (c(767)))
812 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
813 DROP TABLE t1;
814 CREATE TABLE t1(
815 c text NOT NULL, d text NOT NULL,
816 PRIMARY KEY (c(767)))
817 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
818 DROP TABLE t1;
819 CREATE TABLE t1(
820 c text NOT NULL, d text NOT NULL,
821 PRIMARY KEY (c(767)))
822 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
823 }
824 if ($INNODB_PAGE_SIZE != 4096)
825 {
826 CREATE TABLE t1(
827 c text NOT NULL, d text NOT NULL,
828 PRIMARY KEY (c(767),d(767)))
829 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
830 DROP TABLE t1;
831 CREATE TABLE t1(
832 c text NOT NULL, d text NOT NULL,
833 PRIMARY KEY (c(767),d(767)))
834 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
835 DROP TABLE t1;
836 CREATE TABLE t1(
837 c text NOT NULL, d text NOT NULL,
838 PRIMARY KEY (c(767),d(767)))
839 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
840 }
841 DROP TABLE t1;
842 CREATE TABLE t1(c text, PRIMARY KEY (c(440)))
843 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
844 DROP TABLE t1;
845 --disable_warnings
846 CREATE TABLE t1(c text, PRIMARY KEY (c(293)))
847 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
848 --enable_warnings
849 INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
850 DROP TABLE t1;
851
852 --echo #
853 --echo # MDEV-28950 Assertion `*err == DB_SUCCESS' in btr_page_split_and_insert
854 --echo #
855 CREATE TABLE t1(c CHAR(255) NOT NULL) ENGINE=InnoDB;
856 INSERT INTO t1 VALUES ('');
857 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
858 INSERT INTO t1 VALUES (''),(''),('');
859 SET @save_innodb_compression_level= @@innodb_compression_level;
860 SET GLOBAL innodb_compression_level=0;
861 INSERT INTO t1 VALUES ('');
862 SET GLOBAL innodb_compression_level= @save_innodb_compression_level;
863 DROP TABLE t1;
864
865 --echo #
866 --echo # MDEV-31158 Assertion ...MTR_MEMO_X_LOCKED in btr_attach_half_pages()
867 --echo #
868 --source include/have_innodb.inc
869
870 SET @save_compression_level=@@GLOBAL.innodb_compression_level;
871 SET GLOBAL innodb_compression_level=0;
872 CREATE TEMPORARY TABLE t(a SERIAL, prefix VARBINARY(4), pad INT);
873 INSERT INTO t(prefix, pad) VALUES
874 (_binary 0xff,160),('',19),(_binary 0x0001,253),(_binary 0x0b11,169),
875 (_binary 0x0b010001,23),(_binary 0x0b100001,251),(_binary 0x0d,163),
876 (_binary 0xb3,254),(_binary 0x96,254),(_binary 0xeb,61),
877 (_binary 0xf231,253),(_binary 0x1db0,253),(_binary 0x0005,101),
878 (_binary 0x6370,253),(_binary 0x0b12,112),(_binary 0x0b010002,23),
879 (_binary 0x0b100002,80),(_binary 0x181984,163),(_binary 0x181926,168),
880 (_binary 0xe1,176),(_binary 0xe2,187),(_binary 0xe6,254),(_binary 0xbb,51),
881 (_binary 0x1c,248),(_binary 0x8a,94),(_binary 0x14,254);
882 CREATE TABLE u(a SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
883 b VARBINARY(255), KEY(b)) ENGINE=InnoDB
884 KEY_BLOCK_SIZE=1 ROW_FORMAT=COMPRESSED;
885 INSERT INTO u SELECT a,CONCAT(prefix,REPEAT(chr(0),pad)) FROM t;
886 DROP TABLE u, t;
887 SET GLOBAL innodb_compression_level=@save_compression_level;
888
889 --echo # End of 10.6 tests
890
891 --source include/test_db_charset_restore.inc