myisam.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, "Sergei");
update t1 set b=repeat('a',256);
update t1 set i1=0, i2=0, i3=0, i4=0, i5=0, i6=0, i7=0;
check table t1;
delete from t1 where i8=1;
select i1,i2 from t1;
check table t1;
drop table t1;

#
# Test of REPAIR that once failed
#
CREATE TABLE `t1` (
  `post_id` mediumint(8) unsigned NOT NULL auto_increment,
  `topic_id` mediumint(8) unsigned NOT NULL default '0',
  `post_time` datetime NOT NULL default '0000-00-00 00:00:00',
  `post_text` text NOT NULL,
  `icon_url` varchar(10) NOT NULL default '',
  `sign` tinyint(1) unsigned NOT NULL default '0',
  `post_edit` varchar(150) NOT NULL default '',
  `poster_login` varchar(35) NOT NULL default '',
  `ip` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`post_id`),
  KEY `post_time` (`post_time`),
  KEY `ip` (`ip`),
  KEY `poster_login` (`poster_login`),
  KEY `topic_id` (`topic_id`),
  FULLTEXT KEY `post_text` (`post_text`)
) ENGINE=MyISAM;

INSERT INTO t1 (post_text) VALUES ('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test'),('ceci est un test');

REPAIR TABLE t1;
CHECK TABLE t1;
drop table t1;

#
# Test of creating table with too long key
#

--error 1071
CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255), KEY t1 (a, b, c, d, e));
CREATE TABLE t1 (a varchar(255), b varchar(255), c varchar(255), d varchar(255), e varchar(255));
--error 1071
ALTER TABLE t1 ADD INDEX t1 (a, b, c, d, e);
DROP TABLE t1;

#
# Test of cardinality of keys with NULL
#

CREATE TABLE t1 (a int not null, b int, c int, key(b), key(c), key(a,b), key(c,a));
INSERT into t1 values (0, null, 0), (0, null, 1), (0, null, 2), (0, null,3), (1,1,4);
create table t2 (a int not null, b int, c int, key(b), key(c), key(a));
INSERT into t2 values (1,1,1), (2,2,2);
optimize table t1;
show index from t1;
explain select * from t1,t2 where t1.a=t2.a;
explain select * from t1,t2 force index(a) where t1.a=t2.a;
explain select * from t1 force index(a),t2 force index(a) where t1.a=t2.a;
explain select * from t1,t2 where t1.b=t2.b;
explain select * from t1,t2 force index(c) where t1.a=t2.a;
explain select * from t1 where a=0 or a=2;
explain select * from t1 force index (a) where a=0 or a=2;
explain select * from t1 where c=1;
explain select * from t1 use index() where c=1;
drop table t1,t2;

#
# Test bug when updating a split dynamic row where keys are not changed
#

create table t1 (a int not null auto_increment primary key, b varchar(255));
insert into t1 (b) values (repeat('a',100)),(repeat('b',100)),(repeat('c',100));
update t1 set b=repeat(left(b,1),200) where a=1;
delete from t1 where (a & 1)= 0;
update t1 set b=repeat('e',200) where a=1;
flush tables;
check table t1;

#
# check updating with keys
#

disable_query_log;
let $1 = 100;
while ($1)
{
  eval insert into t1 (b) values (repeat(char(($1 & 32)+65), $1));
  dec $1;
}
enable_query_log;
update t1 set b=repeat(left(b,1),255) where a between 1 and 5;
update t1 set b=repeat(left(b,1),10) where a between 32 and 43;
update t1 set b=repeat(left(b,1),2) where a between 64 and 66;
update t1 set b=repeat(left(b,1),65) where a between 67 and 70;
check table t1;
insert into t1 (b) values (repeat('z',100));
update t1 set b="test" where left(b,1) > 'n';
check table t1;
drop table t1;

#
# two bugs in myisam-space-stripping feature
#
create table t1 ( a text not null, key a (a(20)));
insert into t1 values ('aaa   '),('aaa'),('aa');
check table t1;
repair table t1;
select concat(a,'.') from t1 where a='aaa';
select concat(a,'.') from t1 where binary a='aaa';
update t1 set a='bbb' where a='aaa';
select concat(a,'.') from t1;
drop table t1;

#
# Third bug in the same code (BUG#2295)
#

create table t1(a text not null, b text not null, c text not null, index (a(10),b(10),c(10)));
insert into t1 values('807780', '477', '165');
insert into t1 values('807780', '477', '162');
insert into t1 values('807780', '472', '162');
select * from t1 where a='807780' and b='477' and c='165';
drop table t1;

#
# space-stripping in _mi_prefix_search: BUG#5284
#
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a varchar(150) NOT NULL, KEY (a)); 
INSERT t1 VALUES ("can \tcan"); 
INSERT t1 VALUES ("can   can"); 
INSERT t1 VALUES ("can"); 
SELECT * FROM t1;
CHECK TABLE t1;
DROP TABLE t1;

