Debian Codegraph @

mysql-test/suite/maria/maria3.result

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 select * from INFORMATION_SCHEMA.ENGINES where ENGINE="ARIA";
3 ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
4 Aria YES Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables NO NO NO
5 set global default_storage_engine=aria;
6 set session default_storage_engine=aria;
7 set global aria_page_checksum=0;
8 set global aria_log_file_size=4294959104;
9 drop table if exists t1,t2;
10 SET SQL_WARNINGS=1;
11 create table t1 (a int not null, key `a` (a) key_block_size=512);
12 show create table t1;
13 Table Create Table
14 t1 CREATE TABLE `t1` (
15 `a` int(11) NOT NULL,
16 KEY `a` (`a`) KEY_BLOCK_SIZE=8192
17 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0
18 drop table t1;
19 create table t1 (a varchar(4096), key `a` (a) key_block_size=1000000000000000000);
20 Warnings:
21 Note 1071 Specified key was too long; max key length is 2300 bytes
22 show create table t1;
23 Table Create Table
24 t1 CREATE TABLE `t1` (
25 `a` varchar(4096) DEFAULT NULL,
26 KEY `a` (`a`(2300)) KEY_BLOCK_SIZE=8192
27 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0
28 drop table t1;
29 create table t1 (a int not null, key `a` (a) key_block_size=1025);
30 show create table t1;
31 Table Create Table
32 t1 CREATE TABLE `t1` (
33 `a` int(11) NOT NULL,
34 KEY `a` (`a`) KEY_BLOCK_SIZE=8192
35 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0
36 drop table t1;
37 create table t1 (a int not null, key key_block_size=1024 (a));
38 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=1024 (a))' at line 1
39 create table t1 (a int not null, key `a` key_block_size=1024 (a));
40 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key_block_size=1024 (a))' at line 1
41 CREATE TABLE t1 (
42 c1 INT,
43 c2 VARCHAR(300),
44 KEY (c1) KEY_BLOCK_SIZE 1024,
45 KEY (c2) KEY_BLOCK_SIZE 8192
46 );
47 INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))),
48 (11, REPEAT('b', CEIL(RAND() * 300))),
49 (12, REPEAT('c', CEIL(RAND() * 300))),
50 (13, REPEAT('d', CEIL(RAND() * 300))),
51 (14, REPEAT('e', CEIL(RAND() * 300))),
52 (15, REPEAT('f', CEIL(RAND() * 300))),
53 (16, REPEAT('g', CEIL(RAND() * 300))),
54 (17, REPEAT('h', CEIL(RAND() * 300))),
55 (18, REPEAT('i', CEIL(RAND() * 300))),
56 (19, REPEAT('j', CEIL(RAND() * 300))),
57 (20, REPEAT('k', CEIL(RAND() * 300))),
58 (21, REPEAT('l', CEIL(RAND() * 300))),
59 (22, REPEAT('m', CEIL(RAND() * 300))),
60 (23, REPEAT('n', CEIL(RAND() * 300))),
61 (24, REPEAT('o', CEIL(RAND() * 300))),
62 (25, REPEAT('p', CEIL(RAND() * 300))),
63 (26, REPEAT('q', CEIL(RAND() * 300))),
64 (27, REPEAT('r', CEIL(RAND() * 300))),
65 (28, REPEAT('s', CEIL(RAND() * 300))),
66 (29, REPEAT('t', CEIL(RAND() * 300))),
67 (30, REPEAT('u', CEIL(RAND() * 300))),
68 (31, REPEAT('v', CEIL(RAND() * 300))),
69 (32, REPEAT('w', CEIL(RAND() * 300))),
70 (33, REPEAT('x', CEIL(RAND() * 300))),
71 (34, REPEAT('y', CEIL(RAND() * 300))),
72 (35, REPEAT('z', CEIL(RAND() * 300)));
73 INSERT INTO t1 SELECT * FROM t1;
74 INSERT INTO t1 SELECT * FROM t1;
75 CHECK TABLE t1;
76 Table Op Msg_type Msg_text
77 test.t1 check status OK
78 REPAIR TABLE t1;
79 Table Op Msg_type Msg_text
80 test.t1 repair status OK
81 DELETE FROM t1 WHERE c1 >= 10;
82 CHECK TABLE t1;
83 Table Op Msg_type Msg_text
84 test.t1 check status OK
85 DROP TABLE t1;
86 create table t1 (a int) transactional=0;
87 show create table t1;
88 Table Create Table
89 t1 CREATE TABLE `t1` (
90 `a` int(11) DEFAULT NULL
91 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 TRANSACTIONAL=0
92 drop table t1;
93 create table t1 (a int) row_format=dynamic transactional=0;
94 show create table t1;
95 Table Create Table
96 t1 CREATE TABLE `t1` (
97 `a` int(11) DEFAULT NULL
98 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=DYNAMIC TRANSACTIONAL=0
99 drop table t1;
100 create table t1 (a int) row_format=dynamic transactional=1;
101 Warnings:
102 Note 1478 Row format set to PAGE because of TRANSACTIONAL=1 option
103 show create table t1;
104 Table Create Table
105 t1 CREATE TABLE `t1` (
106 `a` int(11) DEFAULT NULL
107 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
108 alter table t1 row_format=PAGE;
109 show create table t1;
110 Table Create Table
111 t1 CREATE TABLE `t1` (
112 `a` int(11) DEFAULT NULL
113 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
114 alter table t1 row_format=DYNAMIC;
115 Warnings:
116 Note 1478 Row format set to PAGE because of TRANSACTIONAL=1 option
117 show create table t1;
118 Table Create Table
119 t1 CREATE TABLE `t1` (
120 `a` int(11) DEFAULT NULL
121 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
122 alter table t1 transactional=0;
123 show create table t1;
124 Table Create Table
125 t1 CREATE TABLE `t1` (
126 `a` int(11) DEFAULT NULL
127 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=DYNAMIC TRANSACTIONAL=0
128 alter table t1 row_format=DYNAMIC;
129 show create table t1;
130 Table Create Table
131 t1 CREATE TABLE `t1` (
132 `a` int(11) DEFAULT NULL
133 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=DYNAMIC TRANSACTIONAL=0
134 drop table t1;
135 create table t1 (a int) row_format=PAGE;
136 show create table t1;
137 Table Create Table
138 t1 CREATE TABLE `t1` (
139 `a` int(11) DEFAULT NULL
140 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=PAGE
141 drop table t1;
142 create table t1 (a int) row_format=PAGE TRANSACTIONAL=DEFAULT;
143 show create table t1;
144 Table Create Table
145 t1 CREATE TABLE `t1` (
146 `a` int(11) DEFAULT NULL
147 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=PAGE
148 alter table t1 row_format=DYNAMIC;
149 show create table t1;
150 Table Create Table
151 t1 CREATE TABLE `t1` (
152 `a` int(11) DEFAULT NULL
153 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=DYNAMIC
154 drop table t1;
155 create table t1 (a int) transactional=0 row_format=FIXED;
156 show create table t1;
157 Table Create Table
158 t1 CREATE TABLE `t1` (
159 `a` int(11) DEFAULT NULL
160 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=FIXED TRANSACTIONAL=0
161 alter table t1 transactional=1;
162 Warnings:
163 Note 1478 Row format set to PAGE because of TRANSACTIONAL=1 option
164 show create table t1;
165 Table Create Table
166 t1 CREATE TABLE `t1` (
167 `a` int(11) DEFAULT NULL
168 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
169 alter table t1 transactional=0;
170 show create table t1;
171 Table Create Table
172 t1 CREATE TABLE `t1` (
173 `a` int(11) DEFAULT NULL
174 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=FIXED TRANSACTIONAL=0
175 drop table t1;
176 create table t1 (a int) transactional=0 row_format=FIXED;
177 show create table t1;
178 Table Create Table
179 t1 CREATE TABLE `t1` (
180 `a` int(11) DEFAULT NULL
181 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=FIXED TRANSACTIONAL=0
182 alter table t1 transactional=1;
183 Warnings:
184 Note 1478 Row format set to PAGE because of TRANSACTIONAL=1 option
185 show create table t1;
186 Table Create Table
187 t1 CREATE TABLE `t1` (
188 `a` int(11) DEFAULT NULL
189 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
190 alter table t1 transactional=0;
191 show create table t1;
192 Table Create Table
193 t1 CREATE TABLE `t1` (
194 `a` int(11) DEFAULT NULL
195 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 ROW_FORMAT=FIXED TRANSACTIONAL=0
196 drop table t1;
197 create table `t1` (
198 t1_name varchar(255) default null,
199 t1_id int(10) unsigned not null auto_increment,
200 key (t1_name),
201 primary key (t1_id)
202 ) engine=aria auto_increment = 1000 default charset=latin1;
203 lock tables t1 write;
204 INSERT INTO `t1` VALUES ('bla',1000),('bla',1001),('bla',1002);
205 check table t1;
206 Table Op Msg_type Msg_text
207 test.t1 check status OK
208 unlock tables;
209 create table t2 like t1;
210 insert into t2 select * from t1;
211 analyze table t2;
212 Table Op Msg_type Msg_text
213 test.t2 analyze status Engine-independent statistics collected
214 test.t2 analyze status Table is already up to date
215 delete from t2;
216 insert into t2 select * from t1;
217 analyze table t2;
218 Table Op Msg_type Msg_text
219 test.t2 analyze status Engine-independent statistics collected
220 test.t2 analyze status Table is already up to date
221 drop table t1,t2;
222 create table t1 (a bigint auto_increment, primary key(a), b char(255), c varchar(20000));
223 update t1 set b=repeat('a',100) where a between 1 and 100;
224 check table t1;
225 Table Op Msg_type Msg_text
226 test.t1 check status OK
227 update t1 set c=repeat('a',8192*2) where a between 200 and 202;
228 check table t1;
229 Table Op Msg_type Msg_text
230 test.t1 check status OK
231 drop table t1;
232 CREATE TABLE t1 (a int, b int, v varchar(60000)) checksum=1 engine=aria;
233 insert into t1 values (1,1,"aaa"),(1,2,null);
234 checksum table t1;
235 Table Checksum
236 test.t1 1112804611
237 insert into t1 values (1,3,repeat('c',30000)),(4,4,repeat('a',30000));
238 update t1 set v="row5" where b=4;
239 delete from t1 where b=3;
240 select a, b, length(v) from t1;
241 a b length(v)
242 1 1 3
243 1 2 NULL
244 4 4 4
245 drop table t1;
246 CREATE TABLE t1 (
247 auto int(5) unsigned NOT NULL auto_increment,
248 string char(10) default "hello",
249 tiny tinyint(4) DEFAULT '0' NOT NULL ,
250 short smallint(6) DEFAULT '1' NOT NULL ,
251 medium mediumint(8) DEFAULT '0' NOT NULL,
252 long_int int(11) DEFAULT '0' NOT NULL,
253 longlong bigint(13) DEFAULT '0' NOT NULL,
254 real_float float(13,1) DEFAULT 0.0 NOT NULL,
255 real_double double(16,4),
256 utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
257 ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
258 umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
259 ulong int(11) unsigned DEFAULT '0' NOT NULL,
260 ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
261 time_stamp timestamp,
262 date_field date,
263 time_field time,
264 date_time datetime,
265 blob_col blob,
266 tinyblob_col tinyblob,
267 mediumblob_col mediumblob not null default '',
268 longblob_col longblob not null default '',
269 options enum('one','two','tree') not null ,
270 flags set('one','two','tree') not null default '',
271 PRIMARY KEY (auto),
272 KEY (utiny),
273 KEY (tiny),
274 KEY (short),
275 KEY any_name (medium),
276 KEY (longlong),
277 KEY (real_float),
278 KEY (ushort),
279 KEY (umedium),
280 KEY (ulong),
281 KEY (ulonglong,ulong),
282 KEY (options,flags)
283 ) engine=aria;
284 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');
285 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;
286 check table t1,t2;
287 Table Op Msg_type Msg_text
288 test.t1 check status OK
289 test.t2 check status OK
290 select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
291 t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8
292 1 a 256 256 4096 4096
293 drop table t2;
294 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;
295 check table t2;
296 Table Op Msg_type Msg_text
297 test.t2 check status OK
298 drop table t1,t2;
299 CREATE TABLE t1 (seq int, s1 int, s2 blob);
300 insert into t1 values (1, 1, MD5(1));
301 update t1 set s1=2 where seq=1;
302 check table t1 extended;
303 Table Op Msg_type Msg_text
304 test.t1 check status OK
305 drop table t1;
306 select lower(variable_name) as Variable_name, Variable_value as Value from information_schema.session_variables where variable_name like "aria%" order by 1;
307 Variable_name Value
308 aria_block_size #
309 aria_checkpoint_interval #
310 aria_checkpoint_log_activity #
311 aria_encrypt_tables #
312 aria_force_start_after_recovery_failures #
313 aria_group_commit #
314 aria_group_commit_interval #
315 aria_log_dir_path #
316 aria_log_file_size #
317 aria_log_purge_type #
318 aria_max_sort_file_size #
319 aria_pagecache_age_threshold #
320 aria_pagecache_buffer_size #
321 aria_pagecache_division_limit #
322 aria_pagecache_file_hash_size #
323 aria_page_checksum #
324 aria_recover_options #
325 aria_repair_threads #
326 aria_sort_buffer_size #
327 aria_stats_method #
328 aria_sync_log_dir #
329 aria_used_for_temp_tables #
330 show status like 'aria%';
331 Variable_name Value
332 Aria_pagecache_blocks_not_flushed #
333 Aria_pagecache_blocks_unused #
334 Aria_pagecache_blocks_used #
335 Aria_pagecache_read_requests #
336 Aria_pagecache_reads #
337 Aria_pagecache_write_requests #
338 Aria_pagecache_writes #
339 Aria_transaction_log_syncs #
340 create table t1 (b char(0));
341 insert into t1 values(NULL),("");
342 select length(b) from t1;
343 length(b)
344 NULL
345 0
346 alter table t1 add column c char(0), add key (c);
347 insert into t1 values("",""),("",NULL);
348 select length(b),length(c) from t1;
349 length(b) length(c)
350 NULL NULL
351 0 NULL
352 0 0
353 0 NULL
354 select length(b),length(c) from t1 where c is null;
355 length(b) length(c)
356 NULL NULL
357 0 NULL
358 0 NULL
359 select length(b),length(c) from t1 where c is not null;
360 length(b) length(c)
361 0 0
362 select length(b),length(c) from t1 order by c;
363 length(b) length(c)
364 NULL NULL
365 0 NULL
366 0 NULL
367 0 0
368 alter table t1 add column d char(0) not null, add key (d);
369 ERROR 42000: The storage engine Aria can't index column `d`
370 drop table t1;
371 CREATE TABLE t1 (a bit(3));
372 insert into t1 values (NULL),(0),(1),(2),(3),(4),(5),(6),(7);
373 select hex(a) from t1;
374 hex(a)
375 NULL
376 0
377 1
378 2
379 3
380 4
381 5
382 6
383 7
384 drop table t1;
385 create table t1(a bit not null);
386 insert into t1 values(0),(1);
387 select a+0 from t1;
388 a+0
389 0
390 1
391 drop table t1;
392 CREATE TABLE t1 (col1 int, s1 char(16) DEFAULT NULL, s2 char(16) DEFAULT NULL, KEY (s1,s2));
393 insert into t1 (col1) values(0);
394 drop table t1;
395 set global aria_page_checksum=1;
396 create table t1 (a int);
397 show create table t1;
398 Table Create Table
399 t1 CREATE TABLE `t1` (
400 `a` int(11) DEFAULT NULL
401 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1
402 set global aria_page_checksum=0;
403 drop table t1;
404 set global aria_log_file_size=4294967296;
405 Warnings:
406 Warning 1292 Truncated incorrect aria_log_file_size value: '4294967296'
407 create table t1 (a int not null);
408 lock tables t1 write;
409 insert into t1 values (1),(2);
410 delete from t1;
411 unlock tables;
412 select * from t1;
413 a
414 insert into t1 values (1),(2);
415 delete from t1;
416 select * from t1;
417 a
418 drop table t1;
419 create table t1 (c int);
420 insert into t1 values(1),(2);
421 create table t2 select * from t1;
422 create table t3 select * from t1, t2;
423 ERROR 42S21: Duplicate column name 'c'
424 create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
425 drop table t1, t2, t3;
426 create table t1 (t datetime) engine=aria;
427 insert into t1 values (101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030100000000),(20030000000000);
428 select * from t1;
429 t
430 2000-01-01 00:00:00
431 2069-12-31 00:00:00
432 1970-01-01 00:00:00
433 1999-12-31 00:00:00
434 1000-01-01 00:00:00
435 9999-12-31 00:00:00
436 2000-01-01 00:00:00
437 2069-12-31 00:00:00
438 1970-01-01 00:00:00
439 1999-12-31 23:59:59
440 1000-01-01 00:00:00
441 9999-12-31 23:59:59
442 2003-01-00 00:00:00
443 2003-00-00 00:00:00
444 optimize table t1;
445 Table Op Msg_type Msg_text
446 test.t1 optimize status OK
447 check table t1;
448 Table Op Msg_type Msg_text
449 test.t1 check status OK
450 delete from t1 where t > 0;
451 optimize table t1;
452 Table Op Msg_type Msg_text
453 test.t1 optimize status OK
454 check table t1;
455 Table Op Msg_type Msg_text
456 test.t1 check status OK
457 drop table t1;
458 SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
459 CREATE TABLE t1 (id int(11) PRIMARY KEY auto_increment,f1 varchar(10) NOT NULL UNIQUE);
460 INSERT IGNORE INTO t1 (f1) VALUES ("test1");
461 INSERT IGNORE INTO t1 (f1) VALUES ("test1");
462 Warnings:
463 Warning 1062 Duplicate entry 'test1' for key 'f1'
464 INSERT IGNORE INTO t1 (f1) VALUES ("test2");
465 SELECT * FROM t1;
466 id f1
467 1 test1
468 2 test2
469 drop table t1;
470 SET SQL_MODE = '';
471 create table t1 (n int not null primary key auto_increment, c char(1), unique(c));
472 insert into t1 values(100, "a");
473 insert into t1 values(300, "b");
474 insert into t1 values(50, "a");
475 ERROR 23000: Duplicate entry 'a' for key 'c'
476 insert into t1 values(null, "c");
477 select * from t1;
478 n c
479 100 a
480 300 b
481 301 c
482 update t1 set n=400,c='a' where n=301;
483 ERROR 23000: Duplicate entry 'a' for key 'c'
484 insert into t1 values(null, "d");
485 select * from t1;
486 n c
487 100 a
488 300 b
489 301 c
490 302 d
491 drop table t1;
492 create table t1 (n int not null primary key auto_increment, c char(1), unique(c)) transactional=0 row_format=dynamic;
493 insert into t1 values(100, "a");
494 insert into t1 values(300, "b");
495 insert into t1 values(50, "a");
496 ERROR 23000: Duplicate entry 'a' for key 'c'
497 insert into t1 values(null, "c");
498 select * from t1;
499 n c
500 100 a
501 300 b
502 301 c
503 update t1 set n=400,c='a' where n=301;
504 ERROR 23000: Duplicate entry 'a' for key 'c'
505 insert into t1 values(null, "d");
506 select * from t1;
507 n c
508 100 a
509 300 b
510 301 c
511 302 d
512 drop table t1;
513 create table t1 (n int not null, c char(1)) engine=aria;
514 alter table t1 engine=myisam;
515 alter table t1 engine=aria;
516 show create table t1;
517 Table Create Table
518 t1 CREATE TABLE `t1` (
519 `n` int(11) NOT NULL,
520 `c` char(1) DEFAULT NULL
521 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0
522 drop table t1;
523 create table t1 (n int not null, c char(1)) engine=aria transactional=1;
524 alter table t1 engine=myisam;
525 alter table t1 engine=aria;
526 show create table t1;
527 Table Create Table
528 t1 CREATE TABLE `t1` (
529 `n` int(11) NOT NULL,
530 `c` char(1) DEFAULT NULL
531 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 TRANSACTIONAL=1
532 drop table t1;
533 create table t1 (n int not null, c char(1)) engine=myisam transactional=1;
534 Warnings:
535 Warning 1911 Unknown option 'transactional'
536 alter table t1 engine=aria;
537 show create table t1;
538 Table Create Table
539 t1 CREATE TABLE `t1` (
540 `n` int(11) NOT NULL,
541 `c` char(1) DEFAULT NULL
542 ) ENGINE=Aria DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=0 TRANSACTIONAL=1
543 drop table t1;
544 create table t1 (a int, key(a)) transactional=0;
545 insert into t1 values (0),(1),(2),(3),(4);
546 insert into t1 select NULL from t1;
547 check table t1;
548 Table Op Msg_type Msg_text
549 test.t1 check status OK
550 drop table t1;
551 create temporary table t1 (a int, key(a)) transactional=1;
552 create temporary table t2 (a int, key(a)) transactional=1;
553 insert into t1 values (0),(1),(2),(3),(4);
554 insert into t2 select * from t1;
555 insert into t1 select NULL from t2;
556 select count(*) from t1;
557 count(*)
558 10
559 select count(*) from t1 where a >= 4;
560 count(*)
561 1
562 drop table t1, t2;
563 create temporary table t1 (a int, key(a)) transactional=0 row_format=page;
564 create temporary table t2 (a int, key(a)) transactional=0 row_format=page;
565 insert into t1 values (0),(1),(2),(3),(4);
566 insert into t2 select * from t1;
567 insert into t1 select NULL from t2;
568 select count(*) from t1;
569 count(*)
570 10
571 select count(*) from t1 where a >= 4;
572 count(*)
573 1
574 drop table t1, t2;
575 create temporary table t1 (a int, key(a)) transactional=0 row_format=fixed;
576 create temporary table t2 (a int, key(a)) transactional=0 row_format=dynamic;
577 insert into t1 values (0),(1),(2),(3),(4);
578 insert into t2 select * from t1;
579 insert into t1 select NULL from t2;
580 select count(*) from t1;
581 count(*)
582 10
583 select count(*) from t1 where a >= 4;
584 count(*)
585 1
586 drop table t1, t2;
587 create table t1 (i int auto_increment not null primary key) transactional=0;
588 check table t1 extended;
589 Table Op Msg_type Msg_text
590 test.t1 check status OK
591 delete from t1 where i = 10;
592 check table t1 extended;
593 Table Op Msg_type Msg_text
594 test.t1 check status OK
595 drop table t1;
596 create table t1 (i int auto_increment not null primary key);
597 check table t1 extended;
598 Table Op Msg_type Msg_text
599 test.t1 check status OK
600 delete from t1 where i = 10;
601 check table t1 extended;
602 Table Op Msg_type Msg_text
603 test.t1 check status OK
604 drop table t1;
605 CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)) transactional=0;
606 INSERT INTO t1 VALUES('Offside'),('City Of God');
607 SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
608 a
609 City Of God
610 SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of)*' IN BOOLEAN MODE);
611 a
612 City Of God
613 DROP TABLE t1;
614 create table t1(a int) engine=aria transactional=1;
615 select CREATE_OPTIONS from information_schema.TABLES where
616 TABLE_SCHEMA='test' and TABLE_NAME='t1';
617 CREATE_OPTIONS
618 transactional=1
619 drop table t1;
620 create table t1 (a int, unique(a)) engine=aria transactional=1;
621 insert into t1 values(1);
622 insert into t1 values(2),(2);
623 ERROR 23000: Duplicate entry '2' for key 'a'
624 create table t2 (a int, unique(a)) engine=aria transactional=0 row_format=dynamic;
625 insert into t2 values(1);
626 insert into t2 values(2),(2);
627 ERROR 23000: Duplicate entry '2' for key 'a'
628 connect root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK;
629 connection root;
630 insert into t1 values(3);
631 insert into t2 values(3);
632 connection default;
633 drop table t1, t2;
634 CREATE TABLE t1 (
635 a INT PRIMARY KEY,
636 b CHAR(255),
637 c VARCHAR(2048),
638 d VARCHAR(18990),
639 e CHAR(128),
640 f CHAR(192)
641 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
642 INSERT INTO t1 VALUES
643 (1,'A','B','C','','D'),
644 (2,'Abcdefghi','E','F','','G');
645 CREATE TABLE t2 (
646 g INT PRIMARY KEY,
647 h CHAR(32),
648 i CHAR(255),
649 j TEXT
650 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
651 INSERT INTO t2 VALUES (1,'M','','H'),
652 (2,'N','','H');
653 SELECT * FROM t1, t2 WHERE a = g ORDER BY b;
654 a b c d e f g h i j
655 1 A B C D 1 M H
656 2 Abcdefghi E F G 2 N H
657 drop table t1,t2;
658 # End of 5.1 tests
659 create table t1 (a int) engine=aria;
660 lock table t1 write;
661 drop table t1;
662 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;