create temporary table t1
(keyc int, c1 char(100), c2 char(100),
primary key(keyc), index sec_index(c1)
) engine = innodb;
create temporary table t2
(keyc int, c1 char(100), c2 char(100),
primary key(keyc), index sec_index(c1)
) engine = innodb;
create procedure populate_t1()
begin
declare i int default 1;
while (i <= 200) do
insert into t1 values (i, 'a', 'b');
set i = i + 1;
end while;
end|
create procedure populate_t1_small()
begin
declare i int default 1;
while (i <= 20) do
insert into t1 values (i, 'c', 'd');
set i = i + 1;
end while;
end|
create procedure populate_t1_small2()
begin
declare i int default 30;
while (i <= 50) do
insert into t1 values (i, 'e', 'f');
set i = i + 1;
end while;
end|
begin;
select count(*) from t1;
count(*)
0
call populate_t1();
select count(*) from t1;
count(*)
200
select * from t1 limit 10;
keyc	c1	c2
1	a	b
2	a	b
3	a	b
4	a	b
5	a	b
6	a	b
7	a	b
8	a	b
9	a	b
10	a	b
rollback;
select count(*) from t1;
count(*)
0
begin;
call populate_t1();
select count(*) from t1;
count(*)
200
commit;
select count(*) from t1;
count(*)
200
truncate table t1;
select count(*) from t1;
count(*)
0
call populate_t1_small();
select count(*) from t1;
count(*)
20
rollback;
select count(*) from t1;
count(*)
20
truncate table t1;
call populate_t1();
select count(*) from t1;
count(*)
200
delete from t1 where keyc <= 60;
select count(*) from t1;
count(*)
140
call populate_t1_small();
select count(*) from t1;
count(*)
160
select * from t1 limit 10;
keyc	c1	c2
1	c	d
2	c	d
3	c	d
4	c	d
5	c	d
6	c	d
7	c	d
8	c	d
9	c	d
10	c	d
begin;
call populate_t1_small2();
select count(*) from t1;
count(*)
181
select * from t1 where keyc > 30 limit 10;
keyc	c1	c2
31	e	f
32	e	f
33	e	f
34	e	f
35	e	f
36	e	f
37	e	f
38	e	f
39	e	f
40	e	f
rollback;
select count(*) from t1;
count(*)
160
select * from t1 where keyc > 30 limit 10;
keyc	c1	c2
61	a	b
62	a	b
63	a	b
64	a	b
65	a	b
66	a	b
67	a	b
68	a	b
69	a	b
70	a	b
update t1 set keyc = keyc + 2000;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
rollback;
begin;
update t1 set keyc = keyc + 2000;
select * from t1 limit 10;
keyc	c1	c2
4001	c	d
4002	c	d
4003	c	d
4004	c	d
4005	c	d
4006	c	d
4007	c	d
4008	c	d
4009	c	d
4010	c	d
rollback;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
commit;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
insert into t2 select * from t1 where keyc < 2101;
select count(*) from t2;
count(*)
60
drop procedure populate_t1;
drop procedure populate_t1_small;
drop procedure populate_t1_small2;
drop temporary table t1,t2;
create temporary table t1
(keyc int, c1 char(100), c2 char(100),
primary key(keyc), index sec_index(c1)
) engine = innodb key_block_size = 4;
ERROR HY000: CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.
create temporary table t1
(keyc int, c1 char(100), c2 char(100),
primary key(keyc), index sec_index(c1)
) engine = innodb;
set innodb_strict_mode=off;
create temporary table t2
(keyc int, c1 char(100), c2 char(100),
primary key(keyc), index sec_index(c1)
) engine = innodb key_block_size = 8;
set innodb_strict_mode=on;
create procedure populate_t1()
begin
declare i int default 1;
while (i <= 200) do
insert into t1 values (i, 'a', 'b');
set i = i + 1;
end while;
end|
create procedure populate_t1_small()
begin
declare i int default 1;
while (i <= 20) do
insert into t1 values (i, 'c', 'd');
set i = i + 1;
end while;
end|
create procedure populate_t1_small2()
begin
declare i int default 30;
while (i <= 50) do
insert into t1 values (i, 'e', 'f');
set i = i + 1;
end while;
end|
begin;
select count(*) from t1;
count(*)
0
call populate_t1();
select count(*) from t1;
count(*)
200
select * from t1 limit 10;
keyc	c1	c2
1	a	b
2	a	b
3	a	b
4	a	b
5	a	b
6	a	b
7	a	b
8	a	b
9	a	b
10	a	b
rollback;
select count(*) from t1;
count(*)
0
begin;
call populate_t1();
select count(*) from t1;
count(*)
200
commit;
select count(*) from t1;
count(*)
200
truncate table t1;
select count(*) from t1;
count(*)
0
call populate_t1_small();
select count(*) from t1;
count(*)
20
rollback;
select count(*) from t1;
count(*)
20
truncate table t1;
call populate_t1();
select count(*) from t1;
count(*)
200
delete from t1 where keyc <= 60;
select count(*) from t1;
count(*)
140
call populate_t1_small();
select count(*) from t1;
count(*)
160
select * from t1 limit 10;
keyc	c1	c2
1	c	d
2	c	d
3	c	d
4	c	d
5	c	d
6	c	d
7	c	d
8	c	d
9	c	d
10	c	d
begin;
call populate_t1_small2();
select count(*) from t1;
count(*)
181
select * from t1 where keyc > 30 limit 10;
keyc	c1	c2
31	e	f
32	e	f
33	e	f
34	e	f
35	e	f
36	e	f
37	e	f
38	e	f
39	e	f
40	e	f
rollback;
select count(*) from t1;
count(*)
160
select * from t1 where keyc > 30 limit 10;
keyc	c1	c2
61	a	b
62	a	b
63	a	b
64	a	b
65	a	b
66	a	b
67	a	b
68	a	b
69	a	b
70	a	b
update t1 set keyc = keyc + 2000;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
rollback;
begin;
update t1 set keyc = keyc + 2000;
select * from t1 limit 10;
keyc	c1	c2
4001	c	d
4002	c	d
4003	c	d
4004	c	d
4005	c	d
4006	c	d
4007	c	d
4008	c	d
4009	c	d
4010	c	d
rollback;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
commit;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
insert into t2 select * from t1 where keyc < 2101;
select count(*) from t2;
count(*)
60
drop procedure populate_t1;
drop procedure populate_t1_small;
drop procedure populate_t1_small2;
drop temporary table t1, t2;
create temporary table t1
(keyc int, c1 char(100), c2 char(100),
primary key(keyc), index sec_index(c1)
) engine = innodb;
create temporary table t2
(keyc int, c1 char(100), c2 char(100),
primary key(keyc), index sec_index(c1)
) engine = innodb;
create procedure populate_t1()
begin
declare i int default 1;
while (i <= 200) do
insert into t1 values (i, 'a', 'b');
set i = i + 1;
end while;
end|
create procedure populate_t1_small()
begin
declare i int default 1;
while (i <= 20) do
insert into t1 values (i, 'c', 'd');
set i = i + 1;
end while;
end|
create procedure populate_t1_small2()
begin
declare i int default 30;
while (i <= 50) do
insert into t1 values (i, 'e', 'f');
set i = i + 1;
end while;
end|
begin;
select count(*) from t1;
count(*)
0
call populate_t1();
select count(*) from t1;
count(*)
200
select * from t1 limit 10;
keyc	c1	c2
1	a	b
2	a	b
3	a	b
4	a	b
5	a	b
6	a	b
7	a	b
8	a	b
9	a	b
10	a	b
rollback;
select count(*) from t1;
count(*)
0
begin;
call populate_t1();
select count(*) from t1;
count(*)
200
commit;
select count(*) from t1;
count(*)
200
truncate table t1;
select count(*) from t1;
count(*)
0
call populate_t1_small();
select count(*) from t1;
count(*)
20
rollback;
select count(*) from t1;
count(*)
20
truncate table t1;
call populate_t1();
select count(*) from t1;
count(*)
200
delete from t1 where keyc <= 60;
select count(*) from t1;
count(*)
140
call populate_t1_small();
select count(*) from t1;
count(*)
160
select * from t1 limit 10;
keyc	c1	c2
1	c	d
2	c	d
3	c	d
4	c	d
5	c	d
6	c	d
7	c	d
8	c	d
9	c	d
10	c	d
begin;
call populate_t1_small2();
select count(*) from t1;
count(*)
181
select * from t1 where keyc > 30 limit 10;
keyc	c1	c2
31	e	f
32	e	f
33	e	f
34	e	f
35	e	f
36	e	f
37	e	f
38	e	f
39	e	f
40	e	f
rollback;
select count(*) from t1;
count(*)
160
select * from t1 where keyc > 30 limit 10;
keyc	c1	c2
61	a	b
62	a	b
63	a	b
64	a	b
65	a	b
66	a	b
67	a	b
68	a	b
69	a	b
70	a	b
update t1 set keyc = keyc + 2000;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
rollback;
begin;
update t1 set keyc = keyc + 2000;
select * from t1 limit 10;
keyc	c1	c2
4001	c	d
4002	c	d
4003	c	d
4004	c	d
4005	c	d
4006	c	d
4007	c	d
4008	c	d
4009	c	d
4010	c	d
rollback;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
commit;
select * from t1 limit 10;
keyc	c1	c2
2001	c	d
2002	c	d
2003	c	d
2004	c	d
2005	c	d
2006	c	d
2007	c	d
2008	c	d
2009	c	d
2010	c	d
insert into t2 select * from t1 where keyc < 2101;
select count(*) from t2;
count(*)
60
drop procedure populate_t1;
drop procedure populate_t1_small;
drop procedure populate_t1_small2;