innodb.test

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

# Test for innodb_thread_sleep_delay variable
show variables like "innodb_thread_sleep_delay";
set global innodb_thread_sleep_delay=100000;
show variables like "innodb_thread_sleep_delay";
set global innodb_thread_sleep_delay=0;
show variables like "innodb_thread_sleep_delay";
set global innodb_thread_sleep_delay=10000;
show variables like "innodb_thread_sleep_delay";

#
# Test varchar
#

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

#
# Some errors/warnings on create
#

# Clean up filename -- embedded server reports whole path without .frm,
# regular server reports relative path with .frm (argh!)
--replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / t1.frm t1
create table t1 (v varchar(65530), key(v));
drop table 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;

eval set storage_engine=$default;

# InnoDB specific varchar tests
create table t1 (v varchar(16384)) engine=innodb;
drop table t1;

#
# BUG#11039 Wrong key length in min()
#

create table t1 (a char(1), b char(1), key(a, b)) engine=innodb;
insert into t1 values ('8', '6'), ('4', '7');
select min(a) from t1;
select min(b) from t1 where a='8';
drop table t1;

#
# Bug #11080 & #11005  Multi-row REPLACE fails on a duplicate key error
#

CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY  (`a`),UNIQUE KEY `b` (`b`)) ENGINE=innodb;
insert into t1 (b) values (1);
replace into t1 (b) values (2), (1), (3);
select * from t1;
truncate table t1;
insert into t1 (b) values (1);
replace into t1 (b) values (2);
replace into t1 (b) values (1);
replace into t1 (b) values (3);
select * from t1;
drop table t1;

create table t1 (rowid int not null auto_increment, val int not null,primary
key (rowid), unique(val)) engine=innodb;
replace into t1 (val) values ('1'),('2');
replace into t1 (val) values ('1'),('2');
--error 1062
insert into t1 (val) values ('1'),('2');
select * from t1;
drop table t1;

#
# Test that update does not change internal auto-increment value
#

create table t1 (a int not null auto_increment primary key, val int) engine=InnoDB;
insert into t1 (val) values (1);
update t1 set a=2 where a=1;
# We should get the following error because InnoDB does not update the counter
--error 1062
insert into t1 (val) values (1);
select * from t1;
drop table t1;
#
# Bug #10465
#

--disable_warnings
CREATE TABLE t1 (GRADE DECIMAL(4) NOT NULL, PRIMARY KEY (GRADE)) ENGINE=INNODB;
--enable_warnings
INSERT INTO t1 (GRADE) VALUES (151),(252),(343);
SELECT GRADE  FROM t1 WHERE GRADE > 160 AND GRADE < 300;
SELECT GRADE  FROM t1 WHERE GRADE= 151;
DROP TABLE t1;

#
# Bug #12340 multitable delete deletes only one record
#
create table t1 (f1 varchar(10), f2 varchar(10), primary key (f1,f2)) engine=innodb;
create table t2 (f3 varchar(10), f4 varchar(10), key (f4)) engine=innodb;
insert into t2 values ('aa','cc');
insert into t1 values ('aa','bb'),('aa','cc');
delete t1 from t1,t2 where f1=f3 and f4='cc';
select * from t1;
drop table t1,t2;

#
# Test that the slow TRUNCATE implementation resets autoincrement columns
# (bug #11946)
#

