diff options
Diffstat (limited to 'mysql-test/t/innodb.test')
| -rw-r--r-- | mysql-test/t/innodb.test | 804 |
1 files changed, 776 insertions, 28 deletions
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test index ee411a1bb37..832c4635815 100644 --- a/mysql-test/t/innodb.test +++ b/mysql-test/t/innodb.test @@ -129,6 +129,32 @@ insert into t1 values (6); -- error 1062 insert into t1 values (4); select n from t1; +set autocommit=0; +# +# savepoints +# +begin; +savepoint `my_savepoint`; +insert into t1 values (7); +savepoint `savept2`; +insert into t1 values (3); +select n from t1; +savepoint savept3; +rollback to savepoint savept2; +--error 1305 +rollback to savepoint savept3; +rollback to savepoint savept2; +release savepoint `my_savepoint`; +select n from t1; +-- error 1305 +rollback to savepoint `my_savepoint`; +--error 1305 +rollback to savepoint savept2; +insert into t1 values (8); +savepoint sv; +commit; +savepoint sv; +set autocommit=1; # nop rollback; drop table t1; @@ -415,6 +441,7 @@ set @a:=now(); CREATE TABLE t1 (a int not null, b timestamp not null, primary key (a)) engine=innodb; insert into t1 (a) values(1),(2),(3); select t1.a from t1 natural join t1 as t2 where t1.b >= @a order by t1.a; +select a from t1 natural join t1 as t2 where b >= @a order by a; update t1 set a=5 where a=1; select a from t1; drop table t1; @@ -560,7 +587,7 @@ insert into mysqltest.t3 values(1); commit; drop database mysqltest; # Don't check error message ---error 12,12 +--error 1049 show tables from mysqltest; # @@ -634,9 +661,9 @@ CREATE TABLE t1 ( cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', - last_mod_date timestamp(14) NOT NULL, + last_mod_date timestamp NOT NULL, last_mod_id smallint(6) NOT NULL default '0', - last_app_date timestamp(14) NOT NULL, + last_app_date timestamp NOT NULL, last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', @@ -653,9 +680,9 @@ CREATE TABLE t2 ( cname char(15) NOT NULL default '', carrier_id smallint(6) NOT NULL default '0', privacy tinyint(4) NOT NULL default '0', - last_mod_date timestamp(14) NOT NULL, + last_mod_date timestamp NOT NULL, last_mod_id smallint(6) NOT NULL default '0', - last_app_date timestamp(14) NOT NULL, + last_app_date timestamp NOT NULL, last_app_id smallint(6) default '-1', version smallint(6) NOT NULL default '0', assigned_scps int(11) default '0', @@ -951,11 +978,11 @@ create table `t2` (`id` int( 11 ) not null default '0',unique key `id` ( `id` ) insert into `t2`values ( 1 ) ; create table `t3` (`id` int( 11 ) not null default '0',key `id` ( `id` ) ,constraint `t2_id_fk` foreign key ( `id` ) references `t2` (`id` )) engine = innodb; insert into `t3`values ( 1 ) ; ---error 1217 +--error 1451 delete t3,t2,t1 from t1,t2,t3 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; ---error 1217 +--error 1451 update t1,t2,t3 set t3.id=5, t2.id=6, t1.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; ---error 1109 +--error 1054 update t3 set t3.id=7 where t1.id =1 and t2.id = t1.id and t3.id = t2.id; drop table t3,t2,t1; @@ -969,7 +996,7 @@ create table t1( foreign key(pid) references t1(id) on delete cascade) engine=innodb; insert into t1 values(0,0),(1,0),(2,1),(3,2),(4,3),(5,4),(6,5),(7,6), (8,7),(9,8),(10,9),(11,10),(12,11),(13,12),(14,13),(15,14); --- error 1217 +-- error 1451 delete from t1 where id=0; delete from t1 where id=15; delete from t1 where id=0; @@ -998,7 +1025,7 @@ CREATE TABLE t1 ( `id` int(10) unsigned NOT NULL auto_increment, `id_object` int(10) unsigned default '0', `id_version` int(10) unsigned NOT NULL default '1', - label varchar(100) NOT NULL default '', + `label` varchar(100) NOT NULL default '', `description` text, PRIMARY KEY (`id`), KEY `id_object` (`id_object`), @@ -1016,8 +1043,8 @@ CREATE TABLE t2 ( INSERT INTO t2 VALUES("3524", "1"),("3525", "1"),("1794", "4"),("102", "5"),("1822", "6"),("3382", "9"); -SELECT t2.id, t1.label FROM t2 INNER JOIN -(SELECT t1.id_object as id_object FROM t1 WHERE t1.label LIKE '%test%') AS lbl +SELECT t2.id, t1.`label` FROM t2 INNER JOIN +(SELECT t1.id_object as id_object FROM t1 WHERE t1.`label` LIKE '%test%') AS lbl ON (t2.id = lbl.id_object) INNER JOIN t1 ON (t2.id = t1.id_object); drop table t1,t2; @@ -1097,7 +1124,10 @@ show create table t2; drop table t2; # Test error handling ---replace_result \\ / $MYSQL_TEST_DIR . /var/master-data/ / + +# 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/ / t2.frm t2 --error 1005 create table t2 (id int(11) not null, id2 int(11) not null, constraint t1_id_fk foreign key (id2,id) references t1 (id)) engine = innodb; @@ -1143,7 +1173,6 @@ delete from t1; commit; show status like "binlog_cache_use"; show status like "binlog_cache_disk_use"; - drop table t1; # @@ -1240,45 +1269,492 @@ select min(a) from t1; select min(b) from t1 where a='8'; drop table t1; +# End of 4.1 tests + +# +# range optimizer problem +# + +create table t1 (x bigint unsigned not null primary key) engine=innodb; +insert into t1(x) values (0xfffffffffffffff0),(0xfffffffffffffff1); +select * from t1; +select count(*) from t1 where x>0; +select count(*) from t1 where x=0; +select count(*) from t1 where x<0; +select count(*) from t1 where x < -16; +select count(*) from t1 where x = -16; +explain select count(*) from t1 where x > -16; +select count(*) from t1 where x > -16; +select * from t1 where x > -16; +select count(*) from t1 where x = 18446744073709551601; +drop table t1; + + +# Test for testable InnoDB status variables. This test +# uses previous ones(pages_created, rows_deleted, ...). +show status like "Innodb_buffer_pool_pages_total"; +show status like "Innodb_page_size"; +show status like "Innodb_rows_deleted"; +show status like "Innodb_rows_inserted"; +show status like "Innodb_rows_updated"; + +# Test for row locks InnoDB status variables. +show status like "Innodb_row_lock_waits"; +show status like "Innodb_row_lock_current_waits"; +show status like "Innodb_row_lock_time"; +show status like "Innodb_row_lock_time_max"; +show status like "Innodb_row_lock_time_avg"; + +# Test for innodb_sync_spin_loops variable +show variables like "innodb_sync_spin_loops"; +set global innodb_sync_spin_loops=1000; +show variables like "innodb_sync_spin_loops"; +set global innodb_sync_spin_loops=0; +show variables like "innodb_sync_spin_loops"; +set global innodb_sync_spin_loops=20; +show variables like "innodb_sync_spin_loops"; + +# Test for innodb_thread_concurrency variable +show variables like "innodb_thread_concurrency"; +set global innodb_thread_concurrency=1001; +show variables like "innodb_thread_concurrency"; +set global innodb_thread_concurrency=0; +show variables like "innodb_thread_concurrency"; +set global innodb_thread_concurrency=16; +show variables like "innodb_thread_concurrency"; + +# Test for innodb_concurrency_tickets variable +show variables like "innodb_concurrency_tickets"; +set global innodb_concurrency_tickets=1000; +show variables like "innodb_concurrency_tickets"; +set global innodb_concurrency_tickets=0; +show variables like "innodb_concurrency_tickets"; +set global innodb_concurrency_tickets=500; +show variables like "innodb_concurrency_tickets"; + +# 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 test_checksum(a int not null) engine=innodb DEFAULT CHARSET=latin1; -insert into test_checksum values (1),(2); +create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; +insert into t1 values (1),(2); set autocommit=0; -checksum table test_checksum; +checksum table t1; connection b; -insert into test_checksum values(3); +insert into t1 values(3); connection a; # # Here checksum should not see insert # -checksum table test_checksum; +checksum table t1; connection a; commit; -checksum table test_checksum; +checksum table t1; commit; -drop table test_checksum; +drop table t1; # # autocommit = 1 # connection a; -create table test_checksum(a int not null) engine=innodb DEFAULT CHARSET=latin1; -insert into test_checksum values (1),(2); +create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1; +insert into t1 values (1),(2); set autocommit=1; -checksum table test_checksum; +checksum table t1; connection b; set autocommit=1; -insert into test_checksum values(3); +insert into t1 values(3); connection a; # # Here checksum sees insert # -checksum table test_checksum; -drop table test_checksum; +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'); +update t1 set filler = 'boo' where a = 1; +update t2 set filler ='email' where a = 4; +select a,hex(b),hex(c),filler from t1 order by filler; +select a,hex(b),hex(c),filler from t2 order by filler; +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'); +update t1 set filler = 'boo' where a = 1; +update t2 set filler ='email' where a = 4; +select a,hex(b),hex(c),filler from t1 order by filler; +select a,hex(b),hex(c),filler from t2 order by filler; +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'); +update t1 set filler = 'boo' where a = 1; +update t2 set filler ='email' where a = 4; +select a,hex(b),hex(c),filler from t1 order by filler; +select a,hex(b),hex(c),filler from t2 order by filler; +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'); +update t1 set filler = 'boo' where a = 1; +update t2 set filler ='email' where a = 4; +select a,hex(b),hex(c),filler from t1 order by filler; +select a,hex(b),hex(c),filler from t2 order by filler; +drop table t1; +drop table t2; +commit; # tests for bugs #9802 and #13778 @@ -1286,6 +1762,7 @@ drop table test_checksum; set foreign_key_checks=0; create table t2 (a int primary key, b int, foreign key (b) references t1(a)) engine = innodb; +--replace_result $MYSQLTEST_VARDIR . master-data/ '' -- error 1005 create table t1(a char(10) primary key, b varchar(20)) engine = innodb; set foreign_key_checks=1; @@ -1296,6 +1773,7 @@ drop table t2; set foreign_key_checks=0; create table t1(a varchar(10) primary key) engine = innodb DEFAULT CHARSET=latin1; +--replace_result $MYSQLTEST_VARDIR . master-data/ '' -- error 1005 create table t2 (a varchar(10), foreign key (a) references t1(a)) engine = innodb DEFAULT CHARSET=utf8; set foreign_key_checks=1; @@ -1325,11 +1803,192 @@ drop table t2,t1; 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; +--replace_result $MYSQLTEST_VARDIR . 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 1451 +truncate t1; +-- error 1451 +truncate t3; +truncate t2; +truncate t4; +truncate t1; +truncate t3; + +drop table t4,t3,t2,t1; + + +# +# 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; + + +# 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 1062 +insert into t2 values (0x41),(0x4120),(0x4100); +insert into t2 values (0x41),(0x4120); +-- error 1062 +insert into t3 values (0x41),(0x4120),(0x4100); +insert into t3 values (0x41),(0x4100); +-- error 1062 +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; +-- error 1451 +update t1 set s1=0x12345678 where a=1; +-- 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; +# +# Test cases for bug #15308 Problem of Order with Enum Column in Primary Key +# +CREATE TABLE t1 ( + ind enum('0','1','2') NOT NULL default '0', + string1 varchar(250) NOT NULL, + PRIMARY KEY (ind) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t2 ( + ind enum('0','1','2') NOT NULL default '0', + string1 varchar(250) NOT NULL, + PRIMARY KEY (ind) +) ENGINE=InnoDB DEFAULT CHARSET=ucs2; + +INSERT INTO t1 VALUES ('1', ''),('2', ''); +INSERT INTO t2 VALUES ('1', ''),('2', ''); +SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; +SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; +drop table t1,t2; + +CREATE TABLE t1 ( + ind set('0','1','2') NOT NULL default '0', + string1 varchar(250) NOT NULL, + PRIMARY KEY (ind) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t2 ( + ind set('0','1','2') NOT NULL default '0', + string1 varchar(250) NOT NULL, + PRIMARY KEY (ind) +) ENGINE=InnoDB DEFAULT CHARSET=ucs2; + +INSERT INTO t1 VALUES ('1', ''),('2', ''); +INSERT INTO t2 VALUES ('1', ''),('2', ''); +SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; +SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; +drop table t1,t2; + +CREATE TABLE t1 ( + ind bit not null, + string1 varchar(250) NOT NULL, + PRIMARY KEY (ind) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +CREATE TABLE t2 ( + ind bit not null, + string1 varchar(250) NOT NULL, + PRIMARY KEY (ind) +) ENGINE=InnoDB DEFAULT CHARSET=ucs2; +insert into t1 values(0,''),(1,''); +insert into t2 values(0,''),(1,''); +select hex(ind),hex(string1) from t1 order by string1; +select hex(ind),hex(string1) from t2 order by string1; +drop table t1,t2; + # tests for bug #14056 Column prefix index on UTF-8 primary key column causes 'Can't find record..' create table t2 ( @@ -1378,4 +2037,93 @@ ALTER TABLE t2 DROP FOREIGN KEY t2_ibfk_0; SHOW CREATE TABLE t2; DROP TABLE t2,t1; -# End of 4.1 tests +# +# Test case for bug #16229: MySQL/InnoDB uses full explicit table locks in trigger processing +# + +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; +connection b; +set autocommit = 0; +update t1 set b = 5 where a = 2; +connection a; +delimiter |; +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 ;| +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; +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; |