myisam.test

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

flush tables;
truncate table t1;
insert into t1 values (1);
# Close all tables.
flush tables;
# Open t2 and (implicitly) t1.
select * from t2;
# Truncate t1, wich was not recognized as open without the bugfix.
# Now, it should fail with a table-in-use error message.
--error 1105
truncate table t1;
# The insert used to fail on the crashed table.
insert into t1 values (1);
drop table t1,t2;

#
# bug9188 - Corruption Can't open file: 'table.MYI' (errno: 145)
#
create table t1 (c1 int, c2 varchar(4) not null default '',
                 key(c2(3))) default charset=utf8;
insert into t1 values (1,'A'), (2, 'B'), (3, 'A');
update t1 set c2='A  B' where c1=2;
check table t1;
drop table t1;


#
# Bug#12296 - CHECKSUM TABLE reports 0 for the table
# This happened if the first record was marked as deleted.
#
create table t1 (c1 int);
insert into t1 values (1),(2),(3),(4);
checksum table t1;
delete from t1 where c1 = 1;
create table t2 as select * from t1;
# The following returns 0 with the bug in place.
checksum table t1;
# The above should give the same number as the following.
checksum table t2;
drop table t1, t2;

#
# BUG#12232: New myisam_stats_method variable.
#

show variables like 'myisam_stats_method';

create table t1 (a int, key(a));
insert into t1 values (0),(1),(2),(3),(4);
insert into t1 select NULL from t1;

# default: NULLs considered inequal
analyze table t1; 
show index from t1;
insert into t1 values (11);
delete from t1 where a=11;
check table t1;
show index from t1;

# Set nulls to be equal:
set myisam_stats_method=nulls_equal;
show variables like 'myisam_stats_method';
insert into t1 values (11);
delete from t1 where a=11;

analyze table t1; 
show index from t1;

insert into t1 values (11);
delete from t1 where a=11;

check table t1;
show index from t1;

# Set nulls back to be equal 
set myisam_stats_method=DEFAULT;
show variables like 'myisam_stats_method';
insert into t1 values (11);
delete from t1 where a=11;

analyze table t1; 
show index from t1;

insert into t1 values (11);
delete from t1 where a=11;

check table t1;
show index from t1;

drop table t1;

# WL#2609, CSC#XXXX: MyISAM 
set myisam_stats_method=nulls_ignored;
show variables like 'myisam_stats_method';

create table t1 (
  a char(3), b char(4), c char(5), d char(6),
  key(a,b,c,d)
);
insert into t1 values ('bcd','def1', NULL, 'zz');
insert into t1 values ('bcd','def2', NULL, 'zz');
insert into t1 values ('bce','def1', 'yuu', NULL);
insert into t1 values ('bce','def2', NULL, 'quux');
analyze table t1;
show index from t1;
delete from t1;
analyze table t1;
show index from t1;

set myisam_stats_method=DEFAULT;
drop table t1;

# BUG#13814 - key value packed incorrectly for TINYBLOBs

create table t1(
  cip INT NOT NULL,
  time TIME NOT NULL,
  score INT NOT NULL DEFAULT 0,
  bob TINYBLOB
);

insert into t1 (cip, time) VALUES (1, '00:01'), (2, '00:02'), (3,'00:03');
insert into t1 (cip, bob, time) VALUES (4, 'a', '00:04'), (5, 'b', '00:05'), 
                                       (6, 'c', '00:06');
select * from t1 where bob is null and cip=1;
create index bug on t1 (bob(22), cip, time);
select * from t1 where bob is null and cip=1;
drop table t1;

#
# Bug#14980 - COUNT(*) incorrect on MyISAM table with certain INDEX
#
create table t1 (
  id1 int not null auto_increment,
  id2 int not null default '0',
  t text not null,
  primary key  (id1),
  key x (id2, t(32))
) engine=myisam;
insert into t1 (id2, t) values
(10, 'abc'), (10, 'abc'), (10, 'abc'),
(20, 'abc'), (20, 'abc'), (20, 'def'),
(10, 'abc'), (10, 'abc');
select count(*)   from t1 where id2 = 10;
select count(id1) from t1 where id2 = 10;
drop table t1;

# End of 4.1 tests

#
# Test varchar
#

let $default=`select @@storage_engine`;
set storage_engine=MyISAM;
source include/varchar.inc;

#
# Some errors/warnings on create
#

create table t1 (v varchar(65530), key(v));
drop table if exists t1;
create table t1 (v varchar(65536));
show create table t1;
drop table t1;
create table t1 (v varchar(65530) character set utf8);
show create table t1;
drop table t1;

# MyISAM specific varchar tests
--error 1118
create table t1 (v varchar(65535));

eval set storage_engine=$default;

#
# Test how DROP TABLE works if the index or data file doesn't exists

create table t1 (a int) engine=myisam;
system rm $MYSQLTEST_VARDIR/master-data/test/t1.MYI ;
drop table if exists t1;
create table t1 (a int) engine=myisam;
system rm $MYSQLTEST_VARDIR/master-data/test/t1.MYI ;
--error 1051,6
drop table t1;
create table t1 (a int) engine=myisam;
system rm $MYSQLTEST_VARDIR/master-data/test/t1.MYD ;
--error 1105,6,29
drop table t1;
--error 1051
drop table t1;

#
# Test concurrent insert
# First with static record length
#
set @save_concurrent_insert=@@concurrent_insert;
set global concurrent_insert=1;
create table t1 (a int);
insert into t1 values (1),(2),(3),(4),(5);
lock table t1 read local;
connect (con1,localhost,root,,);
connection con1;
# Insert in table without hole
insert into t1 values(6),(7);
connection default;
unlock tables;
delete from t1 where a>=3 and a<=4;
lock table t1 read local;
connection con1;
set global concurrent_insert=2;
# Insert in table with hole -> Should insert at end
insert into t1 values (8),(9);
connection default;
unlock tables;
# Insert into hole
insert into t1 values (10),(11),(12);
select * from t1;
check table t1;
drop table t1;
disconnect con1;

# Same test with dynamic record length
create table t1 (a int, b varchar(30) default "hello");
insert into t1 (a) values (1),(2),(3),(4),(5);
lock table t1 read local;
connect (con1,localhost,root,,);
connection con1;
# Insert in table without hole
insert into t1 (a) values(6),(7);
connection default;
unlock tables;
delete from t1 where a>=3 and a<=4;
lock table t1 read local;
connection con1;
set global concurrent_insert=2;
# Insert in table with hole -> Should insert at end
insert into t1 (a) values (8),(9);
connection default;
unlock tables;
# Insert into hole
insert into t1 (a) values (10),(11),(12);
select a from t1;
check table t1;
drop table t1;
disconnect con1;
set global concurrent_insert=@save_concurrent_insert;


# BUG#9622 - ANALYZE TABLE and ALTER TABLE .. ENABLE INDEX produce
# different statistics on the same table with NULL values.
create table t1 (a int, key(a));

insert into t1 values (1),(2),(3),(4),(NULL),(NULL),(NULL),(NULL);
analyze table t1;
show keys from t1;

alter table t1 disable keys;
alter table t1 enable keys;
show keys from t1;

drop table t1;

#
# Bug#10056 - PACK_KEYS option take values greater than 1 while creating table
#
create table t1 (c1 int) engine=myisam pack_keys=0;
create table t2 (c1 int) engine=myisam pack_keys=1;
create table t3 (c1 int) engine=myisam pack_keys=default;
--error 1064
create table t4 (c1 int) engine=myisam pack_keys=2;
drop table t1, t2, t3;