Debian Codegraph @

mysql-test/suite/innodb/t/innodb-16k.test

raw
1 --source include/no_valgrind_without_big.inc
2 # Tests for setting innodb-page-size=16k; default value
3 --source include/have_innodb.inc
4 --source include/have_innodb_16k.inc
5 --source include/test_db_charset_latin1.inc
6
7 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
8 SET GLOBAL innodb_stats_persistent = 0;
9
10 call mtr.add_suppression("InnoDB: Cannot add field .* in table");
11 let $MYSQLD_DATADIR= `select @@datadir`;
12
13 --echo # Test 1) Show the page size from Information Schema
14 SELECT variable_value FROM information_schema.global_status
15 WHERE LOWER(variable_name) = 'innodb_page_size';
16
17 --echo # Test 4) The maximum row size is dependent upon the page size.
18 --echo # Redundant: 8123, Compact: 8126, Dynamic: 8126.
19 --echo # Each row format has its own amount of overhead that
20 --echo # varies depending on number of fields and other overhead.
21
22 SET SESSION innodb_strict_mode = ON;
23 SET @save_level=@@GLOBAL.innodb_compression_level;
24 SET GLOBAL innodb_compression_level=1;
25
26 # Compressed table: compressBound() for the s390x DFLTCC instruction
27 CREATE TABLE t1 (
28 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
29 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
30 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
31 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
32 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
33 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
34 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
35 c36 char(200), c37 char(200), c38 char(196)
36 ) ROW_FORMAT=compressed;
37
38 DROP TABLE t1;
39
40 --error ER_TOO_BIG_ROWSIZE
41 CREATE TABLE t1 (
42 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
43 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
44 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
45 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
46 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
47 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
48 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
49 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(160)
50 ) ROW_FORMAT=compressed;
51
52 #
53 # Test the maximum key length
54 # Max Key Length is 3072 for 16k pages.
55 # Max key Part length is 767
56 # InnoDB assumes 3 bytes for each UTF8 character.
57 #
58 CREATE TABLE t1 (a varchar(255) character set utf8,
59 b varchar(255) character set utf8,
60 c varchar(255) character set utf8,
61 d varchar(255) character set utf8,
62 e varchar(4) character set utf8,
63 PRIMARY KEY (a,b,c,d,e))
64 ENGINE=innodb;
65
66 DROP TABLE t1;
67
68 --error ER_TOO_LONG_KEY
69 CREATE TABLE t1 (a varchar(255) character set utf8,
70 b varchar(255) character set utf8,
71 c varchar(255) character set utf8,
72 d varchar(255) character set utf8,
73 e varchar(5) character set utf8,
74 PRIMARY KEY (a,b,c,d,e))
75 ENGINE=innodb;
76
77 CREATE TABLE t1 (a varchar(255) character set utf8,
78 b varchar(255) character set utf8,
79 c varchar(255) character set utf8,
80 d varchar(255) character set utf8,
81 e varchar(255) character set utf8,
82 f varchar(4) character set utf8,
83 PRIMARY KEY (a), KEY (b,c,d,e,f))
84 ENGINE=innodb;
85
86 DROP TABLE t1;
87
88 --error ER_TOO_LONG_KEY
89 CREATE TABLE t1 (a varchar(255) character set utf8,
90 b varchar(255) character set utf8,
91 c varchar(255) character set utf8,
92 d varchar(255) character set utf8,
93 e varchar(255) character set utf8,
94 f varchar(5) character set utf8,
95 PRIMARY KEY (a), KEY (b,c,d,e,f))
96 ENGINE=innodb;
97
98 --echo # Test 5) Make sure that KEY_BLOCK_SIZE=16, 8, 4, 2 & 1
99 --echo # are all accepted.
100
101 SET SESSION innodb_strict_mode = ON;
102
103 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
104 SHOW WARNINGS;
105
106 SELECT table_name, row_format, create_options
107 FROM information_schema.tables WHERE table_name = 't1';
108
109 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
110 SHOW WARNINGS;
111
112 SELECT table_name, row_format, create_options
113 FROM information_schema.tables WHERE table_name = 't1';
114
115 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
116 SHOW WARNINGS;
117
118 SELECT table_name, row_format, create_options
119 FROM information_schema.tables WHERE table_name = 't1';
120
121 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
122 SHOW WARNINGS;
123
124 SELECT table_name, row_format, create_options
125 FROM information_schema.tables WHERE table_name = 't1';
126
127 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
128 SHOW WARNINGS;
129
130 SELECT table_name, row_format, create_options
131 FROM information_schema.tables WHERE table_name = 't1';
132
133 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
134 SHOW WARNINGS;
135
136 SELECT table_name, row_format, create_options
137 FROM information_schema.tables WHERE table_name = 't1';
138
139 DROP TABLE t1;
140
141 SET SESSION innodb_strict_mode = OFF;
142
143 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
144 SHOW WARNINGS;
145
146 SELECT table_name, row_format, create_options
147 FROM information_schema.tables WHERE table_name = 't1';
148
149 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
150 SHOW WARNINGS;
151
152 SELECT table_name, row_format, create_options
153 FROM information_schema.tables WHERE table_name = 't1';
154
155 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
156 SHOW WARNINGS;
157
158 SELECT table_name, row_format, create_options
159 FROM information_schema.tables WHERE table_name = 't1';
160
161 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
162 SHOW WARNINGS;
163
164 SELECT table_name, row_format, create_options
165 FROM information_schema.tables WHERE table_name = 't1';
166
167 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
168 SHOW WARNINGS;
169
170 SELECT table_name, row_format, create_options
171 FROM information_schema.tables WHERE table_name = 't1';
172
173 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
174 SHOW WARNINGS;
175
176 SELECT table_name, row_format, create_options
177 FROM information_schema.tables WHERE table_name = 't1';
178
179 DROP TABLE t1;
180
181 CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8;
182 CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb;
183 INSERT INTO t3 VALUES (22),(44),(33),(55),(66);
184 INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'),
185 ('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
186 CREATE TABLE t1(a int, b blob, c text, d text NOT NULL)
187 ENGINE=innodb DEFAULT CHARSET=utf8;
188 INSERT INTO t1
189 SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3
190 ORDER BY a,d;
191 DROP TABLE t2, t3;
192 SELECT COUNT(*) FROM t1 WHERE a=44;
193 SELECT a,
194 LENGTH(b),b=LEFT(REPEAT(d,100*a),65535),LENGTH(c),c=REPEAT(d,20*a),d FROM t1
195 ORDER BY a,d;
196
197 # in-place alter table should trigger ER_PRIMARY_CANT_HAVE_NULL
198 --error ER_DUP_ENTRY
199 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
200 DELETE FROM t1 WHERE d='null';
201 --error ER_DUP_ENTRY
202 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
203 DELETE FROM t1 WHERE a%2;
204 CHECK TABLE t1;
205 ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));
206 SELECT COUNT(*) FROM t1 WHERE a=44;
207 SELECT a,
208 LENGTH(b), b=LEFT(REPEAT(d,100*a), 65535),LENGTH(c), c=REPEAT(d,20*a), d FROM t1
209 ORDER BY a,d;
210 SHOW CREATE TABLE t1;
211 CHECK TABLE t1;
212 DROP TABLE t1;
213
214 --echo # Test 8) Test creating a table that could lead to undo log overflow.
215
216 CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,
217 h blob,i blob,j blob,k blob,l blob,m blob,n blob,
218 o blob,p blob,q blob,r blob,s blob,t blob,u blob)
219 ENGINE=InnoDB ROW_FORMAT=dynamic;
220
221 SET @a = repeat('a', 767);
222 SET @b = repeat('b', 767);
223 SET @c = repeat('c', 767);
224 SET @d = repeat('d', 767);
225 SET @e = repeat('e', 767);
226
227 # With no indexes defined, we can update all columns to max key part length.
228
229 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);
230
231 UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b,
232 k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b;
233
234
235 # With this many indexes defined, we can still update all fields.
236 CREATE INDEX t1a ON t1 (a(767));
237 CREATE INDEX t1b ON t1 (b(767));
238 CREATE INDEX t1c ON t1 (c(767));
239 CREATE INDEX t1d ON t1 (d(767));
240 CREATE INDEX t1e ON t1 (e(767));
241
242 UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c,
243 k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c;
244
245
246 # Add one more index and the UNDO record becomes too big to update all columns.
247 # But a single transaction can update the columns in separate statements.
248 # because the UNDO records will be smaller.
249
250 CREATE INDEX t1f ON t1 (f(767));
251 BEGIN;
252 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d,f=@d,g=@d,h=@d,i=@d,j=@d,
253 k=@d,l=@d,m=@d,n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
254 ROLLBACK;
255 BEGIN;
256 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d;
257 UPDATE t1 SET f=@d,g=@d,h=@d,i=@d,j=@d,k=@d,l=@d,m=@d,
258 n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
259 COMMIT;
260
261 # More indexes can still be added and a single field can still be updated
262 CREATE INDEX t1g ON t1 (g(767));
263 UPDATE t1 SET g=@e;
264 CREATE INDEX t1h ON t1 (h(767));
265 UPDATE t1 SET h=@e;
266 CREATE INDEX t1i ON t1 (i(767));
267 UPDATE t1 SET i=@e;
268 CREATE INDEX t1j ON t1 (j(767));
269 UPDATE t1 SET j=@e;
270 CREATE INDEX t1k ON t1 (k(767));
271 UPDATE t1 SET k=@e;
272 CREATE INDEX t1l ON t1 (l(767));
273 UPDATE t1 SET l=@e;
274 CREATE INDEX t1m ON t1 (m(767));
275 UPDATE t1 SET m=@e;
276 CREATE INDEX t1n ON t1 (n(767));
277 UPDATE t1 SET n=@e;
278 CREATE INDEX t1o ON t1 (o(767));
279 UPDATE t1 SET o=@e;
280 CREATE INDEX t1p ON t1 (p(767));
281 UPDATE t1 SET p=@e;
282 CREATE INDEX t1q ON t1 (q(767));
283 UPDATE t1 SET q=@e;
284 CREATE INDEX t1r ON t1 (r(767));
285 UPDATE t1 SET r=@e;
286 CREATE INDEX t1s ON t1 (s(767));
287 UPDATE t1 SET s=@e;
288
289 # Add one more index and we cannot update a column to its defined index length.
290 # This is a problem. It means that the DDL is allowed to create a table
291 # that CANNOT be updated.
292
293 CREATE INDEX t1t ON t1 (t(767));
294 BEGIN;
295 UPDATE t1 SET t=@e;
296 ROLLBACK;
297
298 SHOW CREATE TABLE t1;
299 DROP TABLE t1;
300
301 --echo Test an assertion failure on purge.
302
303 # This test is not in innodb_8k or innodb_4k since the bug is not about
304 # page size. It just tests the condition that caused the assertion.
305
306 CREATE TABLE t1_purge (
307 A int,
308 B blob, C blob, D blob, E blob,
309 F blob, G blob, H blob,
310 PRIMARY KEY (B(767), C(767), D(767), E(767), A),
311 INDEX (A)
312 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
313
314 INSERT INTO t1_purge VALUES (1,
315 REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
316 REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766));
317
318 CREATE TABLE t2_purge (
319 A int PRIMARY KEY,
320 B blob, C blob, D blob, E blob,
321 F blob, G blob, H blob, I blob,
322 J blob, K blob, L blob,
323 INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
324
325 INSERT INTO t2_purge VALUES (1,
326 REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
327 REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766), REPEAT('i', 766),
328 REPEAT('j', 766), REPEAT('k', 766), REPEAT('l', 766));
329
330 CREATE TABLE t3_purge (
331 A int,
332 B varchar(800), C varchar(800), D varchar(800), E varchar(800),
333 F varchar(800), G varchar(800), H varchar(800),
334 PRIMARY KEY (B(767), C(767), D(767), E(767), A),
335 INDEX (A)
336 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
337
338 INSERT INTO t3_purge SELECT * FROM t1_purge;
339
340 CREATE TABLE t4_purge (
341 A int PRIMARY KEY,
342 B varchar(800), C varchar(800), D varchar(800), E varchar(800),
343 F varchar(800), G varchar(800), H varchar(800), I varchar(800),
344 J varchar(800), K varchar(800), L varchar(800),
345 INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
346
347 INSERT INTO t4_purge SELECT * FROM t2_purge;
348
349 # This would trigger the failure
350 # if purge gets a chance to run before DROP TABLE t1_purge, ....
351 DELETE FROM t1_purge;
352 DELETE FROM t2_purge;
353 DELETE FROM t3_purge;
354 DELETE FROM t4_purge;
355
356 # A secondary index tuple is found to be too long to fit into a page.
357 SET @r=REPEAT('a',500);
358
359 CREATE TABLE tlong(a int,
360 v1 varchar(500), v2 varchar(500), v3 varchar(500),
361 v4 varchar(500), v5 varchar(500), v6 varchar(500),
362 v7 varchar(500), v8 varchar(500), v9 varchar(500),
363 v10 varchar(500), v11 varchar(500), v12 varchar(500),
364 v13 varchar(500), v14 varchar(500), v15 varchar(500),
365 v16 varchar(500), v17 varchar(500), v18 varchar(500)
366 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
367
368 CREATE INDEX idx1 ON tlong(a,v1);
369 INSERT INTO tlong VALUES(9,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
370 UPDATE tlong SET a=1000;
371 DELETE FROM tlong;
372
373 # the number of indexes has changed since the UNDO record was logged.
374 # page size. It just tests the condition that caused the crash.
375
376 CREATE TABLE tlong2(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob,
377 i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob)
378 ENGINE=innodb ROW_FORMAT=dynamic;
379
380 SET @r = REPEAT('a', 767);
381 INSERT INTO tlong2 VALUES (@r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r);
382 CREATE INDEX ndx_a ON tlong2 (a(500));
383 CREATE INDEX ndx_b ON tlong2 (b(500));
384 CREATE INDEX ndx_c ON tlong2 (c(500));
385 CREATE INDEX ndx_d ON tlong2 (d(500));
386 CREATE INDEX ndx_e ON tlong2 (e(500));
387 CREATE INDEX ndx_f ON tlong2 (f(500));
388 CREATE INDEX ndx_k ON tlong2 (k(500));
389 CREATE INDEX ndx_l ON tlong2 (l(500));
390
391 SET @r = REPEAT('b', 500);
392 UPDATE tlong2 set a=@r,b=@r,c=@r,d=@r;
393 UPDATE tlong2 set e=@r,f=@r,g=@r,h=@r;
394 UPDATE tlong2 set i=@r,j=@r,k=@r,l=@r;
395 UPDATE tlong2 set m=@r,n=@r,o=@r,p=@r;
396
397 ALTER TABLE tlong2 DROP INDEX ndx_a;
398 ALTER TABLE tlong2 DROP INDEX ndx_b;
399 CREATE INDEX ndx_g ON tlong2 (g(500));
400 CREATE INDEX ndx_h ON tlong2 (h(500));
401 CREATE INDEX ndx_i ON tlong2 (i(500));
402 CREATE INDEX ndx_j ON tlong2 (j(500));
403 CREATE INDEX ndx_m ON tlong2 (m(500));
404 CREATE INDEX ndx_n ON tlong2 (n(500));
405 CREATE INDEX ndx_o ON tlong2 (o(500));
406 CREATE INDEX ndx_p ON tlong2 (p(500));
407 SHOW CREATE TABLE tlong2;
408
409 # InnoDB cannot know that this undo record would be too big for the undo
410 # page. Too much of text field is stored in the clustered record in this
411 # DYNAMIC row formatted record.
412
413 SET SESSION innodb_strict_mode = ON;
414
415 CREATE TABLE bug12547647(
416 a int NOT NULL, b blob NOT NULL, c text,
417 PRIMARY KEY (b(10), a), INDEX (c(767)), INDEX(b(767))
418 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
419
420 INSERT INTO bug12547647 VALUES (5,REPEAT('khdfo5AlOq',1900),REPEAT('g',7751));
421 COMMIT;
422
423 # The following used to cause a hang while doing infinite undo log allocation.
424 BEGIN;
425 UPDATE bug12547647 SET c = REPEAT('b',16928);
426 SHOW WARNINGS;
427 ROLLBACK;
428 DROP TABLE bug12547647;
429
430 SET SESSION innodb_strict_mode = on;
431 --error ER_TOO_BIG_ROWSIZE
432 CREATE TABLE t1(
433 c text NOT NULL, d text NOT NULL,
434 PRIMARY KEY (c(767),d(767)))
435 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
436
437 --error ER_TOO_BIG_ROWSIZE
438 CREATE TABLE t1(
439 c text NOT NULL, d text NOT NULL,
440 PRIMARY KEY (c(767),d(767)))
441 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
442
443 CREATE TABLE t1(
444 c text NOT NULL, d text NOT NULL,
445 PRIMARY KEY (c(767),d(767)))
446 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
447
448 drop table t1;
449
450 --error ER_TOO_BIG_ROWSIZE
451 CREATE TABLE t1(c text, PRIMARY KEY (c(440)))
452 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
453
454 CREATE TABLE t1(c text, PRIMARY KEY (c(292)))
455 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
456
457 INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
458
459 DROP TABLE t1;
460
461 --source include/wait_all_purged.inc
462
463 SET GLOBAL innodb_compression_level=@save_level;
464 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
465
466 DROP TABLE t1_purge, t2_purge, t3_purge, t4_purge;
467 DROP TABLE tlong;
468 DROP TABLE tlong2;
469
470 --source include/test_db_charset_restore.inc