~drizzle-trunk/drizzle/development : contents of tests/r/innodb

~drizzle-trunk/drizzle/development : (revision 146)
set global innodb_support_xa=default;
set session innodb_support_xa=default;
SET SESSION STORAGE_ENGINE = InnoDB;
drop table if exists t1,t2,t3,t1m,t1i,t2m,t2i,t4;
drop procedure if exists p1;
create table t1 (
c_id int(11) not null default '0',
org_id int(11) default null,
unique key contacts$c_id (c_id),
key contacts$org_id (org_id)
);
insert into t1 values
(2,null),(120,null),(141,null),(218,7), (128,1),
(151,2),(234,2),(236,2),(243,2),(255,2),(259,2),(232,3),(235,3),(238,3),
(246,3),(253,3),(269,3),(285,3),(291,3),(293,3),(131,4),(230,4),(231,4);
create table t2 (
slai_id int(11) not null default '0',
owner_tbl int(11) default null,
owner_id int(11) default null,
sla_id int(11) default null,
inc_web int(11) default null,
inc_email int(11) default null,
inc_chat int(11) default null,
inc_csr int(11) default null,
inc_total int(11) default null,
time_billed int(11) default null,
activedate timestamp null default null,
expiredate timestamp null default null,
state int(11) default null,
sla_set int(11) default null,
unique key t2$slai_id (slai_id),
key t2$owner_id (owner_id),
key t2$sla_id (sla_id)
);
insert into t2(slai_id, owner_tbl, owner_id, sla_id) values
(1,3,1,1), (3,3,10,2), (4,3,3,6), (5,3,2,5), (6,3,8,3), (7,3,9,7),
(8,3,6,8), (9,3,4,9), (10,3,5,10), (11,3,11,11), (12,3,7,12);
flush tables;
select si.slai_id
from t1 c join t2 si on
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
( si.owner_tbl = 2 and si.owner_id = c.c_id))
where
c.c_id = 218 and expiredate is null;
slai_id
12
select * from t1 where org_id is null;
c_id	org_id
2	NULL
120	NULL
141	NULL
select si.slai_id
from t1 c join t2 si on
((si.owner_tbl = 3 and si.owner_id = c.org_id) or
( si.owner_tbl = 2 and si.owner_id = c.c_id))
where
c.c_id = 218 and expiredate is null;
slai_id
12
drop table t1, t2;
CREATE TABLE t1 (a int, b int, KEY b (b));
CREATE TABLE t2 (a int, b int, PRIMARY KEY  (a,b));
CREATE TABLE t3 (a int, b int, c int, PRIMARY KEY  (a),
UNIQUE KEY b (b,c), KEY a (a,b,c));
INSERT INTO t1 VALUES (1, 1);
INSERT INTO t1 SELECT a + 1, b + 1 FROM t1;
INSERT INTO t1 SELECT a + 2, b + 2 FROM t1;
INSERT INTO t2 VALUES (1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7),(1,8);
INSERT INTO t2 SELECT a + 1, b FROM t2;
DELETE FROM t2 WHERE a = 1 AND b < 2;
INSERT INTO t3 VALUES (1,1,1),(2,1,2);
INSERT INTO t3 SELECT a + 2, a + 2, 3 FROM t3;
INSERT INTO t3 SELECT a + 4, a + 4, 3 FROM t3;
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 2;
b	a
1	1
2	2
SELECT STRAIGHT_JOIN SQL_NO_CACHE t1.b, t1.a FROM t1, t3, t2 WHERE
t3.a = t2.a AND t2.b = t1.a AND t3.b = 1 AND t3.c IN (1, 2)
ORDER BY t1.b LIMIT 5;
b	a
1	1
2	2
2	2
3	3
3	3
DROP TABLE t1, t2, t3;
CREATE TABLE `t1` (`id1` INT) ;
INSERT INTO `t1` (`id1`) VALUES (1),(5),(2);
CREATE TABLE `t2` (
`id1` INT,
`id2` INT NOT NULL,
`id3` INT,
`id4` INT NOT NULL,
UNIQUE (`id2`,`id4`),
KEY (`id1`)
);
INSERT INTO `t2`(`id1`,`id2`,`id3`,`id4`) VALUES
(1,1,1,0),
(1,1,2,1),
(5,1,2,2),
(6,1,2,3),
(1,2,2,2),
(1,2,1,1);
SELECT `id1` FROM `t1` WHERE `id1` NOT IN (SELECT `id1` FROM `t2` WHERE `id2` = 1 AND `id3` = 2);
id1
2
DROP TABLE t1, t2;
create table t1 (c1 int) engine=innodb;
handler t1 open;
handler t1 read first;
c1
Before and after comparison
0
drop table t1;
CREATE TABLE t1(c1 TEXT, UNIQUE (c1(1)), cnt INT DEFAULT 1)
ENGINE=INNODB CHARACTER SET UTF8;
INSERT INTO t1 (c1) VALUES ('1a');
SELECT * FROM t1;
c1	cnt
1a	1
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
SELECT * FROM t1;
c1	cnt
1a	2
DROP TABLE t1;
CREATE TABLE t1(c1 VARCHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
ENGINE=INNODB CHARACTER SET UTF8;
INSERT INTO t1 (c1) VALUES ('1a');
SELECT * FROM t1;
c1	cnt
1a	1
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
SELECT * FROM t1;
c1	cnt
1a	2
DROP TABLE t1;
CREATE TABLE t1(c1 CHAR(2), UNIQUE (c1(1)), cnt INT DEFAULT 1)
ENGINE=INNODB CHARACTER SET UTF8;
INSERT INTO t1 (c1) VALUES ('1a');
SELECT * FROM t1;
c1	cnt
1a	1
INSERT INTO t1 (c1) VALUES ('1b') ON DUPLICATE KEY UPDATE cnt=cnt+1;
SELECT * FROM t1;
c1	cnt
1a	2
DROP TABLE t1;
CREATE TABLE t1 (
a1 decimal(10,0) DEFAULT NULL,
a2 blob,
a3 time DEFAULT NULL,
a4 blob,
a5 char(175) DEFAULT NULL,
a6 timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
a7 tinyblob,
INDEX idx (a6,a7(239),a5)
) ENGINE=InnoDB;
EXPLAIN SELECT a4 FROM t1 WHERE
a6=NULL AND
a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
EXPLAIN SELECT t1.a4 FROM t1, t1 t WHERE
t.a6=t.a6 AND t1.a6=NULL AND
t1.a4='UNcT5pIde4I6c2SheTo4gt92OV1jgJCVkXmzyf325R1DwLURkbYHwhydANIZMbKTgdcR5xS';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const tables
DROP TABLE t1;
create table t1m (a int) engine = MEMORY;
create table t1i (a int);
create table t2m (a int) engine = MEMORY;
create table t2i (a int);
insert into t2m values (5);
insert into t2i values (5);
select min(a) from t1i;
min(a)
NULL
select min(7) from t1i;
min(7)
NULL
select min(7) from DUAL;
min(7)
7
explain select min(7) from t2i join t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
select min(7) from t2i join t1i;
min(7)
NULL
select max(a) from t1i;
max(a)
NULL
select max(7) from t1i;
max(7)
NULL
select max(7) from DUAL;
max(7)
7
explain select max(7) from t2i join t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	Using join buffer
select max(7) from t2i join t1i;
max(7)
NULL
select 1, min(a) from t1i where a=99;
1	min(a)
1	NULL
select 1, min(a) from t1i where 1=99;
1	min(a)
1	NULL
select 1, min(1) from t1i where a=99;
1	min(1)
1	NULL
select 1, min(1) from t1i where 1=99;
1	min(1)
1	NULL
select 1, max(a) from t1i where a=99;
1	max(a)
1	NULL
select 1, max(a) from t1i where 1=99;
1	max(a)
1	NULL
select 1, max(1) from t1i where a=99;
1	max(1)
1	NULL
select 1, max(1) from t1i where 1=99;
1	max(1)
1	NULL
explain select count(*), min(7), max(7) from t1m, t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t1m, t1i;
count(*)	min(7)	max(7)
0	NULL	NULL
explain select count(*), min(7), max(7) from t1m, t2i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1m	system	NULL	NULL	NULL	NULL	0	const row not found
1	SIMPLE	t2i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t1m, t2i;
count(*)	min(7)	max(7)
0	NULL	NULL
explain select count(*), min(7), max(7) from t2m, t1i;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2m	system	NULL	NULL	NULL	NULL	1	
1	SIMPLE	t1i	ALL	NULL	NULL	NULL	NULL	1	
select count(*), min(7), max(7) from t2m, t1i;
count(*)	min(7)	max(7)
0	NULL	NULL
drop table t1m, t1i, t2m, t2i;
create table t1 (
a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
) ENGINE = MEMORY;
insert into t1 (a1, a2, b, c, d) values
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4'),
('a','a','a','a111','xy1'),('a','a','a','b111','xy2'),('a','a','a','c111','xy3'),('a','a','a','d111','xy4'),
('a','a','b','e112','xy1'),('a','a','b','f112','xy2'),('a','a','b','g112','xy3'),('a','a','b','h112','xy4'),
('a','b','a','i121','xy1'),('a','b','a','j121','xy2'),('a','b','a','k121','xy3'),('a','b','a','l121','xy4'),
('a','b','b','m122','xy1'),('a','b','b','n122','xy2'),('a','b','b','o122','xy3'),('a','b','b','p122','xy4'),
('b','a','a','a211','xy1'),('b','a','a','b211','xy2'),('b','a','a','c211','xy3'),('b','a','a','d211','xy4'),
('b','a','b','e212','xy1'),('b','a','b','f212','xy2'),('b','a','b','g212','xy3'),('b','a','b','h212','xy4'),
('b','b','a','i221','xy1'),('b','b','a','j221','xy2'),('b','b','a','k221','xy3'),('b','b','a','l221','xy4'),
('b','b','b','m222','xy1'),('b','b','b','n222','xy2'),('b','b','b','o222','xy3'),('b','b','b','p222','xy4'),
('c','a','a','a311','xy1'),('c','a','a','b311','xy2'),('c','a','a','c311','xy3'),('c','a','a','d311','xy4'),
('c','a','b','e312','xy1'),('c','a','b','f312','xy2'),('c','a','b','g312','xy3'),('c','a','b','h312','xy4'),
('c','b','a','i321','xy1'),('c','b','a','j321','xy2'),('c','b','a','k321','xy3'),('c','b','a','l321','xy4'),
('c','b','b','m322','xy1'),('c','b','b','n322','xy2'),('c','b','b','o322','xy3'),('c','b','b','p322','xy4'),
('d','a','a','a411','xy1'),('d','a','a','b411','xy2'),('d','a','a','c411','xy3'),('d','a','a','d411','xy4'),
('d','a','b','e412','xy1'),('d','a','b','f412','xy2'),('d','a','b','g412','xy3'),('d','a','b','h412','xy4'),
('d','b','a','i421','xy1'),('d','b','a','j421','xy2'),('d','b','a','k421','xy3'),('d','b','a','l421','xy4'),
('d','b','b','m422','xy1'),('d','b','b','n422','xy2'),('d','b','b','o422','xy3'),('d','b','b','p422','xy4');
create table t4 (
pk_col int auto_increment primary key, a1 char(64), a2 char(64), b char(16), c char(16) not null, d char(16), dummy char(64) default ' '
);
insert into t4 (a1, a2, b, c, d, dummy) select * from t1;
create index idx12672_0 on t4 (a1);
create index idx12672_1 on t4 (a1,a2,b,c);
create index idx12672_2 on t4 (a1,a2,b);
analyze table t4;
Table	Op	Msg_type	Msg_text
test.t4	analyze	status	OK
select distinct a1 from t4 where pk_col not in (1,2,3,4);
a1
a
b
c
d
drop table t1,t4;
DROP TABLE IF EXISTS t2, t1;
CREATE TABLE t1 (i INT NOT NULL PRIMARY KEY) ENGINE= InnoDB;
CREATE TABLE t2 (
i INT NOT NULL,
FOREIGN KEY (i) REFERENCES t1 (i) ON DELETE NO ACTION
) ENGINE= InnoDB;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
DELETE IGNORE FROM t1 WHERE i = 1;
Warnings:
Error	1451	Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`i`) REFERENCES `t1` (`i`) ON DELETE NO ACTION)
SELECT * FROM t1, t2;
i	i
1	1
DROP TABLE t2, t1;
End of 4.1 tests.
create table t1 (
a varchar(30), b varchar(30), primary key(a), key(b)
);
select distinct a from t1;
a
drop table t1;
create table t1(a int, key(a));
insert into t1 values(1);
select a, count(a) from t1 group by a with rollup;
a	count(a)
1	1
NULL	1
drop table t1;
create table t1 (f1 int, f2 char(1), primary key(f1,f2));
insert into t1 values ( 1,"e"),(2,"a"),( 3,"c"),(4,"d");
alter table t1 drop primary key, add primary key (f2, f1);
explain select distinct f1 a, f1 b from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	5	NULL	4	Using index; Using temporary
explain select distinct f1, f2 from t1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	NULL	PRIMARY	5	NULL	3	Using index for group-by; Using temporary
drop table t1;
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20),
INDEX (name));
CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11));
ALTER TABLE t2 ADD FOREIGN KEY (fkey) REFERENCES t2(id);
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B');
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3);
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	PRIMARY,name	PRIMARY	4	NULL	3	Using where
1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using index
EXPLAIN
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
WHERE t1.name LIKE 'A%' OR FALSE;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	5	
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
DROP TABLE t1,t2;
CREATE TABLE t1 (
id int NOT NULL,
name varchar(20) NOT NULL,
dept varchar(20) NOT NULL,
age tinyint(3) unsigned NOT NULL,
PRIMARY KEY (id),
INDEX (name,dept)
) ENGINE=InnoDB;
INSERT INTO t1(id, dept, age, name) VALUES
(3987, 'cs1', 10, 'rs1'), (3988, 'cs2', 20, 'rs1'), (3995, 'cs3', 10, 'rs2'),
(3996, 'cs4', 20, 'rs2'), (4003, 'cs5', 10, 'rs3'), (4004, 'cs6', 20, 'rs3'),
(4011, 'cs7', 10, 'rs4'), (4012, 'cs8', 20, 'rs4'), (4019, 'cs9', 10, 'rs5'),
(4020, 'cs10', 20, 'rs5'),(4027, 'cs11', 10, 'rs6'),(4028, 'cs12', 20, 'rs6');
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
name	dept
rs5	cs10
rs5	cs9
DELETE FROM t1;
EXPLAIN SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	name	name	44	NULL	2	Using where; Using index for group-by
SELECT DISTINCT t1.name, t1.dept FROM t1 WHERE t1.name='rs5';
name	dept
DROP TABLE t1;
drop table if exists t1;
show variables like 'innodb_rollback_on_timeout';
Variable_name	Value
innodb_rollback_on_timeout	OFF
create table t1 (a int unsigned not null primary key) engine = innodb;
insert into t1 values (1);
commit;
begin work;
insert into t1 values (2);
select * from t1;
a
1
2
begin work;
insert into t1 values (5);
select * from t1;
a
1
5
insert into t1 values (2);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
select * from t1;
a
1
5
commit;
select * from t1;
a
1
2
commit;
select * from t1;
a
1
2
5
drop table t1;
set @save_qcache_size=@@global.query_cache_size;
set @save_qcache_type=@@global.query_cache_type;
set global query_cache_size=10*1024*1024;
set global query_cache_type=1;
drop table if exists `test`;
Warnings:
Note	1051	Unknown table 'test'
CREATE TABLE `test` (`test1` varchar(3) NOT NULL,
`test2` varchar(4) NOT NULL,PRIMARY KEY  (`test1`))
ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '5678');
select * from test;
test1	test2
tes	5678
INSERT INTO `test` (`test1`, `test2`) VALUES ('tes', '1234')
ON DUPLICATE KEY UPDATE `test2` = '1234';
select * from test;
test1	test2
tes	1234
flush tables;
select * from test;
test1	test2
tes	1234
drop table test;
set global query_cache_type=@save_qcache_type;
set global query_cache_size=@save_qcache_size;
drop table if exists t1;
show variables like 'innodb_rollback_on_timeout';
Variable_name	Value
innodb_rollback_on_timeout	OFF
create table t1 (a int unsigned not null primary key) engine = innodb;
insert into t1 values (1);
commit;
begin work;
insert into t1 values (2);
select * from t1;
a
1
2
begin work;
insert into t1 values (5);
select * from t1;
a
1
5
insert into t1 values (2);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
select * from t1;
a
1
5
commit;
select * from t1;
a
1
2
commit;
select * from t1;
a
1
2
5
drop table t1;
create table t1(
id int auto_increment,
c char(1) not null,
counter int not null default 1,
primary key (id),
unique key (c)
) engine=innodb;
insert into t1 (id, c) values
(NULL, 'a'),
(NULL, 'a')
on duplicate key update id = values(id), counter = counter + 1;
select * from t1;
id	c	counter
2	a	2
insert into t1 (id, c) values
(NULL, 'b')
on duplicate key update id = values(id), counter = counter + 1;
select * from t1;
id	c	counter
2	a	2
3	b	1
truncate table t1;
insert into t1 (id, c) values (NULL, 'a');
select * from t1;
id	c	counter
1	a	1
insert into t1 (id, c) values (NULL, 'b'), (NULL, 'b')
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
select * from t1;
id	c	counter
1	a	1
3	b	2
insert into t1 (id, c) values (NULL, 'a')
on duplicate key update id = values(id), c = values(c), counter = counter + 1;
select * from t1;
id	c	counter
3	b	2
4	a	2
drop table t1;
CREATE TABLE t1(
id int AUTO_INCREMENT PRIMARY KEY,
stat_id int NOT NULL,
acct_id int DEFAULT NULL,
INDEX idx1 (stat_id, acct_id),
INDEX idx2 (acct_id)
) ENGINE=MyISAM;
CREATE TABLE t2(
id int AUTO_INCREMENT PRIMARY KEY,
stat_id int NOT NULL,
acct_id int DEFAULT NULL,
INDEX idx1 (stat_id, acct_id),
INDEX idx2 (acct_id)
) ENGINE=InnoDB;
INSERT INTO t1(stat_id,acct_id) VALUES
(1,759), (2,831), (3,785), (4,854), (1,921),
(1,553), (2,589), (3,743), (2,827), (2,545),
(4,779), (4,783), (1,597), (1,785), (4,832),
(1,741), (1,833), (3,788), (2,973), (1,907);
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
INSERT INTO t1(stat_id,acct_id) SELECT stat_id, mod(id+100000, acct_id) FROM t1;
UPDATE t1 SET acct_id=785 
WHERE MOD(stat_id,2)=0 AND MOD(id,stat_id)=MOD(acct_id,stat_id);
OPTIMIZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
SELECT COUNT(*) FROM t1;
COUNT(*)
40960
SELECT COUNT(*) FROM t1 WHERE acct_id=785;
COUNT(*)
8702
EXPLAIN SELECT COUNT(*) FROM t1 WHERE stat_id IN (1,3) AND acct_id=785;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	idx1,idx2	idx1	9	NULL	2	Using where; Using index
INSERT INTO t2 SELECT * FROM t1;
OPTIMIZE TABLE t2;
Table	Op	Msg_type	Msg_text
test.t2	optimize	status	OK
EXPLAIN SELECT COUNT(*) FROM t2 WHERE stat_id IN (1,3) AND acct_id=785;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t2	range	idx1,idx2	idx1	9	NULL	2	Using where; Using index
DROP TABLE t1,t2;
create table t1(a int) engine=innodb;
alter table t1 comment '123';
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='123'
drop table t1;
CREATE TABLE t1 (a CHAR(2), KEY (a)) ENGINE = InnoDB DEFAULT CHARSET=UTF8;
INSERT INTO t1 VALUES ('uk'),('bg');
SELECT * FROM t1 WHERE a = 'uk';
a
uk
DELETE FROM t1 WHERE a = 'uk';
SELECT * FROM t1 WHERE a = 'uk';
a
UPDATE t1 SET a = 'us' WHERE a = 'uk';
SELECT * FROM t1 WHERE a = 'uk';
a
CREATE TABLE t2 (a CHAR(2), KEY (a)) ENGINE = InnoDB;
INSERT INTO t2 VALUES ('uk'),('bg');
SELECT * FROM t2 WHERE a = 'uk';
a
uk
DELETE FROM t2 WHERE a = 'uk';
SELECT * FROM t2 WHERE a = 'uk';
a
INSERT INTO t2 VALUES ('uk');
UPDATE t2 SET a = 'us' WHERE a = 'uk';
SELECT * FROM t2 WHERE a = 'uk';
a
CREATE TABLE t3 (a CHAR(2), KEY (a)) ENGINE = MyISAM;
INSERT INTO t3 VALUES ('uk'),('bg');
SELECT * FROM t3 WHERE a = 'uk';
a
uk
DELETE FROM t3 WHERE a = 'uk';
SELECT * FROM t3 WHERE a = 'uk';
a
INSERT INTO t3 VALUES ('uk');
UPDATE t3 SET a = 'us' WHERE a = 'uk';
SELECT * FROM t3 WHERE a = 'uk';
a
DROP TABLE t1,t2,t3;
create table t1 (a int) engine=innodb;
select * from bug29807;
ERROR 42S02: Table 'test.bug29807' doesn't exist
drop table t1;
drop table bug29807;
ERROR 42S02: Unknown table 'bug29807'
create table bug29807 (a int);
drop table bug29807;
CREATE TABLE t1 (a INT) ENGINE=InnoDB;
CREATE TABLE t2 (a INT) ENGINE=InnoDB;
switch to connection c1
SET AUTOCOMMIT=0;
INSERT INTO t2 VALUES (1);
switch to connection c2
SET AUTOCOMMIT=0;
LOCK TABLES t1 READ, t2 READ;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
switch to connection c1
COMMIT;
INSERT INTO t1 VALUES (1);
switch to connection default
SET AUTOCOMMIT=default;
DROP TABLE t1,t2;
CREATE TABLE t1 (
id int NOT NULL auto_increment PRIMARY KEY,
b int NOT NULL,
c datetime NOT NULL,
INDEX idx_b(b),
INDEX idx_c(c)
) ENGINE=InnoDB;
CREATE TABLE t2 (
b int NOT NULL auto_increment PRIMARY KEY,
c datetime NOT NULL
) ENGINE= MyISAM;
INSERT INTO t2(c) VALUES ('2007-01-01');
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t2(c) SELECT c FROM t2;
INSERT INTO t1(b,c) SELECT b,c FROM t2;
UPDATE t2 SET c='2007-01-02';
INSERT INTO t1(b,c) SELECT b,c FROM t2;
UPDATE t2 SET c='2007-01-03';
INSERT INTO t1(b,c) SELECT b,c FROM t2;
set @@sort_buffer_size=8192;
Warnings:
Warning	1292	Truncated incorrect sort_buffer_size value: '8192'
SELECT COUNT(*) FROM t1;
COUNT(*)
3072
EXPLAIN 
SELECT COUNT(*) FROM t1 
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	idx_b,idx_c	NULL	NULL	NULL	#	Using where
SELECT COUNT(*) FROM t1 
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
COUNT(*)
3072
EXPLAIN 
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c) 
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index_merge	idx_b,idx_c	idx_c,idx_b	8,4	NULL	#	Using sort_union(idx_c,idx_b); Using where
SELECT COUNT(*) FROM t1 FORCE INDEX(idx_b, idx_c)
WHERE (c >= '2007-01-02' AND c <= '2007-01-03') OR b >= 1;
COUNT(*)
3072
set @@sort_buffer_size=default;
DROP TABLE t1,t2;
CREATE TABLE t1 (a int, b int);
insert into t1 values (1,1),(1,2);
CREATE TABLE t2 (primary key (a)) select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
drop table if exists t2;
Warnings:
Note	1051	Unknown table 't2'
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
drop table if exists t2;
Warnings:
Note	1051	Unknown table 't2'
CREATE TABLE t2 (a int, b int, primary key (a));
BEGIN;
INSERT INTO t2 values(100,100);
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t2;
a	b
100	100
ROLLBACK;
SELECT * from t2;
a	b
100	100
TRUNCATE table t2;
INSERT INTO t2 select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t2;
a	b
drop table t2;
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
BEGIN;
INSERT INTO t2 values(100,100);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t2;
a	b
100	100
COMMIT;
BEGIN;
INSERT INTO t2 values(101,101);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t2;
a	b
100	100
101	101
ROLLBACK;
SELECT * from t2;
a	b
100	100
TRUNCATE table t2;
INSERT INTO t2 select * from t1;
ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
SELECT * from t2;
a	b
drop table t1,t2;
create table t1(f1 varchar(800) binary not null, key(f1))
character set utf8 collate utf8_general_ci;
Warnings:
Warning	1071	Specified key was too long; max key length is 767 bytes
insert into t1 values('aaa');
drop table t1;
CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c FLOAT, KEY b(b)) ENGINE = INNODB;
INSERT INTO t1 VALUES (    1 , 1              , 1);
INSERT INTO t1 SELECT  a + 1 , MOD(a + 1 , 20), 1 FROM t1;
INSERT INTO t1 SELECT  a + 2 , MOD(a + 2 , 20), 1 FROM t1;
INSERT INTO t1 SELECT  a + 4 , MOD(a + 4 , 20), 1 FROM t1;
INSERT INTO t1 SELECT  a + 8 , MOD(a + 8 , 20), 1 FROM t1;
INSERT INTO t1 SELECT  a + 16, MOD(a + 16, 20), 1 FROM t1;
INSERT INTO t1 SELECT  a + 32, MOD(a + 32, 20), 1 FROM t1;
INSERT INTO t1 SELECT  a + 64, MOD(a + 64, 20), 1 FROM t1;
EXPLAIN SELECT b, SUM(c) FROM t1 GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	b	5	NULL	128	
EXPLAIN SELECT SQL_BIG_RESULT b, SUM(c) FROM t1 GROUP BY b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	128	Using filesort
DROP TABLE t1;
drop table if exists t1;
show variables like 'innodb_rollback_on_timeout';
Variable_name	Value
innodb_rollback_on_timeout	OFF
create table t1 (a int unsigned not null primary key) engine = innodb;
insert into t1 values (1);
commit;
begin work;
insert into t1 values (2);
select * from t1;
a
1
2
begin work;
insert into t1 values (5);
select * from t1;
a
1
5
insert into t1 values (2);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
select * from t1;
a
1
5
commit;
select * from t1;
a
1
2
commit;
select * from t1;
a
1
2
5
drop table t1;
drop table if exists t1;
create table t1 (a int) engine=innodb;
alter table t1 alter a set default 1;
drop table t1;

