summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/r/create.result23
-rw-r--r--mysql-test/r/heap.result2
-rw-r--r--mysql-test/r/innodb.result112
-rw-r--r--mysql-test/r/multi_update.result64
-rw-r--r--mysql-test/r/rpl_relayspace.result13
-rw-r--r--mysql-test/t/create.test15
-rw-r--r--mysql-test/t/heap.test2
-rw-r--r--mysql-test/t/innodb.test73
-rw-r--r--mysql-test/t/multi_update.test28
-rw-r--r--mysql-test/t/rpl_relayspace-slave.opt1
-rw-r--r--mysql-test/t/rpl_relayspace.test33
11 files changed, 362 insertions, 4 deletions
diff --git a/mysql-test/r/create.result b/mysql-test/r/create.result
index e061a78304f..136b7272c1d 100644
--- a/mysql-test/r/create.result
+++ b/mysql-test/r/create.result
@@ -253,3 +253,26 @@ Incorrect table name 'a/a'
drop table t1, t2, t3;
drop table t3;
drop database test_$1;
+SET SESSION table_type="heap";
+SELECT @@table_type;
+@@table_type
+HEAP
+CREATE TABLE t1 (a int not null);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0'
+) TYPE=HEAP
+drop table t1;
+SET SESSION table_type="gemini";
+SELECT @@table_type;
+@@table_type
+GEMINI
+CREATE TABLE t1 (a int not null);
+show create table t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `a` int(11) NOT NULL default '0'
+) TYPE=MyISAM
+SET SESSION table_type=default;
+drop table t1;
diff --git a/mysql-test/r/heap.result b/mysql-test/r/heap.result
index ffd62ceabb6..76022b66100 100644
--- a/mysql-test/r/heap.result
+++ b/mysql-test/r/heap.result
@@ -23,7 +23,7 @@ a b
4 6
alter table t1 add c int not null, add key (c,a);
drop table t1;
-create table t1 (a int not null,b int not null, primary key (a)) type=heap comment="testing heaps";
+create table t1 (a int not null,b int not null, primary key (a)) type=memory comment="testing heaps";
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a > 0;
select * from t1;
diff --git a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
index fd80ef785ea..df33af0709c 100644
--- a/mysql-test/r/innodb.result
+++ b/mysql-test/r/innodb.result
@@ -1,4 +1,4 @@
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3;
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb;
insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'), (2, 'Erik'), (3, 'Sasha'), (3, 'Jeremy'), (4, 'Matt');
select id, code, name from t1 order by id;
@@ -1093,3 +1093,113 @@ SELECT * from t1;
id
3
DROP TABLE t1,t2;
+set autocommit=0;
+CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) TYPE=InnoDB;
+CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) TYPE=InnoDB;
+CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) TYPE=InnoDB;
+INSERT INTO t3 VALUES("my-test-1", "my-test-2");
+COMMIT;
+INSERT INTO t1 VALUES("this-key", "will disappear");
+INSERT INTO t2 VALUES("this-key", "will also disappear");
+DELETE FROM t3 WHERE id1="my-test-1";
+SELECT * FROM t1;
+id value
+this-key will disappear
+SELECT * FROM t2;
+id value
+this-key will also disappear
+SELECT * FROM t3;
+id1 id2
+ROLLBACK;
+SELECT * FROM t1;
+id value
+SELECT * FROM t2;
+id value
+SELECT * FROM t3;
+id1 id2
+my-test-1 my-test-2
+SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
+id1 id2
+my-test-1 my-test-2
+COMMIT;
+set autocommit=1;
+DROP TABLE t1,t2,t3;
+CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) type=innodb;
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
+SELECT * from t1;
+a b
+1 1
+102 2
+103 3
+4 4
+5 5
+6 6
+7 7
+8 8
+9 9
+drop table t1;
+CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb;
+CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb;
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+a b
+101 1
+102 2
+103 3
+104 4
+105 5
+106 6
+107 7
+108 8
+109 9
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+a b
+201 1
+102 2
+103 3
+104 4
+105 5
+106 6
+107 7
+108 8
+109 9
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+a b
+201 1
+103 3
+104 4
+105 5
+106 6
+107 7
+108 8
+109 9
+102 12
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+a b
+201 1
+103 5
+104 6
+106 6
+105 7
+107 7
+108 8
+109 9
+102 12
+select * from t2;
+a b
+1 5
+2 5
+3 5
+4 5
+5 5
+6 5
+7 5
+8 5
+9 5
+drop table t1,t2;
diff --git a/mysql-test/r/multi_update.result b/mysql-test/r/multi_update.result
index 4dbb3c8adb1..da1c429df63 100644
--- a/mysql-test/r/multi_update.result
+++ b/mysql-test/r/multi_update.result
@@ -260,3 +260,67 @@ INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
drop table t1,t2,t3;
+CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
+CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+a b
+101 1
+102 2
+103 3
+104 4
+105 5
+106 6
+107 7
+108 8
+109 9
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+a b
+201 1
+102 2
+103 3
+104 4
+105 5
+106 6
+107 7
+108 8
+109 9
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+a b
+201 1
+102 12
+103 3
+104 4
+105 5
+106 6
+107 7
+108 8
+109 9
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+a b
+201 1
+102 12
+103 5
+104 6
+105 7
+106 6
+107 7
+108 8
+109 9
+select * from t2;
+a b
+1 3
+2 3
+3 3
+4 3
+5 3
+6 3
+7 3
+8 3
+9 3
+drop table t1,t2;
diff --git a/mysql-test/r/rpl_relayspace.result b/mysql-test/r/rpl_relayspace.result
new file mode 100644
index 00000000000..5e552ef7400
--- /dev/null
+++ b/mysql-test/r/rpl_relayspace.result
@@ -0,0 +1,13 @@
+slave stop;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+slave start;
+stop slave;
+create table t1 (a int);
+reset slave;
+start slave;
+select master_pos_wait('master-bin.001',5000,45)=-1;
+master_pos_wait('master-bin.001',5000,45)=-1
+0
diff --git a/mysql-test/t/create.test b/mysql-test/t/create.test
index 98d76bf2883..cda9307804b 100644
--- a/mysql-test/t/create.test
+++ b/mysql-test/t/create.test
@@ -167,3 +167,18 @@ drop table t1, t2, t3;
drop table t3;
drop database test_$1;
+#
+# Test default table type
+#
+SET SESSION table_type="heap";
+SELECT @@table_type;
+CREATE TABLE t1 (a int not null);
+show create table t1;
+drop table t1;
+# Test what happens when using a non existing table type
+SET SESSION table_type="gemini";
+SELECT @@table_type;
+CREATE TABLE t1 (a int not null);
+show create table t1;
+SET SESSION table_type=default;
+drop table t1;
diff --git a/mysql-test/t/heap.test b/mysql-test/t/heap.test
index cd5dbd5afbe..22d9e57a574 100644
--- a/mysql-test/t/heap.test
+++ b/mysql-test/t/heap.test
@@ -20,7 +20,7 @@ select * from t1;
alter table t1 add c int not null, add key (c,a);
drop table t1;
-create table t1 (a int not null,b int not null, primary key (a)) type=heap comment="testing heaps";
+create table t1 (a int not null,b int not null, primary key (a)) type=memory comment="testing heaps";
insert into t1 values(1,1),(2,2),(3,3),(4,4);
delete from t1 where a > 0;
select * from t1;
diff --git a/mysql-test/t/innodb.test b/mysql-test/t/innodb.test
index 95a690e56b7..bfaeee78f8e 100644
--- a/mysql-test/t/innodb.test
+++ b/mysql-test/t/innodb.test
@@ -5,7 +5,7 @@
#
--disable_warnings
-drop table if exists t1,t2;
+drop table if exists t1,t2,t3;
--enable_warnings
create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) type=innodb;
@@ -728,3 +728,74 @@ SELECT * from t1;
UPDATE t1,t2 SET t1.id=t1.id+1 where t1.id!=t2.id;
SELECT * from t1;
DROP TABLE t1,t2;
+
+#
+# Test of range_optimizer
+#
+
+set autocommit=0;
+
+CREATE TABLE t1 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) TYPE=InnoDB;
+
+CREATE TABLE t2 (id CHAR(15) NOT NULL, value CHAR(40) NOT NULL, PRIMARY KEY(id)) TYPE=InnoDB;
+
+CREATE TABLE t3 (id1 CHAR(15) NOT NULL, id2 CHAR(15) NOT NULL, PRIMARY KEY(id1, id2)) TYPE=InnoDB;
+
+INSERT INTO t3 VALUES("my-test-1", "my-test-2");
+COMMIT;
+
+INSERT INTO t1 VALUES("this-key", "will disappear");
+INSERT INTO t2 VALUES("this-key", "will also disappear");
+DELETE FROM t3 WHERE id1="my-test-1";
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+ROLLBACK;
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+SELECT * FROM t3;
+SELECT * FROM t3 WHERE id1="my-test-1" LOCK IN SHARE MODE;
+COMMIT;
+set autocommit=1;
+DROP TABLE t1,t2,t3;
+
+#
+# Check update with conflicting key
+#
+
+CREATE TABLE t1 (a int not null primary key, b int not null, unique (b)) type=innodb;
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+# We need the a < 1000 test here to quard against the halloween problems
+UPDATE t1 set a=a+100 where b between 2 and 3 and a < 1000;
+SELECT * from t1;
+drop table t1;
+
+#
+# Test multi update with different join methods
+#
+
+CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) type=innodb;
+CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) type=innodb;
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+
+# Full join, without key
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+
+# unique key
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+
+# ref key
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+
+# Range key (in t1)
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+select * from t2;
+
+drop table t1,t2;
diff --git a/mysql-test/t/multi_update.test b/mysql-test/t/multi_update.test
index 6156da82ec0..390dd58d806 100644
--- a/mysql-test/t/multi_update.test
+++ b/mysql-test/t/multi_update.test
@@ -232,3 +232,31 @@ INSERT INTO t3 VALUES (1,'jedan'),(2,'dva');
update t1,t2 set t1.naziv="aaaa" where t1.broj=t2.broj;
update t1,t2,t3 set t1.naziv="bbbb", t2.naziv="aaaa" where t1.broj=t2.broj and t2.broj=t3.broj;
drop table t1,t2,t3;
+
+#
+# Test multi update with different join methods
+#
+
+CREATE TABLE t1 (a int not null primary key, b int not null, key (b));
+CREATE TABLE t2 (a int not null primary key, b int not null, key (b));
+INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+INSERT INTO t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
+
+# Full join, without key
+update t1,t2 set t1.a=t1.a+100;
+select * from t1;
+
+# unique key
+update t1,t2 set t1.a=t1.a+100 where t1.a=101;
+select * from t1;
+
+# ref key
+update t1,t2 set t1.b=t1.b+10 where t1.b=2;
+select * from t1;
+
+# Range key (in t1)
+update t1,t2 set t1.b=t1.b+2,t2.b=t1.b where t1.b between 3 and 5;
+select * from t1;
+select * from t2;
+
+drop table t1,t2;
diff --git a/mysql-test/t/rpl_relayspace-slave.opt b/mysql-test/t/rpl_relayspace-slave.opt
new file mode 100644
index 00000000000..9365a2a0a26
--- /dev/null
+++ b/mysql-test/t/rpl_relayspace-slave.opt
@@ -0,0 +1 @@
+ -O relay_log_space_limit=1024 \ No newline at end of file
diff --git a/mysql-test/t/rpl_relayspace.test b/mysql-test/t/rpl_relayspace.test
new file mode 100644
index 00000000000..8d4f01339c7
--- /dev/null
+++ b/mysql-test/t/rpl_relayspace.test
@@ -0,0 +1,33 @@
+# The slave is started with relay_log_space_limit=1024 bytes,
+# to force the deadlock
+
+source include/master-slave.inc;
+connection slave;
+stop slave;
+connection master;
+create table t1 (a int);
+let $1=200;
+disable_query_log;
+while ($1)
+{
+# eval means expand $ expressions
+ eval insert into t1 values( $1 );
+ dec $1;
+}
+# This will generate one 10kB master's binlog
+enable_query_log;
+save_master_pos;
+connection slave;
+reset slave;
+start slave;
+# The I/O thread stops filling the relay log when
+# it's 1kB. And the SQL thread cannot purge this relay log
+# as purge is done only when the SQL thread switches to another
+# relay log, which does not exist here.
+# So we should have a deadlock.
+# if it is not resolved automatically we'll detect
+# it with master_pos_wait that waits for farther than 1kB;
+# it will timeout after 45 seconds;
+# also the slave will probably not cooperate to shutdown
+# (as 2 threads are locked)
+select master_pos_wait('master-bin.001',5000,45)=-1;
View Lorry Controller Status