CREATE TABLE t1 (
id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE t2 (
id INTEGER NOT NULL,
FOREIGN KEY (id) REFERENCES t1 (id)
) ENGINE=InnoDB;

INSERT INTO t1 (id) VALUES (NULL);
SELECT * FROM t1;
TRUNCATE t1;
INSERT INTO t1 (id) VALUES (NULL);
SELECT * FROM t1;

# continued from above; test that doing a slow TRUNCATE on a table with 0
# rows resets autoincrement columns
DELETE FROM t1;
TRUNCATE t1;
INSERT INTO t1 (id) VALUES (NULL);
SELECT * FROM t1;
DROP TABLE t2, t1;

-- Test that foreign keys in temporary tables are not accepted (bug #12084)
CREATE TABLE t1
(
 id INT PRIMARY KEY
) ENGINE=InnoDB;

--error 1005,1005
CREATE TEMPORARY TABLE t2
(
 id INT NOT NULL PRIMARY KEY,
 b INT,
 FOREIGN KEY (b) REFERENCES test.t1(id)
) ENGINE=InnoDB;
DROP TABLE t1;

#
# Test that index column max sizes are honored (bug #13315)
#

# prefix index
create table t1 (col1 varchar(2000), index (col1(767)))
 character set = latin1 engine = innodb;

# normal indexes
create table t2 (col1 char(255), index (col1))
 character set = latin1 engine = innodb;
create table t3 (col1 binary(255), index (col1))
 character set = latin1 engine = innodb;
create table t4 (col1 varchar(767), index (col1))
 character set = latin1 engine = innodb;
create table t5 (col1 varchar(767) primary key)
 character set = latin1 engine = innodb;
create table t6 (col1 varbinary(767) primary key)
 character set = latin1 engine = innodb;
create table t7 (col1 text, index(col1(767)))
 character set = latin1 engine = innodb;
create table t8 (col1 blob, index(col1(767)))
 character set = latin1 engine = innodb;

# multi-column indexes are allowed to be longer
create table t9 (col1 varchar(512), col2 varchar(512), index(col1, col2))
 character set = latin1 engine = innodb;

show create table t9;

drop table t1, t2, t3, t4, t5, t6, t7, t8, t9;

# these should have their index length trimmed
create table t1 (col1 varchar(768), index(col1))
 character set = latin1 engine = innodb;
create table t2 (col1 varbinary(768), index(col1))
 character set = latin1 engine = innodb;
create table t3 (col1 text, index(col1(768)))
 character set = latin1 engine = innodb;
create table t4 (col1 blob, index(col1(768)))
 character set = latin1 engine = innodb;

show create table t1;

drop table t1, t2, t3, t4;

# these should be refused
--error 1071
create table t1 (col1 varchar(768) primary key)
 character set = latin1 engine = innodb;
--error 1071
create table t2 (col1 varbinary(768) primary key)
 character set = latin1 engine = innodb;
--error 1071
create table t3 (col1 text, primary key(col1(768)))
 character set = latin1 engine = innodb;
--error 1071
create table t4 (col1 blob, primary key(col1(768)))
 character set = latin1 engine = innodb;

#
# Test improved foreign key error messages (bug #3443)
#

CREATE TABLE t1
(
 id INT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE t2
(
 v INT,
 CONSTRAINT c1 FOREIGN KEY (v) REFERENCES t1(id)
) ENGINE=InnoDB;

--error 1452
INSERT INTO t2 VALUES(2);

INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(1);

--error 1451
DELETE FROM t1 WHERE id = 1;

--error 1217
DROP TABLE t1;

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE t1;
SET FOREIGN_KEY_CHECKS=1;

--error 1452
INSERT INTO t2 VALUES(3);

DROP TABLE t2;
#
# Test that checksum table uses a consistent read Bug #12669
#
connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
insert into t1 values (1),(2);
set autocommit=0;
checksum table t1;
connection b;
insert into t1 values(3);
connection a;
#
# Here checksum should not see insert
#
checksum table t1;
connection a;
commit;
checksum table t1;
commit;
drop table t1;
#
# autocommit = 1
#
connection a;
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
insert into t1 values (1),(2);
set autocommit=1;
checksum table t1;
connection b;
set autocommit=1;
insert into t1 values(3);
connection a;
#
# Here checksum sees insert
#
checksum table t1;
drop table t1;

connection default;
disconnect a;
disconnect b;

#
# BUG 14056 Column prefix index on UTF-8 primary key column causes: Can't find record..
#

create table t1 (
  a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
drop table t1;
drop table t2;

create table t1 (
  a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
drop table t1;
drop table t2;

create table t1 (
  a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six');
insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven');
insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
drop table t1;
drop table t2;

create table t1 (
  a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
) character set utf8 engine = innodb;
create table t2 (
  a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2))
) character set ucs2 engine = innodb;
insert into t1 values (1,'abcdefg','abcdefg','one');
insert into t1 values (2,'ijkilmn','ijkilmn','two');
insert into t1 values (3,'qrstuvw','qrstuvw','three');
insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four');
insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five');
insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight');
insert into t2 values (1,'abcdefg','abcdefg','one');
insert into t2 values (2,'ijkilmn','ijkilmn','two');
insert into t2 values (3,'qrstuvw','qrstuvw','three');
insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four');
insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five');
insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six');
insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven');
insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight');
insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten');
insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken');
drop table t1;
drop table t2;
commit;

#
# Test that we can create a large (>1K) key
#
create table t1 (a varchar(255) character set utf8,
                 b varchar(255) character set utf8,
                 c varchar(255) character set utf8,
                 d varchar(255) character set utf8,
                 key (a,b,c,d)) engine=innodb;
drop table t1;
--error ER_TOO_LONG_KEY
create table t1 (a varchar(255) character set utf8,
                 b varchar(255) character set utf8,
                 c varchar(255) character set utf8,
                 d varchar(255) character set utf8,
                 e varchar(255) character set utf8,
                 key (a,b,c,d,e)) engine=innodb;

--echo End of 5.0 tests