Debian Codegraph @

mysql-test/suite/innodb_zip/r/16k.result

raw
1 SET default_storage_engine=InnoDB;
2 # Test 1) Show the page size from Information Schema
3 SELECT variable_value FROM performance_schema.global_status
4 WHERE LOWER(variable_name) = 'innodb_page_size';
5 variable_value
6 16384
7 # Test 2) The number of buffer pool pages is dependent upon the page size.
8 SELECT variable_value FROM performance_schema.global_status
9 WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
10 variable_value
11 {checked_valid}
12 # Test 3) Query some information_shema tables that are dependent upon
13 # the page size.
14 SELECT t.name table_name, t.n_cols, t.flag table_flags,
15 i.name index_name, i.page_no root_page, i.type,
16 i.n_fields, i.merge_threshold
17 FROM INFORMATION_SCHEMA.INNODB_TABLES t,
18 INFORMATION_SCHEMA.INNODB_INDEXES i
19 WHERE t.table_id = i.table_id
20 AND t.name LIKE 'mysql%'
21 AND t.name NOT LIKE 'mysql/ndb_binlog_index'
22 ORDER BY t.name, i.index_id;
23 table_name n_cols table_flags index_name root_page type n_fields merge_threshold
24 CREATE TABLE t1 (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
25 CREATE TABLE t2 (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
26 CREATE TABLE t3 (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
27 CREATE TABLE t4 (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
28 SELECT t.name table_name, t.n_cols, t.flag table_flags,
29 i.name index_name, i.page_no root_page, i.type,
30 i.n_fields, i.merge_threshold
31 FROM INFORMATION_SCHEMA.INNODB_TABLES t,
32 INFORMATION_SCHEMA.INNODB_INDEXES i
33 WHERE t.table_id = i.table_id
34 AND t.name LIKE 'test%'
35 ORDER BY t.name, i.name;
36 table_name n_cols table_flags index_name root_page type n_fields merge_threshold
37 test/t1 5 0 PRIMARY 4 3 4 50
38 test/t2 5 1 PRIMARY 4 3 4 50
39 test/t3 5 41 PRIMARY 4 3 4 50
40 test/t4 5 33 PRIMARY 4 3 4 50
41 === information_schema.innodb_tablespaces and innodb_datafiles ===
42 Space_Name Space_Type Page_Size Zip_Size BlockSize!=0 FileSize!=0 Formats_Permitted Path
43 mtr/asserted_test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
44 mtr/global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/global_suppressions.ibd
45 mtr/test_ignored_global_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
46 mtr/test_suppressions Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/mtr/test_suppressions.ibd
47 test/t1 Single DEFAULT 0 1 1 Compact or Redundant MYSQLD_DATADIR/test/t1.ibd
48 test/t2 Single DEFAULT 0 1 1 Compact or Redundant MYSQLD_DATADIR/test/t2.ibd
49 test/t3 Single DEFAULT 8192 1 1 Compressed MYSQLD_DATADIR/test/t3.ibd
50 test/t4 Single DEFAULT 0 1 1 Dynamic MYSQLD_DATADIR/test/t4.ibd
51 === information_schema.files ===
52 Space_Name File_Type Engine Status Tablespace_Name Path
53 mtr/asserted_test_suppressions TABLESPACE InnoDB NORMAL mtr/asserted_test_suppressions MYSQLD_DATADIR/mtr/asserted_test_suppressions.ibd
54 mtr/global_suppressions TABLESPACE InnoDB NORMAL mtr/global_suppressions MYSQLD_DATADIR/mtr/global_suppressions.ibd
55 mtr/test_ignored_global_suppressions TABLESPACE InnoDB NORMAL mtr/test_ignored_global_suppressions MYSQLD_DATADIR/mtr/test_ignored_global_suppressions.ibd
56 mtr/test_suppressions TABLESPACE InnoDB NORMAL mtr/test_suppressions MYSQLD_DATADIR/mtr/test_suppressions.ibd
57 test/t1 TABLESPACE InnoDB NORMAL test/t1 MYSQLD_DATADIR/test/t1.ibd
58 test/t2 TABLESPACE InnoDB NORMAL test/t2 MYSQLD_DATADIR/test/t2.ibd
59 test/t3 TABLESPACE InnoDB NORMAL test/t3 MYSQLD_DATADIR/test/t3.ibd
60 test/t4 TABLESPACE InnoDB NORMAL test/t4 MYSQLD_DATADIR/test/t4.ibd
61 DROP TABLE t1, t2, t3, t4;
62 # Test 4) The maximum row size is dependent upon the page size.
63 # Redundant: 8123, Compact: 8126.
64 # Compressed: 8126, Dynamic: 8126.
65 # Each row format has its own amount of overhead that
66 # varies depending on number of fields and other overhead.
67 SET SESSION innodb_strict_mode = ON;
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(211)
77 ) charset latin1 ROW_FORMAT=redundant;
78 DROP TABLE t1;
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(200), c40 char(212)
88 ) charset latin1 ROW_FORMAT=redundant;
89 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.
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(246)
99 ) charset latin1 ROW_FORMAT=compact;
100 DROP TABLE t1;
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(200), c39 char(250), c40 char(247)
110 ) charset latin1 ROW_FORMAT=compact;
111 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.
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(157)
121 ) ROW_FORMAT=compressed;
122 DROP TABLE t1;
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(200), c40 char(160)
132 ) charset latin1 ROW_FORMAT=compressed;
133 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.
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(246)
143 ) ROW_FORMAT=dynamic;
144 DROP TABLE t1;
145 CREATE TABLE t1 (
146 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
147 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
148 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
149 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
150 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
151 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
152 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
153 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
154 ) charset latin1 ROW_FORMAT=dynamic;
155 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.
156 CREATE TABLE t1 (a varchar(255) character set utf8,
157 b varchar(255) character set utf8,
158 c varchar(255) character set utf8,
159 d varchar(255) character set utf8,
160 e varchar(4) character set utf8,
161 PRIMARY KEY (a,b,c,d,e))
162 ENGINE=innodb;
163 Warnings:
164 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
165 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
166 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
167 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
168 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
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(5) character set utf8,
175 PRIMARY KEY (a,b,c,d,e))
176 ENGINE=innodb;
177 ERROR 42000: Specified key was too long; max key length is 3072 bytes
178 CREATE TABLE t1 (a varchar(255) character set utf8,
179 b varchar(255) character set utf8,
180 c varchar(255) character set utf8,
181 d varchar(255) character set utf8,
182 e varchar(255) character set utf8,
183 f varchar(4) character set utf8,
184 PRIMARY KEY (a), KEY (b,c,d,e,f))
185 ENGINE=innodb;
186 Warnings:
187 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
188 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
189 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
190 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
191 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
192 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
193 DROP TABLE t1;
194 CREATE TABLE t1 (a varchar(255) character set utf8,
195 b varchar(255) character set utf8,
196 c varchar(255) character set utf8,
197 d varchar(255) character set utf8,
198 e varchar(255) character set utf8,
199 f varchar(5) character set utf8,
200 PRIMARY KEY (a), KEY (b,c,d,e,f))
201 ENGINE=innodb;
202 ERROR 42000: Specified key was too long; max key length is 3072 bytes
203 # Test 5) Make sure that KEY_BLOCK_SIZE=16, 8, 4, 2 & 1
204 # are all accepted.
205 SET SESSION innodb_strict_mode = ON;
206 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
207 SHOW WARNINGS;
208 Level Code Message
209 SELECT table_name, row_format, create_options
210 FROM information_schema.tables WHERE table_name = 't1';
211 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
212 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16
213 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
214 SHOW WARNINGS;
215 Level Code Message
216 SELECT table_name, row_format, create_options
217 FROM information_schema.tables WHERE table_name = 't1';
218 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
219 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
220 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
221 SHOW WARNINGS;
222 Level Code Message
223 SELECT table_name, row_format, create_options
224 FROM information_schema.tables WHERE table_name = 't1';
225 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
226 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4
227 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
228 SHOW WARNINGS;
229 Level Code Message
230 SELECT table_name, row_format, create_options
231 FROM information_schema.tables WHERE table_name = 't1';
232 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
233 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
234 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
235 SHOW WARNINGS;
236 Level Code Message
237 SELECT table_name, row_format, create_options
238 FROM information_schema.tables WHERE table_name = 't1';
239 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
240 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
241 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
242 SHOW WARNINGS;
243 Level Code Message
244 SELECT table_name, row_format, create_options
245 FROM information_schema.tables WHERE table_name = 't1';
246 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
247 t1 Compressed row_format=COMPRESSED
248 DROP TABLE t1;
249 SET SESSION innodb_strict_mode = OFF;
250 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
251 SHOW WARNINGS;
252 Level Code Message
253 SELECT table_name, row_format, create_options
254 FROM information_schema.tables WHERE table_name = 't1';
255 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
256 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=16
257 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
258 SHOW WARNINGS;
259 Level Code Message
260 SELECT table_name, row_format, create_options
261 FROM information_schema.tables WHERE table_name = 't1';
262 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
263 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=8
264 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
265 SHOW WARNINGS;
266 Level Code Message
267 SELECT table_name, row_format, create_options
268 FROM information_schema.tables WHERE table_name = 't1';
269 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
270 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=4
271 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
272 SHOW WARNINGS;
273 Level Code Message
274 SELECT table_name, row_format, create_options
275 FROM information_schema.tables WHERE table_name = 't1';
276 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
277 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=2
278 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
279 SHOW WARNINGS;
280 Level Code Message
281 SELECT table_name, row_format, create_options
282 FROM information_schema.tables WHERE table_name = 't1';
283 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
284 t1 Compressed row_format=COMPRESSED KEY_BLOCK_SIZE=1
285 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
286 SHOW WARNINGS;
287 Level Code Message
288 SELECT table_name, row_format, create_options
289 FROM information_schema.tables WHERE table_name = 't1';
290 TABLE_NAME ROW_FORMAT CREATE_OPTIONS
291 t1 Compressed row_format=COMPRESSED
292 DROP TABLE t1;
293 # Test 6) Make sure that KEY_BLOCK_SIZE = 8 and 16
294 # are rejected when innodb_file_per_table=OFF
295 SET SESSION innodb_strict_mode = ON;
296 SET GLOBAL innodb_file_per_table = OFF;
297 SHOW VARIABLES LIKE 'innodb_file_per_table';
298 Variable_name Value
299 innodb_file_per_table OFF
300 CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
301 ERROR HY000: Table storage engine for 't4' doesn't have this option
302 SHOW WARNINGS;
303 Level Code Message
304 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
305 Error 1031 Table storage engine for 't4' doesn't have this option
306 CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
307 ERROR HY000: Table storage engine for 't5' doesn't have this option
308 SHOW WARNINGS;
309 Level Code Message
310 Warning 1478 InnoDB: KEY_BLOCK_SIZE requires innodb_file_per_table.
311 Error 1031 Table storage engine for 't5' doesn't have this option
312 SET GLOBAL innodb_file_per_table = ON;
313 CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
314 SHOW WARNINGS;
315 Level Code Message
316 CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
317 SHOW WARNINGS;
318 Level Code Message
319 DROP TABLE t4,t5;
320 # Test 7) This series of tests were moved from innodb-index to here
321 # because the second alter table t1 assumes a 16k page size.
322 # Moving the test allows the rest of innodb-index to be run on all
323 # page sizes. The previously disabled portions of this test were
324 # moved as well.
325 CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8;
326 Warnings:
327 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
328 CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb;
329 INSERT INTO t3 VALUES (22),(44),(33),(55),(66);
330 INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'),
331 ('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
332 CREATE TABLE t1(a int, b blob, c text, d text NOT NULL)
333 ENGINE=innodb DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
334 Warnings:
335 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
336 INSERT INTO t1
337 SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3 order by a, d;
338 DROP TABLE t2, t3;
339 SELECT COUNT(*) FROM t1 WHERE a=44;
340 COUNT(*)
341 5
342 SET SESSION sort_buffer_size = 1048576;
343 SELECT a,
344 LENGTH(b),b=LEFT(REPEAT(d,100*a),65535),LENGTH(c),c=REPEAT(d,20*a),d FROM t1
345 ORDER BY 1, 2, 3, 4, 5, 6;
346 a LENGTH(b) b=LEFT(REPEAT(d,100*a),65535) LENGTH(c) c=REPEAT(d,20*a) d
347 22 22000 1 4400 1 adfd72nh9k
348 22 22000 1 4400 1 jejdkrun87
349 22 26400 1 5280 1 adfdpplkeock
350 22 28600 1 5720 1 adfdijnmnb78k
351 22 35200 1 7040 1 adfdijn0loKNHJik
352 33 33000 1 6600 1 adfd72nh9k
353 33 33000 1 6600 1 jejdkrun87
354 33 39600 1 7920 1 adfdpplkeock
355 33 42900 1 8580 1 adfdijnmnb78k
356 33 52800 1 10560 1 adfdijn0loKNHJik
357 44 44000 1 8800 1 adfd72nh9k
358 44 44000 1 8800 1 jejdkrun87
359 44 52800 1 10560 1 adfdpplkeock
360 44 57200 1 11440 1 adfdijnmnb78k
361 44 65535 1 14080 1 adfdijn0loKNHJik
362 55 55000 1 11000 1 adfd72nh9k
363 55 55000 1 11000 1 jejdkrun87
364 55 65535 1 13200 1 adfdpplkeock
365 55 65535 1 14300 1 adfdijnmnb78k
366 55 65535 1 17600 1 adfdijn0loKNHJik
367 66 65535 1 13200 1 adfd72nh9k
368 66 65535 1 13200 1 jejdkrun87
369 66 65535 1 15840 1 adfdpplkeock
370 66 65535 1 17160 1 adfdijnmnb78k
371 66 65535 1 21120 1 adfdijn0loKNHJik
372 SET SESSION sort_buffer_size = DEFAULT;
373 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
374 ERROR 23000: Duplicate entry 'NN' for key 't1.PRIMARY'
375 DELETE FROM t1 WHERE d='null';
376 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
377 ERROR 23000: Duplicate entry 'NN' for key 't1.PRIMARY'
378 DELETE FROM t1 WHERE a%2;
379 CHECK TABLE t1;
380 Table Op Msg_type Msg_text
381 test.t1 check status OK
382 ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));
383 SELECT COUNT(*) FROM t1 WHERE a=44;
384 COUNT(*)
385 5
386 SELECT a,
387 LENGTH(b), b=LEFT(REPEAT(d,100*a), 65535),LENGTH(c), c=REPEAT(d,20*a), d FROM t1;
388 a LENGTH(b) b=LEFT(REPEAT(d,100*a), 65535) LENGTH(c) c=REPEAT(d,20*a) d
389 22 22000 1 4400 1 adfd72nh9k
390 22 35200 1 7040 1 adfdijn0loKNHJik
391 22 28600 1 5720 1 adfdijnmnb78k
392 22 26400 1 5280 1 adfdpplkeock
393 22 22000 1 4400 1 jejdkrun87
394 44 44000 1 8800 1 adfd72nh9k
395 44 65535 1 14080 1 adfdijn0loKNHJik
396 44 57200 1 11440 1 adfdijnmnb78k
397 44 52800 1 10560 1 adfdpplkeock
398 44 44000 1 8800 1 jejdkrun87
399 66 65535 1 13200 1 adfd72nh9k
400 66 65535 1 21120 1 adfdijn0loKNHJik
401 66 65535 1 17160 1 adfdijnmnb78k
402 66 65535 1 15840 1 adfdpplkeock
403 66 65535 1 13200 1 jejdkrun87
404 SHOW CREATE TABLE t1;
405 Table Create Table
406 t1 CREATE TABLE `t1` (
407 `a` int NOT NULL,
408 `b` blob NOT NULL,
409 `c` text NOT NULL,
410 `d` text NOT NULL,
411 PRIMARY KEY (`a`,`b`(255),`c`(255)),
412 KEY `b` (`b`(767))
413 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 STATS_PERSISTENT=0
414 CHECK TABLE t1;
415 Table Op Msg_type Msg_text
416 test.t1 check status OK
417 EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';
418 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
419 1 SIMPLE t1 NULL range b b 769 NULL 12 100.00 Using where
420 Warnings:
421 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d` from `test`.`t1` where (`test`.`t1`.`b` like 'adfd%')
422 DROP TABLE t1;
423 # Test 8) Test creating a table that could lead to undo log overflow.
424 CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,
425 h blob,i blob,j blob,k blob,l blob,m blob,n blob,
426 o blob,p blob,q blob,r blob,s blob,t blob,u blob)
427 ENGINE=InnoDB ROW_FORMAT=dynamic;
428 SET @a = repeat('a', 767);
429 SET @b = repeat('b', 767);
430 SET @c = repeat('c', 767);
431 SET @d = repeat('d', 767);
432 SET @e = repeat('e', 767);
433 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);
434 UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b,
435 k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b;
436 CREATE INDEX t1a ON t1 (a(767));
437 CREATE INDEX t1b ON t1 (b(767));
438 CREATE INDEX t1c ON t1 (c(767));
439 CREATE INDEX t1d ON t1 (d(767));
440 CREATE INDEX t1e ON t1 (e(767));
441 UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c,
442 k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c;
443 CREATE INDEX t1f ON t1 (f(767));
444 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d,f=@d,g=@d,h=@d,i=@d,j=@d,
445 k=@d,l=@d,m=@d,n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
446 ERROR HY000: Undo log record is too big.
447 BEGIN;
448 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d;
449 UPDATE t1 SET f=@d,g=@d,h=@d,i=@d,j=@d,k=@d,l=@d,m=@d,
450 n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
451 COMMIT;
452 CREATE INDEX t1g ON t1 (g(767));
453 UPDATE t1 SET g=@e;
454 CREATE INDEX t1h ON t1 (h(767));
455 UPDATE t1 SET h=@e;
456 CREATE INDEX t1i ON t1 (i(767));
457 UPDATE t1 SET i=@e;
458 CREATE INDEX t1j ON t1 (j(767));
459 UPDATE t1 SET j=@e;
460 CREATE INDEX t1k ON t1 (k(767));
461 UPDATE t1 SET k=@e;
462 CREATE INDEX t1l ON t1 (l(767));
463 UPDATE t1 SET l=@e;
464 CREATE INDEX t1m ON t1 (m(767));
465 UPDATE t1 SET m=@e;
466 CREATE INDEX t1n ON t1 (n(767));
467 UPDATE t1 SET n=@e;
468 CREATE INDEX t1o ON t1 (o(767));
469 UPDATE t1 SET o=@e;
470 CREATE INDEX t1p ON t1 (p(767));
471 UPDATE t1 SET p=@e;
472 CREATE INDEX t1q ON t1 (q(767));
473 UPDATE t1 SET q=@e;
474 CREATE INDEX t1r ON t1 (r(767));
475 UPDATE t1 SET r=@e;
476 CREATE INDEX t1s ON t1 (s(767));
477 UPDATE t1 SET s=@e;
478 CREATE INDEX t1t ON t1 (t(767));
479 UPDATE t1 SET t=@e;
480 ERROR HY000: Undo log record is too big.
481 CREATE INDEX t1u ON t1 (u(767));
482 CREATE INDEX t1ut ON t1 (u(767), t(767));
483 CREATE INDEX t1st ON t1 (s(767), t(767));
484 SHOW CREATE TABLE t1;
485 Table Create Table
486 t1 CREATE TABLE `t1` (
487 `a` blob,
488 `b` blob,
489 `c` blob,
490 `d` blob,
491 `e` blob,
492 `f` blob,
493 `g` blob,
494 `h` blob,
495 `i` blob,
496 `j` blob,
497 `k` blob,
498 `l` blob,
499 `m` blob,
500 `n` blob,
501 `o` blob,
502 `p` blob,
503 `q` blob,
504 `r` blob,
505 `s` blob,
506 `t` blob,
507 `u` blob,
508 KEY `t1a` (`a`(767)),
509 KEY `t1b` (`b`(767)),
510 KEY `t1c` (`c`(767)),
511 KEY `t1d` (`d`(767)),
512 KEY `t1e` (`e`(767)),
513 KEY `t1f` (`f`(767)),
514 KEY `t1g` (`g`(767)),
515 KEY `t1h` (`h`(767)),
516 KEY `t1i` (`i`(767)),
517 KEY `t1j` (`j`(767)),
518 KEY `t1k` (`k`(767)),
519 KEY `t1l` (`l`(767)),
520 KEY `t1m` (`m`(767)),
521 KEY `t1n` (`n`(767)),
522 KEY `t1o` (`o`(767)),
523 KEY `t1p` (`p`(767)),
524 KEY `t1q` (`q`(767)),
525 KEY `t1r` (`r`(767)),
526 KEY `t1s` (`s`(767)),
527 KEY `t1t` (`t`(767)),
528 KEY `t1u` (`u`(767)),
529 KEY `t1ut` (`u`(767),`t`(767)),
530 KEY `t1st` (`s`(767),`t`(767))
531 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
532 DROP TABLE t1;
533 # Bug #12429576 - Test an assertion failure on purge.
534 CREATE TABLE t1_purge (
535 A int,
536 B blob, C blob, D blob, E blob,
537 F blob, G blob, H blob,
538 PRIMARY KEY (B(767), C(767), D(767), E(767), A),
539 INDEX (A)
540 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
541 INSERT INTO t1_purge VALUES (1,
542 REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
543 REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766));
544 CREATE TABLE t2_purge (
545 A int PRIMARY KEY,
546 B blob, C blob, D blob, E blob,
547 F blob, G blob, H blob, I blob,
548 J blob, K blob, L blob,
549 INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
550 INSERT INTO t2_purge VALUES (1,
551 REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
552 REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766), REPEAT('i', 766),
553 REPEAT('j', 766), REPEAT('k', 766), REPEAT('l', 766));
554 CREATE TABLE t3_purge (
555 A int,
556 B varchar(800), C varchar(800), D varchar(800), E varchar(800),
557 F varchar(800), G varchar(800), H varchar(800),
558 PRIMARY KEY (B(767), C(767), D(767), E(767), A),
559 INDEX (A)
560 ) charset latin1 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
561 INSERT INTO t3_purge SELECT * FROM t1_purge;
562 CREATE TABLE t4_purge (
563 A int PRIMARY KEY,
564 B varchar(800), C varchar(800), D varchar(800), E varchar(800),
565 F varchar(800), G varchar(800), H varchar(800), I varchar(800),
566 J varchar(800), K varchar(800), L varchar(800),
567 INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
568 INSERT INTO t4_purge SELECT * FROM t2_purge;
569 DELETE FROM t1_purge;
570 DELETE FROM t2_purge;
571 DELETE FROM t3_purge;
572 DELETE FROM t4_purge;
573 SET @r=REPEAT('a',500);
574 CREATE TABLE t12637786(a int,
575 v1 varchar(500), v2 varchar(500), v3 varchar(500),
576 v4 varchar(500), v5 varchar(500), v6 varchar(500),
577 v7 varchar(500), v8 varchar(500), v9 varchar(500),
578 v10 varchar(500), v11 varchar(500), v12 varchar(500),
579 v13 varchar(500), v14 varchar(500), v15 varchar(500),
580 v16 varchar(500), v17 varchar(500), v18 varchar(500)
581 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
582 CREATE INDEX idx1 ON t12637786(a,v1);
583 INSERT INTO t12637786 VALUES(9,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
584 UPDATE t12637786 SET a=1000;
585 DELETE FROM t12637786;
586 # Bug#12963823 - Test that the purge thread does not crash when
587 CREATE TABLE t12963823(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob,
588 i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob)
589 ENGINE=innodb ROW_FORMAT=dynamic;
590 SET @r = REPEAT('a', 767);
591 INSERT INTO t12963823 VALUES (@r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r);
592 CREATE INDEX ndx_a ON t12963823 (a(500));
593 CREATE INDEX ndx_b ON t12963823 (b(500));
594 CREATE INDEX ndx_c ON t12963823 (c(500));
595 CREATE INDEX ndx_d ON t12963823 (d(500));
596 CREATE INDEX ndx_e ON t12963823 (e(500));
597 CREATE INDEX ndx_f ON t12963823 (f(500));
598 CREATE INDEX ndx_k ON t12963823 (k(500));
599 CREATE INDEX ndx_l ON t12963823 (l(500));
600 SET @r = REPEAT('b', 500);
601 UPDATE t12963823 set a=@r,b=@r,c=@r,d=@r;
602 UPDATE t12963823 set e=@r,f=@r,g=@r,h=@r;
603 UPDATE t12963823 set i=@r,j=@r,k=@r,l=@r;
604 UPDATE t12963823 set m=@r,n=@r,o=@r,p=@r;
605 ALTER TABLE t12963823 DROP INDEX ndx_a;
606 ALTER TABLE t12963823 DROP INDEX ndx_b;
607 CREATE INDEX ndx_g ON t12963823 (g(500));
608 CREATE INDEX ndx_h ON t12963823 (h(500));
609 CREATE INDEX ndx_i ON t12963823 (i(500));
610 CREATE INDEX ndx_j ON t12963823 (j(500));
611 CREATE INDEX ndx_m ON t12963823 (m(500));
612 CREATE INDEX ndx_n ON t12963823 (n(500));
613 CREATE INDEX ndx_o ON t12963823 (o(500));
614 CREATE INDEX ndx_p ON t12963823 (p(500));
615 SHOW CREATE TABLE t12963823;
616 Table Create Table
617 t12963823 CREATE TABLE `t12963823` (
618 `a` blob,
619 `b` blob,
620 `c` blob,
621 `d` blob,
622 `e` blob,
623 `f` blob,
624 `g` blob,
625 `h` blob,
626 `i` blob,
627 `j` blob,
628 `k` blob,
629 `l` blob,
630 `m` blob,
631 `n` blob,
632 `o` blob,
633 `p` blob,
634 KEY `ndx_c` (`c`(500)),
635 KEY `ndx_d` (`d`(500)),
636 KEY `ndx_e` (`e`(500)),
637 KEY `ndx_f` (`f`(500)),
638 KEY `ndx_k` (`k`(500)),
639 KEY `ndx_l` (`l`(500)),
640 KEY `ndx_g` (`g`(500)),
641 KEY `ndx_h` (`h`(500)),
642 KEY `ndx_i` (`i`(500)),
643 KEY `ndx_j` (`j`(500)),
644 KEY `ndx_m` (`m`(500)),
645 KEY `ndx_n` (`n`(500)),
646 KEY `ndx_o` (`o`(500)),
647 KEY `ndx_p` (`p`(500))
648 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
649 # Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE
650 SET SESSION innodb_strict_mode = ON;
651 CREATE TABLE bug12547647(
652 a int NOT NULL, b blob NOT NULL, c text,
653 PRIMARY KEY (b(10), a), INDEX (c(767)), INDEX(b(767))
654 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
655 INSERT INTO bug12547647 VALUES (5,REPEAT('khdfo5AlOq',1900),REPEAT('g',7751));
656 COMMIT;
657 UPDATE bug12547647 SET c = REPEAT('b',16928);
658 ERROR HY000: Undo log record is too big.
659 SHOW WARNINGS;
660 Level Code Message
661 Error 1713 Undo log record is too big.
662 DROP TABLE bug12547647;
663 SET SESSION innodb_strict_mode = off;
664 CREATE TABLE t1(
665 c text NOT NULL, d text NOT NULL,
666 PRIMARY KEY (c(767),d(767)))
667 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
668 Warnings:
669 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.
670 DROP TABLE t1;
671 CREATE TABLE t1(
672 c text NOT NULL, d text NOT NULL,
673 PRIMARY KEY (c(767),d(767)))
674 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 CHARSET=ASCII;
675 Warnings:
676 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.
677 DROP TABLE t1;
678 CREATE TABLE t1(
679 c text NOT NULL, d text NOT NULL,
680 PRIMARY KEY (c(767),d(767)))
681 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
682 drop table t1;
683 CREATE TABLE t1(c text, PRIMARY KEY (c(440)))
684 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
685 Warnings:
686 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.
687 DROP TABLE t1;
688 CREATE TABLE t1(c text, PRIMARY KEY (c(438)))
689 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
690 INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
691 DROP TABLE t1;
692 #
693 # Bug#56862 Execution of a query that uses index merge returns a wrong result
694 #
695 CREATE TABLE t1 (
696 pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
697 a int,
698 b int,
699 INDEX idx(a))
700 ENGINE=INNODB;
701 INSERT INTO t1(a,b) VALUES
702 (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
703 (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
704 (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
705 (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
706 INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
707 INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
708 INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
709 INSERT INTO t1(a,b) SELECT a,b FROM t1;
710 INSERT INTO t1(a,b) SELECT a,b FROM t1;
711 INSERT INTO t1(a,b) SELECT a,b FROM t1;
712 INSERT INTO t1(a,b) SELECT a,b FROM t1;
713 INSERT INTO t1(a,b) SELECT a,b FROM t1;
714 INSERT INTO t1(a,b) SELECT a,b FROM t1;
715 INSERT INTO t1(a,b) SELECT a,b FROM t1;
716 INSERT INTO t1(a,b) SELECT a,b FROM t1;
717 INSERT INTO t1 VALUES (1000000, 0, 0);
718 set @optimizer_switch_saved=@@optimizer_switch;
719 SET SESSION optimizer_switch='derived_merge=off';
720 SET SESSION sort_buffer_size = 1024*36;
721 EXPLAIN
722 SELECT COUNT(*) FROM
723 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
724 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
725 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
726 1 PRIMARY <derived2> NULL ALL NULL NULL NULL NULL 1537 100.00 NULL
727 2 DERIVED t1 NULL index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 1537 100.00 Using sort_union(idx,PRIMARY); Using where
728 Warnings:
729 Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from (/* select#2 */ select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` FORCE INDEX (PRIMARY) FORCE INDEX (`idx`) where ((`test`.`t1`.`a` between 2 and 7) or (`test`.`t1`.`pk` = 1000000))) `t`
730 SELECT COUNT(*) FROM
731 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
732 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
733 COUNT(*)
734 1537
735 set @@optimizer_switch=@optimizer_switch_saved;
736 SET SESSION sort_buffer_size = DEFAULT;
737 DROP TABLE t1;
738 DROP TABLE t1_purge, t2_purge, t3_purge, t4_purge;
739 DROP TABLE t12637786;
740 DROP TABLE t12963823;