#
# Verify blob handling
#
create table t1 (a blob);
insert into t1 values('a '),('a');
select concat(a,'.') from t1 where a='a';
select concat(a,'.') from t1 where a='a ';
alter table t1 add key(a(2));
select concat(a,'.') from t1 where a='a';
select concat(a,'.') from t1 where a='a ';
drop table t1;

#
# Test text and unique
#
create table t1 (a int not null auto_increment primary key, b text not null, unique b (b(20)));
insert into t1 (b) values ('a'),('b'),('c');
select concat(b,'.') from t1;
update t1 set b='b ' where a=2;
--error 1062
update t1 set b='b  ' where a > 1;
--error 1062
insert into t1 (b) values ('b');
select * from t1;
delete from t1 where b='b';
select a,concat(b,'.') from t1;
drop table t1;

#
# Test keys with 0 segments. (Bug #3203)
#
create table t1 (a int not null);
create table t2 (a int not null, primary key (a));
insert into t1 values (1);
insert into t2 values (1),(2);
select sql_big_result distinct t1.a from t1,t2 order by t2.a;
select distinct t1.a from t1,t2 order by t2.a;
select sql_big_result distinct t1.a from t1,t2;
explain select sql_big_result distinct t1.a from t1,t2 order by t2.a;
explain select distinct t1.a from t1,t2 order by t2.a;
drop table t1,t2;

#
# Bug#14616 - Freshly imported table returns error 124 when using LIMIT
#
create table t1 (
  c1 varchar(32),
  key (c1)
) engine=myisam;
alter table t1 disable keys;
insert into t1 values ('a'), ('b');
select c1 from t1 order by c1 limit 1;
drop table t1;

#
# Bug #14400  Join could miss concurrently inserted row
#
# Partial key.
create table t1 (a int not null, primary key(a));
create table t2 (a int not null, b int not null, primary key(a,b));
insert into t1 values (1),(2),(3),(4),(5),(6);
insert into t2 values (1,1),(2,1);
lock tables t1 read local, t2 read local;
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
connect (root,localhost,root,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
insert into t2 values(2,0);
disconnect root;
connection default;
select straight_join * from t1,t2 force index (primary) where t1.a=t2.a;
drop table t1,t2;
#
# Full key.
CREATE TABLE t1 (c1 varchar(250) NOT NULL);
CREATE TABLE t2 (c1 varchar(250) NOT NULL, PRIMARY KEY (c1));
INSERT INTO t1 VALUES ('test000001'), ('test000002'), ('test000003');
INSERT INTO t2 VALUES ('test000002'), ('test000003'), ('test000004');
LOCK TABLES t1 READ LOCAL, t2 READ LOCAL;
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
  WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
connect (con1,localhost,root,,);
connection con1;
INSERT INTO t2 VALUES ('test000001'), ('test000005');
disconnect con1;
connection default;
SELECT t1.c1 AS t1c1, t2.c1 AS t2c1 FROM t1, t2
  WHERE t1.c1 = t2.c1 HAVING t1c1 != t2c1;
DROP TABLE t1,t2;

# End of 4.0 tests

#
# Test RTREE index
#
--error 1235, 1289
CREATE TABLE t1 (`a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', UNIQUE KEY `a` USING RTREE (`a`,`b`)) ENGINE=MyISAM;
# INSERT INTO t1 VALUES (1,1),(1,1);
# DELETE FROM rt WHERE a<1;
# DROP TABLE IF EXISTS t1;

create table t1 (a int, b varchar(200), c text not null) checksum=1;
create table t2 (a int, b varchar(200), c text not null) checksum=0;
insert t1 values (1, "aaa", "bbb"), (NULL, "", "ccccc"), (0, NULL, "");
insert t2 select * from t1;
checksum table t1, t2, t3 quick;
checksum table t1, t2, t3;
checksum table t1, t2, t3 extended;
#show table status;
drop table t1,t2;

create table t1 (a int, key (a));
show keys from t1;
alter table t1 disable keys;
show keys from t1;
create table t2 (a int);
let $i=1000;
set @@rand_seed1=31415926,@@rand_seed2=2718281828;
--disable_query_log
while ($i)
{
  dec $i;
  insert t2 values (rand()*100000);
}
--enable_query_log
insert t1 select * from t2;
show keys from t1;
alter table t1 enable keys;
show keys from t1;
alter table t1 engine=heap;
alter table t1 disable keys;
show keys from t1;
drop table t1,t2;

#
# index search for NULL in blob. Bug #4816
#
create table t1 ( a tinytext, b char(1), index idx (a(1),b) );
insert into t1 values (null,''), (null,'');
explain select count(*) from t1 where a is null;
select count(*) from t1 where a is null;
drop table t1;

#
# Bug #8306: TRUNCATE leads to index corruption 
#
create table t1 (c1 int, index(c1));
create table t2 (c1 int, index(c1)) engine=merge union=(t1);
insert into t1 values (1);
# Close all tables.