isam.result 2.67 KB
Newer Older
1
drop table if exists t1,t2;
monty@bitch.mysql.fi's avatar
monty@bitch.mysql.fi committed
2
create table t1 (a tinyint not null auto_increment, b blob not null, primary key (a)) type=isam;
3
delete from t1 where (a & 1);
monty@bitch.mysql.fi's avatar
monty@bitch.mysql.fi committed
4 5 6
select sum(length(b)) from t1;
sum(length(b))
3274494
7 8 9 10 11 12
drop table t1;
create table t1 (a int not null auto_increment,b int, primary key (a)) type=isam;
insert into t1 values (1,1),(NULL,2),(3,3),(NULL,4);
delete from t1 where a=4 or a=2;
insert into t1 values (NULL,4),(NULL,5),(6,6);
select * from t1;
13 14 15 16 17 18
a	b
1	1
5	5
3	3
4	4
6	6
19 20 21 22 23 24
delete from t1 where a=6;
replace t1 values (3,1);
replace t1 values (3,3);
ALTER TABLE t1 add c int;
insert into t1 values (NULL,6,6);
select * from t1;
25 26 27 28 29 30
a	b	c
1	1	NULL
5	5	NULL
3	3	NULL
4	4	NULL
6	6	6
31 32 33 34 35 36 37 38 39 40 41 42 43 44
drop table t1;
create table t1 (a int,b text, index(a)) type=isam;
Column 'a' is used with UNIQUE or INDEX but is not defined as NOT NULL
create table t1 (a int,b text, index(b)) type=isam;
BLOB column 'b' can't be used in key specification with the used table type
create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid)) type=isam;
Incorrect table definition; There can only be one auto column and it must be defined as a key
create table t1 (ordid int(8), unique (ordid)) type=isam;
Column 'ordid' is used with UNIQUE or INDEX but is not defined as NOT NULL
drop table if exists t1;
create table t1 (a int not null primary key, b int not null,c int not null, key(b,c));
insert into t1 values (1,2,2),(2,2,3),(3,2,4),(4,2,4);
create table t2 type=isam select * from t1;
optimize table t1;
45 46
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
47
check table t1,t2;
48 49
Table	Op	Msg_type	Msg_text
test.t1	check	status	OK
serg@serg.mysql.com's avatar
serg@serg.mysql.com committed
50
test.t2	check	error	The handler for the table doesn't support check
51
repair table t1,t2;
52 53
Table	Op	Msg_type	Msg_text
test.t1	repair	status	OK
serg@serg.mysql.com's avatar
serg@serg.mysql.com committed
54
test.t2	repair	error	The handler for the table doesn't support repair
55
check table t2,t1;
56
Table	Op	Msg_type	Msg_text
serg@serg.mysql.com's avatar
serg@serg.mysql.com committed
57
test.t2	check	error	The handler for the table doesn't support check
58
test.t1	check	status	OK
59 60
lock tables t1 write;
check table t2,t1;
61 62 63
Table	Op	Msg_type	Msg_text
test.t2	check	error	Table 't2' was not locked with LOCK TABLES
test.t1	check	status	OK
64
show columns from t1;
65 66 67 68
Field	Type	Null	Key	Default	Extra
a	int(11)		PRI	0	
b	int(11)		MUL	0	
c	int(11)			0	
69
show full columns from t1;
70 71 72 73
Field	Type	Null	Key	Default	Extra	Privileges
a	int(11)		PRI	0		select,insert,update,references
b	int(11)		MUL	0		select,insert,update,references
c	int(11)			0		select,insert,update,references
74
show index from t1;
75 76 77 78
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment
t1	0	PRIMARY	1	a	A	4	NULL	NULL		BTREE	
t1	1	b	1	b	A	1	NULL	NULL		BTREE	
t1	1	b	2	c	A	4	NULL	NULL		BTREE	
79
drop table t1,t2;