Debian Codegraph @

mysql-test/suite/innodb_zip/r/page_size.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 SET default_storage_engine=InnoDB;
3 # Test 1) Show the page size from Information Schema
4 SELECT variable_value FROM information_schema.global_status
5 WHERE LOWER(variable_name) = 'innodb_page_size';
6 variable_value
7 16384
8 # Test 3) Query some information_shema tables that are dependent upon
9 # the page size.
10 SELECT t.name table_name, t.n_cols, t.flag table_flags,
11 i.name index_name, i.page_no root_page, i.type,
12 i.n_fields, i.merge_threshold
13 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES t,
14 INFORMATION_SCHEMA.INNODB_SYS_INDEXES i
15 WHERE t.table_id = i.table_id
16 AND t.name LIKE 'mysql%'
17 ORDER BY t.name, i.index_id;
18 table_name n_cols table_flags index_name root_page type n_fields merge_threshold
19 mysql/innodb_index_stats 11 33 PRIMARY 3 3 4 50
20 mysql/innodb_table_stats 9 33 PRIMARY 3 3 2 50
21 mysql/transaction_registry 8 33 PRIMARY 3 3 1 50
22 mysql/transaction_registry 8 33 commit_id 4 2 1 50
23 mysql/transaction_registry 8 33 begin_timestamp 5 0 1 50
24 mysql/transaction_registry 8 33 commit_timestamp 6 0 2 50
25 CREATE TABLE t1 (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
26 CREATE TABLE t2 (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
27 CREATE TABLE t3 (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
28 CREATE TABLE t4 (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
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 'test%'
36 ORDER BY t.name, i.name;
37 table_name n_cols table_flags index_name root_page type n_fields merge_threshold
38 test/t1 5 0 PRIMARY 3 3 1 50
39 test/t2 5 1 PRIMARY 3 3 1 50
40 test/t3 5 41 PRIMARY 3 3 1 50
41 test/t4 5 33 PRIMARY 3 3 1 50
42 === information_schema.innodb_sys_tablespaces and innodb_sys_datafiles ===
43 Space_Name Page_Size Zip_Size Path
44 innodb_undo001 DEFAULT DEFAULT MYSQLD_DATADIR//undo001
45 innodb_undo002 DEFAULT DEFAULT MYSQLD_DATADIR//undo002
46 innodb_undo003 DEFAULT DEFAULT MYSQLD_DATADIR//undo003
47 test/t1 DEFAULT DEFAULT MYSQLD_DATADIR/test/t1.ibd
48 test/t2 DEFAULT DEFAULT MYSQLD_DATADIR/test/t2.ibd
49 test/t3 DEFAULT 8192 MYSQLD_DATADIR/test/t3.ibd
50 test/t4 DEFAULT DEFAULT MYSQLD_DATADIR/test/t4.ibd
51 innodb_temporary DEFAULT DEFAULT MYSQLD_DATADIR/ibtmp1
52 DROP TABLE t1, t2, t3, t4;
53 # Test 4) The maximum row size is dependent upon the page size.
54 # Each row format has its own amount of overhead that
55 # varies depending on number of fields and other overhead.
56 SET SESSION innodb_strict_mode = ON;
57 CREATE TABLE t1 (
58 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
59 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
60 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
61 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
62 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
63 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
64 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
65 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(211)
66 ) ROW_FORMAT=redundant;
67 DROP TABLE t1;
68 CREATE TABLE t1 (
69 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
70 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
71 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
72 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
73 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
74 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
75 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
76 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(212)
77 ) ROW_FORMAT=redundant;
78 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.
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(200),
82 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
83 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
84 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
85 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
86 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
87 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
88 ) ROW_FORMAT=compact;
89 DROP TABLE t1;
90 CREATE TABLE t1 (
91 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
92 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
93 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
94 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
95 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
96 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
97 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
98 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
99 ) ROW_FORMAT=compact;
100 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.
101 CREATE TABLE t1 (
102 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
103 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
104 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
105 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
106 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
107 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
108 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
109 c36 char(200), c37 char(200), c38 char(196)
110 ) ROW_FORMAT=compressed;
111 DROP TABLE t1;
112 CREATE TABLE t1 (
113 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
114 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
115 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
116 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
117 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
118 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
119 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
120 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(160)
121 ) ROW_FORMAT=compressed;
122 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.
123 CREATE TABLE t1 (
124 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
125 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
126 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
127 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
128 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
129 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
130 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
131 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
132 ) ROW_FORMAT=dynamic;
133 DROP TABLE t1;
134 CREATE TABLE t1 (
135 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
136 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
137 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
138 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
139 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
140 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
141 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
142 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
143 ) ROW_FORMAT=dynamic;
144 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.
145 CREATE TABLE t1 (a varchar(255) character set utf8,
146 b varchar(255) character set utf8,
147 c varchar(255) character set utf8,
148 d varchar(255) character set utf8,
149 e varchar(4) character set utf8,
150 PRIMARY KEY (a,b,c,d,e))
151 ENGINE=innodb;
152 DROP TABLE t1;
153 CREATE TABLE t1 (a varchar(255) character set utf8,
154 b varchar(255) character set utf8,
155 c varchar(255) character set utf8,
156 d varchar(255) character set utf8,
157 e varchar(5) character set utf8,
158 PRIMARY KEY (a,b,c,d,e))
159 ENGINE=innodb;
160 ERROR 42000: Specified key was too long; max key length is 3072 bytes
161 CREATE TABLE t1 (a varchar(255) character set utf8,
162 b varchar(255) character set utf8,
163 c varchar(255) character set utf8,
164 d varchar(255) character set utf8,
165 e varchar(255) character set utf8,
166 f varchar(4) character set utf8,
167 PRIMARY KEY (a), KEY (b,c,d,e,f))
168 ENGINE=innodb;
169 DROP TABLE t1;
170 CREATE TABLE t1 (a varchar(255) character set utf8,
171 b varchar(255) character set utf8,
172 c varchar(255) character set utf8,
173 d varchar(255) character set utf8,
174 e varchar(255) character set utf8,
175 f varchar(5) character set utf8,
176 PRIMARY KEY (a), KEY (b,c,d,e,f))
177 ENGINE=innodb;
178 ERROR 42000: Specified key was too long; max key length is 3072 bytes
179 # Test 5) KEY_BLOCK_SIZE validation
180 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
181 SHOW WARNINGS;
182 Level Code Message
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;
188 SHOW WARNINGS;
189 Level Code Message
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;
195 SHOW WARNINGS;
196 Level Code Message
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;
202 SHOW WARNINGS;
203 Level Code Message
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;
209 SHOW WARNINGS;
210 Level Code Message
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;
216 SHOW WARNINGS;
217 Level Code Message
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
222 DROP TABLE t1;
223 SET SESSION innodb_strict_mode = OFF;
224 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
225 SHOW WARNINGS;
226 Level Code Message
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;
232 SHOW WARNINGS;
233 Level Code Message
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;
239 SHOW WARNINGS;
240 Level Code Message
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;
246 SHOW WARNINGS;
247 Level Code Message
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;
253 SHOW WARNINGS;
254 Level Code Message
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;
260 SHOW WARNINGS;
261 Level Code Message
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
266 DROP TABLE t1;
267 # Test 6) KEY_BLOCK_SIZE with innodb_file_per_table=OFF
268 SET SESSION innodb_strict_mode = ON;
269 SET GLOBAL innodb_file_per_table = OFF;
270 Warnings:
271 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
272 SHOW VARIABLES LIKE 'innodb_file_per_table';
273 Variable_name Value
274 innodb_file_per_table OFF
275 CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
276 ERROR HY000: Can't create table `test`.`t4` (errno: 140 "Wrong create options")
277 SHOW WARNINGS;
278 Level Code Message
279 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
280 Error 1005 Can't create table `test`.`t4` (errno: 140 "Wrong create options")
281 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
282 CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
283 ERROR HY000: Can't create table `test`.`t5` (errno: 140 "Wrong create options")
284 SHOW WARNINGS;
285 Level Code Message
286 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
287 Error 1005 Can't create table `test`.`t5` (errno: 140 "Wrong create options")
288 Warning 1030 Got error 140 "Wrong create options" from storage engine InnoDB
289 SET GLOBAL innodb_file_per_table = ON;
290 Warnings:
291 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
292 # Test 7) This series of tests were moved from innodb-index to here
293 # because the second alter table t1 assumes a 16k page size.
294 # Moving the test allows the rest of innodb-index to be run on all
295 # page sizes. The previously disabled portions of this test were
296 # moved as well.
297 CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8;
298 CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb;
299 INSERT INTO t3 VALUES (22),(44),(33),(55),(66);
300 INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'),
301 ('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
302 CREATE TABLE t1(a int, b blob, c text, d text NOT NULL)
303 ENGINE=innodb DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
304 INSERT INTO t1
305 SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3 order by a, d;
306 DROP TABLE t2, t3;
307 SELECT COUNT(*) FROM t1 WHERE a=44;
308 COUNT(*)
309 5
310 SELECT a,
311 LENGTH(b),b=LEFT(REPEAT(d,100*a),65535),LENGTH(c),c=REPEAT(d,20*a),d FROM t1
312 ORDER BY 1, 2, 3, 4, 5, 6;
313 a LENGTH(b) b=LEFT(REPEAT(d,100*a),65535) LENGTH(c) c=REPEAT(d,20*a) d
314 22 22000 1 4400 1 adfd72nh9k
315 22 22000 1 4400 1 jejdkrun87
316 22 26400 1 5280 1 adfdpplkeock
317 22 28600 1 5720 1 adfdijnmnb78k
318 22 35200 1 7040 1 adfdijn0loKNHJik
319 33 33000 1 6600 1 adfd72nh9k
320 33 33000 1 6600 1 jejdkrun87
321 33 39600 1 7920 1 adfdpplkeock
322 33 42900 1 8580 1 adfdijnmnb78k
323 33 52800 1 10560 1 adfdijn0loKNHJik
324 44 44000 1 8800 1 adfd72nh9k
325 44 44000 1 8800 1 jejdkrun87
326 44 52800 1 10560 1 adfdpplkeock
327 44 57200 1 11440 1 adfdijnmnb78k
328 44 65535 1 14080 1 adfdijn0loKNHJik
329 55 55000 1 11000 1 adfd72nh9k
330 55 55000 1 11000 1 jejdkrun87
331 55 65535 1 13200 1 adfdpplkeock
332 55 65535 1 14300 1 adfdijnmnb78k
333 55 65535 1 17600 1 adfdijn0loKNHJik
334 66 65535 1 13200 1 adfd72nh9k
335 66 65535 1 13200 1 jejdkrun87
336 66 65535 1 15840 1 adfdpplkeock
337 66 65535 1 17160 1 adfdijnmnb78k
338 66 65535 1 21120 1 adfdijn0loKNHJik
339 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
340 ERROR 23000: Duplicate entry '22' for key 'PRIMARY'
341 DELETE FROM t1 WHERE d='null';
342 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
343 ERROR 23000: Duplicate entry '22' for key 'PRIMARY'
344 DELETE FROM t1 WHERE a%2;
345 CHECK TABLE t1;
346 Table Op Msg_type Msg_text
347 test.t1 check status OK
348 ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));
349 SELECT COUNT(*) FROM t1 WHERE a=44;
350 COUNT(*)
351 5
352 SELECT a,
353 LENGTH(b), b=LEFT(REPEAT(d,100*a), 65535),LENGTH(c), c=REPEAT(d,20*a), d FROM t1;
354 a LENGTH(b) b=LEFT(REPEAT(d,100*a), 65535) LENGTH(c) c=REPEAT(d,20*a) d
355 22 22000 1 4400 1 adfd72nh9k
356 22 35200 1 7040 1 adfdijn0loKNHJik
357 22 28600 1 5720 1 adfdijnmnb78k
358 22 26400 1 5280 1 adfdpplkeock
359 22 22000 1 4400 1 jejdkrun87
360 44 44000 1 8800 1 adfd72nh9k
361 44 65535 1 14080 1 adfdijn0loKNHJik
362 44 57200 1 11440 1 adfdijnmnb78k
363 44 52800 1 10560 1 adfdpplkeock
364 44 44000 1 8800 1 jejdkrun87
365 66 65535 1 13200 1 adfd72nh9k
366 66 65535 1 21120 1 adfdijn0loKNHJik
367 66 65535 1 17160 1 adfdijnmnb78k
368 66 65535 1 15840 1 adfdpplkeock
369 66 65535 1 13200 1 jejdkrun87
370 SHOW CREATE TABLE t1;
371 Table Create Table
372 t1 CREATE TABLE `t1` (
373 `a` int(11) NOT NULL,
374 `b` blob NOT NULL,
375 `c` text NOT NULL,
376 `d` text NOT NULL,
377 PRIMARY KEY (`a`,`b`(255),`c`(255)),
378 KEY `b` (`b`(767))
379 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci STATS_PERSISTENT=0
380 CHECK TABLE t1;
381 Table Op Msg_type Msg_text
382 test.t1 check status OK
383 EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';
384 id select_type table type possible_keys key key_len ref rows Extra
385 1 SIMPLE t1 range b b 769 NULL # Using where
386 DROP TABLE t1;
387 # Test 8) Test creating a table that could lead to undo log overflow.
388 CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,
389 h blob,i blob,j blob,k blob,l blob,m blob,n blob,
390 o blob,p blob,q blob,r blob,s blob,t blob,u blob)
391 ENGINE=InnoDB ROW_FORMAT=dynamic;
392 SET @a = repeat('a', 767);
393 SET @b = repeat('b', 767);
394 SET @c = repeat('c', 767);
395 SET @d = repeat('d', 767);
396 SET @e = repeat('e', 767);
397 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);
398 UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b,
399 k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b;
400 CREATE INDEX t1a ON t1 (a(767));
401 CREATE INDEX t1b ON t1 (b(767));
402 CREATE INDEX t1c ON t1 (c(767));
403 CREATE INDEX t1d ON t1 (d(767));
404 CREATE INDEX t1e ON t1 (e(767));
405 UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c,
406 k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c;
407 CREATE INDEX t1f ON t1 (f(767));
408 BEGIN;
409 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d,f=@d,g=@d,h=@d,i=@d,j=@d,
410 k=@d,l=@d,m=@d,n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
411 ROLLBACK;
412 BEGIN;
413 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d;
414 UPDATE t1 SET f=@d,g=@d,h=@d,i=@d,j=@d,k=@d,l=@d,m=@d,
415 n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
416 COMMIT;
417 CREATE INDEX t1g ON t1 (g(767));
418 UPDATE t1 SET g=@e;
419 CREATE INDEX t1h ON t1 (h(767));
420 UPDATE t1 SET h=@e;
421 CREATE INDEX t1i ON t1 (i(767));
422 UPDATE t1 SET i=@e;
423 CREATE INDEX t1j ON t1 (j(767));
424 UPDATE t1 SET j=@e;
425 CREATE INDEX t1k ON t1 (k(767));
426 UPDATE t1 SET k=@e;
427 CREATE INDEX t1l ON t1 (l(767));
428 UPDATE t1 SET l=@e;
429 CREATE INDEX t1m ON t1 (m(767));
430 UPDATE t1 SET m=@e;
431 CREATE INDEX t1n ON t1 (n(767));
432 UPDATE t1 SET n=@e;
433 CREATE INDEX t1o ON t1 (o(767));
434 UPDATE t1 SET o=@e;
435 CREATE INDEX t1p ON t1 (p(767));
436 UPDATE t1 SET p=@e;
437 CREATE INDEX t1q ON t1 (q(767));
438 UPDATE t1 SET q=@e;
439 CREATE INDEX t1r ON t1 (r(767));
440 UPDATE t1 SET r=@e;
441 CREATE INDEX t1s ON t1 (s(767));
442 UPDATE t1 SET s=@e;
443 CREATE INDEX t1t ON t1 (t(767));
444 BEGIN;
445 UPDATE t1 SET t=@e;
446 ROLLBACK;
447 CREATE INDEX t1u ON t1 (u(767));
448 CREATE INDEX t1ut ON t1 (u(767), t(767));
449 CREATE INDEX t1st ON t1 (s(767), t(767));
450 SHOW CREATE TABLE t1;
451 Table Create Table
452 t1 CREATE TABLE `t1` (
453 `a` blob DEFAULT NULL,
454 `b` blob DEFAULT NULL,
455 `c` blob DEFAULT NULL,
456 `d` blob DEFAULT NULL,
457 `e` blob DEFAULT NULL,
458 `f` blob DEFAULT NULL,
459 `g` blob DEFAULT NULL,
460 `h` blob DEFAULT NULL,
461 `i` blob DEFAULT NULL,
462 `j` blob DEFAULT NULL,
463 `k` blob DEFAULT NULL,
464 `l` blob DEFAULT NULL,
465 `m` blob DEFAULT NULL,
466 `n` blob DEFAULT NULL,
467 `o` blob DEFAULT NULL,
468 `p` blob DEFAULT NULL,
469 `q` blob DEFAULT NULL,
470 `r` blob DEFAULT NULL,
471 `s` blob DEFAULT NULL,
472 `t` blob DEFAULT NULL,
473 `u` blob DEFAULT NULL,
474 KEY `t1a` (`a`(767)),
475 KEY `t1b` (`b`(767)),
476 KEY `t1c` (`c`(767)),
477 KEY `t1d` (`d`(767)),
478 KEY `t1e` (`e`(767)),
479 KEY `t1f` (`f`(767)),
480 KEY `t1g` (`g`(767)),
481 KEY `t1h` (`h`(767)),
482 KEY `t1i` (`i`(767)),
483 KEY `t1j` (`j`(767)),
484 KEY `t1k` (`k`(767)),
485 KEY `t1l` (`l`(767)),
486 KEY `t1m` (`m`(767)),
487 KEY `t1n` (`n`(767)),
488 KEY `t1o` (`o`(767)),
489 KEY `t1p` (`p`(767)),
490 KEY `t1q` (`q`(767)),
491 KEY `t1r` (`r`(767)),
492 KEY `t1s` (`s`(767)),
493 KEY `t1t` (`t`(767)),
494 KEY `t1u` (`u`(767)),
495 KEY `t1ut` (`u`(767),`t`(767)),
496 KEY `t1st` (`s`(767),`t`(767))
497 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC
498 DROP TABLE t1;
499 # Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE
500 CREATE TABLE bug12547647(
501 a int NOT NULL, b blob NOT NULL, c text,
502 PRIMARY KEY (b(10), a), INDEX (c(767)), INDEX(b(767))
503 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
504 INSERT INTO bug12547647 VALUES (5,REPEAT('khdfo5AlOq',1900),REPEAT('g',7751));
505 COMMIT;
506 BEGIN;
507 UPDATE bug12547647 SET c = REPEAT('b',16928);
508 SHOW WARNINGS;
509 Level Code Message
510 ROLLBACK;
511 DROP TABLE bug12547647;
512 SET SESSION innodb_strict_mode = OFF;
513 CREATE TABLE t1(
514 pk01 varchar(48), pk02 varchar(48), pk03 varchar(48), pk04 varchar(48),
515 pk05 varchar(48), pk06 varchar(48), pk07 varchar(48), pk08 varchar(48),
516 pk09 varchar(48), pk10 varchar(48), pk11 varchar(48), pk12 varchar(48),
517 pk13 varchar(48), pk14 varchar(48), pk15 varchar(48), pk16 varchar(48),
518 sk01 varchar(48), sk02 varchar(48), sk03 varchar(48), sk04 varchar(48),
519 sk05 varchar(48), sk06 varchar(48), sk07 varchar(48), sk08 varchar(48),
520 sk09 varchar(48), sk10 varchar(48), sk11 varchar(48), sk12 varchar(48),
521 sk13 varchar(48), sk14 varchar(48), sk15 varchar(48), sk16 varchar(48),
522 PRIMARY KEY pk(pk01,pk02,pk03,pk04,pk05,pk06,pk07,pk08,
523 pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16),
524 KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08,
525 sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16))
526 ROW_FORMAT=Redundant ENGINE=InnoDB;
527 Warnings:
528 Warning 1280 Name 'pk' ignored for PRIMARY key.
529 SET @r = repeat('a', 48);
530 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
531 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
532 SET @r = repeat('b', 48);
533 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
534 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
535 SET @r = repeat('c', 48);
536 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
537 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
538 SET @r = repeat('d', 48);
539 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
540 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
541 SET @r = repeat('e', 48);
542 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
543 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
544 DELETE from t1;
545 DROP TABLE t1;
546 CREATE TABLE t1(
547 pk01 varchar(48), pk02 varchar(48), pk03 varchar(48), pk04 varchar(48),
548 pk05 varchar(48), pk06 varchar(48), pk07 varchar(48), pk08 varchar(48),
549 pk09 varchar(48), pk10 varchar(48), pk11 varchar(48), pk12 varchar(48),
550 pk13 varchar(48), pk14 varchar(48), pk15 varchar(48), pk16 varchar(48),
551 sk01 varchar(48), sk02 varchar(48), sk03 varchar(48), sk04 varchar(48),
552 sk05 varchar(48), sk06 varchar(48), sk07 varchar(48), sk08 varchar(48),
553 sk09 varchar(48), sk10 varchar(48), sk11 varchar(48), sk12 varchar(48),
554 sk13 varchar(48), sk14 varchar(48), sk15 varchar(48), sk16 varchar(48),
555 PRIMARY KEY pk(pk01,pk02,pk03,pk04,pk05,pk06,pk07,pk08,
556 pk09,pk10,pk11,pk12,pk13,pk14,pk15,pk16),
557 KEY pk(sk01,sk02,sk03,sk04,sk05,sk06,sk07,sk08,
558 sk09,sk10,sk11,sk12,sk13,sk14,sk15,sk16))
559 ROW_FORMAT=Compressed KEY_BLOCK_SIZE=4 ENGINE=InnoDB;
560 Warnings:
561 Warning 1280 Name 'pk' ignored for PRIMARY key.
562 SET @r = repeat('a', 48);
563 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
564 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
565 SET @r = repeat('b', 48);
566 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
567 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
568 SET @r = repeat('c', 48);
569 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
570 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
571 SET @r = repeat('d', 48);
572 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
573 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
574 SET @r = repeat('e', 48);
575 INSERT INTO t1 VALUES(@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,
576 @r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
577 DELETE from t1;
578 DROP TABLE t1;
579 CREATE TABLE t1(
580 c text NOT NULL, d text NOT NULL,
581 PRIMARY KEY (c(767),d(767)))
582 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
583 Warnings:
584 Warning 139 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.
585 DROP TABLE t1;
586 CREATE TABLE t1(
587 c text NOT NULL, d text NOT NULL,
588 PRIMARY KEY (c(767),d(767)))
589 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
590 Warnings:
591 Warning 139 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.
592 DROP TABLE t1;
593 CREATE TABLE t1(
594 c text NOT NULL, d text NOT NULL,
595 PRIMARY KEY (c(767),d(767)))
596 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
597 DROP TABLE t1;
598 CREATE TABLE t1(c text, PRIMARY KEY (c(440)))
599 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
600 Warnings:
601 Warning 139 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.
602 DROP TABLE t1;
603 CREATE TABLE t1(c text, PRIMARY KEY (c(293)))
604 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
605 INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
606 DROP TABLE t1;
607 #
608 # MDEV-28950 Assertion `*err == DB_SUCCESS' in btr_page_split_and_insert
609 #
610 CREATE TABLE t1(c CHAR(255) NOT NULL) ENGINE=InnoDB;
611 INSERT INTO t1 VALUES ('');
612 ALTER TABLE t1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
613 INSERT INTO t1 VALUES (''),(''),('');
614 SET @save_innodb_compression_level= @@innodb_compression_level;
615 SET GLOBAL innodb_compression_level=0;
616 INSERT INTO t1 VALUES ('');
617 SET GLOBAL innodb_compression_level= @save_innodb_compression_level;
618 DROP TABLE t1;
619 #
620 # MDEV-31158 Assertion ...MTR_MEMO_X_LOCKED in btr_attach_half_pages()
621 #
622 SET @save_compression_level=@@GLOBAL.innodb_compression_level;
623 SET GLOBAL innodb_compression_level=0;
624 CREATE TEMPORARY TABLE t(a SERIAL, prefix VARBINARY(4), pad INT);
625 INSERT INTO t(prefix, pad) VALUES
626 (_binary 0xff,160),('',19),(_binary 0x0001,253),(_binary 0x0b11,169),
627 (_binary 0x0b010001,23),(_binary 0x0b100001,251),(_binary 0x0d,163),
628 (_binary 0xb3,254),(_binary 0x96,254),(_binary 0xeb,61),
629 (_binary 0xf231,253),(_binary 0x1db0,253),(_binary 0x0005,101),
630 (_binary 0x6370,253),(_binary 0x0b12,112),(_binary 0x0b010002,23),
631 (_binary 0x0b100002,80),(_binary 0x181984,163),(_binary 0x181926,168),
632 (_binary 0xe1,176),(_binary 0xe2,187),(_binary 0xe6,254),(_binary 0xbb,51),
633 (_binary 0x1c,248),(_binary 0x8a,94),(_binary 0x14,254);
634 CREATE TABLE u(a SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
635 b VARBINARY(255), KEY(b)) ENGINE=InnoDB
636 KEY_BLOCK_SIZE=1 ROW_FORMAT=COMPRESSED;
637 INSERT INTO u SELECT a,CONCAT(prefix,REPEAT(chr(0),pad)) FROM t;
638 DROP TABLE u, t;
639 SET GLOBAL innodb_compression_level=@save_compression_level;
640 # End of 10.6 tests
641 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;