Bug#24918 drop table and lock / inconsistent between 
perm and temp tables

Check transactional tables under LOCK TABLES

drop table if exists t24918, t24918_tmp, t24918_trans, t24918_trans_tmp, 
t24918_access;
create table t24918_access (id int);
create table t24918 (id int) engine=myisam;
create temporary table t24918_tmp (id int) engine=myisam;
create table t24918_trans (id int) engine=innodb;
create temporary table t24918_trans_tmp (id int) engine=innodb;
lock table t24918 write, t24918_tmp write, t24918_trans write, t24918_trans_tmp write;
drop table t24918;
select * from t24918_access;
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
drop table t24918_trans;
select * from t24918_access;
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
drop table t24918_trans_tmp;
select * from t24918_access;
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
drop table t24918_tmp;
select * from t24918_access;
ERROR HY000: Table 't24918_access' was not locked with LOCK TABLES
unlock tables;
drop table t24918_access;
CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY bkey (b)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,2),(3,2),(2,2),(4,2),(5,2),(6,2),(7,2),(8,2);
INSERT INTO t1 SELECT a + 8, 2 FROM t1;
INSERT INTO t1 SELECT a + 16, 1 FROM t1;
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a;
id	1
select_type	SIMPLE
table	t1
type	ref
possible_keys	bkey
key	bkey
key_len	5
ref	const
rows	16
Extra	Using where; Using index
SELECT * FROM t1 WHERE b=2 ORDER BY a;
a	b
1	2
2	2
3	2
4	2
5	2
6	2
7	2
8	2
9	2
10	2
11	2
12	2
13	2
14	2
15	2
16	2
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
id	1
select_type	SIMPLE
table	t1
type	index
possible_keys	bkey
key	PRIMARY
key_len	4
ref	NULL
rows	32
Extra	Using where
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY a;
a	b
1	2
2	2
3	2
4	2
5	2
6	2
7	2
8	2
9	2
10	2
11	2
12	2
13	2
14	2
15	2
16	2
17	1
18	1
19	1
20	1
21	1
22	1
23	1
24	1
25	1
26	1
27	1
28	1
29	1
30	1
31	1
32	1
EXPLAIN SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
id	1
select_type	SIMPLE
table	t1
type	range
possible_keys	bkey
key	bkey
key_len	5
ref	NULL
rows	16
Extra	Using where; Using index
SELECT * FROM t1 WHERE b BETWEEN 1 AND 2 ORDER BY b,a;
a	b
17	1
18	1
19	1
20	1
21	1
22	1
23	1
24	1
25	1
26	1
27	1
28	1
29	1
30	1
31	1
32	1
1	2
2	2
3	2
4	2
5	2
6	2
7	2
8	2
9	2
10	2
11	2
12	2
13	2
14	2
15	2
16	2
CREATE TABLE t2 (a int, b int, c int, PRIMARY KEY (a), KEY bkey (b,c))
ENGINE=InnoDB;
INSERT INTO t2 VALUES (1,1,1),(3,1,1),(2,1,1),(4,1,1);
INSERT INTO t2 SELECT a + 4, 1, 1 FROM t2;
INSERT INTO t2 SELECT a + 8, 1, 1 FROM t2;
EXPLAIN SELECT * FROM t2 WHERE b=1 ORDER BY a;
id	1
select_type	SIMPLE
table	t2
type	index
possible_keys	bkey
key	PRIMARY
key_len	4
ref	NULL
rows	16
Extra	Using where; Using index
SELECT * FROM t2 WHERE b=1 ORDER BY a;
a	b	c
1	1	1
2	1	1
3	1	1
4	1	1
5	1	1
6	1	1
7	1	1
8	1	1
9	1	1
10	1	1
11	1	1
12	1	1
13	1	1
14	1	1
15	1	1
16	1	1
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
id	1
select_type	SIMPLE
table	t2
type	ref
possible_keys	bkey
key	bkey
key_len	10
ref	const,const
rows	8
Extra	Using where; Using index
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY a;
a	b	c
1	1	1
2	1	1
3	1	1
4	1	1
5	1	1
6	1	1
7	1	1
8	1	1
9	1	1
10	1	1
11	1	1
12	1	1
13	1	1
14	1	1
15	1	1
16	1	1
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
id	1
select_type	SIMPLE
table	t2
type	ref
possible_keys	bkey
key	bkey
key_len	10
ref	const,const
rows	8
Extra	Using where; Using index
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY b,c,a;
a	b	c
1	1	1
2	1	1
3	1	1
4	1	1
5	1	1
6	1	1
7	1	1
8	1	1
9	1	1
10	1	1
11	1	1
12	1	1
13	1	1
14	1	1
15	1	1
16	1	1
EXPLAIN SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
id	1
select_type	SIMPLE
table	t2
type	ref
possible_keys	bkey
key	bkey
key_len	10
ref	const,const
rows	8
Extra	Using where; Using index
SELECT * FROM t2 WHERE b=1 AND c=1 ORDER BY c,a;
a	b	c
1	1	1
2	1	1
3	1	1
4	1	1
5	1	1
6	1	1
7	1	1
8	1	1
9	1	1
10	1	1
11	1	1
12	1	1
13	1	1
14	1	1
15	1	1
16	1	1
DROP TABLE t1,t2;
CREATE TABLE t1 (a INT, PRIMARY KEY (a)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO t1 SELECT a + 8  FROM t1;
INSERT INTO t1 SELECT a + 16 FROM t1;
CREATE PROCEDURE p1 ()
BEGIN
DECLARE i INT DEFAULT 50;
DECLARE cnt INT;
START TRANSACTION;
ALTER TABLE t1 ENGINE=InnoDB;
COMMIT;
START TRANSACTION;
WHILE (i > 0) DO
SET i = i - 1;
SELECT COUNT(*) INTO cnt FROM t1 LOCK IN SHARE MODE;
END WHILE;
COMMIT;
END;|
CALL p1();
CALL p1();
CALL p1();
DROP PROCEDURE p1;
DROP TABLE t1;
create table t1(a text) engine=innodb default charset=utf8;
insert into t1 values('aaa');
alter table t1 add index(a(1024));
Warnings:
Warning	1071	Specified key was too long; max key length is 767 bytes
Warning	1071	Specified key was too long; max key length is 767 bytes
Warning	1071	Specified key was too long; max key length is 767 bytes
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` text,
  KEY `a` (`a`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
drop table t1;
CREATE TABLE t1 (
a INT,
b INT,
KEY (b)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1,10), (2,10), (2,20), (3,30);
START TRANSACTION;
SELECT * FROM t1 WHERE b=20 FOR UPDATE;
a	b
2	20
START TRANSACTION;
SELECT * FROM t1 WHERE b=10 ORDER BY A FOR UPDATE;
a	b
1	10
2	10
ROLLBACK;
ROLLBACK;
DROP TABLE t1;
CREATE TABLE t1(
a INT, 
b INT NOT NULL, 
c INT NOT NULL, 
d INT, 
UNIQUE KEY (c,b)
) engine=innodb;
INSERT INTO t1 VALUES (1,1,1,50), (1,2,3,40), (2,1,3,4);
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort
SELECT c,b,d FROM t1 GROUP BY c,b,d;
c	b	d
1	1	50
3	1	4
3	2	40
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
SELECT c,b,d FROM t1 GROUP BY c,b,d ORDER BY NULL;
c	b	d
1	1	50
3	1	4
3	2	40
EXPLAIN SELECT c,b,d FROM t1 ORDER BY c,b,d;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	Using filesort
SELECT c,b,d FROM t1 ORDER BY c,b,d;
c	b	d
1	1	50
3	1	4
3	2	40
EXPLAIN SELECT c,b,d FROM t1 GROUP BY c,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	c	8	NULL	3	
SELECT c,b,d FROM t1 GROUP BY c,b;
c	b	d
1	1	50
3	1	4
3	2	40
EXPLAIN SELECT c,b   FROM t1 GROUP BY c,b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	c	8	NULL	3	Using index
SELECT c,b   FROM t1 GROUP BY c,b;
c	b
1	1
3	1
3	2
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a), INDEX b (b)) ENGINE=InnoDB;
INSERT INTO t1(a,b) VALUES (1,1), (2,2), (3,2);
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
id	1
select_type	SIMPLE
table	t1
type	ref
possible_keys	b
key	b
key_len	5
ref	const
rows	1
Extra	Using where; Using index
SELECT * FROM t1 WHERE b=2 ORDER BY a ASC;
a	b
2	2
3	2
EXPLAIN SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
id	1
select_type	SIMPLE
table	t1
type	ref
possible_keys	b
key	b
key_len	5
ref	const
rows	1
Extra	Using where; Using index
SELECT * FROM t1 WHERE b=2 ORDER BY a DESC;
a	b
3	2
2	2
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a ASC;
id	1
select_type	SIMPLE
table	t1
type	index
possible_keys	NULL
key	b
key_len	5
ref	NULL
rows	3
Extra	Using index
SELECT * FROM t1 ORDER BY b ASC, a ASC;
a	b
1	1
2	2
3	2
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a DESC;
id	1
select_type	SIMPLE
table	t1
type	index
possible_keys	NULL
key	b
key_len	5
ref	NULL
rows	3
Extra	Using index
SELECT * FROM t1 ORDER BY b DESC, a DESC;
a	b
3	2
2	2
1	1
EXPLAIN SELECT * FROM t1 ORDER BY b ASC, a DESC;
id	1
select_type	SIMPLE
table	t1
type	index
possible_keys	NULL
key	PRIMARY
key_len	4
ref	NULL
rows	3
Extra	Using filesort
SELECT * FROM t1 ORDER BY b ASC, a DESC;
a	b
1	1
3	2
2	2
EXPLAIN SELECT * FROM t1 ORDER BY b DESC, a ASC;
id	1
select_type	SIMPLE
table	t1
type	index
possible_keys	NULL
key	PRIMARY
key_len	4
ref	NULL
rows	3
Extra	Using filesort
SELECT * FROM t1 ORDER BY b DESC, a ASC;
a	b
2	2
3	2
1	1
DROP TABLE t1;

#
# Bug#27610: ALTER TABLE ROW_FORMAT=... does not rebuild the table.
#

# - prepare;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1(c INT)
ENGINE = InnoDB
ROW_FORMAT = COMPACT;

# - initial check;

SELECT table_schema, table_name, row_format
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = DATABASE() AND table_name = 't1';
table_schema	table_name	row_format
test	t1	Compact

# - change ROW_FORMAT and check;

ALTER TABLE t1 ROW_FORMAT = REDUNDANT;

SELECT table_schema, table_name, row_format
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = DATABASE() AND table_name = 't1';
table_schema	table_name	row_format
test	t1	Redundant

# - that's it, cleanup.

DROP TABLE t1;
create table t1(a char(10) not null, unique key aa(a(1)),
b char(4) not null, unique key bb(b(4))) engine=innodb;
desc t1;
Field	Type	Null	Key	Default	Extra
a	char(10)	NO	UNI	NULL	
b	char(4)	NO	PRI	NULL	
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` char(10) NOT NULL,
  `b` char(4) NOT NULL,
  UNIQUE KEY `bb` (`b`),
  UNIQUE KEY `aa` (`a`(1))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
drop table t1;
CREATE TABLE t1 (id int, type char(6), d int, INDEX idx(id,d)) ENGINE=InnoDB;
INSERT INTO t1 VALUES 
(191, 'member', 1), (NULL, 'member', 3), (NULL, 'member', 4), (201, 'member', 2);
EXPLAIN SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	idx	NULL	NULL	NULL	4	Using where; Using filesort
SELECT * FROM t1 WHERE id=191 OR id IS NULL ORDER BY d;
id	type	d
191	member	1
NULL	member	3
NULL	member	4
DROP TABLE t1;
set @my_innodb_autoextend_increment=@@global.innodb_autoextend_increment;
set global innodb_autoextend_increment=8;
set global innodb_autoextend_increment=@my_innodb_autoextend_increment;
set @my_innodb_commit_concurrency=@@global.innodb_commit_concurrency;
set global innodb_commit_concurrency=0;
set global innodb_commit_concurrency=@my_innodb_commit_concurrency;
End of 5.0 tests
CREATE TABLE `t2` (
`k` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
`c` int(11) default NULL,
PRIMARY KEY  (`k`),
UNIQUE KEY `idx_1` (`a`)
);
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
ifnull( c,
0 ) + 1;
insert into t2 ( a ) values ( 7 ) on duplicate key update c =
ifnull( c,
0 ) + 1;
select last_insert_id();
last_insert_id()
2
select * from t2;
k	a	c
1	6	NULL
2	7	NULL
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
ifnull( c,
0 ) + 1;
select last_insert_id();
last_insert_id()
2
select last_insert_id(0);
last_insert_id(0)
0
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
ifnull( c,
0 ) + 1;
select last_insert_id();
last_insert_id()
0
select * from t2;
k	a	c
1	6	2
2	7	NULL
insert ignore into t2 values (null,6,1),(10,8,1);
select last_insert_id();
last_insert_id()
0
insert ignore into t2 values (null,6,1),(null,8,1),(null,15,1),(null,20,1);
select last_insert_id();
last_insert_id()
11
select * from t2;
k	a	c
1	6	2
2	7	NULL
10	8	1
11	15	1
12	20	1
insert into t2 ( a ) values ( 6 ) on duplicate key update c =
ifnull( c,
0 ) + 1, k=last_insert_id(k);
select last_insert_id();
last_insert_id()
1
select * from t2;
k	a	c
1	6	3
2	7	NULL
10	8	1
11	15	1
12	20	1
drop table t2;
drop table if exists t1, t2;
create table t1 (i int);
alter table t1 modify i int default 1;
alter table t1 modify i int default 2, rename t2;
lock table t2 write;
alter table t2 modify i int default 3;
unlock tables;
lock table t2 write;
alter table t2 modify i int default 4, rename t1;
unlock tables;
drop table t1;
drop table if exists t1;
create table t1 (i int);
insert into t1 values ();
lock table t1 write;
alter table t1 modify i int default 1;
insert into t1 values ();
select * from t1;
i
NULL
1
alter table t1 change i c char(10) default "Two";
insert into t1 values ();
select * from t1;
c
NULL
1
Two
unlock tables;
select * from t1;
c
NULL
1
Two
drop tables t1;
create table t1(f1 varchar(5) unique, f2 timestamp NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
insert into t1(f1) values(1);
select @a:=f2 from t1;
@a:=f2
#
update t1 set f1=1;
select @b:=f2 from t1;
@b:=f2
#
select if(@a=@b,"ok","wrong");
if(@a=@b,"ok","wrong")
ok
insert into t1(f1) values (1) on duplicate key update f1="1";
select @b:=f2 from t1;
@b:=f2
#
select if(@a=@b,"ok","wrong");
if(@a=@b,"ok","wrong")
ok
insert into t1(f1) select f1 from t1 on duplicate key update f1="1";
select @b:=f2 from t1;
@b:=f2
#
select if(@a=@b,"ok","wrong");
if(@a=@b,"ok","wrong")
ok
drop table t1;
SET SESSION AUTOCOMMIT = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# Switch to connection con1
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
ENGINE = InnoDB;
INSERT INTO t1 VALUES (1,2);
# 1. test for locking:
BEGIN;
UPDATE t1 SET b = 12 WHERE a = 1;
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
SELECT * FROM t1;
a	b
1	12
# Switch to connection con2
UPDATE t1 SET b = 21 WHERE a = 1;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# Switch to connection con1
SELECT * FROM t1;
a	b
1	12
ROLLBACK;
# 2. test for serialized update:
CREATE TABLE t2 (a INT);
TRUNCATE t1;
INSERT INTO t1 VALUES (1,'init');
CREATE PROCEDURE p1()
BEGIN
UPDATE t1 SET b = CONCAT(b, '+con2')  WHERE a = 1;
INSERT INTO t2 VALUES ();
END|
BEGIN;
UPDATE t1 SET b = CONCAT(b, '+con1') WHERE a = 1;
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
SELECT * FROM t1;
a	b
1	init+con1
# Switch to connection con2
CALL p1;;
# Switch to connection con1
SELECT * FROM t1;
a	b
1	init+con1
COMMIT;
SELECT * FROM t1;
a	b
1	init+con1
# Switch to connection con2
SELECT * FROM t1;
a	b
1	init+con1+con2
# Switch to connection con1
# 3. test for updated key column:
TRUNCATE t1;
TRUNCATE t2;
INSERT INTO t1 VALUES (1,'init');
BEGIN;
UPDATE t1 SET a = 2, b = CONCAT(b, '+con1') WHERE a = 1;
affected rows: 1
info: Rows matched: 1  Changed: 1  Warnings: 0
SELECT * FROM t1;
a	b
2	init+con1
# Switch to connection con2
CALL p1;;
# Switch to connection con1
SELECT * FROM t1;
a	b
2	init+con1
COMMIT;
SELECT * FROM t1;
a	b
2	init+con1
# Switch to connection con2
SELECT * FROM t1;
a	b
2	init+con1
DROP PROCEDURE p1;
DROP TABLE t1, t2;
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
ALTER TABLE t2 DROP FOREIGN KEY c2;
DROP TABLE t2;
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`c`,`d`),
  CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
  CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
  CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
DROP TABLE t2;
DROP TABLE t1;
create table t1 (a int auto_increment primary key) engine=innodb;
alter table t1 order by a;
Warnings:
Warning	1105	ORDER BY ignored as there is a user-defined clustered index in the table 't1'
drop table t1;
CREATE TABLE t1
(vid integer NOT NULL,
tid integer NOT NULL,
idx integer NOT NULL,
name varchar(128) NOT NULL,
type varchar(128) NULL,
PRIMARY KEY(idx, vid, tid),
UNIQUE(vid, tid, name)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
(1,1,1,'pk',NULL),(2,1,1,'pk',NULL),(3,1,1,'pk',NULL),(4,1,1,'c1',NULL),
(5,1,1,'pk',NULL),(1,1,2,'c1',NULL),(2,1,2,'c1',NULL),(3,1,2,'c1',NULL),
(4,1,2,'c2',NULL),(5,1,2,'c1',NULL),(2,1,3,'c2',NULL),(3,1,3,'c2',NULL),
(4,1,3,'pk',NULL),(5,1,3,'c2',NULL),
(2,1,4,'c_extra',NULL),(3,1,4,'c_extra',NULL);
EXPLAIN SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	vid	PRIMARY	12	NULL	16	Using where
SELECT * FROM t1 WHERE tid = 1 AND vid = 3 ORDER BY idx DESC;
vid	tid	idx	name	type
3	1	4	c_extra	NULL
3	1	3	c2	NULL
3	1	2	c1	NULL
3	1	1	pk	NULL
DROP TABLE t1;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(id INT PRIMARY KEY)
ENGINE=innodb;
CREATE TABLE t2(
t1_id INT PRIMARY KEY,
CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
ENGINE=innodb;

ALTER TABLE t1 CHANGE id id2 INT;

DROP TABLE t2;
DROP TABLE t1;
End of 5.1 tests