diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb_misc1.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb_misc1.test | 1196 |
1 files changed, 0 insertions, 1196 deletions
diff --git a/mysql-test/suite/innodb/t/innodb_misc1.test b/mysql-test/suite/innodb/t/innodb_misc1.test deleted file mode 100644 index 9efec68afd5..00000000000 --- a/mysql-test/suite/innodb/t/innodb_misc1.test +++ /dev/null @@ -1,1196 +0,0 @@ --- source include/have_innodb.inc - -let $MYSQLD_DATADIR= `select @@datadir`; -# Save the original values of some variables in order to be able to -# estimate how much they have changed during the tests. Previously this -# test assumed that e.g. rows_deleted is 0 here and after deleting 23 -# rows it expected that rows_deleted will be 23. Now we do not make -# assumptions about the values of the variables at the beginning, e.g. -# rows_deleted should be 23 + "rows_deleted before the test". This allows -# the test to be run multiple times without restarting the mysqld server. -# See Bug#43309 Test main.innodb can't be run twice --- disable_query_log - -call mtr.add_suppression("Cannot add field .* in table .* because after adding it, the row size is"); -call mtr.add_suppression("\\[ERROR\\] InnoDB: in ALTER TABLE `test`.`t1`"); -call mtr.add_suppression("\\[ERROR\\] InnoDB: in RENAME TABLE table `test`.`t1`"); -SET @innodb_thread_concurrency_orig = @@innodb_thread_concurrency; --- enable_query_log - ---disable_warnings -drop table if exists t1,t2,t3,t4; -drop database if exists mysqltest; ---enable_warnings - -# 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 ER_DUP_ENTRY -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 ER_DUP_ENTRY -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; ---error ER_TRUNCATE_ILLEGAL_FK -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; ---error ER_TRUNCATE_ILLEGAL_FK -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 ER_CANT_CREATE_TABLE -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 tests can be found in innodb_16k, innodb_8k & innodb_4k - -drop table t1, t2, t3, t4, t5, t6, t7, t8; - -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; - -SET GLOBAL innodb_default_row_format=compact; ---error ER_INDEX_COLUMN_TOO_LONG -create table t1 (col1 varchar(768) primary key) - character set = latin1 engine = innodb; ---error ER_INDEX_COLUMN_TOO_LONG -create table t2 (col1 varbinary(768) primary key) - character set = latin1 engine = innodb; ---error ER_INDEX_COLUMN_TOO_LONG -create table t3 (col1 text, primary key(col1(768))) - character set = latin1 engine = innodb; ---error ER_INDEX_COLUMN_TOO_LONG -create table t4 (col1 blob, primary key(col1(768))) - character set = latin1 engine = innodb; - -SET GLOBAL innodb_default_row_format=dynamic; -create table t1 (col1 varchar(768) primary key) - character set = latin1 engine = innodb; -drop table t1; -create table t2 (col1 varbinary(768) primary key) - character set = latin1 engine = innodb; -drop table t2; -create table t3 (col1 text, primary key(col1(768))) - character set = latin1 engine = innodb; -drop table t3; -create table t4 (col1 blob, primary key(col1(768))) - character set = latin1 engine = innodb; -drop table t4; -SET GLOBAL innodb_default_row_format=default; - -SET sql_mode = default; - -# these should be refused -SET GLOBAL innodb_default_row_format=compact; ---error ER_INDEX_COLUMN_TOO_LONG -create table t1 (col1 varchar(768) primary key) - character set = latin1 engine = innodb; ---error ER_INDEX_COLUMN_TOO_LONG -create table t2 (col1 varbinary(768) primary key) - character set = latin1 engine = innodb; ---error ER_INDEX_COLUMN_TOO_LONG -create table t3 (col1 text, primary key(col1(768))) - character set = latin1 engine = innodb; ---error ER_INDEX_COLUMN_TOO_LONG -create table t4 (col1 blob, primary key(col1(768))) - character set = latin1 engine = innodb; -SET GLOBAL innodb_default_row_format=default; - -# -# 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 ER_NO_REFERENCED_ROW_2 -INSERT INTO t2 VALUES(2); - -INSERT INTO t1 VALUES(1); -INSERT INTO t2 VALUES(1); - ---error ER_ROW_IS_REFERENCED_2 -DELETE FROM t1 WHERE id = 1; - ---error ER_ROW_IS_REFERENCED_2 -DROP TABLE t1; - -SET FOREIGN_KEY_CHECKS=0; -DROP TABLE t1; -SET FOREIGN_KEY_CHECKS=1; - ---error ER_NO_REFERENCED_ROW_2 -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; - -# tests for bugs #9802 and #13778 - -# test that FKs between invalid types are not accepted - -set foreign_key_checks=0; -create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; ---error ER_CANT_CREATE_TABLE -create table t1(a char(10) primary key, b varchar(20)) engine = innodb; -set foreign_key_checks=1; -drop table t2; - -# test that FKs between different charsets are not accepted in CREATE even -# when f_k_c is 0 - -set foreign_key_checks=0; -create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; ---error ER_CANT_CREATE_TABLE -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8; -set foreign_key_checks=1; -drop table t1; - -# test that invalid datatype conversions with ALTER are not allowed - -set foreign_key_checks=0; -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb; -create table t1(a varchar(10) primary key) engine = innodb; --- error 1025,1025 -alter table t1 modify column a int; -set foreign_key_checks=1; -drop table t2,t1; - -# test that charset conversions with ALTER are allowed when f_k_c is 0 - -set foreign_key_checks=0; -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; -create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; -alter table t1 convert to character set utf8; -set foreign_key_checks=1; -drop table t2,t1; - -# test that RENAME does not allow invalid charsets when f_k_c is 0 - -set foreign_key_checks=0; -create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=latin1; -create table t3(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=utf8; -# Embedded server doesn't chdir to data directory ---replace_result $MYSQLD_DATADIR ./ master-data/ '' --- error 1025 -rename table t3 to t1; -set foreign_key_checks=1; -drop table t2,t3; - -# test that foreign key errors are reported correctly (Bug #15550) - -create table t1(a int primary key) row_format=redundant engine=innodb; -create table t2(a int primary key,constraint foreign key(a)references t1(a)) row_format=compact engine=innodb; -create table t3(a int primary key) row_format=compact engine=innodb; -create table t4(a int primary key,constraint foreign key(a)references t3(a)) row_format=redundant engine=innodb; - -insert into t1 values(1); -insert into t3 values(1); --- error 1452 -insert into t2 values(2); --- error 1452 -insert into t4 values(2); -insert into t2 values(1); -insert into t4 values(1); --- error 1451 -update t1 set a=2; --- error 1452 -update t2 set a=2; --- error 1451 -update t3 set a=2; --- error 1452 -update t4 set a=2; --- error ER_TRUNCATE_ILLEGAL_FK -truncate t1; --- error ER_TRUNCATE_ILLEGAL_FK -truncate t3; -truncate t2; -truncate t4; --- error ER_TRUNCATE_ILLEGAL_FK -truncate t1; --- error ER_TRUNCATE_ILLEGAL_FK -truncate t3; - -drop table t4,t3,t2,t1; - -# -# Test that we can create a large (>1K) key -# Moved to innodb_16k, innodb_8k, and innodb_4k -# since each page size has its own maximum key length. -# - -# test the padding of BINARY types and collations (Bug #14189) - -create table t1 (s1 varbinary(2),primary key (s1)) engine=innodb; -create table t2 (s1 binary(2),primary key (s1)) engine=innodb; -create table t3 (s1 varchar(2) binary,primary key (s1)) engine=innodb; -create table t4 (s1 char(2) binary,primary key (s1)) engine=innodb; - -insert into t1 values (0x41),(0x4120),(0x4100); --- error ER_DUP_ENTRY -insert into t2 values (0x41),(0x4120),(0x4100); -insert into t2 values (0x41),(0x4120); --- error ER_DUP_ENTRY -insert into t3 values (0x41),(0x4120),(0x4100); -insert into t3 values (0x41),(0x4100); --- error ER_DUP_ENTRY -insert into t4 values (0x41),(0x4120),(0x4100); -insert into t4 values (0x41),(0x4100); -select hex(s1) from t1; -select hex(s1) from t2; -select hex(s1) from t3; -select hex(s1) from t4; -drop table t1,t2,t3,t4; - -create table t1 (a int primary key,s1 varbinary(3) not null unique) engine=innodb; -create table t2 (s1 binary(2) not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb; - -insert into t1 values(1,0x4100),(2,0x41),(3,0x4120),(4,0x42); --- error 1452 -insert into t2 values(0x42); -insert into t2 values(0x41); -select hex(s1) from t2; -update t1 set s1=0x123456 where a=2; -select hex(s1) from t2; --- error 1451 -update t1 set s1=0x12 where a=1; -SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; --- error 1451 -update t1 set s1=0x12345678 where a=1; -SET sql_mode = default; --- error 1451 -update t1 set s1=0x123457 where a=1; -update t1 set s1=0x1220 where a=1; -select hex(s1) from t2; -update t1 set s1=0x1200 where a=1; -select hex(s1) from t2; -update t1 set s1=0x4200 where a=1; -select hex(s1) from t2; --- error 1451 -delete from t1 where a=1; -delete from t1 where a=2; -update t2 set s1=0x4120; --- error 1451 -delete from t1; -delete from t1 where a!=3; -select a,hex(s1) from t1; -select hex(s1) from t2; - -drop table t2,t1; - -create table t1 (a int primary key,s1 varchar(2) binary not null unique) engine=innodb; -create table t2 (s1 char(2) binary not null, constraint c foreign key(s1) references t1(s1) on update cascade) engine=innodb; - -insert into t1 values(1,0x4100),(2,0x41); -insert into t2 values(0x41); -select hex(s1) from t2; -update t1 set s1=0x1234 where a=1; -select hex(s1) from t2; -update t1 set s1=0x12 where a=2; -select hex(s1) from t2; -delete from t1 where a=1; --- error 1451 -delete from t1 where a=2; -select a,hex(s1) from t1; -select hex(s1) from t2; - -drop table t2,t1; -# Ensure that <tablename>_ibfk_0 is not mistreated as a -# generated foreign key identifier. (Bug #16387) - -CREATE TABLE t1(a INT, PRIMARY KEY(a)) ENGINE=InnoDB; -CREATE TABLE t2(a INT) ENGINE=InnoDB; -ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1(a); -ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_1; -ALTER TABLE t2 ADD CONSTRAINT t2_ibfk_0 FOREIGN KEY (a) REFERENCES t1(a); -ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0; -SHOW CREATE TABLE t2; -DROP TABLE t2,t1; - -# -# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing -# -## the following cannot be tested after the introduction of metadata locks -## because the create trigger command blocks and waits for connection b to -## commit -## begin disabled_mdl -#connect (a,localhost,root,,); -#connect (b,localhost,root,,); -#connection a; -#create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb; -#insert into t1(a) values (1),(2),(3); -#commit; -#delimiter |; -## in 5.5+, this needs to be created before the UPDATE due to meta-data locking -#create trigger t1t before insert on t1 for each row begin set NEW.b = NEW.a * 10 + 5, NEW.c = NEW.a / 10; end | -#delimiter ;| -#connection b; -#set autocommit = 0; -#update t1 set b = 5 where a = 2; -#connection a; -#set autocommit = 0; -#connection a; -#insert into t1(a) values (10),(20),(30),(40),(50),(60),(70),(80),(90),(100), -#(11),(21),(31),(41),(51),(61),(71),(81),(91),(101), -#(12),(22),(32),(42),(52),(62),(72),(82),(92),(102), -#(13),(23),(33),(43),(53),(63),(73),(83),(93),(103), -#(14),(24),(34),(44),(54),(64),(74),(84),(94),(104); -#connection b; -#commit; -#connection a; -#commit; -#drop trigger t1t; -#drop table t1; -#disconnect a; -#disconnect b; -## -## Another trigger test -## -#connect (a,localhost,root,,); -#connect (b,localhost,root,,); -#connection a; -#create table t1(a int not null, b int, c int, d int, primary key(a)) engine=innodb; -#create table t2(a int not null, b int, c int, d int, primary key(a)) engine=innodb; -#create table t3(a int not null, b int, c int, d int, primary key(a)) engine=innodb; -#create table t4(a int not null, b int, c int, d int, primary key(a)) engine=innodb; -#create table t5(a int not null, b int, c int, d int, primary key(a)) engine=innodb; -#insert into t1(a) values (1),(2),(3); -#insert into t2(a) values (1),(2),(3); -#insert into t3(a) values (1),(2),(3); -#insert into t4(a) values (1),(2),(3); -#insert into t3(a) values (5),(7),(8); -#insert into t4(a) values (5),(7),(8); -#insert into t5(a) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12); -# -#delimiter |; -#create trigger t1t before insert on t1 for each row begin -# INSERT INTO t2 SET a = NEW.a; -#end | -# -#create trigger t2t before insert on t2 for each row begin -# DELETE FROM t3 WHERE a = NEW.a; -#end | -# -#create trigger t3t before delete on t3 for each row begin -# UPDATE t4 SET b = b + 1 WHERE a = OLD.a; -#end | -# -#create trigger t4t before update on t4 for each row begin -# UPDATE t5 SET b = b + 1 where a = NEW.a; -#end | -#delimiter ;| -#commit; -#set autocommit = 0; -#update t1 set b = b + 5 where a = 1; -#update t2 set b = b + 5 where a = 1; -#update t3 set b = b + 5 where a = 1; -#update t4 set b = b + 5 where a = 1; -#insert into t5(a) values(20); -#connection b; -#set autocommit = 0; -#insert into t1(a) values(7); -#insert into t2(a) values(8); -#delete from t2 where a = 3; -#update t4 set b = b + 1 where a = 3; -#commit; -#connection a; -#drop trigger t1t; -#drop trigger t2t; -#drop trigger t3t; -#drop trigger t4t; -#drop table t1, t2, t3, t4, t5; -#connection default; -#disconnect a; -#disconnect b; -## end disabled_mdl - -# -# Test that cascading updates leading to duplicate keys give the correct -# error message (bug #9680) -# - -CREATE TABLE t1 ( - field1 varchar(8) NOT NULL DEFAULT '', - field2 varchar(8) NOT NULL DEFAULT '', - PRIMARY KEY (field1, field2) -) ENGINE=InnoDB; - -CREATE TABLE t2 ( - field1 varchar(8) NOT NULL DEFAULT '' PRIMARY KEY, - FOREIGN KEY (field1) REFERENCES t1 (field1) - ON DELETE CASCADE ON UPDATE CASCADE -) ENGINE=InnoDB; - -INSERT INTO t1 VALUES ('old', 'somevalu'); -INSERT INTO t1 VALUES ('other', 'anyvalue'); - -INSERT INTO t2 VALUES ('old'); -INSERT INTO t2 VALUES ('other'); - ---error ER_FOREIGN_DUPLICATE_KEY_WITH_CHILD_INFO -UPDATE t1 SET field1 = 'other' WHERE field2 = 'somevalu'; - -DROP TABLE t2; -DROP TABLE t1; - -# -# Bug#18477 - MySQL/InnoDB Ignoring Foreign Keys in ALTER TABLE -# -create table t1 ( - c1 bigint not null, - c2 bigint not null, - primary key (c1), - unique key (c2) -) engine=innodb; -# -create table t2 ( - c1 bigint not null, - primary key (c1) -) engine=innodb; -# -alter table t1 add constraint c2_fk foreign key (c2) - references t2(c1) on delete cascade; -show create table t1; -# -alter table t1 drop foreign key c2_fk; -show create table t1; -# -drop table t1, t2; - -# -# Bug #14360: problem with intervals -# - -create table t1(a date) engine=innodb; -create table t2(a date, key(a)) engine=innodb; -insert into t1 values('2005-10-01'); -insert into t2 values('2005-10-01'); -select * from t1, t2 - where t2.a between t1.a - interval 2 day and t1.a + interval 2 day; -drop table t1, t2; - -create table t1 (id int not null, f_id int not null, f int not null, -primary key(f_id, id)) engine=innodb; -create table t2 (id int not null,s_id int not null,s varchar(200), -primary key(id)) engine=innodb; -INSERT INTO t1 VALUES (8, 1, 3); -INSERT INTO t1 VALUES (1, 2, 1); -INSERT INTO t2 VALUES (1, 0, ''); -INSERT INTO t2 VALUES (8, 1, ''); -commit; -DELETE ml.* FROM t1 AS ml LEFT JOIN t2 AS mm ON (mm.id=ml.id) -WHERE mm.id IS NULL; -select ml.* from t1 as ml left join t2 as mm on (mm.id=ml.id) -where mm.id is null lock in share mode; -drop table t1,t2; - -# -# Test case where X-locks on unused rows should be released in a -# update (because READ COMMITTED isolation level) -# - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3); -commit; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -update t1 set b = 5 where b = 1; -connection b; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -# -# X-lock to record (7,3) should be released in a update -# -select * from t1 where a = 7 and b = 3 for update; -connection a; -commit; -connection b; -commit; -drop table t1; -connection default; -disconnect a; -disconnect b; - -# -# Test case where no locks should be released (because we are not -# using READ COMMITTED isolation level) -# - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2); -commit; -set autocommit = 0; -select * from t1 lock in share mode; -update t1 set b = 5 where b = 1; -connection b; -set autocommit = 0; -# -# S-lock to records (2,2),(4,2), and (6,2) should not be released in a update -# ---error ER_LOCK_WAIT_TIMEOUT -select * from t1 where a = 2 and b = 2 for update; -# -# X-lock to record (1,1),(3,1),(5,1) should not be released in a update -# ---error ER_LOCK_WAIT_TIMEOUT -connection a; -commit; -connection b; -commit; -connection default; -disconnect a; -disconnect b; -drop table t1; - -# -# Consistent read should be used in following selects -# -# 1) INSERT INTO ... SELECT -# 2) UPDATE ... = ( SELECT ...) -# 3) CREATE ... SELECT - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values (1,2),(5,3),(4,2); -create table t2(d int not null, e int, primary key(d)) engine=innodb; -insert into t2 values (8,6),(12,1),(3,1); -commit; -set autocommit = 0; -select * from t2 for update; -connection b; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -insert into t1 select * from t2; -update t1 set b = (select e from t2 where a = d); -create table t3(d int not null, e int, primary key(d)) engine=innodb -select * from t2; -commit; -connection a; -commit; -connection default; -disconnect a; -disconnect b; -drop table t1, t2, t3; - -# -# Consistent read should not be used if -# -# (a) isolation level is serializable OR -# (b) select ... lock in share mode OR -# (c) select ... for update -# -# in following queries: -# -# 1) INSERT INTO ... SELECT -# 2) UPDATE ... = ( SELECT ...) -# 3) CREATE ... SELECT - -connect (a,localhost,root,,); -connect (b,localhost,root,,); -connect (c,localhost,root,,); -connect (d,localhost,root,,); -connect (e,localhost,root,,); -connect (f,localhost,root,,); -connect (g,localhost,root,,); -connect (h,localhost,root,,); -connect (i,localhost,root,,); -connect (j,localhost,root,,); -connection a; -create table t1(a int not null, b int, primary key(a)) engine=innodb; -insert into t1 values (1,2),(5,3),(4,2); -create table t2(a int not null, b int, primary key(a)) engine=innodb; -insert into t2 values (8,6),(12,1),(3,1); -create table t3(d int not null, b int, primary key(d)) engine=innodb; -insert into t3 values (8,6),(12,1),(3,1); -create table t5(a int not null, b int, primary key(a)) engine=innodb; -insert into t5 values (1,2),(5,3),(4,2); -create table t6(d int not null, e int, primary key(d)) engine=innodb; -insert into t6 values (8,6),(12,1),(3,1); -create table t8(a int not null, b int, primary key(a)) engine=innodb; -insert into t8 values (1,2),(5,3),(4,2); -create table t9(d int not null, e int, primary key(d)) engine=innodb; -insert into t9 values (8,6),(12,1),(3,1); -commit; -set autocommit = 0; -select * from t2 for update; -connection b; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ---send -insert into t1 select * from t2; -connection c; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ---send -update t3 set b = (select b from t2 where a = d); -connection d; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; ---send -create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2; -connection e; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ---send -insert into t5 (select * from t2 lock in share mode); -connection f; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ---send -update t6 set e = (select b from t2 where a = d lock in share mode); -connection g; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ---send -create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode; -connection h; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ---send -insert into t8 (select * from t2 for update); -connection i; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ---send -update t9 set e = (select b from t2 where a = d for update); -connection j; -SET binlog_format='MIXED'; -set autocommit = 0; -SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; ---send -create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update; - -connection b; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection c; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection d; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection e; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection f; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection g; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection h; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection i; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection j; ---error ER_LOCK_WAIT_TIMEOUT -reap; - -connection a; -commit; - -connection default; -disconnect a; -disconnect b; -disconnect c; -disconnect d; -disconnect e; -disconnect f; -disconnect g; -disconnect h; -disconnect i; -disconnect j; -drop table t1, t2, t3, t5, t6, t8, t9; - -# bug 18934, "InnoDB crashes when table uses column names like DB_ROW_ID" ---error ER_WRONG_COLUMN_NAME -CREATE TABLE t1 (DB_ROW_ID int) engine=innodb; - -# -# Bug #17152: Wrong result with BINARY comparison on aliased column -# - -CREATE TABLE t1 ( - a BIGINT(20) NOT NULL, - PRIMARY KEY (a) - ) ENGINE=INNODB DEFAULT CHARSET=UTF8; - -CREATE TABLE t2 ( - a BIGINT(20) NOT NULL, - b VARCHAR(128) NOT NULL, - c TEXT NOT NULL, - PRIMARY KEY (a,b), - KEY idx_t2_b_c (b,c(100)), - CONSTRAINT t_fk FOREIGN KEY (a) REFERENCES t1 (a) - ON DELETE CASCADE - ) ENGINE=INNODB DEFAULT CHARSET=UTF8; - -INSERT INTO t1 VALUES (1); -INSERT INTO t2 VALUES (1, 'bar', 'vbar'); -INSERT INTO t2 VALUES (1, 'BAR2', 'VBAR'); -INSERT INTO t2 VALUES (1, 'bar_bar', 'bibi'); -INSERT INTO t2 VALUES (1, 'customer_over', '1'); - -SELECT * FROM t2 WHERE b = 'customer_over'; -SELECT * FROM t2 WHERE BINARY b = 'customer_over'; -SELECT DISTINCT p0.a FROM t2 p0 WHERE p0.b = 'customer_over'; -/* Bang: Empty result set, above was expected: */ -SELECT DISTINCT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; -SELECT p0.a FROM t2 p0 WHERE BINARY p0.b = 'customer_over'; - -drop table t2, t1; - -# -# Test optimize on table with open transaction -# - -CREATE TABLE t1 ( a int ) ENGINE=innodb; -BEGIN; -INSERT INTO t1 VALUES (1); -OPTIMIZE TABLE t1; -DROP TABLE t1; - -# -# Bug #24741 (existing cascade clauses disappear when adding foreign keys) -# - -CREATE TABLE t1 (id int PRIMARY KEY, f int NOT NULL, INDEX(f)) ENGINE=InnoDB; - -CREATE TABLE t2 (id int PRIMARY KEY, f INT NOT NULL, - CONSTRAINT t2_t1 FOREIGN KEY (id) REFERENCES t1 (id) - ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB; - -ALTER TABLE t2 ADD FOREIGN KEY (f) REFERENCES t1 (f) ON -DELETE CASCADE ON UPDATE CASCADE; - -SHOW CREATE TABLE t2; -DROP TABLE t2, t1; - -# -# Bug #25927: Prevent ALTER TABLE ... MODIFY ... NOT NULL on columns -# for which there is a foreign key constraint ON ... SET NULL. -# - -CREATE TABLE t1 (a INT, INDEX(a)) ENGINE=InnoDB; -CREATE TABLE t2 (a INT, INDEX(a)) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1); -INSERT INTO t2 VALUES (1); -ALTER TABLE t2 ADD FOREIGN KEY (a) REFERENCES t1 (a) ON DELETE SET NULL; -# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on. ---error ER_FK_COLUMN_CANNOT_CHANGE -ALTER TABLE t2 MODIFY a INT NOT NULL; -DELETE FROM t1; -DROP TABLE t2,t1; - -# -# Bug #26835: table corruption after delete+insert -# - -CREATE TABLE t1 (a VARCHAR(5) COLLATE utf8_unicode_ci PRIMARY KEY) -ENGINE=InnoDB; -INSERT INTO t1 VALUES (0xEFBCA4EFBCA4EFBCA4); -DELETE FROM t1; -INSERT INTO t1 VALUES ('DDD'); -SELECT * FROM t1; -DROP TABLE t1; - -# -# Bug #23313 (AUTO_INCREMENT=# not reported back for InnoDB tables) -# Bug #21404 (AUTO_INCREMENT value reset when Adding FKEY (or ALTER?)) -# - -CREATE TABLE t1 (id int PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB -AUTO_INCREMENT=42; - -INSERT INTO t1 VALUES (0),(347),(0); -SELECT * FROM t1; - -SHOW CREATE TABLE t1; - -CREATE TABLE t2 (id int PRIMARY KEY) ENGINE=InnoDB; -INSERT INTO t2 VALUES(42),(347),(348); -ALTER TABLE t1 ADD CONSTRAINT t1_t2 FOREIGN KEY (id) REFERENCES t2(id); -SHOW CREATE TABLE t1; - -DROP TABLE t1,t2; - -# -# Bug #21101 (Prints wrong error message if max row size is too large) -# -SET innodb_strict_mode=ON; ---replace_result 8126 {checked_valid} 4030 {checked_valid} 1982 {checked_valid} ---error ER_TOO_BIG_ROWSIZE -CREATE TABLE t1 ( - c01 CHAR(255), c02 CHAR(255), c03 CHAR(255), c04 CHAR(255), - c05 CHAR(255), c06 CHAR(255), c07 CHAR(255), c08 CHAR(255), - c09 CHAR(255), c10 CHAR(255), c11 CHAR(255), c12 CHAR(255), - c13 CHAR(255), c14 CHAR(255), c15 CHAR(255), c16 CHAR(255), - c17 CHAR(255), c18 CHAR(255), c19 CHAR(255), c20 CHAR(255), - c21 CHAR(255), c22 CHAR(255), c23 CHAR(255), c24 CHAR(255), - c25 CHAR(255), c26 CHAR(255), c27 CHAR(255), c28 CHAR(255), - c29 CHAR(255), c30 CHAR(255), c31 CHAR(255), c32 CHAR(255) - ) ENGINE = InnoDB; -SET innodb_strict_mode=OFF; - -# -# Bug #31860 InnoDB assumes AUTOINC values can only be positive. -# -DROP TABLE IF EXISTS t1; -CREATE TABLE t1( - id BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY - ) ENGINE=InnoDB; -INSERT INTO t1 VALUES(-10); -SELECT * FROM t1; -# -# NOTE: The server really needs to be restarted at this point -# for the test to be useful. -# -# Without the fix InnoDB would trip over an assertion here. -INSERT INTO t1 VALUES(NULL); -# The next value should be 1 and not -9 or a -ve number -SELECT * FROM t1; -DROP TABLE t1; - -# -# Bug #21409 Incorrect result returned when in READ-COMMITTED with -# query_cache ON -# -CONNECT (c1,localhost,root,,); -CONNECT (c2,localhost,root,,); -CONNECTION c1; -SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; -SET AUTOCOMMIT=0; -DROP TABLE IF EXISTS t1, t2; -CREATE TABLE t1 ( a int ) ENGINE=InnoDB; -CREATE TABLE t2 LIKE t1; -SELECT * FROM t2; -CONNECTION c2; -SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; -SET AUTOCOMMIT=0; -INSERT INTO t1 VALUES (1); -COMMIT; -CONNECTION c1; -SELECT * FROM t1 WHERE a=1; -DISCONNECT c1; -DISCONNECT c2; -CONNECT (c1,localhost,root,,); -CONNECT (c2,localhost,root,,); -CONNECTION c1; -SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; -SET AUTOCOMMIT=0; -SELECT * FROM t2; -CONNECTION c2; -SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; -SET AUTOCOMMIT=0; -INSERT INTO t1 VALUES (2); -COMMIT; -CONNECTION c1; -# The result set below should be the same for both selects -SELECT * FROM t1 WHERE a=2; -SELECT * FROM t1 WHERE a=2; -DROP TABLE t1; -DROP TABLE t2; -DISCONNECT c1; -DISCONNECT c2; -CONNECTION default; - -# -# Bug #29157 UPDATE, changed rows incorrect -# -create table t1 (i int, j int) engine=innodb; -insert into t1 (i, j) values (1, 1), (2, 2); ---enable_info -update t1 set j = 2; ---disable_info -drop table t1; - -# -# Bug #32440 InnoDB free space info does not appear in SHOW TABLE STATUS or -# I_S -# -set @save_innodb_file_per_table= @@global.innodb_file_per_table; -set @@global.innodb_file_per_table=0; -create table t1 (id int) comment='this is a comment' engine=innodb; -ANALYZE TABLE t1; -select table_comment, data_free > 0 as data_free_is_set - from information_schema.tables - where table_schema='test' and table_name = 't1'; -drop table t1; -set @@global.innodb_file_per_table= @save_innodb_file_per_table; - -# -# Bug 34920 test -# -CONNECTION default; -CREATE TABLE t1 ( - c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, - c2 VARCHAR(128) NOT NULL, - PRIMARY KEY(c1) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100; - -CREATE TABLE t2 ( - c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, - c2 INT(10) UNSIGNED DEFAULT NULL, - PRIMARY KEY(c1) -) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200; - -ANALYZE TABLE t2; -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2'; -ALTER TABLE t2 ADD CONSTRAINT t1_t2_1 FOREIGN KEY(c1) REFERENCES t1(c1); -SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 't2'; -DROP TABLE t2; -DROP TABLE t1; -# End 34920 test -# -# Bug #29507 TRUNCATE shows to many rows effected -# -CONNECTION default; -CREATE TABLE t1 (c1 int default NULL, - c2 int default NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - ---enable_info -TRUNCATE TABLE t1; - -INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5); -TRUNCATE TABLE t1; - ---disable_info -DROP TABLE t1; -# -# Bug#35537 Innodb doesn't increment handler_update and handler_delete. -# --- disable_query_log --- disable_result_log - -CONNECT (c1,localhost,root,,); - -DROP TABLE IF EXISTS bug35537; -CREATE TABLE bug35537 ( - c1 int -) ENGINE=InnoDB; - -INSERT INTO bug35537 VALUES (1); - --- enable_result_log - -SHOW SESSION STATUS LIKE 'Handler_update%'; -SHOW SESSION STATUS LIKE 'Handler_delete%'; - -UPDATE bug35537 SET c1 = 2 WHERE c1 = 1; -DELETE FROM bug35537 WHERE c1 = 2; - -SHOW SESSION STATUS LIKE 'Handler_update%'; -SHOW SESSION STATUS LIKE 'Handler_delete%'; - -DROP TABLE bug35537; - -DISCONNECT c1; -CONNECTION default; - -SET GLOBAL innodb_thread_concurrency = @innodb_thread_concurrency_orig; |