Debian Codegraph @

mysql-test/suite/innodb/t/undo_log_temp_table.test

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