Debian Codegraph @

mysql-test/suite/maria/maria3.test

raw
1 -- source include/have_maria.inc
2 --source include/test_db_charset_latin1.inc
3
4 select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA";
5
6 let $default_engine=`select @@global.default_storage_engine`;
7 let $default_checksum=`select @@global.aria_page_checksum`;
8 set global default_storage_engine=aria;
9 set session default_storage_engine=aria;
10 set global aria_page_checksum=0;
11 let $default_log_file_size=`select @@global.aria_log_file_size`;
12 set global aria_log_file_size=4294959104;
13
14 # Initialise
15 --disable_warnings
16 drop table if exists t1,t2;
17 --enable_warnings
18 SET SQL_WARNINGS=1;
19
20 # Test limits and errors of key_block_size
21
22 create table t1 (a int not null, key `a` (a) key_block_size=512);
23 show create table t1;
24 drop table t1;
25
26 create table t1 (a varchar(4096), key `a` (a) key_block_size=1000000000000000000);
27 show create table t1;
28 drop table t1;
29
30 create table t1 (a int not null, key `a` (a) key_block_size=1025);
31 show create table t1;
32 drop table t1;
33
34 --error 1064
35 create table t1 (a int not null, key key_block_size=1024 (a));
36 --error 1064
37 create table t1 (a int not null, key `a` key_block_size=1024 (a));
38
39 #
40 # Test of changing MI_KEY_BLOCK_LENGTH
41 #
42
43 CREATE TABLE t1 (
44 c1 INT,
45 c2 VARCHAR(300),
46 KEY (c1) KEY_BLOCK_SIZE 1024,
47 KEY (c2) KEY_BLOCK_SIZE 8192
48 );
49 INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))),
50 (11, REPEAT('b', CEIL(RAND() * 300))),
51 (12, REPEAT('c', CEIL(RAND() * 300))),
52 (13, REPEAT('d', CEIL(RAND() * 300))),
53 (14, REPEAT('e', CEIL(RAND() * 300))),
54 (15, REPEAT('f', CEIL(RAND() * 300))),
55 (16, REPEAT('g', CEIL(RAND() * 300))),
56 (17, REPEAT('h', CEIL(RAND() * 300))),
57 (18, REPEAT('i', CEIL(RAND() * 300))),
58 (19, REPEAT('j', CEIL(RAND() * 300))),
59 (20, REPEAT('k', CEIL(RAND() * 300))),
60 (21, REPEAT('l', CEIL(RAND() * 300))),
61 (22, REPEAT('m', CEIL(RAND() * 300))),
62 (23, REPEAT('n', CEIL(RAND() * 300))),
63 (24, REPEAT('o', CEIL(RAND() * 300))),
64 (25, REPEAT('p', CEIL(RAND() * 300))),
65 (26, REPEAT('q', CEIL(RAND() * 300))),
66 (27, REPEAT('r', CEIL(RAND() * 300))),
67 (28, REPEAT('s', CEIL(RAND() * 300))),
68 (29, REPEAT('t', CEIL(RAND() * 300))),
69 (30, REPEAT('u', CEIL(RAND() * 300))),
70 (31, REPEAT('v', CEIL(RAND() * 300))),
71 (32, REPEAT('w', CEIL(RAND() * 300))),
72 (33, REPEAT('x', CEIL(RAND() * 300))),
73 (34, REPEAT('y', CEIL(RAND() * 300))),
74 (35, REPEAT('z', CEIL(RAND() * 300)));
75 INSERT INTO t1 SELECT * FROM t1;
76 INSERT INTO t1 SELECT * FROM t1;
77 CHECK TABLE t1;
78 REPAIR TABLE t1;
79 DELETE FROM t1 WHERE c1 >= 10;
80 CHECK TABLE t1;
81 DROP TABLE t1;
82
83 #
84 # Test that TRANSACTIONAL is preserved
85 #
86
87 create table t1 (a int) transactional=0;
88 show create table t1;
89 drop table t1;
90 create table t1 (a int) row_format=dynamic transactional=0;
91 show create table t1;
92 drop table t1;
93 create table t1 (a int) row_format=dynamic transactional=1;
94 show create table t1;
95 alter table t1 row_format=PAGE;
96 show create table t1;
97 alter table t1 row_format=DYNAMIC;
98 show create table t1;
99 alter table t1 transactional=0;
100 show create table t1;
101 alter table t1 row_format=DYNAMIC;
102 show create table t1;
103 drop table t1;
104 create table t1 (a int) row_format=PAGE;
105 show create table t1;
106 drop table t1;
107 create table t1 (a int) row_format=PAGE TRANSACTIONAL=DEFAULT;
108 show create table t1;
109 alter table t1 row_format=DYNAMIC;
110 show create table t1;
111 drop table t1;
112
113 #
114 # MySQL Bug#39200: optimize table does not recognize ROW_FORMAT=COMPRESSED
115 #
116
117 create table t1 (a int) transactional=0 row_format=FIXED;
118 show create table t1;
119 alter table t1 transactional=1;
120 show create table t1;
121 alter table t1 transactional=0;
122 show create table t1;
123 drop table t1;
124
125 #
126 # MySQL Bug#39200: optimize table does not recognize ROW_FORMAT=COMPRESSED
127 #
128
129 create table t1 (a int) transactional=0 row_format=FIXED;
130 show create table t1;
131 alter table t1 transactional=1;
132 show create table t1;
133 alter table t1 transactional=0;
134 show create table t1;
135 drop table t1;
136
137 # CHECK TABLE was reporting
138 # "Size of datafile is: 0 Should be: 16384"
139 #
140
141 create table `t1` (
142 t1_name varchar(255) default null,
143 t1_id int(10) unsigned not null auto_increment,
144 key (t1_name),
145 primary key (t1_id)
146 ) engine=aria auto_increment = 1000 default charset=latin1;
147 lock tables t1 write;
148 INSERT INTO `t1` VALUES ('bla',1000),('bla',1001),('bla',1002);
149 check table t1;
150 unlock tables;
151
152 #
153 # Check that an empty table uses fast recreate of index when we fill it
154 # with insert ... select.
155
156 create table t2 like t1;
157 insert into t2 select * from t1;
158
159 # This should say that the table is already up to date
160 analyze table t2;
161 delete from t2;
162 insert into t2 select * from t1;
163 analyze table t2;
164
165 drop table t1,t2;
166
167 #
168 # Test when expanding a row so that it doesn't fit into the same page
169 #
170
171 create table t1 (a bigint auto_increment, primary key(a), b char(255), c varchar(20000));
172
173 let $1=1000;
174 --disable_query_log
175 --disable_warnings
176 lock tables t1 write;
177 while ($1)
178 {
179 insert into t1 () values();
180 dec $1;
181 }
182 unlock tables;
183 --enable_query_log
184 update t1 set b=repeat('a',100) where a between 1 and 100;
185 check table t1;
186 update t1 set c=repeat('a',8192*2) where a between 200 and 202;
187 check table t1;
188 drop table t1;
189
190 #
191 # Test where we shrink varchar
192 #
193
194 CREATE TABLE t1 (a int, b int, v varchar(60000)) checksum=1 engine=aria;
195 insert into t1 values (1,1,"aaa"),(1,2,null);
196 checksum table t1;
197 insert into t1 values (1,3,repeat('c',30000)),(4,4,repeat('a',30000));
198 update t1 set v="row5" where b=4;
199 delete from t1 where b=3;
200 select a, b, length(v) from t1;
201 drop table t1;
202
203 #
204 # Test tail pages for blobs
205 #
206
207 CREATE TABLE t1 (
208 auto int(5) unsigned NOT NULL auto_increment,
209 string char(10) default "hello",
210 tiny tinyint(4) DEFAULT '0' NOT NULL ,
211 short smallint(6) DEFAULT '1' NOT NULL ,
212 medium mediumint(8) DEFAULT '0' NOT NULL,
213 long_int int(11) DEFAULT '0' NOT NULL,
214 longlong bigint(13) DEFAULT '0' NOT NULL,
215 real_float float(13,1) DEFAULT 0.0 NOT NULL,
216 real_double double(16,4),
217 utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
218 ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
219 umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
220 ulong int(11) unsigned DEFAULT '0' NOT NULL,
221 ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
222 time_stamp timestamp,
223 date_field date,
224 time_field time,
225 date_time datetime,
226 blob_col blob,
227 tinyblob_col tinyblob,
228 mediumblob_col mediumblob not null default '',
229 longblob_col longblob not null default '',
230 options enum('one','two','tree') not null ,
231 flags set('one','two','tree') not null default '',
232 PRIMARY KEY (auto),
233 KEY (utiny),
234 KEY (tiny),
235 KEY (short),
236 KEY any_name (medium),
237 KEY (longlong),
238 KEY (real_float),
239 KEY (ushort),
240 KEY (umedium),
241 KEY (ulong),
242 KEY (ulonglong,ulong),
243 KEY (options,flags)
244 ) engine=aria;
245 insert into t1 values (10,1,1,1,1,1,1,1,1,1,1,1,1,1,NULL,0,0,0,1,1,1,1,'one','one');
246 create table t2 (primary key (auto)) engine=aria row_format=page select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
247 check table t1,t2;
248 select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
249 drop table t2;
250 create table t2 (primary key (auto)) engine=aria row_format=dynamic select auto+1 as auto,1 as t1, 'a' as t2, repeat('a',256) as t3, binary repeat('b',256) as t4, repeat('a',4096) as t5, binary repeat('b',4096) as t6, '' as t7, binary '' as t8 from t1;
251 check table t2;
252 drop table t1,t2;
253
254 # Test UPDATE with small BLOB which fits on head page
255
256 CREATE TABLE t1 (seq int, s1 int, s2 blob);
257 insert into t1 values (1, 1, MD5(1));
258 update t1 set s1=2 where seq=1;
259 check table t1 extended;
260 drop table t1;
261
262 --replace_column 2 #
263 select lower(variable_name) as Variable_name, Variable_value as Value from information_schema.session_variables where variable_name like "aria%" order by 1;
264 --replace_column 2 #
265 show status like 'aria%';
266
267 #
268 # Test creating table with no field data and index on zero length columns
269 #
270
271 create table t1 (b char(0));
272 insert into t1 values(NULL),("");
273 select length(b) from t1;
274 alter table t1 add column c char(0), add key (c);
275 insert into t1 values("",""),("",NULL);
276 select length(b),length(c) from t1;
277 select length(b),length(c) from t1 where c is null;
278 select length(b),length(c) from t1 where c is not null;
279 select length(b),length(c) from t1 order by c;
280 --error 1167
281 alter table t1 add column d char(0) not null, add key (d);
282 drop table t1;
283
284 CREATE TABLE t1 (a bit(3));
285 insert into t1 values (NULL),(0),(1),(2),(3),(4),(5),(6),(7);
286 select hex(a) from t1;
287 drop table t1;
288 create table t1(a bit not null);
289 insert into t1 values(0),(1);
290 select a+0 from t1;
291 drop table t1;
292
293 #
294 # Test of min_key_length
295 #
296
297 CREATE TABLE t1 (col1 int, s1 char(16) DEFAULT NULL, s2 char(16) DEFAULT NULL, KEY (s1,s2));
298 insert into t1 (col1) values(0);
299 drop table t1;
300
301 #
302 # Show that page_checksum is remembered
303 #
304 set global aria_page_checksum=1;
305 create table t1 (a int);
306 show create table t1;
307 set global aria_page_checksum=0;
308 drop table t1;
309
310 #
311 # Test warning on log file size truncates
312 #
313
314 --enable_warnings
315 set global aria_log_file_size=4294967296;
316
317 #
318 # Test delete of all rows in autocommit and not autocommit
319 #
320
321 create table t1 (a int not null);
322 lock tables t1 write;
323 insert into t1 values (1),(2);
324 delete from t1;
325 unlock tables;
326 select * from t1;
327 insert into t1 values (1),(2);
328 delete from t1;
329 select * from t1;
330 drop table t1;
331
332 # Test for bug "ha_enable_transaction(on) not called by CREATE TABLE"
333 # (originally from type_ranges.test)
334
335 create table t1 (c int);
336 insert into t1 values(1),(2);
337 create table t2 select * from t1;
338 --error 1060
339 create table t3 select * from t1, t2; # Should give an error
340 create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
341 drop table t1, t2, t3;
342
343 # Test for bug "aria_repair() (OPTIMIZE) leaves wrong
344 # data_file_length" (originally from type_datetime.test)
345
346 create table t1 (t datetime) engine=aria;
347 insert into t1 values (101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030100000000),(20030000000000);
348 select * from t1;
349 optimize table t1;
350 check table t1;
351 delete from t1 where t > 0;
352 optimize table t1;
353 check table t1;
354 drop table t1;
355
356 #
357 # Test auto-increment
358 #
359
360 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
361 CREATE TABLE t1 (id int(11) PRIMARY KEY auto_increment,f1 varchar(10) NOT NULL UNIQUE);
362 INSERT IGNORE INTO t1 (f1) VALUES ("test1");
363 INSERT IGNORE INTO t1 (f1) VALUES ("test1");
364 INSERT IGNORE INTO t1 (f1) VALUES ("test2");
365 SELECT * FROM t1;
366 drop table t1;
367 SET SQL_MODE = '';
368
369 create table t1 (n int not null primary key auto_increment, c char(1), unique(c));
370 insert into t1 values(100, "a");
371 insert into t1 values(300, "b");
372 --error 1062
373 insert into t1 values(50, "a");
374 insert into t1 values(null, "c");
375 select * from t1;
376 --error 1062
377 update t1 set n=400,c='a' where n=301;
378 insert into t1 values(null, "d");
379 select * from t1;
380 drop table t1;
381
382 create table t1 (n int not null primary key auto_increment, c char(1), unique(c)) transactional=0 row_format=dynamic;
383 insert into t1 values(100, "a");
384 insert into t1 values(300, "b");
385 --error 1062
386 insert into t1 values(50, "a");
387 insert into t1 values(null, "c");
388 select * from t1;
389 --error 1062
390 update t1 set n=400,c='a' where n=301;
391 insert into t1 values(null, "d");
392 select * from t1;
393 drop table t1;
394
395 #
396 # Test warnings with transactional=1 with MyISAM
397 #
398
399 create table t1 (n int not null, c char(1)) engine=aria;
400 alter table t1 engine=myisam;
401 alter table t1 engine=aria;
402 show create table t1;
403 drop table t1;
404 create table t1 (n int not null, c char(1)) engine=aria transactional=1;
405 alter table t1 engine=myisam;
406 alter table t1 engine=aria;
407 show create table t1;
408 drop table t1;
409 create table t1 (n int not null, c char(1)) engine=myisam transactional=1;
410 alter table t1 engine=aria;
411 show create table t1;
412 drop table t1;
413
414 #
415 # Some tests that have failed with transactional=0
416 #
417
418 # Testing buik insert
419 create table t1 (a int, key(a)) transactional=0;
420 insert into t1 values (0),(1),(2),(3),(4);
421 insert into t1 select NULL from t1;
422 check table t1;
423 drop table t1;
424
425 #
426 # Some tests with temporary tables
427 #
428
429 create temporary table t1 (a int, key(a)) transactional=1;
430 create temporary table t2 (a int, key(a)) transactional=1;
431 insert into t1 values (0),(1),(2),(3),(4);
432 insert into t2 select * from t1;
433 insert into t1 select NULL from t2;
434 select count(*) from t1;
435 select count(*) from t1 where a >= 4;
436 drop table t1, t2;
437
438 create temporary table t1 (a int, key(a)) transactional=0 row_format=page;
439 create temporary table t2 (a int, key(a)) transactional=0 row_format=page;
440 insert into t1 values (0),(1),(2),(3),(4);
441 insert into t2 select * from t1;
442 insert into t1 select NULL from t2;
443 select count(*) from t1;
444 select count(*) from t1 where a >= 4;
445 drop table t1, t2;
446
447 create temporary table t1 (a int, key(a)) transactional=0 row_format=fixed;
448 create temporary table t2 (a int, key(a)) transactional=0 row_format=dynamic;
449 insert into t1 values (0),(1),(2),(3),(4);
450 insert into t2 select * from t1;
451 insert into t1 select NULL from t2;
452 select count(*) from t1;
453 select count(*) from t1 where a >= 4;
454 drop table t1, t2;
455
456 #
457 # Test problems with small rows and row_type=page
458 # Bug 35048 "aria table corruption reported when transactional=0"
459 #
460
461 create table t1 (i int auto_increment not null primary key) transactional=0;
462
463 let $i=510;
464 --disable_query_log
465 while ($i)
466 {
467 dec $i;
468 insert into t1 values (null);
469 }
470 --enable_query_log
471 check table t1 extended;
472 delete from t1 where i = 10;
473 check table t1 extended;
474 drop table t1;
475
476 create table t1 (i int auto_increment not null primary key);
477
478 let $i=510;
479 --disable_query_log
480 while ($i)
481 {
482 dec $i;
483 insert into t1 values (null);
484 }
485 --enable_query_log
486 check table t1 extended;
487 delete from t1 where i = 10;
488 check table t1 extended;
489 drop table t1;
490
491 #
492 # BUG#29445 - match ... against () never returns
493 #
494 CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)) transactional=0;
495 INSERT INTO t1 VALUES('Offside'),('City Of God');
496 SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
497 SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of)*' IN BOOLEAN MODE);
498 DROP TABLE t1;
499
500 #
501 # BUG#36104 - INFORMATION_SCHEMA.TABLES shows TRANSACTIONAL=1 twice in
502 # CREATE_OPTIONS
503 #
504 create table t1(a int) engine=aria transactional=1;
505 select CREATE_OPTIONS from information_schema.TABLES where
506 TABLE_SCHEMA='test' and TABLE_NAME='t1';
507 drop table t1;
508
509 #
510 # BUG#39697 - Aria: hang when failing to insert due to UNIQUE
511 #
512 create table t1 (a int, unique(a)) engine=aria transactional=1;
513 insert into t1 values(1);
514 --error 1062
515 insert into t1 values(2),(2);
516 create table t2 (a int, unique(a)) engine=aria transactional=0 row_format=dynamic;
517 insert into t2 values(1);
518 --error 1062
519 insert into t2 values(2),(2);
520 connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
521 connection root;
522 insert into t1 values(3);
523 insert into t2 values(3);
524 connection default;
525 drop table t1, t2;
526
527 #
528 # BUG#909635 - MariaDB crashes on a select with long varchar and blob fields
529 #
530
531 CREATE TABLE t1 (
532 a INT PRIMARY KEY,
533 b CHAR(255),
534 c VARCHAR(2048),
535 d VARCHAR(18990),
536 e CHAR(128),
537 f CHAR(192)
538 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
539
540 INSERT INTO t1 VALUES
541 (1,'A','B','C','','D'),
542 (2,'Abcdefghi','E','F','','G');
543
544 CREATE TABLE t2 (
545 g INT PRIMARY KEY,
546 h CHAR(32),
547 i CHAR(255),
548 j TEXT
549 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
550
551 INSERT INTO t2 VALUES (1,'M','','H'),
552 (2,'N','','H');
553
554 SELECT * FROM t1, t2 WHERE a = g ORDER BY b;
555 drop table t1,t2;
556
557 --echo # End of 5.1 tests
558
559 create table t1 (a int) engine=aria;
560 lock table t1 write;
561 drop table t1;
562
563 --disable_result_log
564 --disable_query_log
565 eval set global default_storage_engine=$default_engine,
566 aria_page_checksum=$default_checksum,
567 aria_log_file_size=$default_log_file_size;
568 --enable_result_log
569 --enable_query_log
570
571 --source include/test_db_charset_restore.inc