1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
|
#
# WL#6915: InnoDB: Undo logs for temp-tables (and related objects) should
# reside in temp-tablespace
#
--source include/no_valgrind_without_big.inc
################################################################################
#
# Will test following scenarios:
# 1. Try loading table with commit and rollback action
# (this will ensure proper rseg is being used).
# 2. Load temp and non-temp table with purge activity.
# 3. In xa trx try loading temp + non-temp table.
# (Same as case-2 but from xa perspective)
#
################################################################################
#-----------------------------------------------------------------------------
#
# create test-bed
#
set global innodb_file_per_table = off;
#-----------------------------------------------------------------------------
#
# 1. Try loading table with commit and rollback action.
# (this will ensure proper rseg is being used).
#
use test;
create temporary table t1
(a int, b char(100), c char(100)) engine = innodb;
--source suite/innodb/include/innodb_rollback_segments_action.inc
drop table t1;
#
create table t1
(a int, b char(100), c char(100)) engine = innodb;
--source suite/innodb/include/innodb_rollback_segments_action.inc
drop table t1;
#-----------------------------------------------------------------------------
#
# 2. Load temp and non-temp table with purge activity.
#
use test;
create temporary table t1
(a int, b char(100), c char(100)) engine = innodb;
create table t2
(a int, b char(100), c char(100)) engine = innodb;
delimiter |;
create procedure populate_t1_t2()
begin
declare i int default 1;
while (i <= 2000) DO
insert into t1 values (i, 'a', 'b');
insert into t2 values (i, 'a', 'b');
set i = i + 1;
end while;
end|
create procedure populate_t1_t2_small()
begin
declare i int default 1;
while (i <= 200) DO
insert into t1 values (i, 'a', 'b');
insert into t2 values (i, 'a', 'b');
set i = i + 1;
end while;
end|
delimiter ;|
begin;
call populate_t1_t2();
commit;
select count(*) from t1;
select count(*) from t2;
#
begin;
delete from t1 where a <= 100;
delete from t2 where a > 1900;
select count(*) from t1;
select count(*) from t2;
commit;
#
begin;
update t1 set b = 'innodb' where b = 'a' and a <= 1000;
update t2 set b = 'myisam' where c = 'b' and a > 1000;
commit;
#
delete from t1 where b = 'a';
delete from t2 where c = 'myisam';
select count(*) from t1;
select count(*) from t2;
#
insert into t1 values (1000000, 'mysql', 'db');
begin;
call populate_t1_t2_small();
select count(*) from t1;
select count(*) from t2;
rollback;
select count(*) from t1;
select count(*) from t2;
#
drop table t2;
drop table t1;
drop procedure populate_t1_t2;
drop procedure populate_t1_t2_small;
#-----------------------------------------------------------------------------
#
# 3. In xa trx try loading temp + non-temp table.
# (Same as case-2 but from xa perspective)
#
--echo # The use of temporary tables in XA transactions is only permitted when
--echo # xa_detach_on_prepare is OFF.
SET @saved_xa_detach_on_prepare = @@SESSION.xa_detach_on_prepare;
SET SESSION xa_detach_on_prepare = OFF;
create temporary table t1 (i int) engine=innodb;
create table t2 (i int) engine=innodb;
insert into t1 values (1);
insert into t2 values (101);
select * from t1;
select * from t2;
#
xa start 'tx1';
insert into t1 values (2), (3);
insert into t2 values (202), (303);
select * from t1;
select * from t2;
xa end 'tx1';
xa prepare 'tx1';
xa commit 'tx1';
select * from t1;
select * from t2;
#
xa start 'tx1';
insert into t1 values (2), (3);
insert into t2 values (202), (303);
select * from t1;
select * from t2;
xa end 'tx1';
xa prepare 'tx1';
xa rollback 'tx1';
select * from t1;
select * from t2;
#
#
xa start 'tx1';
insert into t1 values (2), (3);
select * from t1;
xa end 'tx1';
xa prepare 'tx1';
xa commit 'tx1';
select * from t1;
#
drop table t1;
drop table t2;
SET SESSION xa_detach_on_prepare = @saved_xa_detach_on_prepare;
#-----------------------------------------------------------------------------
#
# remove test-bed
#
set global innodb_file_per_table=default;
|
