Debian Codegraph @

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

raw
1
2 # Tests for setting innodb-page-size=16k; default value
3 --source include/big_test.inc
4
5 SET default_storage_engine=InnoDB;
6
7 --disable_query_log
8 let $MYSQLD_DATADIR = `select @@datadir`;
9 let $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
10
11 call mtr.add_suppression("Cannot create the table .* because the record size will exceed the maximum allowed size for a record.");
12 # These values can change during the test
13 --enable_query_log
14
15 --echo # Test 1) Show the page size from Information Schema
16 --disable_warnings
17 SELECT variable_value FROM performance_schema.global_status
18 WHERE LOWER(variable_name) = 'innodb_page_size';
19 --enable_warnings
20
21 --echo # Test 2) The number of buffer pool pages is dependent upon the page size.
22 --disable_warnings
23 --replace_result 1535 {checked_valid} 1536 {checked_valid}
24 SELECT variable_value FROM performance_schema.global_status
25 WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total';
26 --enable_warnings
27
28 --echo # Test 3) Query some information_shema tables that are dependent upon
29 --echo # the page size.
30 # Show the metadata for tables in schema 'mysql'.
31 # Pulled from innodb-system-table-view.test
32 # The IDs of mysql.innodb_table_stats and mysql.innodb_index_stats are
33 # unpredictable. They depend on whether mtr has created the database for
34 # this test from scratch or is using a previously created database where
35 # those tables have been dropped and recreated. Since we cannot force mtr
36 # to use a freshly created database for this test we do not return the
37 # table or index IDs. We can return the space IS of mysql schema tables
38 # since they are created consistently during bootstrap.
39 SELECT t.name table_name, t.n_cols, t.flag table_flags,
40 i.name index_name, i.page_no root_page, i.type,
41 i.n_fields, i.merge_threshold
42 FROM INFORMATION_SCHEMA.INNODB_TABLES t,
43 INFORMATION_SCHEMA.INNODB_INDEXES i
44 WHERE t.table_id = i.table_id
45 AND t.name LIKE 'mysql%'
46 AND t.name NOT LIKE 'mysql/ndb_binlog_index'
47 ORDER BY t.name, i.index_id;
48
49 CREATE TABLE t1 (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
50 CREATE TABLE t2 (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
51 CREATE TABLE t3 (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
52 CREATE TABLE t4 (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
53
54 # Show the metadata for tables in schema 'test'.
55 # Do not return the space ID since this tablespace may have existed before
56 # this test runs. The root page number of each index should be consistent
57 # within a file-per-table tablespace.
58 SELECT t.name table_name, t.n_cols, t.flag table_flags,
59 i.name index_name, i.page_no root_page, i.type,
60 i.n_fields, i.merge_threshold
61 FROM INFORMATION_SCHEMA.INNODB_TABLES t,
62 INFORMATION_SCHEMA.INNODB_INDEXES i
63 WHERE t.table_id = i.table_id
64 AND t.name LIKE 'test%'
65 ORDER BY t.name, i.name;
66 --source suite/innodb/include/show_i_s_tablespaces.inc
67 DROP TABLE t1, t2, t3, t4;
68
69 --echo # Test 4) The maximum row size is dependent upon the page size.
70 --echo # Redundant: 8123, Compact: 8126.
71 --echo # Compressed: 8126, Dynamic: 8126.
72 --echo # Each row format has its own amount of overhead that
73 --echo # varies depending on number of fields and other overhead.
74
75 SET SESSION innodb_strict_mode = ON;
76
77 # Redundant table; 8122 bytes with 40 char fields
78 # Calculation :
79 # 6 (extra bytes)
80 # 2 * 43 = 86 (fields len) = 2 * n_fields (3 system fields + 40 user fields)
81 # 6 + 6 + 7 = 19 (ROW_ID, TRX_ID, ROLL_PTR)
82 # 200 * 39 = 7800 (each char(200) field)
83 # 211 = (char(211))
84 # T0tal = 8122
85 # NOTE : charset latin1 causes each char to have max 1 bytes. If we use default
86 # (utf8_mb4) then each char will need 4 bytes. So char(200) will need
87 # max 800 bytes.
88 CREATE TABLE t1 (
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(200), c40 char(211)
97 ) charset latin1 ROW_FORMAT=redundant;
98 DROP TABLE t1;
99 # Redundant table; 8123 bytes with 40 char fields
100 --error ER_TOO_BIG_ROWSIZE
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(200), c40 char(212)
110 ) charset latin1 ROW_FORMAT=redundant;
111
112 # Compact table; 8125 bytes with 40 CHAR fields
113 # Calculation :
114 # 5 (extra bytes)
115 # 5 (null bitmap)
116 # 6 + 6 + 7 = 19 (ROW_ID, TRX_ID, ROLL_PTR)
117 # 200 * 38 = 7600 (each char(200) field)
118 # 250 = (char(250))
119 # 246 = (char(246))
120 # Total = 8125
121 # NOTE : charset latin1 causes each char to have max 1 bytes. If we use default
122 # (utf8_mb4) then each char will need 4 bytes. So char(200) will need
123 # max 800 bytes.
124 CREATE TABLE t1 (
125 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
126 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
127 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
128 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
129 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
130 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
131 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
132 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
133 ) charset latin1 ROW_FORMAT=compact;
134 DROP TABLE t1;
135 # Compact table; 8126 bytes with 40 CHAR fields
136 --error ER_TOO_BIG_ROWSIZE
137 CREATE TABLE t1 (
138 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
139 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
140 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
141 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
142 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
143 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
144 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
145 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
146 ) charset latin1 ROW_FORMAT=compact;
147
148 # Compressed table; 7959 bytes with 40 CHAR fields
149 # Bug#13391353 Limit is 7957 on 32-Linux only
150 CREATE TABLE t1 (
151 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
152 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
153 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
154 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
155 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
156 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
157 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
158 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(157)
159 ) ROW_FORMAT=compressed;
160 DROP TABLE t1;
161 --error ER_TOO_BIG_ROWSIZE
162 CREATE TABLE t1 (
163 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
164 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
165 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
166 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
167 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
168 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
169 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
170 c36 char(200), c37 char(200), c38 char(200), c39 char(200), c40 char(160)
171 ) charset latin1 ROW_FORMAT=compressed;
172
173 # Dynamic table; 8096 bytes with 40 CHAR fields
174 CREATE TABLE t1 (
175 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
176 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
177 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
178 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
179 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
180 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
181 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
182 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(246)
183 ) ROW_FORMAT=dynamic;
184 DROP TABLE t1;
185 --error ER_TOO_BIG_ROWSIZE
186 CREATE TABLE t1 (
187 c01 char(200), c02 char(200), c03 char(200), c04 char(200), c05 char(200),
188 c06 char(200), c07 char(200), c08 char(200), c09 char(200), c10 char(200),
189 c11 char(200), c12 char(200), c13 char(200), c14 char(200), c15 char(200),
190 c16 char(200), c17 char(200), c18 char(200), c19 char(200), c20 char(200),
191 c21 char(200), c22 char(200), c23 char(200), c24 char(200), c25 char(200),
192 c26 char(200), c27 char(200), c28 char(200), c29 char(200), c30 char(200),
193 c31 char(200), c32 char(200), c33 char(200), c34 char(200), c35 char(200),
194 c36 char(200), c37 char(200), c38 char(200), c39 char(250), c40 char(247)
195 ) charset latin1 ROW_FORMAT=dynamic;
196
197 #
198 # Test the maximum key length
199 # Moved from innodb-index.test since each page size has its own max key length.
200 # Max Key Length is 3072 for 16k pages.
201 # Max key Part length is 767
202 # InnoDB assumes 3 bytes for each UTF8 character.
203 #
204 CREATE TABLE t1 (a varchar(255) character set utf8,
205 b varchar(255) character set utf8,
206 c varchar(255) character set utf8,
207 d varchar(255) character set utf8,
208 e varchar(4) character set utf8,
209 PRIMARY KEY (a,b,c,d,e))
210 ENGINE=innodb;
211 DROP TABLE t1;
212 --error ER_TOO_LONG_KEY
213 CREATE TABLE t1 (a varchar(255) character set utf8,
214 b varchar(255) character set utf8,
215 c varchar(255) character set utf8,
216 d varchar(255) character set utf8,
217 e varchar(5) character set utf8,
218 PRIMARY KEY (a,b,c,d,e))
219 ENGINE=innodb;
220 CREATE TABLE t1 (a varchar(255) character set utf8,
221 b varchar(255) character set utf8,
222 c varchar(255) character set utf8,
223 d varchar(255) character set utf8,
224 e varchar(255) character set utf8,
225 f varchar(4) character set utf8,
226 PRIMARY KEY (a), KEY (b,c,d,e,f))
227 ENGINE=innodb;
228 DROP TABLE t1;
229 --error ER_TOO_LONG_KEY
230 CREATE TABLE t1 (a varchar(255) character set utf8,
231 b varchar(255) character set utf8,
232 c varchar(255) character set utf8,
233 d varchar(255) character set utf8,
234 e varchar(255) character set utf8,
235 f varchar(5) character set utf8,
236 PRIMARY KEY (a), KEY (b,c,d,e,f))
237 ENGINE=innodb;
238
239 --echo # Test 5) Make sure that KEY_BLOCK_SIZE=16, 8, 4, 2 & 1
240 --echo # are all accepted.
241
242 SET SESSION innodb_strict_mode = ON;
243
244 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
245 SHOW WARNINGS;
246 SELECT table_name, row_format, create_options
247 FROM information_schema.tables WHERE table_name = 't1';
248
249 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
250 SHOW WARNINGS;
251 SELECT table_name, row_format, create_options
252 FROM information_schema.tables WHERE table_name = 't1';
253
254 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
255 SHOW WARNINGS;
256 SELECT table_name, row_format, create_options
257 FROM information_schema.tables WHERE table_name = 't1';
258
259 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
260 SHOW WARNINGS;
261 SELECT table_name, row_format, create_options
262 FROM information_schema.tables WHERE table_name = 't1';
263
264 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
265 SHOW WARNINGS;
266 SELECT table_name, row_format, create_options
267 FROM information_schema.tables WHERE table_name = 't1';
268
269 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
270 SHOW WARNINGS;
271 SELECT table_name, row_format, create_options
272 FROM information_schema.tables WHERE table_name = 't1';
273 DROP TABLE t1;
274
275 SET SESSION innodb_strict_mode = OFF;
276
277 CREATE TABLE t1 (i int) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=16;
278 SHOW WARNINGS;
279 SELECT table_name, row_format, create_options
280 FROM information_schema.tables WHERE table_name = 't1';
281
282 ALTER TABLE t1 KEY_BLOCK_SIZE=8;
283 SHOW WARNINGS;
284 SELECT table_name, row_format, create_options
285 FROM information_schema.tables WHERE table_name = 't1';
286
287 ALTER TABLE t1 KEY_BLOCK_SIZE=4;
288 SHOW WARNINGS;
289 SELECT table_name, row_format, create_options
290 FROM information_schema.tables WHERE table_name = 't1';
291
292 ALTER TABLE t1 KEY_BLOCK_SIZE=2;
293 SHOW WARNINGS;
294 SELECT table_name, row_format, create_options
295 FROM information_schema.tables WHERE table_name = 't1';
296
297 ALTER TABLE t1 KEY_BLOCK_SIZE=1;
298 SHOW WARNINGS;
299 SELECT table_name, row_format, create_options
300 FROM information_schema.tables WHERE table_name = 't1';
301
302 ALTER TABLE t1 KEY_BLOCK_SIZE=0;
303 SHOW WARNINGS;
304 SELECT table_name, row_format, create_options
305 FROM information_schema.tables WHERE table_name = 't1';
306 DROP TABLE t1;
307
308
309 --echo # Test 6) Make sure that KEY_BLOCK_SIZE = 8 and 16
310 --echo # are rejected when innodb_file_per_table=OFF
311 # Moved from innodb-zip.test
312 SET SESSION innodb_strict_mode = ON;
313 SET GLOBAL innodb_file_per_table = OFF;
314 SHOW VARIABLES LIKE 'innodb_file_per_table';
315 --error ER_ILLEGAL_HA
316 CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
317 SHOW WARNINGS;
318 --error ER_ILLEGAL_HA
319 CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
320 SHOW WARNINGS;
321 SET GLOBAL innodb_file_per_table = ON;
322 CREATE TABLE t4 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=8;
323 SHOW WARNINGS;
324 CREATE TABLE t5 (id int PRIMARY KEY) ENGINE=innodb KEY_BLOCK_SIZE=16;
325 SHOW WARNINGS;
326 DROP TABLE t4,t5;
327
328 --echo # Test 7) This series of tests were moved from innodb-index to here
329 --echo # because the second alter table t1 assumes a 16k page size.
330 --echo # Moving the test allows the rest of innodb-index to be run on all
331 --echo # page sizes. The previously disabled portions of this test were
332 --echo # moved as well.
333
334 CREATE TABLE t2(d varchar(17) PRIMARY KEY) ENGINE=innodb DEFAULT CHARSET=utf8;
335 CREATE TABLE t3(a int PRIMARY KEY) ENGINE=innodb;
336
337 INSERT INTO t3 VALUES (22),(44),(33),(55),(66);
338
339 INSERT INTO t2 VALUES ('jejdkrun87'),('adfd72nh9k'),
340 ('adfdpplkeock'),('adfdijnmnb78k'),('adfdijn0loKNHJik');
341
342 CREATE TABLE t1(a int, b blob, c text, d text NOT NULL)
343 ENGINE=innodb DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
344
345 INSERT INTO t1
346 SELECT a,LEFT(REPEAT(d,100*a),65535),REPEAT(d,20*a),d FROM t2,t3 order by a, d;
347 DROP TABLE t2, t3;
348 SELECT COUNT(*) FROM t1 WHERE a=44;
349 SET SESSION sort_buffer_size = 1048576;
350 SELECT a,
351 LENGTH(b),b=LEFT(REPEAT(d,100*a),65535),LENGTH(c),c=REPEAT(d,20*a),d FROM t1
352 ORDER BY 1, 2, 3, 4, 5, 6;
353 SET SESSION sort_buffer_size = DEFAULT;
354 # in-place alter table should trigger ER_PRIMARY_CANT_HAVE_NULL
355 --replace_regex /'\d\d'/'NN'/
356 --error ER_DUP_ENTRY
357 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
358 DELETE FROM t1 WHERE d='null';
359 --replace_regex /'\d\d'/'NN'/
360 --error ER_DUP_ENTRY
361 ALTER TABLE t1 ADD PRIMARY KEY (a), ADD KEY (b(20));
362 DELETE FROM t1 WHERE a%2;
363 CHECK TABLE t1;
364 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
365 # And adding a PRIMARY KEY will also add NOT NULL implicitly!
366 ALTER TABLE t1 ADD PRIMARY KEY (a,b(255),c(255)), ADD KEY (b(767));
367 SELECT COUNT(*) FROM t1 WHERE a=44;
368 SELECT a,
369 LENGTH(b), b=LEFT(REPEAT(d,100*a), 65535),LENGTH(c), c=REPEAT(d,20*a), d FROM t1;
370 SHOW CREATE TABLE t1;
371 CHECK TABLE t1;
372 EXPLAIN SELECT * FROM t1 WHERE b LIKE 'adfd%';
373
374 # The following tests are disabled because of the introduced timeouts for
375 # metadata locks at the MySQL level as part of the fix for
376 # Bug#45225 Locking: hang if drop table with no timeout
377 # The following commands now play with MySQL metadata locks instead of
378 # InnoDB locks
379 # start disabled45225_1
380 ##
381 ## Test locking
382 ##
383 #
384 #CREATE TABLE t2(a int, b varchar(255), PRIMARY KEY(a,b)) ENGINE=innodb;
385 #INSERT INTO t2 SELECT a,LEFT(b,255) FROM t1;
386 #DROP TABLE t1;
387 #RENAME TABLE t2 to t1;
388 #
389 #connect (a,localhost,root,,);
390 #connect (b,localhost,root,,);
391 #connection a;
392 #SET innodb_lock_wait_timeout=1;
393 #begin;
394 ## Obtain an IX lock on the table
395 #SELECT a FROM t1 limit 1 FOR UPDATE;
396 #connection b;
397 #SET innodb_lock_wait_timeout=1;
398 ## This would require an S lock on the table, conflicting with the IX lock.
399 #--error ER_LOCK_WAIT_TIMEOUT
400 #CREATE INDEX t1ba ON t1 (b,a);
401 #connection a;
402 #commit;
403 #begin;
404 ## Obtain an IS lock on the table
405 #SELECT a FROM t1 limit 1 lock in share mode;
406 #connection b;
407 ## This will require an S lock on the table. No conflict with the IS lock.
408 #CREATE INDEX t1ba ON t1 (b,a);
409 ## This would require an X lock on the table, conflicting with the IS lock.
410 #--error ER_LOCK_WAIT_TIMEOUT
411 #DROP INDEX t1ba ON t1;
412 #connection a;
413 #commit;
414 #EXPLAIN SELECT a FROM t1 ORDER BY b;
415 #--send
416 #SELECT a,sleep(2+a/100) FROM t1 ORDER BY b limit 3;
417 #
418 ## The following DROP INDEX will succeed, altough the SELECT above has
419 ## opened a read view. However, during the execution of the SELECT,
420 ## MySQL should hold a table lock that should block the execution
421 ## of the DROP INDEX below.
422 #
423 #connection b;
424 #SELECT sleep(1);
425 #DROP INDEX t1ba ON t1;
426 #
427 ## After the index was dropped, subsequent SELECTs will use the same
428 ## read view, but they should not be accessing the dropped index any more.
429 #
430 #connection a;
431 #reap;
432 #EXPLAIN SELECT a FROM t1 ORDER BY b;
433 #SELECT a FROM t1 ORDER BY b limit 3;
434 #commit;
435 #
436 #connection default;
437 #disconnect a;
438 #disconnect b;
439 #
440 # end disabled45225_1
441 DROP TABLE t1;
442
443 --echo # Test 8) Test creating a table that could lead to undo log overflow.
444 CREATE TABLE t1(a blob,b blob,c blob,d blob,e blob,f blob,g blob,
445 h blob,i blob,j blob,k blob,l blob,m blob,n blob,
446 o blob,p blob,q blob,r blob,s blob,t blob,u blob)
447 ENGINE=InnoDB ROW_FORMAT=dynamic;
448 SET @a = repeat('a', 767);
449 SET @b = repeat('b', 767);
450 SET @c = repeat('c', 767);
451 SET @d = repeat('d', 767);
452 SET @e = repeat('e', 767);
453
454 # With no indexes defined, we can update all columns to max key part length.
455 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);
456 UPDATE t1 SET a=@b,b=@b,c=@b,d=@b,e=@b,f=@b,g=@b,h=@b,i=@b,j=@b,
457 k=@b,l=@b,m=@b,n=@b,o=@b,p=@b,q=@b,r=@b,s=@b,t=@b,u=@b;
458
459 # With this many indexes defined, we can still update all fields.
460 CREATE INDEX t1a ON t1 (a(767));
461 CREATE INDEX t1b ON t1 (b(767));
462 CREATE INDEX t1c ON t1 (c(767));
463 CREATE INDEX t1d ON t1 (d(767));
464 CREATE INDEX t1e ON t1 (e(767));
465 UPDATE t1 SET a=@c,b=@c,c=@c,d=@c,e=@c,f=@c,g=@c,h=@c,i=@c,j=@c,
466 k=@c,l=@c,m=@c,n=@c,o=@c,p=@c,q=@c,r=@c,s=@c,t=@c,u=@c;
467
468 # Add one more index and the UNDO record becomes too big to update all columns.
469 # But a single transaction can update the columns in separate statements.
470 # because the UNDO records will be smaller.
471 CREATE INDEX t1f ON t1 (f(767));
472 --error ER_UNDO_RECORD_TOO_BIG
473 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d,f=@d,g=@d,h=@d,i=@d,j=@d,
474 k=@d,l=@d,m=@d,n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
475 BEGIN;
476 UPDATE t1 SET a=@d,b=@d,c=@d,d=@d,e=@d;
477 UPDATE t1 SET f=@d,g=@d,h=@d,i=@d,j=@d,k=@d,l=@d,m=@d,
478 n=@d,o=@d,p=@d,q=@d,r=@d,s=@d,t=@d,u=@d;
479 COMMIT;
480
481 # More indexes can still be added and a single field can still be updated
482 CREATE INDEX t1g ON t1 (g(767));
483 UPDATE t1 SET g=@e;
484 CREATE INDEX t1h ON t1 (h(767));
485 UPDATE t1 SET h=@e;
486 CREATE INDEX t1i ON t1 (i(767));
487 UPDATE t1 SET i=@e;
488 CREATE INDEX t1j ON t1 (j(767));
489 UPDATE t1 SET j=@e;
490 CREATE INDEX t1k ON t1 (k(767));
491 UPDATE t1 SET k=@e;
492 CREATE INDEX t1l ON t1 (l(767));
493 UPDATE t1 SET l=@e;
494 CREATE INDEX t1m ON t1 (m(767));
495 UPDATE t1 SET m=@e;
496 CREATE INDEX t1n ON t1 (n(767));
497 UPDATE t1 SET n=@e;
498 CREATE INDEX t1o ON t1 (o(767));
499 UPDATE t1 SET o=@e;
500 CREATE INDEX t1p ON t1 (p(767));
501 UPDATE t1 SET p=@e;
502 CREATE INDEX t1q ON t1 (q(767));
503 UPDATE t1 SET q=@e;
504 CREATE INDEX t1r ON t1 (r(767));
505 UPDATE t1 SET r=@e;
506 CREATE INDEX t1s ON t1 (s(767));
507 UPDATE t1 SET s=@e;
508
509 # Add one more index and we cannot update a column to its defined index length.
510 # This is a problem. It means that the DDL is allowed to create a table
511 # that CANNOT be updated. See bug#12953735.
512 CREATE INDEX t1t ON t1 (t(767));
513 --error ER_UNDO_RECORD_TOO_BIG
514 UPDATE t1 SET t=@e;
515
516 CREATE INDEX t1u ON t1 (u(767));
517 CREATE INDEX t1ut ON t1 (u(767), t(767));
518 CREATE INDEX t1st ON t1 (s(767), t(767));
519
520 SHOW CREATE TABLE t1;
521 DROP TABLE t1;
522
523 --echo # Bug #12429576 - Test an assertion failure on purge.
524 # This test is not in innodb_8k or innodb_4k since the bug is not about
525 # page size. It just tests the condition that caused the assertion.
526 CREATE TABLE t1_purge (
527 A int,
528 B blob, C blob, D blob, E blob,
529 F blob, G blob, H blob,
530 PRIMARY KEY (B(767), C(767), D(767), E(767), A),
531 INDEX (A)
532 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
533
534 INSERT INTO t1_purge VALUES (1,
535 REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
536 REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766));
537
538 CREATE TABLE t2_purge (
539 A int PRIMARY KEY,
540 B blob, C blob, D blob, E blob,
541 F blob, G blob, H blob, I blob,
542 J blob, K blob, L blob,
543 INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
544
545 INSERT INTO t2_purge VALUES (1,
546 REPEAT('b', 766), REPEAT('c', 766), REPEAT('d', 766), REPEAT('e', 766),
547 REPEAT('f', 766), REPEAT('g', 766), REPEAT('h', 766), REPEAT('i', 766),
548 REPEAT('j', 766), REPEAT('k', 766), REPEAT('l', 766));
549
550 CREATE TABLE t3_purge (
551 A int,
552 B varchar(800), C varchar(800), D varchar(800), E varchar(800),
553 F varchar(800), G varchar(800), H varchar(800),
554 PRIMARY KEY (B(767), C(767), D(767), E(767), A),
555 INDEX (A)
556 ) charset latin1 ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
557
558 INSERT INTO t3_purge SELECT * FROM t1_purge;
559
560 CREATE TABLE t4_purge (
561 A int PRIMARY KEY,
562 B varchar(800), C varchar(800), D varchar(800), E varchar(800),
563 F varchar(800), G varchar(800), H varchar(800), I varchar(800),
564 J varchar(800), K varchar(800), L varchar(800),
565 INDEX (B(767))) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
566
567 INSERT INTO t4_purge SELECT * FROM t2_purge;
568
569 # This would trigger the failure (Bug #12429576)
570 # if purge gets a chance to run before DROP TABLE t1_purge, ....
571 DELETE FROM t1_purge;
572 DELETE FROM t2_purge;
573 DELETE FROM t3_purge;
574 DELETE FROM t4_purge;
575 # We need to activate the purge thread.
576 # Instead of doing a --sleep 10 now, do it once at the end.
577
578 # Bug#12637786 - Assertion hit; ut_ad(index->is_clustered());
579 # A secondary index tuple is found to be too long to fit into a page.
580 # This test is not in innodb_8k or innodb_4k since the bug is not about
581 # page size. It just tests the condition that caused the assertion.
582 SET @r=REPEAT('a',500);
583 CREATE TABLE t12637786(a int,
584 v1 varchar(500), v2 varchar(500), v3 varchar(500),
585 v4 varchar(500), v5 varchar(500), v6 varchar(500),
586 v7 varchar(500), v8 varchar(500), v9 varchar(500),
587 v10 varchar(500), v11 varchar(500), v12 varchar(500),
588 v13 varchar(500), v14 varchar(500), v15 varchar(500),
589 v16 varchar(500), v17 varchar(500), v18 varchar(500)
590 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
591 CREATE INDEX idx1 ON t12637786(a,v1);
592 INSERT INTO t12637786 VALUES(9,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r,@r);
593 UPDATE t12637786 SET a=1000;
594 DELETE FROM t12637786;
595 # We need to activate the purge thread to make sure it does not assert and
596 # is able to clean up the old versions of secondary index entries.
597 # Instead of doing a --sleep 10 now for each test, do it once at the end.
598
599 --echo # Bug#12963823 - Test that the purge thread does not crash when
600 # the number of indexes has changed since the UNDO record was logged.
601 # This test is not in innodb_8k or innodb_4k since the bug is not about
602 # page size. It just tests the condition that caused the crash.
603 CREATE TABLE t12963823(a blob,b blob,c blob,d blob,e blob,f blob,g blob,h blob,
604 i blob,j blob,k blob,l blob,m blob,n blob,o blob,p blob)
605 ENGINE=innodb ROW_FORMAT=dynamic;
606 SET @r = REPEAT('a', 767);
607 INSERT INTO t12963823 VALUES (@r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r, @r,@r,@r,@r);
608 CREATE INDEX ndx_a ON t12963823 (a(500));
609 CREATE INDEX ndx_b ON t12963823 (b(500));
610 CREATE INDEX ndx_c ON t12963823 (c(500));
611 CREATE INDEX ndx_d ON t12963823 (d(500));
612 CREATE INDEX ndx_e ON t12963823 (e(500));
613 CREATE INDEX ndx_f ON t12963823 (f(500));
614 CREATE INDEX ndx_k ON t12963823 (k(500));
615 CREATE INDEX ndx_l ON t12963823 (l(500));
616
617 SET @r = REPEAT('b', 500);
618 UPDATE t12963823 set a=@r,b=@r,c=@r,d=@r;
619 UPDATE t12963823 set e=@r,f=@r,g=@r,h=@r;
620 UPDATE t12963823 set i=@r,j=@r,k=@r,l=@r;
621 UPDATE t12963823 set m=@r,n=@r,o=@r,p=@r;
622 ALTER TABLE t12963823 DROP INDEX ndx_a;
623 ALTER TABLE t12963823 DROP INDEX ndx_b;
624 CREATE INDEX ndx_g ON t12963823 (g(500));
625 CREATE INDEX ndx_h ON t12963823 (h(500));
626 CREATE INDEX ndx_i ON t12963823 (i(500));
627 CREATE INDEX ndx_j ON t12963823 (j(500));
628 CREATE INDEX ndx_m ON t12963823 (m(500));
629 CREATE INDEX ndx_n ON t12963823 (n(500));
630 CREATE INDEX ndx_o ON t12963823 (o(500));
631 CREATE INDEX ndx_p ON t12963823 (p(500));
632 SHOW CREATE TABLE t12963823;
633 # We need to activate the purge thread at this point to see if it crashes.
634 # Instead of doing a --sleep 10 now for each test, do it once at the end.
635
636 --echo # Bug#12547647 UPDATE LOGGING COULD EXCEED LOG PAGE SIZE
637 # InnoDB cannot know that this undo record would be too big for the undo
638 # page. Too much of text field is stored in the clustered record in this
639 # DYNAMIC row formatted record.
640 # This test is not in innodb_8k or innodb_4k since the bug is not about
641 # page size. It just tests the condition that caused the hang.
642
643 SET SESSION innodb_strict_mode = ON;
644 CREATE TABLE bug12547647(
645 a int NOT NULL, b blob NOT NULL, c text,
646 PRIMARY KEY (b(10), a), INDEX (c(767)), INDEX(b(767))
647 ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
648 INSERT INTO bug12547647 VALUES (5,REPEAT('khdfo5AlOq',1900),REPEAT('g',7751));
649 COMMIT;
650 # The following used to cause a hang while doing infinite undo log allocation.
651 --error ER_UNDO_RECORD_TOO_BIG
652 UPDATE bug12547647 SET c = REPEAT('b',16928);
653 SHOW WARNINGS;
654 DROP TABLE bug12547647;
655
656 # The following should fail in non-strict mode too.
657 # (The fix of Bug #50945 only affects REDUNDANT and COMPACT tables.)
658 SET SESSION innodb_strict_mode = off;
659 CREATE TABLE t1(
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=1 CHARSET=ASCII;
663 DROP TABLE t1;
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=2 CHARSET=ASCII;
668 DROP TABLE t1;
669 CREATE TABLE t1(
670 c text NOT NULL, d text NOT NULL,
671 PRIMARY KEY (c(767),d(767)))
672 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 CHARSET=ASCII;
673 drop table t1;
674 CREATE TABLE t1(c text, PRIMARY KEY (c(440)))
675 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
676 DROP TABLE t1;
677 CREATE TABLE t1(c text, PRIMARY KEY (c(438)))
678 ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 CHARSET=ASCII;
679 INSERT INTO t1 VALUES(REPEAT('A',512)),(REPEAT('B',512));
680 DROP TABLE t1;
681
682
683 --echo #
684 --echo # Bug#56862 Execution of a query that uses index merge returns a wrong result
685 --echo #
686
687 # Moved to here from innodb_mysql.test. Some PB3 systems sporadically
688 # had timeouts doing this with smaller page sizes.
689
690 CREATE TABLE t1 (
691 pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
692 a int,
693 b int,
694 INDEX idx(a))
695 ENGINE=INNODB;
696
697 INSERT INTO t1(a,b) VALUES
698 (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
699 (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
700 (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
701 (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
702 INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
703 INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
704 INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
705 INSERT INTO t1(a,b) SELECT a,b FROM t1;
706 INSERT INTO t1(a,b) SELECT a,b FROM t1;
707 INSERT INTO t1(a,b) SELECT a,b FROM t1;
708 INSERT INTO t1(a,b) SELECT a,b 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 VALUES (1000000, 0, 0);
714
715 set @optimizer_switch_saved=@@optimizer_switch;
716 SET SESSION optimizer_switch='derived_merge=off';
717 SET SESSION sort_buffer_size = 1024*36;
718
719 EXPLAIN
720 SELECT COUNT(*) FROM
721 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
722 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
723
724 SELECT COUNT(*) FROM
725 (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
726 WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
727
728 set @@optimizer_switch=@optimizer_switch_saved;
729 SET SESSION sort_buffer_size = DEFAULT;
730
731 DROP TABLE t1;
732
733
734 # The tests that uses these tables required the purge thread to run.
735 # Just in case it has not by now, provide a 10 second wait.
736 --sleep 10
737 DROP TABLE t1_purge, t2_purge, t3_purge, t4_purge;
738 DROP TABLE t12637786;
739 DROP TABLE t12963823;