1
select * from INFORMATION_SCHEMA.ENGINES where ENGINE="MARIA";
2
ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS
3
MARIA YES Crash-safe tables with MyISAM heritage YES NO NO
4
set global storage_engine=maria;
5
set session storage_engine=maria;
6
set global maria_page_checksum=0;
7
set global maria_log_file_size=4294967295;
8
drop table if exists t1,t2;
10
create table t1 (a int not null, key `a` (a) key_block_size=512);
13
t1 CREATE TABLE `t1` (
15
KEY `a` (`a`) KEY_BLOCK_SIZE=8192
16
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0
18
create table t1 (a varchar(2048), key `a` (a) key_block_size=1000000000000000000);
20
Warning 1071 Specified key was too long; max key length is 1444 bytes
23
t1 CREATE TABLE `t1` (
24
`a` varchar(2048) DEFAULT NULL,
25
KEY `a` (`a`(1444)) KEY_BLOCK_SIZE=8192
26
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0
28
create table t1 (a int not null, key `a` (a) key_block_size=1025);
31
t1 CREATE TABLE `t1` (
33
KEY `a` (`a`) KEY_BLOCK_SIZE=8192
34
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0
36
create table t1 (a int not null, key key_block_size=1024 (a));
37
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1024 (a))' at line 1
38
create table t1 (a int not null, key `a` key_block_size=1024 (a));
39
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key_block_size=1024 (a))' at line 1
43
KEY (c1) KEY_BLOCK_SIZE 1024,
44
KEY (c2) KEY_BLOCK_SIZE 8192
46
INSERT INTO t1 VALUES (10, REPEAT('a', CEIL(RAND(10) * 300))),
47
(11, REPEAT('b', CEIL(RAND() * 300))),
48
(12, REPEAT('c', CEIL(RAND() * 300))),
49
(13, REPEAT('d', CEIL(RAND() * 300))),
50
(14, REPEAT('e', CEIL(RAND() * 300))),
51
(15, REPEAT('f', CEIL(RAND() * 300))),
52
(16, REPEAT('g', CEIL(RAND() * 300))),
53
(17, REPEAT('h', CEIL(RAND() * 300))),
54
(18, REPEAT('i', CEIL(RAND() * 300))),
55
(19, REPEAT('j', CEIL(RAND() * 300))),
56
(20, REPEAT('k', CEIL(RAND() * 300))),
57
(21, REPEAT('l', CEIL(RAND() * 300))),
58
(22, REPEAT('m', CEIL(RAND() * 300))),
59
(23, REPEAT('n', CEIL(RAND() * 300))),
60
(24, REPEAT('o', CEIL(RAND() * 300))),
61
(25, REPEAT('p', CEIL(RAND() * 300))),
62
(26, REPEAT('q', CEIL(RAND() * 300))),
63
(27, REPEAT('r', CEIL(RAND() * 300))),
64
(28, REPEAT('s', CEIL(RAND() * 300))),
65
(29, REPEAT('t', CEIL(RAND() * 300))),
66
(30, REPEAT('u', CEIL(RAND() * 300))),
67
(31, REPEAT('v', CEIL(RAND() * 300))),
68
(32, REPEAT('w', CEIL(RAND() * 300))),
69
(33, REPEAT('x', CEIL(RAND() * 300))),
70
(34, REPEAT('y', CEIL(RAND() * 300))),
71
(35, REPEAT('z', CEIL(RAND() * 300)));
72
INSERT INTO t1 SELECT * FROM t1;
73
INSERT INTO t1 SELECT * FROM t1;
75
Table Op Msg_type Msg_text
76
test.t1 check status OK
78
Table Op Msg_type Msg_text
79
test.t1 repair status OK
80
DELETE FROM t1 WHERE c1 >= 10;
82
Table Op Msg_type Msg_text
83
test.t1 check status OK
85
create table t1 (a int) transactional=0;
88
t1 CREATE TABLE `t1` (
89
`a` int(11) DEFAULT NULL
90
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 TRANSACTIONAL=0
92
create table t1 (a int) row_format=dynamic transactional=0;
95
t1 CREATE TABLE `t1` (
96
`a` int(11) DEFAULT NULL
97
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=DYNAMIC TRANSACTIONAL=0
99
create table t1 (a int) row_format=dynamic transactional=1;
101
Note 1478 Row format set to PAGE because of TRANSACTIONAL=1 option
102
show create table t1;
104
t1 CREATE TABLE `t1` (
105
`a` int(11) DEFAULT NULL
106
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
107
alter table t1 row_format=PAGE;
108
show create table t1;
110
t1 CREATE TABLE `t1` (
111
`a` int(11) DEFAULT NULL
112
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
113
alter table t1 row_format=DYNAMIC;
115
Note 1478 Row format set to PAGE because of TRANSACTIONAL=1 option
116
show create table t1;
118
t1 CREATE TABLE `t1` (
119
`a` int(11) DEFAULT NULL
120
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=1
121
alter table t1 transactional=0;
122
show create table t1;
124
t1 CREATE TABLE `t1` (
125
`a` int(11) DEFAULT NULL
126
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=PAGE TRANSACTIONAL=0
127
alter table t1 row_format=DYNAMIC;
128
show create table t1;
130
t1 CREATE TABLE `t1` (
131
`a` int(11) DEFAULT NULL
132
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=DYNAMIC TRANSACTIONAL=0
134
create table t1 (a int) row_format=PAGE;
135
show create table t1;
137
t1 CREATE TABLE `t1` (
138
`a` int(11) DEFAULT NULL
139
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=PAGE
141
create table t1 (a int) row_format=PAGE TRANSACTIONAL=DEFAULT;
142
show create table t1;
144
t1 CREATE TABLE `t1` (
145
`a` int(11) DEFAULT NULL
146
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=PAGE
147
alter table t1 row_format=DYNAMIC;
148
show create table t1;
150
t1 CREATE TABLE `t1` (
151
`a` int(11) DEFAULT NULL
152
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=0 ROW_FORMAT=DYNAMIC
155
t1_name varchar(255) default null,
156
t1_id int(10) unsigned not null auto_increment,
159
) engine=maria auto_increment = 1000 default charset=latin1;
160
lock tables t1 write;
161
INSERT INTO `t1` VALUES ('bla',1000),('bla',1001),('bla',1002);
163
Table Op Msg_type Msg_text
164
test.t1 check status OK
166
create table t2 like t1;
167
insert into t2 select * from t1;
169
Table Op Msg_type Msg_text
170
test.t2 analyze status Table is already up to date
172
insert into t2 select * from t1;
174
Table Op Msg_type Msg_text
175
test.t2 analyze status Table is already up to date
177
create table t1 (a bigint auto_increment, primary key(a), b char(255), c varchar(20000));
178
update t1 set b=repeat('a',100) where a between 1 and 100;
180
Table Op Msg_type Msg_text
181
test.t1 check status OK
182
update t1 set c=repeat('a',8192*2) where a between 200 and 202;
184
Table Op Msg_type Msg_text
185
test.t1 check status OK
187
CREATE TABLE t1 (a int, b int, v varchar(60000)) checksum=1 engine=maria;
188
insert into t1 values (1,1,"aaa"),(1,2,null);
193
insert into t1 values (1,3,repeat('c',30000)),(4,4,repeat('a',30000));
194
update t1 set v="row5" where b=4;
195
delete from t1 where b=3;
196
select a, b, length(v) from t1;
203
auto int(5) unsigned NOT NULL auto_increment,
204
string char(10) default "hello",
205
tiny tinyint(4) DEFAULT '0' NOT NULL ,
206
short smallint(6) DEFAULT '1' NOT NULL ,
207
medium mediumint(8) DEFAULT '0' NOT NULL,
208
long_int int(11) DEFAULT '0' NOT NULL,
209
longlong bigint(13) DEFAULT '0' NOT NULL,
210
real_float float(13,1) DEFAULT 0.0 NOT NULL,
211
real_double double(16,4),
212
utiny tinyint(3) unsigned DEFAULT '0' NOT NULL,
213
ushort smallint(5) unsigned zerofill DEFAULT '00000' NOT NULL,
214
umedium mediumint(8) unsigned DEFAULT '0' NOT NULL,
215
ulong int(11) unsigned DEFAULT '0' NOT NULL,
216
ulonglong bigint(13) unsigned DEFAULT '0' NOT NULL,
217
time_stamp timestamp,
222
tinyblob_col tinyblob,
223
mediumblob_col mediumblob not null default '',
224
longblob_col longblob not null default '',
225
options enum('one','two','tree') not null ,
226
flags set('one','two','tree') not null default '',
231
KEY any_name (medium),
237
KEY (ulonglong,ulong),
240
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');
241
create table t2 (primary key (auto)) engine=maria 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;
243
Table Op Msg_type Msg_text
244
test.t1 check status OK
245
test.t2 check status OK
246
select t1,t2,length(t3),length(t4),length(t5),length(t6),t7,t8 from t2;
247
t1 t2 length(t3) length(t4) length(t5) length(t6) t7 t8
248
1 a 256 256 4096 4096
250
create table t2 (primary key (auto)) engine=maria 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;
252
Table Op Msg_type Msg_text
253
test.t2 check status OK
255
CREATE TABLE t1 (seq int, s1 int, s2 blob);
256
insert into t1 values (1, 1, MD5(1));
257
update t1 set s1=2 where seq=1;
258
check table t1 extended;
259
Table Op Msg_type Msg_text
260
test.t1 check status OK
262
show variables like 'maria%';
264
maria_block_size 8192
265
maria_checkpoint_interval 30
266
maria_force_start_after_recovery_failures 0
267
maria_log_file_size 4294959104
268
maria_log_purge_type immediate
269
maria_max_sort_file_size 9223372036854775807
270
maria_page_checksum OFF
271
maria_pagecache_age_threshold 300
272
maria_pagecache_buffer_size 8388600
273
maria_pagecache_division_limit 100
275
maria_repair_threads 1
276
maria_sort_buffer_size 8388608
277
maria_stats_method nulls_unequal
278
maria_sync_log_dir NEWFILE
279
show status like 'maria%';
281
Maria_pagecache_blocks_not_flushed #
282
Maria_pagecache_blocks_unused #
283
Maria_pagecache_blocks_used #
284
Maria_pagecache_read_requests #
285
Maria_pagecache_reads #
286
Maria_pagecache_write_requests #
287
Maria_pagecache_writes #
288
create table t1 (b char(0));
289
insert into t1 values(NULL),("");
290
select length(b) from t1;
294
alter table t1 add column c char(0), add key (c);
295
insert into t1 values("",""),("",NULL);
296
select length(b),length(c) from t1;
302
select length(b),length(c) from t1 where c is null;
307
select length(b),length(c) from t1 where c is not null;
310
select length(b),length(c) from t1 order by c;
316
alter table t1 add column d char(0) not null, add key (d);
317
ERROR 42000: The used storage engine can't index column 'd'
319
CREATE TABLE t1 (a bit(3));
320
insert into t1 values (NULL),(0),(1),(2),(3),(4),(5),(6),(7);
321
select hex(a) from t1;
333
create table t1(a bit not null);
334
insert into t1 values(0),(1);
340
CREATE TABLE t1 (col1 int, s1 char(16) DEFAULT NULL, s2 char(16) DEFAULT NULL, KEY (s1,s2));
341
insert into t1 (col1) values(0);
343
set global maria_page_checksum=1;
344
create table t1 (a int);
345
show create table t1;
347
t1 CREATE TABLE `t1` (
348
`a` int(11) DEFAULT NULL
349
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
351
set global maria_log_file_size=4294967296;
353
Warning 1292 Truncated incorrect log_file_size value: '4294967296'
354
create table t1 (a int not null);
355
lock tables t1 write;
356
insert into t1 values (1),(2);
361
insert into t1 values (1),(2);
366
create table t1 (c int);
367
insert into t1 values(1),(2);
368
create table t2 select * from t1;
369
create table t3 select * from t1, t2;
370
ERROR 42S21: Duplicate column name 'c'
371
create table t3 select t1.c AS c1, t2.c AS c2,1 as "const" from t1, t2;
372
drop table t1, t2, t3;
373
create table t1 (t datetime) engine=maria;
374
insert into t1 values (101),(691231),(700101),(991231),(10000101),(99991231),(101000000),(691231000000),(700101000000),(991231235959),(10000101000000),(99991231235959),(20030100000000),(20030000000000);
392
Table Op Msg_type Msg_text
393
test.t1 optimize status OK
395
Table Op Msg_type Msg_text
396
test.t1 check status OK
397
delete from t1 where t > 0;
399
Table Op Msg_type Msg_text
400
test.t1 optimize status OK
402
Table Op Msg_type Msg_text
403
test.t1 check status OK
405
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
406
CREATE TABLE t1 (id int(11) PRIMARY KEY auto_increment,f1 varchar(10) NOT NULL UNIQUE);
407
INSERT IGNORE INTO t1 (f1) VALUES ("test1");
408
INSERT IGNORE INTO t1 (f1) VALUES ("test1");
409
INSERT IGNORE INTO t1 (f1) VALUES ("test2");
415
SET SQL_MODE = 'TRADITIONAL';
416
create table t1 (n int not null primary key auto_increment, c char(1), unique(c));
417
insert into t1 values(100, "a");
418
insert into t1 values(300, "b");
419
insert into t1 values(50, "a");
420
ERROR 23000: Duplicate entry 'a' for key 'c'
421
insert into t1 values(null, "c");
427
update t1 set n=400,c='a' where n=301;
428
ERROR 23000: Duplicate entry 'a' for key 'c'
429
insert into t1 values(null, "d");
437
create table t1 (n int not null primary key auto_increment, c char(1), unique(c)) transactional=0 row_format=dynamic;
438
insert into t1 values(100, "a");
439
insert into t1 values(300, "b");
440
insert into t1 values(50, "a");
441
ERROR 23000: Duplicate entry 'a' for key 'c'
442
insert into t1 values(null, "c");
448
update t1 set n=400,c='a' where n=301;
449
ERROR 23000: Duplicate entry 'a' for key 'c'
450
insert into t1 values(null, "d");
458
create table t1 (n int not null, c char(1)) engine=maria;
459
alter table t1 engine=myisam;
460
alter table t1 engine=maria;
461
show create table t1;
463
t1 CREATE TABLE `t1` (
464
`n` int(11) NOT NULL,
465
`c` char(1) DEFAULT NULL
466
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
468
create table t1 (n int not null, c char(1)) engine=maria transactional=1;
469
alter table t1 engine=myisam;
471
Error 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
472
alter table t1 engine=maria;
473
show create table t1;
475
t1 CREATE TABLE `t1` (
476
`n` int(11) NOT NULL,
477
`c` char(1) DEFAULT NULL
478
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1
480
create table t1 (n int not null, c char(1)) engine=myisam transactional=1;
482
Error 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'
483
alter table t1 engine=maria;
484
show create table t1;
486
t1 CREATE TABLE `t1` (
487
`n` int(11) NOT NULL,
488
`c` char(1) DEFAULT NULL
489
) ENGINE=MARIA DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1 TRANSACTIONAL=1
491
create table t1 (a int, key(a)) transactional=0;
492
insert into t1 values (0),(1),(2),(3),(4);
493
insert into t1 select NULL from t1;
495
Table Op Msg_type Msg_text
496
test.t1 check status OK
498
create temporary table t1 (a int, key(a)) transactional=1;
499
create temporary table t2 (a int, key(a)) transactional=1;
500
insert into t1 values (0),(1),(2),(3),(4);
501
insert into t2 select * from t1;
502
insert into t1 select NULL from t2;
503
select count(*) from t1;
506
select count(*) from t1 where a >= 4;
510
create table t1 (i int auto_increment not null primary key) transactional=0;
511
check table t1 extended;
512
Table Op Msg_type Msg_text
513
test.t1 check status OK
514
delete from t1 where i = 10;
515
check table t1 extended;
516
Table Op Msg_type Msg_text
517
test.t1 check status OK
519
create table t1 (i int auto_increment not null primary key);
520
check table t1 extended;
521
Table Op Msg_type Msg_text
522
test.t1 check status OK
523
delete from t1 where i = 10;
524
check table t1 extended;
525
Table Op Msg_type Msg_text
526
test.t1 check status OK
528
CREATE TABLE t1(a VARCHAR(20), FULLTEXT(a)) transactional=0;
529
INSERT INTO t1 VALUES('Offside'),('City Of God');
530
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city of*' IN BOOLEAN MODE);
533
SELECT a FROM t1 WHERE MATCH a AGAINST ('+city (of)*' IN BOOLEAN MODE);
537
create table t1(a int) engine=maria transactional=1;
538
select CREATE_OPTIONS from information_schema.TABLES where
539
TABLE_SCHEMA='test' and TABLE_NAME='t1';