1
SET default_storage_engine=InnoDB;
2
SET GLOBAL innodb_file_format = `Barracuda`;
3
SET GLOBAL innodb_file_per_table = ON;
4
# Test 1) Show the page size from Information Schema
5
SELECT variable_value FROM information_schema.global_status
6
WHERE LOWER(variable_name) = 'innodb_page_size';
9
# Test 2) The number of buffer pool pages is dependent upon the page size.
10
SELECT variable_value FROM information_schema.global_status
11
WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
14
# Test 3) Query some information_shema tables that are dependent upon
16
INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NO SPACE
17
11 ID_IND 11 3 1 302 0
18
12 FOR_IND 11 0 1 303 0
19
13 REF_IND 11 0 1 304 0
20
14 ID_IND 12 3 2 305 0
21
15 SYS_TABLESPACES_SPACE 13 3 1 307 0
22
16 SYS_DATAFILES_SPACE 14 3 1 308 0
23
CREATE TABLE t1 (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
24
CREATE TABLE t2 (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
25
CREATE TABLE t3 (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
26
CREATE TABLE t4 (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
27
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
28
WHERE name LIKE 'test%' ORDER BY table_id;
29
TABLE_ID NAME FLAG N_COLS SPACE FILE_FORMAT ROW_FORMAT ZIP_PAGE_SIZE
30
{id} test/t1 0 5 {id} Antelope Redundant 0
31
{id} test/t2 1 5 {id} Antelope Compact 0
32
{id} test/t3 41 5 {id} Barracuda Compressed 8192
33
{id} test/t4 33 5 {id} Barracuda Dynamic 0
34
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
35
WHERE name LIKE 'test%' ORDER BY space;
36
SPACE NAME FLAG FILE_FORMAT ROW_FORMAT PAGE_SIZE ZIP_PAGE_SIZE
37
{id} test/t1 0 Antelope Compact or Redundant 16384 0
38
{id} test/t2 0 Antelope Compact or Redundant 16384 0
39
{id} test/t3 41 Barracuda Compressed 16384 8192
40
{id} test/t4 33 Barracuda Dynamic 16384 0
41
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES
42
WHERE path LIKE '%test%' ORDER BY space;
44
{id} MYSQLD_DATADIR/test/t1.ibd
45
{id} MYSQLD_DATADIR/test/t2.ibd
46
{id} MYSQLD_DATADIR/test/t3.ibd
47
{id} MYSQLD_DATADIR/test/t4.ibd
48
DROP TABLE t1, t2, t3, t4;
49
# Test 4) The maximum row size is dependent upon the page size.
50
# Redundant: 8123, Compact: 8126.
51
# Compressed: 8126, Dynamic: 8126.
52
# Each row format has its own amount of overhead that
53
# varies depending on number of fields and other overhead.
54
SET SESSION innodb_strict_mode = ON;
56
c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
57
c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
58
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
59
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
60
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
61
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
62
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
63
c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(211)
64
) ROW_FORMAT=redundant;
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(200),
69
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
70
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
71
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
72
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
73
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
74
c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(212)
75
) ROW_FORMAT=redundant;
76
ERROR 42000: Row size too large (> 8123). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
78
c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
79
c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
80
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
81
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
82
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
83
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
84
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
85
c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
89
c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
90
c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
91
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
92
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
93
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
94
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
95
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
96
c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
98
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
100
c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
101
c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
102
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
103
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
104
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
105
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
106
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
107
c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157)
108
) ROW_FORMAT=compressed;
111
c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
112
c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
113
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
114
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
115
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
116
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
117
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
118
c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(160)
119
) ROW_FORMAT=compressed;
120
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
122
c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
123
c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
124
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
125
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
126
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
127
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
128
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
129
c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
130
) ROW_FORMAT=dynamic;
133
c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
134
c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
135
c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
136
c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
137
c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
138
c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
139
c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
140
c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
141
) ROW_FORMAT=dynamic;
142
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
143
CREATE TABLE t1 (a varchar(255) character set utf8,
144
b varchar(255) character set utf8,
145
c varchar(255) character set utf8,
146
d varchar(255) character set utf8,
147
e varchar(4) character set utf8,
148
PRIMARY KEY (a,b,c,d,e))
151
CREATE TABLE t1 (a varchar(255) character set utf8,
152
b varchar(255) character set utf8,
153
c varchar(255) character set utf8,
154
d varchar(255) character set utf8,
155
e varchar(5) character set utf8,
156
PRIMARY KEY (a,b,c,d,e))
158
ERROR 42000: Specified key was too long; max key length is 3072 bytes
159
CREATE TABLE t1 (a varchar(255) character set utf8,
160
b varchar(255) character set utf8,
161
c varchar(255) character set utf8,
162
d varchar(255) character set utf8,
163
e varchar(255) character set utf8,
164
f varchar(4) character set utf8,
165
PRIMARY KEY (a), KEY (b,c,d,e,f))
168
CREATE TABLE t1 (a varchar(255) character set utf8,
169
b varchar(255) character set utf8,
170
c varchar(255) character set utf8,
171
d varchar(255) character set utf8,
172
e varchar(255) character set utf8,
173
f varchar(5) character set utf8,
174
PRIMARY KEY (a), KEY (b,c,d,e,f))
176
ERROR 42000: Specified key was too long; max key length is 3072 bytes
177
# Test 5) Make sure that KEY_BLOCK_SIZE=16, 8, 4, 2 & 1
179
SET SESSION innodb_strict_mode = ON;
180
CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
183
SELECT table_name, row_format, create_options
184
FROM information_schema.tables WHERE table_name = 't1';
185
table_name row_format create_options
186
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16
187
ALTER TABLE t1 KEY_BLOCK_SIZE=8;
190
SELECT table_name, row_format, create_options
191
FROM information_schema.tables WHERE table_name = 't1';
192
table_name row_format create_options
193
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
194
ALTER TABLE t1 KEY_BLOCK_SIZE=4;
197
SELECT table_name, row_format, create_options
198
FROM information_schema.tables WHERE table_name = 't1';
199
table_name row_format create_options
200
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4
201
ALTER TABLE t1 KEY_BLOCK_SIZE=2;
204
SELECT table_name, row_format, create_options
205
FROM information_schema.tables WHERE table_name = 't1';
206
table_name row_format create_options
207
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
208
ALTER TABLE t1 KEY_BLOCK_SIZE=1;
211
SELECT table_name, row_format, create_options
212
FROM information_schema.tables WHERE table_name = 't1';
213
table_name row_format create_options
214
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
215
ALTER TABLE t1 KEY_BLOCK_SIZE=0;
218
SELECT table_name, row_format, create_options
219
FROM information_schema.tables WHERE table_name = 't1';
220
table_name row_format create_options
221
t1 Compressed row_format=COMPRESSED
223
SET SESSION innodb_strict_mode = OFF;
224
CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
227
SELECT table_name, row_format, create_options
228
FROM information_schema.tables WHERE table_name = 't1';
229
table_name row_format create_options
230
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16
231
ALTER TABLE t1 KEY_BLOCK_SIZE=8;
234
SELECT table_name, row_format, create_options
235
FROM information_schema.tables WHERE table_name = 't1';
236
table_name row_format create_options
237
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
238
ALTER TABLE t1 KEY_BLOCK_SIZE=4;
241
SELECT table_name, row_format, create_options
242
FROM information_schema.tables WHERE table_name = 't1';
243
table_name row_format create_options
244
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4
245
ALTER TABLE t1 KEY_BLOCK_SIZE=2;
248
SELECT table_name, row_format, create_options
249
FROM information_schema.tables WHERE table_name = 't1';
250
table_name row_format create_options
251
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
252
ALTER TABLE t1 KEY_BLOCK_SIZE=1;
255
SELECT table_name, row_format, create_options
256
FROM information_schema.tables WHERE table_name = 't1';
257
table_name row_format create_options
258
t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
259
ALTER TABLE t1 KEY_BLOCK_SIZE=0;
262
SELECT table_name, row_format, create_options
263
FROM information_schema.tables WHERE table_name = 't1';
264
table_name row_format create_options
265
t1 Compressed row_format=COMPRESSED
267
# Test 6) Make sure that KEY_BLOCK_SIZE = 8 and 16
268
# are rejected when innodb_file_per_table=OFF
269
SET SESSION innodb_strict_mode = ON;
270
SET GLOBAL innodb_file_per_table = OFF;
271
SHOW VARIABLES LIKE 'innodb_file_per_table';
273
innodb_file_per_table OFF
274
CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
275
ERROR HY000: Table storage engine for 't4' doesn't have this option
278
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
279
Error 1031 Table storage engine for 't4' doesn't have this option
280
CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
281
ERROR HY000: Table storage engine for 't5' doesn't have this option
284
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
285
Error 1031 Table storage engine for 't5' doesn't have this option
286
SET GLOBAL innodb_file_per_table = ON;
287
SET GLOBAL innodb_file_format = `Antelope`;
288
SHOW VARIABLES LIKE 'innodb_file%';
290
innodb_file_format Antelope
291
innodb_file_format_check ON
292
innodb_file_format_max Barracuda
293
innodb_file_per_table ON
294
CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
295
ERROR HY000: Table storage engine for 't4' doesn't have this option
298
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
299
Error 1031 Table storage engine for 't4' doesn't have this option
300
CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
301
ERROR HY000: Table storage engine for 't5' doesn't have this option
304
Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_format > Antelope.
305
Error 1031 Table storage engine for 't5' doesn't have this option
306
SET GLOBAL innodb_file_format = `Barracuda`;
307
# Test 7) This series of tests were moved from innodb-index to here
308
# because the second alter table t1 assumes a 16k page size.
309
# Moving the test allows the rest of innodb-index to be run on all
310
# page sizes. The previously disabled portions of this test were
312
CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8;
313
CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb;
314
INSERT INTO t3 VALUES (22),(44),(33),(55),(66);
315
INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'),
316
('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
317
CREATE TABLE t1(a int, b blob, c text, d text NOT NULL)
318
ENGINE=innodb DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
320
SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3;
322
SELECT COUNT(*) FROM t1 WHERE a=44;
326
LENGTH(b),b=LEFT(REPEAT(d,100*a),65535),LENGTH(c),c=REPEAT(d,20*a),d FROM t1;
327
a LENGTH(b) b=LEFT(REPEAT(d,100*a),65535) LENGTH(c) c=REPEAT(d,20*a) d
328
22 22000 1 4400 1 adfd72nh9k
329
22 35200 1 7040 1 adfdijn0loKNHJik
330
22 28600 1 5720 1 adfdijnmnb78k
331
22 26400 1 5280 1 adfdpplkeock
332
22 22000 1 4400 1 jejdkrun87
333
33 33000 1 6600 1 adfd72nh9k
334
33 52800 1 10560 1 adfdijn0loKNHJik
335
33 42900 1 8580 1 adfdijnmnb78k
336
33 39600 1 7920 1 adfdpplkeock
337
33 33000 1 6600 1 jejdkrun87
338
44 44000 1 8800 1 adfd72nh9k
339
44 65535 1 14080 1 adfdijn0loKNHJik
340
44 57200 1 11440 1 adfdijnmnb78k
341
44 52800 1 10560 1 adfdpplkeock
342
44 44000 1 8800 1 jejdkrun87
343
55 55000 1 11000 1 adfd72nh9k
344
55 65535 1 17600 1 adfdijn0loKNHJik
345
55 65535 1 14300 1 adfdijnmnb78k
346
55 65535 1 13200 1 adfdpplkeock
347
55 55000 1 11000 1 jejdkrun87
348
66 65535 1 13200 1 adfd72nh9k
349
66 65535 1 21120 1 adfdijn0loKNHJik
350
66 65535 1 17160 1 adfdijnmnb78k
351
66 65535 1 15840 1 adfdpplkeock
352
66 65535 1 13200 1 jejdkrun87
353
ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
354
ERROR 23000: Duplicate entry '22' for key 'PRIMARY'
355
DELETE FROM t1 WHERE d='null';
356
ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
357
ERROR 23000: Duplicate entry '22' for key 'PRIMARY'
358
DELETE FROM t1 WHERE a%2;
360
Table Op Msg_type Msg_text
361
test.t1 check status OK
362
SET @old_sql_mode = @@sql_mode;
363
SET @@sql_mode = 'STRICT_TRANS_TABLES';
364
ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));
365
SET @@sql_mode = @old_sql_mode;
366
SELECT COUNT(*) FROM t1 WHERE a=44;
370
LENGTH(b), b=LEFT(REPEAT(d,100*a), 65535),LENGTH(c), c=REPEAT(d,20*a), d FROM t1;
371
a LENGTH(b) b=LEFT(REPEAT(d,100*a), 65535) LENGTH(c) c=REPEAT(d,20*a) d
372
22 22000 1 4400 1 adfd72nh9k
373
22 35200 1 7040 1 adfdijn0loKNHJik
374
22 28600 1 5720 1 adfdijnmnb78k
375
22 26400 1 5280 1 adfdpplkeock
376
22 22000 1 4400 1 jejdkrun87
377
44 44000 1 8800 1 adfd72nh9k
378
44 65535 1 14080 1 adfdijn0loKNHJik
379
44 57200 1 11440 1 adfdijnmnb78k
380
44 52800 1 10560 1 adfdpplkeock
381
44 44000 1 8800 1 jejdkrun87
382
66 65535 1 13200 1 adfd72nh9k
383
66 65535 1 21120 1 adfdijn0loKNHJik
384
66 65535 1 17160 1 adfdijnmnb78k
385
66 65535 1 15840 1 adfdpplkeock
386
66 65535 1 13200 1 jejdkrun87
387
SHOW CREATE TABLE t1;
389
t1 CREATE TABLE `t1` (
390
`a` int(11) NOT NULL DEFAULT '0',
394
PRIMARY KEY (`a`,`b`(255),`c`(255)),
396
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0
398
Table Op Msg_type Msg_text
399
test.t1 check status OK
400
EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';
401
id select_type table type possible_keys key key_len ref rows Extra
402
1 SIMPLE t1 range b b 769 NULL 11 Using where
404
# Test 8) Test creating a table that could lead to undo log overflow.
405
CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,
406
h blob,i blob,j blob,k blob,l blob,m blob,n blob,
407
o blob,p blob,q blob,r blob,s blob,t blob,u blob)
408
ENGINE=InnoDB ROW_FORMAT=dynamic;
409
SET @a = repeat('a', 767);
410
SET @b = repeat('b', 767);
411
SET @c = repeat('c', 767);
412
SET @d = repeat('d', 767);
413
SET @e = repeat('e', 767);
414
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);
415
UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b,
416
k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b;
417
CREATE INDEX t1a ON t1 (a(767));
418
CREATE INDEX t1b ON t1 (b(767));
419
CREATE INDEX t1c ON t1 (c(767));
420
CREATE INDEX t1d ON t1 (d(767));
421
CREATE INDEX t1e ON t1 (e(767));
422
UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c,
423
k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c;
424
CREATE INDEX t1f ON t1 (f(767));
425
UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d,f=@d,g=@d,h=@d,i=@d,j=@d,
426
k=@d,l=@d,m=@d,n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
427
ERROR HY000: Undo log record is too big.
429
UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d;
430
UPDATE t1 SET f=@d,g=@d,h=@d,i=@d,j=@d,k=@d,l=@d,m=@d,
431
n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
433
CREATE INDEX t1g ON t1 (g(767));
435
CREATE INDEX t1h ON t1 (h(767));
437
CREATE INDEX t1i ON t1 (i(767));
439
CREATE INDEX t1j ON t1 (j(767));
441
CREATE INDEX t1k ON t1 (k(767));
443
CREATE INDEX t1l ON t1 (l(767));
445
CREATE INDEX t1m ON t1 (m(767));
447
CREATE INDEX t1n ON t1 (n(767));
449
CREATE INDEX t1o ON t1 (o(767));
451
CREATE INDEX t1p ON t1 (p(767));
453
CREATE INDEX t1q ON t1 (q(767));
455
CREATE INDEX t1r ON t1 (r(767));
457
CREATE INDEX t1s ON t1 (s(767));
459
CREATE INDEX t1t ON t1 (t(767));
461
ERROR HY000: Undo log record is too big.
462
CREATE INDEX t1u ON t1 (u(767));
463
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
464
CREATE INDEX t1ut ON t1 (u(767), t(767));
465
ERROR 42000: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
466
CREATE INDEX t1st ON t1 (s(767), t(767));
467
SHOW CREATE TABLE t1;
469
t1 CREATE TABLE `t1` (
491
KEY `t1a` (`a`(767)),
492
KEY `t1b` (`b`(767)),
493
KEY `t1c` (`c`(767)),
494
KEY `t1d` (`d`(767)),
495
KEY `t1e` (`e`(767)),
496
KEY `t1f` (`f`(767)),
497
KEY `t1g` (`g`(767)),
498
KEY `t1h` (`h`(767)),
499
KEY `t1i` (`i`(767)),
500
KEY `t1j` (`j`(767)),
501
KEY `t1k` (`k`(767)),
502
KEY `t1l` (`l`(767)),
503
KEY `t1m` (`m`(767)),
504
KEY `t1n` (`n`(767)),
505
KEY `t1o` (`o`(767)),
506
KEY `t1p` (`p`(767)),
507
KEY `t1q` (`q`(767)),
508
KEY `t1r` (`r`(767)),
509
KEY `t1s` (`s`(767)),
510
KEY `t1t` (`t`(767)),
511
KEY `t1st` (`s`(767),`t`(767))
512
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
514
SET GLOBAL innodb_file_format = `Barracuda`;
515
SET GLOBAL innodb_file_per_table = ON;
516
# Bug #12429576 - Test an assertion failure on purge.
517
CREATE TABLE t1_purge (
519
B blob, C blob, D blob, E blob,
520
F blob, G blob, H blob,
521
PRIMARY KEY (B(767), C(767), D(767), E(767), A),
523
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
524
INSERT INTO t1_purge VALUES (1,
525
REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
526
REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766));
527
CREATE TABLE t2_purge (
529
B blob, C blob, D blob, E blob,
530
F blob, G blob, H blob, I blob,
531
J blob, K blob, L blob,
532
INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
533
INSERT INTO t2_purge VALUES (1,
534
REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
535
REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766), REPEAT('i', 766),
536
REPEAT('j', 766), REPEAT('k', 766), REPEAT('l', 766));
537
CREATE TABLE t3_purge (
539
B varchar(800), C varchar(800), D varchar(800), E varchar(800),
540
F varchar(800), G varchar(800), H varchar(800),
541
PRIMARY KEY (B(767), C(767), D(767), E(767), A),
543
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
544
INSERT INTO t3_purge SELECT * FROM t1_purge;
545
CREATE TABLE t4_purge (
547
B varchar(800), C varchar(800), D varchar(800), E varchar(800),
548
F varchar(800), G varchar(800), H varchar(800), I varchar(800),
549
J varchar(800), K varchar(800), L varchar(800),
550
INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
551
INSERT INTO t4_purge SELECT * FROM t2_purge;
552
DELETE FROM t1_purge;
553
DELETE FROM t2_purge;
554
DELETE FROM t3_purge;
555
DELETE FROM t4_purge;
556
SET GLOBAL innodb_file_per_table=on;
557
SET GLOBAL innodb_file_format='Barracuda';
558
SET @r=REPEAT('a',500);
559
CREATE TABLE t12637786(a int,
560
v1 varchar(500), v2 varchar(500), v3 varchar(500),
561
v4 varchar(500), v5 varchar(500), v6 varchar(500),
562
v7 varchar(500), v8 varchar(500), v9 varchar(500),
563
v10 varchar(500), v11 varchar(500), v12 varchar(500),
564
v13 varchar(500), v14 varchar(500), v15 varchar(500),
565
v16 varchar(500), v17 varchar(500), v18 varchar(500)
566
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
567
CREATE INDEX idx1 ON t12637786(a,v1);
568
INSERT INTO t12637786 VALUES(9,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
569
UPDATE t12637786 SET a=1000;
570
DELETE FROM t12637786;
571
# Bug#12963823 - Test that the purge thread does not crash when
572
CREATE TABLE t12963823(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob,
573
i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob)
574
ENGINE=innodb ROW_FORMAT=dynamic;
575
SET @r = REPEAT('a', 767);
576
INSERT INTO t12963823 VALUES (@r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r);
577
CREATE INDEX ndx_a ON t12963823 (a(500));
578
CREATE INDEX ndx_b ON t12963823 (b(500));
579
CREATE INDEX ndx_c ON t12963823 (c(500));
580
CREATE INDEX ndx_d ON t12963823 (d(500));
581
CREATE INDEX ndx_e ON t12963823 (e(500));
582
CREATE INDEX ndx_f ON t12963823 (f(500));
583
CREATE INDEX ndx_k ON t12963823 (k(500));
584
CREATE INDEX ndx_l ON t12963823 (l(500));
585
SET @r = REPEAT('b', 500);
586
UPDATE t12963823 set a=@r,b=@r,c=@r,d=@r;
587
UPDATE t12963823 set e=@r,f=@r,g=@r,h=@r;
588
UPDATE t12963823 set i=@r,j=@r,k=@r,l=@r;
589
UPDATE t12963823 set m=@r,n=@r,o=@r,p=@r;
590
ALTER TABLE t12963823 DROP INDEX ndx_a;
591
ALTER TABLE t12963823 DROP INDEX ndx_b;
592
CREATE INDEX ndx_g ON t12963823 (g(500));
593
CREATE INDEX ndx_h ON t12963823 (h(500));
594
CREATE INDEX ndx_i ON t12963823 (i(500));
595
CREATE INDEX ndx_j ON t12963823 (j(500));
596
CREATE INDEX ndx_m ON t12963823 (m(500));
597
CREATE INDEX ndx_n ON t12963823 (n(500));
598
CREATE INDEX ndx_o ON t12963823 (o(500));
599
CREATE INDEX ndx_p ON t12963823 (p(500));
600
SHOW CREATE TABLE t12963823;
602
t12963823 CREATE TABLE `t12963823` (
619
KEY `ndx_c` (`c`(500)),
620
KEY `ndx_d` (`d`(500)),
621
KEY `ndx_e` (`e`(500)),
622
KEY `ndx_f` (`f`(500)),
623
KEY `ndx_k` (`k`(500)),
624
KEY `ndx_l` (`l`(500)),
625
KEY `ndx_g` (`g`(500)),
626
KEY `ndx_h` (`h`(500)),
627
KEY `ndx_i` (`i`(500)),
628
KEY `ndx_j` (`j`(500)),
629
KEY `ndx_m` (`m`(500)),
630
KEY `ndx_n` (`n`(500)),
631
KEY `ndx_o` (`o`(500)),
632
KEY `ndx_p` (`p`(500))
633
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
634
# Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE
635
SET SESSION innodb_strict_mode = ON;
636
CREATE TABLE bug12547647(
637
a int NOT NULL, b blob NOT NULL, c text,
638
PRIMARY KEY (b(10), a), INDEX (c(767)), INDEX(b(767))
639
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
640
INSERT INTO bug12547647 VALUES (5,REPEAT('khdfo5AlOq',1900),REPEAT('g',7751));
642
UPDATE bug12547647 SET c = REPEAT('b',16928);
643
ERROR HY000: Undo log record is too big.
646
Error 1713 Undo log record is too big.
647
DROP TABLE bug12547647;
648
SET SESSION innodb_strict_mode = off;
650
c text NOT NULL, d text NOT NULL,
651
PRIMARY KEY (c(767),d(767)))
652
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
653
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
655
c text NOT NULL, d text NOT NULL,
656
PRIMARY KEY (c(767),d(767)))
657
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
658
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
660
c text NOT NULL, d text NOT NULL,
661
PRIMARY KEY (c(767),d(767)))
662
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
664
CREATE TABLE t1(c text, PRIMARY KEY (c(440)))
665
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
666
ERROR 42000: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
667
CREATE TABLE t1(c text, PRIMARY KEY (c(438)))
668
ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
669
INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
672
# Bug#56862 Execution of a query that uses index merge returns a wrong result
675
pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
680
INSERT INTO t1(a,b) VALUES
681
(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
682
(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
683
(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
684
(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
685
INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
686
INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
687
INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
688
INSERT INTO t1(a,b) SELECT a,b FROM t1;
689
INSERT INTO t1(a,b) SELECT a,b FROM t1;
690
INSERT INTO t1(a,b) SELECT a,b FROM t1;
691
INSERT INTO t1(a,b) SELECT a,b FROM t1;
692
INSERT INTO t1(a,b) SELECT a,b FROM t1;
693
INSERT INTO t1(a,b) SELECT a,b FROM t1;
694
INSERT INTO t1(a,b) SELECT a,b FROM t1;
695
INSERT INTO t1(a,b) SELECT a,b FROM t1;
696
INSERT INTO t1 VALUES (1000000, 0, 0);
697
SET SESSION sort_buffer_size = 1024*36;
700
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
701
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
702
id select_type table type possible_keys key key_len ref rows Extra
703
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 1536 NULL
704
2 DERIVED t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1536 Using sort_union(idx,PRIMARY); Using where
706
(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
707
WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
710
SET SESSION sort_buffer_size = DEFAULT;
712
DROP TABLE t1_purge, t2_purge, t3_purge, t4_purge;
713
DROP TABLE t12637786;
714
DROP TABLE t12963823;